Any DBA worth his or her salt has always eschewed Cartesian products (unless, of course, he or she is keen for a bit of on-call overtime, but that’s for another blog entry). However, with judicious usage, they can be helpful indeed to a time-constrained DBA who has repetitive tasks to, um, repeat. Or indeed perform. Anyway. Here are my examples.

1. I recently had reason to generate BIND cards for a whole bunch of packages, but retain the various DBRMLIBs from which they were originally bound. So, enter stage left my SPUFI:

DECLARE GLOBAL TEMPORARY TABLE T(LINENO INTEGER NOT NULL);
INSERT INTO SESSION.T VALUES 1;
INSERT INTO SESSION.T VALUES 2;
INSERT INTO SESSION.T VALUES 3;
SELECT
   CASE LINENO
        WHEN 1 THEN 'BIND PACKAGE(' CONCAT STRIP(COLLID) CONCAT
                    ') MEMBER(' CONCAT STRIP(NAME) CONCAT ') -'
        WHEN 2 THEN '     OWNER(' CONCAT STRIP(OWNER) CONCAT
                    ') QUALIFIER(' CONCAT STRIP(QUALIFIER) CONCAT ') -'
        WHEN 3 THEN '     LIBRARY(' CONCAT STRIP(PDSNAME) CONCAT
                    ')'
   END 
FROM SYSIBM.SYSPACKAGE, SESSION.T                                      WHERE LOCATION = '' AND COLLID = 'DTESTD' AND NAME LIKE '%' ORDER BY COLLID,NAME,LINENO
WITH UR;

The output looks something like this:

BIND PACKAGE(DTESTD) MEMBER(AB33511M) -
     OWNER(DTESTD) QUALIFIER(DTESTD) -     
     LIBRARY(LIVE.DTESTD.DBRMLIB)
BIND PACKAGE(DTESTD) MEMBER(AB93201M) -    
     OWNER(DTESTD) QUALIFIER(DTESTD) -     
     LIBRARY(TEST.DTESTD.DBRMLIB)              

2. I had need to perform unloads and loads of a whole bunch of tables with BLOBs defined. A particular third-party tool demanded that the implicitly-defined ROWID column “DB2_GENERATED_ROWID_FOR_LOBS” have a unique index defined upon it. Enter stage right, the following, similar SPUFI:


DECLARE GLOBAL TEMPORARY TABLE T(LINENO INTEGER NOT NULL);
INSERT INTO SESSION.T VALUES 1; 
INSERT INTO SESSION.T VALUES 2; 
INSERT INTO SESSION.T VALUES 3;
INSERT INTO SESSION.T VALUES 4;
SELECT 
   CASE LINENO 
        WHEN 1 THEN 'CREATE UNIQUE INDEX '
                    CONCAT STRIP(TBNAME) CONCAT '_LOB'
        WHEN 2 THEN '        ON ' CONCAT STRIP(TBNAME)
        WHEN 3 THEN '        (DB2_GENERATED_ROWID_FOR_LOBS ASC )' 
        WHEN 4 THEN '        USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1'
                    CONCAT ' BUFFERPOOL BP0 ;'  
   END 
  FROM SESSION.T, 
  ( SELECT TBNAME 
    FROM SYSIBM.SYSCOLUMNS
    WHERE TBCREATOR = 'tbcreator'
      AND NAME      = 'DB2_GENERATED_ROWID_FOR_LOBS'
    ORDER BY TBNAME 
  ) C 
WITH UR;

The output looks something like this:

CREATE UNIQUE INDEX WORK_LOB 
        ON WORK 
        (DB2_GENERATED_ROWID_FOR_LOBS ASC ) 
        USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 BUFFERPOOL BP0 ;
CREATE UNIQUE INDEX ITEM_LOB 
        ON ITEM 
        (DB2_GENERATED_ROWID_FOR_LOBS ASC ) 
        USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 BUFFERPOOL BP0 ;

Notice that for each output line needed, a line has to be inserted into the session table T.

Advertisements