DB2 Driver Reference

Understanding the Table Services Driver for DB2

Data Service Connection Options for DB2

Overview

Connection Options

Advanced Connection Options

DB2 Wire Protocol Driver Usage Notes

Understanding the Table Services Driver for DB2

The table services driver for DB2 (driver for DB2) enables table services to read and update legacy DB2 tables. In addition, the driver creates DB2 tables that can be accessed by both table services and the DB2 database management system (DBMS).

The driver for DB2 supports most of the FedSQL functionality. The driver also enables an application to submit native DB2 SQL statements.

The table services driver for DB2 is a remote driver, which means that it connects to a server process in order to access data. The process might be running on the same machine as the table services driver, or it might be running on another machine in the network.

The table services driver for DB2 uses shared libraries that are referenced as shared objects in UNIX. You must add the location of the shared libraries to one of the system environment variables and, if necessary, specify the DB2 version that you have installed. Before setting the environment variables, as shown in the examples below, you must also set the following environment variables:

The INSTHOME environment variable must be set to your DB2 home directory.
The DB2DIR environment variable should also be set to the value of INSTHOME.
The DB2INSTANCE environment variable should be set to the DB2 instance that was configured by the administrator.

AIX
Bourne Shell
$ LIBPATH=$INSTHOME/lib:$LIBPATH
$ export LIBPATH
C Shell
$ setenv LIBPATH $INSTHOME/lib:$LIBPATH
HP-UX and HP-UX for the Itanium Processor
  Family Architecture
Bourne Shell
$ SHLIB_PATH=$INSTHOME/lib:$SHLIB_PATH
$ export SHLIB_PATH
C Shell
$ setenv SHLIB_PATH $INSTHOME/lib:$SHLIB_PATH
Linux for Intel Architecture, Linux for x64, Solaris,
  and Solaris for x64
Bourne Shell
$LD_LIBRARY_PATH=$INSTHOME/lib:$LD_LIBRARY_PATH
$ export LD_LIBRARY_PATH
C Shell
$ setenv LD_LIBRARY_PATH $INSTHOME/lib:$LD_LIBRARY_PATH

Data Service Connection Options for DB2

Overview

The data service connection arguments for DB2 include connection options and advanced options.

Note: When performing connections through DSNs or connection strings, the FedSQL language processor automatically quotes SQL identifiers that do not meet the regular naming convention as defined in SAS FedSQL Reference Guide.

Connection Options

Connection options are used to establish a connection to a data source. Specify one or more connection options. Here is an example:

driver=sql;conopts=(driver=db2;uid=myuid;
pwd=Blue31;conopts=(DSN=MYDSN);CATALOG=TSSQL)

The table services driver for DB2 supports the following connection options for DB2 data sources.

Option	Description
CATALOG	`CATALOG=catalog-identifer;` Specifies an arbitrary identifier for an SQL catalog, which groups logically related schemas. Any identifier is valid (for example, `catalog=DB2`). You must specify a catalog. For the DB2 database, this is a logical catalog name to use as an SQL catalog identifier. Note: The FedSQL language processor automatically quotes SQL identifiers that do not meet the regular naming convention as defined in SAS FedSQL Reference Guide.
DATABASE\|DB	`DATABASE=database-specification;` Specifies the name of the DB2 database (for example, `database=sample, DB=sample`). Note: You must specify a database name.
DRIVER	`DRIVER=DB2;` Identifies the DB2 data source to which you want to connect. Note: You must specify the driver.

Advanced Connection Options

The table services driver for DB2 supports the following advanced connection options for DB2 data sources.

Option

Description

CLIENT_ENCODING

CLIENT_ENCODING=encoding-value

Used to specify the encoding of the DB2CODEPAGE to the DB2 driver. When using this option, you must also set the DB2CODEPAGE environment variable on the client.

When the encoding of the DB2 client layer (stored in DBCODEPAGE) is different from the encoding value of the DB2 operating system value, the DB2 client layer attempts to convert incoming data to the DB2 encoding value that is stored in DB2CODEPAGE. To prevent the client layer from converting data incorrectly, you must first determine the correct value for DB2CODEPAGE and then set the CLIENT_ENCODING= option to match the corresponding encoding value in DB2CODEPAGE.

For example, suppose you are storing Japanese characters in a DB2 database, and the client machine where the DB2 driver is executing is a Windows machine that is running CP1252 encoding. When the application tries to extract the data into the table services driver, the DB2 client layer attempts to convert these Japanese characters into Latin1 representation, which does not contain Japanese characters. As a result, a garbage character appears in order to indicate a failure in transcoding.

To resolve this situation, you must first set the DB2CODEPAGE environment variable value to 1208 (the IBM code page value that matches UTF-8 encoding). That enables you to specify that the DB2 client layer send the data to the application in UTF-8 instead of converting it into Latin1. In addition, you must specify the corresponding encoding value of DB2CODEPAGE because the table services driver for DB2 cannot derive this information from a DB2 session. For this particular Windows case, set the CLIENT_ENCODING= option to the UTF-8 encoding in order to match the DB2CODEPAGE value (1208) and also to specify the DB2CODEPAGE value to the DB2 driver.

However, changing the value of DB2CODEPAGE affects all applications that run on that machine. You should reset the value to the usual DB2CODEPAGE value, which was derived when the database was created.

Note: Setting the DB2CODEPAGE value or the CLIENT_ENCODING= value incorrectly can cause unpredictable results. You should set these values only when a situation such as the example above occurs.

Note: You can specify any valid encoding value for CLIENT_ENCODING=option.

CT_PRESERVE

CT_PRESERVE=STRICT
                                    | SAFE | FORCE | FORCE_COL_SIZE

Enables users to control how data types are mapped. Note that data type mapping is disabled when CT_PRESERVE is set to STRICT. If the requested type does not exist on the target database, an error is returned. Here are the options:

STRICT The requested type must exist in the target database. No type promotion occurs. If the type does not exist, an error is returned.
SAFE Target data types are upscaled only if they do not result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure that all characters can be stored in the new encoding.
FORCE This is the default for all drivers. The best corresponding target data type is chosen, even if it could potentially result in a loss of precision or scale. When character encodings are changed, the new column size is recalculated to ensure that all characters can be stored in the new encoding.
FORCE_COL_SIZE This option is the same as FORCE, except that the column size for the new encoding is the same as the original encoding. This option can be used to avoid column size creep. However, the resulting column might be too large or too small for the target data.

DEFAULT_ATTR

DEFAULT_ATTR=(attr=value;...)

Used to specify connection handle or statement handle attributes that are supported for initial connect-time configuration, where attr=value corresponds to any of the following options:

CURSORS=n- Connection handle option. This option controls the driver’s use of client-side, result set cursors. The possible values are 0, 1, or 2.

0	Causes the driver to use client-side static cursor emulation if a scrollable cursor is requested but the database server cannot provide one.
1	Causes the driver to always use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is not used.
2	(Default) Causes the driver to never use client-side static cursor emulation if a scrollable cursor is requested. The database server’s native cursor is used if available. Otherwise, the cursor is forward-only.

Example: DEFAULT_ATTR=(CURSORS=2)

USE_EVP=n - Statement handle option. This option optimizes the driver for large result sets. The possible values are 0 (OFF) or 1 (ON), which is the default. Example: DEFAULT_ATTR=(USE_EVP=0)
XCODE_WARN=n - Statement handle option. Used to warn about possible character transcoding errors that occur during row input or output operations. Possible values are 0 (returns an error), 1 (returns a warning), or 2 (ignore transaction errors). 0 is the default. Example: DEFAULT_ATTR=(XCODE_WARN=1)

DRIVER_TRACE

DRIVER_TRACE=’API
                                    | SQL | ALL’;

Requests tracing information, which logs transaction records to an external file that can be used for debugging purposes. The driver writes a record of each command that is sent to the database to the trace log based on the specified tracing level, which determines the type of tracing information. Here are the tracing levels:

API Specifies that API method calls be sent to the trace log. This option is most useful if you are having a problem and need to send a trace log to SAS Technical Support for troubleshooting.
SQL Specifies that SQL statements that are sent to the database management system (DBMS) be sent to the trace log. Tracing information is DBMS specific, but most table services drivers log SQL statements such as SELECT and COMMIT.
ALL Activates all trace levels.
DRIVER Specifies that driver-specific information be sent to the trace log.

Default: Tracing is not activated.

Note: If you activate tracing, you must also specify the location of the trace log with DRIVER_TRACEFILE=. Note that DRIVER_TRACEFILE= is resolved against the TRACEFILEPATH set in ALTER SERVER. TRACEFILEPATH is relative to the server's content root location.

(Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

Interaction: You can specify one trace level, or you can concatenate more than one by including the | (OR) symbol. For example, driver_trace='api|sql' generates tracing information for API calls and SQL statements.

DRIVER_TRACEFILE

DRIVER_TRACEFILE=’filename’;

Used to specify the name of the text file for the trace log. Include the filename and extension in single or double quotation marks (for example, driver_tracefile='\mytrace.log').

Default: The default TRACEFILE location applies to a relative filename, and it is placed relative to TRACEFILEPATH.

Requirement: DRIVER_TRACEFILE is required when activating tracing using DRIVER_TRACE.

Interaction: (Optional) You can control trace log formatting with DRIVER_TRACEOPTIONS=.

DRIVER_TRACEOPTIONS

DRIVER_TRACEOPTIONS=APPEND
                                    | THREADSTAMP | TIMESTAMP;

Specifies options in order to control formatting and other properties for the trace log:

APPEND Adds trace information to the end of an existing trace log. The contents of the file are not overwritten.
TIMESTAMP Prepends each line of the trace log with a time stamp.
THREADSTAMP Prepends each line of the trace log with a thread identification.

Default: The trace log is overwritten with no thread identification or time stamp.

PASSWORD

PWD=password

Specifies the password for DB2.

UID

UID=user-id;

Specifies the DB2 login user ID.

DB2 Wire Protocol Driver Usage Notes

There are a number of third-party wire protocol ODBC drivers that communicate directly with a database server, without having to communicate through a client library. When you configure the ODBC drivers on Windows or UNIX, you can set certain options. SAS runs best when these options are selected. Some, but not all, are selected by default.

Windows	The options are located on the Advanced or Performance tabs in the ODBC Administrator.
UNIX	The options are available when configuring data sources using the ODBC Administrator tool. Values can also be set by editing the `odbc.ini` file in which their data sources are defined.

Note: A DSN configuration that uses a wire protocol driver with the catalog option selected returns only the schemas that have associated tables or views. To list all existing schemas, create a DSN without selecting the catalog option.

When configuring an ODBC DSN using the DB2 Wire Protocol driver, set the following advanced option:

Application Using Threads