A time saving DB2 tip from our competition winner

By Guest Geek Marcus Davage

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 tech tip). 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.