Db2 SQL for the DBA

Db2 SQL for the DBA - Using Modern SQL for DBA Tasks

Recent releases of Db2 have introduced new SQL functionality that improves the functionality and productivity of application developers.  But the DBA can also take advantage of some of the “new” Db2 SQL to improve their own productivity.  Db2 12 introduces new SQL that allows the DBA to be MORE effective with their own SQL and queries to the catalog. 

As an example, I had a recent (DBA) requirement to gather data from my Db2 to prepare a bunch of UNLOAD utility statements.  Along the way, I used DECLARED GLOBAL TEMPORARY TABLES (DGTT) for holding my working data.  I was able to use Db2 12 MERGE for easily updating my DGTT.  The functions of ROW_NUMBER and MOD allowed me to extract from the same table multiple times and split the whole result set into 3 smaller and evenly balanced results (I call this FETCH EVERY NTH ROW).  SQL pagination (multi-column matching) helped keep my SQL easy-to-read and made confirming the results a snap. 

It is possible to use DSNTIAUL and SQL to gather data and create output for JCL or PROCS or CTLCARDS or even create more SQL.  As a DBA, I do this all the time.  As a matter of stubbornness, I often try to do everything with SQL and produce formatted output that meets my exact needs!  DSNTIAUL produces output exactly matching my result set.  So, if I keep my result set exactly 80 CHAR wide then it is easy for subsequent steps to use the DSNTIAUL output.

  • If one was not so stubborn then one could use SQL to quickly gather data and then do simple post-result-set processing using some other tool to produce the exact desired result (i.e. use REXX or SAS or COBOL or whatever). I would rather do it all with SQL

I had a requirement to UNLOAD from a bunch of tablespace FULL IMAGE COPY backup datasets for tablespaces that were common between two DB (in the same subsystem).    I wanted to query the catalog table of SYSCOPY to find the image copy dataset name and produce UNLOAD utility control card statements that could be used by a subsequent Db2 utility step.  I wanted to be overly clever by producing multiple control cards that were relatively well balanced by size.  Then my UNLOAD jobs could run in parallel.

DSNTIAUL with comments!  Yes, it is possible

My SQL script below uses the currently undocumented feature of DSNTIAUL that bracketed comments (comments begin with “/*” and end with “*/”) are supported by DSNTIAUL.  This is contrary to the actual DSNTIAUL documentation which states that comments are not allowed in the SQL input.  The simple dash-dash comments are not allowed by DSNTIAUL.  But it appears DSNTIAUL does accept bracketed comments.  It looks like DSNTIAUL passes each SQL statement, with the bracketed comments, to Db2 and then Db2 itself accepts the comments (you can see the comments in the SQL in dynamic statement cache)

  • I try to practice what I preach, and I use comments in my SQL scripts. At the very least, it should help ME when/if I look at the SQL script a few months down the line.
  • And now that DSNTIAUL accepts this type of comments … I can run this exact same SQL script in DSNTIAUL and SPUFI and DSNTEP2 and Data Studio. Most handy.  Previously, I would develop SQL scripts in Data Studio and then copy them down to run in batch DSNTIAUL.  I would manually remove the comments or pre-process the SQL script to remove the comments (lots of tools exist).  But now, I leave bracketed comments in my SQL scripts.
  • Minor caveat with bracketed comments and DSNTIAUL. If the SQL script is in the JCL and the initial “/*” for the comment begins in column 1 of the JCL member, then the JCL preprocessor will get confused and think this line is JCL and no longer part of the SQL script.  A JCL error will result.  In that case, begin the “/*”in column 2… or put the script in a control or PDS.

Declared Global Temporary Tables (DGTTs)

I use DGTTs extensively in this particular SQL script. I find DGTTs easy to use and suited to this type of ad-hoc dynamic reporting. DGTTs allow me to break big problems into many little chunks where I can stuff data and then re-refence.

  • If you have a moment, jump to the bottom and my link to RFE aha idea 940 which is my request to logically extend DGTTs to be even easier to use

Db2 12 MERGE

The next thing you will see is the extensive use of Db2 12 MERGE.  MERGE always looked good.  But now in Db2 12 it really lives up to it’s potential!  With Db2 12 you can update one table based upon a whole other table.  I find it easy to read and powerful.  In fact, I often use MERGE just for UPDATE instead of UPDATE syntax.    In combination with “sql pagination” (multi-column matching), MERGE provides powerful SQL

  • If you have a moment, jump to the bottom and my link to RFE aha idea 1056 which is my request to allow MERGE to use CTE. I fell this would make SQL MERGE even easier to read.

FETCH EVERY NTH ROW

With the DGTT, I can have an order_row_nbr column at the end.  The handy thing here is that I can stuff my DGTT full of data (ignoring the order_row_nbr column) and then go back to the DGTT and use ROW_NUMBER function to find a sorted number for each row and update the DGTT the value to order_row_nbr! Then one can go through the DGTT with MOD Function on order_row_nbr column to fetch every Nth row.  You can select from the DGTT multiple times, using different MOD values, to produce multiple result sets.

This allows one to split a big result set relatively evenly. 

DSNTIAUL to build a control card

At the end of my script, I select from my final DGTT and I sent the DSNTIAUL SYSRECnn output straight into a 80 byte wide PDS member.  If I create my utility control card in a DGTT in a 80 char column variable (not null) then the final select into the PDS is not variable and has no special characters.  It is ready to be used!

SQL Pagination  (Db2 12)

Db2 12 introduced “Data Dependent Pagination” where one can relatively simply compare sets of columns.  The examples in the documentation are all about “paging” through data.  And yes, that is great.  But you can also use this new SQL functionality to compare SETS of columns or even ASSIGN values to a SET of columns.  You can see examples in the MERGE in the JOIN ON clauses and in the MERGE SET.

/* BEGIN OF SQL SCRIPT */

/* DECLARE DGTT TO HOLD TS NAMES COMMON BETWEEN MY TWO DB */
DECLARE GLOBAL TEMPORARY TABLE DGTT_TS_COMMON (
SRC_DBNAME VARCHAR(24) NOT NULL
,TRG_DBNAME VARCHAR(24) NOT NULL
,TSNAME VARCHAR(24) NOT NULL
,TSTYPE CHAR(1) NOT NULL
,PARTITIONS INTEGER NOT NULL
,TRG_TBCREATOR VARCHAR(128) NOT NULL
,TRG_TBNAME VARCHAR(128) NOT NULL
);

/* DECLARE DGTT TO HOLD MY THE FIC DSNAME FOR THE SRC TS */
DECLARE GLOBAL TEMPORARY TABLE DGTT_LAST_FIC (
SRC_DBNAME VARCHAR(24)
,TRG_DBNAME VARCHAR(24)
,TSNAME VARCHAR(24)
,TSTYPE CHAR(1)
,PARTITIONS SMALLINT
,DSNUM INTEGER
,ICTYPE CHAR(1)
,SHRLEVEL CHAR(1)
,ICBACKUP CHAR(1)
,TS TIMESTAMP
,FIC_DSNAME CHAR(44) NOT NULL WITH DEFAULT
,PRT CHAR(12) NOT NULL WITH DEFAULT
,FIC_JOBNAME VARCHAR(24)
,AUTHID VARCHAR(24)
,COPYPAGESF DOUBLE
,TOTALROWS BIGINT
,TRG_TBCREATOR VARCHAR(24)
,TRG_TBNAME VARCHAR(128)
,ORDER_ROW_NBR SMALLINT NOT NULL WITH DEFAULT
);

/* DECLARE DGTT TO STUFF SOME Db2 UTILITY CTLCARD STMTS */
DECLARE GLOBAL TEMPORARY TABLE DGTT_UTIL_CMDS
( UTIL_TYPE VARCHAR(24)
, CARD_NBR INTEGER
, ORDER_ROW_NBR SMALLINT
, LINE_NBR INTEGER
, UTIL_CMD CHAR(80) NOT NULL /* NOT NULL IS IMPORTANT FOR LATER SEL */
, DBNAME VARCHAR(24)
, TSNAME VARCHAR(24)
, DSNUM INTEGER
)
;
/**********************************************************************/
/**********************************************************************/
/**********************************************************************/
/* FIRST, INSERT INTO A DGTT ALL THE TS THAT ARE COMMON BETWEEN 2 DB */
INSERT INTO SESSION.DGTT_TS_COMMON
SELECT P1.DBNAME, P4.DBNAME, P4.NAME AS TSNAME, P4.TYPE, P4.PARTITIONS
, T.CREATOR AS TRG_TBCREATOR
, T.NAME AS TRG_TBNAME
FROM SYSIBM.SYSTABLESPACE P4
INNER JOIN SYSIBM.SYSTABLESPACE P1
ON P4.NAME = P1.NAME
INNER JOIN SYSIBM.SYSTABLES T
ON P4.DBNAME = T.DBNAME AND P4.NAME = T.TSNAME
WHERE 1=1
AND P4.DBNAME = 'DCL04P' /* TARGET DB */
AND P1.DBNAME = 'DCL01P' /* SOURCE DB */
AND P4.NAME NOT IN ('ZICTL') /* IGNORE THE SILLY TS */
AND T.TYPE = 'T'
;

SELECT * FROM SESSION.DGTT_TS_COMMON ORDER BY TRG_DBNAME, TSNAME;

/**********************************************************************/
/***********************************************************************
SECOND, MY SECOND DGTT WILL HOLD THE FIC DSNAME OF THE SOURC DB TS
FIND LAST FIC TIMESTAMP FROM SYSCOPY FOR SRC_DBNAME
> NOTE, JUST FINDING MAX(TIMESTAMP) <RELATIVE TO NOW>
***********************************************************************/
INSERT INTO SESSION.DGTT_LAST_FIC
(SRC_DBNAME ,TSNAME ,DSNUM ,ICTYPE ,SHRLEVEL ,ICBACKUP ,TS )

SELECT DBNAME, TSNAME, DSNUM, ICTYPE, SHRLEVEL, ICBACKUP
, MAX(TIMESTAMP) AS TS
FROM SYSIBM.SYSCOPY S
WHERE 1=1
AND DBNAME = (SELECT DISTINCT SRC_DBNAME FROM SESSION.DGTT_TS_COMMON)
AND TSNAME IN (SELECT TSNAME FROM SESSION.DGTT_TS_COMMON)
AND ICTYPE = 'F' AND ICBACKUP = ' '
AND TIMESTAMP > (CURRENT TIMESTAMP - 07 DAYS)
GROUP BY DBNAME, TSNAME, DSNUM, ICTYPE, SHRLEVEL, ICBACKUP
ORDER BY DBNAME, TSNAME, DSNUM
;

SELECT * FROM SESSION.DGTT_LAST_FIC ORDER BY SRC_DBNAME, TSNAME, DSNUM;

/***********************************************************************
(USE THE FUN Db2 12 MERGE!!!)
NOW THAT I KNOW TIMESTAMP OF LAST FIC!
> I CAN GO BACK TO SYSCOPY AND GET THE DSNAME FROM THAT TIMESTAMP
***********************************************************************/
MERGE INTO
SESSION.DGTT_LAST_FIC D
USING (
SELECT DBNAME, TSNAME, DSNUM, ICTYPE, SHRLEVEL, ICBACKUP
, TIMESTAMP AS TS
, DSNAME, JOBNAME, AUTHID, COPYPAGESF
FROM SYSIBM.SYSCOPY
WHERE 1=1
AND DBNAME =
(SELECT DISTINCT SRC_DBNAME FROM SESSION.DGTT_TS_COMMON)
AND TSNAME IN (SELECT TSNAME FROM SESSION.DGTT_TS_COMMON)
AND ICTYPE = 'F' AND ICBACKUP = ' '
AND TIMESTAMP > (CURRENT TIMESTAMP - 07 DAYS)
) A

ON (D.SRC_DBNAME, D.TSNAME, D.DSNUM, D.ICTYPE, D.SHRLEVEL, D.TS)
= (A.DBNAME , A.TSNAME, A.DSNUM, A.ICTYPE, A.SHRLEVEL, A.TS)
WHEN MATCHED THEN
UPDATE
SET (D.FIC_DSNAME, D.FIC_JOBNAME, D.AUTHID, D.COPYPAGESF)
= (A.DSNAME, A.JOBNAME, A.AUTHID, A.COPYPAGESF)
;
SELECT * FROM SESSION.DGTT_LAST_FIC ORDER BY SRC_DBNAME, TSNAME, DSNUM;

/***********************************************************************
USE THE FUN Db2 12 MERGE AGAIN!!
DYNAMICALLY BUILD A LITTLE TABLE I WILL CALL Z WITH ROW_NUMBER
USING THE (FUN) ROW_NUMBER() FUNCTION WITH OVER....
AND USE MERGE TO GO BACK TO SOURE TABLE AND UPD THE COL ORDER_ROW_NBR
***********************************************************************/
MERGE INTO SESSION.DGTT_LAST_FIC D
USING (SELECT SRC_DBNAME, TSNAME, DSNUM
,ROW_NUMBER() OVER (ORDER BY COPYPAGESF DESC) AS R
FROM SESSION.DGTT_LAST_FIC
) Z
ON D.SRC_DBNAME= Z.SRC_DBNAME
AND D.TSNAME = Z.TSNAME
AND D.DSNUM = Z.DSNUM
WHEN MATCHED THEN
UPDATE SET (D.ORDER_ROW_NBR) = (Z.R)
;

SELECT * FROM SESSION.DGTT_LAST_FIC ORDER BY ORDER_ROW_NBR;

/***********************************************************************
SIMPLE UPDATE IN THE FIC TABLE.. TO SET A FIELD FOR EASY REF LATER
***********************************************************************/
UPDATE SESSION.DGTT_LAST_FIC
SET PRT = (CASE WHEN DSNUM = 0 THEN ' '
ELSE ' PART '||CHAR(DSNUM)
END)
;

SELECT * FROM SESSION.DGTT_LAST_FIC ORDER BY ORDER_ROW_NBR;

/**********************************************************************/
/***********************************************************************
THIRD. BUILD SOME Db2 UNLOAD COMMAND STATEMENTS -> PUT INTO A DGTT
***********************************************************************/
INSERT INTO SESSION.DGTT_UTIL_CMDS
( UTIL_TYPE, CARD_NBR, ORDER_ROW_NBR, LINE_NBR
, UTIL_CMD, DBNAME, TSNAME, DSNUM)

SELECT 'UNLOAD' AS UTIL_TYPE
, (MOD(ORDER_ROW_NBR,3)+1) AS CARD_NBR /* USE MOD TO CONVERT ROW_NBR */
, ORDER_ROW_NBR
, 1000 AS LINE_NBR
, 'UNLOAD TABLESPACE '||STRIP(SRC_DBNAME)||'.'||STRIP(TSNAME)||PRT
||'-- COPYPAGES: '||STRIP(CHAR(BIGINT(COPYPAGESF)))
AS UNLD_UTIL_CMD
, SRC_DBNAME, TSNAME, DSNUM
FROM SESSION.DGTT_LAST_FIC
UNION ALL
SELECT 'UNLOAD' AS UTIL_TYPE
, (MOD(ORDER_ROW_NBR,3)+1) AS CARD_NBR
, ORDER_ROW_NBR
, 2000 AS LINE_NBR
, ' FROMCOPY '||STRIP(FIC_DSNAME)||' FORMAT INTERNAL'
AS UNLD_UTIL_CMD
, SRC_DBNAME, TSNAME, DSNUM
FROM SESSION.DGTT_LAST_FIC
UNION ALL
SELECT 'UNLOAD' AS UTIL_TYPE
, (MOD(ORDER_ROW_NBR,3)+1) AS CARD_NBR
, ORDER_ROW_NBR
, 3000 AS LINE_NBR
, ' UNLDDN UNLDDATA PUNCHDDN GENLDP1'
AS UNLD_UTIL_CMD
, SRC_DBNAME, TSNAME, DSNUM
FROM SESSION.DGTT_LAST_FIC
;



/**********************************************************************/
/***********************************************************************
FINALLY, SELECT THE UTIL_CMD COL FROM THE DGTT
> IT SHOULD BE NOT NULL SO DSNTIAUL DOES NOT NEED TO WORRY ABOUT
THE NULL INDICATOR IN THE RESULT SET.. HELPS KEEP THE OUTPUT 80 CH
> AND DOING 3 SELECTS - ONE FOR EACH CARD_NBR!
> SO THIS HELPS ME ESENTIALLY FETCH EVERY NTH ROW!
***********************************************************************/
SELECT UTIL_CMD
FROM SESSION.DGTT_UTIL_CMDS
WHERE UTIL_TYPE='UNLOAD' AND CARD_NBR =1
ORDER BY ORDER_ROW_NBR, LINE_NBR
;
SELECT UTIL_CMD
FROM SESSION.DGTT_UTIL_CMDS
WHERE UTIL_TYPE='UNLOAD' AND CARD_NBR =2
ORDER BY ORDER_ROW_NBR, LINE_NBR
;
SELECT UTIL_CMD
FROM SESSION.DGTT_UTIL_CMDS
WHERE UTIL_TYPE='UNLOAD' AND CARD_NBR =3
ORDER BY ORDER_ROW_NBR, LINE_NBR
;
/* end of SQL script */

The output of the last SELECTs went straight into a normal 80 char PDS.

  • You can see a sample output below (in blue).

Each of the 3 UNLOAD utility control cards was sorted with equal quantity of UNLOAD statements with the large FIC spread across the 3 and unloaded from largest down to smallest.

It ran pretty good in real life (other hiccups occurred, but you do not need to know)

//************************************************************
//UNLOAD EXEC DSNUPROC,SYSTEM=PDBC,TIME=600
//GENLDP1 DD DISP=(MOD,CATLG,CATLG),DSN=TCL.TCLUF4.GENLDP14,
// SPACE=(TRK,(15,15)),UNIT=WORK
//SYSIN DD DISP=SHR,DSN=TCL.DBA.CTLCARDS(P1P4UNL1) template
// DD DISP=SHR,DSN=TCL.DBA.CTLCARDS.P1P4(UNLDAA)

UNLOAD TABLESPACE DCL01P.ZCFLW PART 14 -- COPYPAGES: 126067
FROMCOPY PCL.Db2.LF.D19329.T0308.DCL01P.ZCFLW.P014 FORMAT INTERNAL
UNLDDN UNLDDATA PUNCHDDN GENLDP1
UNLOAD TABLESPACE DCL01P.ZPOL -- COPYPAGES: 97283
FROMCOPY PCL.Db2.LF.D19329.T0308.DCL01P.ZPOL.P000 FORMAT INTERNAL
UNLDDN UNLDDATA PUNCHDDN GENLDP1
 

If you read this far, here is the logical conclusion to my story.  Using UNLOAD utility, it generated a sample LOAD card for me in the PUNCHDDN of GENLDP1 (the DD name for my SYSPUNCH).  I specified this DD in my JCL with DISP=MOD so each UNLOAD would add the next generated sample LOAD at the end of the PUNCHDDN.  I love that UNLOAD utility generates LOAD cards with a TEMPLATE and the generated LOAD references the template.  Of course, the generated LOAD is for putting the data back into the source.  I was able to easily copy and modify (with batch FileAid in my case) this generated LOAD PUNCHDDN and change the P1 to P4 (and change RESUME YES to REPLACE NOCOPYPEND).  The modified LOAD was what was used to LOAD the target.  It ran pretty good.  Multiple times.

 

Did you read this far in my post? 

Go to the aha-ha website and vote for my RFE (request for enhancement) to IBM to enhance Db2 functionality.  These RFE relate to the SQL discussed above! 

https://ibm-data-and-ai.ideas.aha.io/ideas/Db24ZOS-I-1056

extend MERGE syntax to allow Common Table Expressions (CTE)

https://ibm-data-and-ai.ideas.aha.io/ideas/Db24ZOS-I-940

DGTT - declare and use in one SQL statement (basically, this RFE is extension to syntax and functionality of DGTT)

3 Likes
Recent Stories
Anomaly Detection in Python and SQL (part 2)

Db2 Client Packages

INDEX COMPRESSION in Db2 Z, A recap and overview!