Recovering from a failed LOAD operation in DB2 LUW

Have you ever dealt with situation where you have to bring a table to a normal state from the faild load condition and you don't know what load command the user used ?
When you do a db2 load and if that terminates with error then the table sits in integrity pending state, below are the sql statements and the sysmtoms of the situation

SELECT * FROM DB2INST1.EMPLOYEE
Operation not allowed for reason code "1" on table "db2inst1.employee".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.8.86

Based on the above error code you attempt

SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0668N  Operation not allowed for reason code "3" on table
"db2inst1.employee".  SQLSTATE=57016

SQL0668N - reason code "3" - states that

Cause:
        The table is in the Load Pending state. A previous LOAD attempt
         on this table resulted in failure. No access to the table is
         allowed until the LOAD operation is restarted or terminated.

Resolution:

         Restart or terminate the previously failed LOAD operation on
         this table by issuing LOAD with the RESTART or TERMINATE option
         respectively.

Caution: below steps will truncate the data in your table.

Recovery Steps:

touch test.del ...just to create some dummy file (zero bytes)
LOAD FROM 'test.del' OF DEL TERMINATE INTO db2inst1.employee .. I did not know what the original load command was.
SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED

After these steps your table should be back to normal and is usable.

Duplicate Permission/Privileges in DB2 LUW 9

Accidentally I and my Co-DBA discovered this. In DB2 LUW V9, when 2 different id's grant certain privileges  to a user/id the privilege entry in *AUTH tables are multiplied for each GRANTOR. If you run db2look and script out all the privileges you see multiple grants statement for a the same object with same privileges.

How to reproduce this, login as user X and grant SELECT on some table named TEST for some user named  "sam" and then log-out and log back in as user Y and do the same. Now run db2look as follows.

"db2look -d SAMPLE -x -o db2look_grant.out"

examine the "db2look_grant.out" and you will see 2 lines with "grant select on table TEST to user SAM"

This is not a big issue but this will simply increase the size of your syscat.*auth tables. And in worst cases DB2 might be spending significant time in reading these tables. So, to keep this problem in check I came-up with following script. I have tested this in my environment - it worked and it did not cause any issues, but you use your own due diligence.

#!/bin/bash # # read from file and INSERT into DB table # clear if [ $# -le 0 ] ; then echo "Usage: grant_cleanup_v1.sh " exit fi DB=$1 echo "Connecting..." db2 connect to $DB > /dev/null 2>&1 db2 "CREATE TABLE GRANTS_CLEANUP (STMT VARCHAR(2000))" > /dev/null 2>&1 echo "Generating db2look output..." db2look -d $DB -x -o db2look_grant01.tmp > /dev/null 2>&1 grep "GRANT " db2look_grant01.tmp > db2look_grant02.tmp db2 "TRUNCATE TABLE GRANTS_CLEANUP REUSE STORAGE IMMEDIATE" > /dev/null 2>&1 echo "Inserting into table..." grep "GRANT " db2look_grant02.tmp | while read stmts do db2 "INSERT INTO GRANTS_CLEANUP VALUES ('$stmts')" > /dev/null 2>&1 done # -- generate grants db2 -x "SELECT TRIM(STMT) FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > re-grants.dcl # -- generate REVOKEs db2 -x "SELECT REPLACE(REPLACE(REPLACE( STMT, 'GRANT ', 'REVOKE '), 'WITH REVOKE OPTION', '' ), ' TO ', ' FROM ') FROM GRANTS_CLEANUP GROUP BY STMT HAVING COUNT(1) > 1" > revokes.dcl echo "DCL scripts are ready for your review !" #rm db2look_grant*.tmp db2 "DROP TABLE GRANTS_CLEANUP" > /dev/null 2>&1 # end




This script will create 2 output files,

revokes.dcl - Review and run this to clean the duplicated privileges, caution - this will revoke all the privileges that are found to be duplicated and in the next step you re-grant then but till then users/applications will fail.

re-grants.dcl - Again, review and run this script to apply the privileges. But now there will be single set of privileges. You can verify this by running the db2look again.