DB2 Nitty Gritty Stuff

To change a column name of a table:

ALTER TABLE SCHEMANAME.TABLENAME RENAME COLUMN OLDCOLUMNNAME TO NEWCOLUMNNAME;

To take a DB backup:

db2stop force

db2start

db2 CONNECT TO DATABASENAME USER username USING password

db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS

db2 CONNECT RESET

db2 BACKUP DATABASE DATABASENAME COMPRESS

Tip: SQL1035N  The operation failed because the specified database cannot be

connected to in the mode requested.  SQLSTATE=57019

If during database backup, if the above error comes up, check if any application server is accessing database or shut down any application server that can use database.


To restore a database backup:

db2> restore database DBNAME FROM D:\db_dump ON D:\ into DBNAME without prompting

D:\db_dump\DBNAME.0.DB2.DBPART000.20160610133049.001

DBNAME in bold is used in the above query

If the above command doesn't work.

1. Open Command as Administrator

2. Run the command runas /profile /user:machinename\dbadminuserid cmd

3. Enter the database password: dbadminpassword

4. Change to DB2 install location: [C:\Program Files\IBM\SQLLIB\BIN]

5. Run db2cwadmin.bat

6. Change directory to db_dump

7. Execute the above restore command.

Note: dbadminuserid must have been added in Windows user accounts [Control panel --> User Accounts]

To find database version:

db2> db2level

To verify if DB2 license is expired or not:

db2> db2licm.exe –l

To renew DB2 license, 

Open command prompt and change directory to the file location, where db2 license is stored. [db2ese_u.lic is the license file]

db2> db2licm -a db2ese_u.lic

To drop a database

db2> drop database DATABASENAME  

To list all the databases

 db2>list database directory

To drop a column from the table

db2> ALTER TABLE TABLENAME DROP COLUMNNAME

If after an alter script, the following error comes up

DB2 SQL Error: SQLCODE=-668, SQLSTATE=57016, SQLERRMC=7;xxx.xxx, DRIVER=3.69.24 [SQL State=57016, DB Errorcode=-668]

This error means access to the database is restricted.

Execute the following query to resolve the error:

db2> REORG TABLE TABLENAME

To create a new database

db2> create database NEWDATABASENAME

To disconnect from the current database

db2> disconnect DATABASENAME

To find the current database connected to the database.

db2> SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1