Processing SQL statements by using the DB2 coprocessor

As an alternative to the DB2® precompiler, you can use theDB2 coprocessor to process SQL statements. The DB2 coprocessor performs DB2 precompiler functions at compile time.

About this task

Exception: For PL/I, the DB2 coprocessor is called from the PL/I SQL preprocessor instead of the compiler.

The DB2 coprocessor has fewer restrictions on SQL programs than the DB2 precompiler. When you process SQL statements with the DB2 coprocessor, you can do the following things in your program:

Use fully qualified names for structured host variables.
Include SQL statements at any level of a nested program, instead of in only the top-level source file.(Although you can include SQL statements at any level of a nested program, you must compile the entire program as one unit.)
Use nested SQL INCLUDE statements.
For C or C++ programs only: Write applications with variable length format.
For C or C++ programs only: Use codepage-dependent characters, such as left and right brackets, without using tri-graph notation when the programs use different code pages.

To process SQL statements by using the DB2 coprocessor, perform one of the following actions:

Submit a JCL job to process that SQL statement. Include the following information:
- Specify the SQL compiler option when you compile your program:
  The SQL compiler option indicates that you want the compiler to invoke the DB2 coprocessor. Specify a list of SQL processing options in parentheses after the SQL keyword. Table 1 lists the options that you can specify.
  
  For COBOL and PL/I, enclose the list of SQL processing options in single or double quotation marks. For PL/I, separate options in the list by a comma, blank, or both.
  Examples:
  C/C++
```
SQL(APOSTSQL STDSQL(NO))
```
  COBOL
```
SQL("APOSTSQL STDSQL(NO)")
```
  PL/I
```
PP(SQL("APOSTSQL,STDSQL(NO)")
```
- For PL/I programs that use BIGINT or LOB data types, specify the following compiler options when you compile your program:
```
LIMITS(FIXEDBIN(63), FIXEDDEC(31))
```
- If needed, increase the user's region size so that it can accommodate more memory for the DB2 coprocessor.
- Include DD statements for the following data sets in the JCL for your compile step:
  - DB2 load library (prefix.SDSNLOAD)
    The DB2 coprocessor calls DB2modules to process the SQL statements. You therefore need to include the name of the DB2 load library data set in the STEPLIB concatenation for the compiler step.
  - DBRM library
    The DB2 coprocessor produces a DBRM. DBRMs and the DBRM library are described in Output from the DB2 precompiler. You need to include a DBRMLIB DD statement that specifies the DBRM library data set.
  - Library for SQL INCLUDE statements
    If your program contains SQL INCLUDE member-name statements that specify secondary input to the source program, you need to also specify the data set for member-name. Include the name of the data set that contains member-name in the SYSLIB concatenation for the compiler step.

For C/C++ only: Invoke the DB2 coprocessor from UNIX System Services on z/OS®. If you invoke the C/C++ DB2 coprocessor from UNIX System Services, you can choose to have the DBRM generated in a partitioned data set or an HFS file.

When you invoke the DB2 coprocessor, include the following information:

Specify the SQL compiler option.
The SQL compiler option indicates that you want the compiler to invoke the DB2 coprocessor. Specify a list of SQL processing options in parentheses after the SQL keyword. Table 1 lists the options that you can specify.

Specify a location for the DBRM as the parameter for the dbrmlib option. You can specify one of the following items:

The name of a partitioned data set
Example: The following example invokes the C/C++ DB2 coprocessor to compile (with the c89 compiler) a sample C program and requests that the resulting DBRM is stored in the test member of the userid.dbrmlib.data data set:
```
c89 -Wc,"sql,dbrmlib(//'userid.dbrmlib.data(test)'),langlvl(extended)" -c t.c
```

The name of an HFS file

The file name can be qualified, partially qualified, or unqualified. The file path can contain a maximum of 1024 characters, and the file name can contain a maximum of 255 characters. The first 8 characters of the file name, not including the file extension, should be unique within the file system.

Assume that your directory structure is /u/USR001/c/example and that your current working directory is /u/USR001/c. The following table shows examples of how to specify the HFS file names with the dbrmlib option and how the file names are resolved.

Table 1. How to specify HFS files to store DBRMs
If you specify...	The DBRM is generated in...
`dbrmlib(/u/USR001/sample.dbrm)`	`/u/USR001/sample.dbrm`
`dbrmlib(example/sample.dbrm)`	`/u/USR001/c/example/sample.dbrm`
`dbrmlib(../sample.dbrm)`	`/u/USR001/sample.dbrm`
`dbrmlib(sample.dbrm)`	`/u/USR001/c/sample.dbrm`

Example: The following example invokes the DB2 coprocessor to compile (with the c89 compiler) a sample C program and requests that the resulting DBRM is stored in the file test.dbrm in the tmp directory:

c89 -Wc,"sql,dbrmlib(/tmp/test.dbrm),langlvl(extended)" -c t.c

If you request that the DBRM be generated in an HFS file, you can bind the resulting DBRM by using the command line processor BIND command. For more information about using the command line processor BIND command, see Binding a DBRM that is in an HFS file to a package or collection. Optionally, you can also copy the DBRM into a partitioned data set member by using the oput and oget commands and then bind the DBRM by using conventional JCL.

Parent topic: Processing SQL statements