C 1891601
C 1891601
C 1891601
SC18-9160-01
IBM DB2 Information Integrator
SC18-9160-01
Before using this information and the product it supports, be sure to read the general information under “Notices” on page 61.
This document contains proprietary information of IBM. It is provided under a license agreement and copyright law
protects it. The information contained in this publication does not include any product warranties, and any
statements provided in this manual should not be interpreted as such.
You can order IBM publications online or through your local IBM representative:
v To order publications online, go to the IBM Publications Center at www.ibm.com/shop/publications/order
v To find your local IBM representative, go to the IBM Directory of Worldwide Contacts at
www.ibm.com/planetwide
When you send information to IBM, you grant IBM a nonexclusive right to use or distribute the information in any
way it believes appropriate without incurring any obligation to you.
© Copyright International Business Machines Corporation 2003, 2004. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
© CrossAccess Corporation 1993, 2003
Contents
Chapter 1. Introduction to the clients for Logging . . . . . . . . . . . . . . 32
IBM DB2 Information Integrator Classic Code page support in the DB2 Information
Integrator Classic Federation Windows ODBC driver 33
Federation and Classic Event Publisher . 1
Supported data types . . . . . . . . . . 33
iv DB2 II Client Guide for Classic Federation and Classic Event Publishing
Chapter 1. Introduction to the clients for IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher
This manual provides an overview of the clients that are provided with IBM®
DB2® Information Integrator Classic Federation and Classic Event Publisher. The
clients enable client applications or tools to submit SQL queries to the data server.
JDBC, ODBC, and UNIX® Call Level Interface (CLI) clients are provided.
The clients can be used with IBM DB2 Information Integrator Classic Federation to
enable client applications to access data in any of the data sources that are
connected through the data server, such as IMS™ and VSAM. The clients can be
used with IBM DB2 Information Integrator Classic Event Publisher to access the
metadata catalog on the data server for validation and troubleshooting purposes.
Desktop tools and applications can issue SQL data access requests to a data server
through a JDBC, ODBC, or UNIX Call Level Interface (CLI) client. The JDBC,
ODBC, or UNIX CLI clients provide a single interface between end-user tools and
applications and other IBM DB2 Information Integrator Classic Federation and
Classic Event Publisher operational components. High performance and
application integrity are provided by the 32-bit, thread-safe JDBC, ODBC, and
UNIX CLI clients. A single client can access all data sources on all platforms.
The client serves as a JDBC, ODBC, and UNIX CLI client and a connection handler
to other platforms, leveraging the underlying TCP/IP or WebSphere® MQ
communications backbone.
The following software components interact to enable data access using IBM DB2
Information Integrator Classic Federation:
v A platform-specific ODBC driver manager that loads clients on behalf of an
application. This component is delivered with the operating system for all
supported Microsoft® Windows platforms (for ODBC only).
v The ODBC, JDBC, or UNIX CLI client that processes function calls, submits SQL
requests to a specific data source, and returns results to the application.
v Data source definitions that consist of the name and location of the data the user
wants to access. The required data source definitions consist of a data source
name and communications parameters (TCP/IP or WebSphere MQ). The data
source name is used to identify a specific data server or enterprise server that
will be used to service data access requests.
JDBC applications
JDBC applications can be in the form of Java applets, Java servlets, and Java
applications.
The Java applets that use the JDBC client are subject to the “sandbox” security
restrictions for a Web browser. When you run a two-tier JDBC application that
contains calls from a Java applet to a data server, you must change Web browser
security settings.
This section describes how to use the JDBC client URL to access the JDBC client.
DATASOURCENAME
The name of the remote data source. Valid values are:
v Allowable value type: string
v Representation: string
v Maximum Permitted Value: 18 characters for data source name
v Minimum Permitted Value: 1 character
host name (or IP address)
The server that hosts the data server. This value is used with the port
number or service name to identify the data server that the JDBC client
connects to. If the host server is registered with your network name server,
you can use the host name, otherwise, use the IP_address. The valid host
name values are:
v Allowable value type: alphanumeric
v Representation: string
port number (or service name)
Supplies the host port number (or service name) of the data server. This
value is used with the host name or IP_address to identify the data server
to which the JDBC client connects. If the data server is registered with a
network name server, you can use the data server name; otherwise, you
must use the TCP port number, which is the decimal value of the socket
number. Valid values are:
v Allowable value type: alphanumeric
v Representation: decimal
4 DB2 II Client Guide for Classic Federation and Classic Event Publishing
v TRACELEVEL
v MESSAGECATALOGNAME
Using a URL
The properties can also be passed through the URL. The format is as follows:
jdbc:cac:<DATASOURCENAME>:tcp/p390/7000:prop1=value:prop2=
value
The properties are the same as above for the properties object. No spaces are
allowed in the URL. The properties are not case-sensitive.
env.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY,
com.sun.jndi.fscontext.RefFSContextFactory);
env.put(javax.naming.Context.PROVIDER_URL,
"file:///jndi/naming");
javax.naming.Context ctx =
new javax.naming.InitialContext(env);
ctx.bind("datasourcename",cpds);
} catch(javax.naming.NamingException n) { }
Not shown in the above example but available are the
ConnectionPoolDataSource methods setServerName, setUser, and setPassword.
The methods shown in this step are the same for the DataSource and
ConnectionPoolDataSource classes.
2. Connect to the data source.
The following example shows how to connect to the data source in the JNDI
database:
java.util.Hashtable env = new Hashtable();
env.put(javax.naming.Context.INITIAL_CONTEXT_FACTORY,
com.sun.jndi.fscontext.RefFSContextFactory);
env.put(javax.naming.Context.PROVIDER_URL,
"file:///jndi/naming");
javax.naming.Context ctx =
new javax.naming.InitialContext(env);
com.ibm.cac.jdbc.ConnectionPoolDataSource
cpds = (com.ibm.cac.jdbc.ConnectionPoolDataSource)ctx.lookup("name");
try {
javax.sql.PooledConnection pcon =
cpds.getPooledConnection("user","password");
try {
java.sql.Connection con = pcon.getConnection();
java.sql.PreparedStatement ps =
con.prepareStatement("select * from dbname.tablename");
java.sql.ResultSet rs = ps.executeQuery();
//parse ResultSet and do something with the data
} catch (java.sql.SQLException e) { }
} catch(javax.naming.NamingException n) { }
JNDI classes
Table 1 lists the JNDI class names that applications require to create the connection
objects to the IBM DB2 Information Integrator Classic Federation data server.
Table 1. JNDI classes
JNDI classes JNDI class names
java.sql.Driver com.ibm.cac.jdbc.Driver
javax.sql.DataSource com.ibm.cac.jdbc.DataSource
javax.sql.ConnectionPoolDataSource com.ibm.cac.jdbc.ConnectionPoolDataSource
javax.sql.XADataSource com.ibm.cac.jdbc.XADataSource
6 DB2 II Client Guide for Classic Federation and Classic Event Publishing
The following table describes each of these parameters.
Table 3. WebSphere MQ connection parameters
Parameter name Description
mqi Required. This lets the JDBC client and the data server
know that you are using WebSphere MQ.
Source queue manager name Required. The name of the queue manager where a
model queue is defined for use as a dynamic local
queue (the local endpoint).
Source model queue Required. The name of the model queue, defined under
the source queue manager, on which a dynamic queue
will be generated (the local endpoint).
Destination queue manager name Required. The name of the queue manager that owns
the queue on which the data server is listening.
Destination queue name Required. The name of the queue on which the server
is listening.
Host name of the channel Required on Solaris. Optional on all other platforms.
Used in conjunction with Channel name, establishes a
TCP/IP client connection to the source queue, rather
than a locally-bound connection.
Channel names Required on Solaris. Cannot be used on z/OS®.
Optional on all other platforms. Used in conjunction
with host name of the channel, establishes a TCP/IP
client connection to the source queue.
The final two parameters in the connection string are optional, unless you are
running Solaris. On Solaris, you must specify the channel parameters. On z/OS,
they cannot be used. But on all other operating systems, they are optional.
If the parameters are absent, WebSphere MQ will attempt to use local bindings
(JNI to MQ routines specific to the platform). If they are present, WebSphere MQ
will use a TCP/IP connection to the specified channel/source queue.
If you do not provide the channel parameters, you must leave the extra slash at
the end of the connection string:
mqi/QM_SOURCEMGR/SOURCE_Q/QM_DESTMGR/DEST_Q//
java.sql.properties
This section contains descriptions of the properties objects that can be used with
the JDBC clients.
CODEPAGE
Description: Optional parameter that specifies the code page to use to convert
characters between systems. Java provides code pages to convert characters
between various formats, such as EBCDIC to ASCII.
Restrictions:
v Use CODEPAGE=USS only when the environment is pure USS and the local
code page is EBCDIC.
v If the JVM running on USS is configured to use ASCII, do not use
CODEPAGE=USS.
FETCHBUFFERSIZE
Description: Optional parameter that specifies the size of the result set buffer that
is returned to a client application. This is specified in the client application’s
configuration file.
Regardless of the size of the fetch buffer specified, IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher always returns a complete row of
data in this buffer. Setting the fetch buffer size to 1 causes IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher to return single rows of
data to the client application.
If the FETCHBUFFERSIZE is set smaller than a single result set row, then the size
of the actual fetch buffer that is transmitted is based on the result set row size. The
size of a single result set row in the fetch buffer depends on the number of
columns in the result set and the size of the data returned for each column.
The following calculations can be used to determine the size of a result set row in
the buffer:
fetchbufferrowsize = (number of data bytes returned) x
(number of columns * 6)
There is also a fixed overhead for each fetch buffer. This can be computed as:
fetchbufferoverhead = 100 + (number of columns *8)
If your applications are routinely retrieving large result sets you will want to
contact your network administrator in order to determine the optimum
communication packet size and set the FETCHBUFFERSIZE to a size that takes this
into account.
Example:
FETCHBUFFERSIZE = 64000
Representation: decimal
8 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Minimum permitted value: 1
Default: 64000
MESSAGECATALOGNAME
Description: Required parameter that specifies the full path name of the language
catalog. The language catalog contains system messages in a specified language
and is pointed to by a file contained within the IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher configuration files. System
messages include errors generated in the data server and created on the client side.
The default catalog is engcat, or English Catalog, the only supported catalog in
this version of JDBC.
Representation: string
RESPONSETIMEOUT
Description: Optional parameter that specifies the response time-out. This value
specifies the maximum amount of time, in milliseconds, that this service waits for
an expected response before terminating a connection. The default is 0, wait
forever (do not time out). All other values will ultimately cause a timeout error
and request an end to query processing.
Example:
RESPONSETIMEOUT = 10M
Representation: decimal
Default: 0M
TRACELEVEL
Description: Optional parameter that regulates the amount of information placed
into trace log by data server tasks. Any non-zero number turns on the diagnostic
tracing. Trace information is recorded by JDBC in the JDBC system log. Tracing can
be resource intensive and is not recommended unless you need to debug system
problems.
Example:
TRACELEVEL = 0
Representation: decimal
Default: 0
Batch operations
SQL Batch Operations are supported. Note the following details:
v For java.sql.Statement, an executeUpdate, executeQuery, or execute(sql) with
an UPDATE/DELETE/INSERT statement will cause the update to be executed
even when Batch operations are pending.
v For java.sql.PreparedStatement, an executeUpdate, executeQuery, or execute()
with an UPDATE/DELETE/INSERT statement will cause the update to be
executed even when the Batch operations are pending, using the parameter
markers set before the first addBatch operation on the statement.
v executeBatch() returns an array of integers that indicate the number of rows
affected. It stops if there is an error in execution of any of the statements, and
will return only the number of integers that were successfully executed. If a
statement returns a ResultSet, the executeBatch treats it as a failure and returns
the array of integers up to that point.
By default, these statements create a statement that creates a result set that is
TYPE_FORWARD_ONLY and which is CONCUR_READ_ONLY.
10 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Connector API
The following sections detail the classes that you can use to connect to the IBM
DB2 Information Integrator Classic Federation and Classic Event Publisher JDBC
drivers.
DataSource
Use a DataSource object when you manage connection pooling on your own, or
when you do not desire to use connection pooling. If you want connection pooling
and do not want to manage it on your own, use a ConnectionPoolDataSource
object (see “ConnectionPoolDataSource” on page 14).
getConnection
Input parameters: Two java.lang.String parameters
If the DataSource object does not have enough information to initiate a connection,
it will throw an SQL connect exception.
See also “getConnection” on page 11, for a version of the method that does not
take input parameters.
getConnection
Input parameters: None
getDatabaseName
Input parameters: None
Description: Returns the name of the database. If the database name has not been
set, it returns null.
getDataSourceName
Input parameters: None
Description: Returns the name of the data source that was set for the object. If the
data source name has not been set, it returns null.
getDescription
Input parameters: None
getLoginTimeout
Input parameters: None
Description: Returns the timeout value for logging into the database.
getLogWriter
Input parameters: None
Description: Returns the PrintWriter being used to write to the log. If the log
PrintWriter has not been set, it returns null.
getPassword
Input parameters: None
Description: Returns the specified password. If the password has not been
specified, it returns null.
getPort
Input parameters: None
Description: Returns the port that was set on the object. If the port has not been
set, it returns null.
getPortNumber
See “getPort” on page 12.
getReference
Input parameters: None
getServerName
Input parameters: None
Description: Returns the name of the server. If the server name has not been
specified, it returns null.
getUrl
Input parameters: None
12 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Description: Returns the connection URL used to provide the object with enough
information to make a connection.
getUser
Input parameters: None
Description: Returns the user name. If the user name has not been specified, it
returns null.
setDatabaseName
Input parameters: java.lang.String
setDescription
Input parameters: java.lang.String
setLoginTimeout
Input parameters: int
setLogWriter
Input parameters: java.io.PrintWriter
setPassword
Input parameters: java.lang.String
setPort
Input parameters: java.lang.String
setPortNumber
See “setPort” on page 13.
setUrl
Input parameters: java.lang.String
Description: Sets the URL for the object. The URL provides all of the required
connection information for the object in a single location. Use this method instead
of the individual setDatabaseName, setServerName, setPort, setPassword, and
setUser methods.
setUser
Input parameters: java.lang.String
ConnectionPoolDataSource
Use the ConnectionPoolDataSource class when you want to have IBM DB2
Information Integrator Classic Federation and Classic Event Publisher manage
connection pooling for you. If you want to manage connection pooling outside of
IBM DB2 Information Integrator Classic Federation and Classic Event Publisher, or
do not want to use connection pooling, use a DataSource object instead (see
“DataSource” on page 11). If you will be performing distributed transactions, use a
XADataSource object instead (see “XADataSource” on page 16).
getDescription
See “getDescription” on page 11.
getDatabaseName
See “getDatabaseName” on page 11.
getLoginTimeout
See “getLoginTimeout” on page 12.
getLogWriter
See “getLogWriter” on page 12.
getPassword
See “getPassword” on page 12.
getPooledConnection
Input parameters: None
Description: There are two signatures for this method. This first one does not take
any input parameters, and returns a connection from the connection pool. See
“getPooledConnection” on page 15 for the other signature for this method.
14 DB2 II Client Guide for Classic Federation and Classic Event Publishing
getPooledConnection
Input parameters: Two java.lang.String parameters.
Description: There are two signatures for this method. This version takes two
strings as input parameters. The first string specifies the URL; the second string
specifies the connection properties. See “getPooledConnection” on page 14 for the
other signature for the method.
getPort
See “getPort” on page 12.
getPortNumber
See “getPortNumber” on page 12.
getReference
See “getReference” on page 12.
getServerName
See “getServerName” on page 12.
getUrl
See “getUrl” on page 12.
getUser
See “getUser” on page 13.
setDatabaseName
See “setDatabaseName” on page 13.
setDescription
See “setDescription” on page 13.
setLoginTimeout
See “setLoginTimeout” on page 13.
setLogWriter
See “setLogWriter” on page 13.
setPassword
See “setPassword” on page 13.
setPort
See “setPort” on page 13.
setPortNumber
See “setPort” on page 13.
setServerName
See “setServerName” on page 14.
setUrl
See “setUrl” on page 14.
setUser
See “setUser” on page 14.
getXAConnection
Input parameters: Two java.lang.String objects
If a connection cannot be established with the information provided for the object,
it throws an SQL connect exception.
getXAConnection
Input parameters: None
You specify code pages for the java.sql.Connection object. When you create objects
for the connection object, these objects inherit the code page properties of that
connection object.
To specify the code page as part of the URL, use the optional codepage parameter.
The codepage parameter default value is Cp500, which is the Unicode to EBCDIC
code page.
16 DB2 II Client Guide for Classic Federation and Classic Event Publishing
jdbc:cac:CACSAMP:tcp/p39d/8095:codepage=Cp933
The property name is codepage and the associated method name is public void
setCodepage(String codepage).
Examples:
The following example uses TCP/IP as the transport mechanism to set the
codepage parameter.
jdbc:cac:CACSAMP:tcp/p39d/8091:CODEPAGE=USS
The following example uses WebSphere MQ as the transport mechanism to set the
codepage parameter.
jdbc:cac:CACSAMP:mqi/Queue1:CODEPAGE=USS
18 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Chapter 3. ODBC client for windows
Microsoft’s Open Database Connectivity (ODBC) interface allows applications to
use Structured Query Language (SQL) to access data in database management
systems.
The Driver Manager and the client appear to an application as one unit that
processes ODBC function calls.
The ODBC client provides access to data in servers from a Windows platform.
You can define many data sources on a single system. For example, a single IMS
system can have a data source called MARKETING_INFO and a data source called
CUSTOMER_INFO. Each data source name should provide a unique description of
the data.
Configuration prerequisites
The following information must be available before attempting to configure the
ODBC client. If you are missing any of this information, see your system
administrator.
v Name of the data source to define in the Microsoft ODBC Administrator.
v TCP/IP specific information:
– The IP address for the host system where the server runs.
– The port number assigned to the TCP/IP connection handler in the SERVICE
INFO ENTRY parameter of the server.
v WebSphere MQ specific information:
– The name of the WebSphere MQ Queue Manager that is used to communicate
with the z/OS data or enterprise server.
– The name of the Local/Remote Queue Definition that the z/OS data or
enterprise server listens on for SQL requests from ODBC clients.
– The name of the Model Queue that the ODBC client receives responses on
from the data or enterprise server.
Before configuring the ODBC client, be sure that the Windows client is set up for
the connection handler (CH) you want to use, either TCP/IP or WebSphere MQ.
See “Configuring TCP/IP communications” on page 22, for more information.
This dialog box displays a list of data sources and connectors under the User
DSN tab.
20 DB2 II Client Guide for Classic Federation and Classic Event Publishing
2. Click Add under the User DSN tab.
The Create New Data Source dialog box appears.
UNIX ODBC CLI Configuration File Server
Communication
SERVICE INFO ENTRY = CACINIT...protocol_address
3. Select IBM DB2 Information Integrator z/OS ODBC Driver from the list.
4. Click Finish.
The Communications Protocol dialog box appears.
Data Server
ODBC Client
Enterprise Server
WebSphere MQ WebSphere MQ
Transport Module Transport Module
WebSphere MQ WebSphere MQ
Client Server
CAC.Server
SQL requests
Local Queue
SQL Responses
Model Queue
5. Select either TCP/IP or WebSphere MQ, to use with the data source that you
are configuring.
6. Click OK.
From the setup dialog boxes, you can enter parameters for new data sources or
modify parameters for existing data sources. Many of the parameters must match
the values specified in the data server configuration. If you do not know the
settings for these parameters, contact the data server administrator.
ODBC 3.51/CLI
The ODBC 3.51/CLI product is a new version of the existing IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher ODBC 2.0 product. This
product includes all the necessary APIs and functionality to conform to the core
level specification of ODBC 3.51.
In defining the core-level specification for ODBC 3.x, Microsoft incorporated the
required features defined in the ISO/IEC and X/Open CLI standards. This makes
it possible to provide a single set of APIs that are usable by both ODBC and CLI
applications. A CLI application header file, caccli.h, is provided for CLI
applications running in non-Win32 environments. This header file replaces the
Microsoft ODBC header files sql.h and sqlext.h. The prototypes in caccli.h
include only the CLI subset of the ODBC API function prototypes.
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/odbc/htm/dasdkodbcoverview.asp
It can also be downloaded free as part of the latest version of the Microsoft data
access SDK from:
http://www.microsoft.com/data/download_MDAC_SDK.htm
Because ODBC includes several APIs that are not part of the CLI specification, see
“Implemented APIs” on page 23 for a list of APIs that are available when
developing CLI applications.
This information in this document is intended to cover material not included in the
ODBC help file. For information on using any of the APIs or descriptions of error
states, please refer to the ODBC help file.
22 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Implemented APIs
The following APIs are implemented in the IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher ODBC 3.51 CLI product. These APIs
are available to both ODBC and CLI applications unless otherwise specified.
Table 5. API list
ODBC API name Comments
SQLAllocConnect
SQLAllocEnv
SQLAllocHandle
SQLAllocStmt
SQLBindCol
SQLBindParam (CLI only)
SQLBindParameter (ODBC only)
SQLCancel
SQLColAttribute
SQLColumns
SQLConnect
SQLCopyDesc
SQLCloseCursor
SQLDataSources
SQLDescribeCol
SQLDescribeParam
SQLDisconnect
SQLDriverConnect (ODBC only)
SQLEndTran
SQLError
SQLExecDirect
SQLExecute
SQLFetch
SQLFetchScroll Support for scrollable result sets is
limited to SQLFetchScroll support
with a fetch orientation of
SQL_FETCH_NEXT.
SQLFreeConnect
SQLFreeEnv
SQLFreeHandle
SQLFreeStmt
SQLGetData
SQLGetDescField
SQLGetDescRec
SQLGetDiagField
SQLGetDiagRec
SQLGetConnectAttr
24 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Table 6. Deprecated APIs (continued)
Deprecated API ODBC 3.x replacement
SQLSetParam SQLBindParameter
SQLSetScrollOption SQLSetStmtAttr
SQLSetStmtOption SQLSetStmtAttr
SQLTransact SQLEndTran
Win32 Considerations: The ODBC 3.x driver manager is required to use the IBM
DB2 Information Integrator Classic Federation and Classic Event Publisher ODBC
3.51 driver. This version of the driver manager automatically supports both 3.x
applications and older, pre-3.x applications. Calls to deprecated APIs by older
applications are automatically re-mapped to the new 3.x APIs.
SQLBindParam description
SQLBindParam is a CLI-only API call, and so it is not described in the ODBC help
file. It is essentially the same as the ODBC SQLBindParameter API, with the
omission of the parameter type and buffer length arguments (arguments 3 and 9).
The SQLBindParameter description for the parameters other than InputOutputType
26 DB2 II Client Guide for Classic Federation and Classic Event Publishing
and BufferLength in the ODBC documentation can be used as reference material
for SQLBindParam. All parameters bound with SQLBindParam are assumed to be
INPUT.
See “Stored procedure considerations for CLI applications” on page 27, for
information on binding OUTPUT and INOUT parameters.
SQLCancel considerations
In the ODBC 2.0 product, SQLCancel disconnected from the server and deleted all
statements on a connection. In the 3.51 product, SQLCancel can cancel a single
query without impacting any other statements allocated to the connection. Because
asynchronous mode is not supported, SQLCancel must be issued in a separate
program thread from the thread actually issuing the query that needs to be
cancelled.
For SQLCancel to succeed, the server INTERLEAVE LEVEL parameter must be set
to a non-0 value to successfully cancel statements. Interleaving permits checking
for additional messages (such as cancel) from clients while processing an SQL
request. See “INTERLEAVE INTERVAL,” the IBM DB2 Information Integrator
Administration Guide and Reference for Classic Federation and Classic Event Publishing
for more information about the parameter.
To bind OUTPUT and INOUT parameters from a CLI application, programs must
retrieve the Implementation Parameter Descriptor after calling SQLBindParam and
modify the descriptors parameter mode field using SQLSetDescField. For example,
to bind an INOUT parameter for a stored procedure call, the application program
might issue the following API calls.
/* Bind an 8 byte character parameter. CLI assumes the parameter */
/* is INPUT, mode must be changed to INOUT after the bind */
sqlrc = SQLBindParam( hStmt, 1, SQL_CHAR, SQL_CHAR,
8, 0, DataPtr, IndPtr );
/* Retrieve the implementation parameter descriptor */
if ( sqlrc == SQL_SUCCESS )
sqlrc = SQLGetStmtAttr( hStmt, SQL_ATTR_IMP_PARAM_DESC,
&hIPD, sizeof(hIPD), NULL );
/* Change the parameter’s mode from the INPUT default to OUTIN */
if ( sqlrc == SQL_SUCCESS )
sqlrc = SQLSetDescField( hIPD, 1, SQL_DESC_PARAMETER_MODE,
(SQLPOINTER) SQL_PARAM_MODE_INOUT,
sizeof( SQLPOINTER ) );
The configuration file is standard text file that contains tokens and values defining
environment parameters and data source information. The following is a sample
file:
MESSAGE POOL SIZE = 40000000
TRACE LEVEL = 8
FETCH BUFFER SIZE = 64000
RESPONSE TIME OUT = 10M
DEFLOC = LCLSAMP
datasource = LCLSAMP tcp/lclpc/7110
datasource = MVSSAMP tcp/mvs390/7111
NL = US English
NL CAT = \Program Files\IBM\DB2IIClassic82\ODBC\engcat
The ODBC driver for Microsoft Windows can be configured using the Windows
ODBC Data Source Manager or a DB2 Information Integrator ODBC Administrator,
which saves configuration values in the system registry.
The following dialogs show the DB2 Information Integrator z/OS ODBC Driver
setup dialog (CACCFG32.DLL), which is opened by the Microsoft Windows ODBC
28 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Data Source Administrator.
In Windows 32-bit and UNIX environments, the log file created is named
CACLOG and is placed in the same directory as the ODBC/CLI driver itself. This
file is overwritten each time the ODBC/CLI driver executes.
At this time, there are only two types of information logged by the ODBC/CLI
software:
v Creation of diagnostic messages. Any time an API call results in the creation of a
diagnostic record due to an ERROR or INFO situation, the message is logged. If
the message is an error message, then logging will take place if the TRACE
32 DB2 II Client Guide for Classic Federation and Classic Event Publishing
LEVEL is less than 8. If the message is an INFO message, then logging will take
place if the TRACE LEVEL is less than 3.
v API call entry and exit with return code. With few exceptions, API calls start
with validation of a passed handle and end with unlocking of the passed
handle. The API called and the return code are logged if trace level is set to 1. In
cases where and invalid handle is passed or an SQL_ERROR is returned, the
logging takes place if TRACE LEVEL is set to any value less than 8.
Logging is not recommended for ODBC applications, because the log file cannot be
shared by multiple application processes and ODBC has a tracing facility which
includes all the functionality built into the IBM DB2 Information Integrator Classic
Federation and Classic Event Publisher drivers.
The log file itself is a binary file that must be formatted and printed using the
CACPLOG utility.
The form of character data that DB2 Information Integrator Classic Federation
supports is mixed-mode SBCS data. Mixed-mode character data can be strictly SBCS
data or can include DBCS data. ODBC driver support includes conversion of
graphic data types and bi-directional languages.
You can use the ODBC Data Source Administrator interface to define client and
server code pages when you configure the ODBC data source. “Configuring the
ODBC/CLI driver” on page 28 shows the dialogs where you specify code page
settings. For detailed information about the CCSIDs that DB2 Information
Integrator Classic Federation supports, see IBM DB2 Information Integrator
Administration Guide and Reference for Classic Federation and Classic Event Publishing.
34 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Chapter 4. CLI client for UNIX
The UNIX Call Level Interface (CLI) client that is provided with DB2 Information
Integrator Classic Federation for z/OS allows applications to use Structured Query
Language (SQL) to access data in both relational and nonrelational database
management systems.
The data source name is equivalent to the DATASOURCE (field 1) in the IBM DB2
Information Integrator Classic Federation and Classic Event Publisher system
configuration file.
Defining a data source consists of defining the service name and communication
parameters (TCP/IP) to determine the data server with which the client is
communicating.
The driver manager and the CLI client appear to an application as one unit that
processes CLI function calls.
The UNIX CLI client provides access from a UNIX client application or tool to data
in data servers. The CLI clients communicate through a connection handler to
access all databases defined throughout the IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher network. Each CLI instance can
service multiple client applications and/or client tools concurrently.
Configuring the CLI client consists of editing and customizing client configuration
parameters.
For specific information about the available settings for a configuration parameter,
see Appendix A, “Configuration parameters,” on page 39.
Configuration steps
The following configuration example accesses a DATASOURCE defined as
CACSAMP on the host.
Note:: Parameters not mentioned in this section should only be changed at the
request of IBM Technical Support.
1. Edit the file odbc.ini in the application’s CLI software directory where the
driver manager resides. Add a new data source for the IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher UNIX CLI client. The
data source name must correspond to a query processor name defined on the
data server and a DATASOURCE name defined in the client configuration.
For example:
[ODBC data sources]
CACSAMP=DB2 Information Integrator Classic Federation and Event Publisher client
.
.
.
[CACSAMP]
client=/opt/IBM/DB2IIClassic82/CLI/cacdrv
You must add a data source definition for each IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher DATASOURCE you want to
access.
The IBM DB2 Information Integrator Classic Federation and Classic Event
Publisher clients are as follows:
v AIX: cacdrv
v HP-UX: libcacdrv.sl
v Solaris: libcacdrv.so
2. Go to the IBM DB2 Information Integrator Classic Federation and Classic Event
Publisher installation directory (/opt/IBM/DB2IIClassic82/CLI/) and open the
sample configuration file cac.ini.
The file is as follows.
*********************************************************
* Sample configuration file *
*********************************************************
* messages and codes catalog
NL CAT = /opt/IBM/DB2IIClassic82/CLI/engcat
NL = US English
* user id/pwd needed for catalog security
USERID = CACUSER
USERPASSWORD = CACPWD
* default datasource location
DEFLOC = CACSAMP
DATASOURCE = CACSAMP tcp/111.111.111.111/nnnn
* performance and memory parameters
FETCH BUFFER SIZE = 32000
MESSAGE POOL SIZE = 1000000
3. Edit the DATASOURCE configuration parameter.
The DATASOURCE configuration parameter identifies the data server and a
data source within the data server that the application must access. If the
application will communicate with multiple data servers or data sources within
a data server, a DATASOURCE configuration parameter must be defined for
each data server or data source to be accessed.
The subparameters on the DATASOURCE parameter identify:
v The data source name
v The type of connection handler service to use to access the data server
36 DB2 II Client Guide for Classic Federation and Classic Event Publishing
v The name of the listen port that a connection handler service in the data
server users (for TCP/IP only)
The following diagram shows the relationship between the DATASOURCE
parameter and the SERVICE INFO ENTRY parameters in the data server that
are required for IBM DB2 Information Integrator Classic Federation and Classic
Event Publisher client-to-data server communication.
Communication
SERVICE INFO ENTRY = CACINIT...protocol_address
Note: Obtain the communication mode used to communicate with the data
server. TCP/IP can be used to access the data server from the client.
For TCP/IP communication, enter the DATASOURCE as:
DATASOURCE = sourcename tcp/hostname/portnumber
5. Create an environment variable CAC_CONFIG and set it to point to the UNIX CLI
client configuration file cac.ini.
6. Create a library environment variable to include the directories where the IBM
DB2 Information Integrator Classic Federation and Classic Event Publisher
shared libraries are installed:
v AIX: LIBPATH
v HP-UX: SHLIB_PATH
v SOLARIS: LD_LIBRARY_PATH
7. Execute the CLI application in this environment.
Example for AIX:
export CAC_CONFIG=/opt/IBM/DB2IIClassic82/CLI/cac.ini
export LIBPATH=/lib:/opt/IBM/DB2IIClassic82/CLI
program1
Example:
parameter name = value
In the example:
v Parameter name is one or more keywords beginning in the first column of the
record,
v There must be one blank on either side of the equal sign,
v Value is any number of characters up to the end of the record,
v String values are not surrounded by delimiters, and
v Comments after the value are not allowed.
The maximum parameter length is 255 characters, but you can continue parameters
across 80-byte records by using the backslash (\) as a continuation character. You
cannot use the continuation character until after the equal sign, and it must be the
last non-blank character of the record. The backslash character is discarded, as are
leading blanks on the continued record. Comment lines might be inserted between
the continued records.
The result of this continuation line is the same as the following DATASOURCE:
DATASOURCE = CACSAMP tcp/111.111.111.11/2222
CATALOG NAME
Description: Required parameter that specifies the full path name of the language
catalog. The language catalog contains messages in a specified language and is
pointed to by a file contained within the IBM DB2 Information Integrator Classic
Federation and Classic Event Publisher configuration files.
Representation: string
CLIENT CODEPAGE
Description: Optional parameter that specifies the client code page value that
ICU4C uses for decoding to this code page from the server code page and
decoding to the server code page from this code page. This parameter corresponds
to the code page converter names for the CCSID that is used on the client and on
the server. ICU4C provides conversion between code pages.
Example:
CLIENT CODEPAGE = IBM-970
DATASOURCE
Description: Required parameter that is used to specify the name of the data
source a client is attempting to connect to. Field 1 is the name of the remote data
source which matches the service name (field 2) of the SERVICE INFO ENTRY
parameter in the data server’s query processor task. Field 2 is the address field by
which this client connects to the named data source. This field consists of three
parts separated by the backslash (/) character and must match the service
information field (field 10) of the SERVICE INFO ENTRY parameter in the data
server’s connection handler task.
v Sample address field for TCP/IP Protocol with data source name, CACSAMP:
The first part of the field must be set to tcp.
The second part of the field is the hostname (string) of the server or the IP
address of the server. If an IP address is specified, it must be defined in dot
notation (123.456.789.10).
The third part of the field is the port number (decimal value), or service name
on which the server is listening for connection requests.
Example:
DATASOURCE = CACSAMP tcp/host1/socket#
Representation: string
Maximum Permitted Value: 18 characters for data source name; 64 characters for
address field
40 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Default: None
ENABLE TRACE
Description: Optional parameter that generates an ODBC trace when enabled.
Regardless of the size of the fetch buffer specified, IBM DB2 Information Integrator
Classic Federation and Classic Event Publisher always returns a complete row of
data in this buffer. Setting the fetch buffer size to 1 causes IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher to return single rows of
data to the client application.
Setting an appropriate FETCH BUFFER SIZE depends upon the average size of the
result set rows that are sent to the client application and the optimum
communication packet size. From a performance standpoint, you will want to pack
as many rows as possible into a fetch buffer. The default fetch buffer size is
generally adequate for most queries.
If the FETCH BUFFER SIZE is set smaller than a single result set row, then the size
of the actual fetch buffer that is transmitted is based on the result set row size. The
size of a single result set row in the fetch buffer depends on the number of
columns in the result set and the size of the data returned for each column.
The following calculations can be used to determine the size of a result set row in
the buffer:
fetch buffer row size = (number of data bytes returned) x
(number of columns * 6)
There is also a fixed overhead for each fetch buffer. This can be computed as:
fetch buffer overhead = 100 + (number of columns *8)
If your applications are routinely retrieving large result sets you will want to
contact your network administrator in order to determine the optimum
communication packet size and set the FETCH BUFFER SIZE to a size that takes
this into account.
Example:
FETCH BUFFER SIZE = 64000
Representation: decimal
Example:
MESSAGE POOL SIZE = 16777216
Representation: decimal
Default: 1048575
NL CAT
Description: Required parameter that defines the directory where the message
catalogs are installed. The drivers automatically access the message catalogs, based
on the locale.
Example:
NL CAT = /opt/IBM/DB2IIClassic82/CLI/engcat
Representation: string
Note: For CLI drivers, the Japanese catalog file is cacmsg_ja_JP.cat. The Japanese
catalogs are distributed in SJIS and eucJP code pages. Rename the code page you
want to use to cacmsg_ja_JP.cat.
42 DB2 II Client Guide for Classic Federation and Classic Event Publishing
v nS = number of seconds
v nM = number of minutes
Example:
RESPONSE TIME OUT = 10M
Representation: decimal
Default: 6M
SERVER CODEPAGE
Description: Optional parameter that specifies the server code page value that
ICU4C uses for encoding to this code page from the client code page and encoding
to the client code page from this code page. This parameter corresponds to the
code page converter names for the CCSID that is used on the client and on the
server. ICU4C provides conversion between code pages.
Example:
SERVER CODEPAGE = IBM-933
NOTE: If a directory is not indicated, it will be created under the directory of the
front-end tool used for the query under Program Files.
TRACE LEVEL
Description: Optional parameter that regulates the amount of information placed
into trace log by data server tasks.
Example:
TRACE LEVEL = 4
Representation: decimal
Default: 4
Warning: This parameter should only be changed at the request of IBM Technical
Support. Settings lower than 4 will cause response time degradation.
USERID
Description: Optional parameter that is the default SQL ID if no ID is present on a
CONNECT statement or if a dynamic CONNECT is issued due to the client
application not issuing a CONNECT statement.
Example:
USERID = CACUSER
USERPASSWORD
Description: Optional parameter that is the default SQL ID password if no
password is present on a CONNECT statement or if a dynamic CONNECT is
issued due to the client application not issuing a CONNECT statement.
Example:
USERPASSWORD = CACPWD
44 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Appendix B. WebSphere MQ configuration
This appendix assumes that you are familiar with WebSphere MQ concepts and
terminology and that you have a working knowledge of how to configure and
operate WebSphere MQ on Microsoft Windows platforms.
Important: IBM DB2 Information Integrator Classic Federation and Classic Event
Publisher supports WebSphere MQ on Microsoft Windows platforms only.
Websphere MQ support is not provided on UNIX platforms.
See the IBM DB2 Information Integrator Installation Guide for Classic Federation and
Classic Event Publishing for information about the versions of WebSphere MQ that
are supported.
Conceptual overview
IBM DB2 Information Integrator Classic Federation and Classic Event Publisher can
use WebSphere MQ as a transport mechanism between Windows clients and data
servers (or enterprise servers). The IBM DB2 Information Integrator Classic
Federation and Classic Event Publisher WebSphere MQ implementation is referred
to as a transport layer because IBM DB2 Information Integrator Classic Federation
and Classic Event Publisher does not use any advanced WebSphere MQ facilities
such as message persistence or two-phase commit protocols. For Windows clients,
using WebSphere MQ as a transport mechanism still uses TCP/IP as the
underlying transport mechanism between the client and the data server (or
enterprise server).
For IBM DB2 Information Integrator Classic Federation and Classic Event Publisher
to use WebSphere MQ as a transport vehicle, at a minimum two queue definitions
are required. One of these queue definitions is a local queue on which the data
server or enterprise server listens for requests from clients. The other queue is a
temporary dynamic model queue that is used by clients. The client connects to the
local queue definition and sends messages to that queue for processing by the data
server or enterprise server. The data server or enterprise server puts response
messages on the instance of the temporary dynamic queue (that is created when
Data Server
ODBC Client
Enterprise Server
WebSphere MQ WebSphere MQ
Transport Module Transport Module
WebSphere MQ WebSphere MQ
Client Server
CAC.Server
SQL requests
Local Queue
SQL Responses
Model Queue
Figure 2. Basic IBM DB2 Information Integrator Classic Federation and Classic Event
PublisherWebSphere MQ architecture
In the diagram, the CAC.CLIENT queue is the temporary dynamic model queue
on which the data server places messages in response to SQL Requests from a
client. During initialization processing, the client opens the CAC.CLIENT
temporary dynamic queue that causes WebSphere MQ to create a unique queue
46 DB2 II Client Guide for Classic Federation and Classic Event Publishing
name for use by the client. When the client sends a message to the CAC.SERVER
local queue, the MQ message header contains the name of the reply-to queue,
which in this case is the unique name of the CAC.CLIENT queue assigned to the
client by WebSphere MQ. After the data server has finished processing a client
SQL request, the z/OS WebSphere MQ transport module sends a message to the
reply-to queue name identified in the originating message from the client.
Note: You can use any queue name that want for the local and temporary
dynamic queue names. The use of CAC.CLIENT and CAC.SERVER are used
for illustrative purposes only.
Data Server
ODBC Client
Enterprise Server
WebSphere MQ WebSphere MQ
Transport Module Transport Module
Microsoft Windows/UNIX
CAC.REMOTE CAC.SERVER
CAC.CLIENT
Model Queue
Specifically, IBM DB2 Information Integrator Classic Federation and Classic Event
Publisher assume the following:
v The WebSphere MQ z/OS queue manager has been installed and configured for
communications between any other queue managers that IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher will be using (or
passing through) and/or the WebSphere MQ clients that will be connecting to
the z/OS queue manager.
v The Windows WebSphere MQ client (or a Windows WebSphere MQ server) has
been installed on the Windows workstation where the IBM DB2 Information
Integrator Classic Federation and Classic Event Publisher client is/will be
installed.
v You have tested connectivity between all WebSphere MQ components by putting
and getting messages with the WebSphere MQ supplied utility programs.
Note: If you do not have your own queues for test purposes you can put and
get test messages from the local queue that the data or enterprise server
will be using. When the server starts up, it will retrieve the test messages,
determine that a reply-to queue does not exist, and then discard the
message(s) from the local queue.
48 DB2 II Client Guide for Classic Federation and Classic Event Publishing
The following tables identify the values that must be supplied depending upon
whether the Windows client will be communicating directly with the z/OS
WebSphere MQ queue manager or going through an intermediate Windows queue
manager.
Table 8. Required information for direct z/OS connections
Information Description
Queue manager name The 4-character name of the z/OS WebSphere MQ queue
manager that the data or enterprise server has been
configured to connect to.
Server queue name The name of the local queue that the data or enterprise server
has been configured to listen on for connections requests. In
the example in Figure 2 on page 46, the Model queue name is
CAC.SERVER.
Model queue name The name of the Model Queue that has been defined on
z/OS for use by Windows clients. In the example in Figure 2
on page 46, the Model queue name is CAC.CLIENT.
To access the latest DB2 Information Integrator product documentation, from the
DB2 Information Integrator Support Web site, click on the Product Information
link, as shown in Figure 4 on page 52.
You can access the latest DB2 Information Integrator documentation, in all
supported languages, from the Product Information link:
v DB2 Information Integrator product documentation in PDF files
v Fix pack product documentation, including release notes
v Instructions for downloading and installing the DB2 Information Center for
Linux, UNIX, and Windows
v Links to the DB2 Information Center online
Scroll though the list to find the product documentation for the version of DB2
Information Integrator that you are using.
52 DB2 II Client Guide for Classic Federation and Classic Event Publishing
The DB2 Information Integrator Support Web site also provides support
documentation, IBM Redbooks, white papers, product downloads, links to user
groups, and news about DB2 Information Integrator.
You can also view and print the DB2 Information Integrator PDF books from the
DB2 PDF Documentation CD.
54 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Table 12. DB2 Information Integrator documentation about event publishing function for IMS
and VSAM on z/OS (continued)
Form
Name number Location
Planning Guide for Event Publisher for z/OS SC18-9158 DB2 Information Integrator
Support Web site
Reference for Classic Federation and Event SC18-9156 DB2 Information Integrator
Publisher for z/OS Support Web site
System Messages for Classic Federation and SC18-9162 DB2 Information Integrator
Event Publisher for z/OS Support Web site
Release Notes for IBM DB2 Information N/A DB2 Information Integrator
Integrator Event Publisher for IMS for z/OS Support Web site
Release Notes for IBM DB2 Information N/A DB2 Information Integrator
Integrator Event Publisher for VSAM for z/OS Support Web site
56 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Table 15. DB2 Information Integrator documentation about federated function on Linux, UNIX,
and Windows (continued)
Form
Name number Location
C++ API Reference for Developing Wrappers SC18-9172 v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Data Source Configuration Guide N/A v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Federated Systems Guide SC18-7364 v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Guide to Configuring the Content Connector for N/A DB2 Information Integrator
VeniceBridge Support Web site
Installation Guide for Linux, UNIX, and GC18-7036 v DB2 PDF Documentation CD
Windows
v DB2 Information Integrator
Support Web site
Java API Reference for Developing Wrappers SC18-9173 v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Migration Guide SC18-7360 v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Wrapper Developer’s Guide SC18-9174 v DB2 PDF Documentation CD
v DB2 Information Integrator
Support Web site
Release Notes for IBM DB2 Information N/A v In the DB2 Information
Integrator Standard Edition, Advanced Edition, Center, Product Overviews
and Replication for z/OS > Information Integration >
DB2 Information Integrator
overview > Problems,
workarounds, and
documentation updates
v DB2 Information Integrator
Installation launchpad
v DB2 Information Integrator
Support Web site
v The DB2 Information
Integrator product CD
58 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Table 17. DB2 Information Integrator Release Notes and Installation
Requirements (continued)
Name File name Location
Release Notes for IBM DB2 N/A DB2 Information Integrator Support
Information Integrator Event Web site
Publisher for VSAM for z/OS
Release Notes for IBM DB2 N/A DB2 Information Integrator Support
Information Integrator Classic Web site
Federation for z/OS
Release Notes for Enterprise Search N/A DB2 Information Integrator Support
Web site
To view the installation requirements and release notes that are on the product CD:
v On Windows operating systems, enter:
x:\doc\%L
x is the Windows CD drive letter and %L is the locale of the documentation that
you want to use, for example, en_US.
v On UNIX operating systems, enter:
/cdrom/doc/%L/
cdrom refers to the UNIX mount point of the CD and %L is the locale of the
documentation that you want to use, for example, en_US.
IBM may have patents or pending patent applications covering subject matter
described in this document. The furnishing of this document does not give you
any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the IBM
Intellectual Property Department in your country/region or send inquiries, in
writing, to:
IBM World Trade Asia Corporation
Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106-0032, Japan
The following paragraph does not apply to the United Kingdom or any other
country/region where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY, OR FITNESS
FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions; therefore, this statement may not apply
to you.
Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those Web
sites. The materials at those Web sites are not part of the materials for this IBM
product, and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.
The licensed program described in this document and all licensed material
available for it are provided by IBM under terms of the IBM Customer Agreement,
IBM International Program License Agreement, or any equivalent agreement
between us.
All statements regarding IBM’s future direction or intent are subject to change or
withdrawal without notice, and represent goals and objectives only.
This information contains examples of data and reports used in daily business
operations. To illustrate them as completely as possible, the examples include the
names of individuals, companies, brands, and products. All of these names are
fictitious, and any similarity to the names and addresses used by an actual
business enterprise is entirely coincidental.
COPYRIGHT LICENSE:
62 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Each copy or any portion of these sample programs or any derivative work must
include a copyright notice as follows:
© (your company name) (year). Portions of this code are derived from IBM Corp.
Sample Programs. © Copyright IBM Corp. _enter the year or years_. All rights
reserved.
Trademarks
The following terms are trademarks of International Business Machines
Corporation in the United States, other countries, or both:
IBM
DB2
DB2 Universal Database
IMS
WebSphere
z/OS
Java and all Java-based trademarks and logos are trademarks or registered
trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Microsoft, Windows, Windows NT, and the Windows logo are trademarks of
Microsoft Corporation in the United States, other countries, or both.
Intel, Intel Inside (logos), MMX and Pentium are trademarks of Intel Corporation
in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other
countries.
Notices 63
64 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Index
A com.ibm.cac.jdbc (continued)
DataSource (continued)
getServerName 12, 15
getUrl 12, 15
APIs getPort 12 getUser 13, 15
deprecated 24 getPortNumber 12, 15 getXAConnection 16
automatic remapping 26 getReference 12
implemented for CLI 23 getServerName 12
implemented for ODBC 3.51 23 getUrl 12
getUser 13
I
INOUT parameters
setDatabaseName 13
C setDescription 13
using in stored procedures with
ODBC 3.51 25
caccli.h 22 setLoginTimeout 13
Call Level Interface. setLogWriter 13
See CLI setPassword 13
CATALOG NAME parameter 39 setPort 13 J
CLI 22, 33 setPortNumber 13, 15 java.sql.properties 4, 7
auto-commit 25 setServerName 14 JDBC
binding parameters 25 setUser 14 batch operations 10
compared to ODBC 3.51 25 com.ibm.cac.jdbc.XADataSource 16 supported optional 2.1 features 10
configuring the driver 28 Configuration parameters 39 updatable scrollable resultsets 10
implemented APIs 23 ConnectionPoolDataSource object 6 JDBC client
logging 32 createStatement 10 setup 3
ODBC 3.51 escape sequences 25 URL to connect to 4
ODBC 3.51-only APIs 25 JNDI database
SQLBindParam call 26 D storing database connection
information 5
stored procedure considerations 27 Data sources
supported C data types 26 adding 20
supported SQL data types 26 configuration 19
CLIENT CODEPAGE parameter 40 configuring 19, 20 L
CLOSE TRACE ON WRITE for TCP/IP 22 Logging
parameter 40 for WebSphere MQ 48 ODBC 3.51 and CLI 32
CODEPAGE 7 Database connections
com.ibm.cac.jdbc storing in a JNDI database 5
ConnectionPoolDataSource
getDatabaseName 14
DataSource object 6
DATASOURCE parameter 40
M
getDescription 14 MESSAGE POOL SIZE parameter 42
getLoginTimeout 14 MESSAGECATALOGNAME 9
getLogWriter 14 Microsoft ODBC Administrator 19
getPassword 14 E
getPooledConnection 14, 15 ENABLE TRACE parameter 41
getPort 15 Escape sequences
ODBC 3.51 25
N
getReference 15 NL CAT parameter 42
getServerName 15
getUrl 15
getUser 15 F O
setDatabaseName 15 FETCH BUFFER SIZE parameter 41
setDescription 15 ODBC
FETCHBUFFERSIZE 8
setLoginTimeout 15 adding data sources 20
setLogWriter 15 ODBC 3.51 22, 33
setPassword 15 auto-commit 25
setPort 15 G automatic remapping of deprecated
setServerName 15 getConnection 11 API calls 26
setUrl 15 getDatabaseName 11, 14 binding parameters 25
setUser 15 getDataSourceName 11 compared to CLI 25
DataSource getDescription 11, 14 configuring the driver 28
getConnection 11 getLoginTimeout 12, 14 escape sequences 25
getDatabaseName 11 getLogWriter 12, 14 implemented APIs 23
getDataSourceName 11 getPassword 12, 14 logging 32
getDescription 11 getPooledConnection 14, 15 OUTPUT and INOUT parameters in
getLoginTimeout 12 getPort 12, 15 stored procedures 25
getLogWriter 12 getPortNumber 12, 15 querying metadata information 25
getPassword 12 getReference 12, 15 specifying schema names 25
P W
WebSphere MQ 45, 48
Platform dependencies 3 connecting to a database 6
prepareCall 10 data source configuration 48
prepareStatement 10 prerequisites 48
Prerequisites
for WebSphere MQ 48
Pure Java 3
R
RESPONSE TIME OUT parameter 42
RESPONSETIMEOUT 9
S
Schema names
specifying 25
SERVER CODEPAGE parameter 43
setDatabaseName 13, 15
setDescription 13, 15
setLoginTimeout 13, 15
setLogWriter 13, 15
setPassword 13, 15
setPort 13, 15
setPortNumber 13, 15
setServerName 14, 15
setUrl 15
setUser 14, 15
SQL queries
timing out with ODBC 3.51 25
sql.h 22
SQLBindParam call 26
SQLCancel call
ODBC 2.0 vs. 3.51 27
sqlext.h 22
SQLTablePrivileges function 25
Stored procedures
CLI considerations 27
OUTPUT and INOUT parameters
with ODBC 3.51 25
using with ODBC 3.51 25
66 DB2 II Client Guide for Classic Federation and Classic Event Publishing
Contacting IBM
To contact IBM customer service in the United States or Canada, call
1-800-IBM-SERV (1-800-426-7378).
To learn about available service options, call one of the following numbers:
v In the United States: 1-888-426-4343
v In Canada: 1-800-465-9600
To locate an IBM office in your country or region, see the IBM Directory of
Worldwide Contacts on the Web at www.ibm.com/planetwide.
Product information
Information about DB2 Information Integrator is available by telephone or on the
Web.
If you live in the United States, you can call one of the following numbers:
v To order products or to obtain general information: 1-800-IBM-CALL
(1-800-426-2255)
v To order publications: 1-800-879-2755
Printed in USA
SC18-9160-01