Application Building Guide

Sample Programs

Notes:

  1. This section describes sample programs for the programming languages for all platforms supported by DB2. Not all sample programs have been ported to all platforms or supported programming languages.

  2. DB2 sample programs are provided "as is" without any warranty of any kind. The user, and not IBM, assumes the entire risk of quality, performance, and repair of any defects.

The sample programs come with the DB2 Application Development (DB2 AD) Client. You can use the sample programs as templates to create your own applications.

Sample program file extensions differ for each supported language, and for embedded SQL and non-embedded SQL programs within each language. File extensions may also differ for groups of programs within a language. These different sample file extensions are categorized in the following tables:

Sample File Extensions by Language
Table 1.

Sample File Extensions by Program Group
Table 2.

The following tables document the sample programs by type:

DB2 API Sample Programs with No Embedded SQL
Table 3.

DB2 API Embedded SQL Sample Programs
Table 4.

Embedded SQL Sample Programs with No DB2 APIs
Table 5.

User-Defined Function Sample Programs
Table 6

DB2 CLI Sample Programs
Table 7.

Java JDBC Sample Programs
Table 8.

Java SQLJ Sample Programs
Table 9.

SQL Procedure Sample Programs
Table 10.

ActiveX Data Objects, Remote Data Objects, and Microsoft Transaction Server Sample Programs
Table 11.

Object Linking and Embedding (OLE) Automation Sample Programs
Table 12.

Object Linking and Embedding Database (OLE DB) Table Functions
Table 13.

Command Line Processor (CLP) Sample Programs
Table 14.

Log Management User Exit Programs
Table 15.

Notes:

  1. Table 4 contains programs that have both DB2 APIs and embedded SQL statements. For all DB2 API sample programs, please see both Table 3 and Table 4. For all embedded SQL sample programs (except for Java SQLJ), please see both Table 4 and Table 5.

  2. Table 6 of UDF sample programs does not contain DB2 CLI UDF programs. For these, please see Table 7.


Table 1. Sample File Extensions by Language
Language Directory Embedded SQL Programs Non-embedded SQL Programs
C
samples/c
samples/cli (CLI programs)

.sqc .c
C++ samples/cpp
.sqC (UNIX)
.sqx (Windows & OS/2)
.C (UNIX)
.cxx (Windows & OS/2)

COBOL
samples/cobol
samples/cobol_mf

.sqb .cbl
JAVA samples/java .sqlj .java
REXX samples/rexx .cmd .cmd


Table 2. Sample File Extensions by Program Group
Sample Group Directory File Extension
ADO, RDO, MTS
samples\ADO\VB (Visual Basic)
samples\ADO\VC (Visual C++)
samples\RDO
samples\MTS
.bas .frm .vbp (Visual Basic)
.cpp .dsp .dsw (Visual C++)

CLP samples/clp .db2
OLE
samples\ole\msvb (Visual Basic)
samples\ole\msvc (Visual C++)
.bas .vbp (Visual Basic)
.cpp (Visual C++)

OLE DB samples\oledb .db2
SQL Procedures samples/sqlproc
.db2
.c .sqc (Client Applications)

User Exit samples/c
.cad (OS/2)
.cadsm (UNIX & Windows)
.cdisk (UNIX & Windows)
.ctape (UNIX)

Note:

Directory Delimiters
On UNIX are /. On OS/2 and Windows platforms, are \. In the tables, the UNIX delimiters are used unless the directory is only available on Windows and/or OS/2.

File Extensions
Are provided for the samples in the tables where only one extension exists.

Links to Sample Source Code
Are provided for some (but not all) programs in the samples tables.

Embedded SQL Programs
Require precompilation, except for REXX embedded SQL programs where the embedded SQL statements are interpreted when the program is run.

IBM COBOL samples
Are only supplied for AIX, OS/2, and Windows 32-bit operating systems in the cobol subdirectory.

Micro Focus Cobol Samples
Are only supplied for AIX, HP-UX, OS/2, Solaris Operating Environment, and Windows 32-bit operating systems in the cobol_mf subdirectory.

Java Samples
Are Java Database Connectivity (JDBC) applets, applications, and stored procedures, embedded SQL for Java (SQLJ) applets, applications, and stored procedures, as well as Java UDFs. Java samples are available on all supported DB2 platforms.

REXX Samples
Are only supplied for AIX, OS/2, and Windows NT operating systems.

CLP Samples
Are Command Line Processor scripts that execute SQL statements.

OLE Samples
Are for Object Linking and Embedding (OLE) in Microsoft Visual Basic and Microsoft Visual C++, supplied for Windows 32-bit operating systems only.

ADO, RDO, and MTS Samples
Are ActiveX Data Objects samples in Microsoft Visual Basic and Microsoft Visual C++, and Remote Data Objects and Microsoft Transaction Server samples in Microsoft Visual Basic, supplied for Windows 32-bit operating systems only.

User Exit samples
Are Log Management User Exit programs used to archive and retrieve database log files. The files must be renamed with a .c extension and compiled as C language programs.

You can find the sample programs in the samples subdirectory of the directory where DB2 has been installed. There is a subdirectory for each supported language. The following examples show you how to locate the samples written in C or C++ on each supported platform.

The sample programs directory is typically read-only on most platforms. Before you alter or build the sample programs, copy them to your working directory.

DB2 API Non-Embedded SQL Samples


Table 3. DB2 API Sample Programs with No Embedded SQL
Sample Program Included APIs
backrest

C: backrest.c

  • sqlbftcq - Fetch Tablespace Container Query
  • sqlbstsc - Set Tablespace Containers
  • sqlfudb - Update Database Configuration
  • sqlubkp - Backup Database
  • sqluroll - Rollforward Database
  • sqlurst - Restore Database

checkerr

COBOL: checkerr.cbl

  • sqlaintp - Get Error Message
  • sqlogstt - Get SQLSTATE Message

cli_info

C: cli_info.c

  • sqleqryi - Query Client Information
  • sqleseti - Set Client Information

client

C: client.c

C++: client.C

COBOL: client.cbl

  • sqleqryc - Query Client
  • sqlesetc - Set Client

d_dbconf

C: d_dbconf.c

COBOL: d_dbconf.cbl

  • sqleatin - Attach
  • sqledtin - Detach
  • sqlfddb - Get Database Configuration Defaults

d_dbmcon

C: d_dbmcon.c

COBOL: d_dbmcon.cbl

  • sqleatin - Attach
  • sqledtin - Detach
  • sqlfdsys - Get Database Manager Configuration Defaults

db_udcs

C: db_udcs.c

COBOL: db_udcs.cbl

  • sqleatin - Attach
  • sqlecrea - Create Database
  • sqledrpd - Drop Database

db2mon

C: db2mon.c

  • sqleatin - Attach
  • sqlmon - Get/Update Monitor Switches
  • sqlmonss - Get Snapshot
  • sqlmonsz - Estimate Size Required for sqlmonss() Output Buffer
  • sqlmrset - Reset Monitor

dbcat

C: dbcat.c

COBOL: dbcat.cbl

  • sqlecadb - Catalog Database
  • sqledcls - Close Database Directory Scan
  • sqledgne - Get Next Database Directory Entry
  • sqledosd - Open Database Directory Scan
  • sqleuncd - Uncatalog Database

dbcmt

C: dbcmt.c

COBOL: dbcmt.cbl

  • sqledcgd - Change Database Comment
  • sqledcls - Close Database Directory Scan
  • sqledgne - Get Next Database Directory Entry
  • sqledosd - Open Database Directory Scan
  • sqleisig - Install Signal Handler

dbconf

C: dbconf.c

COBOL: dbconf.cbl

  • sqleatin - Attach
  • sqlecrea - Create Database
  • sqledrpd - Drop Database
  • sqlfrdb - Reset Database Configuration
  • sqlfudb - Update Database Configuration
  • sqlfxdb - Get Database Configuration

dbinst

C: dbinst.c

COBOL: dbinst.cbl

  • sqleatcp - Attach and Change Password
  • sqleatin - Attach
  • sqledtin - Detach
  • sqlegins - Get Instance

dbmconf

C: dbmconf.c

COBOL: dbmconf.cbl

  • sqleatin - Attach
  • sqledtin - Detach
  • sqlfrsys - Reset Database Manager Configuration
  • sqlfusys - Update Database Manager Configuration
  • sqlfxsys - Get Database Manager Configuration

dbsnap

C: dbsnap.c

COBOL: dbsnap.cbl

  • sqleatin - Attach
  • sqlmonss - Get Snapshot

dbstart

C: dbstart.c

COBOL: dbstart.cbl

  • sqlepstart - Start Database Manager

dbstop

C: dbstop.c

COBOL: dbstop.cbl

  • sqlefrce - Force Application
  • sqlepstp - Stop Database Manager

dcscat

C: dcscat.c

COBOL: dcscat.cbl

  • sqlegdad - Catalog DCS Database
  • sqlegdcl - Close DCS Directory Scan
  • sqlegdel - Uncatalog DCS Database
  • sqlegdge - Get DCS Directory Entry for Database
  • sqlegdgt - Get DCS Directory Entries
  • sqlegdsc - Open DCS Directory Scan

dmscont

C: dmscont.c

  • sqleatin - Attach
  • sqlecrea - Create Database
  • sqledrpd - Drop Database

ebcdicdb

C: ebcdicdb.c

COBOL: ebcdicdb.cbl

  • sqleatin - Attach
  • sqlecrea - Create Database
  • sqledrpd - Drop Database

migrate

C: migrate.c

COBOL: migrate.cbl

  • sqlemgdb - Migrate Database

monreset

C: monreset.c

COBOL: monreset.cbl

  • sqleatin - Attach
  • sqlmrset - Reset Monitor

monsz

C: monsz.c

COBOL: monsz.cbl

  • sqleatin - Attach
  • sqlmonss - Get Snapshot
  • sqlmonsz - Estimate Size Required for sqlmonss() Output Buffer

nodecat

C: nodecat.c

COBOL: nodecat.cbl

  • sqlectnd - Catalog Node
  • sqlencls - Close Node Directory Scan
  • sqlengne - Get Next Node Directory Entry
  • sqlenops - Open Node Directory Scan
  • sqleuncn - Uncatalog Node

restart

C: restart.c

COBOL: restart.cbl

  • sqlerstd - Restart Database

setact

C: setact.c

COBOL: setact.cbl

  • sqlesact - Set Accounting String

setrundg

C: setrundg.c

  • sqlesdeg - Set Runtime Degree

sws

C: sws.c

COBOL: sws.cbl

  • sqleatin - Attach
  • sqlmon - Get/Update Monitor Switches

utilapi

C: utilapi.c

C++: utilapi.C

  • sqlaintp - Get Error Message
  • sqlogstt - Get SQLSTATE Message

DB2 API Embedded SQL Samples


Table 4. DB2 API Embedded SQL Sample Programs
Sample Program Included APIs
asynrlog

C: asynrlog.sqc

  • sqlurlog - Asynchronous Read Log

autocfg

C: autocfg.sqc

C++: autocfg.sqC

  • db2AutoConfig -- Autoconfig
  • db2AutoConfigMemory -- Autoconfig Free Memory
  • sqlfudb -- Update Database Configuration
  • sqlfusys -- Update Database Manager Configuration
  • sqlesetc -- Set Client
  • sqlaintp -- SQLCA Message

dbauth

C: dbauth.sqc

COBOL: dbauth.sqb

  • sqluadau - Get Authorizations

dbstat

C: dbstat.sqc

COBOL: dbstat.sqb

  • sqlureot - Reorganize Table
  • sqlustat - Runstats

expsamp

C: expsamp.sqc

COBOL: expsamp.sqb

  • sqluexpr - Export
  • sqluimpr - Import

impexp

C: impexp.sqc

COBOL: impexp.sqb

  • sqluexpr - Export
  • sqluimpr - Import

loadqry

C: loadqry.sqc

  • db2LoadQuery - Load Query

makeapi

C: makeapi.sqc

  • sqlabndx - Bind
  • sqlaprep - Precompile Program
  • sqlepstp - Stop Database Manager
  • sqlepstr - Start Database Manager

rebind

C: rebind.sqc

COBOL: rebind.sqb

  • sqlarbnd - Rebind

rechist

C: rechist.sqc

  • sqlubkp - Backup Database
  • sqluhcls - Close Recovery History File Scan
  • sqluhgne - Get Next Recovery History File Entry
  • sqluhops - Open Recovery History File Scan
  • sqluhprn - Prune Recovery History File
  • sqluhupd - Update Recovery History File

tabscont

C: tabscont.sqc

COBOL: tabscont.sqb

  • sqlbctcq - Close Tablespace Container Query
  • sqlbftcq - Fetch Tablespace Container Query
  • sqlbotcq - Open Tablespace Container Query
  • sqlbtcq - Tablespace Container Query
  • sqlefmem - Free Memory

tabspace

C: tabspace.sqc

COBOL: tabspace.sqb

  • sqlbctsq - Close Tablespace Query
  • sqlbftpq - Fetch Tablespace Query
  • sqlbgtss - Get Tablespace Statistics
  • sqlbmtsq - Tablespace Query
  • sqlbotsq - Open Tablespace Query
  • sqlbstpq - Single Tablespace Query
  • sqlefmem - Free Memory

tload

C: tload.sqc

COBOL: tload.sqb

  • sqluexpr - Export
  • sqluload - Load
  • sqluvqdp - Quiesce Tablespaces for Table

tspace

C: tspace.sqc

COBOL: tspace.sqb

  • sqlbctcq - Close Tablespace Container Query
  • sqlbctsq - Close Tablespace Query
  • sqlbftcq - Fetch Tablespace Container Query
  • sqlbftpq - Fetch Tablespace Query
  • sqlbgtss - Get Tablespace Statistics
  • sqlbmtsq - Tablespace Query
  • sqlbotcq - Open Tablespace Container Query
  • sqlbotsq - Open Tablespace Query
  • sqlbstpq - Single Tablespace Query
  • sqlbstsc - Set Tablespace Containers
  • sqlbtcq - Tablespace Container Query
  • sqlefmem - Free Memory

utilemb

C: utilemb.sqc

C++: utilemb.C

  • sqlaintp - Get Error Message
  • sqlogstt - Get SQLSTATE Message

Embedded SQL Samples With No DB2 APIs


Table 5. Embedded SQL Sample programs with No DB2 APIs
Sample Program Name Program Description
adhoc Demonstrates dynamic SQL and the SQLDA structure to process SQL commands interactively. SQL commands are input by the user, and output corresponding to the SQL command is returned.

C: adhoc.sqc

advsql Demonstrates the use of advanced SQL expressions like CASE, CAST, and scalar full selects.

C: advsql.sqc ; COBOL: advsql.sqb

blobfile Demonstrates the manipulation of a Binary Large Object (BLOB), by reading a BLOB value from the sample database and placing it in a file. The contents of this file can be displayed using an external viewer.

C: blobfile.sqc

columns Demonstrates the use of a cursor that is processed using dynamic SQL. This program lists a result set from SYSCAT.COLUMNS under a desired schema name.

C: columns.sqc

cursor Demonstrates the use of a cursor using static SQL.

C: cursor.sqc ; C++: cursor.sqC ; COBOL: cursor.sqb

delet Demonstrates static SQL to delete items from a database.

C: delet.sqc ; COBOL: delet.sqb

dynamic Demonstrates the use of a cursor using dynamic SQL.

C: dynamic.sqc

joinsql Demonstrates using advanced SQL join expressions.

C: joinsql.sqc ; COBOL: joinsql.sqb

largevol Demonstrates parallel query processing in a partitioned environment, and the use of an NFS file system to automate the merging of the result sets. Only available on AIX.

C: largevol.sqc

lobeval Demonstrates the use of LOB locators and defers the evaluation of the actual LOB data.

C: lobeval.sqc ; COBOL: lobeval.sqb

lobfile Demonstrates the use of LOB file handles.

C: lobfile.sqc ; COBOL: lobfile.sqb

lobloc Demonstrates the use of LOB locators.

C: lobloc.sqc ; COBOL: lobloc.sqb

lobval Demonstrates the use of LOBs.

C: lobval.sqc

openftch Demonstrates fetching, updating, and deleting of rows using static SQL.

C: openftch.sqc ; COBOL: openftch.sqb

recursql Demonstrates the use of advanced SQL recursive queries.

C: recursql.sqc

sampudf Demonstrates User-Defined Types (UDTs) and User-Defined Functions (UDFs) implemented to modify table entries. All UDFs declared in this program are sourced UDFs.

C: sampudf.sqc

spclient A client application that calls stored procedures in the spserver shared library.

C: spclient.sqc ; C++: spclient.sqC

spcreate.db2 A CLP script that contains the CREATE PROCEDURE statements to register the stored procedures created by the spserver program.

C/C++: spcreate.db2

spdrop.db2 A CLP script that contains the DROP PROCEDURE statements necessary for deregistering the stored procedures created by the spserver program.

C/C++: spdrop.db2

spserver A server program demonstrating stored procedures. The client program is spclient.

C: spserver.sqc ; C++: spserver.sqC

static Demonstrates static SQL to retrieve information.

C: static.sqc ; C++: static.sqC ; COBOL: static.sqb

tabsql Demonstrates the use of advanced SQL table expressions.

C: tabsql.sqc ; COBOL: tabsql.sqb

tbdefine Demonstrates creating and dropping tables.

C: tbdefine.sqc

thdsrver Demonstrates the use of POSIX threads APIs for thread creation and management. The program maintains a pool of contexts. A generate_work function is executed from main, and creates dynamic SQL statements that are executed by worker threads. When a context becomes available, a thread is created and dispatched to do the specified work. The work generated consists of statements to delete entries from either the STAFF or EMPLOYEE tables of the sample database. This program is only available on UNIX platforms.

C: thdsrver.sqc ; C++: thdsrver.sqC

trigsql Demonstrates using advanced SQL triggers and constraints.

C: trigsql.sqc ; COBOL: trigsql.sqb

udfcli Demonstrates calling a user-defined function (UDF) created by the udfsrv program, and stored on the server to access tables in the sample database.

C: udfcli.sqc ; C++: udfcli.sqC

updat Demonstrates static SQL to update a database.

C: updat.sqc ; C++: updat.sqC ; COBOL: updat.sqb

varinp Demonstrates variable input to Embedded Dynamic SQL statement calls using parameter markers.

C: varinp.sqc ; COBOL: varinp.sqb

User-Defined Function Samples


Table 6. User-Defined Function Sample programs
Sample Program Name Program Description
DB2Udf.java
A Java UDF that demonstrates several tasks, including integer division, manipulation of Character Large Objects (CLOBs), and the use of Java instance variables.
udfsrv.c
Creates a library with the User-Defined Function ScalarUDF, to access the sample database tables.
UDFsrv.java
Demonstrates the use of Java User-Defined Functions (UDFs).

DB2 Call Level Interface Samples


Table 7. Sample CLI Programs in DB2 Universal Database
Sample Program Name Program Description
Common Utility Files
utilcli.c
Utility functions used in CLI samples.
utilapi.c
Utility functions that call DB2 APIs.
Application Level - Samples that deal with the application level of DB2 and CLI.
apinfo.c
How to get and set application level information.
aphndls.c
How to allocate and free handles.
apsqlca.c
How to work with SQLCA data.
Installation Image Level - Samples that deal with the installation image level of DB2 and CLI.
ilinfo.c
How to get and set installation level information (such as the version of the CLI driver).
Instance Level - Samples that deal with the instance level of DB2 and CLI.
ininfo.c
How to get and set instance level information.
Database Level - Samples that deal with database objects in DB2.
dbconn.c
How to connect and disconnect from a database.
dbinfo.c
How to get and set information at a database level.
dbmconn.c
How to connect and disconnect from multiple databases (uses DB2 APIs to create and drop second database).
dbmuse.c
How to perform transactions with multiple databases (uses DB2 APIs to create and drop second database).
dbnative.c
How to translate a statement that contains an ODBC escape clause to a data source specific format.
dbuse.c
How to work with database objects.
dbusemx.sqc
How to use a single database in conjunction with embedded SQL.
Table Level - Samples that deal with table objects in DB2.
tbconstr.c
How to work with table constraints.
tbconstr.c
How to create, alter and drop tables.
tbinfo.c
How to get and set information at a table level.
tbmod.c
How to modify information in a table.
tbread.c
How to read information in a table.
Data Type Level - Samples that deal with data types.
dtinfo.c
How to get information about data types.
dtlob.c
How to read and write LOB data.
dtudt.c
How to create, use, and drop user defined distinct types.
UDF Level - Samples that demonstrate user defined functions.
udfcli.c
Client application which calls the user defined function in udfsrv.c.
udfsrv.c
User defined function ScalarUDF called by udfcli.c sample.
Stored Procedure Level - Samples that demonstrate stored procedures in CLI.
spcreate.db2 CLP script to issue CREATE PROCEDURE statements.
spdrop.db2 CLP script to drop stored procedures from the catalog.
spclient.c
Client program used to call the server functions declared in spserver.c.
spserver.c
Stored procedure functions built and run on the server.
spcall.c
Program to call any stored procedure.
Note:Other files in the samples/cli directory include:
  • README - Lists all example files.
  • makefile - Makefile for all files
  • build files for applications and stored procedures

Java Samples


Table 8. Java Database Connectivity (JDBC) Sample Programs
Sample Program Name Program Description
DB2Appl.java
A JDBC application that queries the sample database using the invoking user's privileges.
DB2Applt.java
A JDBC applet that queries the database using the JDBC applet driver. It uses the user name, password, server, and port number parameters specified in DB2Applt.html .
DB2Applt.html
An HTML file that embeds the applet sample program, DB2Applt . It needs to be customized with server and user information.
DB2UdCli.java
A Java client application that calls the Java user-defined function, DB2Udf .
Dynamic.java
Demonstrates a cursor using dynamic SQL.
MRSPcli.java
This is the client program that calls the server program MRSPsrv. The program demonstrates multiple result sets being returned from a Java stored procedure.
MRSPsrv.java
This is the server program that is called by the client program, MRSPcli. The program demonstrates multiple result sets being returned from a Java stored procedure.
Outcli.java
A Java client application that calls the SQLJ stored procedure, Outsrv .
PluginEx.java
A Java program that adds new menu items and toolbar buttons to the DB2 Web Control Center.
Spclient.java
A JDBC client application that calls PARAMETER STYLE JAVA stored procedures in the Spserver stored procedure class.
Spcreate.db2
A CLP script that contains the CREATE PROCEDURE statements to register the methods contained in the Spserver class as stored procedures.
Spdrop.db2
A CLP script that contains the DROP PROCEDURE statements necessary for deregistering the stored procedures contained in the Spserver class.
Spserver.java
A JDBC program demonstrating PARAMETER STYLE JAVA stored procedures. The client program is Spclient.java .
UDFcli.java
A JDBC client application that calls functions in the Java user-defined function library, UDFsrv .
UseThrds.java
Shows how to use threads to run an SQL statement asynchronously (JDBC version of CLI sample async.c).
V5SpCli.java
A Java client application that calls the DB2GENERAL stored procedure, V5Stp.java .
V5Stp.java
Demonstrates a DB2GENERAL stored procedure that updates the EMPLOYEE table on the server, and returns new salary and payroll information to the client. The client program is V5SpCli.java .
Varinp.java
Demonstrates variable input to Embedded Dynamic SQL statement calls using parameter markers.

Table 9. Embedded SQL for Java (SQLJ) Sample Programs
Sample Program Name Program Description
App.sqlj
Uses static SQL to retrieve and update data from the EMPLOYEE table of the sample database.
Applt.sqlj
An applet that queries the database using the JDBC applet driver. It uses the user name, password, server, and port number parameters specified in Applt.html .
Applt.html
An HTML file that embeds the applet sample program, Applt . It needs to be customized with server and user information.
Cursor.sqlj
Demonstrates an iterator using static SQL.
OpF_Curs.sqlj
Class file for the Openftch program.
Openftch.sqlj
Demonstrates fetching, updating, and deleting rows using static SQL.
Outsrv.sqlj
Demonstrates a stored procedure using the SQLDA structure. It fills the SQLDA with the median salary of the employees in the STAFF table of the sample database. After the database processing (finding the median), the stored procedure returns the filled SQLDA and the SQLCA status to the JDBC client application, Outcli .
Stclient.sqlj
An SQLJ client application that calls PARAMETER STYLE JAVA stored procedures created by the SQLJ stored procedure program, Stserver .
Stcreate.db2
A CLP script that contains the CREATE PROCEDURE statements to register the methods contained in the Stserver class as stored procedures.
Stdrop.db2
A CLP script that contains the DROP PROCEDURE statements necessary for deregistering the stored procedures contained in the Stserver class.
Stserver.sqlj
An SQLJ program demonstrating PARAMETER STYLE JAVA stored procedures. The client program is Stclient.sqlj .
Static.sqlj
Uses static SQL to retrieve information.
Stp.sqlj
A stored procedure that updates the EMPLOYEE table on the server, and returns new salary and payroll information to the JDBC client program, StpCli .
UDFclie.sqlj
A client application that calls functions from the Java user-defined function library, UDFsrv .
Updat.sqlj
Uses static SQL to update a database.

SQL Procedure Samples


Table 10. SQL Procedure Sample Programs
Sample Program Name Program Description
basecase.db2 The UPDATE_SALARY procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to a CASE statement that uses the "rating" IN parameter.
basecase.sqc Calls the UPDATE_SALARY procedure.
baseif.db2 The UPDATE_SALARY_IF procedure raises the salary of an employee identified by the "empno" IN parameter in the "staff" table of the "sample" database. The procedure determines the raise according to an IF statement that uses the "rating" IN parameter.
baseif.sqc Calls the UPDATE_SALARY_IF procedure.
dynamic.db2 The CREATE_DEPT_TABLE procedure uses dynamic DDL to create a new table. The name of the table is based on the value of the IN parameter to the procedure.
dynamic.sqc Calls the CREATE_DEPT_TABLE procedure.
iterate.db2 The ITERATOR procedure uses a FETCH loop to retrieve data from the "department" table. If the value of the "deptno" column is not 'D11', modified data is inserted into the "department" table. If the value of the "deptno" column is 'D11', an ITERATE statement passes the flow of control back to the beginning of the LOOP statement.
iterate.sqc Calls the ITERATOR procedure.
leave.db2 The LEAVE_LOOP procedure counts the number of FETCH operations performed in a LOOP statement before the "not_found" condition handler invokes a LEAVE statement. The LEAVE statement causes the flow of control to exit the loop and complete the stored procedure.
leave.sqc Calls the LEAVE_LOOP procedure.
loop.db2 The LOOP_UNTIL_SPACE procedure counts the number of FETCH operations performed in a LOOP statement until the cursor retrieves a row with a space (' ') value for column "midinit". The loop statement causes the flow of control to exit the loop and complete the stored procedure.
loop.sqc Calls the LOOP_UNTIL_SPACE procedure.
nestcase.db2 The BUMP_SALARY procedure uses nested CASE statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
nestcase.sqc Calls the BUMP_SALARY procedure.
nestif.db2 The BUMP_SALARY_IF procedure uses nested IF statements to raise the salaries of employees in a department identified by the dept IN parameter from the "staff" table of the "sample" database.
nestif.sqc Calls the BUMP_SALARY_IF procedure.
repeat.db2 The REPEAT_STMT procedure counts the number of FETCH operations performed in a repeat statement until the cursor can retrieve no more rows. The condition handler causes the flow of control to exit the repeat loop and complete the stored procedure.
repeat.sqc Calls the REPEAT_STMT procedure.
rsultset.c Calls the MEDIAN_RESULT_SET procedure, displays the median salary, then displays the result set generated by the SQL procedure. This client is written using the CLI API, which can accept result sets.
rsultset.db2 The MEDIAN_RESULT_SET procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "rsultset" client. The procedure then opens a WITH RETURN cursor to return a result set of the employees with a salary greater than the median. The procedure returns the result set to the client.
spserver.db2 The SQL procedures in this CLP script demonstrate basic error-handling, nested stored procedure calls, and returning result sets to the client application or the calling application. You can call the procedures using the "spcall" application, in the CLI samples directory. You can also use the "spclient" application, in the C and CPP samples directories, to call the procedures that do not return result sets.
whiles.db2 The DEPT_MEDIAN procedure obtains the median salary of employees in a department identified by the "dept" IN parameter from the "staff" table of the "sample" database. The median value is assigned to the salary OUT parameter and returned to the "whiles" client. The whiles client then prints the median salary.
whiles.sqc Calls the DEPT_MEDIAN procedure.

ADO, RDO, and MTS Samples


Table 11. ADO, RDO, and MTS Sample Programs
Sample Program Name Program Description
Bank.vbp An RDO program to create and maintain data for bank branches, with the ability to perform transactions on customer accounts. The program can use any database specified by the user as it contains the DDL to create the necessary tables for the application to store data.
Blob.vbp This ADO program demonstrates retrieving BLOB data. It retrieves and displays pictures from the emp_photo table of the sample database. The program can also replace an image in the emp_photo table with one from a local file.
BLOBAccess.dsw This sample demonstrates highlighting ADO/Blob access using Microsoft Visual C++. It is similar to the Visual Basic sample, Blob.vbp. The BLOB sample has two main functions:
  1. Read a BLOB from the Sample database and display it to the screen.
  2. Read a BLOB from a file and insert it into the database. (Import)
Connect.vbp This ADO program will create a connection object, and establish a connection, to the sample database. Once completed, the program will disconnect and exit.
Commit.vbp This application demonstrates the use of autocommit/manual-commit features of ADO. The program queries the EMPLOYEE table of the sample database for employee number and name. The user has an option of connecting to the database in either autocommit or manual-commit mode. In the autocommit mode, all of the changes that a user makes on a record are updated automatically in the database. In the manual-commit mode, the user needs to begin a transaction before he/she can make any changes. The changes made since the beginning of a transaction can be undone by performing a rollback. The changes can be saved permanently by committing the transaction. Exiting the program automatically rolls back the changes.
db2com.vbp This Visual Basic project demonstrates updating a database using the Microsoft Transaction Server. It creates a server DLL used by the client program, db2mts.vbp, and has four class modules:
  • UpdateNumberColumn.cls
  • UpdateRow.cls
  • UpdateStringColumn.cls
  • VerifyUpdate.cls
For this program a temporary table, DB2MTS, is created in the sample database.
db2mts.vbp This is a Visual Basic project for a client program that uses the Microsoft Transaction Server to call the server DLL created from db2com.vbp.
Select-Update.vbp This ADO program performs the same functions as Connect.vbp, but also provides a GUI interface. With this interface, the user can view, update, and delete data stored in the ORG table of the sample database.
Sample.vbp This Visual Basic project uses Keyset cursors via ADO to provide a graphical user interface to all data in the sample database.
VarCHAR.dsp A Visual C++ program that uses ADO to access VarChar data as textfields. It provides a graphical user interface to allow users to view and update data in the ORG table of the sample database.

Object Linking and Embedding Samples


Table 12. Object Linking and Embedding (OLE) Sample Programs
Sample Program Name Program Description
sales Demonstrates rollup queries on a Microsoft Excel sales spreadsheet (implemented in Visual Basic).
names Queries a Lotus Notes address book (implemented in Visual Basic).
inbox Queries Microsoft Exchange inbox e-mail messages through OLE/Messaging (implemented in Visual Basic).
invoice An OLE automation user-defined function that sends Microsoft Word invoice documents as e-mail attachments (implemented in Visual Basic).
bcounter An OLE automation user-defined function demonstrating a scratchpad using instance variables (implemented in Visual Basic).
ccounter A counter OLE automation user-defined function (implemented in Visual C++).
salarysrv An OLE automation stored procedure that calculates the median salary of the STAFF table of the sample database (implemented in Visual Basic).
salarycltvc A Visual C++ embedded SQL sample that calls the Visual Basic stored procedure, salarysrv.
salarycltvb A Visual Basic DB2 CLI sample that calls the Visual Basic stored procedure, salarysrv.
testcli An OLE automation embedded SQL client application that calls the stored procedure, tstsrv (implemented in Visual C++).
tstsrv An OLE automation stored procedure demonstrating the passing of various types between client and stored procedure (implemented in Visual C++).

Table 13. Object Linking and Embedding Database (OLE DB) Table Functions
Sample Program Name Program Description
jet.db2 Microsoft.Jet.OLEDB.3.51 Provider
mapi.db2 INTERSOLV Connect OLE DB for MAPI
msdaora.db2 Microsoft OLE DB Provider for Oracle
msdasql.db2 Microsoft OLE DB Provider for ODBC Drivers
msidxs.db2 Microsoft OLE DB Index Server Provider
notes.db2 INTERSOLV Connect OLE DB for Notes
sampprov.db2 Microsoft OLE DB Sample Provider
sqloledb.db2 Microsoft OLE DB Provider for SQL Server

Command Line Processor Samples


Table 14. Command Line Processor (CLP) Sample Programs.
Sample File Name File Description
const.db2 Creates a table with a CHECK CONSTRAINT clause.
cte.db2 Demonstrates a common table expression. The equivalent sample program demonstrating this advanced SQL statement is tabsql.
flt.db2 Demonstrates a recursive query. The equivalent sample program demonstrating this advanced SQL statement is recursql.
join.db2 Demonstrates an outer join of tables. The equivalent sample program demonstrating this advanced SQL statement is joinsql.
stock.db2 Demonstrates the use of triggers. The equivalent sample program demonstrating this advanced SQL statement is trigsql.
testdata.db2 Uses DB2 built-in functions such as RAND() and TRANSLATE() to populate a table with randomly generated test data.
thaisort.db2 This script is particularly for Thai users. Thai sorting is by phonetic order requiring pre-sorting/swapping of the leading vowel and its consonant, as well as post-sorting in order to view the data in the correct sort order. The file implements Thai sorting by creating UDF functions presort and postsort, and creating a table; then it calls the functions against the table to sort the table data. To run this program, you first have to build the user-defined function program, udf, from the C source file, udf.c.

Log Management User Exit Samples


Table 15. Log Management User Exit Sample Programs.
Sample File Name File Description
db2uext2.cadsm This is a sample User Exit utilizing ADSTAR DSM ( ADSM ) APIs to archive and retrieve database log files. The sample provides an audit trail of calls (stored in a separate file for each option) including a timestamp and parameters received. It also provides an error trail of calls in error including a timestamp and an error isolation string for problem determination. These options can be disabled. The file must be renamed db2uext2.c and compiled as a C program. Available on UNIX and Windows 32-bit operating systems. The OS/2 version is db2uexit.cad.
db2uexit.cad This is the OS/2 version of db2uext2.cadsm. The file must be renamed db2uexit.c and compiled as a C program.
db2uext2.cdisk This is a sample User Exit utilizing the system copy command for the particular platform on which it ships. The program archives and retrieves database log files, and provides an audit trail of calls (stored in a separate file for each option) including a timestamp and parameters received. It also provides an error trail of calls in error including a timestamp and an error isolation string for problem determination. These options can be disabled. The file must be renamed db2uext2.c and compiled as a C program. Available on UNIX and Windows 32-bit operating systems.
db2uext2.ctape This is a sample User Exit utilizing system tape commands for the particular UNIX platform on which it ships. The program archives and retrieves database log files. All limitations of the system tape commands are limitations of this user exit. The sample provides an audit trail of calls (stored in a separate file for each option) including a timestamp and parameters received. It also provides an error trail of calls in error including a timestamp and an error isolation string for problem determination. These options can be disabled. The file must be renamed db2uext2.c and compiled as a C program. Available on UNIX platforms only.

[ Top of Page | Previous Page | Next Page ]