Following  is a good  document  on  possible reasons  why  db2 connections could  be slow, http://www-01.ibm.com/support/docview.wss?uid=swg21455469   I  just  wanted to highlight  one more area to consider   for the connect slowness. Sometimes a  database   which  might not be continuously  in use. Connections are happening  in low numbers and then being terminated  and there is a silent  period  after that before  some more... [More]

Tags:  slow db2luw ibm-blog db2 connection

This  is a common question we face. A  db2   registry  was set. Then, a  problem faced. The  collected data showed  it's   set currently  from  db2set  command. But,   how to  know if  the  registry was  in effect  and  not  just  showing in the output of  db2set   command ? Manyt  of the db2 registry settings are  not  effective  until  and unless   the instance is ... [More]

Tags:  registry ibm-blog effective db2set env_get_reg_variables

Just wanted to share an example with sample database  on how to do a SSL  loopback in Db2LUW. db2 catalog tcpip node LOOPNODE  remote  MYHOST  server 21212 security ssl That  shows, $ db2 list node directory  Node Directory  Number of entries in the directory = 1 Node 1 entry:  Node name                      = LOOPNODE... [More]

Tags:  ibm-blog loopback ssl db2luw

Basic Guideline for  rebuilding a  Db2LUW database  : Create a  temp directory to store  the  outputs  of  db rebuild and  go to that directory. Save  all the DDLs  for the database  : db2look -d  <db-name>  -a -e -m -l -x -f -o   db2look.out refer :   https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.1.0/com.ibm.db2.luw.qb.upgrade.doc/doc/t0053156.html Save   config  of the database : db2  get db config ... [More]

Tags:  db2mov ibm-blog db2luw corruption rebuild db2dart ddel

Why db2luw instance went  down suddenly without any associated diagnostic dumps.   This is something  explained as part of different documents   But,  still been asked on this currently. So,   I  thought to write few lines on that.   First of all  we need to understand how db2  dumps any diagnostics when it experience a  crash or,  trap situation. Db2 has it's own  signal handler which  handles the UNIX signals to invoke it's signal handler function/routine... [More]

Tags:  dump trap sigkill db2luw crash adm0503c db2wdog ibm-blog

Here is an example about how to perform an incremental restore with rebuild option. In the original database, we have 2 tablespaces created, TBSP01 and TBSP02. When doing incremental db restore, we are going to restore only one of them. The other one will not be available in restored database.   db2 create db MYDB01 db2 update db cfg for MYDB01 using logarchmeth1 logretain TRACKMOD on db2 terminate db2 backup db MYDB01 to /dev/null db2 connect to MYDB01 db2 create tablespace tbsp01 db2 create tablespace tbsp02 db2 "create table t01... [More]

Tags:  db2

 There are two independent insertions on table T01, one after another. The insertion on table T01 will trigger a stored procedure call which then writes a row into another table T02. Will the sequence of insertions on T01 guarantee the sequence of insertions on T02? Let's take a look into the scenario:   Point in time: A ----------> B --------------------> C           Insert #1 ---------->  calls SP1() ---------> insert #3   Point in time: X ----------> Y... [More]

Tags:  db2

  It is easily possible to identify the amount of space consumed by tables in a given tablespace. This is required when size of tables increases to reach the size limit or makes tablespaces have unequal data distribution which might eventually lead to bad performance mainly for backups.     Lets consider an example and take a look at the top 3 space consuming tables in tablespace 3 :   $ db2 "SELECT substr(a.TABSCHEMA,1,12) sc, substr(a.TABNAME,1,12) tb, SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LONG_OBJECT_P_SIZ... [More]

Tags:  highest tables size tablespace consumer

User wants to set up Db2 audit to monitor their database. Unfortunately, Db2 audit does not work as expected and does generate the expected output files.   Debugging the issue : 1> User has set up Db2 audit on system as follows: $ db2audit describe DB2 AUDIT SETTINGS: Audit active: "TRUE " Log audit events: "FAILURE" Log checking events: "FAILURE" Log object maintenance events: "FAILURE" Log security maintenance events: "BOTH" Log system administrator events: "BOTH" Log... [More]

Tags:  audit db2 db2audit

To migrate a DB2 database from AESE to ESE you will have to :  1. Install a new DB2 ESE software copy. (this should be done in a separate installation directory of course) 2. Migrate your DB2 instance to the new ESE copy using the command db2iupdt. Tthis db2iupdt command should be run from the NEW installation path.      Please refer the following links, which should be able to show you how the migration from one db2 product to another works : IBM link for converting AESE to ESE : Converting instances from one DB2 database... [More]

Tags:  ese product db2 aese migrate

  db2trc with any options may fail with following error sometimes (even after selecting higher buffer size):      "Unexpected error: Trace buffer size is too small”.   This might occur more often when trying to run db2trc on a db2 cli client.        db2trc buffer allocation :  It will be allocated on db2start or when "db2trc on” command is run it will allocate trace buffer if it has not been created yet.  When a new trace buffer is being created, the size can be... [More]

  We recently had a case where customer was attempting to get the EXPLAIN PLAN of a query and kept getting SQL0220N with wrong schema name.   Steps followed were :  1> SET SCHEMA B 2> CALL SYSPROC.SYSINSTALLOBJECTS('EXP LAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128))) Then explain plan was run on desired SQL statement :   EXPLAIN PLAN FOR <SQL statement>  This returned following error, but note that it was for a different SCHEMA SYSTOOLS and not for B : DB21034E The... [More]

It is important to check database is eligible to be upgraded BEFORE actually issuing upgrade database command: db2ckupgrade YourDB -l /tmp/db2ckupgrade.log If you find any errors returned by the above command, fix the errors found until db2ckupgrade reports you can upgrade database.   If you have already issued upgrade database command and errors are returned in the process of database upgrade, chances are database files such as database configuration file, history file, or log control files(LFHs) are already modified. You may need to... [More]

Came across a behavior where Db2 Reorg was taking atleast 3X times longer in GDPC environment as opposed to a regular purescale environment.   We gathered various performance monitoring and diagnostic data (eg db2mon reports, db2trc etc) and reviewed the data /repro in lab. From the db2mon data, we can see that WAR (writing a new version of the page, which goes to both cf's) and RAR (read and register page) calls from a member to the CF on the other site take longer in a GDPC env. For example, the avg time taken for WriteAndRegister... [More]