SORT JOIN

SORT JOIN is a feature of syncsort that became available with release 1.2 for Z/OS. It allows you to match two files based on key fields that you specify using JOINKEYS. You specify the two files that you want to match using SORTJNF1 and SORTJNF2 DD cards:

//SORT01 EXEC PGM=SORT
//SORTJNF1 DD DSN=MASTER.FILE,DISP=SHR
//SORTJNF2 DD DSN=TRANS.FILE,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *

SORTJNF1 and SORTJNF2 do not need to be in the same format. Let's assume for this example that MASTER.FILE has the following fields:

Field	Position	Length
Account # Account Name Address Phone # Total Txn	1 6 26 56 63	5 20 30 7 6

Let's assume that TRANS.FILE has the following fields:

Field	Position	Length
Trans ID Account # Trans Date Txn Amount	1 6 11 21	5 5 10 6

If you want to get the transaction date and transaction amount for all matching account #s from the two files, you will have this in your SORT CARD:
JOINKEYS FILES=F1,FIELDS=(1,5,A)
JOINKEYS FILES=F2,FIELDS=(6,5,A)
REFORMAT FIELDS=(F1:1,5,    Account#
                 F2:11,10,  Transaction Date
                    21,6)   Transaction Amount
SORT FIELDS=COPY

Using DSNTIAR

DSNTIAR formats the SQLCA and returns a readable text message based on the SQLCODE. Here's the syntax:

CALL 'DSNTIAR' USING SQLCA ERROR-MESSAGE ERROR-TEXT-LENGTH

ERROR-MESSAGE should contain an array where the actual formatted message should be stored. In addition to that, the first elementary item in ERROR-MESSAGE should be an integer indicating the length of the array. For example, if you declared a 5-element array and each element is 72 characters long, the length of the array is 360 and this should be indicated by the integer in ERROR-MESSAGE.

How many elements should your array have and how long should the text be? That will depend on your requirements. The number of elements in the array determines how many lines of text you will be able to display. If the message for the SQLCODE that you are decoding exceeds the number of elements in your array, DSNTIAR will have a return-code of 4. Your requirements will also dictate how long each element should be. The minimum length is 72 and the maximum is 240. If you want each line to fit in the screen, you'll probably go for 72 to 80.

ERROR-TEXT-LENGTH indicates the size of each element in the array inside ERROR-MESSAGE.

Here's an example:
01  WS-ERROR-MESSAGE.
    05 WS-ERROR-MSG-LENGTH PIC S9(4) COMP VALUE +288.
    05 WS-ERROR-MSG-TEXT PIC X(72
       OCCURS 4 TIMES
       INDEXED BY ERROR-IX.

01 WORK-AREA.
    05 WS-ERROR-TEXT-LENGTH PIC S9(9) COMP VALUE +72.
.
.
.
CALL 'DSNTIAR' USING SQLCA
               WS-ERROR-MESSAGE
               WS-ERROR-TEXT-LENGTH

PERFORM VARYING ERROR-IX
   FROM 1 BY 1
  UNTIL ERROR-IX > 4

        DISPLAY WS-ERROR-MESSAGE-TEXT(WS-ERROR-IX)

END-PERFORM

If your SQLCODE = -803, the code above will display the following:

DSNT408I SQLCODE = -803, ERROR: AN INSERTED OR UPDATED VALUE IS INVALID
         BECAUSE INDEX IN INDEX SPACE XPERSON CONSTRAINS COLUMNS OF THE
         TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE
         COLUMNS.

Diagnosing Multi-Row Insert Errors

Errors encountered on an ATOMIC multi-row insert will be reflected on the SQLCODE. For example, if the ATOMIC multi-row insert failed due to duplicate rows, SQLCODE will be -803.

A NON-ATOMIC multi-row insert can return the following SQLCODE:
   

• 0 - Successful
  
• +252 - Successful but with Warnings
  
• -253 - At least one row (but not all) failed
  
• -254 - All inserts failed. No rows were inserted.
  

In order to determine how many rows were successfully inserted, which row failed, and to find out what errors were encountered, you need to execute the GET DIAGNOSTICS statement.

To get the number of rows and the number of error conditions encountered, you can execute GET DIAGNOSTICS this way:

EXEC SQL GET DIAGNOSTICS
    :ROWS-INSERTED = ROW_COUNT,
    :ERROR-CONDITIONS = NUMBER
END-EXEC

Now that you know the number of error conditions encountered you can perform a loop to check out those errors:

PERFORM VARYING I FROM 1 BY 1
  UNTIL I > ERROR-CONDITIONS

        EXEC SQL GET DIAGNOSTICS
            :WS-SQLCODE = DB2_RETURNED_SQLCODE,
            :WS-ROWNUM = DB2_ROW_NUMBER
        END-EXEC

        PERFORM Z900-DB2-ERRORS

END-PERFORM

Multi-Row Inserts for DB2 Version 8

One of the new features of DB2 for Z/OS Version 8 is the multi-row insert. It enables you to insert more than one row per execution of your EXEC SQL INSERT statement. Inserting more than one row at a time will improve the performance of your application.


In order to be able to use multi-row insert, you need to declare your host variables as arrays. Each host variable needs to have their own OCCURS clause. I fell to the temptation once of putting my host variables in a group item that is declared as an array. DB2 precomile was successful but it failed in the COBOL compile. DB2 wasn't able to interpret the host-variables as belonging to an array. Here's how your host variable should be declared:

       01 HV-SAMPLE.
          05 HV-ACCOUNT PIC X(08) OCCURS 100.
          05 HV-NAME PIC X(20) OCCURS 100.
          05 HV-AMOUNT PIC S9(09)V99 OCCURS 100.

In addition to declaring your host variables as arrays, you need to add the new clause FOR N ROWS and the new keywords ATOMIC or NOT ATOMIC CONTINUE ON EXCEPTION.

Here's an example:

          MOVE 100 TO WS-NUMROWS-TO-INSERT
          EXEC SQL
               INSERT INTO ACCOUNT-TABLE
               VALUES(:HV-ACCOUNT,
                      :HV-NAME,
                      :HV-AMOUNT)
                  FOR :WS-NUMROWS-TO-INSERT ROWS
               ATOMIC
          END-EXEC.

You can see that the number of rows that will be inserted is controlled by the WS-NUMROWS-TO-INSERT variable. You can also use a literal if the number of rows to insert will always be the same.

The ATOMIC keyword tells DB2 to back out the INSERT if it fails to insert one of the rows including the other rows prior to the row that encountered the error. For example, your multi-row insert is trying to insert 100 rows but encountered an error on the 10th row. The nine rows that were already inserted will be backed out, and DB2 will no longer attempt to insert the 90 remaining rows. If you don't want DB2 to do this, you need to specify NOT ATOMIC CONTINUE ON EXCEPTION. This will treat each error encountered separately and will not back out the prior rows that were already inserted. Also, it will continue processing the remaining rows after the row that encountered the error. ATOMIC is the default.