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

Read More

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

Read More

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.

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

Read More

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

Read More

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.

Read More

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