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)

Getting the most out of CICS

CICS has been around for a long time and has seen an enormous number of changes over the years. And that’s the reason it is still in constant use at so many Fortune 500 companies. It’s re-invented itself and kept up with computing trends, but never lost its overall reliability.

Optimizing its performance has been on the mind of CICS systems programmers since day 1. In this blog, I’d like to pass on two hints and tips that I’ve recently received.

The first one is to ensure that all exits etc are threadsafe and defined as such, so that OPENAPI TRUEs, such as DB2, can be exploited efficiently using OTE (Open Transaction Environment), the dynamic plan exit, for example, and the XRMIIN and XRMIOUT GLUEs. This is to avoid TCB switching, which would have an adverse effect on performance. Readers might like to take a look at the recently updated redbook on threadsafety in CICS.

The second tip is about WS-Security, and it really says, use WS-Security only if it is what you need! It’s worth remembering that the performance impact of using WS-Security to secure Web services is not insignificant. Therefore, if SSL (point-to-point) security is adequate, then use it rather than WS-Security because performance will be better! Also, if all you require is a simple username/password check, then it is better to implement this via a handler program in the pipeline rather than using the WS-Security solution. So, think about what security you really want – in many situations, WS-Security is not what is required and better performing alternatives probably exist.

Talking about CICS (hopefully) gives me the opportunity to mention Xephon’s CICS Update (www.xephonusa.com) journal. If you have something to say about CICS, a simple hint and tip, or you have recently completed a project that is worth sharing (the mistakes and the successes), or you’ve tested software that runs with CICS and identified its strengths and weaknesses, or even CICS performance tips, then contact the editor (TrevorE@xephon.com). Articles from new contributors are always welcome.

My thanks to Andy Wright and Darren Beard from Hursley for their CICS tips.

Get Mainframe Weekly on your desktop

I have been using widgets on my laptop for ages now. Widgets were originally created for the Mac as small mini-applications that just sat on your desktop until you needed them. They could also be fun to use. A company called Konfabulator created a JavaScript run-time engine so the widgets would also run on Windows.

Yahoo bought Konfabulator some time ago. If you don’t have it already, go to http://widgets.yahoo.com/ and download the software. Cleverly (in a dull sort of way) and ahead of Vista, you can make the widgets semi-transparent (or, I suppose, totally transparent if you wanted). Once you’ve got the software you can then go to the widget gallery (http://www.widgetgallery.com) and choose from over 3500 (and growing all the time) widgets. These have usually been written by people who wanted a simple tool or toy on their desktop.

There are so many types to choose from. For example, on my home computer I have an excellent drum kit widget. There are also lots of traffic camera widgets, so you can check the state of the traffic before you set off on a journey. I have a Dilbert cartoon downloaded each day, I have a link straight into wikipedia to look up just about anything, I have the weather for the next few days, I have a handy calendar, and I even have a dalek that wanders around my screen (don’t ask). Like I say, the choice is huge.

Anyway, if you like widgets, you can now have Mainframe Weekly come straight to your desktop. Download widget 3252, the RSS Feed Viewer (updated 27 November) by Rob Pinkasavage. Once you’ve downloaded it and installed it, you need to right click on the screen and then click on Widget Preferences. Where it says “Selected Feed:”, enter http://mainframeweekly.blogspot.com/rss.xml and click the “Save” button. I set the “Headline count:” to 15. If you click on the Window icon at the top, you can change the opacity (if you want).

Anyway, once you’ve done that, you can have Mainframe Weekly on your desktop all the time and you’ll know immediately when a new blog has been added - like this one.