DB2 "FIRST_DAY" function

DB2 comes with a lot of in-built functions. One of them is called LAST_DAY which returns the Last Day of a month, but there is no FIRST_DAY of the month function. Some of my colleagues asked about it. Here is an UDF (User Defined Function) that'll return FIRST DAY of the month:

CREATE FUNCTION schema.FIRST_DAY (IN_DATE DATE)
RETURNS DATE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN DATE(LAST_DAY(IN_DATE - 1 MONTH) + 1 DAY);

SQL ERROR "00D31034"

You have a web application connecting to DB2 for z/OS and it times out with the message:

SQL30081N A communication error has been detected. 
Communication protocol being used: "TCP/IP". Communication
 API being used: "SOCKETS". Location where the error was
detected: ip-address", Communication function detecting
the error: "recv". Protocol specific error code(s):
 "*", "10054", "*". SQLSTATE=08001

There could be many reasons for this error. One that I found recently is here.

Look in your z/OS DB2 MSTR log for a message like this:

DSNL030I  +DB2x DSNLILNR DDF PROCESSING FAILURE FOR
LUWID=xxxxxxxx.GE21.000000000000
AUTHID=-N/A-, REASON=00D31034

As per the DB2 manual, "00D31034" means

The MAX REMOTE CONNECTED limit was reached and the
conversation was abnormally deallocated. The thread
was not allowed to connect to DB2.

Now, as the message says, there may be too many remote database connections and the number of remote connections exceeded the ZPARM CONDBAT(MAX REMOTE CONNECTED) limit.

But, before jumping to the conclusion that all the connections are genuine and asking your DBA to bump this number, look at your connection threads closely.

The web application might be using an old version of the arch.jar (arch-2_2_3.jar). This version of arch.jar does not close the database connection successfully on a few occasions.

The resolution is a simple upgrade to the latest arch.jar (arch-3_0_1.jar) which has the fix to close the connection.

DB2 SQLCODE -741

I was installing a DB2 vendor tool yesterday. Part of the install was to create a new temporary database. When I ran the following DDL,

CREATE DATABASE database
              STOGROUP ASTERISK
              BUFFERPOOL BP2
              INDEXBP BP2
              AS TEMP

I got this message:

DSNT408I SQLCODE = -741, ERROR:  A TEMP DATABASE IS ALREADY DEFINED FOR
       MEMBER ssid

The problem here is, I was trying to define a temporary database whereas a temporary database already exists for that subsystem. ONLY ONE TEMPORARY DATABASE is allowed per subsystem or data sharing member.

I learnt a lesson !!

BMC Unload PLUS - BMC50033E

We're moving away from CA/Platinum, yes, our shop is converting from CA/Platinum tools to BMC tools for DB2.

I ran my first UNLOAD PLUS job today and it went down with this message:

BMC50033E CPU NOT AUTHORIZED TO RUN UNLOAD PLUS FOR DB2 RETURN CODE = 0004 REASON CODE = P1

Found out that, in the JCL, I need to include the Password dataset - either in STEPLIB or JOBLIB:

// DD DISP=SHR,DSN=hlq.BMCPSWD

DB2 Subsystem Size

How do you find the size of your entire DB2 subsystem on z/OS?

Try this ...

List the base datasets in ISPF option 3.4, e.g., DB2CAT*.DSNDBD.**, and then do a SAVE LIST. It'll save all the Tracks Used info. Add them up to come up with the total tracks used. Remember, for compressed tablespaces, this information shows size for the compressed data. Uncompressed data might occupy more space.

If you want to know the relation between tracks and Kilo Bytes, use this formula (this example is for a 3390 DASD - your model may be different, check with your systems programmer):
1 Track = 48 KB
1 Cylinder = 720 KB
1 Cylinder = 15 Tracks

COBOL - INITIALIZE

In a COBOL INITIALIZE statement, elementary FILLER items are ignored. That means, INITIALIZE does not touch the values in the FILLER.

e.g.,

01 WS-VAR.
05 FILLER PIC X(5) VALUE 'junk1'.
05 WS-FILLER PIC X(5) VALUE 'junk2'.
05 FILLER PIC X(5) VALUE 'junk3'.

After the statement INITIALIZE WS-VAR is executed, FILLER contains 'junk' ,whereas WS-FILLER contains all spaces.

Effectively, WS-VAR changes from 'junk1junk2junk3' to 'junk1 junk3'.

DB2 "OPTIMIZE FOR 1 ROW"

Use this clause to avoid sorts - You can influence the access path most by using OPTIMIZE FOR 1 ROW.

OPTIMIZE FOR 1 ROW tells DB2 to select an access path that returns the first qualifying row quickly. This means that whenever possible, DB2 avoids any access path that
involves a sort.

In "OPTIMIZE FOR n ROWS", if you specify a value for n that is anything but 1, DB2 chooses an access path based on cost, and you won't necessarily avoid sorts.

SDSF POS Field

I used to sort the jobs by POS (position) field in SDSF. This puts all the jobs in ascending order by date and time except the jobs that are in input queue or that are currently executing - they always appear in the top.

However, we recently upgraded from z/OS 1.8 to z/OS 1.9 and I don't see it sorted this way any more and the POS field is not populated by any value either. So I asked my sys prog and she asked IBM.

IBM's answer is here:
"The difference your seeing is described by the HOLD data for APAR OA26013 now PE'd by OA27126. The difference in behavior on the panel has to do with a change in the way SDSF gets the jobs/data for the panel. Previously, SDSF read the data from spool which meant running thru the queues and calculating the POS field on its own. These POS values were calculated erroneously by SDSF, assigning a position value to jobs on all queues.

Starting in SDSF 1.9, we're using JES2 SSI calls to acquire the job information which returns the correct POS values assigned by JES2. The POS field on the SDSF ST panel only applies to jobs on the INPUT queue, that is, jobs in-and-awaiting execution, not to any other queues like OUTPUT, SPIN and HARDCOPY.

These changes were necessary to expand support for SDSF to include JES3 compatibility and is considered working as designed."

So, now I'm sorting my jobs by ST-DATE and ST-TIME which is not the same as sorting on POS but comes a little close to that. I miss my POS field :(

CICS EIBRESP=27

Last week I was coding a simple CICS program and in the first execution, I got this error message:

EIBRESP=27 and EIBRESP2=0

As per the CICS manual, EIBRESP=27 means "Length Error". Well I found it strange that it got a
length error. After analyzing further I found that, I forgot to define the program in CICS. I defined it in CICS and then ran the program again, it started working !!

Why wouldn't CICS give me a straight forward "I cannot find this program in CICS" instead saying "Length Error"?

Well, the problem is solved now, I'm not worried about it anymore.

DB2 SQLCODE -497

If you get an SQLCODE -497 while trying to run a QMF PROC, read this:

SQLCODE -497 means "THE MAXIMUM LIMIT OF INTERNAL IDENTIFIERS HAS BEEN
EXCEEDED FOR DATABASE database"

You need to run the MODIFY utility with RECOVERY option to fix this issue.

The MODIFY online utility with the RECOVERY option deletes records from the SYSIBM.SYSCOPY catalog table, related log records from the SYSIBM.SYSLGRNX directory table, and entries from the DBD. You can remove records that were written before a specific date or you can remove records of a specific age. You can delete records for an entire table space, partition, or data set.

Sample SYSIN for MODIFY:

MODIFY RECOVERY TABLESPACE database.tablespace DELETE AGE(365)