SQLCODE -904 with Reason Code "00D70024"

When you try to access or drop a table, you may get this error if the underlying VSAM data set for the table does not exist. If you are dropping the table, try dropping the tablespace instead (if there is just one table in the tablespace). If you are trying to browse the data, you are out of luck. Try recreating the table from another subsystem and load the data from there. Or, do a LOAD REPLACE with DUMMY SYSREC (it will create the underlying datasets). This will allow you access the table, but the table will be empty.

CA Fast Load Vs BMC Load Plus

Well, I've been compiling this for a while now. Here is a list of CA/Platinum's Fast Load utility keywords and the equivalent BMC's Load Plus keywords.


If you have any questions, or something doesn't make sense, or if you are looking for something else, please let me know (comments/email/chat).


I'm sorry, I couldn't find a way to insert a Word Document (with a table) into this blog, so I pasted the table here as an image.


Thanks !!

NULL VALUES and NULL INDICATORS in DB2

I was looking for storage space required for a column defined as WITH NULL. Got the answer here:

http://mainframe-faqs.blogspot.com/2008/02/null-values-and-null-indicators-in-db2.html

Excellent information here. Just wanted to share it with my readers here.

Thanks Arunam !!

Disclaimer:
THIS IS NOT MY OWN BLOG ENTRY. I'm just linking it to Arunam's blog entry.

DB2 crash with S04F (DSNJ113E)

This happened a couple of days ago. One of our CICS transactions inserted more than 18 million rows (probably loop) in a DB2 table before someone noticed and cancelled it. DB2 started rolling back the data but was slow, so the DBAs tried to issue the CANCEL THREAD command with NOBACKOUT option (this was in a TEST region) but that didn't work. So they just let it roll back. When there was less than 2 million rows to be rolled back DB2 crashed with the following message (S04F):

DSNJ113E +DB2T DSNJR003 RBA 'C35EF8BC2000' NOT IN
ANY ACTIVE OR ARCHIVE LOG DATA SET. CONNECTION-ID=DB2T,
CORRELATION-ID=003.RCRSC 02, MEMBER-ID=0

The operators restarted DB2, but it went down again after some time. DB2 manual suggested that the log record might be missing from the active/archive log. DBAs ran DSNJU004 utility and found that this RBA was available in a archive log data set which was not in the BSDS. DB2 manual suggested to add the dataset to the BSDS using DSNJU003 utility. But before doing that, the DBAs stopped DB2 and ran DSNJU003 using the parameters

CRESTART CREATE,FORWARD=YES,BACKOUT=NO

to reset checkpoint. Then they stopped DB2 with MODE(FORCE) since it didn't stop after issuing a regular stop DB2 command. But that failed. So they issued the following command to stop IRLM:

F DB2TIRLM,ABEND,NODUMP

After stopping DB2, they ran the DSNJU003 utility again and then started DB2.

Most of these are possible because it was a TEST system. Not sure what would we have done if this had happened in a PROD system.

JCL - REGION Parameter

Coding REGION parameter on a JOB step is different from coding it on a EXEC step.

A JOB statement REGION parameter applies to all steps of the job and overrides any EXEC statement REGION parameters.

When no REGION parameter is on the JOB statement, the system uses an EXEC statement REGION parameter, but only during the job step. Code EXEC statement REGION parameters when you want to specify a different region size for each job step.

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