DB2 Stored Procedure Last Used Date

Ever wonder when was a stored procedure last used/invoked in DB2 LUW? Knowing this may be helpful in determining whether a stored procedure is still in use or how recently it was used. Run this query to find this information.

SELECT PR.PROCSCHEMA
      ,PR.PROCNAME
      ,PK.LASTUSED
  FROM SYSCAT.PROCEDURES PR
      ,SYSCAT.ROUTINEDEP RD
      ,SYSCAT.PACKAGES   PK
 WHERE PR.SPECIFICNAME =  RD.ROUTINENAME
   AND RD.BNAME        =  PK.PKGNAME
   AND PR.PROCSCHEMA  <> 'SYSIBMADM'
   AND PK.LASTUSED     > '01/01/0001'
 ORDER BY PK.LASTUSED DESC
  WITH UR;

Remember that, you can use this query only from DB2 LUW V9.7 onwards.

DB2 DIAGNOSE DISPLAY MEPL

DIAGNOSE is a DB2 for z/OS utility that, as the name suggests, is used in diagnosing problems. It generates information that is useful in diagnosing problems. But you should use this utility only under the direction of IBM Software Support staff, except for DIAGNOSE DISPLAY MEPL.

You can use the DISPLAY MEPL even without contacting IBM in the following scenario.

First of all, MEPL is an acronym for Module Entry Point List (MEPL). DIAGNOSE DISPLAY MEPL dumps module entry point lists to SYSPRINT.

Recently, we had an issue with a query after applying the maintenance RSU1304. I opened a SR (Service Request) with IBM and the support staff found a PTF in PE status. It took about 2 months to find a fix and eventually when I applied a PTF to fix the PTF in PE, it didn't work. The query still had the same problem.

IBM asked me to run this utility to find if the PTF is in fact applied to the subsystem that we were testing the query. The SYSPRINT from this utility didn't show the PTF. That's when I realized that the PTF was applied to the maintenance library, but the load module was not copied to the production run time library. After copying the load module from maintenance library to run time library, the query ran good.

Usually I do search the SMPPTS library to see if PTF that was applied recently is found, but that's not foolproof. This method of running the DIAGNOSE DISPLAY MEPL utility looks foolproof. Lesson learnt !!

Here is a sample job to run this utility:

//jobcard
//JOBLIB   DD  DISP=SHR,DSN=SYS2.DB2.DBxx.SDSNLOAD
//STEP0010 EXEC PGM=DSNUTILB,PARM='DBxx,MEPL',REGION=4M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  DIAGNOSE DISPLAY MEPL
/*

Thanks for stopping by.

CBT Tape

Just a quick note about a freeware site for mainframers. Check this out: http://www.cbttape.org/.

The CBT tape is a collection of freeware for almost all open-source distribution for the IBM mainframe z/OS environment. The entire collection of free software can be downloaded at the site as well as freeware for JES2, JES3, and other related S/390 material. A large repository of links to S/390 related sites on the Internet and archives of older material are also features.

How to load LOB tablespaces using DSN1COPY?

In the past, I've tried to load tablespaces with LOB columns using the regular IBM Load, BMC Load Plus & CA Fast Load. None of these tools support LOB tablespaces. You get into a series of errors.

The only utility that has ever worked for me is DSN1COPY. It's a pain to use DSN1COPY though. You need several things to go right for this to work. Let me try to explain as simple as possible.

In this example, I'm copying data from an imagecopy dataset into a table in a TEST subsystem. The first thing you need to remember is that, you need to know the DBID of the database, PSID of the tablespace and OBID of the table for both the source and target subsystems.

These are the steps:

1. STOP the base and LOB tablespaces and indexes for the LOB table on the target subsystem

2. Run DSN1COPY with the right parameters. I use PARM='PAGESIZE(32K),LOB,FULLCOPY,OBIDXLAT,RESET'. Supply the imagecopy dataset of the table on the source subsystem for SYSUT1 and supply the DSNDBC linear dataset of the table on the target subsystem for SYSUT2.

3. START the base and LOB tablespaces and indexes for the LOB table on the target subsystem

4. Rebuild the indexes

5. Run runstats

If the DSN1COPY step abends with "DSN1992I VSAM PUT ERROR, RPLERREG = 008, RPLERRCD = 028", check JESMSGLG. If you see a message like this "IEC070I 204-220,jobname,stepname,SYSUT2,6BF7,DBD115& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001", it means you do not have enough space in that dataset. Make sure, you assign multiple datasets with suffix A001, A002, etc. in the target subsystem, it needs to accommodate all data from the source.

If you see a message like this "IEC070I 209-220,jobname,stepname,SYSUT2,89B5,DBD235,& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001,", that means no space is available in the DASD volume. To fix this problem, make the dataset multi volume so that it can extend space into multiple volumes.

HTH !!

Date of a particular day

A developer asked me, how do I find the date for last Sunday. It was a Wednesday when he asked. After some thinking, this is what I gave him as solution. This works in DB2, not sure about other databases. 

SELECT CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS 
  FROM SYSIBM.SYSDUMMY1                                 

This solution works only for Sunday. For Monday, change 1 to 2, for Tuesday 3 and so on.

I've not done an extensive testing, so take it with a grain of salt :)