DB2 v9.7 - SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2

xx Apr 20xx 09:52:06,363 [quartzScheduler_Worker-3] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;THIS_.ISO_CNTRY_CD, DRIVER=3.50.152
xx Apr 20xx 09:52:06,363 [quartzScheduler_Worker-3] ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;THIS_.ISO_CNTRY_CD, DRIVER=3.50.152

Users were facing this issue after deployment. Tired doing rebind package but it did not work.

Later realized that ISO_CNTRY_CD column does not exist in the table.  they changed the column name in the job and issue got resolved.

Partition detach hanged in DB2 v9.7

I accidentally issued folloiwng command twice and it was showing partition detach status for a while for small table.

alter table T1 detach partition PART5 intoT1_02

db2 list utilities was showing AIC was running for 90 mins..

db2 list utilities show detail | grep -p -i detach
ID                               = 1208
Type                             = ASYNCHRONOUS PARTITION DETACH
Database Name                    = DB1
Partition Number                 = 0
Description                      = Finalize detach for partition '9' of table 'T1' and make table 'T1_02' available
Start Time                       = 01/28/2014 10:48:37.682113
State                            = Executing
Invocation Type                  = Automatic
Progress Monitoring:
      Description                = Waiting for old access to the partitioned table to complete.
      Start Time                 = 01/28/2014 13:08:40.480134

db2pd -util

Database Partition 0 -- Active -- Up 93 days 14:48:44 -- Date 2014-01-28-13.18.33.334414

Utilities:
Address            ID         Type                   State      Invoker    Priority   StartTime           DBName   NumPhases  CurPhase   Description
0x0780000001FAE4C0 1208       ASYNCHRONOUS PARTITION DETACH 1          1          0          Tue Jan 28 10:48:37 DB1  -1          -1          Finalize detach for partition '9' of table 'T1' and make table 'T1_02' available

Progress:
Address            ID         PhaseNum   CompletedWork                TotalWork                    StartTime           Description

I killed the hanging application as per the following link..

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

Hope this helps..

Introduction to Unix LVM

the following link gives nice introduction to LVM aka logical volume manager

unix LVM

http://www.youtube.com/watch?v=BysRGDgqtwY

DB2 V9.7 - db2fmpterm

This is new for me. Today i encountered high memory usage by fenced user id in V9.7 FP 6 running on AIX.
FMP process count is unusually high and contributing to high memory usage.

We restarted instance to fix the issue. However, meanwhile IBM suggested to use
" db2fmpterm <pid of offending> " to kill fmp process rather than recycling the instance.

Hope this helps.

DB2 V9.7 - Reduce Tablespace Size

I have got an opportunity to work with different versions of DB2 LUW environments.
This is what I follow if I need to reduce the size/hwm of tablespace.

For TBS migrated to V9.7 -with Automatic storage  - alter tablespace tbs reduce
For TBS migrated to V9.7 - without Automatic storage  - alter tablespace tbsname reduce (all 1000)   - 1000 is no.of pages
For TBS created in V9.7 - Automatic storage - alter tablespace tbsname reduce max
For TBS created in V9.7 - without Automatic storage - alter tablespace tbsname lower high water mark

Monitor the progress in V9.7 using below command

select varchar(tbsp_name, 15) as tbsp_name, tbsp_state from table (mon_get_tablespace('TBS1',-2)) as t;

Hope this helps.

DB2 V9.7 Audit setup - Quick Steps

These steps are from my Run notes. This may help one of you who is looking for quick audit set up.


db2audit is changed from V9.5.  From V9.5, audit is in two levels. One is at Instance level and another is at DB level.

This is how I configure for any new DB set up.

Instance level
---------------
db2audit configure reset
db2audit configure scope audit status BOTH, CHECKING STATUS FAILURE, OBJMAINT STATUS BOTH, SECMAINT STATUS BOTH, SYSADMIN STATUS FAILURE, VALIDATE STATUS BOTH,CONTEXT STATUS NONE datapath /auditdata/ archivepath /auditarch/
db2audit start

Verify Instance level setting with below command
db2audit describe

DB Level
-----------
db2 "connect to <dbname>"
db2 "CREATE AUDIT POLICY CHECK_AUDIT CATEGORIES   VALIDATE STATUS BOTH,  CHECKING STATUS NONE, OBJMAINT STATUS BOTH, SECMAINT STATUS BOTH, CONTEXT  STATUS NONE, AUDIT    STATUS BOTH,  SYSADMIN STATUS BOTH   ERROR    TYPE   AUDIT"
db2 "AUDIT DATABASE USING POLICY CHECK_AUDIT"

Verify DB  level setting with below command
db2 "select substr(AUDITPOLICYNAME,1,25) policy,OBJECTTYPE,SUBOBJECTTYPE,substr(OBJECTSCHEMA,1,20) schema,substr(OBJECTNAME,1,20) object from syscat.audituse"

db2 "select substr(AUDITPOLICYNAME,1,10) policy,AUDITSTATUS,CONTEXTSTATUS,VALIDATESTATUS,CHECKINGSTATUS,SECMAINTSTATUS,OBJMAINTSTATUS,SYSADMINSTATUS,EXECUTESTATUS,EXECUTEWITHDATA from syscat.auditpolicies"

Hope this helps...

DB2 LUW - Online Reorg

In DB2 LUW, Online reorg of table is Asynchronous which means if you trigger the command DB2 immediately gives message like "completed successfully" but it runs in the background.
Lets say you have a set of tables and would like to perform Online reorg on them using a script.
If you trigger a script it gets completed in few seconds however, DB2 runs them in the back ground (Read Online reorg of a table is Asynchronous). System may become IO bound.

Following function checks if online reorg on a table is completed and if it is, then reorg for next table gets triggered.

sysproc.snapshot_tbreorg - Table function being used
Reorg_status = 4 <if online reorg is completed>

online_reorg()
{
    unset PID
    echo "."
    echo "###Online Reorg started on ${TBNAME} at `date`"
    db2 -v "reorg table ${SCHEMA}.${TBNAME} inplace allow write access" &
     sleep 1
    unset CHK

    while true;do
        CHK=`db2 -x "select REORG_STATUS from table(sysproc.snapshot_tbreorg('',-1))as t where table_schema in '${SCHEMA}' and table_name in '${TBNAME}'"`
        if [ $CHK = 4 ];then
           echo "###Online Reorg completed on ${TBNAME} at `date`"
           break
        else
           sleep 2
        fi
    done
   }


Hope this function helps you...

Thanks for reading.