MQPUT Through JCL

JCL to LOAD the Data into MQ(Message Queue) channels from a sequential data set.

With CSQUTIL MQPUT can be done in batch.

//MQLOAD1  JOB (ABCDEFGH),'ABCDEFG',

//         MSGCLASS=T,SCHENV=DB2DBJB,

//         NOTIFY=&SYSUID,TIME=90

//*

//MKCMDS   EXEC PGM=CSQUTIL,PARM='Queue Manger Name'

//STEPLIB   DD DSN=MQM.M***.SCSQAUTH,DISP=SHR

//INFILE   DD DISP=SHR,DSN=TEST.RSS.XCI0500.STRGTEST.INPUT

//SYSPRINT DD SYSOUT=*

//CSQUOUT  DD SYSOUT=*

//SYSIN    DD *

LOAD QUEUE ( PK01) DDNAME(INFILE)

/*

JCL to Alter and Define Queues

Commands can be executed in JCL to alter/define MQ

Here I am altering INITQ for Local Queue PK01.

//PRODSTEP EXEC PGM=CSQUTIL,PARM='QueueManger Name'        <===== QMGR ?????

//SYSPRINT DD SYSOUT=* 

//STEPLIB DD DSN=MQM.SCSQLOAD,DISP=SHR 

//SYSIN DD *

COMMAND DDNAME(CMDGET)

/*

//CMDGET DD *

ALTER QLOCAL(PK01) INITQ(CICS1.INITQ)                                 

Delete Messages from Queue through JCL

Messages can be deleted from batch job

The below sample job deletes all the messages from a MQ. CSQUTIL is a utility program which enables to delete message from queue. Only the developer running this should have authority to access queue.

//EMPTY EXEC PGM=CSQUTIL,PARM='AB01'                    <---- Queue Manager
//STEPLIB DD DISP=SHR,DSN=MQM.SCSQANLE
//        DD DISP=SHR,DSN=MQM.SCSQAUTH
//SYSPRINT DD SYSOUT=*
//SYSIN DD *
EMPTY QUEUE(MQTEST_Q)
/

COBOL z/OS Performance Improvements

COBOL tips for performance improvements

• Always consider using SEARCH ALL option instead of checking long table by coding.
• Internal SORT is usually uses longer CPU than External SORT.
• Always consider using Evaluate, it creates program more modular.
• Avoid using go to statement as they create irregular flow.

DB2 - Locks

How DB2 locks works?

DB2 Database Manager implicitly acquires locks as they are need, except for occasions where the Uncommitted Read isolation level is used.

It is possible for the DB2 Database Manager to acquire row-level locks or table-level locks on a specific table resource by executing a special form of the ALTER TABLE SQL statement. By default, the DB2 Database Manager always attempts to acquire row-level locks.

  

 The syntax is ALTER TABLE [Table Name] LOCKSIZE [ROW | TABLE] 

 E.g. ALTER TABLE school LOCKSIZE TABLE

COBOL Date Function to get correct offset from GMT

COBOL DATE FUNCTION

There was requirement were we had to send MT202 to Swift. Swift accepts time of GMT or either it accepts with correct offset and when we use current time option in COBOL it gives local time hence we were getting ET time.I had faced issue with time. The offset I was getting was not correct from GMT. 

For E.g.: ET time is 4 hrs offset from GMT when day-light saving is on and it is 5 hrs offset from GMT when day light saving is off.  

I used following function and by using this function we get the offset from GMT of where your application is running, then we can easily just use native COBOL.

See the CURRENT-DATE intrinsic function at:

ICETOOL Example - Matching and non matching records

Create files with matching and non-matching records

There are two input files containing lists of records, as follows:

Input File1

V
F
C
H
D

Input File2

K
H
C
V
M

How can we create output files for the following?

• The records that appear in both File1 and File2
• The records that appear only in File1
• The records that appear only in File2

Below is an ICETOOL job that can do this match by using the SPLICE operator. SPLICE operator in ICETOOL helps to perform various file join and match operations. 

Compile new DB2 program in Changeman

Add new DB2 program in Changeman

Point to take care while creating new DB2 program in changeman and to promote it to test regions

Create a PKG member in the package where a new DB2 program is present. This member is required in order for DBRM to be promoted to the test region. If this member is not present, changeman gives error while promoting DBRM.

          BIND PACKAGE      (PVNPRBDL) +

                MEMBER          (PVNBPBOT) + 

                OWNER            (DABOC) + 

                QUALIFIER       (PVNDB001) + 

                ACTION           (REPLACE) + 

                DEGREE          (1) +  

                ISOLATION      (CS) + 

                VALIDATE       (BIND) + 

                EXPLAIN         (YES) +  

                 RELEASE       (COMMIT) + 

                CURRENTDATA  (YES) 

OTE (Open Transaction Environment)

What is OTE (Open Transaction Environment)?

Task Control Block 

The open transaction environment (OTE) function was added to CICS Transaction Server for OS/390, Version 1 Release 3 and later versions.

In earlier releases of CICS, CICS ran all user transactions under a single z/OS TCB, the CICS quasi-reentrant (QR) TCB. Direct invocation of other services outside the scope of the CICS permitted interfaces could interfere with the use by CICS of the QR TCB. In particular, requests that resulted in the suspension ("blocking") of the QR TCB, which happens when an MVS wait is issued, would cause all CICS tasks to wait. For example, some services provided by DB2, MVS, UNIX® System Services, or TCP/IP, might result in TCB blocking.

MQGET read in non-destructive way

MQGET OPTIONS to read Queue in Non Destructive Way

To delete from queue explicitly, developer needs to Read a queue, performing some other action and finally delete queue explicitly.

Steps:

During MQOPEN Call:

•  Open Queue with MQOO_BROWSE option
•   Check the message counter

DB2 Bind process

DB2 Bind Process  

Find a quick overview of the bind process in my earlier post.

                                         Process for Bind in Brief

Precompiler : Functions:

Checks the DB2 code  in the program for errors. 

Adds working storage areas and source code compatible statements that are used to     invoke DB2. One to the working storage areas contains a literal "timestamp" called a consistency token.

Extracts all the SQL statement from the program source and placed into a member called   the DataBase Request Module, or DBRM, which has same consistency token.

Compiler:The COBOL code with SQL is input to complier and compiler checks for any error and Object Module is formed. This code is complied version of code.

DB2 Bind Process: Overview

The bind process

 The bind process establishes a relationship between an application program and its relational data. This step is necessary before you can execute your program. Currently, DB2 allows you two basic ways of binding a program: to a package, or directly to an application plan.

Even when they are bound into packages, all programs must be designated in an application plan. BIND PLAN establishes the relationship between DB2 and all DBRMs or packages in that plan. Plans can specify explicitly named DBRMs, packages, collections of packages, or a combination of these elements. The plan contains information about the designated DBRMs or packages and about the data the application program intends to use. It is stored in the DB2 catalog.

JCLs to Alter Lock parameter of DB2 table

   

                         JCLS TO ALTER LOCK PARAMETERS OF ANY TABLE (DB2 V 9)

LOCKPART

JCL to alter tablespace lockpart parameter (only for partitioned) to NO in DB2

//PRODDB2  EXEC PGM=IKJEFT01,DYNAMNBR=30                

//STEPLIB  DD DSN=SYS1.DSN900.SDSNLOAD,DISP=SHR         

//SYSTSPRT DD SYSOUT=X                                  

//SYSTSIN  DD *                                         

    DSN SYSTEM(DB2PROD)                                    

          RUN PROGRAM(DSNTEP) PLAN(DB2PLAN) -         

          LIBRARY('DSN.DB2PROD.RUNLIB.LOAD')            

      END                                               

//SYSPRINT DD SYSOUT=*                                  

//SYSUDUMP DD SYSOUT=*                                  

//SYSIN    DD *                                         

ALTER TABLESPACE PRDX1.TSSMG                            

LOCKPART NO;                                            

COMMIT;                                                 

This will give problem as tablespace is not stopped. it will give following error:

PAGE    1                                                                      

***INPUT STATEMENT:                                                             

 ALTER TABLESPACE PRDX1.TSSMG                                                   

 LOCKPART NO;                                                                  

SQLERROR ON   ALTER     COMMAND, EXECUTE   FUNCTION                            

 RESULT OF SQL STATEMENT:                                                      

 DSNT408I SQLCODE = -626, ERROR:  THE ALTER STATEMENT IS NOT EXECUTABLE BECAUSE  THE PAGE SET IS NOT STOPPED 

 DSNT418I SQLSTATE   = 55015 SQLSTATE RETURN CODE                               

 DSNT415I SQLERRP    = DSNXIATS SQL PROCEDURE DETECTING ERROR                  

 DSNT416I SQLERRD    = 155  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION          

 DSNT416I SQLERRD    = X'0000009B'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

          INFORMATION                                                          

PAGE    1                                                                      

***INPUT STATEMENT:                                                            

 COMMIT;                                                                        

 RESULT OF SQL STATEMENT:                                                      

 DSNT400I SQLCODE = 000,  SUCCESSFUL EXECUTION                                 

 DSNT418I SQLSTATE   = 00000 SQLSTATE RETURN CODE                              

 DSNT416I SQLERRD    = 0  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION            

 DSNT416I SQLERRD    = X'00000000'  X'00000000'  X'00000000'  X'FFFFFFFF'  X'000

SYSIBM entries for TableSpace Partitioned Table

STEPS TO CHECK ALL THE TABLESPACE PARTITIONED ENTRIES

1. Tables are always defined in respective tablespaces 

SELECT * FROM SYSIBM.SYSTABLES     
WHERE NAME=  'MSG_RCVD'         
WITH UR;                           

The tablespace for the mentioned table is TSMPR
----------------------------------------------------------------------------------------

2. The table space is partitioned by defining index on each partition with Limit keys.

SELECT * FROM SYSIBM.SYSTABLESPACE      
WHERE NAME  = 'TSMPR'                 
WITH UR;                                

The partition we get from here were 

SYNCSORT or DFSORT: Part 2

SORT Function and Examples

SORT

SORT Fields: The syntax is given below:


SORT FIELDS=(p1,l1,f1,o1,p2,l2,f2,o2,.....) ¦         

FIELDS=(p1,l1,o1,p2,l2,o2...),FORMAT=x 

FIELDS={(} COPY {)} ¦                             
{,CKPT}                                           
{,DYNALLOC{=(d ¦ ,n ¦ d,n ¦ OFF)}}                
{,EQUALS ¦ ,NOEQUALS}                             
{,FILSZ={n ¦ En ¦ Un} }                           
{,SIZE={n ¦ En ¦ Un} }                            
{,SKIPREC=n}                                      

SYNCSORT or DFSORT: Part 1

The sort program is used to sort data into a desired sequence based on  business requirement. Also, it can be used to merge many files into one single file.

Features of the SYNCSORT utility

• Copy Function
• Merge function capable of merging up to 32 data sets into one sequence provided those data sets are sorted. Latest versions allow up to 100 data sets to be merged.
• Sort facility which can sort single or concatenated data sets into one data set.
• Conversion of the packed decimal fields to printable formats.

DB2 Abends while loading unloading tables

Here are some common abends faced while running DB2 load unload.

Issue: The error shows input field is not matching the data or vice versa. Key word here is 'INPUT FIELD 'ACCT_3' NOT ENTIRELY WITHIN INPUT RECORD'

DSNURWI - TABLE SPACE DatabaseName.tablespace WILL REMAIN IN BASIC ROW FORMAT DUE TO 

SPRMRRF=DISABLE

DSNURRST - EXISTING RECORDS DELETED FROM TABLESPACE                            

DSNURWBG - INPUT FIELD 'ACCT_3' NOT ENTIRELY WITHIN INPUT RECORD          

DSNURWBF - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1     

GBAC - UTILITY DATA BASE SERVICES MEMORY EXECUTION ABENDED, REASON=X'00E40323' 

JCL TO ALTER or DEFINE Queues

Code snippets for defining or altering Message Queues through JCL

Here I am changing INITQ for Local Queue PK01.

//PRODSTEP EXEC PGM=CSQUTIL,PARM='QueueManger Name'              

//SYSPRINT DD SYSOUT=*                                                 

//STEPLIB DD DSN=MQM.SCSQLOAD,DISP=SHR                                

//SYSIN DD *      

MQ Batch Triggering Process

We can set up MQ job through MQ triggering Process. Job will be automatically triggered through MQ. Whenever the message is written to MQ the job mentioned will get triggered (G170G170). Here are the steps to be followed.

SET UP MQ Trigger batch job

DB2 Commands for Table Space

Access Commands

Steps to  go DB2 Commands panel:

 

Go to DB2 DATA BASE INTERACTIVE FUNCTIONS MENU Go to DB2I PRIMARY OPTION Select MENU in the list (It is generally at position 1).