Embedded SQL Programming Guide

Installing, Building and Executing the Sample Programs

The sample programs used in this book show examples of embedded SQL statements and API calls in the supported host languages. The sample programs are written to be short and simple. Production applications should check the return codes, and especially the SQLCODE or SQLSTATE from all API calls and SQL statements. For information on handling error conditions, SQLCODEs, and SQLSTATEs, see "Diagnostic Handling and the SQLCA Structure". See the DB2 SDK Building Applications for details on how to install, build, and execute these programs in your environment.

The DB2 SDK comes with sample programs. The file extensions for each supported language, and the directories where the programs can be found on the supported platforms, are given in Table 23.

The sample programs providing examples of embedded SQL and DB2 API calls are shown in Table 24. Command Line Processor (CLP) files provided by DB2 are shown in Table 25.

Java sample programs are shown in Table 26. Object Linking and Embedding (OLE) sample programs are shown in Table 27. The sample programs demonstrating DB2 CLI calls are shown in Table 28.

You can use the sample programs to learn how to code your applications.

Table 23. Sample Program File Extensions and Locations

Language CLI Programs Programs with Embedded SQL Programs without Embedded SQL
C File Ext. .c .sqc .c
Directory samples/cli samples/c samples/c
C++ File Ext. Not Applicable
.sqC (UNIX) .sqx

.C (UNIX) .cxx (Intel)

Directory Not Applicable samples/cpp
samples/cpp
COBOL File Ext. Not Applicable .sqb .cbl
Directory Not Applicable
samples/cobol samples/cobol_mf

samples/cobol samples/cobol_mf

FORTRAN File Ext. Not Applicable .sqf
.f (UNIX) .for (OS/2)

Directory Not Applicable samples/fortran samples/fortran
REXX File Ext. Not Applicable .cmd .cmd
Directory Not Applicable samples/rexx samples/rexx
JAVA File Ext. Not Applicable Not Applicable .java
Directory Not Applicable Not Applicable samples/java
OLE File Ext. Not Applicable Not Applicable Not Applicable
Directory samples\ole Not Applicable samples\ole

Note:

Programs without SQL
Denotes programs with no SQL statements in them (primarily programs using DB2 API functions).
Directory Delimiters
On UNIX are /. On OS/2 and Windows platforms, are \.
IBM COBOL samples
Are only supplied on the OS/2, AIX, Windows NT and Windows 95 platforms in the cobol subdirectory.
Micro Focus Cobol Samples
Are supplied on all platforms except the Macintosh. The 16-bit Micro Focus COBOL examples are supplied in the cobol_16 subdirectory on OS/2, and the cobol subdirectory on Windows 3.1. For 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.
REXX Samples
Are only supplied on the AIX, OS/2, Windows NT and Windows 95 platforms.
Java Samples
Are stored procedures and UDFs, as well as Java Database Connectivity (JDBC) applications and applets. Java samples are available on the AIX, HP-UX, Solaris, OS/2, Windows NT and Windows 95 platforms.
OLE Samples
Are for Object Linking and Embedding (OLE) in Microsoft Visual Basic and Microsoft Visual C++, supplied on the Windows NT and Windows 95 platforms only.

The above table lists the supported languages within the specified programming paradigms. Not all sample programs have been ported to all the supported programming languages.

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 sample programs in Table 24 and Table 28, refer to the README file in the appropriate samples subdirectory under your database manager instance. The README file will contain any additional samples that are not listed in this book.
On OS/2, Windows NT, and Windows 95 platforms.
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 which drive DB2 is installed, %DB2PATH% will point to drive:\sqllib. For additional information about the sample programs in Table 24 and Table 28, 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.
On Windows 3.1.
You can find the C source code for embedded SQL and DB2 API programs in %DB2PATH%\samples\c; the C source code for DB2 CLI programs is in %DB2PATH%\samples\cli. The db2.ini file, which stores the DB2 settings, defines the value for %DB2PATH%, which by default points to drive:\sqllib\win. The value of %DB2PATH%, as referenced in the db2.ini file, is only recognized within the DB2 environment. For additional information about the sample programs in Table 24 and Table 28, refer to the README files in these subdirectories. The README files will contain any additional samples that are not listed in this book.
On Macintosh.
You can find the sample programs in the DB2:samples: folder. There are sub-folders for sample programs written in C and CLI. For additional information about the sample programs in Table 24 and Table 28, refer to the README file in the DB2:samples: folder. The README file will contain any additional samples that are not listed in this book.

If your platform is not addressed in Table 23, please refer to the DB2 SDK Building Applications book for your platform for information specific to your environment.

Not all of the sample programs are available in all the supported programming languages.

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. On the Macintosh, copy them to your working folder.
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 for the following countries:

France
Italy
Spain
Finland
Norway
People's Republic of China

In Table 24, 'Yes', in the Embedded SQL column, indicates that the program contains embedded SQL. A blank indicates that the program does not contain embedded SQL, and thus no precompiling is required.

Table 24. Sample Programs Showing Embedded SQL and APIs

Sample Program Name Embedded SQL Program Description
adhoc Yes 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. See "Example ADHOC Program" for details.
advsql Yes Demonstrates the use of advanced SQL expressions like CASE, CAST, and scalar full selects.
asynrlog Yes Demonstrates the use of the following API:
ASYNCHRONOUS LOG READ

autoloader
A UNIX Korn shell script that prepares ftp scripts for data transfer from remote hosts and generates a temporary buffer space (FIFO or named pipes). It then starts db2split and invokes DB2 LOAD.
In a partitioned environment, partitioning keys are used to determine the partition where the data resides. Therefore, data must pass through a splitting phase before it can be loaded at the correct partition.
The entire split and load process can be accomplished by the autoLoader utility. It uses a system-defined hashing function to partition the data into as many output files as there are partitions in the nodegroup in which the table is defined. It then loads these output files concurrently across the set of partitions in the nodegroup.
backrest
Demonstrates the use of the following APIs:
BACKUP DATABASE RESTORE DATABASE ROLL FORWARD DATABASE

blobfile Yes 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 which can be displayed using an external viewer.
bindfile Yes Demonstrates the use of the BIND API to bind an embedded SQL application to a database.
calludf Yes Demonstrates the use of the library of User-Defined Functions (UDFs) created by udf for the SAMPLE database tables.
client
Demonstrates the use of the following APIs:
SET CLIENT QUERY CLIENT

columns Yes 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.
cursor Yes Demonstrates the use of a cursor using static SQL. See "Example Cursor Program" for details.
d_dbconf
Demonstrates the use of the following API:
GET DATABASE CONFIGURATION DEFAULTS

d_dbmcon
Demonstrates the use of the following API:
GET DATABASE MANAGER CONFIGURATION DEFAULTS

da_manip Yes Provides a library of routines to manipulate SQLDAs and SQLVARs.
db2mon
Demonstrates how to use the Database System Monitor APIs, and how to process the output data buffer returned from the Snapshot API.
db2uext2
Provides a sample log management user exit.
dbauth Yes Demonstrates the use of the following API:
GET AUTHORIZATIONS

dbcat
Demonstrates the use of the following APIs:
CATALOG DATABASE CLOSE DATABASE DIRECTORY SCAN GET NEXT DATABASE DIRECTORY ENTRY OPEN DATABASE DIRECTORY SCAN UNCATALOG DATABASE

dbcmt
Demonstrates the use of the following APIs:
CHANGE DATABASE COMMENT

dbconf
Demonstrates the use of the following APIs:
CREATE DATABASE DROP DATABASE GET DATABASE CONFIGURATION RESET DATABASE CONFIGURATION UPDATE DATABASE CONFIGURATION

dbinst
Demonstrates the use of the following APIs:
ATTACH TO INSTANCE DETACH FROM INSTANCE GET INSTANCE

dbmconf
Demonstrates the use of the following APIs:
GET DATABASE MANAGER CONFIGURATION RESET DATABASE MANAGER CONFIGURATION UPDATE DATABASE MANAGER CONFIGURATION

dbsnap
Demonstrates the use of the following API:
DATABASE SYSTEM MONITOR SNAPSHOT

dbstart
Demonstrates the use of the following API:
START DATABASE MANAGER

dbstat Yes Demonstrates the use of the following APIs:
REORGANIZE TABLE RUN STATISTICS

dbstop
Demonstrates the use of the following APIs:
FORCE USERS STOP DATABASE MANAGER

db_udcs
Demonstrates the use of the following APIs in order to simulate the collating behaviour of a DB2 for MVS/ESA or OS/390 CCSID 500 (EBCDIC International) collating sequence:
CREATE DATABASE DROP DATABASE

dcscat
Demonstrates the use of the following APIs:
ADD DCS DIRECTORY ENTRY CLOSE DCS DIRECTORY SCAN GET DCS DIRECTORY ENTRY FOR DATABASE GET DCS DIRECTORY ENTRIES OPEN DCS DIRECTORY SCAN UNCATALOG DCS DIRECTORY ENTRY

delet Yes Demonstrates static SQL to delete items from a database.
dmscont
Demonstrates the use of the following APIs in order to create a database with more than one database managed storage (DMS) container:
CREATE DATABASE DROP DATABASE

dynamic Yes Demonstrates the use of a cursor using dynamic SQL. See "Example Dynamic SQL Program" for details.
ebcdicdb
Demonstrates the use of the following APIs in order to simulate the collating behaviour of a DB2 for MVS/ESA or OS/390 CCSID 037 (EBCDIC US English) collating sequence:
CREATE DATABASE DROP DATABASE

expsamp Yes Demonstrates the use of the following APIs:
EXPORT IMPORT
in conjunction with a DRDA database.
fillcli Yes 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.
fillsrv Yes 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.
impexp Yes Demonstrates the use of the following APIs:
EXPORT IMPORT

inpcli Yes 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. See "How the Example Input-SQLDA Client Application Works" for details.
inpsrv Yes 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. See "How the Example Input-SQLDA Stored Procedure Works" for details.
joinsql Yes An example using advanced SQL join expressions.
largevol Yes Demonstrates parallel query processing in a partitioned environment, and the use of an NFS file system to automate the merging of the result sets. See "Example: Extracting Large Volume of Data (largevol.c)" for details.
lobeval Yes Demonstrates the use of LOB locators and deferring the evaluation of the actual LOB data. See "How the Sample LOBEVAL Program Works" for details.
lobfile Yes Demonstrates the use of LOB file handles. See "How the Sample LOBFILE Program Works" for details.
lobloc Yes Demonstrates the use of LOB locators. See "How the Sample LOBLOC Program Works" for details.
loblocud
Demonstrates the use of LOB locators in a user-defined function. See "Example: Function using LOB locators" for details.
lobval Yes Demonstrates the use of LOBs.
makeapi Yes Demonstrates the use of the following APIs:
BIND PRECOMPILE PROGRAM START DATABASE MANAGER STOP DATABASE MANAGER

migrate
Demonstrates the use of the following API:
MIGRATE DATABASE

monreset
Demonstrates the use of the following API:
RESET DATABASE SYSTEM MONITOR DATA AREAS

monsz
Demonstrates the use of the following APIs:
ESTIMATE DATABASE SYSTEM MONITOR BUFFER SIZE DATABASE SYSTEM MONITOR SNAPSHOT

nodecat
Demonstrates the use of the following APIs:
CATALOG NODE CLOSE NODE DIRECTORY SCAN GET NEXT NODE DIRECTORY ENTRY OPEN NODE DIRECTORY SCAN UNCATALOG NODE

openftch Yes Demonstrates fetching, updating, and deleting of rows using static SQL. See "How the Example OPENFTCH SQL Program Works" for details.
outcli Yes Demonstrates stored procedures using the SQLDA structure. This is the client program of a client/server example. (The server program is called outsrv.) This program allocates and initializes a one variable SQLDA, and passes it to the server program for further processing. The filled SQLDA is returned to the client program along with the SQLCA status. This program shows the invocation of stored procedures using an embedded SQL CALL statement. See "How the Example Output-SQLDA Client Application Works" for details.
outsrv Yes Demonstrates stored procedures using the SQLDA structure. This is the server program of a client/server example. (The client program is called outcli.) The program fills the SQLDA with the median SALARY of the employees in the STAFF table of the SAMPLE database. The server program does all the database processing (finding the median). The server program returns the filled SQLDA and the SQLCA status to the client program. See "How the Example Output-SQLDA Stored Procedure Works" for details.
qload Yes Demonstrates the use of the following API:
LOAD QUERY

rebind Yes Demonstrates the use of the following API:
REBIND PACKAGE

rechist
Demonstrates the use of the following APIs:
CLOSE RECOVERY HISTORY FILE SCAN GET NEXT RECOVERY HISTORY FILE ENTRY OPEN RECOVERY HISTORY FILE SCAN PRUNE RECOVERY HISTORY FILE ENTRY UPDATE RECOVERY HISTORY FILE ENTRY

recursql Yes Demonstrates the use of advanced SQL recursive queries.
regder
Demonstrates the use of the following APIs:
REGISTER DEREGISTER

restart
Demonstrates the use of the following API:
RESTART DATABASE

sampudf Yes Demonstrates the use of User-Defined Types (UDTs) and User-Defined Functions (UDFs). The UDFs declared in this program are all sourced UDFs.
setact
Demonstrates the use of the following API:
SET ACCOUNTING STRING

setrundg
Demonstrates the use of the following API:
SET RUNTIME DEGREE

static Yes Uses static SQL to retrieve information. See "Example Static SQL Program" for details.
sws
Demonstrates the use of the following API:
DATABASE MONITOR SWITCH

system
Demonstrates most of the system-specific calls.
tabinfo Yes Provides a library of routines for obtaining table and column information from the system tables and for accessing the information obtained.
tabscont
Demonstrates the use of the following APIs:
TABLESPACE CONTAINER QUERY OPEN TABLESPACE CONTAINER QUERY FETCH TABLESPACE CONTAINER QUERY CLOSE TABLESPACE CONTAINER QUERY SET TABLESPACE CONTAINER QUERY

tabspace
Demonstrates the use of the following APIs:
TABLESPACE QUERY SINGLE TABLESPACE QUERY OPEN TABLESPACE QUERY FETCH TABLESPACE QUERY GET TABLESPACE STATISTICS CLOSE TABLESPACE QUERY

tabsql Yes Demonstrates the use of advanced SQL table expressions.
tblcli
Demonstrates a call to a table function (client-side) to display weather information for a number of cities.
tblsrv
Demonstrates a table function (server-side) that processes weather information for a number of cities.
tload Yes Demonstrates the use of the following APIs:
EXPORT QUIESCE TABLESPACE FOR TABLES LOAD

trigsql Yes An example using advanced SQL triggers and constraints.
udf Yes 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.
updat Yes Uses static SQL to update a database. See "Example UPDAT Program" for details.
util
Demonstrates the use of the following APIs:
GET ERROR MESSAGE GET SQLSTATE MESSAGE INSTALL SIGNAL HANDLER INTERRUPT
This program also contains code to output information from an SQLDA. See "Using GET ERROR MESSAGE in Example Programs" for details.
varinp Yes An example of variable input to Embedded Dynamic SQL statement calls using parameter markers. See "How the Example VARINP SQL Program Works" for details.

Sample Program Name	Embedded SQL	Program Description
`adhoc`	Yes	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. See "Example ADHOC Program" for details.
`advsql`	Yes	Demonstrates the use of advanced SQL expressions like CASE, CAST, and scalar full selects.
`asynrlog`	Yes	Demonstrates the use of the following API: ASYNCHRONOUS LOG READ
`autoloader`		A UNIX Korn shell script that prepares ftp scripts for data transfer from remote hosts and generates a temporary buffer space (FIFO or named pipes). It then starts `db2split` and invokes DB2 LOAD. In a partitioned environment, partitioning keys are used to determine the partition where the data resides. Therefore, data must pass through a splitting phase before it can be loaded at the correct partition. The entire split and load process can be accomplished by the `autoLoader` utility. It uses a system-defined hashing function to partition the data into as many output files as there are partitions in the nodegroup in which the table is defined. It then loads these output files concurrently across the set of partitions in the nodegroup.
`backrest`		Demonstrates the use of the following APIs: BACKUP DATABASE RESTORE DATABASE ROLL FORWARD DATABASE
`blobfile`	Yes	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 which can be displayed using an external viewer.
`bindfile`	Yes	Demonstrates the use of the BIND API to bind an embedded SQL application to a database.
`calludf`	Yes	Demonstrates the use of the library of User-Defined Functions (UDFs) created by `udf` for the SAMPLE database tables.
`client`		Demonstrates the use of the following APIs: SET CLIENT QUERY CLIENT
`columns`	Yes	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.
`cursor`	Yes	Demonstrates the use of a cursor using static SQL. See "Example Cursor Program" for details.
`d_dbconf`		Demonstrates the use of the following API: GET DATABASE CONFIGURATION DEFAULTS
`d_dbmcon`		Demonstrates the use of the following API: GET DATABASE MANAGER CONFIGURATION DEFAULTS
`da_manip`	Yes	Provides a library of routines to manipulate SQLDAs and SQLVARs.
`db2mon`		Demonstrates how to use the Database System Monitor APIs, and how to process the output data buffer returned from the Snapshot API.
`db2uext2`		Provides a sample log management user exit.
`dbauth`	Yes	Demonstrates the use of the following API: GET AUTHORIZATIONS
`dbcat`		Demonstrates the use of the following APIs: CATALOG DATABASE CLOSE DATABASE DIRECTORY SCAN GET NEXT DATABASE DIRECTORY ENTRY OPEN DATABASE DIRECTORY SCAN UNCATALOG DATABASE
`dbcmt`		Demonstrates the use of the following APIs: CHANGE DATABASE COMMENT
`dbconf`		Demonstrates the use of the following APIs: CREATE DATABASE DROP DATABASE GET DATABASE CONFIGURATION RESET DATABASE CONFIGURATION UPDATE DATABASE CONFIGURATION
`dbinst`		Demonstrates the use of the following APIs: ATTACH TO INSTANCE DETACH FROM INSTANCE GET INSTANCE
`dbmconf`		Demonstrates the use of the following APIs: GET DATABASE MANAGER CONFIGURATION RESET DATABASE MANAGER CONFIGURATION UPDATE DATABASE MANAGER CONFIGURATION
`dbsnap`		Demonstrates the use of the following API: DATABASE SYSTEM MONITOR SNAPSHOT
`dbstart`		Demonstrates the use of the following API: START DATABASE MANAGER
`dbstat`	Yes	Demonstrates the use of the following APIs: REORGANIZE TABLE RUN STATISTICS
`dbstop`		Demonstrates the use of the following APIs: FORCE USERS STOP DATABASE MANAGER
`db_udcs`		Demonstrates the use of the following APIs in order to simulate the collating behaviour of a DB2 for MVS/ESA or OS/390 CCSID 500 (EBCDIC International) collating sequence: CREATE DATABASE DROP DATABASE
`dcscat`		Demonstrates the use of the following APIs: ADD DCS DIRECTORY ENTRY CLOSE DCS DIRECTORY SCAN GET DCS DIRECTORY ENTRY FOR DATABASE GET DCS DIRECTORY ENTRIES OPEN DCS DIRECTORY SCAN UNCATALOG DCS DIRECTORY ENTRY
`delet`	Yes	Demonstrates static SQL to delete items from a database.
`dmscont`		Demonstrates the use of the following APIs in order to create a database with more than one database managed storage (DMS) container: CREATE DATABASE DROP DATABASE
`dynamic`	Yes	Demonstrates the use of a cursor using dynamic SQL. See "Example Dynamic SQL Program" for details.
`ebcdicdb`		Demonstrates the use of the following APIs in order to simulate the collating behaviour of a DB2 for MVS/ESA or OS/390 CCSID 037 (EBCDIC US English) collating sequence: CREATE DATABASE DROP DATABASE
`expsamp`	Yes	Demonstrates the use of the following APIs: EXPORT IMPORT in conjunction with a DRDA database.
`fillcli`	Yes	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.
`fillsrv`	Yes	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.
`impexp`	Yes	Demonstrates the use of the following APIs: EXPORT IMPORT
`inpcli`	Yes	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. See "How the Example Input-SQLDA Client Application Works" for details.
`inpsrv`	Yes	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. See "How the Example Input-SQLDA Stored Procedure Works" for details.
`joinsql`	Yes	An example using advanced SQL join expressions.
`largevol`	Yes	Demonstrates parallel query processing in a partitioned environment, and the use of an NFS file system to automate the merging of the result sets. See "Example: Extracting Large Volume of Data (largevol.c)" for details.
`lobeval`	Yes	Demonstrates the use of LOB locators and deferring the evaluation of the actual LOB data. See "How the Sample LOBEVAL Program Works" for details.
`lobfile`	Yes	Demonstrates the use of LOB file handles. See "How the Sample LOBFILE Program Works" for details.
`lobloc`	Yes	Demonstrates the use of LOB locators. See "How the Sample LOBLOC Program Works" for details.
`loblocud`		Demonstrates the use of LOB locators in a user-defined function. See "Example: Function using LOB locators" for details.
`lobval`	Yes	Demonstrates the use of LOBs.
`makeapi`	Yes	Demonstrates the use of the following APIs: BIND PRECOMPILE PROGRAM START DATABASE MANAGER STOP DATABASE MANAGER
`migrate`		Demonstrates the use of the following API: MIGRATE DATABASE
`monreset`		Demonstrates the use of the following API: RESET DATABASE SYSTEM MONITOR DATA AREAS
`monsz`		Demonstrates the use of the following APIs: ESTIMATE DATABASE SYSTEM MONITOR BUFFER SIZE DATABASE SYSTEM MONITOR SNAPSHOT
`nodecat`		Demonstrates the use of the following APIs: CATALOG NODE CLOSE NODE DIRECTORY SCAN GET NEXT NODE DIRECTORY ENTRY OPEN NODE DIRECTORY SCAN UNCATALOG NODE
`openftch`	Yes	Demonstrates fetching, updating, and deleting of rows using static SQL. See "How the Example OPENFTCH SQL Program Works" for details.
`outcli`	Yes	Demonstrates stored procedures using the SQLDA structure. This is the client program of a client/server example. (The server program is called `outsrv`.) This program allocates and initializes a one variable SQLDA, and passes it to the server program for further processing. The filled SQLDA is returned to the client program along with the SQLCA status. This program shows the invocation of stored procedures using an embedded SQL CALL statement. See "How the Example Output-SQLDA Client Application Works" for details.
`outsrv`	Yes	Demonstrates stored procedures using the SQLDA structure. This is the server program of a client/server example. (The client program is called `outcli`.) The program fills the SQLDA with the median `SALARY` of the employees in the `STAFF` table of the `SAMPLE` database. The server program does all the database processing (finding the median). The server program returns the filled SQLDA and the SQLCA status to the client program. See "How the Example Output-SQLDA Stored Procedure Works" for details.
`qload`	Yes	Demonstrates the use of the following API: LOAD QUERY
`rebind`	Yes	Demonstrates the use of the following API: REBIND PACKAGE
`rechist`		Demonstrates the use of the following APIs: CLOSE RECOVERY HISTORY FILE SCAN GET NEXT RECOVERY HISTORY FILE ENTRY OPEN RECOVERY HISTORY FILE SCAN PRUNE RECOVERY HISTORY FILE ENTRY UPDATE RECOVERY HISTORY FILE ENTRY
`recursql`	Yes	Demonstrates the use of advanced SQL recursive queries.
`regder`		Demonstrates the use of the following APIs: REGISTER DEREGISTER
`restart`		Demonstrates the use of the following API: RESTART DATABASE
`sampudf`	Yes	Demonstrates the use of User-Defined Types (UDTs) and User-Defined Functions (UDFs). The UDFs declared in this program are all sourced UDFs.
`setact`		Demonstrates the use of the following API: SET ACCOUNTING STRING
`setrundg`		Demonstrates the use of the following API: SET RUNTIME DEGREE
`static`	Yes	Uses static SQL to retrieve information. See "Example Static SQL Program" for details.
`sws`		Demonstrates the use of the following API: DATABASE MONITOR SWITCH
`system`		Demonstrates most of the system-specific calls.
`tabinfo`	Yes	Provides a library of routines for obtaining table and column information from the system tables and for accessing the information obtained.
`tabscont`		Demonstrates the use of the following APIs: TABLESPACE CONTAINER QUERY OPEN TABLESPACE CONTAINER QUERY FETCH TABLESPACE CONTAINER QUERY CLOSE TABLESPACE CONTAINER QUERY SET TABLESPACE CONTAINER QUERY
`tabspace`		Demonstrates the use of the following APIs: TABLESPACE QUERY SINGLE TABLESPACE QUERY OPEN TABLESPACE QUERY FETCH TABLESPACE QUERY GET TABLESPACE STATISTICS CLOSE TABLESPACE QUERY
`tabsql`	Yes	Demonstrates the use of advanced SQL table expressions.
`tblcli`		Demonstrates a call to a table function (client-side) to display weather information for a number of cities.
`tblsrv`		Demonstrates a table function (server-side) that processes weather information for a number of cities.
`tload`	Yes	Demonstrates the use of the following APIs: EXPORT QUIESCE TABLESPACE FOR TABLES LOAD
`trigsql`	Yes	An example using advanced SQL triggers and constraints.
`udf`	Yes	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.
`updat`	Yes	Uses static SQL to update a database. See "Example UPDAT Program" for details.
`util`		Demonstrates the use of the following APIs: GET ERROR MESSAGE GET SQLSTATE MESSAGE INSTALL SIGNAL HANDLER INTERRUPT This program also contains code to output information from an SQLDA. See "Using GET ERROR MESSAGE in Example Programs" for details.
`varinp`	Yes	An example of variable input to Embedded Dynamic SQL statement calls using parameter markers. See "How the Example VARINP SQL Program Works" for details.

Table 25. Command Line Processor (CLP) Sample Files.

Sample File Name File Description
const.clp Creates a table with a CHECK CONSTRAINT clause.
cte.clp Demonstrates a common table expression. The equivalent sample program demonstrating this advanced SQL statement is tabsql.
flt.clp Demonstrates a recursive query. The equivalent sample program demonstrating this advanced SQL statement is recursql.
join.clp Demonstrates an outer join of tables. The equivalent sample program demonstrating this advanced SQL statement is joinsql.
stock.clp Demonstrates the use of triggers. The equivalent sample program demonstrating this advanced SQL statement is trigsql.
testdata.clp Uses DB2 built-in functions such as RAND() and TRANSLATE() to populate a table with randomly generated test data.

Sample File Name	File Description
`const.clp`	Creates a table with a CHECK CONSTRAINT clause.
`cte.clp`	Demonstrates a common table expression. The equivalent sample program demonstrating this advanced SQL statement is `tabsql`.
`flt.clp`	Demonstrates a recursive query. The equivalent sample program demonstrating this advanced SQL statement is `recursql`.
`join.clp`	Demonstrates an outer join of tables. The equivalent sample program demonstrating this advanced SQL statement is `joinsql`.
`stock.clp`	Demonstrates the use of triggers. The equivalent sample program demonstrating this advanced SQL statement is `trigsql`.
`testdata.clp`	Uses DB2 built-in functions such as RAND() and TRANSLATE() to populate a table with randomly generated test data.

Table 26. Java Sample Programs

Sample Program Name Program Description
DB2Appl.java A Java Database Connectivity (JDBC) application that queries the sample database using the invoking user's privileges.
DB2Applt.java A Java Database Connectivity (JDBC) applet that queries the sample database using a user and server specified as applet parameters.
DB2Applt.html An HTML file that embeds the DB2Applt.java applet sample program. It needs to be customized with server and user information.
DB2Stp.java A Java stored procedure that updates the EMPLOYEE table on the server, and returns new salary and payroll information to the client.
DB2Udf.java A Java user-defined function (UDF) that demonstrates several tasks, including integer division, manipulation of Character Large OBjects (CLOBs), and the use of Java instance variables.
samples.zip A file containing compiled .class files for all DB2 Java samples.

Sample Program Name	Program Description
`DB2Appl.java`	A Java Database Connectivity (JDBC) application that queries the sample database using the invoking user's privileges.
`DB2Applt.java`	A Java Database Connectivity (JDBC) applet that queries the sample database using a user and server specified as applet parameters.
`DB2Applt.html`	An HTML file that embeds the `DB2Applt.java` applet sample program. It needs to be customized with server and user information.
`DB2Stp.java`	A Java stored procedure that updates the EMPLOYEE table on the server, and returns new salary and payroll information to the client.
`DB2Udf.java`	A Java user-defined function (UDF) that demonstrates several tasks, including integer division, manipulation of Character Large OBjects (CLOBs), and the use of Java instance variables.
`samples.zip`	A file containing compiled `.class` files for all DB2 Java samples.

Table 27. 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).
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).
salaryclt A client program that invokes the median salary OLE automation stored procedure salarysrv (implemented in Visual Basic and in Visual C++).

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).
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).
salaryclt	A client program that invokes the median salary OLE automation stored procedure `salarysrv` (implemented in Visual Basic and in Visual C++).

Table 28. 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
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 resume clob 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
Version 2 Samples unchanged
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: Samples marked with a ** are new for this release.
Other files in the samples/cli directory include:

README - Lists all example files.
makefile - Makefile for all files

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]