Getting started with embedded SQL for XL C/C++ on IBM z/OS Systems

This guide will help you learn how to run embedded SQL XL C/C++ applications on a z/OS platform. It describes the interfaces between IBM XL C/C++ and IBM DB2 along with their advantages and disadvantages. This article is written for developers with a basic understanding of z/OS and SQL. The steps in this article are tested with DB2 V9 to V10 and XL C/C++ z/OS V1R13 to V2R1.

Share:

Francesco Cassullo (cassullo@ca.ibm.com), Software Engineer , IBM

Author photoFrancesco Cassullo is a software developer in the IBM XL Compilers group. He has been at IBM since 2008 and has worked on Fortran, C/C++, and COBOL compilers.



Igor Todorovski (itodorov@ca.ibm.com), Software Developer, IBM

Author1 photoIgor Todorovski is a software developer in the IBM XL Compilers group. He has been with IBM since 2008 and specializes in IBM z/OS C/C++ compilers.



17 December 2013

Building an application

There are two methods for compiling a XL C/C++ application that contains embedded SQL statements on IBM z/OS systems, the DB2 XL C/C++ precompiler and the XL C/C++ DB2 coprocessor.

Precompiler

The precompiler performs syntax checking, parsing, and translation on all SQL statements and host variables within the source. As the name suggests this step precedes compilation.

If successful it produces two outputs, a database request module (DBRM) and a modified source file. In other words, if the XL C/C++ source does not contain any SQL statements the DBRM file will be empty. The DBRM is a dataset or Hierarchical File System (HFS) file which stores details on host variables and SQL statements used within the application. The modified source is a copy of the original source with all of the SQL statements replaced by calls to the DB2 XL C/C++ language interface modules.

It is recommended that you choose the coprocessor in order to exploit new compiler features in SQL statements and host variables

The modified source must be passed to a XL C/C++ compiler. The DBRM must go through a DB2 bind process to connect the application to the database, so that the database requests can be processed at runtime. See the Post-compilation section for a description on how to build the load module.

Figure 1. Steps to build an application with the Precompiler
Precompiler generates modified source and DBRM

The advantage of the DB2 precompiler is that the syntax checking of the SQL and XL C/C++ occurs in separate steps. This gives you more accurate reporting of SQL error messages.

The disadvantages of the DB2 precompiler are:

  • New compiler features are not supported in SQL statements and host variables.
  • The precompiler is not supported in Unix System Services (USS).

Coprocessor

The DB2 coprocessor performs SQL syntax checking, parsing, and code generation during compile time. Essentially, it runs the precompiler during compilation. This makes it appear as though syntax checking of SQL and XL C/C++ happens simultaneously. The SQL compiler option is required to use this approach. Upon successful compilation an object module and DBRM are generated. The coprocessor generates the DBRM with similar contents and purpose as the precompiler. It is recommended that you choose the coprocessor in order to exploit new compiler features in SQL statements and host variables. The coprocessor expands all preprocessor directives prior to syntax checking. This is also accomplished in two steps by, generating a copy of the source with expanded preprocessor statements and compiling this copy.

But compiling preprocessed output of an SQL application will fail if the "NULL" keyword is present in any SQL statement. The preprocessor confuses the SQL "NULL" keyword with the XL C/C++ "NULL" keyword and performs a macro expansion which is no longer valid SQL. Preprocessed output is generated by compiling any source with the PPONLY compiler option.

Figure 2. Steps to build an application with the Coprocessor
Coprocessor generates an object module and DBRM

Note: In order to use the coprocessor, a minimum of DB2 V7 and XL C/C++ z/OS V1R9 is required.

The advantages of the DB2 coprocessor are:

  • Permits a more seamless integration between XL C/C++ and DB2.
  • Host variables obey XL C/C++ language scope rules.
  • Source program can have a variable record format with a record length larger than 80 bytes.
  • Nested SQL INCLUDE statements are supported.
  • Codepage dependent characters such as '[' and ']' are supported without the need for tri-graphs.
  • Decimal Floating Point host variables are supported.
  • SQLSTATE, SQLCODE, SQL, and sqlca are recognized as embedded SQL keywords, when SQLCA is included, and cannot be used as host variable names.
  • New compiler features are not supported in SQL statements or host variables.
  • Supported in batch and USS modes. However, to run the application a DBRM PDS member is required by the DB2 DSN commands.

Database request module (DBRM)

The Database request module (DBRM) is a dataset or Hierarchical File System (HFS) file that contains details on host variables and SQL statements. When bound to a load module it allows the application to connect and interact with a DB2 database.

There are two techniques to specify a DBRM

  • The DBRMLIB compiler option
  • The DBRMLIB DD statement

The compiler option is only applicable to the coprocessor. The DBRMLIB DD statement is accepted by the precompiler and the coprocessor. The option and DD statement accept a dataset or HFS file as input. However, the DBRM must be a Partitioned dataset (PDS) member with Fixed Block (FB) RECFM and 80-byte LRECL to bind with a load module.

If neither the DD statement nor the option is specified, the compiler creates a DBRM based on the name of the source. For example, if the source PDS is DB2RUNU.SQLEXT.SOURCE(A), the generated DBRM will be DB2RUNU.SQLEXT.SOURCE.DBRM(A). If instead, the source is an HFS file named sample.c, the DBRM with be named sample.dbrm.

Table 1. Examples for assigning a DBRM
Mode DBRMLIB reference
Batch mode ddname //DBRMLIB DD DSN=DB2RUNU.SQL.DBRMLIB(A),DISP=SHR
Batch mode option DBRMLIB('DB2RUNU.SQL.DBRMLIB(A)')
USS mode option for a dataset -qDBRMLIB=//'DB2RUNU.SQL.DBRMLIB(A)'
USS mode option for a HFS file -qDBRMLIB=/home/db2runu/a.dbrm

The LIST compiler option can help you identify the name of the DBRM dataset or HFS file.


SQL option

Using the SQL compiler option automatically enables the coprocessor. This option is disabled by default.

Table 2. Examples for specifying the SQL option
precompilercoprocessor
Batch ModeNOSQL or defaultSQL
USS Mode-qNOSQL or default-qSQL

The SQL option also accepts DB2 precompiler options as parameters. For example, SQL(APOST, COMMA) enables the precompiler options APOST and COMMA. See the "Application Programming and SQL" guide cited in Resources for a listing and description of each precompiler option. The LIST compiler option helps you identify which precompiler options are enabled during compilation.


Post-compilation

Once compiled, link the application using the binder to generate a load module. Before running, the following is required:

  • The database subsystem name
  • DB2 Binding the DBRM and the load module into an application plan.

Both requirements can be accomplished through a sequence of DSN commands. Refer to the "Command Reference" cited in Resources for details on these commands. Once this step is complete the application is ready to run.

Listing 1. DSN commands to connect an application with a database
DSN  SYSTEM(DB29)
BIND PLAN(PLANLD) MEMBER(DB2DBRM) ACTION(REPLACE)
RUN  PROGRAM(PLANLD)
FREE PLAN(PLANLD)
END

In Listing 1, the application attempts to connect to DB2 subsystem DB29. Next, DBRM member DB2DBRM is bound with load module member PLANLD. This creates an application plan with the same name. Now it is ready to run.


How to use the Precompiler

The DSNHPC module is used to handle precompilation. DB2 precompiler options can be specified with the PARM parameter to modify SQL attributes. The DBRM is specified through a DD statement and later bound to the application before it runs. See Listing 2 for an example, and Downloads for a complete JCL example using the precompiler.

Listing 2. Sample JCL steps to build with the Precompiler
//* PRE COMPILE
//PC1      EXEC PGM=DSNHPC,PARM='HOST(C) APOST ATTACH(TSO)'
//DBRMLIB  DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.DBRMLIB(PREPROC),DISP=SHR
//STEPLIB  DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//SYSIN    DD DATA,DLM='/>'
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;

int main()
{
    EXEC SQL CREATE TABLE NEWTABLE
    (
        NAME     CHAR(12) NOT NULL,
        ID       INTEGER  NOT NULL
    ) IN DATABASE DSNUCOMP;

    EXEC SQL INSERT INTO NEWTABLE VALUES ('Name1', 123);
	
    EXEC SQL DROP TABLE NEWTABLE;

    return 0;
}
/>
//SYSCIN   DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.SOURCE(PREPROC),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTERM  DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSUT1   DD SPACE=(800,(500,500),,,ROUND),UNIT=SYSDA
//********************************************************************
//* COMPILE
// IF (PC1.RUN AND PC1.RC LE 4) THEN
//COMPILE EXEC PGM=CCNDRVR,REGION=192M,
//        PARM=('/OPTFILE(DD:XOPTS)')
//STEPLIB  DD DSN=CBC.SCCNCMP,DISP=SHR
//         DD DSN=CEE.SCEERUN2,DISP=SHR
//         DD DSN=CEE.SCEERUN,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.DBRMLIB(PREPROC),DISP=SHR
//SYSLIN   DD DSNAME=DB2RUNU.SQLEXT.TXPKPGZJ.OBJECT(PREPROC),DISP=SHR,
//        DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSIN    DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.SOURCE(PREPROC),DISP=SHR
//XOPTS    DD DATA,DLM='/>'
 NOSEARCH SE(//'CEE.SCEEH.+','CBC.SCLBH.+')
 LIST
/>
//SYSOUT   DD SYSOUT=*
//SYSCPRT  DD SYSOUT=*
// ENDIF
//********************************************************************
//* LINK
// IF (COMPILE.RUN AND COMPILE.RC EQ 0) THEN
//BIND     EXEC PGM=IEWL,REGION=5M,
//        PARM='AMODE=31,MAP,RENT,DYNAM=DLL,CASE=MIXED'
//SYSLIB   DD DSN=CEE.SCEELKEX,DISP=SHR
//         DD DSN=CEE.SCEELKED,DISP=SHR
//         DD DSN=CEE.SCEECPP,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSLIN   DD DATA,DLM='/>'
 INCLUDE OBJECT(PREPROC)
/>
//SYSLIN   DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.OBJECT(PREPROC),DISP=SHR
//         DD DSN=CEE.SCEELIB(C128),DISP=SHR
//         DD DSN=CBC.SCLBSID(IOSTREAM),DISP=SHR
//         DD DSN=CBC.SCLBSID(COMPLEX),DISP=SHR
//SYSLMOD  DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.LOAD(PREPROC),DISP=SHR
//SYSDEFSD DD DUMMY
//STEPLIB  DD DSN=CEE.SCEERUN2,DISP=SHR
//         DD DSN=CEE.SCEERUN,DISP=SHR
//SYSIN    DD DUMMY
//OBJECT   DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.OBJECT,DISP=SHR
// ENDIF
//********************************************************************
//* EXECUTE
// IF (BIND.RUN AND (BIND.RC EQ 0 OR BIND.RC EQ 4)) THEN
//GO       EXEC PGM=IKJEFT01,DYNAMNBR=300
//STEPLIB  DD DSN=CEE.SCEERUN,DISP=SHR
//         DD DSN=CBC.SCLBDLL,DISP=SHR
//         DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.LOAD,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=DB2RUNU.SQLEXT.TXPKPGZJ.DBRMLIB(PREPROC),DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD DATA,DLM='/>'
 DSN  SYSTEM(DB29)
 BIND PLAN(PREPROC) -
      MEMBER(PREPROC) ACTION(REPLACE) ISO(CS) VALIDATE(RUN)
 RUN  PROGRAM(PREPROC)
 FREE PLAN(PREPROC)
 END
/>
// ENDIF

How to use the Coprocessor

The SQL option is used in the CCNDRVR module, which is the compiler, to trigger the coprocessor. Notice how similar the build process is to a generic XL C/C++ application. See Downloads for a complete JCL example using the coprocessor.

Listing 3. Sample JCL steps to build with the Coprocessor
//* COMPILE
//COMPILE EXEC PGM=CCNDRVR,REGION=192M,
//        PARM=('/OPTFILE(DD:XOPTS)')
//STEPLIB  DD DSN=CBC.SCCNCMP,DISP=SHR
//         DD DSN=CEE.SCEERUN2,DISP=SHR
//         DD DSN=CEE.SCEERUN,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.DBRMLIB(COPROC),DISP=SHR
//SYSLIN   DD DSNAME=DB2RUNU.SQLEXT.ZUEDDHBX.OBJECT(COPROC),DISP=SHR,
//        DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSIN    DD DATA,DLM='/>'
#include <stdio.h>
EXEC SQL INCLUDE SQLCA;

int main()
{
    EXEC SQL CREATE TABLE NEWTABLE
    (
        NAME     CHAR(12) NOT NULL,
        ID       INTEGER  NOT NULL
    ) IN DATABASE DSNUCOMP;

    EXEC SQL INSERT INTO NEWTABLE VALUES ('Name1', 123);
	
    EXEC SQL DROP TABLE NEWTABLE;

    return 0;
}
/>
//XOPTS    DD DATA,DLM='/>'
 NOSEARCH SE(//'CEE.SCEEH.+','CBC.SCLBH.+')
 SQL LIST
/>
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SYSCPRT  DD SYSOUT=*
//********************************************************************
//* LINK
// IF (COMPILE.RUN AND COMPILE.RC LE 4) THEN
//BIND     EXEC PGM=IEWL,REGION=5M,
//        PARM='AMODE=31,MAP,RENT,DYNAM=DLL,CASE=MIXED'
//SYSLIB   DD DSN=CEE.SCEELKEX,DISP=SHR
//         DD DSN=CEE.SCEELKED,DISP=SHR
//         DD DSN=CEE.SCEECPP,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSLIN   DD DATA,DLM='/>'
 INCLUDE OBJECT(COPROC)
/>
//SYSLIN   DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.OBJECT(COPROC),DISP=SHR
//         DD DSN=CEE.SCEELIB(C128),DISP=SHR
//         DD DSN=CBC.SCLBSID(IOSTREAM),DISP=SHR
//         DD DSN=CBC.SCLBSID(COMPLEX),DISP=SHR
//SYSLMOD  DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.LOAD(COPROC),DISP=SHR
//SYSDEFSD DD DUMMY
//STEPLIB  DD DSN=CEE.SCEERUN2,DISP=SHR
//         DD DSN=CEE.SCEERUN,DISP=SHR
//SYSIN    DD DUMMY
//OBJECT   DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.OBJECT,DISP=SHR
// ENDIF
//********************************************************************
//* EXECUTE
// IF (BIND.RUN AND (BIND.RC EQ 0 OR BIND.RC EQ 4)) THEN
//GO       EXEC PGM=IKJEFT01,DYNAMNBR=300
//STEPLIB  DD DSN=CEE.SCEERUN,DISP=SHR
//         DD DSN=CBC.SCLBDLL,DISP=SHR
//         DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.LOAD,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNEXIT,DISP=SHR
//         DD DSN=DB2V9.DSN910.SDSNLOAD,DISP=SHR
//DBRMLIB  DD DSN=DB2RUNU.SQLEXT.ZUEDDHBX.DBRMLIB(COPROC),DISP=SHR
//SYSOUT   DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN  DD DATA,DLM='/>'
 DSN  SYSTEM(DB29)
 BIND PLAN(COPROC) -
      MEMBER(COPROC) ACTION(REPLACE) ISO(CS) VALIDATE(RUN)
 RUN  PROGRAM(COPROC)
 FREE PLAN(COPROC)
 END
/>
// ENDIF

Acknowledgements

The authors thank the following individuals who helped make this article possible: Zibi Sarbinowski, Jing Chen, and Kobi Vinayagamoorthy.


Downloads

DescriptionNameSize
Precompiler code samplepreproc.zip3KB
Coprocessor code samplecoproc.zip3KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=957945
ArticleTitle=Getting started with embedded SQL for XL C/C++ on IBM z/OS Systems
publish-date=12172013