Natural – DB2 ROWSET POSITIONING

Its multiple Row processing in Natural-Db2 process which helps reduce cpu, increase throughput and save MIPS. This is in some way similar to Natural-Adabas MULTI-FETCH  parameter option where in we specify the multiple row selection when Natural issues the call to Adabas and the limitation of using 32K in view size or fetched record size.

Advantages of using the option

• Improved throughput through fewer database access calls and lower network operations
• Lower CPU consumption

So the Purpose of Rowset positioning is similar to MULTI-FETCH option in Natural-Adabas, a ROWSET POSITIONING offers to read multiple records with a single database call. At statement execution time, the runtime checks if a multi-fetch-factor greater than 1 is supplied for the database statement and the database call is prepared dynamically to read multiple records with a single database access into the buffer.

Prerequisite

• The option will work only for DB2 version 8 and above only.
• Set the compiler option DB2ARRY=ON (through inline program statement coded after END-DEFINE statement or through NOCOPT command in the Natural editor
• Specify a list of receiving array fields in the INTO clause of the SELECT query. Please note for ROWSET option to work, the length of receiving fields should be same as length of the fields in DB2 table otherwise the process will fall over at the time of execution.
• Specify a variable receiving the number of rows retrieved from the database via the ROWS_RETURNED Clause. Variable has to be defined as I4.
• Specify the number of rows to be retrieved from the database by a single FETCH operation via the WITH ROWSET POSITIONING Clause with a value between 0 & 32767. Variable has to be defined as I4.

Looking at a sample program below:

DEFINE DATA LOCAL                                                  01 NAME            (A20/1:10)                                      01 ADDRESS         (A100/1:10)                                     01 DATEOFBIRTH     (A10/1:10)                                       01 SALARY          (P4.2/1:10)                                     01 L§ADDRESS       (I2/1:10)                                       01 ROWS            (I4)                                            01 NUMBER          (I4)                                            01 INDEX           (I4)                                            END-DEFINE                                                         OPTIONS DB2ARRY=ON                                                 ASSIGN NUMBER := 10                                                SEL.                                                               SELECT NAME, ADDRESS , DATEOFBIRTH, SALARY                                INTO  :NAME(*),                             /* <-- ARRAY                 :ADDRESS(*) LINDICATOR :L§ADDRESS(*), /* <-- ARRAY                 :DATEOFBIRTH(1:10),                   /* <-- ARRAY                 :SALARY(01:10)                        /* <-- ARRAY          FROM EMPLOYEE       WHERE NAME > ' '                                                   WITH ROWSET POSITIONING FOR :NUMBER ROWS     /* <-- ROWS REQ       ROWS_RETURNED :ROWS                          /* <-- ROWS RET   IF ROWS > 0                                                           FOR INDEX = 1 TO ROWS                                        DISPLAY                                                                    INDEX (EM=99) *COUNTER (SEL.) (EM=99) ROWS (EM=99)                 NAME(INDEX)                                                        ADDRESS(INDEX) (AL=20)                                             DATEOFBIRTH(INDEX)                                                 SALARY(INDEX)                                             END-FOR                                                          END-IF                                                           END-SELECT                                                         END

What the above program accomplished is reading 10 rows of data by a single call to EMPLOYEE table. The local field NUMBER helped in telling DB2 to fetch 10 rows satisfying the criteria in the SQL statement. The number of records returned by DB2 is saved in ROWS field.