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'