Generating Dynamic SQL I/O module (FSYSQLIO)

Before doing this task, make sure that you successfully activate the CAF interface as described in the previous section Call Attachment Facility (CAF) for Db2 users.

Tailor and submit JCASMSQL supplied JOB to assemble, translate and bind FSYSQLIO dynamic I/O interface module. Adjust BIND step and BIND parameters to your needs.

The source for FSYSQLIO is located in SYS1.SFSYEZTS. There are four parameters that you can change to accommodate your requirements:

NONCURS=64,

Maximum number of non-cursor requests

CURSORS=32,

Maximum number of non-scrollable cursor requests

ICURSOR=16,

Maximum number of insensitive scrollable cursors

PREPARE=NOREFRESH,

Where:

REFRESH: Refresh PREPARE statements on every cursor OPEN call.
NOREFRESH: Do not refresh PREPARE statements on every cursor OPEN.

SCURSOR=16;

Maximum number of sensitive scrollable cursors

The number of specified requests are for supporting a single Db2 program. Do not over-allocate. The module size should be kept to a reasonable size.

Bind a PLAN (or as many as you need) that you will be running your application programs with. You can tailor and run the JCPLNBND job to do so. Make sure that you specify (for the PKLIST) the same package name that was used to bind the FSYSQLIO module.

Grant proper authorization to bound plans (you can use SPUFI from an ISPF panel). For example, you can build and run the following request for PLAN IBMMIGUT and Collection BATCH:

GRANT EXECUTE ON PLAN IBMMIGUT TO PUBLIC;
GRANT PACKADM ON COLLECTION BATCH TO PUBLIC;
GRANT BINDADD TO PUBLIC;

Translate TESTCOL1 using the JCMUCL1P job. The TESTCOL1 program is set to run in Dynamic SQL mode.

The JCMUCL1P JOB is located in SYS1.SFSYJCLS. Uncomment TESTCOL1 at the bottom to translate TESTCOL1. Make sure that the correct DSNLOAD and DSNEXIT libraries are on the JOBLIB.

Make sure that you adjust SSID and PLAN on the PARM statement: PARM LINK(TESTCOL1) BIND(DYNAMIC) SSID(&SSID) PLAN(&PLAN)
Assuming that you are using SSID=DL11 and PLAN=IBMMIGUT, then the top of your TESTCOL1 program should look like this: PARM LINK (TESTCOL1) BIND(DYNAMIC) PLAN(IBMMIGUT) SSID(DL11)
After a clean compile, run JCRSQL1P located in SYS1.SFSYJCLS. Make sure that TESTCOL1 is specified on the EXEC statement.

Db2 libraries are not needed when your application program is running in Dynamic SQL mode. The program is treated as any other non-Db2 program. However, the translator does need Db2 DSNLOAD and/or DSNEXIT libraries to resolve the Db2 tables column definitions.