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 do I need to do in an SQL application?

In an application, you need to complete these tasks:

Declare variables for passing data to and from the DB2 database system.
Execute SQL statements.
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:

By connecting to DB2 for z/OS using IBM Data Server Driver for JDBC and SQLJ.
By remote connections to DB2 for z/OS using DB2 for Linux, UNIX, and Windows client drivers.

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:

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.
Compile the modified source files to produce z/OS object code.
Link edit the object code for all the object modules to produce a z/OS load module.
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 Data Studio
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
Subject	See the following topics
Declaring variables	Declaring host variables and indicator variables in C, Equivalent SQL and C data types
Executing SQL statements	Programming C and C++ applications that issue SQL statements
Checking for and handling SQL errors	Defining the SQL communications area, SQLSTATE, and SQLCODE in 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	Programming 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 writing pureQuery applications
Subject	See the following topics
Starting point	Using IBM Data Studio to develop Java applications that access data

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	Programming 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	Programming 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.

Development of DB2 applications in integrated development environments
In an integrated development environment (IDE), can use various tools and languages to develop applications that access DB2 for z/OS data.
Programming languages and methods for developing application programs that access DB2 for z/OS
You can use a wide variety of programming languages and techniques to develop application programs for DB2 for z/OS. In addition, several methods are available for communicating with DB2.
Performance information for SQL application programming
Efficient applications are an important first step to good system and application performance. As you code applications that access data in DB2, consider performance objectives in your application design.
Preparation process for an application program
How you prepare an application program to run depends on the type of application. The program preparation steps for applications vary based on the type of programming language that is used.
Application programming default values
Each DB2 for z/OS subsystem has a set of application programming default values. These values are the default settings for SQL applications that run on that subsystem. For example, the ENSCHEME application programming default value specifies the default encoding scheme for new objects. Some users colloquially refer to these values as DECP values.
Static SQL applications
For most DB2 users, static SQL provides a straightforward, efficient path to DB2 data.
Dynamic SQL applications
With dynamic SQL, DB2 prepares and executes the SQL statements within a program while the program is running. Dynamic SQL is a good choice when you do not know the format of an SQL statement before you write or run a program.
Use of Java to execute static and dynamic SQL
DB2 for z/OS supports SQLJ and JDBC. In general, Java applications use SQLJ for static SQL, JDBC for dynamic SQL, and PureQuery for both static and dynamic SQL.
Use of an application program as a stored procedure
A stored procedure is a compiled program that can execute SQL statements.
Web application environment
Web-based applications run on a web application server and access data on an enterprise information system, such as a DB2 database server. The components of web-based applications are spread across multiple tiers, or layers.
Web-based applications and WebSphere Studio Application Developer
The WebSphere® Studio Application Developer offers features that developers can use to create web-based applications.
Processing XML data with DB2 pureXML
You can use pureXML® with your client applications to manage XML data in DB2 tables. You can store well-formed XML documents in their hierarchical form and retrieve all or portions of those documents.

Parent topic: What is DB2 for z/OS?