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.