DB2 performance tip

In last week’s blog I passed on two CICS tips that I had been sent. This week I’d like to look at a DB2 performance tip. This one applies to DB2 V8, it is to do with columns, TEXT, and STMT, and was sent in by Bernard Zver, a regular contributor to the DB2 Update (http://www.xephonusa.com/) journal.

If you are in NFM (NEWFUN = YES), the text of your DBRMs, which is stored in column TEXT of the catalog table SYSIBM.SYSSTMT, or the STMT column of SYSIBM.SYSPACKSTMT, is encoded in Unicode. These columns are marked with the FOR BIT DATA attribute, which means that if you look at the column, using QMF or SPUFI for example, you are no longer able to easily read the contents, because columns with the FOR BIT DATA attribute are never converted, and these columns are now in Unicode.

You can use the CAST function to convert the TEXT column to EBCDIC:

SELECT
CAST(CAST(STMT AS VARCHAR(3500) CCSID 1208) AS VARCHAR(3500) CCSID EBCDIC)
FROM SYSIBM.SYSPACKSTMT
WHERE COLLID='DSNUTILS'
AND NOT (STMTNO=0 AND SEQNO=0 AND SECTNO=0)

This query is not user friendly, so here is a scalar user-defined function to make this query easy:

CREATE FUNCTION INFO.STMT
(ITEM VARCHAR(3500))
RETURNS VARCHAR(3500)
LANGUAGE SQL
SPECIFIC STMT
RETURN
CAST(CAST(ITEM AS VARCHAR(3500) CCSID 1208) AS VARCHAR(3500) CCSID EBCDIC) ;
-- Grant statements
COMMIT ;
GRANT EXECUTE ON SPECIFIC FUNCTION INFO.STMT TO PUBLIC ;

This query uses the UDF INFO.STMT:

SELECT
INFO.STMT(STMT)
FROM SYSIBM.SYSPACKSTMT
WHERE COLLID='DSNUTILS'
AND NOT (STMTNO=0 AND SEQNO=0 AND SECTNO=0) ;

The result looks like:

.......DECLARE SYSIBM . SYSPRINT TABLE ( SEQNO INTEGER
.......DECLARE SYSPRINT CURSOR WITH RETURN FOR SELECT
.......DELETE FROM SYSIBM . SYSPRINT ....
.......OPEN SYSPRINT ....
.......INSERT INTO SYSIBM . SYSPRINT ( SEQNO , TEXT )

If you have any queries about this, Bernard’s e-mail address is bernard.zver@informatika.si.
Thanks Bernard.

This will be my last blog until the New Year. So, if you do celebrate Christmas – merry Christmas and a happy New Year. If you don’t celebrate Christmas, then have a good time anyway.
Trevor Eddolls (trevore@xephon.com)