IBM Mainframe Forum

Hi All,
I am new to this forum and this is my first posting.
As you all must be knowing that a single FETCH statement can be used to retrieve multiple rows of data from the result table of a query as a rowset in DB2 V8.
Fetch works with a host variable array in which each element of the array contains a value for the same column. Changes to allow host variable arrays have been made to COBOL.
Declare C1 CURSOR
with ROWSET POSITIONING FOR SELECT * from EMP

FETCH ROWSET STARTING AT ABSOLUTE 20 FROM C1 FOR 10 (or 20,50, 100...) ROWS
INTO ….

In the INTO; :hva1, :hva2, …..the first host variable array corresponds to the first column’s output, the second host variable array corresponds to the second column’s output, and so on.

Thanks in advance.
Basically I need to know how to define arrays in WS section and then use the Fetch statement in the program.

Hi Jack. We use the DV2 V8 Multi Row Fetch and Insert in our application, and works well. This is a good performance enhancer for an application if used correctly.

This is how we do it

WS Array definition

For fields that are optional, put a field for the Null Indicator (see FIELD4)
01 WS-ARRAY.
05 WS-FIELD1 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD2 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD3 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD4 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD4-NULL-IND PIC S9(4) COMP
OCCURS 100 TIMES.
ETC.....

The Cursor Definition

EXEC SQL
DECLARE FETCH-CURSOR CURSOR
WITH ROWSET POSITIONING
WITH HOLD FOR
SELECT FIELD1
,FIELD2
,FIELD3
,FIELD4
FROM DB2.TABLE
END-EXEC.

The FETCH

EXEC SQL
FETCH
NEXT ROWSET
FROM FETCH-CURSOR FOR 100 ROWS
INTO :WS-FIELD1
,:WS-FIELD2
,:WS-FIELD3
,:WS-FIELD4 :WS-FIELD4-NULL-IND
END-EXEC.

Hints and Tips
1) before you do a FETCH, Initialize the array to clear it down.
2) check the SQLCA field SQLERRD(3) after each FETCH. This field is populated with the number of rows actually returned. You may ask for 100, but there could only be 58 on the table.
3) if you populate the array on you first fetch (i.e. 100 rows) and then process those, the next FETCH may only retrieve 58 rows, if you don't initialize the array before each fetch the rows from the previous fetch will still be there, so you have the danger of processing rows twice. But if you initialize the array before each fetch, and process the number of rows in SQLERRD(3), you'll be OK.

Hope this helps.
Cheers
Steve

swd wrote:Hi Jack. We use the DV2 V8 Multi Row Fetch and Insert in our application, and works well. This is a good performance enhancer for an application if used correctly.

This is how we do it

WS Array definition

For fields that are optional, put a field for the Null Indicator (see FIELD4)
01 WS-ARRAY.
05 WS-FIELD1 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD2 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD3 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD4 PIC X(8) OCCURS 100 TIMES.
05 WS-FIELD4-NULL-IND PIC S9(4) COMP
OCCURS 100 TIMES.
ETC.....

The Cursor Definition

EXEC SQL
DECLARE FETCH-CURSOR CURSOR
WITH ROWSET POSITIONING
WITH HOLD FOR
SELECT FIELD1
,FIELD2
,FIELD3
,FIELD4
FROM DB2.TABLE
END-EXEC.

The FETCH

EXEC SQL
FETCH
NEXT ROWSET
FROM FETCH-CURSOR FOR 100 ROWS
INTO :WS-FIELD1
,:WS-FIELD2
,:WS-FIELD3
,:WS-FIELD4 :WS-FIELD4-NULL-IND
END-EXEC.

Hints and Tips
1) before you do a FETCH, Initialize the array to clear it down.
2) check the SQLCA field SQLERRD(3) after each FETCH. This field is populated with the number of rows actually returned. You may ask for 100, but there could only be 58 on the table.
3) if you populate the array on you first fetch (i.e. 100 rows) and then process those, the next FETCH may only retrieve 58 rows, if you don't initialize the array before each fetch the rows from the previous fetch will still be there, so you have the danger of processing rows twice. But if you initialize the array before each fetch, and process the number of rows in SQLERRD(3), you'll be OK.

Hope this helps.
Cheers
Steve

Thanks Steve!
Thanks for your well explained answer.
I could try it yesterday only due to my vacation.
I get a pre-compilation error due to a DB2-Date field.
In my 01 WS-ARRAY, there is a date field. I tried defining this field as Pic X(10) and Date but it gives error like HOST VARIABLE ARRAY "FETCH-DATE1" IS EITHER NOT DEFINED OR IS NOT USABLE
How can I get rid of this problem?

Hi Jack - Glad the answer was useful. A field definition in the array of PIC X(10) OCCURS yy should be fine. Can you please post the whole WS-ARRAY so I can have a look, and also the FETCH Statement.

Cheers
Steve

Thanks for your quick reply Steve!
Here is the piece of code :
Working Storage
01 WS-ACTLG-ARRAY.
05 MULTI-CASE-ACTLG-DT PICX(10) OCCURS 50 TIMES.
05 MULTI-CASE-ACTLG-TM PIC S9(8) USAGE COMP
OCCURS 50 TIMES.
05 MULTI-USERID PIC X(08) OCCURS 50 TIMES.
05 MULTI-CASE-ACTLG-TYP PIC X(01) OCCURS 50 TIMES.
05 MULTI-CASE-ACTLG-MSG1 PIC X(50) OCCURS 50 TIMES.
05 MULTI-CASE-ACTLG-MSG2 PIC X(50) OCCURS 50 TIMES.
05 MULTI-CASE-ACTLG-MSG3 PIC X(50) OCCURS 50 TIMES.

EXEC SQL
DECLARE ACTLG_CUR CURSOR
WITH ROWSET POSITIONING FOR
SELECT CASE_ACTLG_DT,
CASE_ACTLG_TM,
USERID,
CASE_ACTLG_TYP,
CASE_ACTLG_MSG1,
CASE_ACTLG_MSG2,
CASE_ACTLG_MSG3
FROM VCASE_ACTION
WHERE CASE_NUM = B-CASE-NUM
AND CASE_ACTLG_DT <= B-LOG-DATE
AND ( CASE_ACTLG_DT < B-LOG-DATE
OR
( CASE_ACTLG_DT = B-LOG-DATE
AND CASE_ACTLG_TM > B-LOG-TIME )
ORDER BY CASE_ACTLG_DT DESC ,
CASE_ACTLG_TM
FOR FETCH ONLY
WITH UR
END-EXEC.

The Fetch is:INITIALIZE WS-ACTLG-ARRAY.
EXEC SQL
FETCH NEXT ROWSET
FROM CASE_ACTLG_CUR FOR :WS-MULTFETCH ROWS
FROM CASE_ACTLG_CUR FOR 50 ROWS
INTO :MULTI-CASE-ACTLG-DT,
:MULTI-CASE-ACTLG-TM,
:MULTI-USERID,
:MULTI-CASE-ACTLG-TYP,
:MULTI-CASE-ACTLG-MSG1,
:MULTI-CASE-ACTLG-MSG2,
:MULTI-CASE-ACTLG-MSG3
END-EXEC.
EVALUATE TRUE
WHEN SQLCODE = 0
CONTINUE
WHEN SQLCODE = 100
MOVE 'N' TO WS-LOG-FOUND-SW
WHEN OTHER
DISPLAY SQLCODE
GO TO 9999-EXIT
END-EVALUATE.

As I mentioned earlier, I get error on date field. I thought it will go away after changing the copiler option NewFuction(yes), but not.

It wouldn't be anything simple like you don't have a space between the C and the X of PICX would it ? PICX(10)