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