Resolving error 37 of the Vsam file

Resolving error 37 of the Vsam alternate index

Whenever we define Vsam Alternate Index File we might face error 37 when we use to process the file via JCL first time:

The solution mentioned to resolve this is to initialize Vsam file once before directly opening in the program. But still many times you must have faced same error again and again whenever you try to read alternate index vsam file via a cobol program and through JCL.

Whenever we define VSAM alternate index file we have following components:

Cluster:          OLDMF.DATA.KSDALT

Data:              OLDMF.DATA.KSDALT.DAT

INDEX:            OLDMF.DATA.KSDALT.INX
AIX :               OLDMF.DATA.KSDALT1
PATH:             OLDMS.DATA.KSDALT.PATH

This filename and alternate index is used in the JCL to read the Cobol program and in JCL it is placed with its logical name as:


//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD

//OLDMFD    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD



The problem is in the way we write our logical name. Even if you try to initialize file and you may still face the error. It will be resolved by the way we write logical name for the alternate index in the JCLs.
Correct declaration:

//OLDMF     DD   DSN= OLDMF.DATA.KSDALT,DISP=OLD


//OLDMF1    DD   DSN=OLDMF.DATA.KSDALT1,DISP=OLD




Always the logical name will be appended with the next sequence number starting from 1.

Writing MQ Batch Program and MQ JCL

How to start MQ batch program and MQ JCL?

Message Queuing is the bridge between agile world today. Today there are lot of variations in programming language. There is need of hour to make different systems integrate; talk with each other and to make this happen MQ series is one of the best option.

I have written couple of blogs on MQ efficiency  use etc.Today I will write MQ blog to understand how easy is to use MQ .

There are few steps which MQ batch program/JCL requires it:

MQ JCL

1. There is MQ load library which has in build objects which is needed by any MQ JCL and its is mostly named as from MQM*. in your mainframe system

2.  Check if the Queue Manager is passes to get connected to Queue Manager.Typically, all the mainframe system as SySplex. 

Now what is Sysplex? Sysplex is the word derived from system complex. This means generally the mainframe server are divided into one or more LPARs. LPARs are logical partition. This is done to use mainframe server efficiently. I will not go in this details now.

So coming back to point 2, due to Sysplex environment  each LPAR has its Queue Manager. And generally MQ admin group, mergers all the Queue Manager into one. So always confirm for Queue Manager from MQ admin. Due to this, it gives flexibility to MQ JCL to run against any Queue Manager available. Even if one is down, job will not fail it will run against next avaliable.

3.  Make sure you have correct Message Queue Names.

Logic is all the Message Queues are controlled by its Queue Manager. Queue Manager has couple of setting for Queues.

MQ Program

1. Check to include IBM MQ copy books. Its for MQ Manager Check, Message Queue Check, Parameters for both of them and return code checks. These copybooks are available on IBM site and they are pretty much standard for all the MQ programs.

2. Check that you have placed calls correctly. All the call along with parameters are available at IBM website.

Generally to read MQ it goes as:

a. Connect Queue Manager

b. Open Queue

c. Browse Message Queue - Keep it destructive read preferbly

d. Process the message

e. Close Queue

f. Disconnect Queue Manage 

Generally to write MQ it goes as:

Make sure you read the file from where you want to write message to Queue into working storage variable passed to Queue.

a. Connect Queue Manager

b. Open Queue

c. Write Message into Queue 

e. Close Queue

f. Disconnect Queue Manage 

Best practice is to check return code after every call.

3. Also check do MQDISCONNECT at the end. This helps to free resource immediately.

DB2 Performance Tips - EXPLAIN

DB2 EXPLAIN Explained

What is DB2 Explain?

It is an DB2 aid for optimization.

What does DB2 Explain does?

All the SQL statements are passed through DB2 optimizer and the access path are externalized. This means any DBA doing performance test can get correct access path by using EXPLAIN. All the access path are recorded in PLAN_TABLE.

What is PLAN_TABLE?

Important coloumns for PLAN_TABLE are as following:

QUERYNO : This is the query number user should assigned to identify query.
QBLOCKNO:  Position of query in the statement being EXPLAINED
APPLNAME : This is PLAN Name
PROGNAME : Program/Package Name
METHOD : There are 4 options and each number signifies type of join

0 Table Access

1 Nested Loop

2 Merge Scan

3 Sort needed by ORDER BY, GROUP BY, SELECT, DISTINCT or UNION

4 Hybrid
CREATOR : User id
TNAME : Tablename
TABNO : position and reserved for IBM only
ACCESSTYPE : 

I - by index

N - indexscan when predicate is IN

R - Table Space Scan

M - Multiple Index Scan

MX - Index Scan

MI - Intersection of multiple index

MU - Union of multiplex index
MATCHCOLS : number of keys used in index scan

ACCESSCREATOR : Creator of Index

ACCESSNAME : Name of Index

INDEXONLY : Y/N ( Yes is index is sufficient to get required data)

SORTN_UNIQ : Y/D (Yes if Sort performed to remove duplicates )

SORTN_JOIN : Sort is performed on table when method is 2 or 4

SORTN_ORDERBY : if query results in order by 

SORTN_GROUPBY : if query results in group by

TSLOCKMODE : Lock Mode of table or tablespace

IS : Intent Share Lock

IX : Intent Exclusive Lock

S : Share Lock

U : Update Lock

X : Exclusive Lock

SIX:  Share with intent exclusive lock

N : UR isolation; no lock

TIMESTAMP : Timestamp at which explain statement is bound

PREFETCH : List Prefetch or Sequential Prefetch

MIXOPSEQ : Sequence

DB2 performance tips - PART 1

DB2 Queries that can optimize performance 

1. Select columns which are required
2. Use where clause for queries where ever possible
3. Avoid using NOT in Queries as NOT is non-indexable
4. Use DATE Functions wherever possible to get number of days.
       Eg: Select DATE('25-04-1986') - DATE('04-09-1995')
5. Details on Index and EXPLAIN:

Before and After creating index always use EXPLAIN functionality of DB2 to measure how   and index is working.If the index you created is being used then you will get that in your query once explain runs.

If you want to see when your index is last used you can do that by this query:

SELECT * FROM SYSIBM.SYSINDEXES where NAME = 'your index name';

I will give details on EXPLAIN in my next DB2 blog.