Generating Commands from the DB2 Catalog

Introduction

A DB2 subsystem holds a ton of information about itself in the system catalog tables, and DB2 provides the means to access this information - SQL. The two together provide a powerful means of automatically creating commands or input to utilities with no human intervention, and of creating reports without external software.

So, for instance, we could have two jobs in our batch suite to do runstats - the first job to generate the runstats commands, and the second to run those commands. If another tablespace is created, it'll be picked up automatically without anyone having to do anything.

A Simple Query

The catalog contains information about all DB2 objects including attributes and statistical information. First we'll look at some very simple usage of that information - creating RUNSTATS commands which will be fed into a utility job.

Here all we need to get from the catalog is a list of tablespaces which satisfy some criteria - for this example, we want tablespaces whose names start with SDB% and exist in database DDBWORK.

This query will do it (I'm doing this in SPUFI):

 SELECT DBNAME,NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
  

This gives the result:

 ---------+---------+---------+---------+---------+---------+---------+---------+
 DBNAME    NAME
 ---------+---------+---------+---------+---------+---------+---------+---------+
 DDBWORK   SDBACCT
 DDBWORK   SDBACS9
 DDBWORK   SDBMAP
 DDBWORK   SDBRAP01
 DSNE610I NUMBER OF ROWS DISPLAYED IS 4
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
 

The name of the tablespace isn't in the exact form I want - I need dbname.tsname. Let's add a full-stop:

 SELECT DBNAME,'.',NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
 

This gives:

 ---------+---------+---------+---------+---------+---------+---------+---------+
 DBNAME       NAME
 ---------+---------+---------+---------+---------+---------+---------+---------+
 DDBWORK   .  SDBACCT
 DDBWORK   .  SDBACS9
 DDBWORK   .  SDBMAP
 DDBWORK   .  SDBRAP01
 

This still isn't in the form I want. There's an extra space after DDBWORK because it's less than eight characters, and there are extra spaces around the full-stop. I need to strip the trailing space off the database name and suppress the extra spaces between each field.

For each of these, I can use a built-in function.

To strip leading or trailing spaces, I can use STRIP - here it'll be STRIP(DBNAME).

To suppress the spaces that SPUFI puts inbetween fields, I need to join adjacent fields using CONCAT. For instance, specifying '.' CONCAT TSNAME suppresses the space between the full-stop and the tablespace name.

Putting this all together into a query gives:

 SELECT STRIP(DBNAME) CONCAT '.' CONCAT NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
 

and the result:

 ---------+---------+---------+---------+---------+---------+---------+---------+

 ---------+---------+---------+---------+---------+---------+---------+---------+
 DDBWORK.SDBACCT
 DDBWORK.SDBACS9
 DDBWORK.SDBMAP
 DDBWORK.SDBRAP01
  

Note that I've lost the column names as I've created 'my own' columns by concatenation.

I'm almost there now - all I'm missing is RUNSTATS TABLESPACE on the front of each row. As this is always the same, I can just add a literal into the SELECT statement:

 SELECT 'RUNSTATS TABLESPACE',STRIP(DBNAME) CONCAT '.' CONCAT NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
  

giving me the result:

 ---------+---------+---------+---------+---------+---------+---------+---------+

 ---------+---------+---------+---------+---------+---------+---------+---------+
 RUNSTATS TABLESPACE  DDBWORK.SDBACCT
 RUNSTATS TABLESPACE  DDBWORK.SDBACS9
 RUNSTATS TABLESPACE  DDBWORK.SDBMAP
 RUNSTATS TABLESPACE  DDBWORK.SDBRAP01
 

And that's exactly what I was after. So am I finished? - well perhaps not. As you've seen in all the output, I've got all sorts of other gubbins in with my result rows - column header lines, SQL codes, reports on how many rows I've produced. If I feed that sort of stuff into DSNUTILB, it'll throw a wobbly. I need to run my SQL through something that'll give me just the stuff I want.

Enter DSNTIAUL

We normally think of DSNTIAUL as the sample unload program, but it'll take any sort of SQL, as long as we give it the SQL parameter.

So, to feed my query into DSNTIAUL, the SYSTSIN and SYSIN need to look like this:

 //SYSTSIN  DD  *
  RUN PROG(DSNTIAUL) PLAN(DSNTIAUL)  -
  PARM('SQL')
  END
 //SYSIN    DD  *
 SELECT 'RUNSTATS TABLESPACE',STRIP(DBNAME) CONCAT '.' CONCAT NAME
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
 

Browsing the output dataset, I can see I've just got the rows and nothing else, but another problem has emerged which you can see by turning on the hex display in ISPF browse:

 ----+----1----+----2----+----3----+----4---
 ----+----F----+----F----+----F----+----F---
 ----+----1----+----2----+----3----+----4---
  ------------------------------------------
 ********************************* TOP OF DA


  ------------------------------------------
 ..RUNSTATS TABLESPACE..DDBWORK.SDBACCT .
 01DEDEECEE4ECCDCEDCCC01CCCEDDD4ECCCCCE40
 039452313203123527135004426692B242133300
  ------------------------------------------
 ..RUNSTATS TABLESPACE..DDBWORK.SDBACS9 .
 01DEDEECEE4ECCDCEDCCC01CCCEDDD4ECCCCEF40
 039452313203123527135004426692B242132900
  ------------------------------------------
 ..RUNSTATS TABLESPACE..DDBWORK.SDBMAP  .
 01DEDEECEE4ECCDCEDCCC01CCCEDDD4ECCDCD440
 039452313203123527135004426692B242417000
  ------------------------------------------
 ..RUNSTATS TABLESPACE..DDBWORK.SDBRAP01.
 01DEDEECEE4ECCDCEDCCC01CCCEDDD4ECCDCDFF0
 039452313203123527135004426692B242917010
 

DSNTIAUL outputs rows in a format suitable for Load. It considers the strings we've created to be VARCHARs, so it's output a length on the front of each field.

In DB2 V6, this is very easy to solve - the built-in function CHAR can now be used on all sorts of datatypes, including VARCHAR. We need to wrap it around each field we've created:

 SELECT CHAR('RUNSTATS TABLESPACE '),
     CHAR(STRIP(DBNAME) CONCAT '.' CONCAT NAME)
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
  

This gives us what we want.

  ------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBACCT
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCCCCE44
 945231320312352713504426692B242133300
  ------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBACS9
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCCCEF44
 945231320312352713504426692B242132900
  ------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBMAP
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCDCD444
 945231320312352713504426692B242417000
  ------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBRAP01
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCDCDFF4
 945231320312352713504426692B242917010
  ------------------------------------
 

Just one last thing - you can see that the fields are short - DSNUTILB may well object to that, so we can pad out the second field with spaces, lengthening the line, by adding a second parameter to CHAR:

 SELECT CHAR('RUNSTATS TABLESPACE '),
     CHAR(STRIP(DBNAME) CONCAT '.' CONCAT NAME,60)
     FROM SYSIBM.SYSTABLESPACE
     WHERE DBNAME = 'DDBWORK' AND NAME LIKE 'SDB%';
 

giving us:

 ********************************* TOP OF DATA **************************


  -----------------------------------------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBACCT
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCCCCE4444444444444444444444444444444444444
 945231320312352713504426692B24213330000000000000000000000000000000000000
  -----------------------------------------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBACS9
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCCCEF4444444444444444444444444444444444444
 945231320312352713504426692B24213290000000000000000000000000000000000000
  -----------------------------------------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBMAP
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCDCD44444444444444444444444444444444444444
 945231320312352713504426692B24241700000000000000000000000000000000000000
  -----------------------------------------------------------------------
 RUNSTATS TABLESPACE DDBWORK.SDBRAP01
 DEDEECEE4ECCDCEDCCC4CCCEDDD4ECCDCDFF444444444444444444444444444444444444
 945231320312352713504426692B24291701000000000000000000000000000000000000
  -----------------------------------------------------------------------
 

Remember that there's a lot of other stuff in the catalog that you can use in your queries. For instance, you might not want to runstat all the tablespaces every night. By adding the predicate:

AND STATSTIME < CURRENT TIMESTAMP - 3 MONTHS

you will only pick up tablespaces that haven't been runstatted in the last three months, helping you to stagger your runstats.

Something a bit more complex

Here we'll look at generating some SQL - we'll generate some ALTER TABLESPACE statements each tablespace where the primary quantity is less than a tenth of the space it's taking up on disk.

The generated SQL will look something like this:

ALTER TABLESPACE dbname.tsname PART n PRIQTY qty

We'll use catalog tablespace SYSIBM.SYSTABLEPART for the data we need - obviously, we'll need the database name (DBNAME) and the tablespace name (this time called TSNAME) again; we'll need the PARTITION number as well - this is zero if the tablespace isn't partitioned; we'll also need to look at the primary quantity - PQTY, showing the primary quantity in 4K blocks, and SPACE, showing the space taken on DASD in K.

Let's run a query to see what comes back. We need to multiply PQTY by 4 to convert it to K so that we can compare it to SPACE.

 SELECT DBNAME,TSNAME,PARTITION,PQTY*4 AS PQTYK ,SPACE
     FROM SYSIBM.SYSTABLEPART
         WHERE DBNAME = 'PTDB'
             AND PQTY*4 < SPACE / 10;
 

This gives us:

 ---------+---------+---------+---------+---------+---------+---------+---------+
 DBNAME    TSNAME    PARTITION        PQTYK        SPACE
 ---------+---------+---------+---------+---------+---------+---------+---------+
 PTDB      PTG500T2         12           16          528
 PTDB      PTITSGBK          0           52          576
 PTDB      PTITSGB2          0           52          576
 PTDB      PTITSLOG          0           52         2640
 PTDB      PTITSRCE          0           20          288
 PTDB      PTITSRU1          0           20          288
 PTDB      PTITSSEP          0           52          576
 PTDB      PTITSTBL          0           52         1104
 PTDB      PTPQOTS           0           16          288
 PTDB      PTPQTTS1          0           16          288
 PTDB      PTPQTTS2          0           16          288
 PTDB      PTQQFTS1          0           16          288
 PTDB      PTQRFR12          0           12          192
 PTDB      PTQRFR41          0           12          192
 PTDB      PTVISTS1          0           16          288
 DSNE610I NUMBER OF ROWS DISPLAYED IS 15
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
 

I need to do the same sort of stuff that I did with the runstats commands to give us the ALTER statements:

SELECT 'ALTER TABLESPACE',STRIP(DBNAME) CONCAT '.' CONCAT STRIP(TSNAME),
     'PART',PARTITION,'PRIQTY',SPACE,';'
     FROM SYSIBM.SYSTABLEPART
         WHERE DBNAME = 'PTDB'
             AND PQTY*4 < SPACE / 10 ;
 

giving (I'm not showing all the rows this time):

 ---------+---------+---------+---------+---------+---------+---------+---------+
                                            PARTITION                SPACE
 ---------+---------+---------+---------+---------+---------+---------+---------+
 ALTER TABLESPACE  PTDB.PTG500T2      PART         12  PRIQTY          528  ;
 ALTER TABLESPACE  PTDB.PTITSGBK      PART          0  PRIQTY          576  ;
 ALTER TABLESPACE  PTDB.PTITSGB2      PART          0  PRIQTY          576  ;
 ALTER TABLESPACE  PTDB.PTITSLOG      PART          0  PRIQTY         2640  ;
 ALTER TABLESPACE  PTDB.PTITSRCE      PART          0  PRIQTY          288  ;
 ALTER TABLESPACE  PTDB.PTITSRU1      PART          0  PRIQTY          288  ;
 

This is pretty close to what I want, but the problem is that PART 0 is no good for a non-partitioned tablespace - I don't want anything there at all. For these I want to output a blank instead of the literal PART and instead of the partition number. DB2 gives us a way to do this with the CASE statement:

 SELECT 'ALTER TABLESPACE',STRIP(DBNAME) CONCAT '.' CONCAT STRIP(TSNAME),
     CASE WHEN PARTITION = 0
         THEN ' '
     ELSE
         'PART ' CONCAT CHAR(PARTITION)
     END ,
     'PRIQTY',SPACE,';'
     FROM SYSIBM.SYSTABLEPART
         WHERE DBNAME = 'PTDB'
             AND PQTY*4 < SPACE / 10
 

Here I'm saying that if the partition number is zero, I'll just output a space, otherwise I'll output the string PART concatenated with the string formed by taking the character value of the partition number (see I'm using CHAR again - this time to convert a numeric to a string). This gives me what I want.

 ---------+---------+---------+---------+---------+---------+---------+---------+
                                                                 SPACE
 ---------+---------+---------+---------+---------+---------+---------+---------+
 ALTER TABLESPACE  PTDB.PTG500T2      PART 12      PRIQTY          528  ;
 ALTER TABLESPACE  PTDB.PTITSGBK                   PRIQTY          576  ;
 ALTER TABLESPACE  PTDB.PTITSGB2                   PRIQTY          576  ;
 ALTER TABLESPACE  PTDB.PTITSLOG                   PRIQTY         2640  ;
 ALTER TABLESPACE  PTDB.PTITSRCE                   PRIQTY          288  ;
 ALTER TABLESPACE  PTDB.PTITSRU1                   PRIQTY          288  ;
 

As for the previous example, I need to put this into DSNTIAUL to get rid of all the headings and other stuff I don't want.

I've added the odd space and put CHAR around the strings:

 SELECT CHAR('ALTER TABLESPACE '),
  CHAR(STRIP(DBNAME) CONCAT '.' CONCAT STRIP(TSNAME)),
      CHAR(
     CASE WHEN PARTITION = 0
         THEN ' '
     ELSE
         'PART ' CONCAT CHAR(PARTITION)
     END ),
     CHAR('PRIQTY '),SPACE,CHAR(';')
     FROM SYSIBM.SYSTABLEPART
         WHERE DBNAME = 'PTDB'
             AND PQTY*4 < SPACE / 10
 ;
 

This is nearly OK:

 ALTER TABLESPACE PTDB.PTG500T2    PART 12    PRIQTY ....;
 CDECD4ECCDCEDCCC4DECC4DECFFFEF4444DCDE4FF4444DDCDEE400015
 133590312352713507342B7375003200007193012000079983800020E
  --------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSGBK               PRIQTY ... ;
 CDECD4ECCDCEDCCC4DECC4DECEECCD444444444444444DDCDEE400045
 133590312352713507342B7393272200000000000000079983800020E
  --------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSGB2               PRIQTY ... ;
 CDECD4ECCDCEDCCC4DECC4DECEECCF444444444444444DDCDEE400045
 133590312352713507342B7393272200000000000000079983800020E
  --------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSLOG               PRIQTY ...&;
 CDECD4ECCDCEDCCC4DECC4DECEEDDC444444444444444DDCDEE400055
 133590312352713507342B73932367000000000000000799838000A0E

 

Except the numeric amount has come out in hex. We need a CHAR around that as well:

 SELECT CHAR('ALTER TABLESPACE '),
 CHAR(STRIP(DBNAME) CONCAT '.' CONCAT STRIP(TSNAME)),
     CHAR(
     CASE WHEN PARTITION = 0
         THEN ' '
     ELSE
         'PART ' CONCAT CHAR(PARTITION)
     END ),
     CHAR('PRIQTY '),CHAR(SPACE),CHAR(';')
     FROM SYSIBM.SYSTABLEPART
         WHERE DBNAME = 'PTDB'
             AND PQTY*4 < SPACE / 10
 ;
 

This gives us our final result:

 ALTER TABLESPACE PTDB.PTG500T2    PART 12    PRIQTY 528        ;
 CDECD4ECCDCEDCCC4DECC4DECFFFEF4444DCDE4FF4444DDCDEE4FFF444444445
 133590312352713507342B73750032000071930120000799838052800000000E
  -------------------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSGBK               PRIQTY 576        ;
 CDECD4ECCDCEDCCC4DECC4DECEECCD444444444444444DDCDEE4FFF444444445
 133590312352713507342B73932722000000000000000799838057600000000E
  -------------------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSGB2               PRIQTY 576        ;
 CDECD4ECCDCEDCCC4DECC4DECEECCF444444444444444DDCDEE4FFF444444445
 133590312352713507342B73932722000000000000000799838057600000000E
  -------------------------------------------------------------------
 ALTER TABLESPACE PTDB.PTITSLOG               PRIQTY 2640       ;
 CDECD4ECCDCEDCCC4DECC4DECEEDDC444444444444444DDCDEE4FFFF44444445
 133590312352713507342B73932367000000000000000799838026400000000E
 

 

 

Alan C Smith, 2003