Aji's Techie Tips

DB2 for zOS Index on Expression

noreply@blogger.com (Aji) — Sat, 21 Jan 2012 20:38:00 +0000

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

How to find the RSU or PUT level maintenance through SMP/E?

noreply@blogger.com (Aji) — Wed, 19 Oct 2011 18:21:00 +0000

Usually, if you are working on a mainframe issue (DB2 or CICS or MQ or any mainframe stuff), and if you are dealing with a 3rd party vendor, they would ask the maintenance level or patch level or PUT level or RSU level of your system or product. How do you find the RSU/PUT level?

If you install your products through SMP/E, this is how you find this information.
Go to SMP/E panel, and enter SMCSI dataset (global zone data set) and select option 3 and hit enter.

Select option 3 from this screen and hit enter.

Just hit enter again (no input for ZONE NAME).

Now select the region you are interested in. GLOBAL zone will show all the RSU that has been RECEIVED (it may or may not be APPLYed already). TARGET zone will show all the RSU that has been APPLYed.

e.g., this screen shows the last maintenance applied (RSU1103), along with the previous maintenance:

DB2 V9 for z/OS - New ZPARM EDM_SKELETON_POOL

noreply@blogger.com (Aji) — Sun, 28 Aug 2011 00:14:00 +0000

I'm working on upgrading DB2 for zOS from V8 to V9. I completed upgrading it to V9 CM (Compatibility/Conversion Mode) in one of our PROD subsystems about 2 weeks ago and the next business day was horrible. The performance was bad for the entire subsystem. The LPAR CPU peaked to 98% of the total MIPS capacity.

The activity rate against the SPT01 tablespace jumped several times and the CICS regions started sucking 20% more CPU. We noticed a few bad queries through Omegamon and we fixed them by either tweaking the stats or putting some new indexes. But that didn't bring down the CPU consumption much. The interesting part was, our zIIP engine started to peak at 100%, which usually stays at 40%. We found one query that had done a bunch of updates and got stuck - it was using a lot of CPU but was not doing any productive work. We cancelled this thread, but it wouldn't go away and the zIIP engine was still at 100%. Many more transactions/threads started to back log because of lack of CPU availability. The cancelled thread didn't die and still was burning CPU. We ended up cycling DB2 with a MODE(FORCE) command. Still, when DB2 came back, it started recovery of the cancelled thread, so we had to force kill it.

When that thread was at last gone, we looked at the CPU rate, and it was right back at the 98% with the only exception that zIIP processor was at 75% level.

We opened a Sev 1 PMR with IBM. As usual, they asked for dumps, SMF, RMF records etc. [Rant On] I would have thought IBM provided a solution to try for a problem with such a mature product. But instead they were waiting on us to provide docs. [Rant Off]

Anyway, we did some more research and found that, in DB2 V9, copies of packages and plans have been moved from below the 2GB bar to above the bar. This is done through a new zparm called EDM_SKELETON_POOL. The default value for this zparm is too low. So we increased it by 20 times. The system is in a much better shape now.

This is from Omegamon for DB2:
Package Table (PT) Reqs 1005483K
PT Loads 80073250
% of PT Loads from DASD 7.96%

The "% of PT Loads from DASD" should be less than 10% for a healthy system. It was about 55% in our system before increasing the value of EDM_SKELETON_POOL. With any other virtual memory, make sure that there is enough physical memory to support this increase.

After this fix, the CPU looks much better but we are still seeing some runaway transactions or changed accesspath (which are not good in many cases). We're fixing them by rewriting the queries in most cases.

Accessing DB2 LUW table through a COBOL program on mainframe

noreply@blogger.com (Aji) — Sun, 17 Apr 2011 03:52:00 +0000

If you are planning to write a COBOL program to access data from a table on DB2 LUW, you need to do the right setup first. I've described them here as much as I remember. If you get into any trouble, let me know, we can try to resolve it together.

Things to do on Mainframe DB2

Insert row in SYSIBM.LOCATIONS with these values

LOCATION - dbname <-- This is the DB2 LUW database name

LINKNAME - linkname <-- This is parent of a row in SYSIBM.IPNAMES or SYSIBM.LUNAMES (For this to work it needs to be the name of the DB on the UDB box, because this is how it determines which DB to connect to if that DB2 instance has more than one DB under it.)

IBMREQD - N

PORT - portnum <-- This is the connection port on the UDB box. To find this on AIX do: "cat /etc/services". The end of the file will have the connection port for the DB2 instance. On NT it will be 50000.

TPN - Leave this empty

Insert row in SYSIBM.IPNAMES

LINKNAME - linkname This ties to a row in SYSIBM.LOCATIONS with column LINKNAME set to same

SECURITY_OUT - A A means Already verified

USERNAMES -

IBMREQD - N

IPADDR - nn.nn.nn.nn This is the IP address. You can find this by doing a ping command from a TSO command line or from a NT command prompt. (e.g. PING EXPLORER)

Then the DDF address space must be stopped and restarted to pick up the changes made to the SYSIBM.IPNAMES, SYSIBM.LUNAMES.

Things to do on DB2 LUW

You need to update the DataBaseManager configuration which is at the DB2 Instance level. You can display the current settings with this command from a command line on NT or AIX

DB2 GET DBM CFG | more

Part of the output will look like this:

Database manager authentication (AUTHENTICATION) = SERVER

Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

There are 2 parms that must be changed from the default to allow connecting from the host--AUTHENTICATION and TRUST_ALLCLNTS. Use the following commands to change these. The changes don't take effect until the db2 instance is stopped and started, so you may need to coordinate cycling the instance with applications and the server owner(s).

UPDATE DBM CFG USING AUTHENTICATION CLIENT
UPDATE DBM CFG USING TRUST_ALLCLNTS DRDAONLY

To cycle the DB2 instance use DB2STOP After it stops do a DB2START. Its a good idea to do a DB2 LIST APPLICATIONS beforehand to see if there are any threads. You can use a FORCE command if you need to to kill any threads.

On top of the above things, if you are writing a COBOL program on host and trying to connect to DB2 LUW, these steps must also be followed.

1. BIND PACKAGE (luwdbname.collectionid) OWNER(CHGTMANP) ISOLATION(CS) QUALIFIER(ALIUPDP) MEMBER(programname) DYNAMICRULES(RUN) VALIDATE(BIND) SQLERROR(CONTINUE) ACTION(REPLACE)

2. Make sure the above BIND statement doesn't fail on anything other than -204 (especially look for -551s, because this could lead to -525 at run time)

3. Grant DBADM authority to the userid that is specified in the OWNER parameter of your BIND card

4. Add the new "dabase.collection.*" to PKLIST on the plan that you will use to run your cobol program
e.g.,

BIND PLAN(plan name) OWNER(owner) QUALIFIER(qualifier)+

     NODEFER(PREPARE) VALIDATE(BIND) ISOLATION(CS)+
     CACHESIZE(1024) CURRENTDATA(NO) DEGREE(1)+
     SQLRULES(DB2) ACQUIRE(USE) RELEASE(COMMIT)+
     EXPLAIN(NO) REOPT(NONE) KEEPDYNAMIC(NO)+
     IMMEDWRITE(NO) DBPROTOCOL(DRDA) ENCODING(37)+
     DISCONNECT(EXPLICIT)+
     PKLIST(collction1.*,+
            collection2.*,+
            database.collection3.*)+
     ENABLE(*)+
     ACTION(REPLACE) RETAIN

5. In the COBOL program, make sure the programmers are coding these in the right order:

SET CURRENT PACKAGESET = 'collectionname'
CONNECT TO :database-name

6. Give EXECUTE access to PUBLIC on the package on LUW for the first time it is bound. This should be done for each new package you create

DB2 UDB Partitioned Database - Unique Index

noreply@blogger.com (Aji) — Sat, 26 Feb 2011 16:42:00 +0000

In the absence of the DBA that usually works on DB2 UDB, I tried to create an unique index on a table that is defined on a partitioned database. I realized that it's not very straight forward to do so.

From DB2 manual: "There are performance advantages to creating a table across several database partitions in a partitioned database environment. Creating a table that will be a part of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the distribution key. A distribution key is a key that is part of the definition of a table. It determines the database partition on which each row of data is stored.

You must be careful to select an appropriate distribution key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)."

This is where I got totally lost "any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)". I was trying create the unique index on a column which was not the hash key and I was getting an SQL0270N Reason Code 1. After including the hash key along with the other key, it worked.

DB2 4K space filled up

noreply@blogger.com (Aji) — Wed, 12 Jan 2011 20:52:00 +0000

It was a Friday night and I got a call around 2 AM from Operations people saying, a lot of applications are spewing some error message in DB2 Master address space. I saw these messages in DB**MSTR, which started about 30 minutes ago and still continuing.

I looked up both the reason codes 00C900A5 and 00C90084. The DB2 messages and codes guide said "Explanation: The temporary file with the page size shown in NAME is not available. An error was detected either during the opening of the page set or during the formatting of the header and the space map pages of the pageset.

If the reason code appears in a DSNT500I message issued during the -START DATABASE command, the request fails. In either case, the temporary file function does not allocate any temporary file on the page set."

I checked the 4K datasets DB2CAT.DSNDBC.DSNDB07.DSN4Kxx.I0001.A001. We've 6 of them. I checked the extents on these, and they had spanned for only one extent. So the problem must be something else. I decided to stop and start the 4K tablespaces and issued a STOP against these 6 tablespaces. However, they were in STOPP (Stop Pending) status for a long time. That's when I looked in Omegamon for DB2 and found that there were 4 threads that we running for a long time and had consumed millions of getpages, they were sorting large amounts of data, which means they were using up most of the 4K space. I killed these 4 threads and voila, all the 6 tablespaces stopped. I then issued the START command and started them. They came up without any hiccup and all the error messages stopped.

SQLCODE -331

noreply@blogger.com (Aji) — Fri, 01 Oct 2010 16:43:00 +0000

Recently I altered a tablespace CCSID from EBCDIC to UNICODE. This tablespace had a PLAN_TABLE in it. After altering the tablespace, I couldn't retrieve certain rows from this PLAN_TABLE. I started getting SQLCODE -331. The message appeared like this:

"CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION43 CANNNOT BE CONVERTED FROM 367 TO 37, REASON 12"

Position 43 refers to column IBM_REQUIRED_DATA in the PLAN_TABLE. As a short term solution, I spaced out the value in this column and it fixed it. However, it kept appearing for different programs/queries. So, I altered the table column attribute for IBM_REQUIRED_DATA from "FOR SBCS DATA" to "FOR BIT DATA". It fixed the problem.

SMP/E error "NO APPLICABLE ++VER"

noreply@blogger.com (Aji) — Wed, 08 Sep 2010 01:02:00 +0000

I was trying to apply a PTF BPEnnnn on a BMC product called "Snapshot Upgrade Feature". The SMP/E RECEIVE job failed with the message "NO APPLICABLE ++VER". It means that I do not have the correct version of the 'Base Product' on which I was trying to apply the PTF. I checked the technical bulletin that BMC sent me, it mentioned that I should be on Version 5.6 of the product to apply this product. I browsed my SMPPTFIN dataset and searched for the PTF BPEnnnn and found the FMID. It was one version prior to the one mentioned on the technical bulletin. It was clearly my mistake that I didn't read the technical bulletin clearly before doing a SMP/E RECEIVE, but I learnt something new.

SQLCODE -518

noreply@blogger.com (Aji) — Sat, 03 Jul 2010 21:44:00 +0000

The reason mentioned in the DB2 manual for SQLCODE -518 is "THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"

You may get this SQLCODE for many reasons. I don't know them all. For me this is what happened. When I tried to connect to a remote DB2 database (on LUW) from a COBOL program on the mainframe, the CONNECT was successful. However when I tried to run an SQL on that database I got this SQLCODE.

After researching for a while, I found that I had an existing SQL statement within EXEC SQL and END-EXEC. That worked good. But when I commented out one of WHERE clause statements (using * at column 7), it gave me this error. I moved the commented statement to the line after END-EXEC and it started working again.

Bottom line is DB2 doesn't like COBOL-like commented code inside EXEC SQL and END-EXEC when you are trying to run the SQL at a remote DB2 database.

SQLCODE -804

noreply@blogger.com (Aji) — Tue, 20 Apr 2010 22:45:00 +0000

SQLCODE -804 means something messed up with the SQLDA. Since SQLDA is internally created by DB2 for static COBOL programs, it is possible for a COBOL subscript to overwrite SQLDA statements which are inserted by DB2 at the end of the Working Storage section.

If your programmer complains about this sqlcode, have him/her increase the table size (increase the 'TIMES' parameter value) and recompile the program.

DB2_ALL to run DB2 command on all partitions

noreply@blogger.com (Aji) — Fri, 16 Apr 2010 16:23:00 +0000

DB2 LUW is all Greek and French to me. Today I was trying to rename a column name. I issued the DROP command, but I got the message "SQL0290N Table space access is not allowed." I checked the tablespace status, it was in 'backup pending' state. So I issued the BACKUP database tablespace command and the tablespace state showed normal. However, when I reissued the DROP command, I got the same SQL0290N.

After some research, I found that on a partitioned database, issuing DB2 command from DB2 command line DOES NOT work on ALL partitions. I had to use DB2_ALL utility. However, DB2_ALL doesn't work from DB2 command line.

So I invoked DB2 Task Center, created a new task and put in the following in the Command Script.

DB2_ALL "DB2 BACKUP DATABASE database TABLESPACE tablespace ONLINE TOD:\DB2\BACKUP\database COMPRESS"

Then saved this task, right clicked on it and clicked on Run Now. After it ran, checked the "Show Results" and confirmed that the backup was succesful. After that I reran the DROP and CREATE table command ssuccesfully.

Phew !!

JCL to find if a dataset is empty

noreply@blogger.com (Aji) — Fri, 05 Mar 2010 19:33:00 +0000

In your batch job, if there is a need to check for empty dataset, you can do it using IDCAMS.


//*-------------------------------------------------------------------*/
//* This JCL is to find if a file is empty or not. If there are no    */
//* records, the return-code will be 4.                               */
//*-------------------------------------------------------------------*/
//STEP01   EXEC PGM=IDCAMS
//SYSPRINT  DD SYSOUT=*
//IN1       DD DISP=SHR,DSN=your.dataset.name
//SYSIN     DD *
  PRINT INFILE(IN1) COUNT(001)
/*

If you have ICETOOL at your site, you can use that as well.


//*-------------------------------------------------------------------*/
//* This JCL is to find if a file is empty or not using ICETOOL.      */
//* If there are no records, the return-code will be 12               */
//*-------------------------------------------------------------------*/
//STEP01  EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//IN       DD DISP=SHR,DSN=your.dataset.name
//TOOLIN   DD *
COUNT FROM(IN) EMPTY
/*

Gmail Tips

noreply@blogger.com (Aji) — Wed, 10 Feb 2010 01:28:00 +0000

I know, this post has nothing to do with mainframes, but I just thought I would pass this around in case anyone finds it useful.

I've started using my gmail id very frequently because of all the good features it offers. However there are a few things that is missing from gmail. One of them is, you cannot sort the emails by Name, Subject, etc. But I found a way to do something to achieve similar results. Being a search giant, google seems to handle emails like another search item.

To sort on label (folder) "DB2", type label:DB2 in the search field and click on Search Mail

To sort on label (folder) "DB2" and Subject "DDL Lookup", type label:DB2 subject:DDL Lookup in the search field and click Search Mail

To get all emails from "Aji", type From:Aji in the search field and click Search Mail

UNIQUE WHERE NOT NULL - Explained

noreply@blogger.com (Aji) — Sun, 07 Feb 2010 01:49:00 +0000

I always had confusion over the meaning of indexes created with UNIQUE WHERE NOT NULL clause on DB2 for z/OS. The manuals don't seem to do a good job explaining it. I understand it now and here is how it works.

It means its ok to have multiple rows with null values in columns included in the index, but its not ok to have rows where the combination of the columns included in the index is not unique and all the columns are not null. I tested this on a three column index. I was able to insert rows rows shown below just fine, the only duplicate exception I got was where column a, b, c were the same value for two different rows and none of the 3 columns contained a null value.


  COL1  COL2  COL3
  ----  ----  ----
   A     B     C
   A     B     -
   A     B     -
   A     -     -
   A     -     -
   -     -     -
   -     -     -

CREATE UNIQUE WHERE NOT NULL INDEX PRE.IDX1
       ON PRE.TEST
       (COL1   ASC
       ,COL2   ASC
       ,COL3   ASC)
       USING STOGROUP ASTERISK
             PRIQTY 12
             SECQTY 10800
             ERASE NO
       FREEPAGE 0
       PCTFREE 0
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 2097152  K;

CREATE TABLE PRE.TEST
       (COL1     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL2     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL3     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       )
       IN DB.TS;

SMP/E Errors

noreply@blogger.com (Aji) — Fri, 22 Jan 2010 16:36:00 +0000

Well, I've been asked to install Omegamon XE for DB2 PM in our shop. I downloaded the required components from the IBM Shop z website and did a SMP/E RECEIVE. When I started the SMP/E APPLY (with CHECK option), I started getting all kinds of errors. I'm new to SMP/E, so I'll try to document what I find from my experience. It might be useful for those novices who have starting doing SMP/E installs.

These are the steps I follow when I install a product or apply maintenance(PTF) on an existing product:

1. If you are applying maintenance on an existing product, use ADRDSSU to backup ALL the datasets related to the product

2. Download the product CBDBO/PTF from IBM Shop z series website to your PC

3. FTP the product/PTF to mainframe

4. If the PTF is in tersed (IBM way of compressing) format, UNTERSE using TRSMAIN program with UNPACK option - if you need a sample job to run TRSMAIN, please put your request in the comment section

5. For new product installs, allocate SMP/E global zone and target/distribution datasets and all other related SMP/E datasets like SMPLOG, SMPPTS, SMPLTS, SMPMTS etc. Usually when you download the product,you'll get a sample job that allocates these datasets and defines them to the appropriate DD names. Go through the Program Directory to find info on the sample job. You may have to talk to your storage administrator about which volume to use for the new datasets.

6. Run SMP/E APPLY job with CHECK option (CHECK option ensures that you do not update any datasets. In our shop we usually install new product in the "base" libraries and all PTFs in the "maintenance" libraries. After installing thePTFs in the maintenance libraries, if everything goes well, we copy them to the base libraries. With the CHECK option you can see which datasets will be updated and if you do see any issue with updating a particular dataset, you can change the DDDEF - DD definition - in SMP/E and resubmit the job with CHECK option)

7. Run SMP/E APPLY job with CHECK option commented.

8. After a satisfactory testing, run SMP/E ACCEPT with CHECK option (this is in most cases not necessary unless you are going to back out any changes - in my little experience, if I've to backout a PTF, I just RESTORE all the datasets from the ADRSSU backup taken before applying the PTF)

Thanks for stopping by.

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

noreply@blogger.com (Aji) — Thu, 10 Dec 2009 01:34:00 +0000

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)

noreply@blogger.com (Aji) — Mon, 26 Oct 2009 20:35:00 +0000

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

noreply@blogger.com (Aji) — Tue, 22 Sep 2009 21:18:00 +0000

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

noreply@blogger.com (Aji) — Tue, 15 Sep 2009 16:54:00 +0000

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

noreply@blogger.com (Aji) — Mon, 31 Aug 2009 17:46:00 +0000

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

noreply@blogger.com (Aji) — Fri, 14 Aug 2009 02:47:00 +0000

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?

noreply@blogger.com (Aji) — Fri, 31 Jul 2009 21:14:00 +0000

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

noreply@blogger.com (Aji) — Sat, 13 Jun 2009 14:57:00 +0000

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

noreply@blogger.com (Aji) — Sun, 24 May 2009 23:14:00 +0000

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

noreply@blogger.com (Aji) — Mon, 11 May 2009 23:58:00 +0000

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.