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.

SYNCSORT or DFSORT - PART 3

Problem Statement

//*********************************************************************

//* I have two files. 
//* FILEA contains only timestamp X(26).
//* FILEB contains Office X(3), Account X(6), timestamp X(26) and Identity number X(3)
//* I want all records of FILEB for timestamp values not present in FILEA

//*********************************************************************

SORT JCL Used:

Step 1: Sorted FILEB with timestamp as first field.

//SORT01   EXEC PGM=SORT

//SORTIN   DD  DSN= REC.FILE, ---FILE B(WITH LAYOUT GIVEN)

//             DISP=SHR

//SORTOUT  DD  DSN=OUTOUT.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT   DD  SYSOUT=*

//SYSPRINT DD  SYSOUT=*

//SORTLIST DD  SYSOUT=*

//SYSIN    DD  *

        INREC FIELDS=(10,26,1,9,36,3)

        SORT FIELDS=(1,26,CH,A)

/*

Step 2 Remove/Discard duplicates on from the sorted file and the FILE A which is sorted on timestamp.

//SORT02   EXEC PGM=ICETOOL

//INPUT    DD  DSN=OUTOUT.FILE,DISP=OLD      

//         DD  DSN=TMSTMP.FILE,DISP=OLD --FILE A(TIMESTAMPS)

//OUTPUT   DD  DSN=FILE.DISCARD,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(TRK,(1,5),RLSE),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//FINAL1   DD  DSN=FINAL.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(TRK,(1,5),RLSE),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//TOOLMSG  DD  SYSOUT=*

//DFSMSG   DD  SYSOUT=*

//TOOLIN DD *

  SELECT FROM(INPUT) TO(OUTPUT) ON(1,26,CH) ALLDUPS DISCARD(FINAL1)

/*

 ICETOOL is used here to sort on timestamp and remove dupicate timestamp records.

Step 3 Sorted as per layout in FILE B.

//SORT03   EXEC PGM=SORT

//SORTIN   DD  DSN=FINAL.FILE,

//             DISP=SHR

//SORTOUT  DD  DSN=FINAL1.FILE,

//             DISP=(OLD,CATLG,DELETE),

//             SPACE=(CYL,(10,50),RLSE),VOL=(,,,40),

//             DCB=(RECFM=FB,BLKSIZE=0,LRECL=80)

//SYSOUT   DD  SYSOUT=*

//SYSPRINT DD  SYSOUT=*

//SORTLIST DD  SYSOUT=*

//SYSIN    DD  *

        INREC FIELDS=(27,9,1,26,36,3)

        SORT FIELDS=(1,26,CH,A)

/*
Sorted again in original file form.

Please refer PART1 and PART2 here.

JCL System Abend: SOC 1 and SOC 7

SYSTEM ABENDS

I am sure everyone is aware of system ABENDS. I have tried to list ABENDS for all the possible reasons. Hope you all find it useful.

SOC 1 

This abend can be faced in following situations. Rule out following possibilities:

• Any file doesn't have open statement and directly trying to read it
• The subscript it may be out of boundary for a defined COBOL table
• Review JCL for missing or misspelled DD name
• Problem in parm passed to subroutine related to addressing mode or Data mode
• Recording mode may be wrong 
• Sub program called is not found problem in dynamic link or static link
• Check load module there can be a possibility if subroutine program id and program name not same. This means bad load module.
• An uncontrolled loop moved data on top of instructions. 
• Problem can be same name for array and subroutine  

    SOC 7 

This is a data exception and can only occur when decimal (packed) instructions are used. 

• When alphanumeric data moved to packed decimal data (COMP 3).
• When Programmer use a variable, which is not defined in working storage.
• Period missing after imperative statements within AT END clause.
• Binary field in an arithmetic operation is not large enough to accept result.
• Failure to initialize a counter.
• Invalid incoming data (e.g., blanks, decimal points, or commas in a numeric field).
• Exceeding a table via a subscript (index) error, causing a reference to invalid data.
• Moving zeroes or low-values to a group field defined as numeric.
• An omitted or erroneous usage clause.
• Passing parameters between programs in the wrong order.

Options to recover deleted flat files or member for PDS

Options to recover deleted files

There is option on mainframe to recover flat files. Here are the various commands to 
do that:

Go to Start 6 
HLIST BCDS DSN('filename') 
Wait for system response
Issue command HRECOVER 'filename' and wait for system response.

Introduction to most useful SYSIBM DB2 tables - PART 2

Here comes some more SYSIBM tables

SYSIBM.SYSSTMT : This table is input to bind process. It contains statements for every plan known in DB2.

SYSIBM.SYSPLANS: contains information on every plan known to DB2. The plan name is unique in the DB2 subsystem.

SYSIBM.SYSFOREIGNKEYS: contains information foreign keys for table. 

http://supportline.microfocus.com/documentation/books/sx51/rhsqst12.htm

SYSIBM.SYSDBAUTH: Contains information on database level authorizations for each user of each database.  Entries are made into this table when database-related GRANT or REVOKE commands are issued for the location.

http://supportline.microfocus.com/documentation/books/sx51/rhsqst0y.htm

Please refer PART1 link to get more information on SYSIBM tables.

Useful free webinars for COBOL developers

Hello COBOL Developers!!

Here is amazing site for COBOL development, here there are web seminars where we can register and view them:

https://www.microfocus.com/resources/events/VCwebinars/VCwebinars.aspx

Topics included range from Visual COBOL, Using COBOL with JAVA, .NET etc.

XML with COBOL Basics

Introduction to XML coding with COBOL

By the growing Global transactions, its need of an hour to introduce common language that can be used cross platform. Here comes introduction to XML on COBOL. This blog gives brief introduction on XML usage with COBOL. It includes:

1. XML Introduction

2. XML Tags

I have focus on how actually developer can code in COBOL language as you can find lot of literature online. 

XML : 

It is Extensible Markup Language. It can be used cross platform hence universal in nature.It has tag language.Tags can be single or nested depending upon requirements.

Eg: <a>...</a>

Tag documents can be designed in free flow way.Tags can't overlap each other. Eg <p><r>... </p></r> <--  This is not allowed. There are few more rules which I will point it out in my example.

Example:

<?xml version = "1.0"?>

<!--comment line-->

<p>

</p>

As you can see above XML are:

1. Text based can be saved as .txt file or .xml file or as PS file in Mainframe environments.

2. It is case sensitive

3. Contains one root

4. Starts and end with matching tags

5. Any attribute value should be in quotes

6. Properly nested

You will find usage of XML Parser and XML-COBOL coding in my up coming blogs.

EBCDIC characters required in COBOL programing

Here is the chart for EBCDIC characters

Creating Different Dimension Tables in COBOL

As we saw single dimension table in my blog linked here . There are many other ways to use COBOL table.

Another Way of Single Dimension TABLE:

COBOL Declaration:

01  A1-TABLE.

      05 A2-TABLE OCCURS 100 TIMES

            15 P1-NO             PIC X(05).

            15 P1-NAME        PIC X(10).

             15 P1-TEL NO.

                   25   LAND-LINE  PIC 9(10).

                   25   MOBILE        PIC 9(11).

This declarlation will work in COBOL as table described below:

Interview Questions for COBOL

How do you do in-line PERFORM? 

        

PERFORM ... <UNTIL> ...

<sentences>

END-PERFORM

When would you use in-line perform?

     When the body of the perform will not be used in other paragraphs. If the body of the perform is a generic type of code (used from various other places in the program), it would be better to put the code in a separate Para and use

      PERFORM Para name rather than in-line perform.

 What is the difference between CONTINUE & NEXT SENTENCE ?

 They appear to be similar, that is, the control goes to the next sentence in the paragraph. 

 But, Next Sentence would take the control to the sentence after it finds a full stop (.). 

 Check out by writing the following code example, one if sentence followed by 3 display statements 

 If 1 > 0 then next sentence end if display 'line 1' display 'line 2'. display 'line 3'. Note- there is a dot (.) only at the end of the last 2 statements, see the effect by replacing Next Sentence with Continue.     

Efficient use of decalartion in COBOL

Efficiently use of alphanumeric and numeric declaration in COBOL

In Working Section:

03  WS-ALPHX.                          

    05  WS-NUM          PIC 9(4).     

We can use this way as alphanumeric as well as to check numeric.

For Eg:

MOVE CAPIN(1:4)      TO WS-ALPHAX

IF WS-ALPHAX (1:1) IS NOT NUMERIC       

   MOVE 0 TO WS-ALPHAX (1:1)

END-IF      

                                  

IF WS-NUM IS NUMERIC                  

   MOVE WS-NUM TO CAPIN2

ELSE                                          

   MOVE 0              TO CAPIN2

END-IF                        

Search Option of COBOL

Here is how to use search option in COBOL

COBOL Declaration:

01  A1-TABLE.                                               

    03   FILLER                     PIC 9(02) VALUE  01.    

    03   FILLER                     PIC 9(02) VALUE  02.    

    03   FILLER                     PIC 9(02) VALUE  03.    

    03   FILLER                     PIC 9(02) VALUE  40.    

    03   FILLER                     PIC 9(02) VALUE  50.    

                                                               

01 AE-TABLE REDEFINES A1-TABLE.                            

    03  IE-CODE        PIC 9(02)  OCCURS 5 TIMES           

                           INDEXED BY P1.                     

Code in procedure division : 

      SET P1 TO 1.                                

      SEARCH IE-CODE                            

          AT END SET NOT-IE-CODE TO TRUE        

      WHEN AE-CODE IS EQUAL TO IE-CODE (P1)   

          SET IE-CODE1 TO TRUE

Submit JCL through REXX

The following REXX tool explains how JCL can be submitted through REXX.

Step 1: To get inputs from the user

/*REXX*/

/* TRACE I */

USERID = SYSVAR('SYSUID')

 "EXEC 'SYS2.CMDPROC(CLRSCRN)' CLIST"

ARG  ID FILEDT SQLINR

SAY  ID

SAY  FILEDT

SAY  SQLINR

IF ID = ' ' THEN

    DO

      SAY 'ENTER ID REQUIRED FOR INQUIRY'

      PULL IDID

    END

IF FILEDT = ' ' THEN

    DO

      SAY 'ENTER FILE DT REQUIRED FOR INQUIRY IN MM/DD/YYYY FORMAT '

      PULL FILEDT

    END

IF SQLINR = ' ' THEN

    DO

      SAY 'ENTER THE REGION FROM WHERE ID IS REQUIRED'

      PULL SQLINR

    END

IF IDID = 'END' | FILEDT = 'END' | SQLINR = 'END' THEN

    DO

      SAY 'COMMAND ENDED. THANK YOU'

      EXIT

    END

   INTLL  = LEFT(USERID(),3)

/* */

Step: 2: To allocate file where JCL will be returned with the user id automatically appended 

  "ALLOC FI(JCLFILE) DA('"USERID()".PABCB.N.TEMP.ABCD') OLD CATALOG  REUSE",

      "SPACE(1,1) TRACKS LRECL(80) BLKSIZE(3120) RECFM(F,B)"

Step 3: To create JCL

   IF ID <> "END" THEN DO

      JCL.1   = "//#ABC"INTLL"C JOB (00011100000"USERID()"),'X',"

      JCL.2   = "//             CLASS=X,"

      JCL.3   = "//             MSGCLASS=0,"

      JCL.4   = "//             REGION=4M,"

      JCL.5   = "//             NOTIFY="USERID()

      JCL.6   = "/*JOBPARM S="ABCV

      JCL.7   = "//*"

      JCL.8   = "//STEP01S  EXEC PGM=IKJEFT01"

      JCL.9   = "//STEPLIB   DD  DSN=DB2E.PSYSB.P.ABCD.SDSNLOAD,DISP=SHR"

      JCL.10  = "//SYSTSPRT  DD  SYSOUT=*"

      JCL.11  = "//DETAILS   DD  SYSOUT=*"

      JCL.12  = "//FREELST   DD  SYSOUT=*"

      JCL.13  = "//SYSTSIN   DD  *"

 JCL.14 = " EXEC 'ISPS.PABCB.P.ABC.EXEC(PVDREQR)' '"ID" "FILEDT" "SQLINR"'"

    END

      "EXECIO * DISKW JCLFILE (STEM JCL. FINIS)"

      "FREE FI(JCLFILE)"

Step 4: To submit JCL

      "SUBMIT '"USERID()".PABCB.N.TEMP.ABCD'"

After this, you can see the JCL in spool by checking the jobname #ABC*

Options of CICS DB2 connections

Options of CICS DB2 connections 

There are three main types of threads that can  be used for DB2 CICS connection:

TYPE=COMD
TYPE=POOL
TYPE=ENTRY

COMD is DB2 command thread it is used for only processing DB2 commands through DSNC transaction.  It is not used for CICS attachment facility.

POOL is Pool threads are used for all transactions and commands not using an entry type or a DB2 command type. Pool threads are normally used for: 

1. Low volume transactions
2. Overflow transactions  from both COMD and ENTRY type threads 
3. It is terminated immediately when it is unused

ENTRY is ENtry type thread which is used for: 

1. High volume transactions 
2. High priority transactions 
3. Controlled transactions 

ENTRY type can be defined as protected as well as unprotected.The MVS subtask for an entry thread is not terminated, even if the entry thread is terminated. This is true for both protected and unprotected entry threads. 
Requests for an entry thread can be transferred to the pool, if an entry thread is not available.

CEDC Command to check MQconnection

CEDC Commands that can be used to check MQ connection 

If any user wants to know INITQ or any MQ manager or any other MQ parameter here is the command:

CEDC V MQ

It will give option as below. If you dont know give * for both the options and then press enter:

OBJECT CHARACTERISTICS
   CEDC View MQconn(         )
     MQconn         ==>       *
     Group            ==>       *

It will display all the MQConn for all the sysplex region as below.

ENTER COMMANDS
NAME            TYPE            GROUP                       LAST CHANGE 
MQ1A            MQCONN      CICSAB1             25/04/11   11:11:11
MQ1A             MQCONN     CICSAB2   v        25/04/11   11:11:11   
      
Give View in front of group you want to view as I gave above in red and press enter.

Introduction to most useful SYSIBM DB2 tables - PART 1

Here are most commonly used SYSIBM tables

SYSIBM.SYSTABLES : Any table or view or alias defined for your application will be present in this table with all the information.

Here I have got great links for tables where you can get details for each column of  SYSIBM tables. For each table there is separate link I have put them all together for quick reference.

http://supportline.microfocus.com/Documentation/books/sx51/rhsqst2l.htm

SYSIBM.SYSCOLUMNS: All the details about columns for any table along with characteristics of each column is defined here.

http://supportline.microfocus.com/Documentation/books/sx51/rhsqst0s.htm

Common CECI CICS commands used while debugging

CECI Commands that can be used to test or debug any CICS program

There are many commands in CICS which can be very handy when any developer wants to do unit testing.

CECI READ FIL(XYZ) RID(&A)

 Give F5 and define &A give length for that temporary character

When you give above command it will not read file until it is open. It will give errors as below: 

Triggering CICS transaction through batch - PART3

Common Error Messages - Batch Processing

Please refer PART1 and PART2 blog before going through this blog.

There are some common error messages that may be thrown when the transaction is launched using from the batch job.

1) When the CICS region is not active

IEE341I                       CICSREN1 NOT ACTIVE

2) When the console is not defined in the CICS Region

DFHAC2015 date time CICSREN1 Console consname has not been defined to CICS.

3) When the transaction does not exist in the CICS region

DFHAC2001 date time CICSREN1 Transaction 'tranid' is not recognized.

4) When the transaction is disabled in the CICS region

DFHAC2008 date time CICSREN1 Transaction tranid has been disabled and cannot be used.