Category Archives: DB2 LUW

determine the installed XL C/C++ for AIX compiler version

$ /usr/vacpp/bin/xlC -qversion
IBM XL C/C++ for AIX, V11.1 (5724-X13)
Version: 11.01.0000.0015
$ /usr/vac/bin/xlc -qversion
IBM XL C/C++ for AIX, V11.1 (5724-X13)
Version: 11.01.0000.0015






db2: find out agent id from PID

Scenario: The below steps explain to find out AGENT ID for a process id: 20513654

Step1: Get snapshot for all applications running

db2 “connect to TESTDB1”

db2 “get snapshot for applications on TESTDB1” > /tmp/err2

Now, open /tmp/err2 and search for 20513654. Then you see content as below in /tmp/err2:


Application Snapshot

Application handle = 7978
Application status = UOW Waiting
Status change time = 04/11/2017 07:36:23.948027
Application code page = 819
Application country/region code = 1
DUOW correlation token = *LOCAL.db2inst1.170411082659
Application name = cicsas
Application ID = *LOCAL.db2inst1.170411082659
Sequence number = 00001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 04/11/2017 04:26:59.284367
Connect request completion timestamp = 04/11/2017 04:26:59.284827
Application idle time = 29 minutes 18 seconds
CONNECT Authorization ID = CICS
Client login ID = cics
Configuration NNAME of client = SERVER1
Client database manager product ID = SQL10014
Process ID of client application = 20513654
Platform of client application = AIX
Communication protocol of client = Local Client

Inbound communication address = *LOCAL.db2inst1


Step2: From above, the agent id is 7978

/mnt/db2home:> db2 list applications | grep -i 7978
CICS cicsas 7978 *LOCAL.db2inst1.170411082659 TESTDB1 1

Step3: Kill the application id 7978

/mnt/db2home:> db2 “force application (7978)”
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.




db2 luw: rename a column

Below is an example to rename a column:

alter table prod.TEST_line_2016 rename “SHIP_” to “SHIP_$_MONTH”

SQL2314W Some statistics are in an inconsistent state. (SQLSTATE=01650)

Problem: Received below error while collecting statistics on a table PROD.SALES_RK_2016

SQL2314W  Some statistics are in an inconsistent state. The newly collected “TABLE” statistics are inconsistent with the existing “INDEX” statistics.


Issuing RUNSTATS on the table only may result in a situation where the table level statistics are inconsistent with the already existing index level statistics.

Likewise, issuing RUNSTATS for indexes only or during index creation may leave table level statistics in an inconsistent state.

For example, if index level statistics are collected on a particular table and later a significant number of rows is deleted from this table, issuing RUNSTATS on the table only
may end up with the table cardinality less than FIRSTKEYCARD which is an inconsistent state.

Hence, Issue a RUNSTATS to collect both table level and index level statistics.

Below is the runstats command to collect both table and index statistics for SALES_RK_2016 table in PROD schema:

db2 “runstats on table PROD.SALES_RK_2016 with distribution and detailed indexes all”


How to get immediate relief when log space get full?

Last weekend, on one of my production servers, we started seeing below errors in db2 diagnostic log (db2diag.log):

(1) The transaction log for the database is full

(2) MESSAGE : ADM1823E  The active log is full and is held by application handle


Below are DB level configuration parameters that interest in long transactions:

db2 get db cfg for <Database> | egrep “LOGPRIMARY|LOGSECOND|NUM_LOG_SPAN|MAX_LOG”
Number of primary log files (LOGPRIMARY) = 240
Number of secondary log files (LOGSECOND) = 15
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

From above configuration parameters, below are various observations:

(1) In worst case, maximum number of LOG files that will be allocated are: 240 + 15 = 255

(2) There was a very old transaction/application started in log file:1 and spanned till log file:255, and still active. Unfortunately, this is long transaction is still looking for log space

(3) Transaction is still active and looking for next log file, but we have reached a limit of:255. So there is no way we can allocate 256th log file.



(1) Change LOGSECOND to -1 as below. This allows for infinite logging. In this case, an infinite number of LOGSECOND log files are allocated.

db2 “update db cfg for <Database> using LOGSECOND -1 IMMEDIATE”

(2) Kill the application that is doing this oldest long transaction. Below is a nice technote from IBM on this subject:




db2 luw : Grant all privileges to public

The below grant will be used to grant all privileges on a ACCT_CUST table in PROD schema to public:

grant all on PROD.ACCT_CUST to public


DB2 LUW: Create (vs) Declared Global Temporary table

The main difference between declared and create global temporary tables is the SCOPE.

Declared Global Temporary table: Current session

Create Global Temporary table: At current server level. So, it exists for all users who connect.

DB2 LUW Express Server edition discontinued

As per IBM, IBM DB2 LUW Express server edition (DB2 Express) is discontinued from DB2 LUW 11.1.


IBM DB2 Express Server Edition (DB2 Express) will no longer be available for purchase starting on September 23, 2016. This offering will not be part of the introduction for DB2 11.1 for Linux, UNIX and Windows family. Existing DB2 Express license holders can replace those licenses and continue support with DB2 Workgroup Server Edition.



db2 luw: Standard steps to drop a database

Below are steps to drop TESTDB database:


QUIESCE: This will ensure that no other users are currently connected to the database.


This database configuration parameter specifies the number of database backups to retain for a database. This parameter is configurable online.

After the specified number of backups is reached, old backups are marked as expired in the recovery history file. Recovery history file entries for the table space backups and load copy backups that are related to the expired database backup are also marked as expired. When a backup is marked as expired, the physical backups can be removed from where they are stored (for example, disk, tape, TSM). The next database backup prunes the expired entries from the recovery history file.

The rec_his_retentn configuration parameter should be set to a value compatible with the value of num_db_backups. For example, if num_db_backups is set to a large value, the value for rec_his_retentn should be large enough to support the number of backups set as num_db_backups.

If both the num_db_backups and rec_his_retentn configuration parameters are set, backups are not removed unless both the num_db_backups and rec_his_retentn conditions are satisfied.