Generating Commands from the DB2 Catalog Part 2

Introduction

The first part of this article looked at why and how you would generate commands (or SQL or whatever) from the DB2 catalog. It described a method for doing so, but at the end we were left with commands which had to fit onto one line. This article describes the two types of multi-line command and how to generate them.

Multiline Commands

Consider the case where I want to generate a series of bind commands like this:

BIND PACKAGE(CTEST) MEMBER(PDBACS1)  - 
    LIBRARY('NUDBS.SMITHAC.DBRMLIB')  - 
    OWNER(NUDBSSUP) QUALIFIER(SYSIBM) 
  

This command won't fit on one line — it has to go over two or three because the library name alone may be 44 characters. Each set of three lines comes from one row in a catalog table, so let's call the command single-row-sourced.

For the second type of command, consider the case where you have to generate plan rebinds which contain the PKLIST. The plan rebinds look like this:

REBIND PLAN(PVRDRVR) PKLIST(CTEST.*,    -        
                            DBUG.CUSER.*,    -                                
                            DB2U.CUSER.*)                                          
 

In this case, each line of the command comes from a single row in a catalog table (SYSIBM.SYSPACKLIST):

The problem here is that each line in this multiple-row-sourced command needs to be treated differently: the first line needs ‘REBIND PLAN(planname) PKLIST(’ at the start; the last line needs a closing bracket at the end; all lines except for the last line need a comma and a continuation character at the end.

Let's look at each of these situations in turn.

Single-Row-Sourced Commands

Remember the command we want to generate:

BIND PACKAGE(CTEST) MEMBER(PDBACS1)  - 
    LIBRARY('NUDBS.SMITHAC.DBRMLIB')  - 
    OWNER(NUDBSSUP) QUALIFIER(SYSIBM) 
  

The information for the command comes from the rows of SYSIBM.SYSPACKAGE. I can retrieve the information with a piece of SQL:

---------+---------+---------+---------+---------+---------+---------+---------
SELECT COLLID,NAME,OWNER,QUALIFIER,PDSNAME
  FROM SYSIBM.SYSPACKAGE
  WHERE LOCATION = '' AND COLLID = 'CTEST'
      AND NAME LIKE 'PDB%'
WITH UR
---------+---------+---------+---------+---------+---------+---------+---------
COLLID              NAME      OWNER     QUALIFIER  PDSNAME
---------+---------+---------+---------+---------+---------+---------+---------
CTEST               PDBACS1   NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DBRMLIB
CTEST               PDBBP001  NUQAD     NUQAD      ALCHEMD.PQTE1.DB.B.DBRMLIB  
CTEST               PDBCAFT   NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DBRMLIB
CTEST               PDBCNECT  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DBRMLIB
CTEST               PDBCPYEX  NUDBSSUP  SYSIBM     NUBGS.DB2T.PDBCPYEX.DBRMLIB 
CTEST               PDBCRMAP  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DBRMLIB
CTEST               PDBDB23B  NUDBSDEV  NUTS       NU.DB2T.DBRMLIB
CTEST               PDBIN01   NUDBSSUP  NUDB       NUDBS.SMITHAC.DBRMLIB
CTEST               PDBMODGN  NUDBSSUP  NUBG       SYSA.OP0PERF.DBRMLIB
CTEST               PDBMODGN  NUDBSSUP  SYSIBM     SYSA.OP0PERF.DBRMLIB
CTEST               PDBNUTRD  NUTRD19   NUTRD19    NUTRD.NUTRD19.DBRMLIB
  

Note that I've used a WHERE clause as I’m only interested in certain rows.

The rows which qualify from SYSIBM.SYSPACKAGE give me my source table. I need to change each row in the source table into three rows in the bind command. The best way to do this is by using a join. If the source table is joined with another table which has three rows, and all rows on either side of the join match each other (i.e., it's a Cartesian join), I will get three rows output for each row in the source table.

It’s unlikely that you've got a handy table with exactly the right number of rows hanging around your system, so we need to create one. Luckily, version 7 of DB2 introduced Declared Global Temporary Tables which we can create for the duration of our thread.

This bit of SQL creates the temporary table with one column which is a sequence number, and inserts three rows into it:

---------+---------+---------+---------+---------+---------+---------+---------+
DECLARE GLOBAL TEMPORARY TABLE T1(SEQ INTEGER NOT NULL);
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO SESSION.T1 VALUES 1;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO SESSION.T1 VALUES 2;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+---------+
INSERT INTO SESSION.T1 VALUES 3;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 1
  

Note that the table has a qualifier of SESSION.

The SELECT statement then follows straight after:

---------+---------+---------+---------+---------+---------+---------+---------+
SELECT COLLID,NAME,SEQ,OWNER,QUALIFIER,PDSNAME
  FROM SYSIBM.SYSPACKAGE,SESSION.T1
  WHERE LOCATION = '' AND COLLID = 'CTEST'
      AND NAME LIKE 'PDB%'
ORDER BY COLLID,NAME,SEQ
WITH UR
---------+---------+---------+---------+---------+---------+---------+---------+
COLLID              NAME              SEQ  OWNER     QUALIFIER  PDSNAME
---------+---------+---------+---------+---------+---------+---------+---------+
CTEST               PDBACS1             1  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBACS1             2  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBACS1             3  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBBP001            1  NUQAD     NUQAD      ALCHEMD.PQTE1.DB
CTEST               PDBBP001            2  NUQAD     NUQAD      ALCHEMD.PQTE1.DB
CTEST               PDBBP001            3  NUQAD     NUQAD      ALCHEMD.PQTE1.DB
CTEST               PDBCAFT             1  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBCAFT             2  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBCAFT             3  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBCNECT            1  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBCNECT            2  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
CTEST               PDBCNECT            3  NUDBSSUP  SYSIBM     NUDBS.SMITHAC.DB
  

I've included the sequence number from the temporary table in the output. Note that there are no join predicates — this gives us the required Cartesian Join.

So we’ve now got three output lines for each row in the source table. Each of the three lines has the same information apart from the sequence number — and we can use this to vary the contents of each output line using a CASE statement.

In the following SQL, the list of columns from the previous example has been converted into a CASE statement which outputs a different string depending on the value of SEQ:

SELECT                                                                   
   CASE SEQ                                                              
        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.T1                                      
  WHERE LOCATION = '' AND COLLID = 'CTEST'                               
      AND NAME LIKE 'PDB%'                                               
ORDER BY COLLID,NAME,SEQ                                                 
WITH UR                                                                  
  

Note the use of the STRIP and CONCAT functions to build a number of columns and literals into a single string (the process is discussed in the previous article). This gives the final version of the output as required:

BIND PACKAGE(CTEST) MEMBER(PDBACS1) -                                    
     OWNER(NUDBSSUP) QUALIFIER(SYSIBM) -                                 
     LIBRARY(NUDBS.SMITHAC.DBRMLIB)                                      
BIND PACKAGE(CTEST) MEMBER(PDBBP001) -                                   
     OWNER(NUQAD) QUALIFIER(NUQAD) -                                     
     LIBRARY(ALCHEMD.PQTE1.DB.B.DBRMLIB)                                 
BIND PACKAGE(CTEST) MEMBER(PDBCAFT) -                                    
     OWNER(NUDBSSUP) QUALIFIER(SYSIBM) -                                 
     LIBRARY(NUDBS.SMITHAC.DBRMLIB)                                      
BIND PACKAGE(CTEST) MEMBER(PDBCNECT) -                                   
     OWNER(NUDBSSUP) QUALIFIER(SYSIBM) -                                 
     LIBRARY(NUDBS.SMITHAC.DBRMLIB)                                      
  

Multiple-Row-Sourced Commands

Recall that I’m trying to generate some rebind commands which specify the PKLIST. Each command is made up of several lines, each line coming from a single row in the source table, SYSIBM.SYSPACKLIST:

REBIND PLAN(CIDLY3A) PKLIST(CITLY3A.*,    -                
                            DB2U.CITLY3A.*,    -                          
                            DLSG.CITLY3A.*,    -                          
                            CTEST.ENUCNCT,    -                           
                            CTEST.ENUCNCT2,    -                          
                            CTEST.EPMSERVE,    -                          
                            DIUG.CNUDCENT.*,    -                         
                            DIUG.CAZD.*)                                  
REBIND PLAN(PQAMPV03) PKLIST(CUSER.*,    -                 
                            DB2U.CPROD.*)                                 
REBIND PLAN(PVRDRVR) PKLIST(CTEST.*,    -                  
                            DBUG.CUSER.*,    -                            
                            DB2U.CUSER.*)                                 
  

In this case, I wanted to generate REBIND statements for plans which have a PKLIST entry using a location of DB2U. This was because the location was changing name so the PKLISTs needed to be changed. The SQL to find the affected PKLIST entries is:

---------+---------+---------+---------+---------+---------+---------
  SELECT PLANNAME,SEQNO,LOCATION,COLLID,NAME                         
      FROM SYSIBM.SYSPACKLIST                                        
      WHERE LOCATION = 'DB2U'                                        
  ORDER BY PLANNAME,SEQNO                                            
  WITH UR                                                            
---------+---------+---------+---------+---------+---------+---------
PLANNAME   SEQNO  LOCATION          COLLID              NAME         
---------+---------+---------+---------+---------+---------+---------
CIDLY3A        2  DB2U              CITLY3A             *            
PQAMPV03       2  DB2U              CPROD               *            
PVRDRVR        3  DB2U              CUSER               *            
  

The result set only contains the entries with a location of DB2U, whereas I want all the entries in the affected plans. This can be done with EXISTS:

---------+---------+---------+---------+---------+---------+---------
  SELECT PLANNAME,SEQNO,LOCATION,COLLID,NAME                         
      FROM SYSIBM.SYSPACKLIST A                                      
    WHERE EXISTS                                                   
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C                              
     WHERE A.PLANNAME = C.PLANNAME                                   
      AND   LOCATION = 'DB2U')                                       
  ORDER BY PLANNAME,SEQNO                                            
  WITH UR                                                            
---------+---------+---------+---------+---------+---------+---------
PLANNAME   SEQNO  LOCATION          COLLID              NAME         
---------+---------+---------+---------+---------+---------+---------
CIDLY3A        1                    CITLY3A             *            
CIDLY3A        2  DB2U              CITLY3A             *            
CIDLY3A        3  DLSG              CITLY3A             *            
CIDLY3A        4                    CTEST               ENUCNCT      
CIDLY3A        5                    CTEST               ENUCNCT2     
CIDLY3A        6                    CTEST               EPMSERVE     
CIDLY3A        7  DIUG              CNUDCENT            *            
CIDLY3A        8  DIUG              CAZD                *            
PQAMPV03       1                    CUSER               *            
PQAMPV03       2  DB2U              CPROD               *            
PVRDRVR        1                    CTEST               *            
PVRDRVR        2  DBUG              CUSER               *            
PVRDRVR        3  DB2U              CUSER               *            
  

We can add in the REBIND literals:

---------+---------+---------+---------+---------+---------+---------+----
  SELECT                                                                  
   'REBIND PLAN(' || STRIP(PLANNAME) ||                                   
       ') PKLIST(' || STRIP(LOCATION) || '.' || STRIP(COLLID) || '.'      
       || STRIP(NAME) || ', -'                                            
      FROM SYSIBM.SYSPACKLIST A                                           
      WHERE EXISTS                                                        
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C                                   
     WHERE A.PLANNAME = C.PLANNAME                                        
      AND   LOCATION = 'DB2U')                                            
  ORDER BY PLANNAME,SEQNO                                                 
  WITH UR                                                                 
---------+---------+---------+---------+---------+---------+---------+----

---------+---------+---------+---------+---------+---------+---------+----
REBIND PLAN(CIDLY3A) PKLIST(.CITLY3A.*, -                                 
REBIND PLAN(CIDLY3A) PKLIST(DB2U.CITLY3A.*, -                             
REBIND PLAN(CIDLY3A) PKLIST(DLSG.CITLY3A.*, -                             
REBIND PLAN(CIDLY3A) PKLIST(.CTEST.ENUCNCT, -                             
REBIND PLAN(CIDLY3A) PKLIST(.CTEST.ENUCNCT2, -                            
REBIND PLAN(CIDLY3A) PKLIST(.CTEST.EPMSERVE, -                            
REBIND PLAN(CIDLY3A) PKLIST(DIUG.CNUDCENT.*, -                            
REBIND PLAN(CIDLY3A) PKLIST(DIUG.CAZD.*, -                                
REBIND PLAN(PQAMPV03) PKLIST(.CUSER.*, -                                  
REBIND PLAN(PQAMPV03) PKLIST(DB2U.CPROD.*, -                              
REBIND PLAN(PVRDRVR) PKLIST(.CTEST.*, -                                   
REBIND PLAN(PVRDRVR) PKLIST(DBUG.CUSER.*, -                               
REBIND PLAN(PVRDRVR) PKLIST(DB2U.CUSER.*, -                               
  

The first problem is that entries without a location start with a full-stop ('.'). We can sort this out with a CASE statement:

---------+---------+---------+---------+---------+---------
  SELECT                                                   
   'REBIND PLAN(' || STRIP(PLANNAME) ||                    
       ') PKLIST(' ||                                      
       CASE LOCATION WHEN '' THEN ''                       
       ELSE STRIP(LOCATION) || '.'                         
       END                                                 
       || STRIP(COLLID) || '.'                             
       || STRIP(NAME) || ', -'                             
      FROM SYSIBM.SYSPACKLIST A                            
      WHERE EXISTS                                         
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C                    
     WHERE A.PLANNAME = C.PLANNAME                         
      AND   LOCATION = 'DB2U')                             
  ORDER BY PLANNAME,SEQNO                                  
  WITH UR                                                  
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
REBIND PLAN(CIDLY3A) PKLIST(CITLY3A.*, -                   
REBIND PLAN(CIDLY3A) PKLIST(DB2U.CITLY3A.*, -              
REBIND PLAN(CIDLY3A) PKLIST(DLSG.CITLY3A.*, -              
REBIND PLAN(CIDLY3A) PKLIST(CTEST.ENUCNCT, -               
REBIND PLAN(CIDLY3A) PKLIST(CTEST.ENUCNCT2, -              
REBIND PLAN(CIDLY3A) PKLIST(CTEST.EPMSERVE, -              
REBIND PLAN(CIDLY3A) PKLIST(DIUG.CNUDCENT.*, -             
REBIND PLAN(CIDLY3A) PKLIST(DIUG.CAZD.*, -                 
REBIND PLAN(PQAMPV03) PKLIST(CUSER.*, -                    
REBIND PLAN(PQAMPV03) PKLIST(DB2U.CPROD.*, -               
REBIND PLAN(PVRDRVR) PKLIST(CTEST.*, -                     
REBIND PLAN(PVRDRVR) PKLIST(DBUG.CUSER.*, -                
REBIND PLAN(PVRDRVR) PKLIST(DB2U.CUSER.*, -                
  

The second problem is that all the lines in a command have the same literals. ‘REBIND PLAN’ should only be on the first line of the command. We can test the sequence number (SEQNO) with a CASE statement to output different literals for the first line of a command:

---------+---------+---------+---------+---------+---------
  SELECT                                                   
   CASE WHEN SEQNO = 1 THEN                                
       'REBIND PLAN(' || STRIP(PLANNAME) ||                
         ') PKLIST('                                       
   ELSE                                                    
       '             '                                     
   END ||                                                  
   CASE LOCATION WHEN '' THEN ''                       
   ELSE STRIP(LOCATION) || '.'                         
   END                                                 
       || STRIP(COLLID) || '.'                             
       || STRIP(NAME) || ', -'                             
      FROM SYSIBM.SYSPACKLIST A                            
      WHERE EXISTS                                         
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C                    
     WHERE A.PLANNAME = C.PLANNAME                         
      AND   LOCATION = 'DB2U')                             
  ORDER BY PLANNAME,SEQNO                                  
  WITH UR                                                  
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
REBIND PLAN(CIDLY3A) PKLIST(CITLY3A.*, -                   
             DB2U.CITLY3A.*, -                             
             DLSG.CITLY3A.*, -                             
             CTEST.ENUCNCT, -                              
             CTEST.ENUCNCT2, -                             
             CTEST.EPMSERVE, -                             
             DIUG.CNUDCENT.*, -                            
             DIUG.CAZD.*, -                                
REBIND PLAN(PQAMPV03) PKLIST(CUSER.*, -                    
             DB2U.CPROD.*, -                               
REBIND PLAN(PVRDRVR) PKLIST(CTEST.*, -                     
             DBUG.CUSER.*, -                               
             DB2U.CUSER.*, -                               
  

We’re almost there, but not quite — the last line of each command should have a closing bracket instead of a comma and shouldn't have a continuation character. The problem is that you can’t tell whether a line is the last one just by looking at SEQNO because different plans have different numbers of entries. If the sequence number is equal to the number of entries for the plan, we’re on the last line of the command. This SQL joins a subquery containing PLANNAME and a count of the entries for the plan with our original SELECT:

---------+---------+---------+---------+---------+---------+---------+---------+
  SELECT A.PLANNAME,SEQNO,CT,LOCATION,COLLID,NAME
       FROM SYSIBM.SYSPACKLIST A
       INNER JOIN
      (SELECT PLANNAME,COUNT(*) AS CT
       FROM SYSIBM.SYSPACKLIST
       GROUP BY PLANNAME) B
       ON A.PLANNAME = B.PLANNAME
      WHERE EXISTS
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C
     WHERE A.PLANNAME = C.PLANNAME
      AND   LOCATION = 'DB2U')
  ORDER BY 1,2
  WITH UR
---------+---------+---------+---------+---------+---------+---------+---------+
PLANNAME   SEQNO           CT  LOCATION          COLLID              NAME
---------+---------+---------+---------+---------+---------+---------+---------+
CIDLY3A        1            8                    CITLY3A             *
CIDLY3A        2            8  DB2U              CITLY3A             *
CIDLY3A        3            8  DLSG              CITLY3A             *
CIDLY3A        4            8                    CTEST               ENUCNCT
CIDLY3A        5            8                    CTEST               ENUCNCT2   
CIDLY3A        6            8                    CTEST               EPMSERVE   
CIDLY3A        7            8  DIUG              CNUDCENT            *
CIDLY3A        8            8  DIUG              CAZD                *
PQAMPV03       1            2                    CUSER               *
PQAMPV03       2            2  DB2U              CPROD               *
PVRDRVR        1            3                    CTEST               *
PVRDRVR        2            3  DBUG              CUSER               *
PVRDRVR        3            3  DB2U              CUSER               *
  

We can now add back in our formatting and CASE statements, plus an additional CASE statement which tests whether the row is the last one for a plan and acts accordingly:

---------+---------+---------+---------+---------+---------
  SELECT                                                   
   CASE WHEN SEQNO = 1 THEN                                
       'REBIND PLAN(' || STRIP(A.PLANNAME) ||              
         ') PKLIST('                                       
   ELSE                                                    
       '             '                                     
   END ||                                                  
   CASE LOCATION WHEN '' THEN ''                           
   ELSE STRIP(LOCATION) || '.'                             
   END                                                     
   || STRIP(COLLID) || '.'                                 
   || STRIP(NAME) ||                                       
   CASE WHEN SEQNO = CT THEN ')'                           
        ELSE ',    -'                                      
   END                                                     
      FROM SYSIBM.SYSPACKLIST A                            
       INNER JOIN                                          
      (SELECT PLANNAME,COUNT(*) AS CT                      
       FROM SYSIBM.SYSPACKLIST                             
       GROUP BY PLANNAME) B                                
       ON A.PLANNAME = B.PLANNAME                          
      WHERE EXISTS                                         
    (SELECT 1 FROM SYSIBM.SYSPACKLIST C                    
     WHERE A.PLANNAME = C.PLANNAME                         
      AND   LOCATION = 'DB2U')                             
  ORDER BY A.PLANNAME,A.SEQNO                              
  WITH UR                                                  
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
REBIND PLAN(CIDLY3A) PKLIST(CITLY3A.*,    -                
             DB2U.CITLY3A.*,    -                          
             DLSG.CITLY3A.*,    -                          
             CTEST.ENUCNCT,    -                           
             CTEST.ENUCNCT2,    -                          
             CTEST.EPMSERVE,    -                          
             DIUG.CNUDCENT.*,    -                         
             DIUG.CAZD.*)                                  
REBIND PLAN(PQAMPV03) PKLIST(CUSER.*,    -                 
             DB2U.CPROD.*)                                 
REBIND PLAN(PVRDRVR) PKLIST(CTEST.*,    -                  
             DBUG.CUSER.*,    -                            
             DB2U.CUSER.*)                                 
  

This is the output we wanted.

 

 

Alan C Smith, 2005