CICS Scheduled task

I've a CICS program which is invoked when CICS is started (either a cold start or a warm start) through a PLT entry "DFHPLT TYPE=ENTRY,PROGRAM=program_name".

Our shop cycles CICS every Sunday. But I wanted to run this program every day at 10 a.m. and again at 10 p.m. So, what I did was, I coded this piece at the end of the program (just before "EXEC CICS RETURN END-EXEC"):

IF WS-TIME > '21.59.59'
  EXEC CICS START TRANSID(WS-THIS-TRAN)
                  REQID(WS-REQID)
                  AT HOURS(10)
  END-EXEC
ELSE
  EXEC CICS START TRANSID(WS-THIS-TRAN)
                  REQID(WS-REQID)
                  AT HOURS(22)
  END-EXEC
END-IF

Thanks to my colleague who taught me this technique.

ISRDDN

When you log on to your mainframe TSO (Time Sharing Option) session, you can use the TSO supplied commands. Apart from those commands, if you know how to code in REXX or CLIST, you can come up with your own commands.

There are also occasions when you use commands written by some 3rd party vendors. For example, Quick Reference - you invoke their product by typing "QW keyword". Sometimes you wonder, how does TSO know these commands exist or where to get the source/load from - the source could be in a lot of different datasets.

When you logon into your TSO session, the system preallocates a bunch of datasets. This is determined by your logon PROC. Usually the system programmer determines the appropriate logon PROC for each user. For example, a developer might need a set of datasets preallocated, while a DBA might need a different set of datasets preallocated. So, when you type QW in the command line, TSO searches in these datasets and executes it.

To find out which are all the datasets that have been allocated to your TSO session, type ISRDDN (in some shops, you may have to type TSO ISRDDN) in the command line and hit enter. You will get all the datasets that are allocated and the corresponding DD names. If you want to find the source of a particular CLIST, REXX, Panel, etc., just type "M membername" (without quotes). e.g. M qw -- will search for the source code or load for QW.

QMF Tips

If you want to delete/drop a saved data table in QMF, read on.

After doing a "LI TABLES" to list all the saved data tables, if you want to drop one of the tables, use command "ERASE" (DROP, DELETE etc are not for this purpose).

BMC UNLOAD PLUS Tips

BMC Unload Plus is a high-performance DB2 UNLOAD utility (just like CA/Platinum FAST UNLOAD utility). The performance of this utility can be controlled by proper use of the keyword "DIRECT".

DIRECT YES: UNLOAD PLUS reads data directly from the table space data set or image copy data sets to unload the data, using a SELECT-like syntaxfor data selection. SELECT functionality is a limited subset of thefunctionality that is normally available in DB2 SQL. The benefit of the DIRECT YES mode is maximum performance when unloading large volumes of data.

DIRECT NO: UNLOAD PLUS processes the SELECT statement and reads the data usingDB2 dynamic SQL. This mode is not a high-performance solution for unloading large volumes of data. The benefit of the DIRECT NO mode is a full range of DB2 SQL SELECTfunctionality, including joined tables, subqueries, and so on. This functionality includes many of the features that are availablein UNLOAD PLUS including DB2 parallelism, data type conversions, output formatting.

DB2 z/OS DBRM Layout

If you look at a DBRM member, especially in DB2 for z/OS V8 or higher, you cannot make anything out of it. It looks like garbage at worst, or looks like a loadlib member at best. But it's not very difficult to find what's in there, if you know a little bit assembler language.

The layout of DBRM can be found in "high-level-qualifier.SDSNMACS(DSNXNBRM)". This is in macro format.

DB2 ROUND_TIMESTAMP function

ROUND_TIMESTAMP(CURRENT TIMESTAMP,'MI'):

The ROUND_TIMESTAMP function returns a timestamp that is the expression rounded to the unit specified by the format-string. In this case, MI means minutes.

For example, the output of

SELECT ROUND_TIMESTAMP('2008-10-16-14.46.22.121222','MI')
FROM SYSIBM.SYSDUMMY1

looks like this:
2008-10-16-14.46.00.000000

Who updated that DB2 table? (how to read DB2 log?)

There are situations when people drop and re-create a DB2 object, such as a Stored Procedure (usually in the development or test environment) using a common shared user-id. It may be necessary to find out who made the change, but may not be possible, because the person used a CURRENT SQLID = 'shared id' or something like that.

Or, sometimes, someone might have updated (or deleted or inserted) a table without logging it anywhere (or that's what they thought). There is no straight forward way to find who updated this table. But, I didn't say that it is not possible.

To find this out, you may need to read the DB2 log. Here are the steps to do just that:

1. Run a batch job which executes DSNJU004 with the correct Boot Strap dataset (BSDS). This program reads the BSDS and displays information from DB2*MSTR, including the active and archive logs.
2. Get the archive log dataset name that was created around the time that the DB2 object got changed. If the log is still active for time that you are looking for, archive it manually (using DB2 ARCHIVE command)
3. Run a batch job which executes DSN1LOGP with SUMMARY(ONLY) option. This program reads the log, formats it and prints it in the output  dataset.
4. In this job output, search the database in which the change was made. Convert the STARTLRSN time to readable timestamp value (I've a REXX script to do it, if you need it, please let me know). If this matches with the time when the DB2 object was changed, then get other details such as CONNID, AUTHID etc.

Note: If you need sample JCLs for the steps mentioned in this blog entry, you can contact me @ ajjuba@yahoo.com.

LUWID to IP Address conversion

Often it is required to find the server from which a distributed query comes from. In those cases, find the LUWID from DB2 MSTR: e.g.,

STC13507 DSNL032I +DB2P DSNLZDJN DRDA EXCEPTION CONDITION IN 324
REQUEST FROM REQUESTOR LOCATION=10.130.11.23 FOR THREAD WITH
LUWID=GA820B17.O412.0F9017030439=152303
REASON=00D35601

In the above example, the LUWID is GA820B17. To convert this to a IP address, start from the 2nd character and convert as follows:
Decimal (A) = 10
Decimal (82) = 130
Decimal (0B) = 11
Decimal (17) = 23
So the IP address is 10.130.11.23.

Visual Explain Tips

This blog entry explains how to get the information necessary to setup your connection from Visual Explain (VE) to DB2 for z/OS.

When you bring up VE V8, the first screen you are presented with is the "List Databases" panel. This screen lists all of the databases VE is aware of that you can connect to. However, if you are using VE for the first time, you will have to set up your own connections.

On this screen click the "Catalog" button. The "Catalog a Database" screen is displayed. On the top of this screen, there are four mandatory input fields (boxes): "Host Name", "Database Name", "Database Alias", and "Port Number". Now here's the cool part.

Display your DB2 xxxxMSTR address space. Once in the Jeslog portion of the xxxxMSTR, search the DB2 message DSNL004I. It should look similar to the following message only with your values filling in the fields in

DSNL004I = DDF START COMPLETE 837
LOCATION //your location name here//
LU USIBMSY.SYEC1DB2
GENERICLU -NONE
DOMAIN //your hostname here//
TCPPORT //your TCP port number here//
RESPORT 5001

This message has everything you will need to complete the "Catalog a Database" screen. The LOCATION has the value that you need to specify in the "Database Name" box. DOMAIN has the value you need to complete the "Host Name" box. TCPPORT has the value for "Port Number".

That leaves "Database Alias". This can be anything you want to specify. I usually use the same value I specified for "Database Name". I would also strongly suggest filling in the "Comment" box so you have some idea later on what this entry was for.