DB2

Seven commands developers can run to resolve issues in DB2 AIX development

Leave a comment

Now here’s a super geeky one for you, but also super helpful when doing DB2 dev on AIX. Thanks to one of our App DBAs for providing this!

Please note that you will need DBADM rights to do some of these things so your mileage may vary.

First you need to determine what command(s) to run…

  • REORG(#1), RUNSTAT(#5), REBIND (#6) as a first step when performance is suffering
  • REORG(#1) when you recieve a SQL0668N reason code 7 or any other SQL message indicating a REORG is required
  • REORG(#1) after table alters or deleting lots of rows when you want to reclaim space.
  • REORG(#2) when you recieve a ADM6044E or a SQL2216N SQL error “-289” on a REORG (#1)
  • CLEAR LOAD PENDING (#3) when you recieve a SQL0668N reason code 3
  • RUNSTAT(#5) after REORGs(#1 or #2), data volume changes(+10%), and/or adding indexes
  • EMPTY A TABLE WITH A LOAD REPLACE(#4) when you want to empty a medium or larger sized table
  • REBIND(#6) after every RUNSTAT(#5) if you are using stored procedures
  • SET INTEGRITY(#7) to fix SQL0668N reason code 1

Then change the command syntax to match the required objects. All parameters starting with a dollar sign($) e.g. $TABSCHEMA, $TABNAME, $ROUTINESCHEMA, $ROUTINENAME, and/or $STEMP_TBSPACE must be replaced with valid table schema/name, stored procedure schema/name, system temporary tablespace.

Note: RAPID SQL 7.7.5 Issue: Add cast syntax (CAST(‘command-syntax’ AS CLOB)) if your version of Rapid SQL returns an error
Example: CALL SYSPROC.ADMIN_CMD (CAST(‘REORG TABLE $TABSCHEMA.$TABNAME ALLOW READ ACCESS’ AS CLOB));

Finally, run the commands via any interface that can call stored procedures including RAPID SQL

Other resources for troubleshoot messages or research other options for the commands supplied below by searching these

1. REORG A TABLE

CALL SYSPROC.ADMIN_CMD ('REORG TABLE $TABSCHEMA.$TABNAME ALLOW READ ACCESS');

2. REORG A LARGE TABLE

CALL SYSPROC.ADMIN_CMD('REORG TABLE $TABSCHEMA.$TABNAME ALLOW READ ACCESS USE $STEMP_TBSPACE');

Note: To find $STEMP_TBSPACE:

SELECT TBSPACE AS $STEMP_TBSPACE, PAGESIZE FROM SYSCAT.TABLESPACES WHERE DATATYPE = 'T' WITH UR;

3. CLEAR A LOAD PENDING **This leaves the table empty**

CALL SYSPROC.ADMIN_CMD('LOAD FROM /dev/null OF DEL TERMINATE INTO $TABSCHEMA.$TABNAME NONRECOVERABLE');

4. EMPTY A TABLE WITH LOAD REPLACE

CALL SYSPROC.ADMIN_CMD ('LOAD FROM /dev/null OF DEL REPLACE INTO $TABSCHEMA.$TABNAME NONRECOVERABLE');

5. RUNSTAT A TABLE

CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE $TABSCHEMA.$TABNAME ON ALL COLUMNS AND SAMPLED DETAILED INDEX ALL ALLOW WRITE ACCESS');

6. REBIND A STORED PROCEDURE

CALL SYSPROC.REBIND_ROUTINE_PACKAGE('P','$ROUTINESCHEMA.$ROUTINENAME','ANY');

7. SET INTEGRITY

SET INTEGRITY FOR $TABSCHEMA.$TABNAME IMMEDIATE CHECKED;
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply