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