Db2 Roll-Forward Pending SQLSTATE=57019 Solution. How to Restore Database from online backup

Assume that already Online Backup has been done for the database test with Time stamp (T1) . Steps to take Online Backup can be viewd at For DB2 Online Backup. Recovery may be required due to data loss for various resons or records might have been deleted by mistakenly . Now to restore from the online backup image taken with the Timestamp T1, issue the following commands. First close all the applications that uses the database by issuing the following command

db2 force applications all

now issue command to restore

For Linux : db2 restore db test from /db2onlinebkp taken at T1 into test without prompting

For Windows : db2 restore db test from d:/db2onlinebkp taken at T1 into test without promptingwhere db2onlinebkp is the folder where online backup image is kept. If it is kept in the current path , then no need to give from /db2onlinebkp . If we give the command without "without prompting" , then warning will be displayed . Here both online backup image name and target database name is same (test) . Suppose online backup image name is dbbkp , then the command will be db2 restore db dbbkp from /db2onlinebkp taken at T1 into test without prompting

Now assume that database backup is restored successfully , Let us connect to the data base.

db2 connect to test .

Now the following error will be displayed

SQL1117N A connection to or activation of database "TEST" cannot be made because of ROLL-FORWARD PENDING. SQLSTATE=57019

The above error is thrown , because any transaction occurred during the online backup is written into the database transaction logs. These transactions are not available with online backup image. So when we restore from online database backup image , the activities happened during online backup are not updated. So it is mandatory to do a Roll Forward operation to a minimum point in time or end of logs before the database will be made available.

So to roll forward to the end of logs , the following command may be given

db2 rollforward db test to end of logs and stop

the follwing status are displayed.

    
                                 Rollforward Status

 Input database alias                   = test
 Number of nodes have returned status   = 1

 Node number                            = 0
 Rollforward status                     = not pending
 Next log file to be read               =
 Log files processed                    = S0000002.LOG - S0000005.LOG
 Last committed transaction             = 2011-10-10-20.28.50.000000

DB20000I  The ROLLFORWARD command completed successfully.

Note : The stop keyword at the end of rollforward command is very important that indicates the database should be made available for user connections after all the transaction logs have been applied to the database.

Read More

Select , Update , Delete N number of rows in db2

For Select : In db2 , fetching a limited number of rows is very simple. You can use  FETCH FIRST n ROWS ONLY with select query. In some applications, a select query with certain condtion or without condition may return a large number of rows, but you may need only a small subset of those rows.  For example , in a web page with ajax support , you may search a city that starts with ' N'  which  returns many numbers of rows and are filled in a combo.  Retrieving the entire result  from the table can be inefficient and also filling all the records in a combo may lead to poor response of the browser in a  client running with low memory.  So it is better to limit the result with first n number of records. Becaue it improves the performance of queries with  large result tables when you need only a limited number of rows. Also suitable for the system with less memory.   This can be done in db2 easily with  FETCH FIRST n ROWS ONLY clause  in a SELECT statement.  FETCH FIRST n ROWS ONLY, DB2 prefetches only the first  n rows.  For example , to fetch first 10 cities  that start with 'N' , the below query is used. 

                    Select cityCode , cityName from city where cityName like 'N%'  fetch first 10 rows only.

For Update :   In many applications, an update  query with  condtion or without condition may update a large number of rows  ,     but you may need to update only a small subset of those rows 

Consider the following scenerios

        1.Many viewers may send  the  right answer to the question asked in a TV programme, but they may need to update  prize amount for  the first 100 rows in the table.  Then how to  update the first 100 Rows in a table?   There is no direct query in db2 to update the first n number of records.   You can use update command with select query using FETCH first 100 ROWs ONLY.  The update query  is as follows. 

            UPDATE ( SELECT prize_amount FROM fs1  where right_answer='c'  FETCH first 100 ROWs ONLY ) SET prize_amount = 1000;

2. Suppose you are replicating  new rows or modified rows  from source table of  a db to target table in a remote db based on a condition(for eg. where replication_done='N')  through java code. One way to do is to export all the rows that matches the condition (replication_done='N')  to a text file using java code. Then the records in the text file may be imported to the table in the remote database through code. Some times (Ist time) you may need to replicate the whole table  having huge number of records (for e.g. 100000 records).  It is inefficient to replicate all records at one go. Even the import operation may  fail as the  size of the exported file becomes  too large or exceeds the limit. In this situation you can export the rows part by part (50000 + 50000 rows )  and then do the import. ( i.e. updating the first 50000 rows  with replication_done='N'   then do the export & import and updating the second  50000 rows  with replication_done='N'  then do the export & import ).   

For updating the first 50000 rows among 100000 records with replication_done = 'N' ,  use the primary key and rownumber() as given below

 UPDATE  sales  SET replication_done = 'N'   WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales order by sale_trn_id ) AS R   WHERE rn <=50000)

           sale_trn_id is the primary key. 

For updating next 50000 rows with replication_done = 'N' ,  use the below update query

UPDATE  sales  SET replication_done = 'N'   WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales order by sale_trn_id ) AS R   WHERE rn >50000)

Some other useful update queries for updating first n records using unique column OR  unique combination of  the columns of the table 

Using unique column:

       Update sales SET replication_done = 'N'  where sale_trn_id  in (select sale_trn_id  from sales   order by sale_trn_id   fetch first 50000 rows only) 

Using  unique combination of  the columns of the table:

       Update tbl_name SET expression  Where (col1, col2,..., coln) in  (select col1, col2,. coln  from tbl_name   order by col1, col2,. coln   fetch first n rows only)  

       eg.  Update salesmaster SET price = price*0.10  where ( item_code, cat_code)  in (select  item_code,cat_code  from salesmaster   order by  item_code, cat_code   fetch first 100 rows only) 

For Deletion :

     To delete the rows whose rownumber is greater than or equal to 50000 using unique key and rownumber()

          delete from  sales    WHERE sale_trn_id  IN  ( SELECT sale_trn_id   FROM    ( SELECT sale_trn_id   , ROWNUMBER() OVER() rn   FROM sales ) AS R   WHERE rn >=50000 ) 

     To delete the first 50000 rows in a table using the unique id

         delete from sales where sale_trn_id  IN (select sale_trn_id   from sales  order by sale_trn_id   fetch first 50000 rows only)

Read More

Creating Table Space in db2 using command line

Sometime table page size may exceed the default page size 4 K due to increase in the columns & column size which db2move dbname import may fail. for this you have to create table space with bigger page size.
For creating tablespace , First you have to create bufferpool with pagesize . default page size is 4 KB. You can create with the size of 8K, 16 K, 32 K with the following command. for example to create 32k size page give the following command

Linux : db2 create bufferpool testbufpool IMMEDIATE PAGESIZE 32K . This command may run on higher version like Db2 9.5 . Please check the following commad.

For Linux / Windows : db2 create bufferpool testbufpool SIZE 8000 PAGESIZE 32K - where testbufpool is the name of bufferpool.
Now give the following command to create table space .

Windows : db2 "CREATE REGULAR TABLESPACE tblspc PAGESIZE 32 K MANAGED BY SYSTEM USING ('tblspc') BUFFERPOOL testbufpool"
You can mention the path location where the table space to be created.

db2 "create tablespace tblspc pagesize 32k managed by system using ('/data/db2inst1/NODE0000/dbname/T0000002/')" bufferpool testbufpool
Now table space tblspc is created with the page size 32k

Checkout the table space info with the following commands

db2 LIST TABLESPACES SHOW DETAIL
db2 list tablespace containers for 4 - where 4 is the table space ID

Read More

How to access DB2 remote database from the client machine (Db2 Catalog)

To connect the remote database from the client machine, we have to do the following things.

1) Install the DB2 Client Software
2) Catalog Remote Node
3) Catalog Remote Database
4) Connect the Remote Database

Steps in Detail
----------------------
Catalog TCP/IP Node
You have to make an entry to the client's node directory to describe the remote node. This entry specifies the chosen alias (node_name), the hostname (or ip_address), and the servicename (or port_number) that the client will use to access the remote server

Syntax : db2 catalog tcpip node Nodename remote Hostname server service

where Nodename is the name of the node to be added in the client machine . Node name should be unique in the client machine node director, where Hostname is Ip Address or Hostname of the Remote Machine, where service is the service name or Port
to see the node list , give the following command
Command : db2 list node directory

Command: db2 catalog tcpip node testnode remote 192.188.79.129 server 50001

When running the above command the following error may come : Error : SQL1092N : does not have the authority to perform the requested command.
if the above error comes , please run the following command.
db2 UPDATE DBM CFG USING CATALOG_NOAUTH YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.

Again issue the following commands.
------------------------------------------------
db2 catalog tcpip node testnode remote 192.188.79.129 server 50001

DB20000I The CATALOG TCPIP NODE command completed successfully.

DB21056W Directory changes may not be effective until the directory cache is refreshed.

Catalog Database
----------------
Syntax : db2 catalog database dbname as aliasname at node Nodename

where dbname is the remote database name , aliasname which is to be the displayed as dbname in the client name.
Command : db2 catalog database employee as emp_s at node testnode

DB20000I The CATALOG DATABASE command completed successfully.

DB21056W Directory changes may not be effective until the directory cache is refreshed.

Now we can connect the remote database as follows.

Syntax: db2 connect to aliasname user username using password

Command: db2 connect to emp_s user db2inst1 using db2inst1

Read More

How to move db2 database across different platforms ?

Moving / Cloning the data base residing in one machine to different machine with Same Platform (Window to Windows or Linux to Linux) can be done by redirected restore operation on a full database backup image OR using backup and restore commands given below. 

1) db2 backup db dbname 
- For Source Database to be run on source machine 
db2 restore db databasename taken at timestamp into sourcedb replace existing 
- For target Database , to be run on destination machine. 

Now the problem comes when both machine are having different platforms (may be Windows to Linux or Linux to Windows, ..) , Because you can't usually back up a database on one operating system, and restore it on another operating system . For this DB2 UDB has two tools db2move & db2lookup . db2move : It is used to move the database tables across different plat forms. . db2lookup : It is used to transfer other database objects, such as constraints, triggers, indexes, sequences, table spaces, buffer pools, among others.  Using this tool, you can generate the data definition language (DDL) for such objects in the source database, and apply it to recreate those objects in the target database. 

Example for moving database from Windows machine to Linux Machine

For Example , we have to move Employee database from Windows Machine to Linux Machine 
Steps to do 
1) Create a folder EmpDB. Export the Employee database by running the command db2move

D:\EmpDB> db2move employee export
which outputs as follows

***** DB2MOVE *****

Action: EXPORT

Start time: Sat Jun 18 22:32:03 2011


Connecting to database EMPLOYEE ... successful! Server: DB2 Common Server V8.2.
0

EXPORT: 2 rows from table "TECHLAB "."EMPLOYEE"
EXPORT: 32 rows from table "SYSTOOLS"."ALTOBJ_INFO"
EXPORT: 0 rows from table "SYSTOOLS"."DB2LOOK_INFO"
EXPORT: 0 rows from table "TECHLAB "."T20110618_222010"
EXPORT: 0 rows from table "TECHLAB "."T20110618_222010_EXCEPTION"
EXPORT: 4 rows from table "TECHLAB "."DESIG"
EXPORT: 3 rows from table "TECHLAB "."QUAL"

Disconnecting from database ... successful!

End time: Sat Jun 18 22:32:05 2011

2) Generate DDL of the tables including primary key , etc..

D:\EmpDB> db2look -d employee -e -o emp.sql

which outputs 
-- USER is:
-- Creating DDL for table(s)
-- Output is sent to file: emp.sql


Now You can open the file db2move.lst file which contains a list of table names, their corresponding PC/IXF file names, and message file names. You can change this file like Schema name or remove any line having unnecessary table names. Save the fle.
Now copy all the files to linux machine . You can copy files from windows to Linux machine using WinScp utility .

After copying the files , You have to do run the following commands

1) Create new databas by issuing the following command
$ db2 create db employee

2) Run the Sql commands by running the following command
$ db2 -tvf emp.sql

3) Run the db2move command sothat all data will be imported in corresponding tables in the database. If the table does not exist , it will be automatically created.
$ db2move employee import

Read More

How to take Online Backup in DB2

Steps for Online Backup in DB2
To do an online table space and database level backup via CLP command prompt, Please follow the steps below.

In order to perform an online backup , you have to Turn on either the LOGRETAIN or USEREXIT .
Command for Turning LOGRETAIN ON :
In this example, i am using database name is as Employee

db2 => update db cfg for Employee using LOGRETAIN on

Now Shutdown and start up the database again to make the configuration change effective
db2 => terminate
db2 => force application all
Now the configuration parameter is effective, you can see LOGRETAIN = RECOVERY

To see wheather the configuration change is effctive , Give the following command

db2 => get db cfg for Employee
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

Now We need to do a full offline backup. Otherwise, an error message will be received when trying to connect to the database

db2 => connect to employee
SQL1116N A connection to or activation of database "EMPLOYEE" cannot be made
because of BACKUP PENDING. SQLSTATE=57019

To do full offline backup issue the following commands

db2 =>backup db employee to e:\onlinebkp

Now do the online backup

To do a database level online backup, issue the following command:

db2 => backup database employee online to e:\onlinebkp

Backup successful. The timestamp for this backup image is : 20110529224810

To do Table space level online backup, Give the following command

db2 => backup database employee tablespace(userspace1) online to e:\onlinebkp

Backup successful. The timestamp for this backup image is : 20110529225718

Read More

How to uninstall or remove DB2 in Linux

To uninstall or remove DB2 do the following steps,

1: Log in with root user.

2. List out all DB2 instances. To list the db2 instances, Do the following
#cd /opt/IBM/db2/V8.1/instance
the above folder will be applicable for system with Unix/ Linux based OS
# db2ilist
- which lists the all the instances of db2.

3. Drop each instance listed in the above step with the following command
#db2idrop instance name

4. Drop the DB2 administration server (DAS) with the following command
#cd /opt/IBM/db2/V8.1/instance
# dasdrop dasusr1 where dasusr1 is the dasuser name.

5. Uninstall all of the DB2 packages on your system using the db2deinstall command on your DB2 CD-ROM
Issue the following commands.
# /mnt/cdrom/db2/db2_deinstall -n

OR do the following steps to remove DB2

1) Drop all databases by using DROP command or Contro Center.

(2) Deinstall DB2 packages on your system by running db2_deinstall file from DB2 CD

(3) Go to /home directory and remove the directories dasusr1,db2fenc1,db2inst1

(4) Remove the directory db2 and its sub directories from the /opt/IBM directory

(5) Open the file /etc/services and remove the line containing ‘ db2c_db2inst1 50001/tcp ‘ and then save the file

(5) Open the file /etc/passwd and remove the lines starting with dasusr1,db2fenc1,db2inst1 and save the file. Make sure these will be three lines starting with above said user names

(6) Remove the folder DB2 from /var.

(7) Reboot the server and then reinstall the DB2 again using typical option

Read More