How to create a temporary VSAM file

We know how to create a normal temporary sequential file and how to use it.

Let us find out on how we can create temporary VSAM file.

//TEMPFILE  DD  DSN=&&TEMPFILE,DISP=(NEW,PASS,DELETE),

//              UNIT=SYSDA,

//              SPACE=(80,(5,2)),

//              AVGREC=U/K/M

//              RECORG=KS/ES/RR/LS

//              KEYLEN=8,KEYOFF=0

First parameter of SPACE is always numeric and assumes it as a BLKSIZE

AVGREC will have either Units, Kilos or Megabytes

RECORG (Record Organization) such as KSDS,ESDS, RRDS and LDS

KEYLEN specifies the key length

KEYOFF specifies the offset

Different ways to initiate CICS transaction

There are six different ways to initiate a transaction in CICS.

• Embedding four character TRNSID in the top left most corner of the screen.

• Using EXEC CICS START TRANSID().

• Using EXEC CICS RETURN TRANSID().

• Defining TRANSID in DCT(Destination Control Table) to enable ATI (Automatic Task Initiation).

• Making use of PLT (Program List Table).

• Associating four character TRANSID in PCT (Program Control Table).

How to Submit Job Automatically After Current Job

We can use IEBGENER to write the Job into internal reader (INTRDR) as the last step in the current job as below:

       //LASTSTEP PGM=IEBGENER

   //SYSOUT   DD SYSOUT=*

   //SYSPRNT  DD SYSOUT=*

   //SYSIN    DD DUMMY

   //SYSUT1   DD DSN=YOUR.BATCH.JCL(JOBNAME),DISP=SHR

   //SYSUT2   DD SYSOUT=(A,INTRDR)

Reading a Sequential File in Reverse Order

There are two ways to read the sequential file in reverse order

     1. Using FileAid

//STEP0100 EXEC PGM=FILEAID                   

//SYSPRINT DD SYSOUT=*                        

//DD01     DD DSN=YOUR INPUT DATASET,DISP=SHR

//DD01O    DD DSN=YOUR OUTPUT FILE IN REVERSE ORDER,

//         DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//         SPACE=(CYL,(X,Y),RLSE)

//SYSIN    DD  *

 $$DD01 COPYBACK

/*                        

2. Using SORT (Assuming LRECL of input file is 80 bytes)

//STEP0100 EXEC PGM=SORT                   

//SYSOUT   DD SYSOUT=*                        

//SORTIN   DD DSN=YOUR INPUT DATASET,DISP=SHR

//SORTOUT  DD DSN=YOUR OUTPUT FILE IN REVERSE ORDER,

//         DISP=(NEW,CATLG,DELETE),UNIT=SYSDA,

//         SPACE=(CYL,(X,Y),RLSE)

//SYSIN    DD  *

 INREC FIELDS=(1,80,SEQNUM,8,ZD)

 SORT FIELDS=(81,8,ZD,D)

 OUTREC FIELDS=(1,80)

/*                        

Example:

//STEP0100 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD DSN=T365T.T365F1.INPUT,DISP=SHR  
//SORTOUT  DD DSN=T365T.T365F1.OUTPUT,         
//            DISP=(NEW,CATLG,DELETE),LRECL=57,
//            SPACE=(CYL,(1,2),RLSE),AVGREC=K  
//SYSIN    DD  *                               
 INREC FIELDS=(1,57,SEQNUM,8,ZD)               
 SORT FIELDS=(58,8,ZD,D)                       
 OUTREC FIELDS=(1,57)                          
/*                                             

//*                                            

Input file:

Output file:

Splitting files using SORT utility

Using SORT, we can split a file into multiple files based on

1. Record Count
2. Conditions.

Splitting file into multiple files based on record count:

Suppose, i have a file with 100 records and i need to split this file into 3 files where first file should have 30 records, second and third one should have 35 each.

Here is how to do it.

//A171501C JOB (Accounting-Information),CLASS=T, 
//              MSGCLASS=H,NOTIFY=&SYSUID                    
//*THIS JCL SPLITS FILE BY NUMBER OF RECORDS                    
//STEP01   EXEC PGM=SORT                                        
//SORTIN   DD DSN=input-file-name,DISP=SHR                        
//SYSIN    DD *                                                 
  SORT FIELDS=COPY                                              
  OUTFIL FILES=01,ENDREC=30                                    
  OUTFIL FILES=02,STARTREC=31,ENDREC=65                       
  OUTFIL FILES=03,STARTREC=66,ENDREC=100                      
/*                                                              
//SORTOF01 DD DSN=output-file1,DISP=(NEW,CATLG,DELETE),
//            LRECL=57,DSORG=PS,RECFM=FB,AVGREC=K               
//SORTOF02 DD DSN=Output-file2,DISP=(NEW,CATLG,DELETE),
//            LRECL=57,DSORG=PS,RECFM=FB,AVGREC=K               
//SORTOF03 DD DSN=Output-file3,DISP=(NEW,CATLG,DELETE),
//            LRECL=57,DSORG=PS,RECFM=FB,AVGREC=K               
//SYSOUT   DD SYSOUT=*                           
//*     

                                         

ICEMAN Utility

ICEMAN utility is used to count the number of records in a tape data set. 

Example:

//A171501C JOB (Accounting-information),CLASS=T,             
//         MSGCLASS=H,NOTIFY=&SYSUID                      
//*THIS JCL TELLS THE NO. OF RECORDS IN THE SPECIFIED TAPE DATASET 
//COUNT    EXEC PGM=ICEMAN                                         
//SORTIN   DD DSN=Dataset-name,DISP=SHR                           
//SYSOUT   DD SYSOUT=*                                             
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                    
  SORT FIELDS=COPY                                                 
  OUTFIL REMOVECC,NODETAIL,                                        
  TRAILER1=('NO. OF RECORDS: ' ,COUNT)                             
/*                                                                 
//* 

REMOVECC - REMOVES ANSI CONTROL CHARACTERS FROM THE REPORT      
NODETAIL - PREVENTS DETAIL RECORDS FROM BEING WRITTEN TO OUTFIL                                                                

DB2 Error codes

SQLCODE Overview

If SQLCODE = 0, execution was successful.

If SQLCODE > 0, execution was successful with a warning.

If SQLCODE < 0, execution was not successful.

If SQLCODE = 100, "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

New with DB2 V8, when DB2 processes a multiple row FETCH statement, the contents of SQLCODE is set to +100 if the last row in the table has been returned with the set of rows.

SQLCODE - Successful SQL Execution

SQL Return Code +100 ROW NOT FOUND FOR FETCH, UPDATE OR DELETE, OR THE RESULT OF A QUERY IS AN EMPTY TABLE.

Suggestion: If expecting data, verify WHERE clause for accuracy and completeness.

SQL Return Code +117 THE NUMBER OF INSERT VALUES IS NOT THE SAME AS THE NUMBER OF OBJECT COLUMNS.

Suggestion: Correct SQL statement to provide only one value for each column in the table.

SQL Return Code +231 CURRENT POSITION OF CURSOR cursor-name IS NOT VALID FOR FETCH OF THE CURRENT ROW.

Suggestion: Be certain to FETCH to position on a row after opening a cursor. If cursor is declared SENSITIVE STATIC SCROLL, the row may be a hole, from which no values can be fetched.

SQL Return Code +304 A VALUE WITH DATA TYPE data-type1 CANNOT BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN THE RANGE OF THE HOST VARIABLE IN POSITION position-number WITH DATA TYPE data-type2.

Suggestion: Verify DCLGEN host variable definitions are current with DB2 catalog table/view attributes.

SQL Return Code +347 THE RECURSIVE COMMON TABLE EXPRESSION name MAY CONTAIN AN INFINITE LOOP.

Suggestion: Verify predicate in the SQL WHERE clause of the form "counter_col < constant" or "counter_col < :hostvar".

SQL Return Code +802 EXCEPTION ERROR exception-type HAS OCCURRED DURING operation-type OPERATION ON data-type DATA, POSITION position-number.

Suggestion: Check arithmetic operation for divide by zero or result to exceed size of host variable.

SQLCODE - Unsuccessful SQL Execution

SQL Error Code -117 THE NUMBER OF VALUES ASSIGNED IS NOT THE SAME AS THE NUMBER 

OF SPECIFIED OR IMPLIED COLUMNS.

Suggestion: Provide one value for each column in the table.

SQL Error Code -150 THE OBJECT OF THE INSERT, DELETE, OR UPDATE STATEMENT IS A VIEW, SYSTEM-MAINTAINED MATERIALIZED QUERY TABLE, OR TRANSITION TABLE FOR WHICH THE REQUESTED OPERATION IS NOT PERMITTED.

Suggestion: Be certain to specify base DB2 table/view names for INSERT statements.

SQL Error Code -180 THE DATE, TIME OR TIMESTAMP VALUE value IS INVALID.

Suggestion: Verify the data value is in the correct range and value type.

SQL Error Code -181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE.

Suggestion: Verify data format with the SQL Reference Guide.

SQL Error Code -204 name IS AN UNDEFINED NAME.

Suggestion: Correct DB2 CREATOR or OBJECT NAMEs located in SQL statements.

SQL Error Code -227 FETCH fetch-orientation IS NOT ALLOWED, BECAUSE CURSOR cursor-name HAS AN UNKNOWN POSITION (sqlcode,sqlstate).

Suggestion: CLOSE and re-OPEN the cursor; For scrollable use (FIRST, LAST, BEFORE, AFTER, or ABSOLUTE) to establish valid position.

SQL Error Code -305 THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST VARIABLE NUMBER position-number BECAUSE NO INDICATOR VARIABLE IS SPECIFIED.

Suggestion: Add null indicator variable to SELECT statement in the format of "column:hostvarind".

SQL Error Code -501 THE CURSOR IDENTIFIED IN A FETCH OR CLOSE STATEMENT IS NOT OPEN.

Suggestion: Correct logic in application program to OPEN the cursor before the FETCH or CLOSE statement.

SQL Error Code -502 THE CURSOR IDENTIFIED IN AN OPEN STATEMENT IS ALREADY OPEN.

Suggestion: Correct logic in application program to CLOSE the CURSOR before the OPEN statement.

SQL Error Code -503 A COLUMN CANNOT BE UPDATED BECAUSE IT IS NOT IDENTIFIED IN THE UPDATE CLAUSE OF THE SELECT STATEMENT OF THE CURSOR.

Suggestion: Use FOR UPDATE statement in your cursor.

SQL Error Code -530 THE INSERT OR UPDATE VALUE OF FOREIGN KEY constraint-name IS INVALID.
Suggestion: Ensure that INSERT row for DB2 PARENT table is completed before INSERT row in CHILD table.

SQL Error Code -532 THE RELATIONSHIP constraint-name RESTRICTS THE DELETION OF ROW WITH RID X'rid-number'.

Suggestion: Change the program to DELETE CHILD table row before DELETE of row on PARENT table.

SQL Error Code -551 auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name.

Suggestion: Contact the support DBA to GRANT the needed privilege.

SQL Error Code -803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN INDEX SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS Xrid.

Suggestion: Verify DB2 INDEX and, if needed, change the statement to an UPDATE.

SQL Error Code -805 DBRM OR PACKAGE NAME location-name.collection-id.dbrm-name.consistency-token NOT FOUND IN PLAN plan-name. REASON reason.

Suggestion: Ensure COLLECTION name is in DB2 PLAN. Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -811 THE RESULT OF AN EMBEDDED SELECT STATEMENT OR A SUBSELECT IN THE SET CLAUSE OF AN UPDATE STATEMENT IS A TABLE OF MORE THAN ONE ROW, OR THE RESULT OF A SUBQUERY OF A BASIC PREDICATE IS MORE THAN ONE VALUE.

Suggestion: -811 is often detected after program check for DB2 data existence. Consider using new DB2 V8 FETCH FIRST ROW ONLY feature instead.

SQL Error Code -818 THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT FROM THE BIND TIMESTAMP y BUILT FROM THE DBRM z.
Suggestion: Recompile and BIND the DB2 program. Verify correct LOAD library is being used.

SQL Error Code -904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: -904 is usually caused because a database utility job has started the desired DB2 object in utility mode. Check DB2 Master Log for more details on the resource name – contact DBA.

SQL Error Code -911 THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -913 UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Suggestion: Review DB2 Master Log to find process holding DB2 locks. Consider adding additional COMMITs to program holding the DB2 resource.

SQL Error Code -922 AUTHORIZATION FAILURE: error-type ERROR. REASON reason-code.

Suggestion: Connection to DB2 has failed due authority for USER or PLAN. Contact DBA to check DB2 authorizations.

SQL Error Code -927 THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.