How to find if a COBOL program is looping, thru Mainview

If you want to know if a program is looping (on mainframe) and if you have Mainview for MVS, then
1. Go to JCPUR which shows realtime job CPU utilization
2. Place the cursor on the job you are interested in and hit enter
3. It'll take you to EZMJOBR window
4. Under SYSPROG Service, you'll find "MVScope CPU Tracing". Place your cursor there and hit enter
5. Then place your cursor on "Begin CPU Trace" and hit enter. It'll start CPU tracing.
6. Once it finishes, see if it shows any one particular statement. If so, it could mean, the program was in a loop during the trace period.
7. Get the Offset info, go to the compile listing and search for the Offset under HEXLOC (you may not find the exact offset, but the one just before this value is good enough). Get the line number and get the statement that was in loop.

Simple?

Copy Data from DB2 for z/OS to DB2 for LUW (UDB)

One of the applications developer in our shop wanted to know if he can copy data from a mainframe DB2 table to DB2 UDB on LUW. Though there are several ways of doing it, this is the one suggested him assuming the table structure in z/OS and LUW are the same.

1. Some of the tools that can be used to achieve this: Quest Central for DB2, Toad for DB2 or DB2CMD interface

2. Connect to the mainframe database (CONNECT TO ssid) and issue the EXPORT command "EXPORT TO d:\file_name.ixf OF IXF MESSAGES d:\msg_export.txt SELECT *FROM prefix.table_name"
Note: Don't forget to check the message file before moving onto the next step

3. Connect to the LUW database and issue the IMPORT command
"IMPORT FROM d:\file_name.ixf OF IXF MESSAGES d:\msg_import.txt INSERT INTO prefix.table_name"
Note: Don't forget to check the message file

It's that simple. But remember that this works well for relatively smaller tables. For larger tables, you may have to use the LOAD command with proper COMMIT COUNT.

SQL0805N on DB2 package SYSLH203

If you get a SQL0805N on package SYSLH203 then an application is holding a large number of statements open. If this is not an application bug you can bind more packages to allow for more statements open at the same time.

Depending on the type of statement you are executing, DB2 will use a particular package on the server. By default, DB2 creates three packages for each type of package. In this case NULLID.SYSLH2yy is reserved for statements with CURSORHOLD on and isolation level Cursor Stability. The package SYSLH203 means that DB2 is looking for the 4th package (200 is 1st, 201 is 2nd, etc) of this type, but it does not exist. You can create more packages on the server by connecting to the database and issuing the following bind command from the /sqllib/bnd directory:

db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 5

Note: CLIPKG 5 will create 5 large packages, and will give you the package that your application is looking for, as well as one more in this case. This setting only applies to large packages (containing 384 sections). The number of small packages (containing 64 sections) is 3 and cannot be changed.

To bind more CLI packages do the following:

• Find a machine that has the version and fixpack of DB2 that you want to bind. You can map to the machine you're going to bind on and then:
  - Go to the D:\SQLLIB\BND directory in the DB2 Command Line Processor (DB2CMD)
  - Look for the *.BND files in the D:\SQLLIB\BND directory
  - Connect to the host via DB2 connect statement like: db2 connect to dbalias user userid
  - X:\sqllib\bnd>db2 bind @db2cli.lst blocking all grant public sqlerror continue CLIPKG 30 to bind the max of 30 CLI packages. Range is 4 to 30.

DB2 CREATE INDEX abended with S04E 00E70005

There is a couple of news items attached to this post:
1. Today is the first anniversary of this blog
2. This is the 50th post

Well, now into the actual blog item ...

My colleague was trying to drop an existing UNIQUE & CLUSTER index and recreate it after adding 3 new columns at the end of the table, with one of the new columns added to the index. However, the DDL failed with S04E reason code 00E70005.

DB2 messages and codes manual had this info on this reason code: "A relational data system (RDS) subcomponent internal inconsistency was detected". It didn't make any sense. We suspected data issue first, so we REORGed the tablespace, and tried to create the index, but that didn't fix the problem. Next, we tried to repair the DBD using this JCL:

//*===============================================
//* Start the database in UT mode
//*===============================================
//STEP0010   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(UT)
  END
/*
//*===============================================
//* Run REPAIR utility on the DBD
//*===============================================
//STEP020 EXEC PGM=DSNUTILB,PARM='DB2x,utilid'
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  REPAIR DBD REBUILD DATABASE database
/*
//*===============================================
//* Start the database in RW mode
//*===============================================
//STEP0030   EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB    DD DSN=SYS2.DB2.DB2x.DSNLOAD,DISP=SHR
//SYSTSPRT   DD SYSOUT=*
//SYSPRINT   DD SYSOUT=*
//LISTING    DD SYSOUT=*
//SYSUDUMP   DD DUMMY
//SYSTSIN    DD *
  DSN SYSTEM(DB2x)
  -START DB(database) ACCESS(RW)
  END
/*
 

Even that didn't fix the problem. We were not very keen to open a ticket with IBM, since we wanted to fix the problem fast rather than debugging it. So as a last resort we dropped and recreated the tablespace (off course, we had saved the data). That fixed the problem. We don't know what caused it, but the problem is fixed !! I really hate when I don't know what caused the problem, and the fix is based trial-and-error method.

sqloopen -2079391743 sqlcode -970

I bumped into this error message (sqloopen -2079391743) when I tried to run "EXPORT" utility on a DB2 UDB table on my AIX box. No SQLCODE displayed on the screen. I couldn't find much information on the net, however, when I learnt about the DB2DIAG command, it made life easy. I issued the following command:

db2diag -rc -2079391743

and I got exactly what I was looking for:

Input ZRC string '-2079391743' parsed as 0x840F0001 (-2079391743).

ZRC value to map: 0x840F0001 (-2079391743)

V7 Equivalent ZRC value: 0xFFFFC601 (-14847)

ZRC class : Non-Critical Media Error (Class Index: 4)

Component: SQLO ; oper system services (Component Index: 15)

Reason Code: 1 (0x0001)

Identifer: SQLO_ACCD
Identifer (without component): SQLZ_RC_ACCD

Description: Access Denied

Associated information: Sqlcode -970
SQL0970N The system attempted to write to a read-only file.
Number of sqlca tokens : 0
Diaglog message number: 8701

I changed the datapath in my EXPORT command to a folder in which I had write access and the command ran fine.

"DB2LOOK" to extract DDL from DB2 for z/OS

Many of you may be aware that "db2look" command can be used to extract DDL from DB2 UDB for LUW database. But do you know that you can use this command to extract DDL from a DB2 database for z/OS? Here is how you do it.

1. Bring up a db2cmd prompt (press Start button on your PC, click Run, type db2cmd and hit enter)
2. Connect to your mainframe DB2 subsystem using the command "db2 connectto SSID user USERID". It'll prompt for the password. Enter the password and hit enter
3. Issue the following command:
db2look -d SSID -i USERID -w PWD -e -a -l -x -p -o FILENAME.TXT

The options specified extracts the following info:

-e ==> Extract DDL statements for database objects. DDL for the followingdatabase objects are extracted when using the -e option: Tables, Views, Automatic summary tables (AST), Aliases, Indexes, Triggers, Sequences, User-defined distinct types, Primary key, referential integrity, and check constraints, User-defined structured types, User-defined functions, User-defined methods, User-defined transforms, Wrappers, Servers, User mappings, Nicknames, Type mappings, Function templates, Function mappings, Index specifications, Stored procedures

-a ==> When this option is specified the output is not limited to the objects created under a particular creator ID. All objects created by all users are considered. For example, if this option is specified with the -e option, DDL statements are extracted for all objects in the database. If this option is specified with the -m option, UPDATE statistics statements are extracted for all user created tables and indexes in the database.
Note: If neither -u nor -a is specified, the environment variable USER isused.

-l ==> If this option is specified, then the db2look utility will generate DDL for user defined table spaces, database partition groups and bufferpools.

-x ==> If this option is specified, the db2look utility will generate authorization DDL (GRANT statement, for example).

More info is available in the DB2 V8 LUW Command Reference: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsptopic=/com.ibm.db2.udb.doc/core/r0002051.htm

How to find the number of times a CICS program was used in a day?

CICS has a very handy tool called "Statistics Utility Program" - DFHSTUP. If you want to know how many times a program was used in a particular day, run this program at the end of the day.

It prepares and prints reports offline, using the CICS statistics data recorded on the MVS system management facilities (SMF) SYS1.MANx data sets. To enable the CICS statistics domain to record interval statistics on these SMF data sets, you must specify the STATRCD=ON system initialization parameter (SIT). The other statistics record types (unsolicited, requested and end-of-day) are written regardless of the setting of the STATRCD option.

Note: Use the version of the DFHSTUP program from the same release of CICS as the data that it is to process.

Sample Job from CICS manual:

The job shown comprises of two job steps. The job steps are:

1. Unload the SMF data set (or data sets) containing the CICS statistics that you want to process
2. Run DFHSTUP to sort, format, and print the statistics data. You run the DFHSTUP program in a batch region to process any CICS SMF type 110 statistics records that are present in an unloaded SMF data set, which you can write to either a temporary or a cataloged data set.

//**********************************************************************
//*  Step 1: Unload data from the SMF data sets
//**********************************************************************
//SMFDUMP  EXEC PGM=IFASMFDP
//INDD1     DD DSN=SYS1.MANx,DISP=SHR,AMP=('BUFSP=65536')
//INDD2     DD DSN=SYS1.MANy,DISP=SHR
//OUTDD1    DD DSN=user.SMF.DATA,DISP=(NEW,CATLG),
//             SPACE=(CYL,(50,10)),UNIT=SYSDA
//SYSPRINT  DD SYSOUT=A
//SYSIN     DD *
       INDD(INDD1,OPTIONS(DUMP))
       INDD(INDD2,OPTIONS(DUMP))
       OUTDD(OUTDD1,TYPE(0:255))
/*
//**********************************************************************
//*  Step 2: Sort, format and print the statistics records
//**********************************************************************
//STUP1    EXEC PGM=DFHSTUP,REGION=0M
//********************************************
//STEPLIB  DD DSN=CICSTS31.CICS.SDFHLOAD,DISP=SHR
//         DD DSN=CICSTS31.CICS.SDFHAUTH,DISP=SHR
//DFHSTATS  DD DSN=user.SMF.DATA,DISP=SHR
//DFHSTWRK  DD UNIT=SYSDA,SPACE=(CYL,(8,4))
//SORTWK01  DD UNIT=SYSDA,SPACE=(CYL,(4))
//SORTWK02  DD UNIT=SYSDA,SPACE=(CYL,(4))
//SORTWK03  DD UNIT=SYSDA,SPACE=(CYL,(4))
//SORTWK04  DD UNIT=SYSDA,SPACE=(CYL,(4))
//DFHPRINT  DD SYSOUT=*
//SYSPRINT  DD SYSOUT=*
//SYSUDUMP  DD SYSOUT=*
//SYSABEND  DD SYSOUT=*
//SYSOUT    DD SYSOUT=*
//SYSIN     DD *
SELECT APPLID=(applid1,applid2)
COLLECTION TYPE=ALL
/*

For more information, please refer to DFHSTUP in "CICS Transaction Server for z/OS Information Center"

How to turn on Aux trace in CICS

The CICS INTERNAL trace stays on always. But it doesn't get externalized, so the trace data may not be available when you need it. When trace data is needed, you can turn on Aux Trace by changing the Auxiliary Trace Status to "Stopped" to "Started" using CETR transaction. The Aux Trace data set has initial value of A which is defined as DFHAUXT in the CICS started task. When A gets filled, it automatically switches to B which is defined as DFHBUXT in the started task. When B gets filled the status changes to STOPPED. You can do a STANDARD trace or a SPECIAL trace (with only the components that you want to trace - Standard usually captures lot more data than required). To set up values for special tracing, press PF4 and set the values under "special" as needed. The component abbreviations are defined in the help screen (press PF1). Once you make the required changes in this screen, press PF5 to define the transaction for which you want Aux trace turned on.

CETR                CICS Trace Control Facility                   
CICTTOR
Type in your choices.
Item                              Choice       Possible choices
Internal Trace Status       ===>  STARTED      STArted, STOpped
Internal Trace Table Size   ===>  400     K    16K - 1048576K
Auxiliary Trace Status      ===>  STOPPED      STArted, STOpped, Paused
Auxiliary Trace Dataset     ===>  A            A, B
Auxiliary Switch Status     ===>  NO           NO, NExt, All
GTF Trace Status            ===>  STOPPED      STArted, STOpped
Master System Trace Flag    ===>  OFF          ON, OFf
Master User Trace Flag      ===>  OFF          ON, OFf
When finished, press ENTER.
PF1=Help    3=Quit    4=Components    5=Ter/Trn    6=JVM    9=Error List

From the main CETR screen, press PF4 to get to the Component Trace Options screen where you can set the options for Standard and/or Special trace options.

CETR                    Component Trace Options                   
CICxxxx
Over-type where required and press ENTER.                         PAGE 1 OF 4
Component Standard                           Special
--------  --------------------------------------------------------------
  AP      1                                  1-2
  BA      1                                  1-2
  BM      1                                  1
  BR      1                                  1-2
  CP      1                                  1-2
  DC      1                                  1
  DD      1                                  1-2
  DH      1                                  1-2
  DM      1                                  1-2
  DP      1                                  1-2
  DS      1                                  1-2
  DU      1                                  1-2
  EI      1                                  1-2
  EJ      1                                  1-2
  EM      1                                  1-2
  FC      1                                  1-2
  GC      1                                  1-2
PF:  1=Help   3=Quit   7=Back  8=Forward  9=Messages   ENTER=Change

From the main CETR screen, press PF1 to get to this Trace Help screen where you can see the meanings of component abbreviations.

CETR             Help:  CICS Component Trace

(3) MEANINGS OF COMPONENT ABBREVIATIONS.

AP . . . Application domain            IE . . . ECI over TCP/IP domain
BA . . . Business Application Manager  II . . . IIOP domain
BM . . . Basic Mapping Support         IS . . . ISC
BR . . . Bridge                        KC . . . Task Control
CP . . . CPI-C interface               KE . . . Kernel
DC . . . Dump compatibility layer      LC . . . Local Catalog domain
DD . . . Directory manager             LD . . . Loader domain
DH . . . Document Handler domain       LG . . . Log Manager domain
DM . . . Domain Manager domain         LM . . . Lock Manager domain
DP . . . Debugging Profiles domain     ME . . . Message domain
DS . . . Dispatcher domain             MN . . . Monitoring domain
DU . . . Dump domain                   NQ . . . Enqueue Manager
EI . . . Exec interface                OT . . . Object Transaction domain
EJ . . . Enterprise Java domain        PA . . . Parameter Manager
EM . . . Event Manager domain          PC . . . Program control
FC . . . File control                  PG . . . Program Manager domain
GC . . . Global Catalog domain         PI . . . Pipeline Manager domain
IC . . . Interval control              PT . . . Partner Management

PF 3=End  7=Back  8=Next  ENTER=End

From the main CETR screen, press PF9 to get to this "Transaction and Terminal Trace" screen where you can set the transaction name and/or terminal name that needs to be traced.

CETR                    Transaction and Terminal Trace            xxx
CICxxxx
Type in your choices.
Item                              Choice       Possible choices
Transaction ID              ===>               Any valid 4 character ID
Transaction Status          ===>               STandard, SPecial, SUppressed
Terminal ID                 ===>               Any valid Terminal ID
Netname                     ===>               Any valid Netname
Terminal Status             ===>               STandard, SPecial
Terminal VTAM Exit Trace    ===>               ON, OFf
Terminal ZCP Trace          ===>               ON, OFf
VTAM Exit override          ===>  NONE         All, System, None
When finished, press ENTER.
PF1=Help      3=Quit      6=Cancel Exits      9=Error List

To print the trace data use the following job or use IPCS to analyze the trace data.

//PRINT    EXEC PGM=DFHTU640   <-- Program namechanges depending on the CICS version
//STEPLIB  DD   DSN=xxx.SDFHLOAD,DISP=SHR
//DFHAUXT  DD   DSN=xxx.DFHBUXT,DISP=SHR
//DFHAXPRT DD   SYSOUT=*
//DFHAXPRM DD   *
ABBREV
/*
//*TASKID=(74082)
//*TYPETR=(APE160,AP3180)

SYSIBM.SQT* tables and SQLCODE -526

This is going to be a small post ...

If you plan to use the IBM Data Server Driver for JDBC and SQLJ or the DB2 ODBC driver, you must create the SYSIBM.SQT* Tables & SYSIBM.SQL* Stored Procs that provide support for those drivers.

GRANT ALL access to PUBLIC to these tables. Trying to GRANT individual access like GRANT SELECT won't work (you'll get SQLCODE -526).

Trace Master - CICS abend AEYB

My colleague was trying to debug a CICS program in TraceMaster (a Macro4 Prodcut). He got this message:

DFHAC2206 15:45:50 CICTTOR Transaction xxxx failed with abend AZI6. Updates to local recoverable resources backed out. DFHAC2261 System OPR sentmessage (sese code 0824089E). 'DFHAC2206 15:45:50 CICTOPR Transaction xxxx failed with abend AEYB. Updates to local recoverable resources backedout.'

CICS manual describes the condition for code "AEYB" as "INVMPSZ". This "occurs if the specified map is too wide or too long for the terminal." So I had him change the Screen Size on Trace Master profile from "2" for 24x80 to "3"for 32x80 and SAVEd the profile (this is important, Trace Master doesn'tsave the profile automatically).

That fixed the problem.

However, he started getting a "X PROG" after starting the debug session. We looked at the program source code and found what the problem was. Apparently TraceMaster doesn't like FREEKB option. It failed while executing this statement:

EXEC CICS SEND
FREEKB
CONTROL
CURSOR (EIBCPOSN)
END-EXEC.

We don't understand why the program is doing this to free the keyboard because the SEND of the map has the FREEKB option on it. This problem got resolved when we bypassed this statement while debugging in TraceMaster. This extra 'SEND CONTROL' command seems to be confusing it. To bypass this command, we set a breakpoint at this statement and then did a GOTO (PF13) to the exit to skip the SEND to get it to work.

Mainview Tips

Here are some tips if you are an user of Mainview for z/OS

• VIEWS command gives you a list of all the VIEWs installed/available
• Use CON xyza, where xyza is the LPAR name, command to connect to the xyza LPAR
• Use MSG msgnumber command to get an explanation on the Error Messages displayed by Mainview. e.g., msg BBMXC739
• Use CUST command to customize a VIEW
• Use ASU command to update all windows automatically at a particular frequency (similar to the "&n" command in SDSF)
• SYSSUM view shows how busy the LPAR has been in the prev intervals
• To bring up multiple windows, use HS (Horizontal Split) or VS (VerticalSplit) to split the screen as required
• Use CLO n, where n is the window number, to close a window
• Use Wn;MAX to maximize window number 'n'
• Use Wn;REST to restore the window to it's original size
• Type SCREENS to see all the user defined screens
• Use SCR screenname to jump to a user defined screen
• To create your own screen, format the window as desired (multiple windows connecting to different systems etc.) and then type SAVESCR screenname
• Summary VIEWs end with letter Z. e.g., JCPUZ
• Long Term History data VIEWs end with letter L. e.g., JCPUL
• If you are in a view like JCPUZ, you can scroll back and forth in time using the command TIME. Type TIME after getting into JCPUZ. Type 'TIME = = NEXT' to go to the NEXT interval and type 'TIME = = PREV' to go to the PREVious interval without changing the interval data and time. Also, you can assign them to PF keys.

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

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)