Hello db2 DBAs I recently had an interesting situation, which seemed on the face of it highly unusual. So I wanted to share with this group. PROBLEM: Cust called in indicating he has inserted 32 rows into a table.  Then he runs the command db2 "select count (*) from <tabname>" returned correct 32 value. Then he ran the command db2 "select * from <tabname>" However this returned just one row!!!    WHY ? db2 has encountered a bug or what? My thought processes in debugging this was as follows: 1. Are... [More]

Tags:  not_enforced db2 optimizer informational_constraints

The auto maintenance messages  with evaluation of  reorg, runstats  are logged in the db2diag.log every two hours.  These are part of auto maintenance setup in the database. Policy defined  to run  reorg or runstats  during a certain maintenance window supposed to run  reorg,  runstats only during those time windows  defined by the policies. The real   reorg or,  runstats will follow the guideline defined by the auto maintenance policies. But, the evaluation  of the same are... [More]

Tags:  auto-reorg db2 policy

The actual cleanup logic looks at a bunch of things in order, for example, invalid entries, empty entries that got left behind, and eventually evict entries based on least recently used order. The package cache daemon would try to do cleanup if the package cache is at least 90% full. Fixing the package cache size does not affect how the package cache daemon behaves (e.g. how it chooses when and what to cleanup). When PCKCACHESZ is set to AUTOMATIC to be tuned by STMM, there's logic that looks at how many times the package cache has been... [More]

Tags:  db2 package logic eviction cache

The package cache daemon does cleanup exactly the same way how we used to do cleanup in pre-v10.1. The only difference is that in pre-v10.1, the cleanup is triggered on demand when we need memory for new entry in the package cache and we don't have enough, so it's the agent carrying out the request that does the cleanup work. With the package cache daemon, we have a background agent that does the clean-up proactively so it doesn't impact performance for regular applications. The package cache daemon gets kicked in every 10 seconds... [More]

Tags:  package db2 daemon cache

If  RTS (real time statistics)  is enabled then DB2 will collect the statistics internally. If no  RTS is used then it  will use whatever statistics  were present  from before  when a  statistics  was collected last. If the  table never had  statistics  then it  requests the storage layer to tell  the physical size of the table. Then  it will use the physical size, coupled with the UDI  (Update Delete Insert) counter, to fabricate statistics. A  fabrication... [More]

Tags:  initial runstats statistics db2 fabrication udi

A common  mistake  many people make is,   consider a  query  with parameter marker same as the query with  the parameter marker  replaced by literals. As soon as  the parameter markers in a query is replaced by literals  it's not the same query. Basically,  during query  compilation a  query with parameter marker provides much lesser intelligence to the DB2 compiler.  As  a result the access plan chosen by DB2 optimizer might be sub-optimal compared to  a query... [More]

Tags:  reopt db2 cost query

Why is the level classified as Warning, where as the description say Sever/Error messages for the following STMM entries in the db2diag.log?   2016-03-14-04.28.31.397257-240 I19884922E702         LEVEL: Warning PID     : 02028                TID : 140000802021983 PROC : db2sysc 0 INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE APPHDL  : 0-46194          ... [More]

Tags:  stmm_severe_error warning db2 stmm db2diag.log

Some older DB2 levels experienced this problem. If   the database cannot be restored using an earlier  good backup then  following way might help, The SYSCOLDIST  table contains the distribution statistics. So,  first  a  fresh runstats for all objects could be  run with distribution option  and checked if that can refresh that table. It might fail  due to the presence of invalid index.   In that case,  if  it's o.k. to get rid of all the current  distribution... [More]

Tags:  invalid db2 db2dart syscoldist -901 index

db2look is the tool  which could be used to achieve this purpose. http://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html?lang=en   From DB2 10.1  onwards there is a  -createdb  option  which helps to get the original create database syntax. The create database syntax is put at the beginning of  db2look output, so that, when it's used as a  script to recreate the database,  the  first thing  which will be invoked  is the create ... [More]

Tags:  db2look createdb db2

Auto  reorg  is enabled. But,  it's  failing with  SQL0284 messages in the db2diag.log  similar to following,   2016-03-02-08.39.56.808159-360 I4469422E526          LEVEL: Error PID     : 11111                TID : 46912895825555  PROC : db2acd 0 INSTANCE: db2inst1              NODE :... [More]

Tags:  reorg systoolstmpspace sql0284 auto db2 -284

Here is an example of the problem description:   Create Workload: -- db2 "SET WORKLOAD TO SYSDEFAULTADMWORKLOAD"   db2 "CREATE WORKLOAD WL_A SESSION_USER('DB2V10')" db2 "CREATE WORKLOAD WL_B SESSION_USER('JKGERA')"  db2 "ALTER WORKLOAD WL_A COLLECT AGGREGATE ACTIVITY DATA BASE"   db2 "ALTER WORKLOAD WL_B COLLECT AGGREGATE ACTIVITY DATA BASE"   db2 "GRANT USAGE ON WORKLOAD WL_A TO PUBLIC"   db2 "GRANT USAGE ON... [More]

Tags:  mon_statistics_wlstats_pm... db.mon_statistics_wlstats... priority db2 event monitor wlm

It's possible some of the  tables showing  STATS_TIME  "-"  in  syscat.tables catalog table  even when the auto-runstats in the database is tunred  ON. The main reason might be, if a table is never accessed, then auto-runstats will not action on  that table, even if the table has never had runstats done. This is by-design.  This  could be applicable for system tables as well as  user tables.   Regarding system catalog tables there might be differences of observations from... [More]

Tags:  db2 stats_time auto-runstats

Commonly  if  db2stop/db2start  fail  with I/O  errors  it  might point  toward a corruptions of some kind at filesystem level. Or,  either instance binaries are corrupted or, database catalogs /directories have issues. But, before getting worried  do a quick check with  db2diag.log  messages with db2stop/db2start db2diag -A db2stop And, then check the db2diag.log entry. It  might fail with I/O error   even if an important  file system like /home  is ... [More]

Tags:  error db2start i/o db2 db2stop

When the database  recovery happens  DB2  retrieves the transaction log files and use those for rollforward. If  the logs are compressed ones  then it first  DB2 uncompress those to same name except .EXP extension before using that. In-place uncompress also possible if logs are uncompressed manually using internal log uncompress tool. This is one reason,  the  DB2  cannot  use a compressed log in a read-only  filesystem  as it  cannot write the .EXP file  there.

Tags:  .exp db2 read-only uncompress