SQL Monitoring from the Stored Procedure

You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature. Let us understand this through a simple exercise: Step-A:...

read more

Equivalent of Oracle KEEP function in DB2

Equivalent of Oracle KEEP can be in DB2 using a nested query OVER (PARTITION BY ..) and then using MAX and MIN on top of it without having any extra sort. It is best explained by an example: Case-1: Oracle query using KEEP function: CREATE TABLE emp(id INTEGER, name...

read more

DB2 Control Center Code Page

If you are using DB2 Control Center on Windows and wondering how to set the code page of your choice, follow these steps. Open a DB2 command window: C:\>SET DB2CODEPAGE=1208 C:\>db2cc The first command sets the environment variable DB2CODEPAGE to 1208 (UTF-8) and...

read more

Federation of Oracle Data in DB2

Steps to configure Oracle database federation from DB2 on zLinux. (Or any kind of *nix) These steps are for Oracle database (any supported version from Oracle) to DB2 9.5 but they should work as it is for DB2 9.7 or later with proper Oracle client install. 1. Install...

read more

Federation of SQL Server Data in DB2

Steps to configure SQL Server federation from zLinux. (Or any kind of *nix) These steps are for DB2 9.5 but they should work as it is for DB2 9.7 or later with proper install of IBM branded ODBC drivers. 1. Install DB2 2. Install IBM Branded DataDirect ODBC Drivers....

read more


When you want SP to return message_text and you also want to handle error code as per your choice, follow this simple example. CREATE PROCEDURE myproc( OUT v_msg VARCHAR(300)) LANGUAGE SQL BEGIN DECLARE V_ERROR INTEGER DEFAULT 0; DECLARE CONTINUE HANDLER FOR...

read more

New Exciting Features in DB2 9.7

This is by no means an exhaustive list of new features in DB2 9.7. But, it is the list of features that are most important to me as a DBA or a developer. Online Schema Changes See Burt's article on Online Schema changes. CGTT - Created Global Temporary Table Now, it...

read more

Unused indexes, tables and packages in DB2

Starting version DB2 9.7, you can use following query to detect unused indexes in DB2. SELECT INDSCHEMA, INDNAME, TABNAME FROM SYSCAT.INDEXES WHERE LASTUSED = '01/01/0001'; The column LASTUSED tells when that index was last accessed in DB2. This is not per se a...

read more

Online Schema Changes – The Easy Way

I am going to show you an easy way to do online schema changes in DB2. The best to learn things is by doing some simple examples. My intent is to make you an expert in 1/2 hour if you learn the following concepts. Create a database with and without automatic storage...

read more

Java Program Name for DB2

Finally DB2 implemented a long standing demand for setting the Java application name so that you could see the name through DB2 utilities instead of seeing db2jcc_applica. You set a connection property clientProgramName to your chosen name and the db2 utilities will...

read more

Build an INSERT statement

Use this DB2 Stored Procedure to build an INSERT statement. This is very useful when you are developing some Java or .Net program or any other program and need an easy way to do this. Thanks to Serge Rielau of DB2 Toronto lab for sharing this with the DB2 community....

read more

DB2 Update Statement using sub-query – Use MERGE

Thanks to Serge Rielau for insight in DB2 MERGE. For example, if you have this Oracle UPDATE statement and wondering how can you use this in DB2. Here is the solution. Oracle UPDATE UPDATE ( SELECT clm.c2 wc1, m.c2 wc2 FROM schema1.table1 m, schema1.table2 clm WHERE...

read more

Run DB2 Scripts from Java Programs

If you have DB2 scripts that you want to run through your Java programs, you normally use Runtime.getRuntime().exec method to run system commands. The following sample code tells how to run DB2 scripts. The following code shows it for both Windows and Unix systems....

read more

Matt Devlin’s Perl Scripts for DB2

Are you looking for Matt Devlin's Perl Scripts for DB2? Even though, they are old but still they are relevant with new release of DB2 particularly if you know how to modify them. Matt Devlin's website http://mattdevlin.net is no longer available but you can get...

read more

DB2 LOAD using ASC option through MTK

If you use MTK (Migration Toolkit from IBM) to migrate your database to DB2 on Windows platform, the migration goes smooth but you may see problem when you FTP data file and LOAD script to Unix platform if you do not do it right. For example, you chose ASC (ASCII)...

read more

DB2 Check Pending Script

Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the...

read more

How to concatenate rows together?

Say, you want to concatenate all rows of a column and show it as a single row in the same fashion aggregate function works. $ db2 "create table t1(line# int not null, word varchar(20))" $ db2 "insert into t1 values(1,'The'),(1,'horse'),(1,'is'),(1,'white')" $ db2...

read more

Speed up MQT – Tips

Edin I. Aganagic shares his tips on how to increase incremental refresh/maintenance performance of MQTs in DB2. Create an index on the GROUP BY columns. It helps speed up MQT look-ups needed to decide whether existing row needs to be changed, new one inserted, or last...

read more

5.0 Use logging through C Stored Procedures

The logging framework routines are written in C, so you can use them directly from your C stored procedures without having to make a call to the logging stored procedures. The example C stored procedure below uses the logging API. To use the logging methods, include...

read more

4.0 Use logging through SQL Procedures

After you run make successfully, you will notice a file generated known as testsp.sql. The only reason we generate this file instead of giving this file as it is is to calculate the size of logging handle properly for your Linux/Unix platforms. This is due to the fact...

read more

3.0 Logger Setup

In order for you to set up the logging framework, you might need help from your system and/or database administrator to set up the stored procedure logging directory. Before we delve into this, let us understand how external stored procedures (Our logging framework...

read more

2.0 Reference guide

An explanation of logging tokens The first DB2.OPEN_LOG call builds a linked list hash table as shown below from the logging tokens kept in the shared memory. For each logging token read from the shared memory or configuration file on its first invocation, OPEN_LOG...

read more

1.0 Overall architecture and design

Motivation You might need to write informational, error, and debug messages to a log file for a number of reasons: To test, analyze and validate business logic. To track and review detailed error messages. To fix business logic bugs encountered in production...

read more

DB2 Procedures Logger

I published an article Log stored procedures messages for DB2 on Linux or Unix on IBM Developerworks website in January 2006. Since then, I have made several bug fixes and enhancements to this logging framework. The enhanced logging framework is documented here with...

read more

DB2 ODBC Driver double quotes

If you are migrating your ODBC related code from one database to DB2, you might run into an issue of single quote vs. double quote issue. For example: Your ODBC code might issue a call to a Stored Procedure using double quote as shown below: CALL...

read more

C Table UDF to join flat file with your SQL

Sometimes, it may be necessary to expose data in your flat file with the relational data. There might be some situations where there is no option to load that data in a table. But still, you need a way to use this flat file data in your SQL statements. DB2 provides...

read more

Julian day in DB2

DB2 provides a way to calculate date from a Julian day. For example: 2008075 should match to the 75th day of 2008 and i.e. 2008-03-15. If you try to do, $ db2 values date(2008075) You will get a date of 12/03/5498 but that is what you do not want. The argument to DATE...

read more

Optimistic Locking in DB2 9.5 LUW

Improve concurrency with DB2 9.5 optimistic locking New optimistic locking feature to avoid maintaining long-lived locks IBM® DB2®, Version 9.5 for Linux®, UNIX®, and Windows® provides enhanced optimistic locking support, a technique for SQL...

read more

How to setup the Self Tuning Memory Manager (STMM)

Self-tuning memory was first introduced in IBM ® DB2 ® 9.1 and simplifies the task of memory configuration by automatically setting optimal values for most memory configuration parameters, including buffer pools, package cache, locking memory, sort heap, and total...

read more

DB2 LUW Deep Compression

Introduction This article discusses DB2 LUW Deep Compression from the perspective of someone who planned, organized, communicated and coordinated the implementation of this in development, test, regression test and production environments for multi-terabyte data...

read more

Case insensitive string comparison in DB2

You are trying to figure out the best approach to do the case in-sensitive search in DB2. You must first read these 3 excellent articles written on this topic by 3 IBMers. Blair Adamache Doug Doole Knut Stolze In a nutshell, you can take one of the approach as...

read more

How to compile DB2 PHP driver

You are new to ibm_db2 or db2 and PHP driver available with thin ODBC-CLI client do not match with your version of PHP. The following steps take drudgery out of complex compilation process for those DBAs or system administrator who are averse to compilation process....

read more

Top like utility for DB2

Yes, db2 has a top like utility called db2top written by 2 IBMers Subho Chatterjee and Jacques Milman. It uses DB2 snapshot APIs to extract information from DB2 engine and shows in a format that you are familiar with top utility on Unix systems. Click at...

read more

Show Package ISOLATION Level

Use following SQL to find out the ISOLATION LEVEL used by DB2 packages. SELECT 'SHOW_RSETS' "STORED PROCEDURE", SUBSTR(PKGNAME,1,18) PACKAGE, "ISOLATION" FROM syscat.packages WHERE pkgname = (SELECT deps.bname PACKAGE FROM sysibm.sysdependencies deps,...

read more

Equivalent of NEWGUID in DB2

If you have been using UNIQUE_IDENTIFIER as a data type and use newguid as a default in your SQL Server table definition, you can use a UDF called from a trigger to simulate same behavior in DB2. This work around is due to the fact that DB2 does not yet support...

read more

DB2 JDBC Driver Secrets

There are several ways one can do DB2 JDBC tracing and it all really depends how creative you are. Check version of JCC Driver $ java com.ibm.db2.jcc.DB2Jcc -version Simple nonsense solution to use JCC Tracing import java.sql.Connection; import java.sql.DriverManager;...

read more

Get DB2DIAG severe messages of past 24 hours

You can put this script as a cron job so that you get DB2 severe messages through email or to your pager. #!/bin/bash # To display severe errors logged for the last 1 day: db2diag -gi "level=severe" -H 1d | /bin/mail -s "DB2DIAG severe messages in last 24 hours"...

read more

Query optimization in DB2 using REOPT

In Oracle world, DBAs generally say that Oracle can do static and dynamic SQL. When they say this, it amuses a DB2 DBA since Oracle way of static and dynamic SQL as perceived by an Oracle DBA is no way near to what is meant by static and dynamic SQL in DB2. In Oracle,...

read more

Size of temporary bufferpool in DB2

This is a common practice to bump the size of all buffer pools in DB2 as that gives the maximum performance boost. It is true but as a DBA you will like to monitor the buffer pool hit ratio and use of the buffer pool to make sure that you are not wasting your memory....

read more

Asynchronous Read activity

As a DBA, your efforts is to make sure that you have as much asynchronous read activity on your database. The intentions are good but how do I know the amount of asynchronous activity going one in my database. This SQL will give you the Asynchronous read ratio (ARR)...

read more

Difference between db2pd tool and DB2 snapshots

DB2 snapshot command or use of the snapshot administration views or SQL table snapshot functions require latches and use of engine resources to return the information but db2pd tool does not require those latches etc to return the information. You should use db2pd...

read more

How to change Fenced user id?

Sometime back, I wrote about how to know about a fenced user id in DB2 at http://www.db2ude.com/?q=node/30. The other day, someone asked me how do I change the fenced user id in DB2? This is very easy but will require an outage if you are already in production. I...

read more

Why UR is escalating to CS?

Did you wonder why your isolation level escalated to CS from UR? For this discussion, you might want to read about unambiguous cursor at DB2 at http://db2ude.com/?q=node/41. So even if you open a cursor with UR isolation level, there is a possibility that this might...

read more

What are P, B and L locks in DB2?

Did you know about P, B or L locks in DB2? They are internal locks in DB2. As a DBA, it is good to know about them so that we know how to deal with them or avoid them. P Locks These are package level locks and you will see them when any application is executing a...

read more

How to create EXPLAIN tables in DB2

Most of the GUI tools create EXPLAIN tables automatically for you but if you need to create them, you can use one of the following approach. DB2 provides EXPLAIN.DDL in ~/sqllib/MISC directory and you can execute this file $ db2 connect to sample $ db2 -tf...

read more

How do I set isolation level in DB2?

You are a DBA and you want your users to use an isolation level of UR who are doing adhoc queries or typing SQLs using DB2 CLP or some form of the applications. By using DB2 CLP Use SET CURRENT ISOLATION LEVEL command db2 connect to sample db2 set current isolation...

read more

How do I set special registers in DB2 for audit?

Through a application server where you open dedicated connections to database, you get information about your SQL using applications server user-id only. How do you tie those information back to the end user who initiated the transaction? One option is to use DB2...

read more


LAST_DAY(SYSDATE) ==> values (current date + 1 month) - day(current date +1 month) days FIRST_DAY(SYSDATE) ==> values (current date - day(current date + 1 month) days) + 1 DAY

read more

Get index space usage for a table

How do you get index space usage by a table? DB2 provides a Table function ADMIN_GET_TAB_INFO to retrieve information about a table. For example - To find index space used by a non-partition table's indexes, you can use following SQL: SELECT index_object_p_size as...

read more

Speed up DB2 Backup

Generally people try to tune DB2 backup by manipulating PARALLELISM parameter but there is no need to manipulate this parameter if you are looking for a least possible time to backup the database. Let DB2 figure out the optimal value for you for PARALLELISM. But, you...

read more

Tables in LOAD pending

If you are using LOAD utility to load 100s of tables, you can use following SQL to find out the tables which are in LOAD pending status. SELECT TABSCHEMA, TABNAME, LOAD_STATUS FROM SYSIBMADM.ADMINTABINFO WHERE LOAD_STATUS IS NOT NULL

read more

DB2 Audit Facility

The new version of DB2 9.5 has enhanced DB2 audit facility significantly and now it makes a perfect sense for the DBAs to start using it. The traditional notion on any audit (DB2 or Oracle) is that it is very expensive. That is not the case now as you have the options...

read more

LOAD operation in HADR

You have setup your databases in HADR operation and you are wondering how my DB2 LOAD will transfer from one machine to another since DB2 LOAD operation is not logged. Well, the DB2 LOAD operation is of course logged but not the actual contents. So, how do I get my...

read more

DB2 HADR parameters

If you have enabled HADR between 2 databases, the following value of parameters might give you better performance. Database configuration parameters LOGFILSIZ = 6000 LOGBUFSZ = 512 SOFTMAX = 125 DB2 Registry variables db2set DB2TCP_CLIENT_RCVTIMEOUT=20 db2set...

read more

DB2 Backups Archiving and Deleting

DB2 9.5 deletes previous backup images, log files and copies of load images automatically. This alleviates lots of pain for maintaining DB2 backups and log files. Prior to DB2 9.5, these tasks were usually attained by the operating systems scripts. For example, Taking...

read more

DB2 Database on different location in Windows

On windows platform when you create a DB2 database as per following syntax, it gets created in C:\DB2\NODE0000 location where DB2 is the name of the instance. C:\>db2 "CREATE DB TESTDB AUTOMATIC STORAGE YES ON C: USING CODESET UTF-8 TERRITORY US" What if, you want to...

read more

Features used in DB2.

DBAs have to comply with the licensing requirements for DB2. As a DBA, how do you know if you are using a particular feature of DB2 or not? I wrote about this long time back at here. In addition to above, you can also use this SYSIBMADM view to find out if you are...

read more

File system caching in DB2 with SAN devices

Lots of you use DB2 on SAN storages either from IBM or EMC or other third party. Most of SAN storages have their own caching which is to enhance the performance. After working with many DB2 users, I have found that these file system caching is not used properly to use...

read more

How to use db2batch?

The db2batch utility is a powerful utility to time the SQL queries. You should be careful in using the db2batch utility during peak time in your production system just for one issue - db2batch utility uses RR isolation level. For long running SQL statements, the RR...

read more

Convert HEX to CHAR

If you want to convert a 2 digit hex code to a single char code, here is the function that you can use. DROP FUNCTION HEX2CHAR; CREATE FUNCTION HEX2CHAR (hexcode varchar(2)) RETURNS CHAR(1) SPECIFIC hex2char LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION DETERMINISTIC...

read more

Simulate z/OS DB2 Collation sequence in DB2 LUW

You might come across a need to simulate collation sequence of DB2 on mainframe (or MVS or z/OS) as it is on DB2 LUW (Linux, Unix and Windows) platform if you want your application to show the order of the data exactly same way as it is on the mainframe. The main...

read more

Number of Max Files Open in Linux

If you are connected to Linux via SSH and you want to bump up number of max file open either for DB2 or for your any program, you might run into issues where it does not work for you. Say for example: You login as root and do this modification to...

read more

Create MTK UDFs in DB2

Follow these steps if you want to create MTK Java UDFs manually. 1. Locate mtkoradrop.udf in your MTK directory and drop MTK UDFs $ db2 connect to sample $ db2 -td\! -f mtkoradrop.udf 2. Remove Jar if it was installed already. You can look at the ~/sqllib/function/jar...

read more

Get IP Address of connected DB2 client

When DB2 agent (db2bp process) makes a connection for your CONNECT request, it stores the IP address of the client in the application ID as shown below: $ db2 list applications Auth Id Application Appl. Application Id DB # of Name Handle Name Agents --------...

read more

How to know what is installed in DB2?

How do I know what is installed in DB2? Is there a way, I can determine about hidden components installed in DB2? How do I know which language packs are installed in DB2? Sometime, you might want to know answers about above questions. Normally, you will run db2level...

read more

SQL PL function to convert int to hex

If you are converting from Sybase, you might run into issues for converting INT to HEX. In Sybase: 1> select inttohex(234) 2> go -------- 000000EA (1 row affected) In DB2: DB2 => values hex(234) 1 -------- EA000000 If you want the same exact behavior from DB2, you...

read more

DB2 SQL -1035 Error (SQL1035N) database is currently in use

Did you get this DB2 SQL1035N error if you were trying to take offline backup? The error is obvious as someone is connected to the database and you cannot take offline database backup.

There are several solution that you can take to do offline backup when you get SQL -1035 error. They are described as below:

Method – Brute Force

C:\>db2 backup database sample to C:\db2backup

The above method is a brute force method and is not recommended. You may get unexpected results at the expense of even corrupting the database. It is not guaranteed that above will work always. You may still get SQL1035N error even after db2_kill. You try to now kill every process with “kill -9” command and still it is not guaranteed to work. It is not at all recommended to use this brute force method.

Method – Mild Force

C:\>db2 force applications all
C:\>db2stop force
C:\>db2 backup database sample to C:\db2backup

The above method is less brute force but you may not be able to use this if there are other databases in your instance. Stopping instance to just take a database backup is not acceptable if there are other databases in the instance.

Method – Correct way

Scenario – Application server has retry logic

Let us say, you want to take offline backup of your database and there is an application server which is designed to connect to the database as soon as it becomes available. The application server has retry logic and it will keep on trying making a connection to a database but your intent is to take an offline database backup. You are stuck as you have to ask your application server administrator to shut down the application server so that you can take up the backup. This may not be sometime possible to get your application server down. The above brute force methods also do not work in this circumstance. The following is the correct way to take offline backup of the database under this scenerio.

read more

How to determine if I have DB2 database with Automatic Storage?

Did somebody create a DB2 database for you and you are just getting started to work on inherited legacy? How do you find if the database was created with automatic storage YES or NO?

Let us do a simple exercise.

My laptop has two drives C: and D: and I have DB2 9 installed.

C:\>db2 create database sample2 on C:,D:
C:\>db2 connect to sample2
C:\>db2 list active databases

Output on my machine was:

                           Active Databases

Database name                              = SAMPLE2
Applications connected currently           = 1
Database path                              = C:\DB2\NODE0000\SQL00004\

Now go to your windows explorer and check following folders:


In Sample2 directory on C: and D:, you have table spaces created on 2 storage paths that you specified when creating SAMPLE2 database.

Check folder C:\DB2\NODE0000\SQL00004\ and this is where your database was created. How do you know how SQL token SQL0004 is linked to the SAMPLE2 database? You got this from the output of LIST ACTIVE DATABASES command.

How do I know about storage paths if some one else had created the database for me? This simple exercise should help you.

Method – A

C:\>db2 connect to sample2
C:\>db2 update monitor switches using bufferpool on lock on sort on statement on table on timestamp on uow on

With above command, I turned on monitor switches for this current DB2 session. I discuss this elsewhere to explain it in more detail. It is not necessary to turn on all switches and above was just an example.

C:\>db2 get snapshot for all on sample2

You will see the following as a part of your snapshot output.

Number of automatic storage paths = 2
Automatic storage path = c:
File system ID = 2149054632
Storage path free space (bytes) = 50386874368
File system used space (bytes) = 49438912512

read more

Cursor Blocking in DB2

Did you ever wonder what is cursor blocking or row blocking in DB2?

Cursor or Row blocking in DB2 is a way to fetch number of rows in a single operation. This can improve performance of the applications.

There are 3 types of blocking which can be used for your cursors.

  • ALL – Cursors specified with READ ONLY and FOR UPDATE OF clause. Blocking of rows will occur.
  • NO – Blocking of rows will not occur
  • UNAMBIGIOUS – Cursors specified with READ ONLY clause. Blocking of rows will occur.

You must have heard the term AMBIGUOUS CURSOR. What is it? An ambiguous cursor is the one where DB2 does not know if you want to do updates or not and hence it assumes that you are going to do the updates. DB2 treats ambiguous cursors as updatable.

Say for an example, this line of code


will open an ambiguous cursor and db2 assumes that you will update the rows. But if you say


DB2 will open an unambiguous cursor for you.

The READ ONLY cursors are unambiguous as a rule of thumb. You may not say explicitly to DB2 to open a cursor as READ ONLY but DB2 may open a cursor as READ ONLY (unambiguous cursor) based upon your query if you are using SORT using full table scan etc.

The locks will be obtained for ambiguous cursors based upon isolation level in effect. If your application is not updating the rows, it is much better to DECLARE those cursors as unambiguous by adding FOR FETCH ONLY or FOR READ ONLY clause to improve concurrency.

But we deviated from our discussion of BLOCKING of cursors to explain about the difference between ambiguous and unambiguous cursors. So, you introduce BLOCKING so that DB2 can fetch the rows in bulk by setting BLOCKING option. This can be set when you pre-compile your embedded SQL program by specifying BLOCKING parameter to either ALL or NONE or UNAMBIGUOUS as explained above.

read more


DB2 provides a tool db2dclgn that you can use effectively as an aid in your C/C++ or Java coding efforts. The example shown here is for embedded SQL in C.

Create a table for demo


INSERT INTO EXE.CUTION VALUES (1234567890,987463453,'UTILITY',4353262,
NULL,NULL,NULL,'How are you',
5325.436436,457437.437437,326326326,CURRENT TIMESTAMP,

INSERT INTO EXE.CUTION VALUES (35367890,987463453,'SOOKING',4353262,

read more

Follow Me on Linked In