Adsense

Friday, April 26, 2013

How to change the date format in db2


Changing the date format in db2 can be done very easily by issuing 2 commands.
If the data base was created using territory=US , by default the date format looks like MM/DD/YYYY
You can check the format by entering the following command on db2 CLP
db2=> values current date
Output :
1
----------
06/21/2011
1 record(s) selected.

To change the date format, you have to bind the collection of db2 utility packages to use a different date format.
To change the default format to YYYY-MM-DD , which is ISO (International Standards Organization) , do the following steps:

Go to the command prompt & change directory to sqllib\bnd.
On Windows, issues the following commands
cd c:\Program Files\IBM\SQLLIB\bnd
db2 connect to database_name

->where database_name is the actual database name in your system.
db2 bind @db2ubind.lst datetime ISO blocking all grant public

After issuing the above command , the follwing message will be displayed.
LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.
LINE MESSAGES FOR db2clpnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE MESSAGES FOR db2arxnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526
LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0091N Binding was ended with "0" errors and "2" warnings.

Note : On Linux machine path will be /home/db2inst1/sqllib/bnd
Now, you can see date format of the data base which uses ISO date format by issuing the following command
Enter the following command on db2 CLP
db2=> values current date
Output :
1
----------
2011-06-21
1 record(s) selected.

The other formats supported are:
DEF - Date and time format associated with the territory code.
EUR - IBM standard for Europe date and time format
JIS - date and time format of the Japanese Industrial Standard
LOC - Use the date and time format in local form associated with the territory code of the database.
USA - IBM standard for U.S. date and time format

To change the date back to US format , run the following command
db2 bind @db2ubind.lst datetime USA blocking all grant public

How to Change transaction log path in db2


What is Log path ? Log path is a directory path in the server where the transaction log files are created. By default transaction log files get created in the same location where the database control files are stored. As large number of log files are created in the real environment , it is always good practice to store the transaction log files in a different location and also if the log files and control files are stored in the same location , then there is a chance of the file system for db2 control files become full which causes database to malfunction . To change the log path to a new location , the following steps may be used
You can use any existing directory location or you can create a new directory by the command mkdir /db2logs where db2logs is the new folder to store log fles. The new location may be in same server or in SAN . NEWLOGPATH is the database configuration parameter which needs to be updated to change the log path. To see the existing log path , you can issue the command db2 get database configuration for dbname OR db2 get db cfg for dbname. where dbname is the actual database name. The above command displays the some of the following lines .
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files = E:\DB2\NODE0000\SQL00017\SQLOGDIR\
The log path in windows is E:\DB2\NODE0000\SQL00017\SQLOGDIR\ , where SQL00017 is the location created for the 17 th database. For the first database , the location will be E:\DB2\NODE0000\SQL00001\SQLOGDIR\. In linux , it will be , /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR
Now let us change transaction log path. For example to change the log path for student database in linux , issue the following command.
db2 update db cfg for student using NEWLOGPATH /db2logs OR db2 update database configuration for student using NEWLOGPATH /db2logs
For windows , create a folder db2logs under e:\db2 , then execute the command , db2 update db cfg for student using NEWLOGPATH e:/db2/db2logs

How to add / subtract / compare date with other date with example


Db2 gives very flexible support for the date time operations like Date addition (+) , Date Subtration (-) and Date Comparisons (<, ><=, >=,=) .In db2 , You can compare Date, Time and Timestamp with another value of the same type in chronological (in order from earliest to latest 1991, 1992, 1993)
Db2 supports string representation of date, time & timestamp where date or time can use ISO , USA , EUR or JIS format and timestamp uses ISO format. Example formats are given below.
ISO format Date : yyyy-mm-dd eg. 2011-09-09 / 2011-9-09 , Time : hh:mm:ss eg. 16:25:06
USA Format Date : mm/dd/yyyy eg. 9/09/2011 , Time : hh:mm AM / PM eg. 7.50 AM
EUR Format Date : dd.mm.yyyy eg. 09.09.2011 , Time : hh.mm.ss eg. 14.50.00 14.50
JIS Format Similar to ISO format.
Now let as see some examples using the above formats in queries for Date time arithmetic comparisons
Date Comparisons:
select * from employee where dob='1990-01-02'; where dob is a date field.
suppose you want to mention timestamp , the query may be represented as follows for the timestamp January 1st , 1995 9:50 AM
select * from employee where dob1='1995-01-01-09.50.00'; OR select * from employee where dob1='1995-01-01-09.50.00.0000';
In DB2 , decimal durations can be used in expressions (addition , subtraction) and comparisons. Arithmatic operations like + , - with date yields decimal durations. Decimal duration for Date will be having 8 digits , Time will be having 6 digits and TIMESTAMP will be having 20 digits. 8 digits of Date is in the format YYYYMMDD . For example 225 (MDD) represents 2 months and 25 days , 9990605 (YYYMMDD) is 999 years 6 months and 5 days. 6 digit of Time is in the format HHMMSS . For Example 102930 (HHMMSS) represents 10 Hrs , 29 min , 30 seconds. In general decimal(8,0) number represents the difference between two dates in the format YYYYMMDD. Decimal(6,0) number represents the difference between two time values as HHMMSS. A timestamp duration is a decimal(20,6) number representts the differences between two timestamp values as YYYYMMDDHHMMSS.ZZZZZZ (where ZZZZZZ is microseconds which will be in the right of the decimal point ) .

Examples for the concept decimal durations for date & time :
select DATE('2011-09-09') - '2011-07-25' from emp; which results 115 which means 1 month and 15 days
select DATE('2011-09-09') - '2011-07-09' from emp; results 200 which means 2 months and 0 days
Suppose if you want to calculate your age , just run the query select current date - 'dob' from anytable; . For Example, select current date - '1976-05-31' from emp; which results 350309 which means 35 years 3 months and 09 days
select DATE('09/09/2011') - '07/25/2011' + 10 from emp; results 125 which means 1 month and 25 days
select DATE('2011-09-09') + DECIMAL(315,8,0) from emp;
The above query adds 3 months and 15 days to the date 2011-09-09 which results the date 2011-12-24
select TIME('07:02:27') - '00:30:33' from emp; which results 63154 which is 6 hours, 31 minutes and 54 seconds.
select TIME('11:32:56') + DECIMAL(053030,6,0) from emp; outputs 17:03:26 is equal to 05.03.26 PM

Do you want to calculate , how many years your service is left ? If your retiredment age is 60 means , just run the query select DATE('Date_of_birth') + DECIMAL(600000,8,0) - current date from anytable;   .For example , select DATE('1976-05-31') + DECIMAL(600000,8,0) - current date from emp; whic results 240822 which means you have to serve 24 years 8 months and 22 days more.

How to insert data from external file to db2 database using db2 import

Do you have any data in excel file . Do you want to bring those data into db2 table. Suppose I am having the below data in excel file.


1001 Akash Kumar 15000.00 ak@xyz.com
1002 Micky 60000.00 micky@xyz.com
1003 Jackson 100000.00 jack@zyz.com


          How to bring these data into db2 table. Simple you can use the command db2 import. Steps to bring those data into db2 table. Two ways are there.

First one : In excel , save the data in any folder (eg. D:\db2imp) as CSV (Comma seperated value) (emp.csv) file by selecting the file type as CSV (MS-DOS )(*.csv). Now create a table or use the existing table (employee) with the field empcode, empname, salary, email. Give the following command.

D:\db2> db2 import from emp2.csv of del insert into emp (empcode,empname,salary,email)

              Now all the records from the emp.csv are inserted. In the above statement we have used the word of del which means it will seperate the data fields by a delimiter (,) . Here the default delimiter is Comma (,).

IInd way : In excel save the file as Text files by selecting file type as txt (MS-DOS) (*.txt) . Now open the file and put comma (,) between each data in each row and issue the above command.
db2 import command is used

1. To transfer data from one db2 database table to another db2 database table or within the same database . This can be done with the help of db2 export command . First export data in a specied format by putting delimiter . Then the data is imported to another table using import command.

2. To insert data from an external file with a supported file format (csv , DEL , ixf ) into a table , view.

Now let us see how to move data from one table to another table in the same database or another database in the same or any remote system. Give the following commands

D:\db2expimp> db2 export to emp.ixf of ixf messages msgs.txt select * from emp

Copy the emp.ixf file to the system where the target database lying then issue the following command.

D:\db2expimp> db2 import from emp.ixf of ixf messages msg.txt insert into emp ;

- where msgs.txt , msg,txt are message files where error / successful messages , warnings are stored.

Some more examples :

-The default delimiter for column seperation is Comma (,) . Suppose if you are using column seperator as # (eg. 1001# Akash Kumar# 15000.00# ak@xyz.com ) , then you have to use the following command .

D:\db2expimp>db2 import from emp.txt of del modified by coldel# insert into emp (empcode,empname,salary,email)

      - where coldel is used to mention the column delimiter. The default string delimiter is double quote("). You can also change the string delimiter to any charater by using modified by chardel newcharacter; 
    eg. db2 import from emp.txt of del modified by chardel'' insert into emp (empcode,empname,salary,email).

      To substitute for the period as a decimal point character, use decptx where x the character to substitute. For example (;) instead of decimal point (.) , use decpt;

            Now let us see how to insert identity column from a text file to table independent the availabilty of identity column in the text file.

stu.txt

"abc", 400
"xyz", 300
"abc1", 450
"xyz1", 200

In the above file , no rows have identity column values. But we can insert the data into tables with identity column values by the issuing the command

db2 import from stu.txt of del modified by identitymissing replace into stu

Replace identityignore by identitymissing for inserting identity column values for those rows missing identity values in the text file.

DB2 Query to remove duplicate records using single field or multiple fields


Initially due to poor database design , if there is no primary key then database may allow duplicate records either by application or directly. Now you may need to add a primary key . But data base won't allow to add a primary key . Because it has duplicate values. Let us consider the Employee table. It has empcode , empname , designation, salary, email fields. If empcode is entered through manual , there may be a chance that the empcode to be duplicate. For removing the duplicate empcode or displaying duplicate records ,the following methods may be used .

Method I . If the table has already unique value field like Row_id , you can use that field as row-id , otherwise you can create unique value field (Row_id) using the following ways
a) Add a character filed (ROW_ID) with width 13 by the following commad
ALTER TABLE EMPLOYEE ADD COLUMN ROWID CHARACTER (13) ;
Now update the ROW_ID field with unique values with the following command
UPDATE EMPLOYEE SET ROW_ID=GENERATE_UNIQUE();
Now the ROW_ID field is filled with unique timestamp values.

b) Create a new table with identity column (ROW_ID) . Then insert the values from the original table to new table . Now the new tables ROW_ID column is filled with unique values starting 1 or mentioned during table creation. Commands are as follows
CREATE TABLE EMPLOYEE1 ( ROW_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE), empcode varchar(10) , empname varchar(50) , designation varchar(25) , salary decimal(10) ,email varchar(50) );
insert into EMPLOYEE1 (empcode, empname, designation , salary, email) select empcode, empname, designation, salary, email from Employee
You can use either a) or b)
Now You can display the duplicate records except unique values with following queries
select * FROM emp4 a WHERE ROW_ID> (SELECT min(ROW_ID) FROM emp4 b WHERE a.empcode = b.empcode );
To view only unique records , run the following query ,
-select * FROM emp4 a WHERE ROW_ID = (SELECT min(ROW_ID) FROM emp4 b WHERE a.empcode = b.empcode );
Either you can use MIN or MAX function , if you use MAX , then you have to use less than (<) symbol. In where condition of the subquery , you can combine more than one condition.
To delete duplicate records , run the following query
delete FROM emp4 a WHERE ROW_ID > (SELECT min(ROW_ID) FROM emp4 b WHERE a.empcode = b.empcode );
How does it works ? . ROW_ID column is unique because the values are generated by system. There is no chance of duplication. Suppose empcode 455 has five records but those 5 records should have 5 unique ROW_IDs, among those 5 ROW_IDs , one value must be minimum and one value must be maximum . So we can either keep MIN or MAX Row_id . Other rows we can delete.

Method I I :
Create a temporary table from the original table , the copy the distinct values from the original table to temp table. Now drop the orignal table and rename the temporary table to Original table
Generate ddl from the original table . and create a temp table
-insert into temp (empcode, empname, designation, salary, email) select distinct empcode, empname, designation, salary, email from employee
Drop table emploee
Rename table temp to employee

Note in the above select query distinct keyword is used for multiple columns.
-For DB2 UDB for iSeries SQL , you can use the DDL " create table Temp as (select distinct empcode , empname, designation, salary , email from employee )"

How to set maximum number of connections to a database in db2


It is always very important to optimize the number of required connections to the database . This tutorial covers that how to optimize / set the number of database connections for your DB2 database. In DB2 , there are two main configuration parameters to be set to fix the number of required connections to a databse. The following parameters are to be configured which is applicable to the DB2 pre-Version 9.5
1. MAXAGENTS
2. MAXAPPLS
1. MAXAGENTS : the total number of connections allowed by all the databases available with any database instance. The acceptable value range of MAXAGENTS is 1 to 640000 . To get existing value of MAXAGENTS , give the following command.
For windows , with db2 prompt
db2 get dbm cfg | find "MAXAGENTS"
For Linux ,
db2 get dbm cfg | grep MAXAGENTS
Output of the above command in windows :

  
  Max number of existing agents               (MAXAGENTS) = 400
  Max number of coordinating agents     (MAX_COORDAGENTS) = (MAXAGENTS - NUM_INITAGENTS)

the above output shows (MAXAGENTS) = 400 , that means , the maximum number of database manager agents, including coordinator agents or subagents, available to accept requests by application .
For example , you have 3 databases , suppose you expect 100 connections for each database . Then you have to set MAXAGENTS to 300 + 30 = 330 , where 30 (10+10+10 for each db) additional connections to act as your buffer connections for doing any DB2 background processes. It may be less than 10 , which depends upon your requirement. Why you need to set 330 ? why not 64000 ? . As each connection (i.e. if MAXAGENTS = 64000, then 64000 connections ) is allocated a portion of private memory for an agent on your database server , if you set your MAXAGENTS to larger values which you may not require, leads to a negative performance . There is chance to get the error insufficient memory to allocate your shared memory segment , due to this database might not start . So set large value only after you determine what your optimal number of connections .
Now let us see , how to change the value of MAXAGENTS . To change the value to 330 , give the following command
db2 update dbm cfg using MAXAGENTS 330

2. MAXAPPLS : This parameter is used to define the total number of connections that are allowed by the particular database. The valid range of parameter MAXAPPLS is 1 to 60000. To get current value of the MAXAPPLS parameter for the database Employee , run the following commands

db2 connect to employee
For windows , with db2 prompt
db2 get db cfg for employee | find "MAXAPPLS"
For Linux ,
db2 get dbm cfg | grep MAXAPPLS
Output of the above command in windows :

  
Catalog cache size (4KB)              (CATALOGCACHE_SZ) = (MAXAPPLS*4)
 Package cache size (4KB)                   (PCKCACHESZ) = (MAXAPPLS*8)
 Max number of active applications            (MAXAPPLS) = 50 


To change the MAXAPPLS parameter value to 100 for the database Employee
db2 connect to Employee
db2 update db cfg for employee using MAXAPPLS 100 


When you change the value of MAXAPPLS , it should satisfy the formula "maxappls * maxlocks >= 100" , where maxlocks is the maximum percent (%) of lock list before escalation. When the number of locks held by any one application reaches this percentage , escalation will occur for the locks held by that application where escalation is the process to replace row locks with table locks, to reduce the number of locks in the list . Default value for maxlocks in windows is 22 and in linux is 10 . Valid range is 1 to 100 . The recommended formula to calculate max locks is maxlocks = 2 * 100 / maxappls 
The value of MAXAGENTS should be at least the sum of the values of MAXAPPLS in each database allowed to be accessed concurrently. Let us see the following example for setting the value to MAXAPPLS and MAXAGENTS

Suppose we have 2 databases . dbtest1 , db2test2 . Both databases are created under same instance. Let us assume that one server is connected to dbTest1 and expected connections to the database through the server is 70 and two servers are connected with db2Test2 database that requires 50 connections each . Now what will be the values for MAXAGENTS and MAXAPPLS to optimize number of database connections. The value for the parameter MAXAGENTS will be 170 + 20, where 70 connections are for dbTest1 , 100 connections are for dbTest2 . 10 buffer connections for each database and total of 20 connections for db2 backend process. Now we have to set the value to MAXAPPLS parameter . The value for for the MAXAPPLS parameter for the database dbTest1 will be 70 , dbTest2 will be 100 . Commands to do the above settings would be as follows
  
db2 update dbm cfg using MAXAGENTS  190
db2 connect to dbTest1
db2 update db cfg for dbTest2 using MAXAPPLS  70
db2 terminate
db2 connect to dbTest2
db2 update db cfg for dbTest2 using MAXAPPLS  100
db2 terminate

Note : This parameter is being used by pre-Version 9.5 data servers and clients. Any value specified for this parameter will be ignored by the database manager in DB2 Version 9.5 or later.

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.

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)

Wednesday, April 17, 2013

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

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

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

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

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

db2move command with example


db2move : It is used to move the database tables across different plat forms. This utility retrieves list of all user tables in a database from the system catalog and exports these tables in PC/IXF ( Integration Exchange Format ) format. The PC/IXF files can be imported or loaded to another local DB2 database on the same system , or different system with diffent platform.

The syntax of the db2move command is
db2move <dbname > <action > [ <option > <value >]
- dbname is the data base whose tables you want to move
- actions are export, import, or load which is to be performed on the data base.
- Options may be like to limit the operation to certain tables (-tn), table spaces (-ts), table creators (-tc), or schema names (-sn). Specifying a subset of tables, table spaces, or table creators is valid with the export action only. If multiple values are specified, they must be separated by commas; no blanks are allowed between items in the list of values.

eq. db2move employee export
- exports all table
db2move employee export -tn employee,desig
- exports only employee , desig tables

db2move employee import
- imports all the tables listed in the db2move.lst to the database employee.

Some of files which are generated during Export, Import or Load as follows

EXPORT.out , IMPORT.out , LOAD.out - Stores summary of the completed action
db2move.lst - Contains a list of table names, their corresponding PC/IXF file names, and message file names
tabn.ixf - Contains exported data from a user table (where n is 1, 2 , .... (tab1.ixf, tab2.ixf, ....))
tabn.msg - Contains messages about the requested action against a user table , where is 1,2, .....
tabna.nnn - Contains Large Object Date (LOB) identified by n
system.msg - system messages which is created only if the action is export, and a LOB path has been specified

For Step by Step example , Please Visit post How to move db2 database across different platforms / clone database with cross-platform in db2

How to enable Incremental Backup in db2 ? Incremental / Delta Backup Commands and steps


          This tutorial covers how to take incremental / delta backup and also covers advantages over incremental backup. As database sizes are growing larger ( even up to terabyte and petabyte range) , cost of taking full backups every time will be high , in terms of storage for the backup images and time required to take the backups . It is not advisable to back up the entire database every time, when a small percentage of the data changes happen. The solution to the above problem is taking incremental backup , it allows the user to backup only the changes that have been made since the last backup , instead of having to backup the entire table space or entire database . Incremental backup image contains only pages that have been updated since the previous backup was taken also contains all of the initial database meta data (such as database configuration, table space definitions, database history, and so on) similar to full backup images.
DB2 supports two types of incremental backup

1.Incremental :  An incremental backup image contains database data that has changed since the last, successful, full backup operation. This is cumulative backup image, because last incremental backup image will have the contents of the previous incremental backup image plus changes after that.

2.Delta : A delta, or incremental delta, backup image contains database data that has changed since the last successful backup ( it may be full, incremental, or delta) of the table space. This is also known as a differential, or non-cumulative, backup image .

Now let us see how to enable incremental backup and how to take full incremental backup and Delta .
In our example , i have used the database SALES and involved two tables Sales_Master , Sales_TR
To check wheather the incremental backup is enabled or not , give the command db2 get db cfg for salesIf the output contains the below line, then incremental backup is not enabled that means we can not use the incremental backup functionality .
  Track modified pages     (TRACKMOD) = OFF

To enable incremental backup, turn the database configuration parameter TRACKMOD on . Give the following command

db2 connect to sales
db2 update db configuration for sales using TRACKMOD ON
  SQL1363W. One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective. 

If the above message is displayed then issue the following commands
db2 force applications all
db2 terminate
         When TRACKMOD is on , db2 database keeps track of table spaces that have been modified. So when the incremental backup command is issued , the tables spaces that have not been modified since last backup , are skipped.
Incremental backups require a one time full backup to make as a reference point for incremental changes.
db2 connect to sales
db2 backup db sales online to d:\db2bkp

Incremental
Now make some changes in tables.
db2 "update sales_master set price=price*0.20 where cat_code =4"
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1027', '7', 30, '2011-12-06')
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1014', '4', 20, '2011-12-06')

Now issue the following to take incremental backup
db2 backup db sales online incremental to d:\db2bkp
The size of the incremental backup may be less than the complete backup size because it contains only changes since last full backup. You can check the backup size by issuing the dos or linux command
dir D:\db2bkp\SALES.0\DB2\NODE0000\CATN0000\20111206 or ls -ltr /home/db2inst1/db2bkp

Incremental Delta
Now make some more changes in the tables.
db2 "update sales_master set price=price*0.10 where cat_code =7"
db2 "insert into sales_tr (item_code, category,quantity, Sales_date) values ('1056', '3', 50, '2011-12-07')

To take incremental delta backup , issue
db2 backup db sales online incremental delta to d:\db2bkp
As i already said , delta backup contains all pages changed since the last backup ( delta, an incremental, or a full backup image).

To take only incremental backups at table space level issue
db2 backup db sales tablespace(ts_sales) online incremental delta to d:\db2bkp

Now again make some more changes in the tables and issue
db2 backup db sales tablespace(ts_sales) online incremental to d:\db2bkp
Note the timestamp of above backup. Let us say TS4
The above incremental backup image (TS4) contains all the changes happened since the full backup in the tablespace ts_sales, because it is not a delta backup.

How to Recover records deleted by mistake in db2 using Roll forward to a Point in Time recovery

          The following tutorial explains about how to roll forward a database to a point in time. In one of my earlier postings , i have covered how to restore database from online backup and to roll forward to the end of logs.  Once the database is restored from the online backup, it is required to do a Roll Forward operation to a point in time or end of logs depending upon the situation before the database will be made available. Now let us see, when to use "rollforward to end of logs " and when to use "rollforward to point in time" .

End of logs option is useful when a database is lost, and a recovery is needed through all available logs after the online backup has been made to ensure all transactions have been recovered. For example , last online backup has been taken on 10/25/2011, database is corrupted on 10/27/2011. To recover the database, first restore the last backup taken on 10/25/2011 then to apply the transactions happened after the online backup(i.e. using active logs), we have to roll forward to End of logs

Point in Time option is useful when the following situation occurs. Suppose by mistake, lot of records are deleted from the database by a user. Now how to recover the deleted records?. The answer is rolling forward the logs to a Point in Time before the deletion took place. Now let me explain Point in Time recovery option with example.

Please note the number of rows in the sales_trans table
select count(*) from sales_trans
Suppose the records in the sales_trans are 21000

Now let us take Online backup of a database Sales.
db2 connect to sales
db2 backup db sales online to d:\db2onlinebkp
Backup successful. The timestamp for this backup image is : 20111105224708 , where 20111105224708 is timestamp for the last online backup.

After online backup , some transactions are committed
Again note the number of rows in the sales_trans table by executing the query Select count(*) from sales_trans
Suppose now the records in the sales_trans are 21050
Suppose one user has deleted some of the records by mistakenly by issuing the statement delete from sales_trans where sales_date <=date('01/03/2011') . Now how to recover those records ?
Please note the date & time before issuing the delete statement . This date & time will be required later for point in time recovery. For example : date is 11/05/2011 and time is 23.11.10 .
Note the count by issuing the statement Select count(*) from sales_trans which is now 11000 . Almost 10050 records are deleted.

Steps to recover:

db2 force applications all
db2 terminate
db2 restore db sales from d:\db2onlinebkp taken at 20111105224708 without prompting


           The above command restores the database from the last online backup made. Now we need to roll forward to a point in time (in our example : 2011-11-05-23.11.10 ) before the delete statement was issued .The statement to rollforward to a point in time.

db2 rollforward db sales to 2011-11-05-23.11.10 using local time


In the above output, Roll forward status is DB Working. Please Issue the statement db2 rollforward db sales stop which will make the roll forward status to not pending.

Now you can check the number of rows in the sales_trans which is 21050.

Now the deleted records have been recovered by doing Point in Time recovery. Roll forward has been done upto the time before the delete statement was issued. Delete transactions are recorded after this point in time. So delete statements will not be repeated when we do roll forward. But if you do roll forward to end of logs , the delete statements will also be repeated which will again delete the records.

Db2 Incremental Restore steps and commands


This is one of the important database tutorial that explains how to recover / restore data from the incremental backup . Take full , incremental and incremental delta backups before doing incremental restore. Please go through my earlier post How to enable and take incremental , delta backup ? . The following commands are used to take backup offline (full , incremental , delta)
   
1. db2 backup db employee to d:\db2onlinebkp  .  The timestamp for this backup image is : 20120124001028
2. db2 backup db employee incremental to d:\db2onlinebkp .  The timestamp for this backup image is : 20120124001347
3. db2 backup db employee incremental delta to d:\b2onlinebkp. The timestamp for this backup image is : 20120124003554
4. db2 backup db employee incremental to d:\db2onlinebkp.   The timestamp for this backup image is : 20120124004124 

Now you have the following backups
1. Full Backup : Timestamp 20120124001028
2. Incremental backup : Timestamp 20120124001347
3. Incremental Delta backup : Timestamp 20120124003554
4. Incremental backup : Timestamp 20120124004124
Suppose your database is corrupted after the last backup Timestamp 20120124004124. Now you have to recover your data by using the available backups. You may be confused that what backup images are to be restored in which order. When restoring from incremental backups , you have to apply the right sequence of full , incremental and incremental delta backups . This may be tough in real time environment. For this reason , Db2 provides you two ways to restore incremental backup images.
1. Automatic : When you issue restore command with the option automatic , DB2 uses the database backup history to figure out the right sequence for applying backup images and restores them. The RESTORE command needs to be issued only once . Automatic option is recommended
2. Manual : Decide the right sequence of the backup images that needs to restored and issue restore command once for each image
Before doing Automatic or Manual incremental restore , let us the see command db2ckrst (Incremental Restore Image Sequence Command) . This utility is used to see what backup images will be applied in which order, prior to the automatic / manual restore. It generates a list of restore syntax with timestamps for the backup images that are required for an incremental restore .
Syntax : db2ckrst -d dbname -t -r database or tablespace tbs_name
where Timestamp is the last timestamp to be restored
Example :
For database : db2ckrst -d employee -t 20120124004124 -r database
For tablespace of a database - db2ckrst -d employee -t 20120124004124 -r tablespace emptbs
Suggested restore order of images using timestamp 20120124004124 for database employee.
====================================================================
restore db employee incremental taken at 20120124004124
restore db employee incremental taken at 20120124001028
restore db employee incremental taken at 20120124004124
====================================================================
The output shows that restore command with last icremental backup image needs to be run first to read the control and header information only . Then the database will be restored from the full backup . Lastly , the incremental backup will be read again to apply the data in the image. No need to run restore command for incremental backup with timestamp 20120124001347 . Because the last incremental backup cotains database data that has changed since the last, successful, full backup operation.

Automatic Incremental Restore command
To restore a set of incremental backup images using automatic incremental restore, specify the TAKEN AT timestamp option on the RESTORE DATABASE command. Use the time stamp for the last image that you want to restore. For example , you have the backup image with timestamp 20120124004124 is the final backup (incremental)
db2 restore db employee incremental automatic from d:\db2onlinebkp taken at 20120124004124
when you run the above command , you will get the following message ...
SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted.
Do you want to continue ? (y/n) y
DB20000I The RESTORE DATABASE command completed successfully.
For restoring tablespace (emptbs) backup image in online
db2 "restore db employee tablespace(emptbs) online incremental automatic from d:\db2onlinebkp taken at 20120124004124"
db2 "rollforward db employee to end of logs tablespace(emptbs) online"

Manual Incremental Restore Steps with example
1. Decide the right sequence of the backup images that needs to restored .
2. Identify the last final backup image to be restored, and issue incremental restore command with the timestamp of the last backup image. This image is known as the target image of the incremental restore, because it will be the last image to be restored.
3. Restore last full database or table space image to establish a baseline against which each of the subsequent incremental backup images can be applied.
4. Restore each of the incremental backup images, in the order in which they were produced, on top of the baseline image restored .
You can use the utility to decide the sequence order .
db2ckrst -d employee -t 20120124003554 -r database
4. Repeat Step 4 until the target image from Step 2 is read second time. The target image is accessed twice . First time , the control and header information is read from the target image . Second time data of the image is read and applied. The target image of the incremental restore operation must be read two times to ensure that the database is initially configured with the correct history, database configuration, and table space definitions for the database that will be created during the restore operation.
Suppose , backup image with timestamp 20120124003554 is the final backup (Incremental delta). The following commands needs to be run.
   
 restore db employee incremental taken at 20120124003554   - Ist time  (incremental delta)
 restore db employee incremental taken at 20120124001028   - full
 restore db employee incremental taken at 20120124001347   - incremental 
 restore db employee incremental taken at 20120124003554   - IInd time (incremental delta)


Note : For an automatic incremental restore, the RESTORE command is issued only once . DB2 then uses the db history to determine the remaining required backup images and restores them. For a manual incremental restore, the RESTORE command is issued once for each backup image .

How to convert db2 date to timestamp example

DB2 has many functions related to timestamp . The following situation may arise .
   1. Inserting Timestamp value into a Timestamp field using insert query
   2. Updating Timestamp field with Timestamp value using update query
  3. Updating Timestamp field with the existing Date values by converting the date to timestamp values; that is, convert date to timestamp using the db2 timestamp functions.

           As we know , timestamp consists of year, month, day, hour, minute, second, and microsecond. The internal representation of a timestamp is a string of 10 bytes. First 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds.

To return the current timestamp , run db2 values current timestamp, on the db2 command prompt after connecting to a database. The output will be timestamp having current date , time and fractional time element ; eg. 2012-02-17-00.12.05.671002

Now let us see the , some of the timestamp functions which are used to convert date to timestamp , a string representation of the timestamp to timestamp and more.

TIMESTAMP function - returns a timestamp from a timestamp string OR from a date , time values.
Syntax : TIMESTAMP (exp ,[exp] ) :

         a) If you specify only one argument , it should be a timestamp or timestamp string having length of 14 charaters which represents a valid date and time in the form of yyyyxxddhhmmss, where yyyy is the year, xx is the month, dd is the day, hh is the hour, mm is the minute, and ss is the seconds. The result of the is the timestamp represented by the specified string. The microsecond part of the timestamp is zero

          b). If you specify both arguments , the 1st argument must be a date or valid date string and the 2nd second argument should be time or a valid time string .The result is a timestamp which is the combination of date (1st arg) and time (2nd arg). The microsecond part is zero.

        If the database was created using territory=US , so the default date format will be MM/DD/YYYY .
              To see the current format, issue the command, db2 values current date

           To change the default format to ISO (YYYY-MM-DD), run db2 bind @db2ubind.lst datetime ISO blocking all grant public on db2 command prompt, after changing the current directory to c:\program files\IBM\sqllib\bnd on Windows and /home/db2inst1/sqllib/bnd on UNIX.
      For more details about changing date format in db2 , please visit Change db2 date format

         timestamp('20120101230420') returns 2012-01-01 23:04:20.000000
        timestamp('2012-01-01', '23.04.20') returns 2012-01-01 23:04:20.000000

        if dob is 1977-01-02 and tob is 17.03.50 , timestamp(dob, tob) returns 1977-01-02 17:03:50.000000

         To convert current date with time 12.00.00 into timestamp value , TIMESTAMP(char(current date) ||' 12:00:00') returns 2012-02-02 12:00:00.000000

      Run the above functions using values in db2 command prompt . for example values timestamp('2012-01-01', '23.04.20') OR run with with select query . For example select timestamp ('2012-01-01', '23.04.20') from employee , where employee is table name.

Problem A : Suppose Employee table has many fields with many records ; one of the field is dob of date type. Suppose you need to add a field dob_ts of timestamp type.

Now to update timestamp field dob_ts with date of birth with time 09.30.45. Run update employee set dob_ts= TIMESTAMP(dob, '09.30.45')

To insert current timestamp to a timestamp field (dob_ts) , run
insert into employee (empcode,empname, dob_ts) values ('5546','Kumar' , current timestamp)

To insert current timestamp to a timestamp field with microseconds , run
insert into employee (empcode , empname, salary, dob_ts) values ('5871', 'xyz', 344223, TIMESTAMP(char(current date) ||' 12:00:00.760000'))

To update timestamp field with date field and time with fractional time element (microseconds) , run
update employee set dob1=TIMESTAMP(char(current date) ||' 12:30:10.760700')

TIMESTAMP_FORMAT function / TO_DATE / TO_TIMESTAMP :- returns a timestamp from a character string
        Syntax : TIMESTAMP_FORMAT ( string-expression , format-string )

            String expression - returns or contains the components of a timestamp that correspond to the format specified by format-string . The return type is CHAR or VARCHAR with length not greater than 254
         Format-string - contains a timestamp format (template) of how string-expression is interpreted and then converted to a timestamp value . Format-string length is not greater than 254 bytes

Allowed string-format elements :
YYYY or YYY or YY or Y - Year (0000-9999 or 000-999 , 00-99 or 0-9 ) .
Use RR or RRRR to adjust the year based on current year. The following conditions are used
Let A be last two digits of the current year , B be two digit year in string-expression, C be 1st two digit of the year component of timestamp . Now
If A is 0-50 and B is 0-49 then C is first two digits of the current year
If A is 51-99 and B is 0-49 then C is first two digits of the current year + 1
If A is 0-50 and B is 50-99 then C is first two digits of the current year - 1
If A is 51-99 and B is 50-99 then C is irst two digits of the current year

Suppose you give, 99 which means 1999, but if the current year is 2012 means, it is adjusted to 2099.
MM - Month (01-12).
DD - day (01-31).
DDD - day of year (001-366).
HH or HH12 - hour
HH24 - 24 hour format (0-24)
MI - Minutes (0-59)
SS - seconds (0-59)
SSSSS - hours, minutes, and seconds (00000-86400)
NNNNNN or FF[1-n] where n is number of digits ( in earlier version n=6 and in Db2 9.7, n = 12 ) - microseconds (0-999999 ) ->equal to FF6

example format-string is YYYY-MM-DD HH24:MI:SS
Values TIMESTAMP_FORMAT('1999-12-31 23:59:59','YYYY-MM-DD HH24:MI:SS') , returns 1999-12-31 23:59:59.000000

In the problem A , current timestamp is inserted with time element zero. To insert current timestamp with time 23.59.59 , run the following command

insert into employee (empcode , empname, salary, dob,dob1) values ('5602', 'abc', 53223, '2012-01-01',TIMESTAMP_FORMAT(CHAR(current date)||' 23:59:59','YYYY-MM-DD HH24:MI:SS'))
     where CHAR(current date)||' 23:59:59' converts current date to string and concatenated with time.

To update timestamp field with timestamp string (date of birth + time )
update employee set dob_ts = TIMESTAMP_FORMAT(CHAR(dob)||' 06:09:39','YYYY-MM-DD HH24:MI:SS')

TIMESTAMP_ISO Function : Returns a timestamp value based on a date, time, or timestamp
Syntax : TIMESTAMP_ISO ( exp ) , where the exp must be a date , time, or timestamp , or a valid string representation of a date, time or timestamp without time zone. If the argument is a date, then the time element is 00.00.00 and the fractional time element is zero . If the argument is a time, the date part is CURRENT DATE , and fractional time element is zero.

example : db2 values timestamp_iso (current date) - returns 2012-02-16 00:00:00.000000
select timestamp_iso(dob) from employee where empcode='1001' , where dob is date of birth a date field.

To return timestamp of having current date with time 17.30.25 , run the following command
db2 values timestamp_iso ('17.30.25') , returns 2012-02-16 17:30:25.000000 , where 2012-02-16 is current date.

To insert current timestamp with microseconds using TIMESTAMP_ISO Function , run
insert into employee (empcode , empname, salary, dob,dob_ts) values ('5870', 'abc', 32663, '2012-01-01',TIMESTAMP_ISO (char(current date) || '-12.00.00.045000'))

Suppose you want want to convert all date values to timestamp values (i.e. date field to timestamp field) having time element zero.
               update employee set dob_ts=timestamp_iso(dob)

TIMESTAMPDIFF Function : Used to calculate days, months , years , minutes , weeks, hours, seconds between two timestamps.

   Example : values TIMESTAMPDIFF(32,CHAR(TIMESTAMP('2012-02-05-12.07.58.000563')-TIMESTAMP('2012-01-19-11.25.42.473439'))) returns the number of weeks between two timestamps that is 2 . You can go through one of my tutorial about how to find difference between two timestamps for more on timestampdiff

Convert Timestamp to Date in db2

          To convert Timestamp to Date , use Date(exp) function which accepts date , timestamp and string representation of date and timestamp
      
       values DATE (current timestamp) , returns date value from current timestamp
      values date('2001-01-01-17.01.20.929866') , returns 2001-01-01
      select date(dob_ts) from employee , converts timestamp to date.

How to find difference between two timestamps , dates in db2.


The following tutorial explains about how to find the difference of two timestamps and also covers how to calculate the difference of two dates . In many situation , you may need to calculate the difference between two timestamps. For example , your application , may capture the access date and time of a user as timestamp in a user table. User account may be locked when a user makes 3 continues unsuccessful login attempts . The access date and time of the userid is stored. After 72 hours , we may need to unlock the locked accounts using query. In this situation , you have to calculate the difference between current timestamp and timestamp when the user account is locked. Another example , in company , IN_TIME and OUT_TIME may be captured as timestamps. Now you can calculate the duty hours by subtracting the OUT_TIME timestamp with IN_TIME timestamp. And date and timestamp difference calculation may also be required for age calculation , service left for retiredment , etc.

1. Calculate difference between two timestamps :

           Difference between two timestamps can be calculated in the following ways
1. timestamp(exp) - timestamp(exp1) OR timestamp(exp) -exp1 OR exp-timestamp(exp1) , where exp, exp1 are timestamp or valid string representation of timestamp. The result of subtracting one timestamp from another will be timestamp duration which is a decimal(20,6) number that represents the number of years, months, days, hours, minutes, seconds, and microseconds between the two timestamps . The result is in the format of YYYYMMDDHHMMSS.ZZZZZZ

2. date(exp) - date(exp1) where exp, exp1 are timestamp or valid string representation of timestamp. The result will be a decimal duration which is decimal(8,0) number represents the difference between two timestamp values as YYYYMMDD. Both exp1 & exp2 are casted to date
First one is more preferred than second way . IInd one used , when you calculate difference of timestamps , if don't want account the time element of timestamps.
examples :

values timestamp('2012-01-05-12.00.00')-timestamp('2011-02-01-12.00.00') , returns 1104000000.000000 that means 11 months, 4 days
values date('2012-01-01-12.00.00')-date('2011-01-01-12.00.00') , returns 10000 , that means 1 year
values timestamp('2012-01-05-12.00.00')-timestamp_iso('2011-02-01') , returns 1104120000.000000   which is 11 months , 4 days , 12 hours
values current timestamp-timestamp(lastaccess)

To calculate the above results , in days , weeks, months , etc , db2 provides a function calledtimestampdiff()
Syntax : timestampdiff (n, char( timestamp(exp)- timestamp(exp1)))
where n can be 1,2,4,8,16,32,64,128 and 256 . 1 = Fractions of a second , 2 = Seconds , 4 = Minutes , 8 = Hours , 16 = Days , 32 = Weeks , 64 = Months , 128 = Quarters , 256 = Years ,
eg. values timestampdiff(16, char(timestamp('2012-01-05-12.00.00')-timestamp('2011-02-01-12.00.00'))) , returns 334 , which means 11 months and 4 days.
update user set login_failed_attempts=0 where timestampdiff(8,char(current timestamp-timestamp_iso(lastaccess))) >=72
select timestampdiff(8, char(timestamp(OUT_TIME)-timestamp(IN_TIME))) from employee , returns the duty hours
Note : The value returned by the above functions is an approximate value , because it does not account for leap years and assumes only 30 days per month.

1. Calculate difference between two Dates :
difference between two dates can be calculated using
date(exp) - date(exp1) OR date(exp) -exp1 OR exp-date(exp1) , where exp, exp1 are date or valid string representation of date. The result will be a decimal duration which is decimal(8,0) number represents the difference between two date values as YYYYMMDD.
Examples :
values date('2012-02-01') - date('2012-01-01') , returns 100 which is 1 month and zero days
values '2012-01-01' - date('2012-02-01')
values current date-DATE('1976-08-21') , returns 350515 which is 35 years , 5 months and 15 days.
select current date - date(doj) from employee , returns the duration of service in the organization

You can use timestampdiff() to calculate difference between two dates . Convert date to timestamp then calculate the difference using the function
eg. select timestampdiff(256,char(timestamp_iso(current date) -timestamp_iso (dob))) from employee , returns the number of years between current date and birthdate

Db2 SQL Replication Step by step with example

One of the very helpful and important feature in db2 is Replication technique. Replication technique allows you to copy data from one location to another location making the second location data identical to the first location.  Data can be copied either  in the local or  remote machine .
Replication is useful
                       1. To consolidate data from multiple sources in a  distributed environment
                       2.  to support basic load balancing when your server is running many more read queries (SELECT) than write queries (insert / update / delete) .
                       3. Makes a backup of data in the local / remote which helps  for disaster recovery
                       4.  to reduce delay and bring the data closer to the user.

DB2 Universal Database (UDB), supports two types of replication.
SQL Replication can be done using Control center and Scripting language called ansclp .SQL replication capability is included in the base product. From the Control Center you can access the Replication Center, a graphical interface for the setup of replication. There is also a scripting language for replication called ansclp which allows you to create scripts to automate replication setup.

This tutorial overs that how to setup replication using the the Replication Center .   You can access the Replication Center  using Control Center -> Tools -> Replication Center.

What happens when we do replication.
                   Two programs are involved.  Capture Program and Apply Program .Capture program capture the data changes in the source table to the CD Table .    Changes of  data in the  source tables are captured in a CD Tables  . When we insert new rows in the source table , new rows are captured in the CD Tables with flag "I" . When we update  data in the source table ,  the updated rows are captured in the CD Tables with the flag "U" .  SImilarly , When we delete   data in the source table ,  the deleted  rows are captured in the CD Tables with the flag "D"  .   Apply Program , replicate  the the data changes captured   in the CD table to the target tables.

Major Steps for replication :

1. Create  control tables for the Capture program

2. Enable the source database for replication (i.e. to enable logretain on for archival log)

3. Register source tables

4. Create control tables for the Apply program

5. Create a subscription set and  member

6.  Start the operation to capture and  apply

7.  Testing the Replication


1. Create  control tables for the Capture program
To  Create table space and control tables do the following step
            1. Right click on the Create Control Servers  -> Select Create Capture Control Tables -> Select Custom 

             2. Select Capture control server (Source database) to create Capture Control Tables
Give user name and password and select Run then OK.

                               
              Now Capture control tables are created

2. Enable the source database for replication
             Right click on the Source database name (Capture control server ) to enable database for replication .  that means to enable archive logging .


            Press ok button to set the LOGRETAIN value for the database to RECOVERY and initiate offline backup for the db    and take full backup of db


3.  Register a replication source

 a) Before registering source tables , specify the schema and table name and table space to be used  for the CD (Capture Data) tables

Right click on the Capture control server  and Source database name  and select Manage Source Object Profile

   You can specify here
i)  table schema and table name to be used as the defaults for the CD tables
ii) table space & its properties  for the CD tables and naming convention for table spaces
           iii)  schema and name for the CD table indexes & naming convention for the the index name.



b) To register source tables
         1.   Select  Capture control servers - > Source database -> capture schema -> schema name.
         2. Right click on the schema name and select Register tables
         3. Select Retrive All to retrive all  source tables and select the table you want to register.  Now CD (Capture Data) table name and table space details appeared automatically based on the settings in the previous steps (i.e. 3 a)

         4.  Then  Ok which will run the query .

4. Create  control tables for apply  program 

To create control tables for apply  program  , Right click on Apply Control Server -> Create Apply Control Tables -> custom


       Select the target database where the data to be replicated .  Then Ok.

5. Create Subscription Sets 
A subscription set defines a relationship between the source database (ORI_DB  in our example) and a target database (DUP_DB in our example).  A subscription-set member defines a relationship between the  source table (SALES) and one or more target tables (TGSALES).

Here you have to specify , set information ,  source to target mapping , schedule time to replicate data.

Steps to do  :
To create subscription sets , Expand  Apply control server -> Right click on the newly created Apply control server ( target data base) -> Subscription sets
Now you have to fill / select  all the details  like Apply control server , Set Name , Apply Qualifier , Capture control server and target server (Target database)
Check on the activate the subscription set

Now do the source to target mapping  , which maps the source colums to target columns to replicate the data .

Now Set the replication schedule  (Time based / event based)   , Then Ok.

Now let us   Add Members

Right click on the newly created set  , select the member information tab and add the member using retrive all option  .  Use change to do column mapping and etc... Please add the column for index for target table using target table table .   Then Ok.

Now the target table is created in the target server where the data to be replicated .

6. Finally Let us  start the operation to capture and  apply  

To start Capture , Expand the operations on SQL replication under Replication center

Right click on the capture control servers and select add  and give the capture control server already created (target database) ,  userid and password  which adds capture control servers for operations

Right click on the capture control servers  which is added now , then select start capture  , then select capture schema , then Ok.
Now give user name and password  for the database server to access

 Similarly you can stop , resume or suspend capture later.

To start Apply Program
             Similarly Expand the apply control servers under operations , Right click on  the apply control server -> Apply qualifiers , then refresh .

Now right click on the apply qualifiers already created and the select start Apply , give Host  Name or  IP Address  , then Ok.    Now give your target server details by clicking Add New System  ..


 Similarly you can stop apply , if you need ..


7. Now test the replication ....
Insert any record to the source table

Now the new record is captured in the  CD Table  (In our example : CDSALES)  with the flag (IBMSNAP_OPERATION) with following values

'I' - Insert operation tbe done  on the target table.
'U' - Update operation to be done
'D' - Delete Operation to be done

Finally the changes  captured in the CD table is applied  in the target table by the apply program. 



Note : I have created Capture control server and Apply control server on the same system.