Processing SQL statements by using the Db2 coprocessor

You can use the Db2 coprocessor for processing SQL statements at compile time. With the Db2 coprocessor, the compiler scans a program and copies all of the SQL statements and host variable information into a database request module (DBRM). The Db2 coprocessor is the recommended method for processing SQL statements in application programs. Compared to the Db2 precompiler, the Db2 coprocessor has fewer restrictions on SQL programs, and more fully supports the latest SQL and programming language enhancements.

About this task

The Db2 coprocessor processes SQL statements at compile time.

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

The Db2 coprocessor is the recommended method for processing SQL statements in application programs. Compared to the Db2 precompiler, the Db2 coprocessor has fewer restrictions on SQL programs, and more fully supports the latest SQL and programming language enhancements.

For example, 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, take 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, as shown in the following 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 Db2 modules 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 coprocessor. 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.

• Invoke the Db2 coprocessor from z/OS® UNIX System Services.
  If you invoke the Db2 coprocessor from z/OS 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, 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. For the list of options that you can specify, see SQL processing options.

  The file name for the DBRM is determined as described in DRBMLIB. For host languages other than C and C++, the DBRMLIB option is not supported and the file name is always generated. For C and C++, you can specify one of the following items:

  • The name of a partitioned data set. 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 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, must be unique within the file system.

    For example, 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

    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

  The following example invokes the Db2 coprocessor to compile a sample COBOL program with the Enterprise COBOL for z/OS 6.2 or later compilers:

  cob2 myprogram.cbl -c myprogram -dbrmlib -qsql

  The following example invokes the Db2 coprocessor to compile a sample PL/I program from Enterprise PL/I for z/OS 5.2 or later compilers:

  pli -c -qpp=sql -qdbrmlib -qrent myprogram.pli

  If you request that the DBRM be generated in an HFS file, you can bind the resulting DBRM by using the Db2 command line processor BIND command. For more information about using the Db2 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.