Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts
SQL Injection
SQL injection is a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).[1] SQL injection must exploit a security vulnerability in an application's software, for example, when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.
PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:
public insertUser(String name, String email) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = setupTheDatabaseConnectionSomehow();
stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
stmt.setString(1, name);
stmt.setString(2, email);
stmt.executeUpdate();
}
finally {
try {
if (stmt != null) { stmt.close(); }
}
catch (Exception e) {
// log this error
}
try {
if (conn != null) { conn.close(); }
}
catch (Exception e) {
// log this error
}
}
}
Reference :
http://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection
JDBC - Java - Theory - Interview questions
JDBC Basics :
Establishing a connection. : First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This connection is represented by a Connection object.
Create a statement. : A Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create a Statement object.
There are three different kinds of statements:
Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.
Execute the query. :
To execute a query, call an execute method from Statement such as the following:
execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.
executeQuery: Returns one ResultSet object.
executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
Process the ResultSet object. : You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.
Close the connection. : When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.
For example, the method CoffeesTables.viewTable ensures that the Statement object is closed at the end of the method, regardless of any SQLException objects thrown, by wrapping it in a finally block:
} finally {
if (stmt != null) { stmt.close(); }
}
JDBC throws an SQLException when it encounters an error during an interaction with a data source.
Establishing a connection. : First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This connection is represented by a Connection object.
Create a statement. : A Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create a Statement object.
There are three different kinds of statements:
Statement: Used to implement simple SQL statements with no parameters.
PreparedStatement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information.
CallableStatement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.
Execute the query. :
To execute a query, call an execute method from Statement such as the following:
execute: Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.
executeQuery: Returns one ResultSet object.
executeUpdate: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.
Process the ResultSet object. : You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.
Close the connection. : When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.
For example, the method CoffeesTables.viewTable ensures that the Statement object is closed at the end of the method, regardless of any SQLException objects thrown, by wrapping it in a finally block:
} finally {
if (stmt != null) { stmt.close(); }
}
JDBC throws an SQLException when it encounters an error during an interaction with a data source.
DB2 - GROUP BY - HAVING
GROUP BY : To group different rows of data on specific coloumn name .
HAVING : on finale output of select query put we can put a condition to filter specific
Note : While "WHERE" is used to apply selection criteria to the base data, "HAVING" is used to apply selection criteria to the grouped data:
Example 1 :
TOUR_GROUP : Table has following coloumns
TOUR GUIDE LANGUAGE TOUR_DATE START_TIME END_TIME GROUP_SIZE AVAILABILITY
Query 1 :
SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
GROUP BY LANGUAGE
ORDER BY NUMBER_OF_TOURS;
Query 2 :
SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
GROUP BY LANGUAGE
ORDER BY NUMBER_OF_TOURS;
Query 2 :
SELECT LANGUAGE, COUNT(*) "NUMBER_OF_TOURS", MAX(GROUP_SIZE) "MAX_GROUP_SIZE", MIN(GROUP_SIZE) "MIN_GROUP_SIZE"
FROM TOUR_GROUP
WHERE GROUP_SIZE <= 20
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY NUMBER_OF_TOURS;
FROM TOUR_GROUP
WHERE GROUP_SIZE <= 20
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY NUMBER_OF_TOURS;
Query 3 :
SELECT TOUR, COUNT (DISTINCT LANGUAGE) "NUMBER_LANGUAGES"
FROM TOUR_GROUP
GROUP BY TOUR
ORDER BY COUNT(DISTINCT LANGUAGE), TOUR;
FROM TOUR_GROUP
GROUP BY TOUR
ORDER BY COUNT(DISTINCT LANGUAGE), TOUR;
Reference : [1] [2]
Inner join Vs Outer join
This is most common interview question for many developers ... I would like to give a easy understanding of this to remember for life ...
- An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
- An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.
Suppose you have two Tables, with a single column each, and data as follows:
A B
- -
1 3
2 4
3 5
4 6
Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.
select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4
Left outer join
A left outer join will give all rows in A, plus any common rows in B.
select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b(+);
a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4
Full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.
select * from a FULL OUTER JOIN b on a.a = b.b;
a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5
Inner join
Full Outer join
Reference : [1]
I was looking for a SQL query which can display the Trigger description, like while creating a trigger what condition was used especially which key word used like new or old. The below SQL query helped me to know such details.
How to see created trigger description and SQl stament , to confirm new or old is used ?
Launch the SQL prompt and connect to you database as sysdba.
Then run the below commands to know about the trigger you created ...
SQL> drop trigger TSIN_TR;
Trigger dropped.
SQL> CREATE OR REPLACE TRIGGER TSIN_TR
2 AFTER INSERT ON TARGET_SERVER
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
6 WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
7 END;
8 /
Trigger created.
SQL> set linesize 200
SQL> set pagesize 500
SQL> desc user_source;
Name Null? Type
---------------------------------------------------------------------------------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> select distinct type from user_source;
TYPE
------------
TRIGGER
SQL> select text from user_source where name='TSIN_TR' order by line asc;
TEXT
----------------------------------------------------------------------------------------------------
TRIGGER TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
END;
7 rows selected.
SQL> select DESCRIPTION, TRIGGER_BODY from user_triggers where trigger_name = 'TSIN_TR';
DESCRIPTION
----------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGET
SQL >
Hope this helps ...
How to see created trigger description and SQl stament , to confirm new or old is used ?
Launch the SQL prompt and connect to you database as sysdba.
Then run the below commands to know about the trigger you created ...
SQL> drop trigger TSIN_TR;
Trigger dropped.
SQL> CREATE OR REPLACE TRIGGER TSIN_TR
2 AFTER INSERT ON TARGET_SERVER
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
6 WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
7 END;
8 /
Trigger created.
SQL> set linesize 200
SQL> set pagesize 500
SQL> desc user_source;
Name Null? Type
---------------------------------------------------------------------------------------------------
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> select distinct type from user_source;
TYPE
------------
TRIGGER
SQL> select text from user_source where name='TSIN_TR' order by line asc;
TEXT
----------------------------------------------------------------------------------------------------
TRIGGER TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGETLIST_MAP T
WHERE T.TARGETLIST_ID = :NEW.TARGETLIST_ID;
END;
7 rows selected.
SQL> select DESCRIPTION, TRIGGER_BODY from user_triggers where trigger_name = 'TSIN_TR';
DESCRIPTION
----------------------------------------------------------------------------------------------------
TRIGGER_BODY
--------------------------------------------------------------------------------
TSIN_TR
AFTER INSERT ON TARGET_SERVER
FOR EACH ROW
BEGIN
INSERT INTO ADJUST_DISTRIBUTION (PACKAGE_ID) SELECT PACKAGE_ID FROM TARGET
SQL >
Hope this helps ...
While creating New databse If you are facing any of the below errors .. please follow the below solution.
db2inst1@nc184158:~/db2inst1/NODE0000/SQL00001> db2 -tf /opt/builds/DCD1321/MC/cds_db2_admin.sql
SQL1005N The database alias "CDSDB" already exists in either the local
database directory or system database directory.
SQL1013N The database alias name or database name "CDSDB" could not be found.
SQLSTATE=42705
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL0843N The server name does not specify an existing connection.
SQLSTATE=08003
SQL1013N The database alias name or database name "CDSDB" could not be found.
SQLSTATE=42705
SQL1005N The database alias "<name>" already exists
Solution
1. Db2 get dbm cfg | grep DFTDBPATH ( if unix , windows check for DFTDBPATH)
2. Db2 catalog db dbname on DFTDBPATH
3. Db2 terminate
4. Db2 drop db dbname
5. Then create db
db2inst1@nc184158:~/db2inst1/NODE0000/SQL00001> db2 -tf /opt/builds/DCD1321/MC/cds_db2_admin.sql
SQL1005N The database alias "CDSDB" already exists in either the local
database directory or system database directory.
SQL1013N The database alias name or database name "CDSDB" could not be found.
SQLSTATE=42705
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1024N A database connection does not exist. SQLSTATE=08003
SQL0843N The server name does not specify an existing connection.
SQLSTATE=08003
SQL1013N The database alias name or database name "CDSDB" could not be found.
SQLSTATE=42705
SQL1005N The database alias "<name>" already exists
Solution
1. Db2 get dbm cfg | grep DFTDBPATH ( if unix , windows check for DFTDBPATH)
2. Db2 catalog db dbname on DFTDBPATH
3. Db2 terminate
4. Db2 drop db dbname
5. Then create db
How to change the location of Datable used by a Table space in Oracle?
Process to change the location of TABLE SPACE file in oracle :Note : The below steps are very sensitive; not run by normal users. It is recommended to run with the help of Oracle DBA prior taking backups. If any problem happens the old data might be lost.
1. Confirm and note down the existing Data file location for both CDS_TEMP_TS and CDS_TS by running the SQL commands.
Ex : current Data file locations are
- C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TEMP_TS.DBF
- C:\ORACLE\PRODUCT\10.2.0\DB\DATABASE\CDS_TS.DBF
- SQL > shutdown immediate;
Ex : For new location of Data files.
- C:\DCD_DB\CDS_TEMP_TS.DBF
- C:\DCD_DB\CDS_TS.DBF
slplus sys as sysdba; Provide password and connect to idle instance.
5.start the Oracle and mount it with the below command
- SQL> startup mount;ORACLE instance started.Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
6. Now run the below commands at SQL> to associate the new data file location with database. - alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TEMP_TS.DBF' to 'C:\DCD_DB\CDS_TEMP_TS.DBF';
- alter database rename file 'C:\oracle\product\10.2.0\db\database\CDS_TS.DBF' to 'C:\DCD_DB\CDS_TS.DBF';
- alter database open;
- sqlplus "sys/oracle@orcl as sysdba"
- SQL> select tablespace_name,file_name from dba_temp_files where tablespace_name LIKE 'CDS%';
- SQL> select tablespace_name,file_name from dba_data_files where tablespace_name LIKE 'CDS%';
Where are Oracle logs Reflecting this ?
SQL> show parameter dump;
NAME TYPE VALUE
----------------------------------
background_core_dump string partial
background_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\BDUMP
core_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\CDUMP
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP
SQL>
C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\bdump
check the " alert_orcl.log " which shows the commands and details used to create CDS_TEMP_TS and CDS_TS.
Reference : http://www.orafaq.com/wiki/Move_datafile_to_different_location
This post main aim is to redirect the SQL select query output to a file for example to make a simple HTML file to display the select query output.
In this example I created two sample files one is 1.sh and another one is select.sh
File 1:
bash-3.00# cat 1.sh
sqlplus PV_ADMIN/PV@PV<< EOF
@select.sql
exit
EOF
sqlplus PV_ADMIN/PV@PV<< EOF
@select.sql
exit
EOF
File 2 :
bash-3.00# cat select.sql
set linesize 200
set pages 1000
set feedback off
set markup html on spool on
spool test.html
select * from PV_REPORT.AGINFO;
spool off;
set linesize 200
set pages 1000
set feedback off
set markup html on spool on
spool test.html
select * from PV_REPORT.AGINFO;
spool off;
so when I run the 1.sh it simply calls select.sh to spool the output to a test.html which will contain the select of the required SQL query.
Another simple file
bash-3.00# cat selctftp.sh
#!/bin/sh
#
# This script will make connection to Proviso DB
# and selects the data required from a particular
# table to populate it in a file and uploads to
# your target server
#
#
NOW=`(date +"%b-%d-%y")`
echo $NOW
echo "$0 Running on $NOW"
LOGFILE="log-$NOW.log"
echo $LOGFILE
su - oracle <
id
sqlplus PV_ADMIN/PV@PV
set wrap off
spool $LOGFILE
select * from PV_REPORT.AGINFO;
sppol off
quit
kk
bash-3.00# cat selctftp.sh
#!/bin/sh
#
# This script will make connection to Proviso DB
# and selects the data required from a particular
# table to populate it in a file and uploads to
# your target server
#
#
NOW=`(date +"%b-%d-%y")`
echo $NOW
echo "$0 Running on $NOW"
LOGFILE="log-$NOW.log"
echo $LOGFILE
su - oracle <
id
sqlplus PV_ADMIN/PV@PV
set wrap off
spool $LOGFILE
select * from PV_REPORT.AGINFO;
sppol off
quit
kk
DB2 database back up and restore commands & using db2cc tool UNIX and windows
This post briefs the steps to take back up of a database in DB2.
There are mutiple ways to take database back up in DB2. I felt the easiest way of taking back up for a database is using the DB2CC tool (DB2 control center tool). There the process or steps to take back up or restore a particular DB is pretty straight and easy.
Yet another simple process to take back up is commands in the command line.
To take back up :
db2inst1@nc145016:/opt> db2 BACKUP DATABASE CDSDB TO "/opt/BKP"
Backup successful. The timestamp for this backup image is : 20100709051851
However you can see the command generated during the back up taking process using db2cc tool.
It looks some thing like below ...
-- The following commands may not run as expected in a multi database partition environment if executed from a single script.
-- Quiesce Database
-- Run on any database partition.
CONNECT TO CDSDB;
QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;
CONNECT RESET;
-- Backup Database Partition Grouping 1
-- Run on database partition(s): 0
BACKUP DATABASE CDSDB TO "/opt/BKP" WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
-- Unquiesce Database
-- Run on any database partition.
CONNECT TO CDSDB;
UNQUIESCE DATABASE;
CONNECT RESET;
To restore a database :
A simple command looks like below to restore a particular database.
db2inst1@nc145016:/opt/BKP> db2 RESTORE DATABASE CDSDB FROM "/opt/BKP" TAKEN AT 20100708081818
DB20000I The RESTORE DATABASE command completed successfully.
And where as the db2cc generated SQL query may look like some thing below
RESTORE DATABASE CDSDB FROM "/opt/BKP" TAKEN AT 20100708081818 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 WITHOUT PROMPTING;
Hope this helps .. any related information to db2 database back up / restore process can be posted to this in comments.
Here is a sample SQL queries to compare the usage of "NOT IN" in Oracle and DB2..
Usually to filter few rows from the result of one SELECT query we combine another SELECT query with NOT IN phrase.
Example : 1 in Oracle
SELECT * FROM emp WHERE rownum=1 AND rowid NOT IN(SELECT rowid FROM emp WHERE rownum < 10);
Example 2 : in DB2
SELECT FD.SERVER_NAME FROM CDSSCHEMA.DEPOT_SERVER FD where FD.SERVER_NAME NOT IN (SELECT DS.SERVER_NAME FROM CDSSCHEMA.TARGETLIST_MAP TM, CDSSCHEMA.TARGETLIST T, CDSSCHEMA.DEPOT_SERVER DS, CDSSCHEMA.TARGET_SERVER TS WHERE TM.PACKAGE_ID='1278328705570' AND TM.TARGETLIST_ID=T.TARGETLIST_ID AND DS.SERVER_ID=TS.SERVER_ID AND TS.TARGETLIST_ID=T.TARGETLIST_ID ORDER BY DS.SERVER_NAME)
The above query return the list of depot server names which are not in the second select queury.
JAVA sample code to connect to DB2 Database using Type 4 Driver
The below sample code shows the simple way of making connection to a DB2 Server Database exists on a remote machine with out installing any db2 client on your machine. But all we need to have is Type 4 drivers to make this connection which we can use by pointing to the db2jcc.jar and db2jcc_license_cu.jar.
All we need to do is point the java class path to find this jars, in case of eclipse users configure the build path and these 2 jars as external jars from the path they located to the project. change the Host name, port, user name and password like details in the below program and run accordingly.
Syntax for the connection string :
DriverManager.getConnection
("jdbc:db2j:net://<HOSTNAME>:<Port>/<Database name>,"<User name >","<Password> ");
Sample connection string :
DriverManager.getConnection
("jdbc:db2j:net://nc184120.tivlab.austin.ibm.com:50001/CDSDB","db2inst1","db2inst1");
The following is the sample code....
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Java2DB2 {
public static void main(String[] args)
{
try
{
// load the DB2 Driver
Class.forName("com.ibm.db2.jcc.DB2Driver");
// establish a connection to DB2
Connection db2Conn =
DriverManager.getConnection
("jdbc:db2j:net://nc184120.tivlab.austin.ibm.com:50001/CDSDB","db2inst1","db2inst1");
// use a statement to gather data from the database
Statement st = db2Conn.createStatement();
String myQuery = "SELECT * FROM CDSSCHEMA.ACL_ADMIN";
// execute the query
ResultSet resultSet = st.executeQuery(myQuery);
// cycle through the resulSet and display what was grabbed
while (resultSet.next())
{
String name = resultSet.getString("USER_NAME");
String id = resultSet.getString("USER_ID");
System.out.println("User Name: " + name);
System.out.println("User ID: " + id);
System.out.println("-------------------------------");
}
// clean up resources
resultSet.close();
st.close();
db2Conn.close();
}
catch (ClassNotFoundException cnfe)
{
cnfe.printStackTrace();
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
}
}
Happy DB2 connectivity :)
Reference : [1]
Some time we end up in uninstalling the problematic DB2 installation when some thing went for toss
Here are the simple steps to clean and UN-installation. Try as root and before that db2stop force command will stop all running instances.
Go to the DB2 installed location /opt/ibm/db2/V9.5/instance
Once you finished un-installation steps don't forget to delete the db2 related users and groups and their home directories
ex :
userdel -r db2inst1
userdel -r db2fenc1
userdel -r dasuser etc
Here are the simple steps to clean and UN-installation. Try as root and before that db2stop force command will stop all running instances.
Go to the DB2 installed location /opt/ibm/db2/V9.5/instance
[/opt/ibm/db2/V9.5/instance] >> ./db2idrop db2inst1
DBI1070I Program db2idrop completed successfully.
NC142132 [/opt/ibm/db2/V9.5/instance] >> ./dasdrop
SQL4410W The DB2 Administration Server is not active.
DBI1070I Program dasdrop completed successfully.
NC142132 [/opt/ibm/db2/V9.5/install] >> ./db2_deinstall -a
Once you finished un-installation steps don't forget to delete the db2 related users and groups and their home directories
ex :
userdel -r db2inst1
userdel -r db2fenc1
userdel -r dasuser etc
SQL1428N The application is already attached to "DB2INST1" while the command issued requires an attachment to "LHOST0" for successful execution
Have you ever faced this issue while running drop database ???
SQL1428N The application is already attached to "DB2INST1" while the commanddb2 => drop database IBMCDB
issued requires an attachment to "LHOST0" for successful execution
SQL1428N The application is already attached to "DB2INST1" while the command
issued requires an attachment to "LHOST0" for successful execution.
db2 => list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = IBMCDB
Database name = IBMCDB0
Node name = LHOST0
Database release level = c.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =
db2 => drop database IBMCDB
SQL1428N The application is already attached to "DB2INST1" while the command
issued requires an attachment to "LHOST0" for successful execution.
db2 => uncatalog db IBMCDBHope this solved your issue :))
DB20000I The UNCATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is
refreshed.
db2 => list db directory
SQL1057W The system database directory is empty. SQLSTATE=01606
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 8 00:41:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_1/dbs/initdb_2orcl.ora'
If you are facing the above error while startup of oracle , possible reasons might be the init{ORA_SID}.ora file not accessible by oracle user which may be due to the init{ORA_SID}.ora file is not located in the default locations or it is not having the proper file permissions to read by oracle user.
In the abobe error messages the ORACLE_SID is db_2orcl, so the name of the init{ORA_SID}.ora file has become initdb_2orcl.ora. So the file might not be available in the default location.
Possible locations to locate init.ora file are as follows
$ORACLE_BASE/admin/$ORACLE_SID/pfile folder
[ or ]
$ORACLE_HOME/dbs folder
Check the name and location of the init-ora file. Then when starting the database specify explicity the location of the initialization file, i.e.
Startup pfile='<full-pathname-of-initialisation-file>'
Example :
SQL> startup pfile='/home/oracle/oracle/product/10.2.0/db_1/admin/db_2orcl/pfile/init.ora.8220089359'
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 62916876 bytes
Database Buffers 96468992 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select count(*) from tab;
COUNT(*)
----------
3663
SQL> quit
Reference : [1][2][3]
Oracle Basic FAQs
Technorati Tags: LRM-00109:, ORA-01078:
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 8 00:41:36 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_1/dbs/initdb_2orcl.ora'
If you are facing the above error while startup of oracle , possible reasons might be the init{ORA_SID}.ora file not accessible by oracle user which may be due to the init{ORA_SID}.ora file is not located in the default locations or it is not having the proper file permissions to read by oracle user.
In the abobe error messages the ORACLE_SID is db_2orcl, so the name of the init{ORA_SID}.ora file has become initdb_2orcl.ora. So the file might not be available in the default location.
Possible locations to locate init.ora file are as follows
$ORACLE_BASE/admin/$ORACLE_SID/pfile folder
[ or ]
$ORACLE_HOME/dbs folder
Check the name and location of the init-ora file. Then when starting the database specify explicity the location of the initialization file, i.e.
Startup pfile='<full-pathname-of-initialisation-file>'
Example :
SQL> startup pfile='/home/oracle/oracle/product/10.2.0/db_1/admin/db_2orcl/pfile/init.ora.8220089359'
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218292 bytes
Variable Size 62916876 bytes
Database Buffers 96468992 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> select count(*) from tab;
COUNT(*)
----------
3663
SQL> quit
Reference : [1][2][3]
Oracle Basic FAQs
Technorati Tags: LRM-00109:, ORA-01078:
Oracle RAC
Oracle RAC stands for Oracle Real Application clusters.
Why do we need RAC ? With Oracle RAC we can enable Single Database to run across cluster of servers. If one fails the other will be able to manage the user requests.It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Even if the service fails on one system(node) the database services will be available from other remaining nodes. In a non RAC Database the services will be available only on one system, so if the system fails the DB services will be down (single point of failures).
What is cluster ?
A cluster consists of two or more computers working together to provide
a higher level of availability, reliability, and scalability than can
be obtained by using a single computer.
Hardware requirements for Oracle 11gR2 RAC?
* A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
* A shared disk subsystem.
two CPUs, 1GB RAM, two Gigabit Ethernet NICs, a dual channel SCSI host bus adapter (HBA), and eight SCSI disks connected via copper to each host (four disks per channel). The disks were configured as Just a Bunch Of Disks (JBOD)—that is, with no hardware RAID controller.
Ex :
1 cpu
2 GB memory
18 GB local disk with OS
10 GB local disk for Oracle binaries
3 x 10 GB shared disks for RAC
Software Requiremnts for Oracle 10 g RAC ?
1. An operating system
2. Oracle Clusterware
3. Oracle RAC software
4. An Oracle Automatic Storage Management (ASM) instance (optional).
What are the supported operating systems and platforms for Oracle RAC ?
* Windows Clusters
* Linux Clusters
* Unix Clusters like SUN PDB (Parallel DB).
* IBM z/OS in SYSPLEX
* HP Serviceguard extension for RAC
Many other platforms and operating systems are supported refer documentation of product.
References :
http://download.oracle.com/docs/cd/B28359_01/install.111/b28264/toc.htm
http://www.oracle.com/technology/software/products/database/index.html
http://www.oracle.com/technology/pub/articles/smiley_rac10g_install.html
http://www.oracle-base.com/articles/11g/OracleDB11gR1RACInstallationOnLinuxUsingNFS.php
http://www.oracle.com/database/rac_home.html
http://www.oracle.com/technology/products/database/clustering/index.html
http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/
http://www.orafaq.com/wiki/RAC_FAQ
Why do we need RAC ? With Oracle RAC we can enable Single Database to run across cluster of servers. If one fails the other will be able to manage the user requests.It allows multiple nodes in a clustered system to mount and open a single database that resides on shared disk storage. Even if the service fails on one system(node) the database services will be available from other remaining nodes. In a non RAC Database the services will be available only on one system, so if the system fails the DB services will be down (single point of failures).
What is cluster ?
A cluster consists of two or more computers working together to provide
a higher level of availability, reliability, and scalability than can
be obtained by using a single computer.
Hardware requirements for Oracle 11gR2 RAC?
* A dedicated network interconnect - might be as simple as a fast network connection between nodes; and
* A shared disk subsystem.
two CPUs, 1GB RAM, two Gigabit Ethernet NICs, a dual channel SCSI host bus adapter (HBA), and eight SCSI disks connected via copper to each host (four disks per channel). The disks were configured as Just a Bunch Of Disks (JBOD)—that is, with no hardware RAID controller.
Ex :
1 cpu
2 GB memory
18 GB local disk with OS
10 GB local disk for Oracle binaries
3 x 10 GB shared disks for RAC
Software Requiremnts for Oracle 10 g RAC ?
1. An operating system
2. Oracle Clusterware
3. Oracle RAC software
4. An Oracle Automatic Storage Management (ASM) instance (optional).
What are the supported operating systems and platforms for Oracle RAC ?
* Windows Clusters
* Linux Clusters
* Unix Clusters like SUN PDB (Parallel DB).
* IBM z/OS in SYSPLEX
* HP Serviceguard extension for RAC
Many other platforms and operating systems are supported refer documentation of product.
References :
http://download.oracle.com/docs/cd/B28359_01/install.111/b28264/toc.htm
http://www.oracle.com/technology/software/products/database/index.html
http://www.oracle.com/technology/pub/articles/smiley_rac10g_install.html
http://www.oracle-base.com/articles/11g/OracleDB11gR1RACInstallationOnLinuxUsingNFS.php
http://www.oracle.com/database/rac_home.html
http://www.oracle.com/technology/products/database/clustering/index.html
http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/
http://www.orafaq.com/wiki/RAC_FAQ
It is damn! easy to see the running oracle services/processes status. But the same when comes to Windows its some thing different and tricky due to architectural differences.
we can see pmon,smon ..processes in UNIX as follows
ps -eaf | grep ora | grep -v grep | grep -v LOCAL
so obvious question that is there any way to see same kind of out put in windows command prompt too ...
Answer : In a windows environment for oracle, each detached precess runs as a concurrently running thread with single executable called Oracle.exe (Depends on version of oracle this exe file name vary slightly) . Using this executable with several threads , the threads all share the same code, memory space and other structures.
Now go to Task manager (use ctl + alt + del ) and go to processes tab.And in the list of processes also you will find only the main Oracle process but not the threads separately.
So in windows individual thread names (pmon ,smon etc) of oracle process ar not visible neither from Task manager nor from Services window.
We can find some information about them in the alert log when ever the database started.
snippet from the log looks like the below
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
How to see this log :
go to run -> cmd ->
type alert_db10.log
Note : Assuming DB installed is oracle 10g.
PATH: %oracle_home%\network\log
Name vary accordingly.
We can also obtain the list threads and their assignment through SQL*Plus via the following query
select b.name bkpr , s.Username spid p.Pid from V$BGPROCESS b, V$SESSION s, V$PROCESS p, where p.Addr = b.Paddr(+) and p.Addr = s.Paddr
Note : The listener and dispatcher threads do not show up in this list.
In this way we can identify which process is associated with each thread.
Reference : Processes Vs Threads
DB2 FAQ’s
How to start db2 instance ?As an instance owner on the host running db2, issue the following command, means login as db2 user and run the profile once from /home/db2inst1/sqllib/
$ source db2profile
$ db2start
How to stop the instance?
$ db2stop
Connect to the database as instance owner
How to stop forcefully
$ db2stop force
How to create a new instance in the DB2 ?
Make sure that you have already required groups and users exists on your db2 machine.
For ex on a AIX machine ...
bash-3.2# cat /etc/passwd | grep db2fenc1
db2fenc1:!:210:103::/home/db2fenc1:/usr/bin/ksh
bash-3.2# cat /etc/group | grep db2
staff:!:1:ipsec,esaadmin,sshd,discover,neemanga,db2fenc1
db2iadm1:!:102:
db2fadm1:!:103:db2fenc1
db2inst1:!:104:
To create proper db2inst1 user for owning the instance db2inst1 - >
useradd -d /home/db2inst1 -s /usr/bin/ksh -g db2iadm1 -m db2inst1
To change the password for db2inst1 user
passwd db2inst1
To create the new instance by name db2inst1
/opt/IBM/db2/V10/instance/db2icrt -u db2fenc1 db2inst1
How to list the existing Databases in the Db2 ?
System Database Directory
Number of entries in the directory = 2
Database 1 entry:
....
How to create a new db2instance?
Initially you might need to create required users like
db1inst1 , db2fenc1 etc
then you have to run the below command
db2]# ./instance/db2icrt -u db2fenc1 db2inst1
DBI1446I The db2icrt command is running, please wait.
DB2 installation is being initialized.
.....goes on.
How to set TCP IP communication for any instance ?
After running db2profile
run the command
db2set DB2COMM
And then run
sqllib]$ db2 update dbm cfg using SVCENAME db2c_db2inst1
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
Once it is successfully run , stop and start db2instance.
check
db2 get dbm cfg | grep SVCENAME
How to see the current connected Database in DB2 ?
db2 => SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY11
------------------
CDSDB
1 record(s) selected.
How to know the existing DB2 Version ?
db2inst1@nc041031:~/sqllib/bin> db2levelHow to see the list of existing instances in DB2 ?
Db2ilist
How to check the DB2 used ports after the installation ?
nc145016:/etc # netstat -an | grep 50001
Insert command in DB2 ?
insert into CDSSCHEMA.PLAN_DESCRIPTION (PLAN_ID,PACKAGE_ID) values ('34245324','1270803791784')How to check the DB2 used ports after the installation ?
tcp 0 0 0.0.0.0:50001 0.0.0.0:* LISTEN
nc145016:/etc # cat services | grep db2
ibm-db2 523/tcp # IBM-DB2
ibm-db2 523/udp # IBM-DB2
questdb2-lnchr 5677/tcp # Quest Central DB2 Launchr
questdb2-lnchr 5677/udp # Quest Central DB2 Launchr
DB2_db2inst1 60000/tcp
DB2_db2inst1_1 60001/tcp
DB2_db2inst1_2 60002/tcp
DB2_db2inst1_END 60003/tcp
db2c_db2inst1 50001/tcp
nc145016:/etc #
How to run db2 sql file at the prompt ?
db2 -tvf path/cds_db2_admin.sqlDrop / delete a table :
ex :
DROP TABLE TESTUSER.EMPLOYEE
Testuser is schema name
Employee is table name
Drop a database: For this DB should be started before executing.
Db2 drop database sample
The following SQL statement drops the table space ACCOUNTING:
DROP TABLESPACE ACCOUNTING
$ db2
as a user of the database:
$source ~instance/sqllib/db2cshrc (csh users)
$ . ~instance/sqllib/db2profile (sh users)
$ db2 connect to databasename
Create a table
$ db2-> create table employee
(ID SMALLINT NOT NULL,
NAME VARCHAR(9),
DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
HIREDATE DATE,
SALARY DECIMAL(7,2),
COMM DECIMAL(7,2),
PRIMARY KEY (ID),
CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) > 1986 OR SALARY > 40500) )
A simple version:
db2-> create table employee ( Empno smallint, Name varchar(30))
Create a schema
If a user has SYSADM or DBADM authority, then the user can create a schema with any valid name. When a database is created, IMPLICIT_SCHEMA authority is granted to PUBLIC (that is, to all users). The following example creates a schema for an individual user with the authorization ID 'joe'
CREATE SCHEMA joeschma AUTHORIZATION joe
Create an alias
The following SQL statement creates an alias WORKERS for the EMPLOYEE table:
CREATE ALIAS WORKERS FOR EMPLOYEE
You do not require special authority to create an alias, unless the alias is in a schema other than the one owned by your current authorization ID, in which case DBADM authority is required.
Create an Index:
The physical storage of rows in a base table is not ordered. When a row is inserted, it is placed in the most convenient storage location that can accommodate it. When searching for rows of a table that meet a particular selection condition and the table has no indexes, the entire table is scanned. An index optimizes data retrieval without performing a lengthy sequential search. The following SQL statement creates a
non-unique index called LNAME from the LASTNAME column on the EMPLOYEE table, sorted in ascending order:
CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)
The following SQL statement creates a unique index on the phone number column:
CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)
Alter tablespace
Adding a Container to a DMS Table Space You can increase the size of a DMS table space (that is, one created with the MANAGED BY DATABASE clause) by adding one or more containers to the table
space. The following example illustrates how to add two new device containers (each with 40 000 pages) to a table space on a UNIX-based system:
ALTER TABLESPACE RESOURCE
ADD (DEVICE '/dev/rhd9' 10000,
DEVICE '/dev/rhd10' 10000)
You can reuse the containers in an empty table space by dropping the table space but you must COMMIT the DROP TABLESPACE command, or have had AUTOCOMMIT on, before attempting to reuse the containers. The following SQL statement creates a new temporary table space called TEMPSPACE2:
CREATE TEMPORARY TABLESPACE TEMPSPACE2 MANAGED BY SYSTEM USING ('d')
Once TEMPSPACE2 is created, you can then drop the original temporary table space TEMPSPACE1 with the command: DROP TABLESPACE TEMPSPACE1
Add Columns to an Existing Table
When a new column is added to an existing table, only the table description in the system catalog is modified, so access time to the table is not affected immediately. Existing records are not physically altered
until they are modified using an UPDATE statement. When retrieving an existing row from the table, a null or default value is provided for the new column, depending on how the new column was defined. Columns that are added after a table is created cannot be defined as NOT NULL: they must be defined as either NOT NULL WITH DEFAULT or as nullable. Columns can be added with an SQL statement. The following statement uses the ALTER TABLE statement to add three columns to the EMPLOYEE table:
ALTER TABLE EMPLOYEE
ADD MIDINIT CHAR(1) NOT NULL WITH DEFAULT
ADD HIREDATE DATE
ADD WORKDEPT CHAR(3)
GrantPermissions by Users
The following example grants SELECT privileges on the EMPLOYEE table to the user HERON:
GRANT SELECT ON EMPLOYEE TO USER HERON
The following example grants SELECT privileges on the EMPLOYEE table to the group HERON:
GRANT SELECT ON EMPLOYEE TO GROUP HERON
GRANT SELECT,UPDATE ON TABLE STAFF TO GROUP PERSONNL
If a privilege has been granted to both a user and a group with the same name, you must specify the GROUP or USER keyword when revoking the privilege. The following example revokes the SELECT privilege on the EMPLOYEE table from the user HERON:
REVOKE SELECT ON EMPLOYEE FROM USER HERON
To Check what permissions you have within the database
SELECT * FROM SYSCAT.DBAUTH WHERE GRANTEE = USER AND GRANTEETYPE = 'U'
SELECT * FROM SYSCAT.COLAUTH WHERE GRANTOR = USER
At a minimum, you should consider restricting access to the SYSCAT.DBAUTH, SYSCAT.TABAUTH, SYSCAT.PACKAGEAUTH, SYSCAT.INDEXAUTH, SYSCAT.COLAUTH, and SYSCAT.SCHEMAAUTH catalog views. This would prevent information on user privileges, which could be used to target an authorization name for break-in, becoming available to everyone with access to the database. The following statement makes the view available to every authorization name:
GRANT SELECT ON TABLE MYSELECTS TO PUBLIC
And finally, remember to revoke SELECT privilege on the base table:
REVOKE SELECT ON TABLE SYSCAT.TABAUTH FROM PUBLIC
Delete Records from a table
db2-> delete from employee where empno = '001'
db2-> delete from employee
The first example will delete only the records with emplno field = 001 The second example deletes all the records
Import Command
Requires one of the following options: sysadm, dbadm, control privileges on each participating table or view, insert or select privilege, example:
db2->import from testfile of del insert into workemployee
where testfile contains the following information 1090,Emp1086,96613.57,55,Secretary,8,1983-8-14
or your alternative is from the command line:
db2 " import from 'testfile' of del insert into workemployee"
db2 <>
db2 import from test file of del insert into workemployee
Load Command:
Requires the following auithority: sysadm, dbadm, or load authority on the database:
example: db2 "load from 'testfile' of del insert into workemployee"
You may have to specify the full path of testfile in single quotes
Authorization Level:
One of the following:
sysadm
dbadm
load authority on the database and
INSERT privilege on the table when the load utility is invoked in INSERT mode, TERMINATE mode
(to terminate a previous load insert operation), or RESTART mode (to restart a previous load insert
operation)
INSERT and DELETE privilege on the table when the load utility is invoked in REPLACE mode,
TERMINATE mode (to terminate a previous load replace operation), or RESTART mode (to restart a
previous load replace operation)
INSERT privilege on the exception table, if such a table is used as part of the load operation.
Caveat:
If you are performing a load operation and you CTRL-C out of it, the tablespace is left in a load pending state. The only way to get out of it is to reload the data with a terminate statement
First to view tablestate:
Db2 list tablespaces show detail will display the tablespace is in a load pending state.
Db2tbst
Here is the original query
Db2 "load from '/usr/seela/a.del' of del insert into A";
If you break out of the load illegally (ctrl-c), the tablespace is left load pending.
To correct:
Db2 "load form '/usr/seela/a.del' of del terminate into A";
This will return the table to it's original state and roll back the entries that you started loading.
If you try to reset the tablespace with quiesce, it will not work . It's an integrety issue
DB2BATCH- command
Reads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements and returns an answer set: Authorization: sysadmin .and Required Connection -None..eg
db2batch -d databasename -f filename -a userid/passwd -r outfile
DB2expln - DB2 SQL Explain Tool
Describes the access plan selection for static SQL statements in packages that are stored in the DB2 common server systems catalog. Given the database name, package name ,package creator abd section
number the tool interprets and describes the information in these catalogs.
DB2exfmt - Explain Table Format Tool
DB2icrt - Create an instance
DB2idrop - Dropan instance
DB2ilist - List instances
DB2imigr - Migrate instances
DB2iupdt - Update instances
Db2licm - Installs licenses file for product ;
db2licm -a db2entr.lic
DB2look - DB2 Statistics Extraction Tool
Generates the updates statements required to make the catalog statistics of a test database match those of a production. It is advantageous to have a test system contain asubset of your production system's data.
This tool queries the system catalogs of a database and outputs a tablespace n table index, and column information about each table in that database Authorization: Select privelege on system catalogs Required
Connection - None. Syntax
db2look -d databasename -u creator -t Tname -s -g -a -p -o
Fname -e -m -c -r -h
where -s : generate a postscript file, -g a graph , -a for all users in the database, -t limits output to a particular tablename, -p plain text format , -m runs program in mimic mode, examples:
db2look -d db2res -o output will write stats for tables created in db
db2res in latex format
db2look -p -a -d db2res -o output - will write stats in plain text format
DB2 -list tablespaces show detail
displays the following information as an example:
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 2925
Useable pages = 2925
Used pages = 2925
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
db2tbst - Get tablespace state.
Authorization - none , Required connection none, syntax db2tbst tabpespace-state:The state value is part of the output of list tablespaces example
db2tbst 0X0000 returns state normal
db2tbst 2 where 2 indicates tablespace id 2 will also work
DB2dbdft - environment variable
Defining this environment variable with the database you want to connect to automatically connects you to the database . example setenv db2dbdft sample will allow you to connect to sample by default.
CLP - Command Line Processor Invocation:
db2 starts the command line processor. The clp is used to execute database utilities, sql statements and online help. It offers a variety of command options and can be started in :
1. interactive mode : db2->
2. command mode where each command is prefixed by db2
3. batch mode which uses the -f file input option
Update the configuration in the database :
Db2 =>update db cfg for sample using maxappls 60
MAXFILOP = 64 2 - 9150
db2 => update db cfg for sample using maxappls 160
db2 => update db cfg for sample using AVG_APPLS 4
db2 =>update db cfg for sample using MAXFILOP 256
can see updated parameters from client
tcpip ..... not started up properly Check the DB2COMM variable if it it is set
db2set DB2COMM
How to terminate the database if processes are still attached:
db2 force applications all
db2stop
db2start
db2 connect to dbname (locally)
How to trace logs withing the db2diag.log file:
Connections to db fails:
Move the db2diag.log from the sqllib/db2dump directory to some other working directory ( mv db2diag.log
db2 update dbm cfg using diaglevel 4
db2stop
db2start
db2trc on -l 8000000 -e 10
db2 connect to dbname (locally)
db2trc dump 01876.trc
db2trc flw 01876.trc 01876.flw
db2trc fmt 01876.trc 01876.fmt
db2trc off
Import data from ascii file to database
db2 " import from inp.data of del insert into test"
db2 "load from '/cs/home/tech1/seela/inp.data' of del insert into seela.seela"
db2 <>
Revoke permissions from the database from public:
db2 => create database GO3421
DB20000I The CREATE DATABASE command completed successfully.
Now I want to revoke connect, createtab bindadd on database from public
On server: db2 => revoke connect , createtab, bindadd on database from public
Now on client, as techstu, I tried to connect to go3421
db2 => connect to go3421
SQL1060N User "TECHSTU " does not have the CONNECT privilege. SQLSTATE=08004
Now I have to grant connect privilege to group ugrad
On server:
db2 => grant connect, createtab on database to group ugrad
DB20000I The SQL command completed successfully.
Tested on client I can connect successfully.
Now on the client, I can connect as a student, list tables but not select. I
can still describe tables
To prevent this:
On server
revoke select on table syscat.columns from public
Now on client, I cannot describe but also on my tables.
db2 => revoke select on table syscat.columns from public
DB20000I The SQL command completed successfully.
db2 => grant select on table syscat.columns to group ugrad
On server:
db2 => revoke select on table syscat.indexes from public
DB20000I The SQL command completed successfully.
select * from syscat.dbauth will display all the privileges for
dbadm authority:
DBADMAUTH CREATETABAUTH BINDADDAUTH CONNECTAUTH
NOFENCEAUTH IMPLSCHEMAAUTH LOAD AUTH
select
TABNAME,DELETEAUTH,INSERTAUTH,SELECTAUTH from
syscat.tabauth
grant connect, createtab
grant connect, createtab on database to user techstu
to group ugrad
Instance Level Authority
db2 get dbm cfg
db2 get admin cfg
db2 get db cfg
CLP using filename on the command line
Db2 -f filename.clp
The -f option directs the clp to accept input from file.
Db2 +c -v +t infile .. The option can be prefixed by a + sign or turned on by a letter with a -sign
+c is turned off, -v turned on and -f turned on
c is for commit, v for verbose and f for filename
-t termination character is set to semicolon
How to count the number of tables in a schema ?
1. db2 SELECT COUNT(*) FROM syscat.tables WHERE tabschema = 'CDSSCHEMA' AND type = 'T'
2. db2 LIST TABLES FOR SCHEMA CDSSCHEMA
In both the queries above don't forget to give the schema name a CAPS.
How to apply the license in DB2 ?
db2inst1@nc184158:/opt/ibm/db2> lsV9.5
db2inst1@nc184158:/opt/ibm/db2> /home/db2inst1/sqllib/adm/db2licm -a /opt/builds/WAS61DB295LX32bit/DB2/server/db2/license/db2ese_t.lic
LIC1402I License added successfully.
LIC1426I This product is now licensed for use as outlined in your License Agreement. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.5/license/en_US.iso88591"
db2inst1@nc184158:/opt/ibm/db2>
How to know the port used by the current running instance of DB2 ?
db2 get dbm cfg | grep SVCENAME | cut -d= -f2 | awk '{print $1}'Now the ouput will be one port Ex : 50000
Now grep for 50000 on /etc/services
How to start mysql in windows ?
C:\mysql-5.0.16-win32\bin\mysqld.exe
C:\mysql-5.0.16-win32\bin\mysql -u root
How to log in to mysql ?
mysql -u root mysql
Then set a password (changing "NewPw", of course):
update user set Password=password('NewPw') where User='root';
flush privileges;
Creating a Database ?
create database database01;
All that really does is create a new subdirectory in your MYSQLHOME\data directory.
How to connect to DB?
use database01
How to create a new Table ?
create table table01 (field01 integer, field02 char(10));
How to see the existing tables ?
show tables;
How to see the table structure like desc in Oracle ?
show columns from table01;
How to insert data into a table ?
1)insert into table01 (field01, field02) values (1, 'first');
2)insert into table01 (field01,field02,field03,field04,field05) values
-> (2, 'second', 'another', '1999-10-23', '10:30:00');
Select commands in mysql ?
1) select * from table01;
Altering a table ?
1)alter table table01 add column field03 char(20);
2)alter table table01 add column field04 date, add column field05 time;
How to eneter a sql statement in multiple lines ?
create table table33
-> (field01
-> integer,
-> field02
-> char(30));
Updating a table data ?
update table01 set field04=19991022, field05=062218 where field01=1;
Deleting a record ?
delete from table01 where field01=3;
To come out of mysql prompt ?
quit
C:\mysql-5.0.16-win32\bin\mysqld.exe
C:\mysql-5.0.16-win32\bin\mysql -u root
How to log in to mysql ?
mysql -u root mysql
Then set a password (changing "NewPw", of course):
update user set Password=password('NewPw') where User='root';
flush privileges;
Creating a Database ?
create database database01;
All that really does is create a new subdirectory in your MYSQLHOME\data directory.
How to connect to DB?
use database01
How to create a new Table ?
create table table01 (field01 integer, field02 char(10));
How to see the existing tables ?
show tables;
How to see the table structure like desc in Oracle ?
show columns from table01;
How to insert data into a table ?
1)insert into table01 (field01, field02) values (1, 'first');
2)insert into table01 (field01,field02,field03,field04,field05) values
-> (2, 'second', 'another', '1999-10-23', '10:30:00');
Select commands in mysql ?
1) select * from table01;
Altering a table ?
1)alter table table01 add column field03 char(20);
2)alter table table01 add column field04 date, add column field05 time;
How to eneter a sql statement in multiple lines ?
create table table33
-> (field01
-> integer,
-> field02
-> char(30));
Updating a table data ?
update table01 set field04=19991022, field05=062218 where field01=1;
Deleting a record ?
delete from table01 where field01=3;
To come out of mysql prompt ?
quit
Wonderful Oracle Faqs are in the following links
SQL Commands for interviews :
- SQL Query to Find Duplicate Names in a Table
SELECT Names,COUNT(*) AS Occurrence FROM Users1 GROUP BY Names HAVING COUNT(*)>1;
- SQL to find nth highest salary
select * from(
select ename, sal, dense_rank()
over(order by sal desc)r from Employee) where r=&n;
- SQL query to find second highest salary
select *from employee where
salary=(select Max(salary) from employee);
1. Difference between Instance and Database?
The terms instance and database are closely related, but don't refer to the same thing. The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance; however, an instance can open at most one database.
2. How to connect to new database in oracle?
sqlplus username/password@connect_identifier
SQL> connect username/password@connect_identifier
To hide your password, enter the CONNECT command in the form:
SQL> connect username@connect_identifier
You will be prompted to enter your password.
In windows another ex usage :
SQL> connect sys@connect_identifier as sysdba
Enterpassword :
Connected.
3. How to create a new user in a particular database?
CREATE USER user_name IDENTIFIED BY password;
CREATE USER uwclass IDENTIFIED BY uwclass;
CREATE USER user IDENTIFIED {BY password |
EXTERNALLY}
4. How to alter a user?
ALTER USER sidney IDENTIFIED BY second_2nd_pwd DEFAULT TABLESPACE exmple;
ALTER USER sh PROFILE new_profile;
ALTER USER sh DEFAULT ROLE ALL EXCEPT dw_manager;
ALTER USER app_user1 IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';
ALTER USER sidney PASSWORD EXPIRE;
ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;
ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;
ALTER USER app_user1 REVOKE CONNECT THROUGH sh;
ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD;
5. How to see existing users in Oracle Database?
select name from sys.user$;
select username,password from dba_users;
6. How to change the existing user password in the present oracle database?
alter user myuser identified by my!supersecretpassword;
grant connect to myuser identified by my!supersecretpassword
update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
SQL*Plus command: password or password username
7. How to launch the database configuration assistant tool in Oracle?
Go to $ORACLEHOME/bin
And run the “dbca” binary.
/app/oracle/product/10.2.0/Db_1/bin/dbca
8.Oracle Versions
Oracle products have historically followed their own release-numbering and naming conventions. With the Oracle RDBMS 10g release, Oracle Corporation started standardizing all current versions of its major products using the "10g" label, although some sources continued to refer to Oracle Applications Release 11i as Oracle 11i. Major database-related products and some of their versions include:
• Oracle Application Server 10g (also known as "Oracle AS 10g"): a middleware product;
• Oracle Applications Release 11i (aka Oracle e-Business Suite, Oracle Financials or Oracle 11i): a suite of business applications;
• Oracle Developer Suite 10g (9.0.4);
• Oracle JDeveloper 10g: a Java integrated development environment;
Since version 7, Oracle's RDBMS release numbering has used the following codes:
• Oracle7: 7.0.16 — 7.3.4
• Oracle8 Database: 8.0.3 — 8.0.6
• Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
• Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
• Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
• Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5 (Latest current patchset as of December 2003)
• Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8 (Latest current patchset as of April 2007)
• Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5 (Latest current patchset as of February 2006)
• Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.3 (Latest current patchset as of November 2006)
• Oracle Database 11g Release 1: 11.1.0.6 — no patchset available as of October 2007
The version numbering syntax within each release follows the pattern: major.maintenance.application-server.component-specific.platform-specific.
For example, "10.2.0.1 for 64-bit Solaris" means: 10th major version of Oracle, maintenance level 2, Oracle Application Server (OracleAS) 0, level 1 for Solaris 64-bit.
9. How to see exixsting Oracle Version on the system ?
1)select * from v$version;
10.How do we know which version of oracle we are using ?
I need to know whether it is 32 bit Or 64 bit.
From the unix prompt enter, then enter
bash-2.05$ file oracle
a. A 32 bit oracle server will return:
oracle: ELF 32-bit MSB executable SPARC Version 1,
dynamically linked, not stripped.
b. A 64 bit oracle server will return:
oracle: ELF 64-bit MSB executable SPARCV9 Version 1,
dynamically linked, not stripped.
11.How to see the Patches applied on existing Oracle
$ORACLE_HOME/OPatch/opatch lsinventory
opatch does not list the patches applied on DB. it lists the interim patches applied on oracle binaries.
the patched applied on DB are listed with
SQL> select * from registry$history;
How to create a password policy to not to use the used password for any users?
/* when a user is assigned with above policy he cant reuse the password again */
-- Add user CDSSCHEMA. This MUST exist for Oracle schema creation.
-- CDS explicitly addresses the schema, and they way Oracle
-- names a schema is by the user name that creates it.
-- The password should be changed from the default value 'tivoli'.
The above will create a user called CDSSCHEMA and the he will be under profile krish and hence he cant re-use the same password again.
the above will through error because of the not to use used passwords policy.
/* GRANT will reset the passowrd to new one , it will change the existing password if we specify identified by is given */
Error sample
How to avoid overlapping of columns when working on SQL prompts (DOS/UNIX )?
SQL> set wrap off
How to upgrade Oracle 9i(or lower) version to 10g ?
Oracle 9i to 10g
ORcle 9i to 10g upgrade.pdf
The terms instance and database are closely related, but don't refer to the same thing. The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database. A database can be opened (or mounted) by more than one instance; however, an instance can open at most one database.
2. How to connect to new database in oracle?
sqlplus username/password@connect_identifier
SQL> connect username/password@connect_identifier
To hide your password, enter the CONNECT command in the form:
SQL> connect username@connect_identifier
You will be prompted to enter your password.
In windows another ex usage :
SQL> connect sys@connect_identifier as sysdba
Enterpassword :
Connected.
3. How to create a new user in a particular database?
CREATE USER user_name IDENTIFIED BY password;
CREATE USER uwclass IDENTIFIED BY uwclass;
CREATE USER user IDENTIFIED {BY password |
EXTERNALLY}
4. How to alter a user?
ALTER USER sidney IDENTIFIED BY second_2nd_pwd DEFAULT TABLESPACE exmple;
ALTER USER sh PROFILE new_profile;
ALTER USER sh DEFAULT ROLE ALL EXCEPT dw_manager;
ALTER USER app_user1 IDENTIFIED GLOBALLY AS 'CN=tom,O=oracle,C=US';
ALTER USER sidney PASSWORD EXPIRE;
ALTER USER sh TEMPORARY TABLESPACE tbs_grp_01;
ALTER USER app_user1 GRANT CONNECT THROUGH sh WITH ROLE warehouse_user;
ALTER USER app_user1 REVOKE CONNECT THROUGH sh;
ALTER USER sully GRANT CONNECT THROUGH OAS1 AUTHENTICATED USING PASSWORD;
5. How to see existing users in Oracle Database?
select name from sys.user$;
select username,password from dba_users;
6. How to change the existing user password in the present oracle database?
alter user myuser identified by my!supersecretpassword;
grant connect to myuser identified by my!supersecretpassword
update sys.user$ set password='F894844C34402B67' where name='SCOTT'; (restart of the database necessary)
SQL*Plus command: password or password username
7. How to launch the database configuration assistant tool in Oracle?
Go to $ORACLEHOME/bin
And run the “dbca” binary.
/app/oracle/product/10.2.0/Db_1/bin/dbca
8.Oracle Versions
Oracle products have historically followed their own release-numbering and naming conventions. With the Oracle RDBMS 10g release, Oracle Corporation started standardizing all current versions of its major products using the "10g" label, although some sources continued to refer to Oracle Applications Release 11i as Oracle 11i. Major database-related products and some of their versions include:
• Oracle Application Server 10g (also known as "Oracle AS 10g"): a middleware product;
• Oracle Applications Release 11i (aka Oracle e-Business Suite, Oracle Financials or Oracle 11i): a suite of business applications;
• Oracle Developer Suite 10g (9.0.4);
• Oracle JDeveloper 10g: a Java integrated development environment;
Since version 7, Oracle's RDBMS release numbering has used the following codes:
• Oracle7: 7.0.16 — 7.3.4
• Oracle8 Database: 8.0.3 — 8.0.6
• Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
• Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
• Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
• Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5 (Latest current patchset as of December 2003)
• Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8 (Latest current patchset as of April 2007)
• Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5 (Latest current patchset as of February 2006)
• Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.3 (Latest current patchset as of November 2006)
• Oracle Database 11g Release 1: 11.1.0.6 — no patchset available as of October 2007
The version numbering syntax within each release follows the pattern: major.maintenance.application-server.component-specific.platform-specific.
For example, "10.2.0.1 for 64-bit Solaris" means: 10th major version of Oracle, maintenance level 2, Oracle Application Server (OracleAS) 0, level 1 for Solaris 64-bit.
9. How to see exixsting Oracle Version on the system ?
1)select * from v$version;
10.How do we know which version of oracle we are using ?
I need to know whether it is 32 bit Or 64 bit.
From the unix prompt enter
bash-2.05$ file oracle
a. A 32 bit oracle server will return:
oracle: ELF 32-bit MSB executable SPARC Version 1,
dynamically linked, not stripped.
b. A 64 bit oracle server will return:
oracle: ELF 64-bit MSB executable SPARCV9 Version 1,
dynamically linked, not stripped.
11.How to see the Patches applied on existing Oracle
$ORACLE_HOME/OPatch/opatch lsinventory
opatch does not list the patches applied on DB. it lists the interim patches applied on oracle binaries.
the patched applied on DB are listed with
SQL> select * from registry$history;
CREATE PROFILE krish LIMIT
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX 10; COMMIT;
-- Add user CDSSCHEMA. This MUST exist for Oracle schema creation.
-- CDS explicitly addresses the schema, and they way Oracle
-- names a schema is by the user name that creates it.
-- The password should be changed from the default value 'tivoli'.
CREATE USER CDSSCHEMA
IDENTIFIED BY oracle
DEFAULT TABLESPACE cds_ts123
TEMPORARY TABLESPACE cds_temp_ts123
QUOTA UNLIMITED ON cds_ts123
PROFILE krish;
COMMIT;
GRANT CONNECT, RESOURCE, ALTER SESSION, CREATE SEQUENCE, CREATE SESSION, IDENTIFIED BY oracle
CREATE SYNONYM, CREATE TABLE, CREATE VIEW, UNLIMITED TABLESPACE
TO CDSSCHEMA;
COMMIT;
/* GRANT will reset the passowrd to new one , it will change the existing password if we specify identified by
Error sample
GRANT CONNECT, RESOURCE, ALTER SESSION, CREATE SEQUENCE, CREATE SESSION,
*
ERROR at line 1:
ORA-28007: the password cannot be reused
Oracle 9i to 10g
ORcle 9i to 10g upgrade.pdf
How do I execute an SQL script file in SQLPlus?
To execute a script file in SQLPlus, type @ and then the file name.
SQL > @{file}
For example, if your file was called script.sql, you'd type the following command at the SQL prompt:
SQL > @script.sql
The above command assumes that the file is in the current directory. (ie: the current directory is usually the directory that you were located in before you launched SQLPlus.)
If you need to execute a script file that is not in the current directory, you would type:
SQL > @{path}{file}
For example:
SQL > @/oracle/scripts/script.sql
This command would run a script file called script.sql that was located in the /oracle/scripts directory.
what does i stands for in oracle 8i and oracle 9i ?
i stands for internet in oracle 8i and 9i
What does g stands for in oracle 10g ?
g stands for grid technology in Oracle 10g.
from 10g onwards oracle supports grid architecture.
How to see the existing constrains applied on a table columns?
select constraint_name, constraint_type from user_constraints where table_name='';
Ex : table name : call_qr_nortel_active
select constraint_name, constraint_type from user_constraints where table_name='call_qr_nortel_active';
what is this grid computing ?
Write a typical insert command to put system date as the date column data ?
insert into call_qr_nortel_active values(sysdate,1,'cProbe:15','iProbe:30','3215551234','3215551234','192.168.2.10:',
'192.18.2.10:','11-APR-2008',12,'E1:30',999,'192.168.2.10:160','192.168.2.10:460',70,
8,41,33,3,5,999,4,33,600,999,'92.168.3.10:48160','192.168.3.10:49160',
54,23,45,33,5,23,999,6,33,5000,677,'unknown data value',
33,'16:40',4,3);
Popular Posts
-
The best solution to know about these init levels is to understand the " man init " command output on Unix. There are basically 8...
-
How to Unlock BSNL 3G data card to use it with Airtel and Vodafone Model no : LW272 ? How to unlock BSNL 3G data card( Model no : LW272 )us...
-
How to transfer bike registration from one State to other (Karnataka to Andhra)?? Most of us having two wheelers purchased and registered in...
-
Latest Post on ETV D3 Old Post on ETV D1 D2 link ETV Dhee 3 - D3 - Enjoy maadi ETV Dhee feedback link - Direct ETV site use this...
-
ఓం శ్రీ స్వామియే శరణం ఆయ్యప్ప!! Related posts : Trip to Sabarimala - Part 1 Trip to Sabarimala - Part 2 Ayappa Deeksha required things...
