Programming for Db2 for z/OS

You use application programs that contain SQL statements to perform data definition or data manipulation operations on Db2 for z/OS objects.

Where can I find application programming samples?

See Db2 programming samples.

What application development tools can I use?

See Tools and IDEs for developing Db2 applications.

What do I need to do in an SQL application?

In an application, you need to complete these tasks:

  1. Declare variables for passing data to and from the Db2 database system.
  2. Execute SQL statements.
  3. Check for and handle SQL errors

What programming languages can I use to write SQL applications?

You can write application programs for Db2 for z/OS data servers in any of the following programming languages:

  1. By connecting to Db2 for z/OS using IBM Data Server Driver for JDBC and SQLJ.
  2. By remote connections to Db2 for z/OS using Db2 for Linux®, UNIX, and Windows client drivers.
  3. By connecting to Db2 for z/OS using the IBM_DB Node.js driver.
  4. By connecting to Db2 for z/OS using the IBM_DB Python driver.

How do I pass data between SQL applications and a Db2 table?

You use host variables and indicator variables to pass data between SQL applications and Db2 tables. Host variables are application variables that contain the data that is exchanged with the Db2 table. Indicator variables are application variables whose values denote whether there is data in the host variables.

What are static and dynamic SQL applications?

In a static SQL application, the SQL statements are written directly in the application. When the application runs, the user can change only the variable values. In a dynamic SQL application, the user provides the SQL statements when the application runs. In general, dynamic SQL applications are more flexible than static SQL applications. However, dynamic applications are harder to write, and often run more slowly because the application has more processing to do at run time.

The following table lists some sample programs that are shipped with Db2, which demonstrate how to write static and dynamic SQL applications. You can find the source code in data set prefix.SDSNSAMP.

Program name Language Type of SQL
DSN8BC3 COBOL Static
DSN8BD3 C Static
DSN8BP3 PL/I Static
DSNTEP2 PL/I Dynamic

How do I prepare an SQL application for execution?

Preparing most SQL applications involves the following tasks:

  1. For each source file in the application:
    • Modify a copy of the source file to convert the SQL statements into host language calls to an attachment facility.
    • Create a database request module (DBRM).

    The preferred way to accomplish this step is to direct the compiler to call the Db2 coprocessor. However, you can include a separate step that runs the Db2 precompiler before the step that runs the compiler.

  2. Compile the modified source files to produce z/OS object code.
  3. Link edit the object code for all the object modules to produce a z/OS load module.
  4. Bind the DBRM for each source file into a Db2 package, and binding the Db2 packages into a Db2 plan.
Note:
For C or C++ programs that use ODBC, Java programs that use JDBC or SQLJ, and REXX programs, the steps for preparing an SQL application for execution are different. The following table lists those steps.
Type of application Program preparation steps
ODBC Compile, prelink, link edit
JDBC Compile
SQLJ Translate, compile, customize, bind
pureQuery® Use IBM® Optim pureQuery Runtime
REXX No program preparation

Where do I find more information?

The following tables provide more information about application programming for each of the commonly used languages.

Table 1. Resources for writing Db2 applications in C and C++
Subject See the following topics
Declaring variables Declaring host variables and indicator variables in C and C++, Equivalent SQL and C data types
Executing SQL statements C and C++ applications that issue SQL statements
Checking for and handling SQL errors Defining the SQL communications area, SQLSTATE, and SQLCODE in C and C++, Checking the execution of SQL statements
Preparing an application to run Preparing an application to run on Db2 for z/OS
Table 2. Resources for writing ODBC applications
Subject See the following topics
Declaring variables Data types and data conversion
Executing SQL statements Transaction processing in Db2 ODBC
Checking for and handling SQL errors Diagnostics
Preparing an application to run Overview of preparing and executing a Db2 ODBC application
Table 3. Resources for writing Db2 applications in COBOL
Subject See the following topics
Declaring variables Declaring host variables and indicator variables in COBOL, Equivalent SQL and COBOL data types
Executing SQL statements COBOL applications that issue SQL statements
Checking for and handling SQL errors Defining the SQL communications area, SQLSTATE, and SQLCODE in COBOL, Checking the execution of SQL statements
Preparing an application to run Preparing an application to run on Db2 for z/OS
Table 4. Resources for writing JDBC applications
Subject See the following topics
Starting point JDBC application programming
Declaring variables Variables in JDBC applications, Data types that map to database data types in Java applications
Executing SQL statements JDBC interfaces for executing SQL
Checking for and handling SQL errors Exceptions and warnings under the IBM Data Server Driver for JDBC and SQLJ
Preparing an application to run Program preparation for JDBC programs
Table 5. Resources for writing SQLJ applications
Subject See the following topics
Starting point SQLJ application programming
Declaring variables Variables in SQLJ applications, Data types that map to database data types in Java applications
Executing SQL statements SQL statement execution in SQLJ applications
Checking for and handling SQL errors Handling SQL errors and warnings in SQLJ applications
Preparing an application to run Program preparation for SQLJ programs
Table 6. Resources for programming with JSON
Subject See the following topics
Starting point JSON application development for IBM data servers
JSON installation requirements Installation requirements for the JSON Java API
Working with JSON documents Working with JSON documents with the Java API
Table 7. Resources for writing Db2 applications in PL/I
Subject See the following topics
Declaring variables Declaring host variables and indicator variables in PL/I, Equivalent SQL and PL/I data types
Executing SQL statements PL/I applications that issue SQL statements
Checking for and handling SQL errors Defining the SQL communications area, SQLSTATE, and SQLCODE in PL/I, Checking the execution of SQL statements
Preparing an application to run Preparing an application to run on Db2 for z/OS
Table 8. Resources for writing Db2 applications in REXX
Subject See the following topics
Declaring variables Passing the data type of an input data type to Db2 for REXX programs, Equivalent SQL and REXX data types
Executing SQL statements REXX applications that issue SQL statements
Checking for and handling SQL errors Defining the SQL communications area, SQLSTATE, and SQLCODE in REXX, Checking the execution of SQL statements
Preparing an application to run No program preparation is necessary.

Other learning resources

For information on training in Db2 application programming, see Db2 for z/OS training paths.