PDF

Getting started with embedded SQL for XL C/C++ on
IBM z/OS Systems
Francesco Cassullo ([email protected])
Software Engineer
IBM
17 December 2013
Igor Todorovski ([email protected])
Software Developer
IBM
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.
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 ”
© Copyright IBM Corporation 2013
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Trademarks
Page 1 of 11
developerWorks®
ibm.com/developerWorks/
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
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).
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 2 of 11
ibm.com/developerWorks/
developerWorks®
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
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:
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 3 of 11
developerWorks®
•
•
•
•
•
•
•
•
•
ibm.com/developerWorks/
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 trigraphs.
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.
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 4 of 11
ibm.com/developerWorks/
developerWorks®
Table 2. Examples for specifying the SQL option
precompiler
coprocessor
Batch Mode
NOSQL or default
SQL
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
BIND
RUN
FREE
END
SYSTEM(DB29)
PLAN(PLANLD) MEMBER(DB2DBRM) ACTION(REPLACE)
PROGRAM(PLANLD)
PLAN(PLANLD)
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()
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 5 of 11
developerWorks®
ibm.com/developerWorks/
{
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
//********************************************************************
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 6 of 11
ibm.com/developerWorks/
developerWorks®
//* 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.+')
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 7 of 11
developerWorks®
ibm.com/developerWorks/
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.
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 8 of 11
ibm.com/developerWorks/
developerWorks®
Downloads
Description
Name
Size
Precompiler code sample
preproc.zip
3KB
Coprocessor code sample
coproc.zip
3KB
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 9 of 11
developerWorks®
ibm.com/developerWorks/
Resources
Learn
• For relevant DB2 database documentation, see:
• DB2 10 for z/OS Application programming and SQL information center (SC19-2969-04).
• DB2 10 for z/OS Command Reference (SC19-2972-06).
• DB2 10 for z/OS Codes (GC19-2971-04).
• For details on the compiler, see:
• "Using DB2 Universal Database" in zOS XL C/C++ Programming Guide
(SC14-7315-00).
• z/OS XL C/C++ User's Guide (SC14-7307-00) for SQL and DBRMLIB option
descriptions.
• Other articles on embedded SQL:
• Error handling in embedded SQL for C/C++ on IBM z/OS systems.
• Explore the Rational software area on developerWorks for technical resources, best
practices, and information about Rational collaborative and integrated solutions for software
and systems delivery.
• Stay current with developerWorks technical events and webcasts focused on a variety of IBM
products and IT industry topics.
• Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and
tools, as well as IT industry trends.
• Watch developerWorks on-demand demos, ranging from product installation and setup
demos for beginners to advanced functionality for experienced developers.
Get products and technologies
• Download a free trial version of Rational software.
• Evaluate other IBM software in the way that suits you best: Download it for a trial, try it online,
use it in a cloud environment.
Discuss
• Check out the Rational C/C++ Café community.
• Get connected with your peers and keep up on the latest information in the Rational
community.
• Check the Rational software forums to ask questions and participate in discussions.
• Ask and answer questions and increase your expertise when you get involved in the Rational
forums, cafés, and wikis.
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 10 of 11
ibm.com/developerWorks/
developerWorks®
About the authors
Francesco Cassullo
Francesco 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
Igor 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.
© Copyright IBM Corporation 2013
(www.ibm.com/legal/copytrade.shtml)
Trademarks
(www.ibm.com/developerworks/ibm/trademarks/)
Getting started with embedded SQL for XL C/C++ on IBM z/OS
Systems
Page 11 of 11