Category: DB2


Judicious Cartesian Joins

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

DB2 SQL performance

I’ve just seen one of the most shockingly stupid pieces of SQL in 20 years of DB2 Administration:

SELECT   (CURRENT DATE – :H MONTHS)
INTO     :H                        
FROM     TABLE1

It does a non-matching index scan of a table of 342,038,069 rows, performing 16,248 getpages at a cost of 1,426,790 timerons, when all it wants is the current date minus x months! They haven’t heard of SYSIBM.SYSDUMMY1, obviously.

%d bloggers like this: