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

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

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

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.com1002 Micky 60000.00 micky@xyz.com1003 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...

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

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

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

Read More

Select , Update , Delete N number of rows in db2

For Select : In db2 , fetching a limited number of rows is very simple. You can use  FETCH FIRST n ROWS ONLY with select query. In some applications, a select query with certain condtion or without condition may return a large number of rows, but you may need only a small subset of those rows.  For example , in a web page with ajax support , you may search a city that starts with ' N'  which  returns many numbers of rows and are filled in a combo.  Retrieving the entire result  from the table can be inefficient...

Read More

Creating Table Space in db2 using command line

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

Read More

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

To connect the remote database from the client machine, we have to do the following things. 1) Install the DB2 Client Software 2) Catalog Remote Node 3) Catalog Remote Database 4) Connect the Remote Database Steps in Detail ---------------------- Catalog TCP/IP Node You have to make an entry to the client's node directory to describe the remote node. This entry specifies the chosen alias (node_name), the hostname (or ip_address), and the servicename (or port_number) that the client will use to access the remote server Syntax : db2 catalog...

Read More

How to move db2 database across different platforms ?

Moving / Cloning the data base residing in one machine to different machine with Same Platform (Window to Windows or Linux to Linux) can be done by redirected restore operation on a full database backup image OR using backup and restore commands given below. 1) db2 backup db dbname - For Source Database to be run on source machine db2 restore db databasename taken at timestamp into sourcedb replace existing - For target Database , to be run on destination machine. Now the problem comes when both machine are having different...

Read More

How to take Online Backup in DB2

Steps for Online Backup in DB2 To do an online table space and database level backup via CLP command prompt, Please follow the steps below. In order to perform an online backup , you have to Turn on either the LOGRETAIN or USEREXIT . Command for Turning LOGRETAIN ON : In this example, i am using database name is as Employee db2 => update db cfg for Employee using LOGRETAIN on Now Shutdown and start up the database again to make the configuration change effective db2 => terminate db2 => force application all Now the configuration parameter...

Read More

How to uninstall or remove DB2 in Linux

To uninstall or remove DB2 do the following steps, 1: Log in with root user. 2. List out all DB2 instances. To list the db2 instances, Do the following #cd /opt/IBM/db2/V8.1/instance the above folder will be applicable for system with Unix/ Linux based OS # db2ilist - which lists the all the instances of db2. 3. Drop each instance listed in the above step with the following command #db2idrop instance name 4. Drop the DB2 administration server (DAS) with the following command #cd /opt/IBM/db2/V8.1/instance # dasdrop dasusr1 where dasusr1 is...

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More

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

Read More