What can cause a loop when I have many DB2 / SQL columns?

Products

• Vision:Report

Issue/Introduction

Description:

Looping can occur with large numbers of DB2 / SQL columns.

Solution:

Converting a database from VSAM to DB2 / SQL the application executed correctly up to 54 columns, but more than that caused the program to loop.

Allocate a work area sufficient to accommodate the longest SQL statement in your program.

This was resolved by increasing the values for the parameters SQLA1 through SQLA5 with the following values:

            SQLA1=8192,                                                       
            SQLA2=4096,                                                       
            SQLA3=1024,                                                       
            SQLA4=1024,                                                       
            SQLA5=8192,    

These values are recommended for DB2 / SQL jobs, but were mistakenly omitted.
The only risk is for your system to run out of space in DB2 / SQL jobs, but if this is the scenario of your database, it should be assumed your system is adequate.

This can be done either at the application level by adding them to the OPTION statement, or at the site level by amending the QJOPTION macro.

We apologize that these parameters are not shown in the r16.1 Installation Manual. This will be corrected when the doc is published for r17.

These are default values in the QJOPTION macro source:

             &SQLA1=0,               RAW EXEC SQL..END-SQL STMTS     *        
             &SQLA2=0,               COMPRSD EXEC SQL..END-SQL STMTS *        
             &SQLA3=0,               SUBSTITUTION VARIABLES          *        
             &SQLA4=0,               EXTENDED SUBSTITUTION VARIABLES *        
             &SQLA5=0,               ESQL DATA AREA                  *        

As suggested on page 1-16 of the r16.1 VISION: Report Installation Guide, copy QJOPTREC to MYOPTION and add these parameters to the copy:

             SQLA1=8192,            RAW EXEC SQL..END-SQL STMTS     *         
             SQLA2=4096,            COMPRSD EXEC SQL..END-SQL STMTS *         
             SQLA3=1024,            SUBSTITUTION VARIABLES          *         
             SQLA4=1024,            EXTENDED SUBSTITUTION VARIABLES *         
             SQLA5=8192,            ESQL DATA AREA                  * 

Use the CUSTMJCL job; to assemble and link edit, the QJOPTION block.
A JCL sample of this procedure is provided in the source sublibrary.

Environment

Release: VSNREP00100-16.1-VISION:Report
Component: