COBOL Bind Step Loops

Burgess Evans

COBOL Bind Step Loops
Hi all,

OS – Z/OS
DB2 UDB V7.1 for Z/OS

I have a problem with a COBOL program. The bind step loops. There is
only one cursor defined in the program. I have verified that the host
variables used in the cursor have the proper attributes for the columns
they will be compared to; char (2) and char (3). The problem appears to
be related to the two “between” statements in the cursor. If I
remove one or the other the program binds. If I leave one “between”
statement and replace the other with >= <= then the program binds. I
have included the working storage for the host variables and a portion
of the cursor.

01 SYSIN-PARAMETER-DATA.
05 SYSIN-REGION-BEGIN PIC X(02) VALUE ZEROES.
05 SYSIN-REGION-END PIC X(02) VALUE ZEROES.
05 SYSIN-TYPE-ONE PIC X(03) VALUE ZEROES.
05 SYSIN-TYPE-TWO PIC X(03) VALUE ZEROES.

FROM AOV.ENTITY_RTNTN_DATE A,
AOS.COUNTY_TABLE B,

AOS.REGION C

WHERE A.COUNTY_NAME = B.COUNTY_NAME

AND B.COUNTY_NUMBER > '00'

AND B.REGION_NUMBER = C.REGION_NUMBER

AND ENTITY_TYPE BETWEEN

:SYSIN-TYPE-ONE AND :SYSIN-TYPE-TWO

AND B.REGION_NUMBER BETWEEN

:SYSIN-REGION-BEGIN AND :SYSIN-REGION-END

ORDER BY B.REGION_NUMBER, A.COUNTY_NAME, ENTITY_NAME



END-EXEC.



I can not find anything on IBMLINK or book manager regarding this
issue. If anyone has any ideas regarding a possible solution I would
appreciate hearing them.

Thank you,
Burgess Evans



[login to unmask email]

Re: COBOL Bind Step Loops
(in response to Burgess Evans)
need to see the other paragraphs in regard to this paragraph


|---------+----------------------------------->
| | Burgess Evans |
| | <[login to unmask email]|
| | E.OH.US> |
| | Sent by: DB2 Data |
| | Base Discussion List |
| | <[login to unmask email]|
| | OC.COM> |
| | |
| | |
| | 12/09/2002 12:22 |
| | Please respond to DB2|
| | Data Base Discussion |
| | List |
| | |
|---------+----------------------------------->
>-------------------------------------------------------------------------------------------------------|
| |
| To: [login to unmask email] |
| cc: |
| Subject: COBOL Bind Step Loops |
>-------------------------------------------------------------------------------------------------------|




Hi all,

OS ? Z/OS
DB2 UDB V7.1 for Z/OS

I have a problem with a COBOL program. The bind step loops. There is
only one cursor defined in the program. I have verified that the host
variables used in the cursor have the proper attributes for the columns
they will be compared to; char (2) and char (3). The problem appears to
be related to the two "between" statements in the cursor. If I
remove one or the other the program binds. If I leave one "between"
statement and replace the other with >= <= then the program binds. I
have included the working storage for the host variables and a portion
of the cursor.

01 SYSIN-PARAMETER-DATA.
05 SYSIN-REGION-BEGIN PIC X(02) VALUE ZEROES.
05 SYSIN-REGION-END PIC X(02) VALUE ZEROES.
05 SYSIN-TYPE-ONE PIC X(03) VALUE ZEROES.
05 SYSIN-TYPE-TWO PIC X(03) VALUE ZEROES.

FROM AOV.ENTITY_RTNTN_DATE A,
AOS.COUNTY_TABLE B,

AOS.REGION C

WHERE A.COUNTY_NAME = B.COUNTY_NAME

AND B.COUNTY_NUMBER > '00'

AND B.REGION_NUMBER = C.REGION_NUMBER

AND ENTITY_TYPE BETWEEN

:SYSIN-TYPE-ONE AND :SYSIN-TYPE-TWO

AND B.REGION_NUMBER BETWEEN

:SYSIN-REGION-BEGIN AND :SYSIN-REGION-END

ORDER BY B.REGION_NUMBER, A.COUNTY_NAME, ENTITY_NAME



END-EXEC.



I can not find anything on IBMLINK or book manager regarding this
issue. If anyone has any ideas regarding a possible solution I would
appreciate hearing them.

Thank you,
Burgess Evans


visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can





Burgess Evans

Re: COBOL Bind Step Loops
(in response to ddlusk@HOUSEHOLD.COM)
Here are the other paragraphs related to the cursor:

AOV.ENTITY_RTNTN_DATE TABLE
COUNTY_NAME CHAR(25) NOT NULL,
ENTITY_NAME CHAR(40) NOT NULL,
ENTITY_TYPE CHAR(3) NOT NULL,
RETENTN_FORM_RCVDT DATE NOT NULL,
ORGANIZATION_ID DECIMAL(7, 0) NOT NULL

AOS.COUNTY_TABLE TABLE
COUNTY_NUMBER CHAR(2) NOT NULL,
COUNTY_NAME CHAR(10) NOT NULL,
REGION_NUMBER CHAR(2) NOT NULL,
FED_COUNTY_NMBR CHAR(3) NOT NULL,
AOS_DATE_STAMP TIMESTAMP NOT NULL,
UPDATE_ID CHAR(8) NOT NULL

AOS.REGION TABLE
REGION_NUMBER CHAR(2) NOT NULL,
REGION_NAME CHAR(45) NOT NULL,
AOS_DATE_STAMP TIMESTAMP NOT NULL,
UPDATE_ID CHAR(8) NOT NULL

MOVE '000' TO SYSIN-TYPE-ONE
MOVE '000' TO SYSIN-TYPE-TWO
MOVE '00' TO SYSIN-REGION-BEGIN
MOVE '00' TO SYSIN-REGION-END

PERFORM 2500-PROCESS-ALL-ENTITY
THRU 2500-EXIT

2500-PROCESS-ALL-ENTITY.
MOVE '100' TO SYSIN-TYPE-ONE
MOVE '499' TO SYSIN-TYPE-TWO

PERFORM 7100-OPEN-ENDTLCSR
THRU 7100-EXIT

PERFORM 3000-ENTITY-DETAIL
THRU 3000-EXIT
UNTIL WS-EOF-DETAIL-CRSR = 'Y'

EXEC SQL
CLOSE ENDTLCSR
END-EXEC

MOVE SPACES TO WS-EOF-DETAIL-CRSR

MOVE '600' TO SYSIN-TYPE-ONE
MOVE '699' TO SYSIN-TYPE-TWO

PERFORM 7100-OPEN-ENDTLCSR
THRU 7100-EXIT

PERFORM 3000-ENTITY-DETAIL
THRU 3000-EXIT
EXEC SQL
CLOSE ENDTLCSR
END-EXEC

MOVE SPACES TO WS-EOF-DETAIL-CRSR
2500-EXIT. EXIT.

3000-ENTITY-DETAIL.
EXEC SQL
FETCH ENDTLCSR
INTO :ENTITY-NAME,
:DCLCOUNTY-TABLE.COUNTY-NAME,
:RETENTN-FORM-RCVDT:WS-NULLS,
:DCLCOUNTY-TABLE.REGION-NUMBER,
:REGION-NAME
END-EXEC

IF WS-NULLS < 0
MOVE SPACES TO RETENTN-FORM-RCVDT
END-IF

IF SQLCODE < +0
MOVE ' ENDTLCSR IN ERROR' TO
P7000101-ERROR-SELECT
PERFORM 9900-ABEND-DB2
END-IF

IF SQLCODE = +100
MOVE 'Y' TO WS-EOF-DETAIL-CRSR
EXEC SQL
CLOSE ENDTLCSR
END-EXEC
GO TO 3000-EXIT
END-IF

IF WS-REGION-NAME-HOLD NOT = REGION-NAME
MOVE REGION-NAME TO WS-REGION-NAME-HOLD
MOVE 0 TO P7000101-PAGE-COUNT
PERFORM 8000-WRITE-P7000101-HEADINGS
THRU 8000-EXIT

END-IF

PERFORM 8250-PRINT-DETAIL
THRU 8250-EXIT

CONTINUE.
3000-EXIT. EXIT.

7100-OPEN-ENDTLCSR.
**************************************************
EXEC SQL
OPEN ENDTLCSR
END-EXEC

IF SQLCODE < +0
OR SQLCODE > +100
MOVE ' ENDTLCSR IN ERROR' TO
P7000101-ERROR-SELECT
PERFORM 9900-ABEND-DB2
END-IF

CONTINUE.
7100-EXIT. EXIT.

Burgess

>>> [login to unmask email] 12/09/02 01:35PM >>>
need to see the other paragraphs in regard to this paragraph


|---------+----------------------------------->
| | Burgess Evans |
| | <[login to unmask email]|
| | E.OH.US> |
| | Sent by: DB2 Data |
| | Base Discussion List |
| | <[login to unmask email]|
| | OC.COM> |
| | |
| | |
| | 12/09/2002 12:22 |
| | Please respond to DB2|
| | Data Base Discussion |
| | List |
| | |
|---------+----------------------------------->

>-------------------------------------------------------------------------------------------------------|
|
|
| To: [login to unmask email]
|
| cc:
|
| Subject: COBOL Bind Step Loops
|

>-------------------------------------------------------------------------------------------------------|




Hi all,

OS ? Z/OS
DB2 UDB V7.1 for Z/OS

I have a problem with a COBOL program. The bind step loops. There is
only one cursor defined in the program. I have verified that the host
variables used in the cursor have the proper attributes for the
columns
they will be compared to; char (2) and char (3). The problem appears
to
be related to the two "between" statements in the cursor. If I
remove one or the other the program binds. If I leave one "between"
statement and replace the other with >= <= then the program binds. I
have included the working storage for the host variables and a portion
of the cursor.

01 SYSIN-PARAMETER-DATA.
05 SYSIN-REGION-BEGIN PIC X(02) VALUE ZEROES.
05 SYSIN-REGION-END PIC X(02) VALUE ZEROES.
05 SYSIN-TYPE-ONE PIC X(03) VALUE ZEROES.
05 SYSIN-TYPE-TWO PIC X(03) VALUE ZEROES.

FROM AOV.ENTITY_RTNTN_DATE A,
AOS.COUNTY_TABLE B,

AOS.REGION C

WHERE A.COUNTY_NAME = B.COUNTY_NAME

AND B.COUNTY_NUMBER > '00'

AND B.REGION_NUMBER = C.REGION_NUMBER

AND ENTITY_TYPE BETWEEN

:SYSIN-TYPE-ONE AND :SYSIN-TYPE-TWO

AND B.REGION_NUMBER BETWEEN

:SYSIN-REGION-BEGIN AND :SYSIN-REGION-END

ORDER BY B.REGION_NUMBER, A.COUNTY_NAME, ENTITY_NAME



END-EXEC.



I can not find anything on IBMLINK or book manager regarding this
issue. If anyone has any ideas regarding a possible solution I would
appreciate hearing them.

Thank you,
Burgess Evans



visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the
list
can



=======================To change your
subscription options or to cancel your subscription visit the DB2-L
webpage at http://listserv.ylassoc.com. The owners of the list can be




Walter Jani&#223;en

Re: COBOL Bind Step Loops
(in response to Burgess Evans)
Hi

If you figured it out, which SQL-statement is the culprit, can you explain
that statement. We recently had a problem, that an explain looped. Applying
the latest maintenance, this problem was gone.



Burgess Evans

Re: COBOL Bind Step Loops
(in response to Walter Janißen)
The program only has one cursor and the program has explain(yes) in the
bind parameters. I took your suggestion and pulled the SQL out.
substituted "?" for host variables and did an explain on the SQL.

I would say it is in a loop.

I will check for the corrective PTF and see what happens.

Thank you for the suggestion.

Burge

>>> [login to unmask email] 12/11/02 09:08AM >>>
Hi

If you figured it out, which SQL-statement is the culprit, can you
explain
that statement. We recently had a problem, that an explain looped.
Applying
the latest maintenance, this problem was gone.



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can



Burgess Evans

Re: COBOL Bind Step Loops
(in response to Burgess Evans)
Hi Walter,

I was wondering if you knew which PTF fixed your problem.

Thank you,
Burge

>>> [login to unmask email] 12/11/02 09:08AM >>>
Hi

If you figured it out, which SQL-statement is the culprit, can you
explain
that statement. We recently had a problem, that an explain looped.
Applying
the latest maintenance, this problem was gone.



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can