Generate dummy data using SQL on DB2 LUW

Not sure where I got this query, but is very handy for generating sample data, try this.

-- columns SSN,FIRST_NAME,LAST_NAME,JOB_CODE,DEPT,SALARY,DOB  
WITH TEMP1 (s1,r1,r2,r3,r4) AS   (
VALUES
(0   ,RAND(2)   ,RAND()+(RAND()/1E5)   ,RAND()* RAND()   ,RAND()* RAND()* RAND())    
UNION
ALL   SELECT
s1 + 1   ,
RAND()   ,
RAND()+(RAND()/1E5)   ,
RAND()* RAND()   ,
RAND()* RAND()* RAND()  
FROM
TEMP1  
WHERE
s1 < 10 --rows   )   SELECT
SUBSTR(DIGITS(INT(r2*988+10)),
8) ||'-'||           SUBSTR(DIGITS(INT(r1*88+10)),
9) || '-' ||           TRANSLATE(SUBSTR(DIGITS(s1),
7),
'9873450126',
'0123456789'),
CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))|| CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),
'aaeeiibmty',
'0123456789'),
CASE            
   WHEN INT(r4*9) > 7 THEN 'MGR'            
   WHEN INT(r4*9) > 5 THEN 'SUPR'            
   WHEN INT(r4*9) > 3 THEN 'PGMR'            
   WHEN INT(R4*9) > 1 THEN 'SEC'            
   ELSE 'WKR'          
END,
INT(r3*98+1),
DECIMAL(r4*99999,
7,
2),
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS  
FROM
TEMP1

DB2 LUW - Drop Schema and all objects under it

Did you ever try to drop schema in DB2 LUW, lot of DBA's face that situation quiet often when building databases for new applications. The command to drop a schema in DB2 LUW 9 is as follows,

DROP SCHEMA DB2INST1 RESTRICT;

But if there are objects undneath that schema you will get following return message.

DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "SCHEMA" cannot be processed because there is an object "DB2INST1.TEST_TAB", of type "TABLE", which depends on it.. SQLCODE=-478, SQLSTATE=42893

You have drop all the child objects under the given schema first and then drop the schema.

So the other good workaround to this is "ADMIN_DROP_SCHEMA", usage is explained below.

db2 "CALL SYSPROC.ADMIN_DROP_SCHEMA('SAMPLE', NULL, 'ERRORSCHEMA', 'ERRORTABLE')";

Where,

"SAMPLE" is the schema name.

NULL is reserved for future use.

'ERRORSCHEMA' specifies the schema name of a table containing error information for objects that could not be dropped. The name is case-sensitive. This table is created for the user by the ADMIN_DROP_SCHEMA procedure in the SYSTOOLSPACE table space. If no errors occurred, then this parameter is NULL on output.

'ERRORTABLE' specifies the name of a table containing error information for objects that could not be dropped. The name is case-sensitive. This table is created for the user by the ADMIN_DROP_SCHEMA procedure in the SYSTOOLSPACE table space. This table is owned by the user ID that invoked the procedure. If no errors occurred, then this parameter is NULL on output. If the table cannot be created or already exists, the procedure operation fails and an error message is returned. The table must be cleaned up by the user following any call to ADMIN_DROP_SCHEMA; that is, the table must be dropped in order to reclaim the space it is consuming in SYSTOOLSPACE.

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.