Set up mainframe SLIP trap

Often times, when you are diagnosing a problem on mainframe, if you contact the tech support of the product, you will be asked to set up a SLIP trap.

When you set up SLIP trap, you can indicate what kinds of events you want trapped and the system conditions for the trap, then specify what action the system is to take when the event occurs during the specified conditions. More info on SLIP trap

How do you set up SLIP trap?

Your SLIP trap may look something as simple as this:

SLIP SET,A=SVCD,COMP=0C4,ID=KC01,JOBLIST=(jobname),END

or something as complicated as this:

SLIP SET,IF,ID=slipid,A=SYNCSVCD,ML=1,ASID=(xxxxx),
ASIDLST=(xxxxx,yyyy,zzzz),PVTMOD=(module,00B0),
DATA=((12R?+60E,EQ,54475249,OR,12R?+60E,EQ,E3C7D9C9),
AND,12R?+756,EQ,E4D5D2D5,
AND,12R?+4A8,EQ,C4C9E2E3,AND,12R?+4AC,EQ,E2C5D9E5),END


If the command spreads for 2 lines are less, you can enter it in SDSF - System Command Extension panel:

However, if it spans for more than 2 lines, you cannot enter it in this panel. You need to create a member in SYS1.PARMLIB with the naming convention IEASLPii and save the command in this member. After saving this member, issue this command on SDSF

/T SLIP=ii

This will enable the SLIP trap.

After you set up the trap, run this MVS system command to display it's status:
/RO lpar,D SLIP=slipid

HTH.

DFSMS Message ARC1237I - No Space for Migration Copy

This morning I was trying to SMP/E RECEIVE a bunch of PTFs as part of a DB2 tool maintenance. After receiving 20 PTFs, the job went down with a storage space abend IEC032I E37-04 on the SMPPTS dataset.

I noticed that the SMPPTS dataset already had 16 extents. I tried to add some cylinders, but I couldn't. So I tried to change the SMS volume by HMIGRATEing and HRECALLing the dataset. But the HMIG command failed with this message:

ARC1001I hlq.SMPPTS MIGRATE FAILED, RC=0037, REAS=0000
ARC1237I NO SPACE FOR MIGRATION COPY    

I tried migrating the dataset directly to Level 2 by issuing this command:

HMIG / ML2

When I issued this command, it failed with the message:

ARC1001I hlq.SMPPTS MIGRATE FAILED, RC=0080, REAS=0000
ARC1280I MIGRATION FAILED - DATA SET IS IN NEED OF BACKUP

I issued this command to take a DFSMS backup:

HBACKDS

After the backup was complete, I tried to migrated to L2, it worked !!

After the migrate was complete, I recalled the dataset. It went to a different volume with more space and also had only 1 extent filled.

DB2 Performance Improvement tip using COALESCE function

One of the developers at our shop fed his query to an optimizer tool and the optimized query looked kind of weird.

This is the original query:
DELETE FROM TABLE_A
 WHERE COL1 IN (SELECT COL1
                          FROM TABLE_A R
                               INNER JOIN TABLE_B O
                                  ON O.COL2 = R.COL1
                         WHERE O.COL3 != R.COL4)


Query suggested by the optimizer:
DELETE
  FROM TABLE_A
 WHERE EXISTS (SELECT 'X'
                 FROM TABLE_A R
                      INNER JOIN TABLE_B O
                         ON R.COL4 <> COALESCE (O.COL3, O.COL3)
                            AND R.COL1 = COALESCE (O.COL2, O.COL2)
                WHERE COL1 = TABLE_A.COL1
                ORDER BY R.COL4)

The puzzling part here is in the revised query: COALESCE (O.COL3, O.COL3). Why would anyone feed the same column twice to the COALESCE function? It's going to give the same result.

Here is the explanation by IBM though: http://www-01.ibm.com/support/docview.wss?uid=swg21259831

In their own words:

Question

Why might adding a special form of the COALESCE predicate produce cheaper data access plans and result in better performance.

Cause

A "no-op" coalesce() predicate of the form "COALESCE(X, X) = X" introduces an estimation error into the planning of any query using it. Currently the DB2 query compiler doesn't have the capability of dissecting that predicate and determining that all rows actually satisfy it. As a result, the predicate artificially reduces the estimated number of rows coming from some part of a query plan. This smaller row estimate usually reduces the row and cost estimates for the rest of query planning, and sometimes results in a different plan being chosen because relative estimates between different candidate plans have changed.

Why can this do-nothing predicate sometimes improve query performance? The addition of the "no-op" coalesce() predicate introduces an error that masks something else that is preventing optimal performance.

What some performance enhancement tools do is a brute-force test: the tool repeatedly introduces the predicate into different places in a query, operating on different columns, to try to find a case where, by introducing an error, it stumbles onto a better-performing plan. This is also true of a query developer hand-coding the "no-op" predicate into a query. Typically, the developer will have some insight on the data to guide the placement of the predicate.

Using this method to improve query performance is a short-term solution which does not address root cause. It hides potential areas for performance improvements and could have the following implications:

This workaround does not guarantee permanent performance improvements. The DB2 query compiler might eventually handle the predicate better, or it might be affected by other random factors.

Other queries might be affected by the same root cause. The performance of your system in general might be suffering as a result.

Answer

Try to identify and address root cause by determining why the original plan chosen by the query compiler did not perform optimally.

Although the actual estimates in a better-performing plan using "COALESCE(X, X) = X" cannot be trusted, such a plan can still be useful because its "shape" (for example, its join order and access methods) can be used as clues to determine where a problem might lie with the original plan.

Here are some examples of questions that can help to better identify the source of performance issues:

Are the statistics out-of-date?

Should more statistics be collected?

Are there statistical correlations that have not been detected?

Are there other characteristics of the data that are not yet captured or modeled?

Are there hardware problems? (e.g. a disk is misbehaving)

Are there configuration problems?

Are any queries stuck behind a lock?

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 :)

REXX to calculate holiday dates in US

What are all the government/bank holiday dates in the US in 2012? You can tell this by grabbing a calendar or doing a search on the net. What if you need this information in a program? What if you need this information for year 2013, 2014 etc.? Mr Martin Dunkel has written a REXX and he has graciously agreed to share his code with us all. The code is available in the link below. Please feel free to make a copy of it and change as needed. I've tested this code as it is. His contact info is available in the REXX, in case you have any questions that I cannot answer.

All credit goes to Mr Martin Dunkel !! Thank You Mr Dunkel.

Here is the code: https://docs.google.com/open?id=0B4whLShAZsgERW1vZmlVSDQ0WXc

DB2 for zOS Index on Expression

DB2 for z/OS version 9 brought in a new feature called "Index on Expression". Yes, you can create indexes on frequently used expressions in your queries. For example, if you often do a SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', then creating an index on UPPER(LAST_NAME) will be useful.

Until V8, you can create index on just columns, not on expressions/functions etc. So, you would have created an index on LAST_NAME, but DB2 would have ignored it because when you use a function on a column, DB2 ignores index access. But starting in V9, you can create an index on UPPER(LAST_NAME) and DB2 will use this index.

Now, to the tricky part ...

I tried to create an index on UPPER(LAST_NAME), DB2 complained that I need to specify 'locale' name. After reading a little bit about 'locale names', I specified UPPER(LAST_NAME, 'En_US') and DB2 happily created the index. I also ran runstats on the table. However, when I did an explain on the query SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', DB2 didn't seem to pick up the newly created index. That puzzled me.

After reading a little further, I found that I need to specify the same 'locale' in the query too. After specifying the 'locale', I did an explain and DB2 picked up the index. This is the modified query:

SELECT FIRST_NAME WHERE UPPER(LAST_NAME, 'EN_US') = 'SMITH'

Keywords: DB2, z/OS, Index, Expression, Upper, Locale, function