TOUR_GROUP : Table has following coloumns

Below is a simple example of java method which will return true if given database contains any table by name DB_VERSION. Else it returns false.

 /*
     * isRegistryDBConfigured()    method returns true if provided Database
     * contains table DB_VERSION. Returns false if it doesn't contain
     * that table.
     */
    public boolean isRegistryDBConfigured(String dbName, String portNumber,String userName, String password) {
        try {
          String hostName = InetAddress.getLocalHost().getCanonicalHostName();
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            StringBuffer dbConnectionURL = new StringBuffer("jdbc:db2://");
            dbConnectionURL.append(hostName + ":" + portNumber + "/" + dbName);
            Connection db2Conn = DriverManager.getConnection(
                    dbConnectionURL.toString(), userName, password);
//Connection db2Conn = DriverManager.getConnection("jdbc:db2j:net://localhost:50000/frsdb","db2admin","db2admin");          
 // Connection db2Conn =  DriverManager.getConnection("jdbc:db2://localhost:50000/test","db2admin","db2admin");
            Statement st = db2Conn.createStatement();
            String myQuery = "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME ='DB_VERSION'";
            ResultSet resultSet = st.executeQuery(myQuery);
            while (resultSet.next()) {
                String name = resultSet.getString("TABNAME");
                if (name.equals("DB_VERSION")) {
                    System.out.println("Tablename:" + name);
                    return true;
                }
            }
            resultSet.close();
            st.close();
            db2Conn.close();
           
        } catch (Exception e) {
            System.out.println("EXCEPTION::" + e);
        }
        return false;
    }

Here is a simple DB2 - SQL  command to find a particular table name exists in given database name.


Example :
Database name : FRSDB
Table Name : DB_VERSION


C:\Users\ADMIN>db2 connect to frsdb

   Database Connection Information

 Database server        = DB2/NT64 10.1.0
 SQL authorization ID   = KRISHNA
 Local database alias   = FRSDB


C:\Users\ADMIN>db2 SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME ='DB_VERSION'

TABNAME
--------------------------------------------------------------------------------------------------------------------------------
DB_VERSION

  1 record(s) selected.


C:\Users\ADMIN>

 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

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.

However you can see the command generated during the back up taking process using db2cc tool. 

It looks some thing like below ...

A simple command looks like below to restore a particular database.

And where as the db2cc generated SQL query may look like some thing below 

Hope this helps .. any related information to db2 database back up / restore process can be posted to this in comments.

Reference : [1] [2] [3]

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.... 


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

[/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

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

Continue every thing below as root user in Unix
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
[db2inst1@cdsbvt1rhl bin]$ db2 list db directory

 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
db2 => SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1
1
------------------
CDSDB
  1 record(s) selected.
db2inst1@nc041031:~/sqllib/bin> db2level
How to see the list of existing instances in DB2 ?
nc145016:/etc # netstat -an | grep 50001
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 #
db2 -tvf path/cds_db2_admin.sql

Drop / 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


$ 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))


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


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.


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)


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


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.
db2inst1@nc184158:/opt/ibm/db2> ls
V9.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>
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