Category Archives: DB2 LUW Real Time Scenarios

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.

 

Thanks!

 

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.
SQLSTATE=01650

Solution:

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.

***

Solutions:

(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:

http://www-01.ibm.com/support/docview.wss?uid=swg21664899

 

Cheers!
Ravi

 

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: Standard steps to drop a database

Below are steps to drop TESTDB database:

db2 “CONNECT TO TESTDB”
db2 “UNQUIESCE DATABASE”
db2 “QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS”
db2 “UNQUIESCE DATABASE”
db2 “CONNECT RESET”
db2 “DROP DATABASE TESTDB”

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

DB2: Various monitoring scripts

(1) Check High Cpu Utilization

1_cpu_check.sh

(2) Swap high utilization check

2_swap_check.sh

(3) Transactional Log utilization

3_Transactional_logs_utilization.sh

(4) Check backup failures

4_backup_fail_check.sh

(5) Backup Running Longer

5_backup_running_long.sh

(6) Diag log monitoring for Severe/Error/Critical errors

6_db2diag_monitor

 

 

db2 luw: Running SQL Script from Command Prompt

Question) Sometimes developers provide an SQL file to DBA to promote to Production. How to run such SQL file from Unix/Linux environment?

Solution:

You can use “db2” command with below options: (db2 list command options)

-t    Set statement termination character (default is semicolon)

-s    Stop execution on command error

-v    Echo current command

-f    Read from input file

Scenario:1   When we receive an .sql file without any line termination (i.e., New Line as line termination)

[db2inst1@rhel1 tmp]$ cat a1.sql
connect to testdb3
select * from t1

$ db2 -vsf ./a1.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Scenario:2  When we receive an .sql file without semicolon (;)

$ cat a2.sql
connect to testdb3;
select * from t1;
[db2inst1@rhel1 tmp]$ db2 -tvsf ./a2.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: By default -t take semicolon as line terminator

Scenario:3  When we receive an .sql file without different terminator(@)

$ cat a3.sql
connect to testdb3@
select * from t1@
$ db2 -td@ -vsf ./a3.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Note: To define termination characters 1 or 2 characters in length, use -td followed by the chosen character or characters. For example, -td%% sets %% as the statement termination characters.

Scenario:4  Define more than 1 termination character

$ cat a4.sql
connect to testdb3@$
select * from t1@$
[db2inst1@rhel1 tmp]$ db2 -td@$ -vsf ./a4.sql
connect to testdb3

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = TESTDB3
select * from t1

C1
——————————
Before full backup:1

1 record(s) selected.

Find out DB2 instance running port number (UNIX/Linux)

(Question): How to find out port number of my DB2 instance?

Answer: Port number is defined at instance level through SVCENAME configuration variable.

/mnt/db2home:> db2 get instance

The current database manager instance is:  db2inst1

/mnt/db2home:> db2 get dbm cfg | grep SVCENAME

TCP/IP Service name                          (SVCENAME) = db2c_db2inst1

/mnt/db2home:> grep db2c_db2inst1 /etc/services

db2c_db2inst1        50000/tcp

 

From above, 50000 is the port number. Sometimes you directly find port number given to SVCENAME as below:

/mnt/db2home:> db2 get dbm cfg | grep SVCENAME

TCP/IP Service name                          (SVCENAME) = 50000

 

Nickname creation & load data

This is continuation to following post: https://rk.guru/2016/07/29/db2-luw-federation-between-2-databases-within-same-instance/

The below example talks about

(1) Nickname creation, and

(2) Loading data using nickname created

****

db2 => connect to feddb2

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => create table t1_fed2 (c1 char(30))
DB20000I The SQL command completed successfully.

db2 => create nickname db2inst1.t1_fed1_nick for server1.db2inst1.t1
DB20000I The SQL command completed successfully.

db2 => declare c cursor for select * from db2inst1.t1_fed1_nick
DB20000I The SQL command completed successfully.

db2 => load from c of cursor replace into db2inst1.t1_fed2 NONRECOVERABLE
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.

SQL1193I The utility is beginning to load data from the SQL statement ”
select * from db2inst1.t1_fed1_nick”.

SQL3500W The utility is beginning the “LOAD” phase at time “07/29/2016
06:01:48.276446”.

SQL3519W Begin Load Consistency Point. Input record count = “0”.

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. “2” rows were read from the
input file.

SQL3519W Begin Load Consistency Point. Input record count = “2”.

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the “LOAD” phase at time “07/29/2016
06:01:48.692010”.
Number of rows read = 2
Number of rows skipped = 0
Number of rows loaded = 2
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 2

db2 => drop nickname db2inst1.t1_fed1_nick
DB20000I The SQL command completed successfully.

db2 => runstats on table db2inst1.t1_fed2 ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL
DB20000I The RUNSTATS command completed successfully.

db2 => commit work
DB20000I The SQL command completed successfully.
db2 => load query table db2inst1.t1_Fed2
Tablestate:
Normal