i am a mainframe newbie with a doubt.
i have a flat file with entries for credit card maintenance
the fields are a mixture of xs and 9s
my db2 columns are a mixture of int and char
Before inserting into the db2 tables i need to check whether the entry exists or not...to do that i have moved my fd int variables to s9(9) since my dclgen inclusion has made it mandatory.Anyhow,after that and i have moved that to a s9(9)comp sync variable ...my code is a s follows:-
IDENTIFICATION DIVISION.
PROGRAM-ID. CRCD.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INFILE.
SELECT OUTFILE ASSIGN TO OUTFILE.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 CUSTOMER-RECORD.
02 CUSTOMER-REC1.
05 CARD-NUMBER PIC 9(16).
05 FIRST-NAME PIC X(10).
05 LAST-NAME PIC X(20).
05 ACCOUNT-NUMBER PIC 9(10).
02 CUSTOMER-REC2.
05 ISSUE-DATE PIC X(10).
05 EXPIRY-DATE PIC X(10).
05 PHONE-NO PIC 9(10).
05 ADDR PIC X(20).
05 STATE PIC X(2).
05 DOB PIC X(10).
05 GENDER PIC X.
05 CARD-STATUS PIC X(2).
05 RENEWAL-REISSUEDATE PIC X(10). <---nullable column in table
05 LOST-CANCELDATE PIC X(10). <----nullable column
FD OUTFILE.
01 OUTREC PIC X(80).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE DCLCD1 END-EXEC.
EXEC SQL INCLUDE DCLMD END-EXEC.
01 WS-EOF PIC X(3).
01 WS-CD PIC S9(9) USAGE COMP.
01 WS-CD1 PIC 9(9).
01 WS-AC PIC S9(9) USAGE COMP.
01 WS-AC1 PIC 9(9).
01 WS-PN PIC S9(9) USAGE COMP.
01 WS-PN1 PIC 9(9).
01 SQL-CONSTANTS.
05 SQL-GOOD-RETURN PIC S9(4) COMP SYNC VALUE +0.
05 SQL-EOF PIC S9(4) COMP SYNC VALUE +100.
PROCEDURE DIVISION.
INITIALIZE HV1 HV2 HV3 HV4 HV5 HV6 HV7
HV8 HV9 HV10 HV11 HV12 HV13 HV14.
INITIALIZE HOV1 HOV2 HOV3 HOV4 HOV5 HOV6 HOV7 HOV8
HOV9 HOV10 HOV11 HOV12 HOV13 HOV14.
OPEN INPUT INFILE.
OPEN OUTPUT OUTFILE.
PERFORM PROCESS-PARA THRU PROCESS-EXIT
UNTIL WS-EOF = 'EOF'.
CLOSE INFILE
OUTFILE.
STOP RUN.
PROCESS-PARA.
READ INFILE AT END MOVE 'EOF' TO WS-EOF.
IF CARD-STATUS = 'N '
PERFORM 0001-PARA1 THRU 0001-PARA1-EXIT
END-IF.
PROCESS-EXIT.
EXIT.
0001-PARA1.
DISPLAY 'IN PARA1'.
MOVE CARD-NUMBER(1:5) TO WS-CD1.
MOVE WS-CD1 TO WS-CD.
MOVE ACCOUNT-NUMBER(1:6) TO WS-AC1.
MOVE WS-AC1 TO WS-AC.
DISPLAY WS-AC.
MOVE PHONE-NO(1:6) TO WS-PN1.
MOVE WS-PN1 TO WS-PN.
DISPLAY WS-PN.
EXEC SQL
EXEC SQL
SELECT CARDNUMBER
INTO:HV1 FROM CD1 WHERE CARDNUMBER =:WS-CD
END-EXEC.
EVALUATE SQLCODE
WHEN SQL-EOF
MOVE WS-CD TO HV1 HOV1
MOVE FIRST-NAME TO HV2 HOV2
MOVE LAST-NAME TO HV3 HOV3
MOVE WS-AC TO HV4 HOV4
MOVE ISSUE-DATE TO HV5 HOV5
MOVE EXPIRY-DATE TO HV6 HOV6
MOVE WS-PN TO HV7 HOV7
MOVE ADDR TO HV8 HOV8
MOVE STATE TO HV9 HOV9
MOVE DOB TO HV10 HOV10
MOVE GENDER TO HV11 HOV11
MOVE CARD-STATUS TO HV12 HOV12
MOVE RENEWAL-REISSUEDATE TO HV13 HOV13
MOVE LOST-CANCELDATE TO HV14 HOV14
EXEC SQL
INSERT INTO CD1
VALUES(:HV1,:HV2,:HV3,:HV4,:HV5,:HV6,:HV7,:HV8,:HV9,
:HV10,:HV11,:HV12,:HV13,:HV14)
END-EXEC
WHEN SQL-GOOD-RETURN
DISPLAY 'SQLCODE' SQLCODE
MOVE CUSTOMER-REC1 TO OUTREC
MOVE '-ADD FAILED ' TO OUTREC(57:24)
WRITE OUTREC.
0001-PARA1-EXIT.
EXIT.
PROGRAM-ID. CRCD.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT INFILE ASSIGN TO INFILE.
SELECT OUTFILE ASSIGN TO OUTFILE.
DATA DIVISION.
FILE SECTION.
FD INFILE.
01 CUSTOMER-RECORD.
02 CUSTOMER-REC1.
05 CARD-NUMBER PIC 9(16).
05 FIRST-NAME PIC X(10).
05 LAST-NAME PIC X(20).
05 ACCOUNT-NUMBER PIC 9(10).
02 CUSTOMER-REC2.
05 ISSUE-DATE PIC X(10).
05 EXPIRY-DATE PIC X(10).
05 PHONE-NO PIC 9(10).
05 ADDR PIC X(20).
05 STATE PIC X(2).
05 DOB PIC X(10).
05 GENDER PIC X.
05 CARD-STATUS PIC X(2).
05 RENEWAL-REISSUEDATE PIC X(10). <---nullable column in table
05 LOST-CANCELDATE PIC X(10). <----nullable column
FD OUTFILE.
01 OUTREC PIC X(80).
WORKING-STORAGE SECTION.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL INCLUDE DCLCD1 END-EXEC.
EXEC SQL INCLUDE DCLMD END-EXEC.
01 WS-EOF PIC X(3).
01 WS-CD PIC S9(9) USAGE COMP.
01 WS-CD1 PIC 9(9).
01 WS-AC PIC S9(9) USAGE COMP.
01 WS-AC1 PIC 9(9).
01 WS-PN PIC S9(9) USAGE COMP.
01 WS-PN1 PIC 9(9).
01 SQL-CONSTANTS.
05 SQL-GOOD-RETURN PIC S9(4) COMP SYNC VALUE +0.
05 SQL-EOF PIC S9(4) COMP SYNC VALUE +100.
PROCEDURE DIVISION.
INITIALIZE HV1 HV2 HV3 HV4 HV5 HV6 HV7
HV8 HV9 HV10 HV11 HV12 HV13 HV14.
INITIALIZE HOV1 HOV2 HOV3 HOV4 HOV5 HOV6 HOV7 HOV8
HOV9 HOV10 HOV11 HOV12 HOV13 HOV14.
OPEN INPUT INFILE.
OPEN OUTPUT OUTFILE.
PERFORM PROCESS-PARA THRU PROCESS-EXIT
UNTIL WS-EOF = 'EOF'.
CLOSE INFILE
OUTFILE.
STOP RUN.
PROCESS-PARA.
READ INFILE AT END MOVE 'EOF' TO WS-EOF.
IF CARD-STATUS = 'N '
PERFORM 0001-PARA1 THRU 0001-PARA1-EXIT
END-IF.
PROCESS-EXIT.
EXIT.
0001-PARA1.
DISPLAY 'IN PARA1'.
MOVE CARD-NUMBER(1:5) TO WS-CD1.
MOVE WS-CD1 TO WS-CD.
MOVE ACCOUNT-NUMBER(1:6) TO WS-AC1.
MOVE WS-AC1 TO WS-AC.
DISPLAY WS-AC.
MOVE PHONE-NO(1:6) TO WS-PN1.
MOVE WS-PN1 TO WS-PN.
DISPLAY WS-PN.
EXEC SQL
EXEC SQL
SELECT CARDNUMBER
INTO:HV1 FROM CD1 WHERE CARDNUMBER =:WS-CD
END-EXEC.
EVALUATE SQLCODE
WHEN SQL-EOF
MOVE WS-CD TO HV1 HOV1
MOVE FIRST-NAME TO HV2 HOV2
MOVE LAST-NAME TO HV3 HOV3
MOVE WS-AC TO HV4 HOV4
MOVE ISSUE-DATE TO HV5 HOV5
MOVE EXPIRY-DATE TO HV6 HOV6
MOVE WS-PN TO HV7 HOV7
MOVE ADDR TO HV8 HOV8
MOVE STATE TO HV9 HOV9
MOVE DOB TO HV10 HOV10
MOVE GENDER TO HV11 HOV11
MOVE CARD-STATUS TO HV12 HOV12
MOVE RENEWAL-REISSUEDATE TO HV13 HOV13
MOVE LOST-CANCELDATE TO HV14 HOV14
EXEC SQL
INSERT INTO CD1
VALUES(:HV1,:HV2,:HV3,:HV4,:HV5,:HV6,:HV7,:HV8,:HV9,
:HV10,:HV11,:HV12,:HV13,:HV14)
END-EXEC
WHEN SQL-GOOD-RETURN
DISPLAY 'SQLCODE' SQLCODE
MOVE CUSTOMER-REC1 TO OUTREC
MOVE '-ADD FAILED ' TO OUTREC(57:24)
WRITE OUTREC.
0001-PARA1-EXIT.
EXIT.
Everything except the insert is working.Please help me
Code'd
