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 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 SDK. You can use the sample programs as templates to create your own applications. The file extensions for each supported language, as well as for programs categorized by group, are given in the following tables:

Sample File Extensions by Language

Table 2.

Sample File Extensions by Program Group

Table 3.

The following tables document the sample programs by type:

DB2 API Sample Programs with No Embedded SQL

Table 4.

DB2 API Embedded SQL Sample Programs

Table 5.

Embedded SQL Sample Programs with No DB2 APIs

Table 6.

User-Defined Function Sample Programs

Table 7

DB2 CLI Sample Programs

Table 8.

Java JDBC Sample Programs

Table 9.

Java SQLJ 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 5 contains programs that have both DB2 APIs and embedded SQL statements. For all DB2 API sample programs, please see both Table 4 and Table 5. For all embedded SQL sample programs (except for Java SQLJ), please see both Table 5 and Table 6.

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

Table 2. 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
Fortran	samples/fortran	.sqf	.f (UNIX) .for (OS/2)
JAVA	samples/java	.sqlj	.java
REXX	samples/rexx	.cmd	.cmd

Table 3. Sample File Extensions by Program Group

Sample Group	Directory	File Extension
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
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++)
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 the OS/2, AIX, and Windows 32-bit operating systems in the cobol subdirectory. Micro Focus Cobol Samples Are supplied on all platforms except Linux and Silicon Graphics IRIX. On all other platforms, the Micro Focus COBOL samples are in the cobol_mf subdirectory. Fortran Samples Are only supplied on the AIX, HP-UX, Silicon Graphics IRIX, Solaris, and OS/2 platforms. 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 the 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.

• On UNIX platforms.

  You can find the C source code for embedded SQL and DB2 API programs in sqllib/samples/c under your database instance directory; the C source code for DB2 CLI programs is in sqllib/samples/cli. For additional information about the programs in the samples tables, refer to the README file in the appropriate samples subdirectory under your DB2 instance. The README file will contain any additional samples that are not listed in this book.

• On OS/2 and Windows 32-bit operating systems.

  You can find the C source code for embedded SQL and DB2 API programs in %DB2PATH%\samples\c under the DB2 install directory; the C source code for DB2 CLI programs is in %DB2PATH%\samples\cli. The variable %DB2PATH% determines where DB2 is installed. Depending on the drive where DB2 is installed, %DB2PATH% will point to drive:\sqllib. For additional information about the sample programs in the samples tables, refer to the README file in the appropriate %DB2PATH%\samples subdirectory. The README file will contain any additional samples that are not listed in this book.

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.

Note:

The sample programs that are shipped with DB2 Universal Database have dependencies on the English version of the sample database and the associated table and column names. If the sample database has been translated into another national language on your version of DB2 Universal Database, you need to update the name of the sample database, and the names of the tables and the columns coded in the supplied sample programs, to the names used in the translated sample database. Otherwise, you will experience problems running the sample programs as shipped. Currently, the sample database is translated into the following languages: Brazilian Portuguese French Korean Norwegian Simplified Chinese

DB2 API Non-Embedded SQL Samples

Table 4. 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
regder C: regder.c COBOL: regder.cbl	sqledreg - Deregister sqleregs - Register
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
util C: util.c C++: util.C	sqlaintp - Get Error Message sqlogstt - Get SQLSTATE Message

DB2 API Embedded SQL Samples

Table 5. DB2 API Embedded SQL Sample Programs

Sample Program	Included APIs
asynrlog C: asynrlog.sqc	sqlurlog - Asynchronous Read Log
bindfile C: bindfile.sqc	sqlabndx - Bind
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
qload C: qload.sqc COBOL: qload.sqb	sqluqry - Load Query
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

Embedded SQL Samples With No DB2 APIs

Table 6. 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
calludf	Demonstrates calling user-defined functions (UDFs) created by the udf program, and stored on the server to accesss tables in the sample database. C: calludf.sqc ; C++: calludf.sqC
columns	Demonstrates the use of a cursor that is processed using dynamic SQL. This program lists all the entries in the system table, SYSIBM.SYSTABLES, 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 ; COBOL: dynamic.sqb
fillcli	Demonstrates the client-side of a stored procedure that uses the SQLDA to pass information specifying which table the stored procedure populates with random data. C: fillcli.sqc ; C++: fillcli.sqC
fillsrv	Demonstrates the server-side of a stored procedure example that uses the SQLDA to receive information from the client specifying the table that the stored procedure populates with random data. C: fillsrv.sqc ; C++: fillsrv.sqC
inpcli	Demonstrates stored procedures using either the SQLDA structure or host variables. This is the client program of a client/server example. (The server program is called inpsrv.) The program fills the SQLDA with information, and passes it to the server program for further processing. The SQLCA status is returned to the client program. This program shows the invocation of stored procedures using an embedded SQL CALL statement. C: inpcli.sqc ; C++: inpcli.sqC ; COBOL: inpcli.sqb
inpsrv	Demonstrates stored procedures using the SQLDA structure. This is the server program of a client/server example. (The client program is called inpcli.) The program creates a table (PRESIDENTS) in the sample database with the information received in the SQLDA. The server program does all the database processing and returns the SQLCA status to the client program. C: inpsrv.sqc ; C++: inpsrv.sqC ; COBOL: inpsrv.sqb
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
outcli	Demonstrates stored procedures using a host variable. This is the client program of a client/server example. (The server program is called outsrv.) This program declares a single OUT variable to hold the value returned from the server program. This program demonstrates the use of the CREATE PROCEDURE statement to register a stored procedure and shows the invocation of stored procedures using an embedded SQL CALL statement. C: outcli.sqc ; C++: outcli.sqC ; COBOL: outcli.sqb
outsrv	Demonstrates stored procedures using host variables. This is the server program of a client/server example. (The client program is called outcli.) The program sets the value of the function parameter to the median SALARY of the employees in the STAFF table of the sample database. The server does all the database processing (finding the median). The server program returns the host variable and the SQLCA status to the client program. C: outsrv.sqc ; C++: outsrv.sqC ; COBOL: outsrv.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
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
tblcli	Demonstrates a call to a table function (client-side) to display weather information for a number of cities. C: tblcli.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
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 7. 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.
tblsrv.c	Demonstrates a table function (server-side) that processes weather information for a number of cities.
udf.c	Creates a library of User-Defined Functions (UDFs) made specifically for the sample database tables, but can be used with tables of compatible column types.
UDFsrv.java	Demonstrates the use of Java User-Defined Functions (UDFs).

DB2 Call Level Interface Samples

Table 8. Sample CLI Programs in DB2 Universal Database

Sample Program Name	Program Description
Utility files used by most CLI samples
samputil.c	Utility functions used by most samples
samputil.h	Header file for samputil.c, included by most samples
General CLI Samples
adhoc.c	Interactive SQL with formatted output (was typical.c)
async.c	Run a function asynchronously (based on fetch.c)
basiccon.c	Basic connection
browser.c	List columns, foreign keys, index columns or stats for a table
calludf.c	Register and call a UDF
colpriv.c	List column Privileges
columns.c	List all columns for table search string
compnd.c	Compound SQL example
datasour.c	List all available data sources
descrptr.c	Example of descriptor usage
drivrcon.c	Rewrite of basiccon.c using SQLDriverConnect
duowcon.c	Multiple DUOW Connect type 2, syncpoint 1 (one phase commit)
embedded.c	Show equivalent DB2 CLI calls, for embedded SQL (in comments)
fetch.c	Simple example of a fetch sequence
getattrs.c	List some common environment, connection and statement options/attributes
getcurs.c	Show use of SQLGetCursor, and positioned update
getdata.c	Rewrite of fetch.c using SQLGetData instead of SQLBindCol
getfuncs.c	List all supported functions
getfuncs.h	Header file for getfuncs.c
getinfo.c	Use SQLGetInfo to get driver version and other information
getsqlca.c	Rewrite of adhoc.c to use prepare/execute and show cost estimate
lookres.c	Extract string from the resume clob field using locators
mixed.sqc	CLI sample with functions written using embedded SQL (Note: This file must be precompiled )
multicon.c	Multiple connections
native.c	Simple example of calling SQLNativeSql, and SQLNumParams
prepare.c	Rewrite of fetch.c, using prepare/execute instead of execdirect
proccols.c	List procedure parameters using SQLProcedureColumns
procs.c	List procedures using SQLProcedures
sfetch.c	Scrollable cursor example (based on xfetch.c)
setcolat.c	Set column attributes (using SQLSetColAttributes)
setcurs.c	Rewrite of getcurs.c using SQLSetCurs for positioned update
seteattr.c	Set environment attribute (SQL_ATTR_OUTPUT_NTS)
tables.c	List all tables
typeinfo.c	Display type information for all types for current data source
xfetch.c	Extended Fetch, multiple rows per fetch
BLOB Samples
picin.c	Loads graphic BLOBS into the emp_photo table directly from a file using SQLBindParamToFile
picin2.c	Loads graphic BLOBS into the emp_photo table using SQLPutData
showpic.c	Extracts BLOB picture to file (using SQLBindColToFile), then displays the graphic.
showpic2.c	Extracts BLOB picture to file using piecewise output, then displays the graphic.
Stored Procedure Samples
clicall.c	Defines a CLI function which is used in the embedded SQL sample mrspcli3.sqc
inpcli.c	Call embedded input stored procedure samples/c/inpsrv
inpcli2.c	Call CLI input stored procedure inpsrv2
inpsrv2.c	CLI input stored procedure (rewrite of embedded sample inpsrv.sqc)
mrspcli.c	CLI program that calls mrspsrv.c
mrspcli2.c	CLI program that calls mrspsrv2.sqc
mrspcli3.sqc	An embedded SQL program that calls mrspsrv2.sqc using clicall.c
mrspsrv.c	Stored procedure that returns a multi-row result set
mrspsrv2.sqc	An embedded SQL stored procedure that returns a multi-row result set
outcli.c	Call embedded output stored procedure samples/c/inpsrv
outcli2.c	Call CLI output stored procedure inpsrv2
outsrv2.c	CLI output stored procedure (rewrite of embedded sample inpsrv.sqc)
Samples using ORDER tables created by create.c (Run in the following order)
create.c	Creates all tables for the order scenario
custin.c	Inserts customers into the customer table (array insert)
prodin.c	Inserts products into the products table (array insert)
prodpart.c	Inserts parts into the prod_parts table (array insert)
ordin.c	Inserts orders into the ord_line, ord_cust tables (array insert)
ordrep.c	Generates order report using multiple result sets
partrep.c	Generates exploding parts report (recursive SQL Query)
order.c	UDF library code (declares a 'price' UDF)
order.exp	Used to build order libary
Samples unchanged from DB2 Version 2
v2sutil.c	samputil.c using old v2 functions
v2sutil.h	samputil.h using old v2 functions
v2fetch.c	fetch.c using old v2 functions
v2xfetch.c	xfetch.c using old v2 functions
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 9. Java Database Connectivity (JDBC) Sample Programs

Sample Program Name	Program Description
BasicCon.java	Basic connection.
Browser.java	List columns, foreign keys, index columns or stats for a table
ColPriv.java	List column Privileges
Columns.java	List all columns for table search string.
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.
DB2SpCli.java	A Java client application that calls the JDBC stored procedure, DB2Stp .
DB2Stp.java	A Java stored procedure that updates the EMPLOYEE table on the server, and returns new salary and payroll information to the client.
DB2UdCli.java	A Java client application that calls the Java user-defined function, DB2Udf .
Dynamic.java	Demonstrates a cursor using dynamic SQL.
Embedded.java	Create, populate, list data from and drop a table.
GetAttrs.java	List some common environment, connection and statement options/attributes.
GetData.java	Simple querying and displaying of data from database.
Inpcli.java	A Java client application that calls the JDBC stored procedure, Inpsrv .
Inpsrv.java	A Java stored procedure demonstrating the SQLDA structure. It creates a PRESIDENTS table in the sample database with information received from the SQLDA, and returns the SQLCA status to the client application, Inpcli .
JavaSample.java	Creates the table, JAVA_SAMPLE, adds data, displays data, and deletes the table.
JobChange.java	Uses a statement and a prepared statement to query the database and update information at the same time (does the same as getcurs.c and setcurs.c but cannot do both ways therefore above method is used).
LookRes.java	Extract string from the resume clob field by converting the entire clob into a string, and searching for the desired substring.
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.
MultiCon.java	Multiple connections.
Outcli.java	A Java client application that calls the SQLJ stored procedure, Outsrv .
PicIn.java	Loads graphic BLOBS into the emp_photo using the setBinaryStream method.
PluginEx.java	A Java program that adds new menu items and toolbar buttons to the DB2 Web Control Center.
Prepare.java	Rewrite of Simple.java, using a prepared statement.
ProcCols.java	List procedure parameters using getProcedureColumns under DatabaseMetaData.
Procs.java	List procedures using DatabaseMetaData.getProcedures.
ShowPic.java	Extracts BLOB picture to file using the getBinaryStream method. The file is read using the InputStream.read method.
Simple.java	Shows basic connecting querying and displaying the result set. (JDBC version of CLI sample fetch.c).
StpCli.java	A Java client application that calls the SQLJ stored procedure, Stp .
Tables.java	List all tables that match user defined search pattern.
Tools.java	Demonstrates a toolkit for Java programs written as DB2 samples.
TypeInfo.java	Display type information for all types for current data source.
UDFcli.java	A JDBC client application that calls functions in the Java user-defined function library, UDFsrv .
UsingThreads.java	Shows how to use threads to run an SQL statement asynchronously (JDBC version of CLI sample async.c).
Varinp.java	Demonstrates variable input to Embedded Dynamic SQL statement calls using parameter markers.
Samples using ORDER tables created by Create.java (Run in the following order).
Create.java	Creates all tables for the order scenario.
CustIn.java	Inserts customers into the customer table (array insert).
ProdIn.java	Inserts products into the products table (array insert).
ProdPart.java	Inserts parts into the prod_parts table (array insert).
OrdIn.java	Inserts orders into the ord_line, ord_cust tables (array insert).
OrdRep.java	Generates a customer order report and uses the user defined function 'price' defined in order.c.
PartRep.java	Generates exploding parts report (recursive SQL Query).
DropJava.java	Removes the new tables created for the ORDER scenario.

Table 10. 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 .
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.

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: Read a BLOB from the Sample database and display it to the screen. 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.vbp	Demonstrates rollup queries on a Microsoft Excel sales spreadsheet (implemented in Visual Basic).
names.vbp	Queries a Lotus Notes address book (implemented in Visual Basic).
inbox.vbp	Queries Microsoft Exchange inbox e-mail messages through OLE/Messaging (implemented in Visual Basic).
invoice.vbp	An OLE automation user-defined function that sends Microsoft Word invoice documents as e-mail attachments (implemented in Visual Basic).
ccounter	A counter OLE automation user-defined function (implemented in Visual C++).
salarysrv.vbp	An OLE automation stored procedure that calculates the median salary of the STAFF table of the sample database (implemented in Visual Basic).
salaryclt	A client program that invokes the median salary OLE automation stored procedure salarysrv (implemented in Visual Basic and 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.

[ DB2 List of Books | Search the DB2 Books ]