Triton Db2 Geek

Confessions of a DB2 geek

IBM Gold Consultant Program and IBM Premier business Partner

Confession of the Month

A time saving DB2 tip from our competition winner

March 14th, 2013 - by

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.

Comments

« »

Tag Archives