IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 CURSOR
IBM Mainframe Forums -> DB2
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 12:12 am
 Reply with quote
Hi,

I am a DB2 newbie. Here is the situation. I have the following delcare cursor in my cobol program:
DECLARE FIP55 CURSOR FOR
SELECT DISTINCT FIP_STATE, FIP_CNTY, FIP_PLAC
FROM FIP55
GROUP BY FIP_STATAE, FIP_CNTY, FIP_PLAC

Procedure:
1 Open FIPP5

My question is for each record on my input file (which there is over a million) I want to lookup the FIP_STATE, FIP_CNTY AND FIP_PLAC. How would I set up a loop that will process each input record against the cursor wwithout re-openning the cursor and re-fetch? I guess using FETCH next, Fetch First would be correct. Could someone please show me how to re-use the cursor without having to reopen/refetch? Example would be great!! Thank you very much.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Tue Sep 29, 2009 12:49 am
 Reply with quote
Hello and welcome to the forum,

Why is this cursor being used in the first place?

Are FIP_STATE, FIP_CNTY, FIP_PLAC a key or the high-order part of some key?

Is the input file in FIP_STATE, FIP_CNTY, FIP_PLAC sequence? If not, why not?

If you explain the actual requirement (rather than some code meant to meet the requirement), someone may have more useful suggestions.
Back to top
View user's profile Send private message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 12:54 am
 Reply with quote
thanks for the welcome! bare with me a little brain dead here. what I am trying to do is validate fip_state, fip_cnty and fip_plac on a seq input file against a db2 table (which has the same elements). If there is a match write the seq record to a valid file and if no match write to error file.

For each of the fields on the input record do a lookup in db2 table. I want to try and limit trips to db2...basicially reuse the same cursor (or something better) using a scrollable cursor. open the cursor once..fetch..does that help??

thanks again
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
PostPosted: Tue Sep 29, 2009 12:56 am
 Reply with quote
Not really. Repeating what you said in your initial post doesn't add much to the clarification. Please answer the questions that Dick asked you.
Back to top
View user's profile Send private message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 12:57 am
 Reply with quote
here is the pgm
Code:
       IDENTIFICATION DIVISION.
       PROGRAM-ID. P6WJR011.
       AUTHOR. WILLIAM RADY.
      *****************************************************************
      * PROGRAM NAME: P6WJR011                                        *
      *---------------------------------------------------------------*
      *                                                               *
      *---------------------------------------------------------------*
      * 09/2009 WJR - PROGRAM WAS CREATED.                            *
      *****************************************************************
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
           SELECT FIP55-NATFEDCD-INPUT       ASSIGN TO FIP55NFC.
           SELECT FIP55-NATFEDCD-VALID       ASSIGN TO FIP55VAL.
           SELECT FIP55-NATFEDCD-ERROR       ASSIGN TO FIP55ERR.
           SELECT P6WJR010-CONTROL-DATA      ASSIGN TO P6WJR010.
           SELECT P6WJR011-CONTROL-DATA      ASSIGN TO P6WJR011.
           SELECT P6WJR11A-CONTROL-DATA      ASSIGN TO P6WJR11A.
       DATA DIVISION.
       FILE SECTION.
       FD FIP55-NATFEDCD-INPUT
           LABEL RECORD IS STANDARD
           RECORD CONTAINS 350 CHARACTERS
           RECORDING MODE IS F
           BLOCK CONTAINS 0 RECORDS
           DATA RECORD FIP55-NATFEDCD-RECORD.
       01 FIP55-NATFEDCD-RECORD                  PIC X(0350).
       FD FIP55-NATFEDCD-VALID
           LABEL RECORD IS STANDARD
           RECORD CONTAINS 229 CHARACTERS
           RECORDING MODE IS F
           BLOCK CONTAINS 0 RECORDS
           DATA RECORD FIP55-NATFEDCD-VAL-REC.
       01 FIP55-NATFEDCD-VAL-REC                 PIC X(0229).
       FD FIP55-NATFEDCD-ERROR
           LABEL RECORD IS STANDARD
           RECORD CONTAINS 380 CHARACTERS
           RECORDING MODE IS F
           BLOCK CONTAINS 0 RECORDS
           DATA RECORD FIP55-NATFEDCD-ERR-REC.
       01 FIP55-NATFEDCD-ERR-REC                 PIC X(0380).
       FD P6WJR010-CONTROL-DATA
           LABEL RECORD IS STANDARD
           RECORDING MODE IS F
           BLOCK  CONTAINS 0 RECORDS
           RECORD CONTAINS 26 CHARACTERS
           DATA RECORD IS P6WJR010-DATA-RECORD.
       01 P6WJR010-DATA-RECORD                  PIC X(0026).
       FD P6WJR011-CONTROL-DATA
           LABEL RECORD IS STANDARD
           RECORDING MODE IS F
           BLOCK  CONTAINS 0 RECORDS
           RECORD CONTAINS 26 CHARACTERS
           DATA RECORD IS P6WJR011-DATA-RECORD.
       01 P6WJR011-DATA-RECORD                  PIC X(0026).
       FD P6WJR11A-CONTROL-DATA
           LABEL RECORD IS STANDARD
           RECORDING MODE IS F
           BLOCK  CONTAINS 0 RECORDS
           RECORD CONTAINS 26 CHARACTERS
           DATA RECORD IS P6WJR11A-DATA-RECORD.
       01 P6WJR11A-DATA-RECORD                  PIC X(0026).
       WORKING-STORAGE SECTION.
      *********************************************************
      * COPYBOOK                                              *
      *********************************************************
       COPY C6WJR010.
       COPY C6WJR011.
       COPY C6WJR11A.
      *********************************************************
      * END OF COPYBOOK SECTION.                              *
      *********************************************************
      *    EXEC SQL
      *     DECLARE FIPS55 CURSOR WITH ROWSET POSITIONING FOR
      *        SELECT DISTINCT WJR00000_FIPS_STCD,
      *                        WJR00000_FIPS_CNTY,
      *                        WJR00000_FIPS_PLAC
      *         FROM WJR00000_FIPS55
      *          GROUP BY WJR00000_FIPS_STCD,
      *                   WJR00000_FIPS_CNTY,
      *                   WJR00000_FIPS_PLAC
      *    END-EXEC.
      *    EXEC SQL
      *     DECLARE FIPS551 CURSOR WITH ROWSET POSITIONING FOR
      *        SELECT DISTINCT WJR00000_FIPS_STCD,
      *               WJR00000_FIPS_CNTY
      *         FROM WJR00000_FIPS55
      *              GROUP BY WJR00000_FIPS_STCD,
      *                       WJR00000_FIPS_CNTY
      *    END-EXEC.
           EXEC SQL
            DECLARE FIPS552 SCROLL CURSOR FOR
               SELECT DISTINCT WJR00000_FIPS_STCD
                FROM WJR00000_FIPS55
                     GROUP BY WJR00000_FIPS_STCD
           END-EXEC.
       01  SQLCA.
           05  SQLCAID                  PIC  X(08).
           05  SQLCABC                  PIC S9(09) COMP-4.
           05  SQLCODE                  PIC S9(09) COMP-4.
           05  SQLERRM.
               49  SQLERRML             PIC S9(04) COMP-4.
               49  SQLERRMC             PIC  X(70).
           05  SQLERRP                  PIC  X(08).
           05  SQLERRD                  OCCURS 6 TIMES
                                        PIC S9(09) COMP-4.
           05  SQLERRM.
               10  SQLWARN0             PIC  X(01).
               10  SQLWARN1             PIC  X(01).
               10  SQLWARN2             PIC  X(01).
               10  SQLWARN3             PIC  X(01).
               10  SQLWARN4             PIC  X(01).
               10  SQLWARN5             PIC  X(01).
               10  SQLWARN6             PIC  X(01).
               10  SQLWARN7             PIC  X(01).
          05  SQLEXT.
               10  SQLWARN8             PIC  X(01).
               10  SQLWARN9             PIC  X(01).
               10  SQLWARNA             PIC  X(01).
               10  SQLSTATE             PIC  X(05).
       01 WS000-PROGRAM-SWITCHES.
          05  WS000-END-OF-FILE         PIC X(0001) VALUE 'N'.
          05  WS000-END-OF-FIP55        PIC X(0001) VALUE 'N'.
          05  WS000-END-OF-FIP551       PIC X(0001) VALUE 'N'.
          05  WS000-END-OF-FIP552       PIC X(0001) VALUE 'N'.
          05  WS000-ERROR-001           PIC X(0001) VALUE 'N'.
       01 WS001-PROGRAM-ACCUMULATORS.
          05 WS001-TOTAL-RECORDS-READ   PIC S9(009) COMP-3 VALUE +0.
          05 WS001-TOTAL-RECORDS-VALID  PIC S9(009) COMP-3 VALUE +0.
          05 WS001-TOTAL-RECORDS-ERROR  PIC S9(009) COMP-3 VALUE +0.
          05 WS001-ERROR-001            PIC S9(009) COMP-3 VALUE +0.
          05 WS001-ERROR-002            PIC S9(009) COMP-3 VALUE +0.
          05 WS001-ERROR-003            PIC S9(009) COMP-3 VALUE +0.
          05 WS001-ERROR-COUNT          PIC S9(002) COMP-3 VALUE +0.
       01 WS002-PROGRAM-DISPLAY-AREA.
          05 WS002-TOTAL-RECORDS-READ   PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-TOTAL-RECORDS-VALID  PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-TOTAL-RECORDS-ERROR  PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-ERROR-001            PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-ERROR-002            PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-ERROR-003            PIC -ZZZ,ZZZ,ZZ9.
          05 WS002-SQLERROR-CODE          PIC -ZZZ,ZZZ,ZZ9.
       01 WS003-P6WJR010-CONTROL-RECORD.
          05 WS003-JOBNAME                PIC X(008).
          05 WS003-BAL-IND                PIC X(001).
          05 WS003-RUNDATE                PIC X(012).
          05 WS003-JOB-TOTALS             PIC S9(009) COMP-3 VALUE +0.
       01 WS004-P6WJR011-CONTROL-RECORD.
          05 WS004-JOBNAME                PIC X(008) VALUE 'J6WJR011'.
          05 WS004-BAL-IND                PIC X(001).
          05 WS004-RUNDATE                PIC X(012).
          05 WS004-JOB-TOTALS             PIC S9(009) COMP-3 VALUE +0.
       01 WS005-FIPS55-RECORD.
          05  WS005-FIPS55-STCD           PIC X(002).
          05  WS005-FIPS55-CNTY           PIC X(003).
          05  WS005-FIPS55-PLAC           PIC X(005).
       01 WS006-FIPS551-RECORD.
          05  WS006-FIPS55-STCD           PIC X(002).
          05  WS006-FIPS55-CNTY           PIC X(003).
       01 WS007-FIPS552-RECORD.
          05  WS007-FIPS55-STCD           PIC X(002).
       PROCEDURE DIVISION.
       P0000-BEGIN-PROCESSING.
           PERFORM P1000-PROGRAM-INITIALIZATION THRU P1000-EXIT.
           PERFORM P2000-PROGRAM-PROCESS        THRU P2000-EXIT
                 UNTIL WS000-END-OF-FILE EQUAL 'Y'.
           PERFORM P3000-PROGRAM-TERMINATION    THRU P3000-EXIT.
           STOP RUN.
       P0000-EXIT.
           EXIT.
       P1000-PROGRAM-INITIALIZATION.
           PERFORM P1001-OPEN-FILE              THRU P1001-EXIT.
           PERFORM P1002-OPEN-FIP55             THRU P1002-EXIT.
           PERFORM P9000-READ-INPUT-FILE        THRU P9000-EXIT.
      *    PERFORM P9001-FETCH-FIP55            THRU P9001-EXIT.
      *    PERFORM P9002-FETCH-FIP551           THRU P9002-EXIT.
       P1000-EXIT.
           EXIT.
       P1001-OPEN-FILE.
           OPEN INPUT  FIP55-NATFEDCD-INPUT.
           OPEN INPUT  P6WJR010-CONTROL-DATA.
           OPEN OUTPUT FIP55-NATFEDCD-VALID.
           OPEN OUTPUT FIP55-NATFEDCD-ERROR.
           OPEN OUTPUT P6WJR011-CONTROL-DATA.
           OPEN OUTPUT P6WJR11A-CONTROL-DATA.
       P1001-EXIT.
           EXIT.
       P1002-OPEN-FIP55.
      *    EXEC SQL
      *       OPEN FIPS55
      *    END-EXEC.
      *    EXEC SQL
      *       OPEN FIPS551
      *    END-EXEC.
           EXEC SQL
              OPEN FIPS552
           END-EXEC.
       P1002-EXIT.
           EXIT.
       P2000-PROGRAM-PROCESS.
           ADD +1 TO WS001-TOTAL-RECORDS-READ.
           MOVE +0 TO WS001-ERROR-COUNT.
      ***************************************************************
      * THE FIRST SET OF EDITS TO BE PERFORMED ARE AGAINST THE      *
      * FIPS55 DB2 TABLE. THE FOLLOWING WILL BE THE RESULTING ERROR *
      * CODES:                                                      *
      * 001 - INVALID STATE CODE; INPUT C6WJR010-STATE-NUMERIC WAS  *
      *                           NOT FOUND IN THE FIPS55 TABLE.    *
      * 002 - INVALID COUNTY CODE; INPUT C6WJR010-COUNTY-NUMERIC WAS*
      *                            NOT FOUND IN THE FIPS55 TABLE.   *
      * 003 - INVALID FIPS55 PLACE; INPUT C6WJR010-CENSUS-CODE WAS  *
      *                             NOT FOUND IN THE FIPS55 TABLE.  *
      ***************************************************************
           PERFORM P2003-FIPS55-EDIT THRU P2003-EXIT.
           IF WS001-ERROR-COUNT EQUAL +0
              PERFORM P2001-WRITE-VALID-RECORD THRU P2001-EXIT
           ELSE
              PERFORM P2002-WRITE-ERROR-RECORD THRU P2002-EXIT.
           PERFORM P9000-READ-INPUT-FILE THRU P9000-EXIT.
       P2000-EXIT.
           EXIT.
       P2001-WRITE-VALID-RECORD.
           MOVE C6WJR010-FEATURE-ID TO C6WJR011-FEATURE-ID.
           MOVE C6WJR010-FEATURE-NAME TO C6WJR011-FEATURE-NAME.
           MOVE C6WJR010-FEATURE-CLASS TO C6WJR011-FEATURE-CLASS.
           MOVE C6WJR010-CENSUS-CODE TO C6WJR011-CENSUS-CODE.
           MOVE C6WJR010-CENSUS-CLASS-CODE TO
                C6WJR011-CENSUS-CLASS-CODE.
           MOVE C6WJR010-GSA-CODE TO C6WJR011-GSA-CODE.
           MOVE C6WJR010-OPM-CODE TO C6WJR011-OPM-CODE.
           MOVE C6WJR010-STATE-NUMERIC TO
                C6WJR011-STATE-NUMERIC.
           MOVE C6WJR010-COUNTY-SEQUENCE TO
                C6WJR011-COUNTY-SEQUENCE.
           MOVE C6WJR010-COUNTY-NUMERIC TO
                C6WJR011-COUNTY-NUMERIC.
           MOVE C6WJR010-PRIMARY-LAT TO
                C6WJR011-PRIMARY-LAT.
           MOVE C6WJR010-PRIMARY-LON TO
                C6WJR011-PRIMARY-LON.
           MOVE C6WJR010-DATE-CREATED TO
                C6WJR011-DATE-CREATED.
           MOVE C6WJR010-DATE-EDITED TO
                C6WJR011-DATE-EDITED.
           ADD +1 TO WS001-TOTAL-RECORDS-VALID.
           WRITE FIP55-NATFEDCD-VAL-REC FROM C6WJR011-RECORD.
       P2001-EXIT.
           EXIT.
       P2002-WRITE-ERROR-RECORD.
           MOVE C6WJR010-FEATURE-ID TO C6WJR11A-FEATURE-ID.
           MOVE C6WJR010-FEATURE-NAME TO C6WJR11A-FEATURE-NAME.
           MOVE C6WJR010-FEATURE-CLASS TO C6WJR11A-FEATURE-CLASS.
           MOVE C6WJR010-CENSUS-CODE TO C6WJR11A-CENSUS-CODE.
           MOVE C6WJR010-CENSUS-CLASS-CODE TO
                C6WJR11A-CENSUS-CLASS-CODE.
           MOVE C6WJR010-GSA-CODE TO C6WJR11A-GSA-CODE.
           MOVE C6WJR010-OPM-CODE TO C6WJR11A-OPM-CODE.
           MOVE C6WJR010-STATE-NUMERIC TO
                C6WJR11A-STATE-NUMERIC.
           MOVE C6WJR010-STATE-ALPHA TO
                C6WJR11A-STATE-ALPHA.
           MOVE C6WJR010-COUNTY-SEQUENCE TO
                C6WJR11A-COUNTY-SEQUENCE.
           MOVE C6WJR010-COUNTY-NUMERIC TO
                C6WJR11A-COUNTY-NUMERIC.
           MOVE C6WJR010-COUNTY-NAME TO
                C6WJR11A-COUNTY-NAME.
           MOVE C6WJR010-PRIMARY-LAT TO
                C6WJR11A-PRIMARY-LAT.
           MOVE C6WJR010-PRIMARY-LON TO
                C6WJR11A-PRIMARY-LON.
           MOVE C6WJR010-DATE-CREATED TO
                C6WJR11A-DATE-CREATED.
           MOVE C6WJR010-DATE-EDITED TO
                C6WJR11A-DATE-EDITED.
           ADD +1 TO WS001-TOTAL-RECORDS-ERROR.
           WRITE FIP55-NATFEDCD-ERR-REC FROM C6WJR11A-RECORD.
       P2002-EXIT.
           EXIT.
       P2003-FIPS55-EDIT.
      *EDIT 001
           MOVE 'N' TO WS000-END-OF-FIP552.
           MOVE 'N' TO WS000-ERROR-001.
           PERFORM P9003-FETCH-FIP552 THRU P9003-EXIT.
           PERFORM P2004-EDIT-001   THRU P2004-EXIT
             UNTIL WS000-END-OF-FIP552 EQUAL 'Y' OR
                   WS000-ERROR-001 EQUAL 'Y'.
           IF WS000-ERROR-001 EQUAL 'N'
              ADD +1 TO WS001-ERROR-COUNT
              MOVE '001' TO C6WJR11A-ERROR-CODE (WS001-ERROR-COUNT)
           ELSE
              NEXT SENTENCE.
      *EDIT 002
      *    IF WS001-ERROR-COUNT EQUAL +0
      *       PERFORM P9002-FETCH-FIP551 THRU P9002-EXIT
      *       IF WS000-END-OF-FIP55 EQUAL 'Y'
      *          ADD +1 TO WS001-ERROR-COUNT
      *          MOVE '002' TO C6WJR11A-ERROR-CODE (WS001-ERROR-COUNT)
      *       ELSE
      *          NEXT SENTENCE
      *    ELSE
      *       NEXT SENTENCE.
      *EDIT 003
      *    IF WS001-ERROR-COUNT EQUAL +0
      *       PERFORM P9001-FETCH-FIP55 THRU P9001-EXIT
      *       IF WS000-END-OF-FIP55 EQUAL 'Y'
      *          ADD +1 TO WS001-ERROR-COUNT
      *          MOVE '003' TO C6WJR11A-ERROR-CODE (WS001-ERROR-COUNT)
      *       ELSE
      *          NEXT SENTENCE
      *    ELSE
      *       NEXT SENTENCE.
      *    PERFORM P3004-CLOSE-FIP55 THRU P3004-EXIT.
       P2003-EXIT.
           EXIT.
       P2004-EDIT-001.
           IF C6WJR010-STATE-NUMERIC EQUAL WS007-FIPS55-STCD
              MOVE 'Y' TO WS000-ERROR-001
              GO TO P2004-EXIT.
           PERFORM P9003-FETCH-FIP552 THRU P9003-EXIT.
       P2004-EXIT.
           EXIT.
       P3000-PROGRAM-TERMINATION.
           PERFORM P3001-DISPLAY-CONTROLS  THRU P3001-EXIT.
           PERFORM P3002-WRITE-CONTROL     THRU P3002-EXIT.
           PERFORM P302A-WRITE-CONTROL     THRU P302A-EXIT.
           PERFORM P3003-CLOSE-FILES       THRU P3003-EXIT.
           PERFORM P3004-CLOSE-FIP55       THRU P3004-EXIT.
       P3000-EXIT.
           EXIT.
       P3001-DISPLAY-CONTROLS.
           MOVE WS001-TOTAL-RECORDS-READ TO WS002-TOTAL-RECORDS-READ.
           MOVE WS001-TOTAL-RECORDS-VALID TO
                WS002-TOTAL-RECORDS-VALID.
           MOVE WS001-TOTAL-RECORDS-ERROR TO
                WS002-TOTAL-RECORDS-ERROR.
           DISPLAY 'J6WJR011 CONTROL REPORT'.
           DISPLAY ' '.
           DISPLAY '    TOTAL RECORDS READ: ',
               WS002-TOTAL-RECORDS-READ.
           DISPLAY '   TOTAL RECORDS VALID: ',
               WS002-TOTAL-RECORDS-VALID.
           DISPLAY 'TOTAL RECORDS IN ERROR: ',
               WS002-TOTAL-RECORDS-ERROR.
           IF (WS001-TOTAL-RECORDS-VALID +
               WS001-TOTAL-RECORDS-ERROR) EQUAL
               WS001-TOTAL-RECORDS-READ
              DISPLAY 'JOB J6WJR011 BALANCED'
           ELSE
              MOVE '0' TO WS004-BAL-IND
              DISPLAY 'JOB J6WJR011 IS OUT OF BALANCE'.
           DISPLAY ' '.
           DISPLAY '********************************************'.
           DISPLAY '*          JOB TO JOB BALANCING            *'.
           DISPLAY '********************************************'.
           DISPLAY ' '.
           IF WS001-TOTAL-RECORDS-READ EQUAL
              WS003-JOB-TOTALS
              DISPLAY 'JOB J6WJR011 BALANCES WITH J6WJR010'
           ELSE
              DISPLAY 'JOB J6WJR011 IS OUT OF BALANCE WITH J6WJR010'
              MOVE '0' TO WS004-BAL-IND.
       P3001-EXIT.
           EXIT.
       P3002-WRITE-CONTROL.
           MOVE FUNCTION CURRENT-DATE TO WS004-RUNDATE.
           MOVE WS001-TOTAL-RECORDS-VALID TO WS004-JOB-TOTALS.
           WRITE P6WJR011-DATA-RECORD FROM
                 WS004-P6WJR011-CONTROL-RECORD.
       P3002-EXIT.
           EXIT.
       P302A-WRITE-CONTROL.
           MOVE FUNCTION CURRENT-DATE TO WS004-RUNDATE.
           MOVE WS001-TOTAL-RECORDS-ERROR TO WS004-JOB-TOTALS.
           WRITE P6WJR11A-DATA-RECORD FROM
                 WS004-P6WJR011-CONTROL-RECORD.
       P302A-EXIT.
           EXIT.
       P3003-CLOSE-FILES.
           CLOSE FIP55-NATFEDCD-INPUT.
           CLOSE FIP55-NATFEDCD-ERROR.
           CLOSE FIP55-NATFEDCD-VALID.
           CLOSE P6WJR010-CONTROL-DATA.
           CLOSE P6WJR011-CONTROL-DATA.
           CLOSE P6WJR11A-CONTROL-DATA.
       P3003-EXIT.
           EXIT.
       P3004-CLOSE-FIP55.
      *    EXEC SQL
      *       CLOSE FIPS55
      *    END-EXEC.
      *    EXEC SQL
      *       CLOSE FIPS551
      *    END-EXEC.
           EXEC SQL
              CLOSE FIPS552
           END-EXEC.
       P3004-EXIT.
           EXIT.
       P9000-READ-INPUT-FILE.
           READ FIP55-NATFEDCD-INPUT
              INTO C6WJR010-RECORD
                 AT END MOVE 'Y' TO WS000-END-OF-FILE.
       P9000-EXIT.
           EXIT.
       P9001-FETCH-FIP55.
           EXEC SQL
              FETCH FIPS55
                 INTO :WS005-FIPS55-STCD,
                      :WS005-FIPS55-CNTY,
                      :WS005-FIPS55-PLAC
             END-EXEC.
             IF SQLCODE EQUAL 0
                GO TO P9001-EXIT.
             IF SQLCODE EQUAL 100
                MOVE 'Y' TO WS000-END-OF-FIP55
                GO TO P9001-EXIT.
             DISPLAY 'SQL ERROR: ', SQLCODE.
             MOVE 'Y' TO WS000-END-OF-FIP55.
       P9001-EXIT.
           EXIT.
       P9002-FETCH-FIP551.
           EXEC SQL
              FETCH FIPS551
                 INTO :WS006-FIPS55-STCD,
                      :WS006-FIPS55-CNTY
             END-EXEC.
             IF SQLCODE EQUAL 0
                GO TO P9002-EXIT.
             IF SQLCODE EQUAL 100
                MOVE 'Y' TO WS000-END-OF-FIP55
                GO TO P9002-EXIT.
             DISPLAY 'SQL ERROR: ', SQLCODE.
             MOVE 'Y' TO WS000-END-OF-FIP55.
       P9002-EXIT.
           EXIT.
       P9003-FETCH-FIP552.
           EXEC SQL
              FETCH FIPS552
                 INTO :WS007-FIPS55-STCD
             END-EXEC.
             IF SQLCODE EQUAL 0
                GO TO P9003-EXIT.
             IF SQLCODE EQUAL 100
                MOVE 'Y' TO WS000-END-OF-FIP552
                GO TO P9003-EXIT.
             MOVE SQLCODE TO WS002-SQLERROR-CODE.
             DISPLAY 'SQL ERROR: ', WS002-SQLERROR-CODE.
             MOVE 'Y' TO WS000-END-OF-FIP552.
       P9003-EXIT.
           EXIT.

Code'd
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
PostPosted: Tue Sep 29, 2009 12:59 am
 Reply with quote
Also, please use BBCode (see FAQ) to make your code more readable.
Back to top
View user's profile Send private message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 1:07 am
 Reply with quote
sorry..

clarification;

for each state, cnty, plac code on an input record, i want to see if there is a match in our db2 table. if match valid otherwise error. what I want to avoid is having to do open cursor, fetch, close cursor for each input record. what i would like to do, is open cursor once..do many fetch (using a scrollable cursor, fetch first...fetch next) and close cursor once. not sure how to set the loop in cobol...does that help?
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
PostPosted: Tue Sep 29, 2009 1:11 am
 Reply with quote
What would help is your answering Dick's questions.
Back to top
View user's profile Send private message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 1:15 am
 Reply with quote
fip_state, fip_cnty, and fip_plac are keys. input is sorted by the key. i guess what i need help with is how do i set up a scrollable cursor?

delclare the cursor

open cursor

read input

fetch key again open cursor

if valid write to valid file otherwise error

read input
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
PostPosted: Tue Sep 29, 2009 1:26 am
 Reply with quote
Since your table and input file are in the same sequence, it seems that a simple match (similar to a 2-file match) would accomplish the purpose. No need to open the cursor more than once. Am I missing something?
Back to top
View user's profile Send private message
WJRADYJR

New User


Joined: 31 Jul 2009
Posts: 6
Location: SOUTH WINDSOR, CT
PostPosted: Tue Sep 29, 2009 1:35 am
 Reply with quote
thanks. i guess that i have been working on this too long. but, for future reference...how would i set up a scrollable cursor in db2/cobol with fetch looping.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Tue Sep 29, 2009 2:01 am
 Reply with quote
Hello,

Posting the entire program just takes up space. . . When code IS posted, it should be posted using the "Code" tag - which preserves alignment and improves readability. Suggest a bit of practice (using the Preview feature) will help.

Suggest that one requirement at a time be dealt with. . . Why the leap to a scrollable cursor? Someone else may, but i don't see how a scrollable cursor would help icon_confused.gif

How many unique values of fip_state, fip_cnty, and fip_plac exist in the existing table?

Having fip_state, fip_cnty, and fip_plac as separate keys does nothing to handle this requirement. If they were all one key or the high-order part of one key, this could help, but as 3 separate keys, not so much.

Something to consider would be to create an array (in the program) or a temporary db2 table with fip_state, fip_cnty, and fip_plac as the key and place an entry in the array/table for each unique value of fip_state, fip_cnty, and fip_plac. This way the current cursor would be processed once - before the input file is even used. . .
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89
PostPosted: Tue Sep 29, 2009 3:19 am
 Reply with quote
WJRADYJR wrote:
Hi,

I am a DB2 newbie. Here is the situation. I have the following delcare cursor in my cobol program:
DECLARE FIP55 CURSOR FOR
SELECT DISTINCT FIP_STATE, FIP_CNTY, FIP_PLAC
FROM FIP55
GROUP BY FIP_STATAE, FIP_CNTY, FIP_PLAC

Procedure:
1 Open FIPP5

My question is for each record on my input file (which there is over a million) I want to lookup the FIP_STATE, FIP_CNTY AND FIP_PLAC. How would I set up a loop that will process each input record against the cursor wwithout re-openning the cursor and re-fetch? I guess using FETCH next, Fetch First would be correct. Could someone please show me how to re-use the cursor without having to reopen/refetch? Example would be great!! Thank you very much.


1.You don't need to use DISTINCT and GROUP BY together in your CURSOR
2.You can use ORDER BY instead
3.You can use ROWSET and put your RS rows in the internal table for future use:
Code:
DECLARE FIP55 CURSOR WITH ROWSET POSITIONING FOR
   SELECT DISTINCT FIP_STATE, FIP_CNTY, FIP_PLAC
     FROM FIP55
   Order By  FIP_STATE, FIP_CNTY, FIP_PLAC
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Similar Topics
Topic Forum Replies
No new posts Is SQLCODE -811 possible while fetchi... DB2 1
No new posts Restart logic by using cursor name in... DB2 1
No new posts Seeking Resolution for SQKCODE -991 o... DB2 2
No new posts Multiple rows within Cursor when Coun... DB2 14
No new posts Dynamic cursor name in Cobol program COBOL Programming 1
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us