IBM Mainframe Forum

by **mahesh3603** » Mon Sep 27, 2010 8:14 pm

Hi ...

Can some one help in resolving the below issue.....

I am trying to update a DB2 table using a flat file.

The Db2 table has got 4 records....

Select all

CO_CD POL_SYM POL_NBR POL_MOD POL_EFF_DT ENTR_DT FEE_AMT FEE_PD_CD
----- ------- ------- ------- ---------- ---------- ------- ---------
83 A4I 5600867 02 05/20/2010 07/29/2010 5.00
83 A4I 5600867 02 05/20/2010 06/29/2010 5.00
83 A4I 5616843 02 05/18/2010 07/27/2010 5.00
83 A4I 5616843 02 05/18/2010 06/27/2010 5.00

out of 4 records...2 of them are duplicated...So i need to write a program to update a Db2 table with FEE_PD_CD
on a row level...

My input flat file data is given below:

83A4I56008670205/20/2010 00001556{W09/20/2010
83A4I56008670205/20/2010 00001606{P09/20/2010
83A4I56168430205/18/2010 00012920{W09/20/2010

I wanted to check the policy from flat file and check it in DB2 table and if SQL code is ZERO then update DB2 table with
corresponding flag value ---W/P value ...

I was expected a output is:

Select all

CO_CD POL_SYM POL_NBR POL_MOD POL_EFF_DT ENTR_DT FEE_AMT FEE_PD_C
----- ------- ------- ------- ---------- ---------- ------- --------
83 A4I 5600867 02 05/20/2010 07/29/2010 5.00 W
83 A4I 5600867 02 05/20/2010 06/29/2010 5.00 P
83 A4I 5616843 02 05/18/2010 07/27/2010 5.00 W
83 A4I 5616843 02 05/18/2010 06/27/2010 5.00

So I tried several times..but not able to get the expected output.

below is the complete source code and correct me where i am going wrong....to update on row level

Select all

*****************************************************************
* CURSOR DECLARATION
*****************************************************************
EXEC SQL
DECLARE T200KT-CSR CURSOR FOR
SELECT FEE_PD_CD, FEE_PD_DT
FROM T200KT_DRCT_BILL_SVC_CHRG
WHERE
CO_CD = :WS-POL-CO AND
POL_SYM = :WS-POL-SYM AND
POL_NBR = :WS-POL-NUM AND
POL_MOD = :WS-POL-MOD AND
POL_EFF_DT = :WS-POL-EFF-DT
FOR UPDATE OF FEE_PD_CD, FEE_PD_DT
END-EXEC.
*
0000-MAIN.

PERFORM 1000-INITIALIZE.

PERFORM 2000-PROCESS-INPUT THRU 2000-EXIT
UNTIL EOF-INPUT-FILE.

CLOSE INPUT-FILE.

STOP RUN.
1000-INITIALIZE.

OPEN INPUT INPUT-FILE.
READ INPUT-FILE INTO WS-INPUT-REC
AT END MOVE 'Y' TO EOF-INPUT-SW.

1300-OPEN-CURSOR.
EXEC SQL
OPEN T200KT-CSR
END-EXEC.
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
CONTINUE
WHEN OTHER
MOVE '1300-OPEN-CURSOR' TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.
*
****************************************************************
2000-PROCESS-INPUT.
****************************************************************
PERFORM 1300-OPEN-CURSOR.
PERFORM 2100-FETCH-CURSOR UNTIL WS-END-OF-CURSOR.
PERFORM 3500-CLOSE-CURSOR.
READ INPUT-FILE INTO WS-INPUT-REC
AT END MOVE 'Y' TO EOF-INPUT-SW.
2000-EXIT.
EXIT.
*
2100-FETCH-CURSOR.
**************************************************************
* FETCHES T200KT CURSOR
**************************************************************
INITIALIZE T200KT-TABLE-ORD-STR.
*
EXEC SQL
FETCH T200KT-CSR
INTO :T200KT-FEE-PD-CD, :T200KT-FEE-PD-DT
END-EXEC.
*
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
IF T200KT-FEE-PD-CD = SPACES
MOVE WS-POL-FEE-PD-CD TO T200KT-FEE-PD-CD
MOVE WS-POL-FEE_PD_DT TO T200KT-FEE-PD-DT
PERFORM 3200-UPDT-TABLE
END-IF
WHEN +100 ALSO ANY
SET WS-END-OF-CURSOR TO TRUE
WHEN OTHER
MOVE '2100-FETCH-CURSOR' TO WS-ABEND-PARAGRAPH
MOVE WS-POL-FEE-PD-CD
TO WS-ABEND-HOST-VAR1
PERFORM 9999-SQL-ABEND
END-EVALUATE
.

3200-UPDT-TABLE.

EXEC SQL
UPDATE T200KT_DRCT_BILL_SVC_CHRG
SET FEE_PD_CD = :T200KT-FEE-PD-CD,
FEE_PD_DT = :T200KT-FEE-PD-DT
WHERE CURRENT OF T200KT-CSR
END-EXEC.

EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN OTHER
MOVE '3200-UPDT-TABLE'
TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.

3500-CLOSE-CURSOR.

EXEC SQL
CLOSE T200KT-CSR
END-EXEC.
*
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
EXEC SQL
COMMIT
END-EXEC
WHEN OTHER
MOVE '3500-CLOSE-CURSOR' TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.

by **NicC** » Mon Sep 27, 2010 11:23 pm

Suggest you use code tags so that things line up. Also, you have not posted your file/working storage declarations. You say you were unable to get the expected output but what output did you get?

by **GuyC** » Tue Sep 28, 2010 1:36 pm

you can't use host-variables out of the file section.

by **GuyC** » Tue Sep 28, 2010 1:37 pm

forget my prev post, you are reading into WS

by **NicC** » Tue Sep 28, 2010 3:35 pm

Well, the file section and working storage are not shown so we do not know where the host variables are.

Also, redundant code: 2000-EXIT para.

by **mahesh3603** » Tue Sep 28, 2010 6:59 pm

Guys....Thank you so much for the quick updtes...I finally resolved the issue and completed the code & is given below.

Please review if this works...in all the scenario's........

Select all

INPUT-OUTPUT SECTION.
FILE-CONTROL.

SELECT INPUT-FILE ASSIGN TO UT-S-PMSFILE.

*
*
DATA DIVISION.
*
FILE SECTION.

FD INPUT-FILE
RECORDING MODE IS F
BLOCK CONTAINS 0 RECORDS
RECORD CONTAINS 54 CHARACTERS.

01 INPUT-REC.
05 POLICY.
10 POL-CO PIC X(02).
10 POL-SYM PIC X(03).
10 POL-NUM PIC X(07).
10 POL-MOD PIC X(02).
10 POL-EFF-DT PIC X(10).
10 POL-ENT-DT PIC X(10).
10 POL-FEE-AMT PIC X(09).
10 POL-FEE-PD-CD PIC X(01).
10 POL-FEE_PD_DT PIC X(10).
*
***************************************************************
* WORKING STORAGE
***************************************************************
WORKING-STORAGE SECTION.
*
01 WS-START PIC X(43) VALUE
'WORKING STORAGE FOR MHM6002 BEGINS HERE'.
*
01 WS-RECORD-COUNT PIC 9(05) VALUE 0.
01 WS-WORK-AREA.
05 WS-USER-RETURN-CODE PIC S9(04) COMP.
05 WS-END-CURSOR-SW PIC X.
88 WS-END-OF-CURSOR VALUE 'Y'.
88 WS-MORE-CURSOR-DATA VALUE 'N'.
*
01 LCP-ABND-CODE PIC S9(9) COMP VALUE +519.
01 LCP-LE-DUMP-CODE PIC S9(9) COMP VALUE +1.
*
01 EOF-INPUT-SW PIC X VALUE 'N'.
88 EOF-INPUT-FILE VALUE 'Y'.
*
01 WS-INPUT-REC.
10 WS-POL-CO PIC X(02).
10 WS-POL-SYM PIC X(03).
10 WS-POL-NUM PIC X(07).
10 WS-POL-MOD PIC X(02).
10 WS-POL-EFF-DT PIC X(10).
10 WS-POL-ENT-DT PIC X(10).
10 WS-POL-FEE-AMT PIC X(09).
10 WS-POL-FEE-PD-CD PIC X(01).
10 WS-POL-FEE_PD_DT PIC X(10).
*
01 HOLD-POL-NUM PIC X(07).

*
******************************************************************
* FIELDS USED FOR THE SQL ABEND ROUTINE *
******************************************************************
COPY UW4C999.
******************************************************************
* SQLCA REQUIRED FOR ALL DB2 PROGRAMS *
******************************************************************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******************************************************************
* DCLGEN FOR T200KT_DRCT_BILL_SVC_CHRG TABLE *
******************************************************************
EXEC SQL
INCLUDE T200KT
END-EXEC.
*
******************************************************************
* CURSOR DECLARATION *
******************************************************************
EXEC SQL
DECLARE T200KT-CSR CURSOR FOR
SELECT FEE_PD_CD, FEE_PD_DT
FROM T200KT_DRCT_BILL_SVC_CHRG
WHERE
CO_CD = :WS-POL-CO AND
POL_SYM = :WS-POL-SYM AND
POL_NBR = :WS-POL-NUM AND
POL_MOD = :WS-POL-MOD AND
POL_EFF_DT = :WS-POL-EFF-DT
FOR UPDATE OF FEE_PD_CD, FEE_PD_DT
END-EXEC.
*
01 WS-END PIC X(43) VALUE
'WORKING STORAGE FOR MHM6002 ENDS HERE'.
*
******************************************************************
* PROCEDURE DIVISION *
******************************************************************
PROCEDURE DIVISION.
*0000-MAIN.
*
PERFORM 1000-INITIALIZE.
*
PERFORM 2000-PROCESS-INPUT THRU 2000-EXIT
UNTIL EOF-INPUT-FILE.
*
CLOSE INPUT-FILE.
*
STOP RUN.
*
1000-INITIALIZE.
******************************************************************
* INITIALIZE MISCELLANEOUS VARIBLES, OPEN CURSOR, FIRST FETCH *
******************************************************************
*
OPEN INPUT INPUT-FILE.
*
READ INPUT-FILE INTO WS-INPUT-REC
AT END MOVE 'Y' TO EOF-INPUT-SW.

MOVE WS-POL-NUM TO HOLD-POL-NUM.

*
1300-OPEN-CURSOR.
******************************************************************
* OPENS T200KT CURSOR *
******************************************************************
*
EXEC SQL
OPEN T200KT-CSR
END-EXEC.
*
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
CONTINUE
WHEN OTHER
MOVE '1300-OPEN-CURSOR' TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.
*
******************************************************************
2000-PROCESS-INPUT.
******************************************************************
*
PERFORM 1300-OPEN-CURSOR.
*
PERFORM 2100-FETCH-CURSOR UNTIL
WS-POL-NUM NOT = HOLD-POL-NUM.
*
MOVE WS-POL-NUM TO HOLD-POL-NUM.
*
PERFORM 3500-CLOSE-CURSOR.
*
2000-EXIT.
EXIT.
*
*
2100-FETCH-CURSOR.
******************************************************************
* FETCHES T200KT CURSOR *
******************************************************************
*
INITIALIZE T200KT-TABLE-ORD-STR.
*
EXEC SQL
FETCH T200KT-CSR
INTO :T200KT-FEE-PD-CD, :T200KT-FEE-PD-DT
END-EXEC.
*
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
IF T200KT-FEE-PD-CD = SPACES
MOVE WS-POL-FEE-PD-CD TO T200KT-FEE-PD-CD
MOVE WS-POL-FEE_PD_DT TO T200KT-FEE-PD-DT
PERFORM 3200-UPDT-TABLE
READ INPUT-FILE INTO WS-INPUT-REC
AT END MOVE 'Y' TO EOF-INPUT-SW
END-READ
IF EOF-INPUT-FILE
PERFORM 3500-CLOSE-CURSOR
GO TO 2000-EXIT
END-IF
END-IF
WHEN +100 ALSO ANY
READ INPUT-FILE INTO WS-INPUT-REC
AT END MOVE 'Y' TO EOF-INPUT-SW
END-READ
IF EOF-INPUT-FILE
PERFORM 3500-CLOSE-CURSOR
GO TO 2000-EXIT
END-IF
MOVE '0000000' TO HOLD-POL-NUM
WHEN OTHER
MOVE '2100-FETCH-CURSOR' TO WS-ABEND-PARAGRAPH
MOVE WS-POL-FEE-PD-CD
TO WS-ABEND-HOST-VAR1
PERFORM 9999-SQL-ABEND
END-EVALUATE
.
*
******************************************************************
******************************************************************
** 3200-UPDT-TABLE. **
** **
** CALL TO UPDATE DB2 TABLE T200KT_DRCT_BILL_SVC_CHRG **
** **
******************************************************************
3200-UPDT-TABLE.

EXEC SQL
UPDATE T200KT_DRCT_BILL_SVC_CHRG
SET FEE_PD_CD = :T200KT-FEE-PD-CD,
FEE_PD_DT = :T200KT-FEE-PD-DT
WHERE CURRENT OF T200KT-CSR
END-EXEC.

EVALUATE SQLCODE
WHEN +0
CONTINUE
WHEN OTHER
MOVE '3200-UPDT-TABLE'
TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.

*
3500-CLOSE-CURSOR.
******************************************************************
* CLOSE PROCESS *
******************************************************************
*
EXEC SQL
CLOSE T200KT-CSR
END-EXEC.
*
EVALUATE SQLCODE ALSO SQLWARN0
WHEN +0 ALSO SPACES
EXEC SQL
COMMIT
END-EXEC
WHEN OTHER
MOVE '3500-CLOSE-CURSOR' TO WS-ABEND-PARAGRAPH
PERFORM 9999-SQL-ABEND
END-EVALUATE
.
*
******************************************************************
** 9999-SQL-ABEND. **
** **
** - SQL ABEND ROUTINE **
** **
******************************************************************
9999-SQL-ABEND.

DISPLAY 'INPUT RECORD = ' WS-RECORD-COUNT
MOVE SQLCODE TO WS-ABEND-SQLCODE
:
:

"Code'd"

out put:

Select all

CO_CD POL_SYM POL_NBR POL_MOD POL_EFF_DT ENTR_DT FEE_AMT FEE_PD_CD
----- ------- ------- ------- ---------- ---------- ------- ---------
********************************* TOP *******************************
83 A4I 5600867 02 05/20/2010 07/29/2010 5.00 P
83 A4I 5600867 02 05/20/2010 06/29/2010 5.00 W
83 A4I 5616843 02 05/18/2010 07/27/2010 5.00
83 A4I 5616843 02 05/18/2010 06/27/2010 5.00 W

"Code'd"

by **dick scherrer** » Wed Sep 29, 2010 1:13 am

Hello,

Please review if this works...in all the scenario's........

It is up to you to test "all the scenarios" - you have the environment in which it can be tested. . . If something does not work and you don not understand why, post the problem so we might be able to help.

When posting code, jcl, data, etc you need to use the "Code" tag to preserve alignment and improve readability. Use Preview to see your post as it will appear to the forum. Once you have it the way you want, Submit.

IBM Mainframe Forum

Update Db2 Table using flat file sequentially

Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially

Re: Update Db2 Table using flat file sequentially