The following sections describe how to configure and use the WebLogic Type 4 JDBC driver for DB2:
The WebLogic Type 4 JDBC driver for DB2 (the “DB2 driver”) supports:
Note: | This documentation uses the following terms to describe the different DB2 versions: |
The driver classes for the WebLogic Type 4 JDBC DB2 driver are as follows:
XA: weblogic.jdbcx.db2.DB2DataSource
Non-XA: weblogic.jdbc.db2.DB2Driver
Use these driver classes when configuring a JDBC data source in your WebLogic Server domain.
The connection URL format for the DB2 driver is:
jdbc:bea:db2://hostname
:port
[;property
=value
[;...]]
hostname
is the IP address or TCP/IP host name of the server to which you are connecting. See
Using IP Addresses for details on using IP addresses.Note: | Untrusted applets cannot open a socket to a machine other than the originating host. |
port
is the number of the TCP/IP port.property=value
specifies connection properties. For a list of connection properties and their valid values, see
DB2 Connection Properties.DB2 UDB for Linux, UNIX, and Windows
jdbc:bea:db2://server1:50000;DatabaseName=jdbc;User=test;Password=secret
jdbc:bea:db2://server1:446;LocationName=Sample;User=test;Password=secret
Table 3-1 lists the JDBC connection properties supported by the DB2 driver, and describes each property. You can use these connection properties in a JDBC data source configuration in your WebLogic Server domain.
Note: | All connection property names are case-insensitive. For example, Password is the same as password. Required properties are noted as such. The data type listed for each connection property is the Java data type used for the property value in a JDBC data source. |
To specify a property, use the following form in the JDBC data source configuration:
{true | false}. DB2 provides a mechanism that automatically closes a result set when all rows of the result set have been fetched. This mechanism increases application performance by reducing the number of database round trips. The WebLogic DB2 driver uses this mechanism by default.
|
|||
{kerberos | encryptedUIDPassword | encryptedPassword | clearText | client}. Determines which authentication method the driver uses when establishing a connection.
If set to kerberos, the driver uses Kerberos authentication. The driver ignores any user ID or password specified.
If set to encryptedUIDPassword, the driver uses user ID/password authentication. The driver sends an encrypted user ID and password to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the
EncryptionMethod property for details).
If set to encryptedPassword, the driver uses user ID/password authentication. The driver sends a user ID in clear text and an encrypted password to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the
EncryptionMethod property for details).
If set to clearText (the default), the driver uses user ID/password authentication. The driver sends the user ID and password in clear text to the DB2 server for authentication. If a user ID and password are not specified, the driver throws an exception. If this value is set, the driver can also use data encryption (see the
EncryptionMethod property for details).
If set to client, the driver uses client authentication. The DB2 server relies on the client to authenticate the user and does not provide additional authentication. The driver ignores any user ID or password specified.
If the specified authentication method is not supported by the DB2 server, the connection fails and the driver throws an exception.
SeeAuthentication for more information about using authentication with the DB2 driver.
|
|||
{
true | false }.The DB2 driver uses the native DB2 batch mechanism. This property determines whether certain restrictions are enforced to facilitate data conversions.
See Batch Inserts and Updates for more information.
|
|||
{
true | false }. Determines whether synonyms are included in the result sets returned from the DatabaseMetaData.getColumns() method.
If set to
true (the default), synonyms are included in the result sets returned from the DatabaseMetaData.getColumns() method.
If set to
false , synonyms are omitted from result sets returned from the DatabaseMetaData.getColumns() method.
This property is ignored for DB2 v8.x and higher for Linux/UNIX/Windows. The driver always returns synonyms for the DatabaseMetaData.getColumns() method when connected to DB2 v8.x and higher for Linux/UNIX/Windows.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
The DB2 schema to use for catalog functions. The value must be the name of a valid DB2 schema. The default depends on the platform of the DB2 database.
The default is SYSCAT (DB2 for Linux/UNIX/Windows),
SYSIBM (DB2 for z/OS), or QSYS2 (DB2 for iSeries)
To improve performance, views of system catalog tables can be created in a schema other than the default catalog schema. Setting this property to a schema that contains views of the catalog tables allows the driver to use those views. To ensure that catalog methods function correctly, views for specific catalog tables must exist in the specified schema. The views that are required depend on your DB2 database. See Non-Default Schemas for Catalog Methods for the required views of catalog tables.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
The code page to use to convert character data stored as bit data in character columns (Char, Varchar, Longvarchar, Char for Bit Data, Varchar for Bit Data, Longvarchar for Bit Data) defined with CCSID 65535. All character data stored as bit data retrieved from the database using columns defined with CCSID 65535 is converted using the specified code page. The value must be a string containing the name of a valid code page supported by your JVM, for example,
CharsetFor65535 =CP950. This property has no effect when writing data to character columns defined with CCSID 65535.
|
|||
A code page to be used to convert Character and Clob data. The specified code page overrides the default database code page or column collation. All Character and Clob data retrieved from or written to the database is converted using the specified code page. The value must be a string containing the name of a valid code page supported by your JVM, for example,
CodePageOverride =CP950.
|
|||
This property is recognized for backward compatibility, but we recommend that you use the
PackageCollection property instead to specify the name of the collection or library (group of packages) to which DB2 packages are bound.
See Creating a DB2 Package for more information about creating DB2 packages.
|
|||
The number of times the driver retries connection attempts until a successful connection is established. Valid values are 0 and any positive integer.
If set to 0, the driver does not retry connections if a successful connection is not established on the driver’s first attempt to create a connection.
If an application sets a login timeout value (for example, using DataSource.loginTimeout or DriverManager.loginTimeout), the login timeout takes precedence over this property. For example, if the login timeout expires, any connection attempts stop.
|
|||
The number of seconds the driver waits between connection retry attempts when ConnectionRetryCount is set to a positive integer.
|
|||
If set to 1 (the default), the driver checks the data type being requested against the data type of the table column storing the data. If a conversion between the requested type and column type is not defined, the driver generates an "unsupported data conversion" exception regardless of the data type of the column value.
|
|||
If set to true, the DB2 driver automatically creates required DB2 packages, even if they already exist. Existing DB2 packages are replaced by the new packages.
If set to false (the default), the driver determines if the required DB2 packages exist. If they do not, the driver automatically creates them.
For DB2 for Linux/UNIX/Windows, this property must be used in conjunction with the
ReplacePackage property.
For DB2 for z/OS and DB2 for iSeries, DB2 packages are created in the collection or library specified by the
PackageCollection property.
For more information about creating DB2 packages, see Creating a DB2 Package.
|
|||
See also Database Connection Property.
|
|||
{true | false}. Determines whether a cancel request sent by the driver as the result of a query timing out is subject to the same query timeout value as the statement it cancels.
If set to true, the cancel request times out using the same timeout value, in seconds, that is set for the statement it cancels. For example, if your application calls
Statement.setQueryTimeout(5) on a statement and that statement is cancelled because its timeout value was exceeded, the driver sends a cancel request that also will time out if its execution exceeds 5 seconds. If the cancel request times out, because the server is down, for example, the driver throws an exception indicating that the cancel request was timed out and the connection is no longer valid.
|
|||
Determines whether a DB2-specific encryption algorithm is used to encrypt and decrypt data transmitted over the network between the driver and database server. To use encryption, you also must set the
AuthenticationMethod property to a value of clearText, encryptedPassword, or encryptedUIDPassword.
If set to DBEncryption, data is encrypted using DES encryption if the database server supports it. If the database server does not support DES encryption, the connection fails and the driver throws an exception.
If set to requestDBEncryption, data is encrypted using DES encryption if the database server supports it. If the database server does not support DES encryption, the driver attempts to establish an unencrypted connection.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
Specifies the name of the schema to which you want to grant EXECUTE privileges for DB2 packages. The value must be a valid DB2 schema.This property is ignored if the
GrantExecute property is set to false.
IMPORTANT: Using a value other than PUBLIC restricts access to use the driver. For example, if you set this property to TSMITH, only the user TSMITH would be allowed access to use the driver against the server.
See Creating a DB2 Package for more information about creating DB2 packages.
|
|||
If set to true (the default), EXECUTE privileges are granted to the schema specified by the
Grantee property.
See Creating a DB2 Package for more information about creating DB2 packages.
|
|||
Specifies one or multiple SQL commands to be executed by the driver after it has established the connection to the database and has performed all initialization for the connection.
For example, suppose USER1 needs to invoke stored procedures owned by USER2 without specifying the qualified name for those procedures. You can use this property to add USER2 to the CURRENT PATH special register, which sets the default schema, or schemas, to use when executing a user-defined function or stored procedure.
Multiple commands must be separated by semicolons. In addition, if this property is specified in a connection URL, the entire value must be enclosed in parentheses when multiple commands are specified. The following connection URL adds USER2 to the CURRENT PATH special register and sets the CURRENT PRECISION special register to DEC31.
|
|||
{-1 | 0 | x}. Determines the amount of memory used by the driver to cache insensitive result set data. It must have one of the following values:
If set to -1, the driver caches all insensitive result set data in memory. If the size of the result set exceeds available memory, an OutOfMemoryException is generated. Because the need to write result set data to disk is eliminated, the driver processes the data more efficiently.
If set to 0, the driver caches all insensitive result set data in memory, up to a maximum of 2 GB. If the size of the result set data exceeds available memory, the driver pages the result set data to disk. Because result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk.
If set to x, where x is a positive integer that specifies the size (in KB) of the memory buffer used to cache insensitive result set data. If the size of the result set data exceeds the buffer size, the driver pages the result set data to disk. Because the result set data may be written to disk, the driver may have to reformat the data to write it correctly to disk. Specifying a buffer size that is a power of 2 results in more efficient memory use.
|
|||
{true | false}. Determines whether the driver uses its internal conversion algorithm or the JVM conversion algorithm when converting double or float values to string values.
If set to true, the driver uses the JVM algorithm when converting double or float values to string values.
If set to false (the default), the driver uses its internal algorithm when converting double or float values to string values. Using this value improves performance; however, slight rounding differences can occur when compared to the same conversion using the JVM algorithm. These differences are within the allowable error of the double and float data types.
|
|||
For DB2 for z/OS, your system administrator can determine the name of your DB2 location using the following command:
For DB2 for iSeries, your system administrator can determine the name of your DB2 location using the following command. The name of the database that is listed as *LOCAL is the value you should use for this property.
See also DatabaseName Connection Property.
|
|||
NOTE: This property replaces the
CollectionId property; however, the CollectionId property is still recognized for backward compatibility. If both the PackageCollection and CollectionId properties are specified, the CollectionId property is ignored.
See Creating a DB2 Package for more information about creating DB2 packages.
|
|||
See Creating a DB2 Package for more information about creating DB2 packages.
|
|||
{positive integer | -1 | 0}. Sets the default query timeout (in seconds) for all statements created by a connection.
If set to a positive integer, the driver uses the value as the default timeout for any statement created by the connection. To override the default timeout value set by this connection option, call the
Statement.setQueryTimeout() method to set a timeout value for a particular statement.
|
|||
{true | false}.Determines whether the current bind process will replace the existing DB2 packages used by the driver.
For DB2 for Linux/UNIX/Windows, this property must be used in conjunction with the CreateDefaultPackage property.
For more information about creating DB2 packages, see Creating a DB2 Package.
|
|||
{0 | 1}. The DB2 driver can return table name information in the ResultSet metadata for Select statements if your application requires that information.
If set to 0 (the default) and the
ResultSetMetaData.getTableName() method is called, the DB2 driver does not perform additional processing to determine the correct table name for each column in the result set. In this case, the getTableName() method may return an empty string for each column in the result set.
If set to 1 and the
ResultSetMetaData.getTableName() method is called, the DB2 driver performs additional processing to determine the correct table name for each column in the result set. The DB2 driver also can return schema name and catalog name information when the ResultSetMetaData.getSchemaName() and ResultSetMetaData.getCatalogName() methods are called if the driver can determine that information.
For information about configuring this property for optimal performance, see Performance Considerations.
|
|||
{true | false}. Determines whether binary stream data that is less than 32K bytes is sent to the database as Long Varchar for Bit Data or Blob data. Binary streams that are larger than 32K bytes can only be inserted into a Blob column. The driver always sends binary stream data larger than 32K bytes to the database as Blob data.
If set to true, the driver sends binary stream data that is less than 32K to the database as DB2 Blob data. If the target column is a Long Varchar for Bit Data column and not a Blob column, the Insert or Update statement fails. The driver automatically retries the Insert or Update statement, sending the data as Long Varchar for Bit Data, if the pointer in the stream can be reset to the beginning of the stream. If you know that you are sending the binary stream data to a Blob column, setting this value improves performance.
If set to false, the driver sends binary stream data that is less than 32K to the database as Long Varchar for Bit Data data. If the target column is a Blob column and not a Long Varchar for Bit Data column, the Insert or Update statement fails. The driver retries the Insert or Update statement, sending the data as Blob data, if the pointer in the stream can be reset to the beginning of the stream.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{true | false}. Specifies whether new-line characters in a SQL statement are sent to the DB2 server. If you know that the SQL statements used in your application do not contain newline characters, instructing the driver to not remove them eliminates parsing by the DB2 server and improves performance.
If set to false (the default), the driver does not remove any newline characters from SQL statements.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
{true | false}. Specifies whether results are restricted to the tables and views in the current schema if a
DatabaseMetaData.getTables or DatabaseMetaData.getColumns() method is called without specifying a schema or if the schema is specified as the wildcard character %. Restricting results to the tables and views in the current schema improves the performance of calls for getTables() methods that do not specify a schema.
If set to true, results that are returned from the
getTables() and getColumns() methods are restricted to tables and views in the current schema.
If set to false (the default), results of the
getTables() and getColumns() methods are not restricted.
See Performance Considerations for information about configuring this property for optimal performance.
|
|||
See Returning and Inserting/Updating XML Data for more information.
|
Setting the following connection properties for the DB2 driver as described in the following list can improve performance for your applications:
The DatabaseMetaData.getColumns
method is often used to determine characteristics about a table, including the synonym, or alias, associated with a table. If your application accesses DB2 v7.x for Linux/UNIX/Windows, DB2 for z/OS, or DB2 for iSeries and your application does not use database table synonyms, the driver can improve performance by ignoring this information. The driver always returns synonyms for the DatabaseMetaData.getColumns()
method when accessing DB2 v8.x and higher for Linux/UNIX/Windows.
To improve performance, views of system catalog tables can be created in a catalog schema other than the default. The DB2 driver can access the views of catalog tables if this property is set to the name of the schema containing the views. The default catalog schema is SYSCAT for DB2 for Linux/UNIX/Windows, SYSIBM for DB2 for z/OS, and QSYS2 for DB2 for iSeries.
To ensure that catalog methods function correctly, views for specific catalog tables must exist in the specified schema. The views that are required depend on your DB2 database. See Non-Default Schemas for Catalog Methods for views for catalog tables that must exist in the specified schema.
Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data.
To improve performance when using scroll-insensitive result sets, the driver can cache the result set data in memory instead of writing it to disk. By default, the driver caches 2 MB of insensitive result set data in memory and writes any remaining result set data to disk. Performance can be improved by increasing the amount of memory used by the driver before writing data to disk or by forcing the driver to never write insensitive result set data to disk. The maximum cache size setting is 2 GB.
By default, the DB2 driver skips the additional processing required to return the correct table name for each column in the result set when the ResultSetMetaData.getTableName()
method is called. Because of this, the getTableName()
method may return an empty string for each column in the result set. If you know that your application does not require table name information, this setting provides the best performance.
See ResultSet Metadata Support for more information about returning ResultSet metadata.
If the large binary objects you insert or update are stored as Blobs, performance can be improved by sending the binary stream as Blob data. In this case, this property should be set to true.
If you know that the SQL statements used in your application do not contain newline characters, the driver can improve performance by omitting the parsing required to remove them.
If your application needs to access tables and views owned only by the current user, performance of your application can be improved by setting this property to true. When this property is set to true, the driver returns only tables and views owned by the current user when executing getTables()
and getColumns()
methods. Setting this property to true is equivalent to passing the user ID used on the connection as the schemaPattern
argument to the getTables()
or getColumns()
call.
When connecting to a DB2 database running on AS/400, you must set the locationName
property:
WRKRDBDIRE
command on AS/400.You should see output similar to the following:
,Relational,,Remote,Option,,Database,,Location,,Text,
, ,, , ,,S10B757B,,*LOCAL ,, ,
Properties
object with "user" and "password" DB2 connection properties (see DB2 Connection Properties).Driver.connect()
, specify the following string and the Properties object as parameters: jdbc:bea:db2://<Host>:<Port>;LocationName=RelationalDatabaseName
In this example, RelationalDatabaseName
is the value of Database
obtained from the result of running the WRKRDBDIRE
command.
The following is an excerpt of the Java client:
...
Properties props = new Properties();
props.put("user", user);
props.put("password", password);
...
myDriver = (Driver)Class.forName("weblogic.jdbc.db2.DB2Driver").newInstance();
conn = myDriver.connect("jdbc:bea:db2://10.1.4.1:446;LocationName=S10B757B", props);
stmt = conn.createStatement();
stmt.execute("select * from MYDATABASE.MYTABLE");
rs = stmt.getResultSet();
...
A DB2 package is a control structure on the DB2 server produced during program preparation that is used to execute SQL statements. The DB2 driver automatically creates all DB2 packages required at connection time. If a package already exists, the driver uses the existing package to establish a connection.
Notes: | The initial connection may take a few minutes because of the number and size of the packages that must be created for the connection. Subsequent connections do not incur this delay. |
Note: | When the driver has completed creating packages, it writes the following message to the standard output: DB2 packages created. |
By default, DB2 packages created by the DB2 driver contain 200 dynamic sections and are created in the NULLID collection (or library). In most cases, you do not need to create DB2 packages because the DB2 driver automatically creates them at connection time. If required, you can create DB2 packages in either of the following ways:
dbping
utility. See Creating a DB2 Package Using dbping.Note: | Your user ID must have CREATE PACKAGE privileges on the database, or your database administrator must create packages for you. |
Note: | Your user ID (the user ID listed in the JDBC data source configuration) must be the owner of the package. |
Note: | The user ID creating the DB2 packages must have BINDADD privileges on the database. Consult with your database administrator to ensure that you have the correct privileges. |
To create a package on the DB2 server with the WebLogic Type 4 JDBC DB2 driver, you can use the WebLogic Server dbping
utility. The dbping
utility is used to test the connection between your client machine and a DBMS via a JDBC driver. Because the WebLogic Type 4 JDBC DB2 driver automatically creates a DB2 package if one does not already exist, running this utility creates a default DB2 package on the DB2 server.
For details about using the dbping
utility to create a DB2 package, see
Creating a DB2 Package with dbping.
You can create a DB2 package automatically by specifying specific connection properties in the initial connection URL. Table 3-2 lists the connection properties you should use in your initial connection URL when you create a DB2 package:
Note: | This method is not recommended for use with WebLogic Server JDBC data sources because every connection in the data source uses the same URL and connection properties. When a JDBC data source with multiple connections is created, the package would be recreated when each database connection is created. |
Using CreateDefaultPackage=TRUE
creates a package with a default name. If you use CreateDefaultPackage=TRUE
, and you do not specify a CollectionId, the NULLID CollectionId is created.
Note: | To create new DB2 packages on DB2 for Linux/UNIX/Windows, you must use ReplacePackage=true in conjunction with CreateDefaultPackage=true . If a DB2 package already exists, it will be replaced when ReplacePackage=true . |
The following URL creates DB2 packages with 400 dynamic sections. If any DB2 packages already exist, they will be replaced by the new ones being created.
jdbc:bea:db2://server1:50000;DatabaseName=SAMPLE;
CreateDefaultPackage=TRUE;ReplacePackage=TRUE;DynamicSections=400
The following URL creates DB2 packages with 400 dynamic sections.
jdbc:bea:db2://server1:50000;LocationName=SAMPLE;
CreateDefaultPackage=TRUE;DynamicSections=400
A dynamic section is the actual executable object that contains the logic needed to satisfy a dynamic SQL request. These sections are used for handles and prepared statements and the associated result sets.
In some cases, you may need to create DB2 packages with more than the default number of dynamic sections (200). Consider the following information if your application requires DB2 packages with a large number of dynamic sections:
PCKCACHE_SZ
to allow the larger packages to be created.
Table 3-3 lists the data types supported by the DB2 driver and how they are mapped to JDBC data types.
Bigint1
|
|
Blob2
|
|
DBClob3
|
|
Rowid4
|
|
1Supported only for DB2 v8.1 and v 8.2 for Linux/UNIX/Windows. 2Supported only for DB2 v8.1 and v 8.2 for Linux/UNIX/Windows, DB2 for z/OS, and DB2 V5R2 and V5R3 for iSeries (see Large Object (LOB) Support). 3Supported only for DB2 v8.1 and v 8.2 for Linux/UNIX/Windows, DB2 7.x v8.1, and v8.2 for z/OS, and DB2 V5R2 and V5R3 for iSeries (see Large Object (LOB) Support). 4Supported only for DB2 for z/OS, and DB2 V5R2 and V5R3 for iSeries. |
See Large Object (LOB) Support for more information about the Blob, Clob, and DBClob data types. See Returning and Inserting/Updating XML Data for more information about the XML data type.See GetTypeInfo for more information about data types.
For DB2 V9.1 for Linux/UNIX/Windows, the DB2 driver supports the XML data type. By default, the driver maps the XML data type to the JDBC CLOB data type, but you can choose to map the XML data type to the BLOB data type by setting the XMLDescribeType
connection property to a value of blob.
The driver can return XML data as character or binary data. For example, given a database table defined as:
CREATE TABLE xmlTable (id int, xmlCol xml NOT NULL)
String sql="SELECT xmlCol FROM xmlTable";
ResultSet rs=stmt.executeQuery(sql);
The driver returns the XML data from the database as character or binary data depending on the setting of the XMLDescribeType
property. By default, the driver maps the XML data type to the JDBC CLOB data type. If the following connection URL mapped the XML data type to the BLOB data type, the driver would return the XML data as binary data instead of character data:
jdbc:bea:db2://server1:50000;DatabaseName=jdbc;User=test;
Password=secret;XMLDescribeType=blob
When XMLDescribeType=clob
, XML data is returned as character data. The result set column is described with a column type of CLOB and the column type name is xml.
When XMLDescribeType=clob
, your application can use the following methods to return data stored in XML columns as character data:
ResultSet.getString()
ResultSet.getCharacterStream()
ResultSet.getClob()
CallableStatement.getString()
CallableStatement.getClob()
The driver converts the XML data returned from the database server from the UTF-8 encoding used by the database server to the UTF-16 Java String encoding.
Your application can use the following method to return data stored in XML columns as ASCII data:
ResultSet.getAsciiStream()
The driver converts the XML data returned from the database server from the UTF-8 encoding to the ISO-8859-1 (latin1) encoding.
Note: | The conversion caused by using the getAsciiStream() method may create XML that is not well-formed because the content encoding is not the default encoding and does not contain an XML declaration specifying the content encoding. Do not use the getAsciiStream() method if your application requires well-formed XML. |
When XMLDescribeType=blob
, your application should not use any of the methods for returning character data described in this section. In this case, the driver applies the standard JDBC character-to-binary conversion to the data, which returns the hexadecimal representation of the character data.
When XMLDescribeType=blob
, the driver returns XML data as binary data. The result set column is described with a column type of BLOB and the column type name is xml.
When XMLDescribeType=blob
, your application can use the following methods to return XML data as binary data:
ResultSet.getBytes()
ResultSet.getBinaryStream()
ResultSet.getBlob()
ResultSet.getObject()
CallableStatement.getBytes()
CallableStatement.getBlob()
CallableStatement.getObject()
The driver does not apply any data conversions to the XML data returned from the database server. These methods return a byte array or binary stream that contains the XML data encoded as UTF-8.
When XMLDescribeType=clob
, your application should not use any of the methods for returning binary data described in this section. In this case, the driver applies the standard JDBC binary-to-character conversion to the data, which returns the hexadecimal representation of the binary data.
The driver can insert or update XML data as character or binary data regardless of the setting of the XMLDescribeType
connection property.
Your application can use the following methods to insert or update XML data as character data:
PreparedStatement.setString()
PreparedStatement.setCharacterStream()
PreparedStatement.setClob()
PreparedStatement.setObject()
ResultSet.updateString()
ResultSet.updateCharacterStream()
ResultSet.updateClob()
ReultSet.updateObject()
The driver converts the character representation of the data to the XML character set used by the database server and sends the converted XML data to the server. The driver does not parse or remove any XML processing instructions.
Your application can update XML data as ASCII data using the following methods:
PreparedStatement.setAsciiStream()
ResultSet.updateAsciiStream()
The driver interprets the data supplied to these methods using the ISO-8859-1 (latin 1) encoding. The driver converts the data from ISO-8859-1 to the XML character set used by the database server and sends the converted XML data to the server.
Your application can use the following methods to insert or update XML data as binary data:
PreparedStatement.setBytes()
PreparedStatement.setBinaryStream()
PreparedStatement.setBlob()
PreparedStatement.setObject()
ResultSet.updateBytes()
ResultSet.updateBinaryStream()
ResultSet.updateBlob()
ReultSet.updateObject()
The driver does not apply any data conversions when sending XML data to the database server.
Authentication protects the identity of the user so that user credentials cannot be intercepted by malicious hackers when transmitted over the network. See Authentication for an overview.
The DB2 driver supports the following methods of authentication:
This method requires knowledge of how to configure your Kerberos environment and supports Windows Active Directory Kerberos and MIT Kerberos.
Note: | Because the database server does not authenticate the user when client authentication is used, use this method of authentication if you can guarantee that only trusted clients can access the database server. |
The driver’s AuthenticationMethod
connection property controls which authentication mechanism the driver uses when establishing connections. SeeUsing the AuthenticationMethod Property for information about setting the value for this property.
The AuthenticationMethod
connection property controls which authentication mechanism the driver uses when establishing connections.
When AuthenticationMethod=kerberos
, the driver uses Kerberos authentication when establishing a connection. The driver ignores any values specified by the User property and Password properties.
When AuthenticationMethod=encryptedUIDPassword
, AuthenticationMethod=encryptedPassword
, or AuthenticationMethod=clearText
(the default), the driver uses user ID/password authentication when establishing a connection. The User property provides the user ID. The Password property provides the password. The set of credentials that are passed to the DB2 server depend on the specified value:
AuthenticationMethod=encryptedUIDPassword
, an encrypted user ID and encrypted password are sent to the DB2 server for authentication.AuthenticationMethod=encryptedPassword
, a user ID in clear text and an encrypted password are sent to the DB2 server for authentication.AuthenticationMethod=clearText
, both a user ID and a password are sent in clear text to the DB2 server for authentication.
If any of these values are set, the driver also can use data encryption by setting the EncryptionMethod
property.
When AuthenticationMethod=client
, the driver uses the user ID of the user logged onto the system on which the driver is running when establishing a connection. The DB2 database server relies on the client to authenticate the user and does not provide additional authentication. The driver ignores any values specified by the User
property and Password properties.
J2SE 1.4 or higher is required to use encrypted user ID and password authentication.
AuthenticationMethod
property to encryptedUIDPassword, encryptedPassword, or clearText (the default). See Using the AuthenticationMethod Property for more information about setting a value for this property.User
property to provide the user ID.Password
property to provide the password.This section provides requirements and instructions for configuring Kerberos authentication for the DB2 driver.
Verify that your environment meets the requirements listed in Table 3-4 before you configure the driver for Kerberos authentication.
During installation of the WebLogic Server JDBC drivers, the following files required for Kerberos authentication are installed in the WL_HOME
\server\lib
folder, where WL_HOME
is the directory in which you installed WebLogic Server:
AuthenticationMethod
property to kerberos. See Using the AuthenticationMethod Property for more information about setting a value for this property.Note: | If using Windows Active Directory, the Kerberos realm name is the Windows domain name and the KDC name is the Windows domain controller name. |
For example, if your Kerberos realm name is XYZ.COM and your KDC name is kdc1, your krb5.conf file would look like this:
[libdefaults]
default_realm = XYZ.COM
[realms]
XYZ.COM = {
kdc = kdc1
}
If the krb5.conf file does not contain a valid Kerberos realm and KDC name, the following exception is thrown:
Message:[BEA][DB2 JDBC Driver]Could not establish a connection using integrated security: No valid credentials provided
The krb5.conf file installed with the WebLogic JDBC drivers is configured to load automatically unless the java.security.krb5.conf system property is set to point to another Kerberos configuration file.
By default, when Kerberos authentication is used, the DB2 driver takes advantage of the user name and password maintained by the operating system to authenticate users to the database. By allowing the database to share the user name and password used for the operating system, users with a valid operating system account can log into the database without supplying a user name and password.
There may be times when you want the driver to use another set of user credentials. For example, many application servers or Web servers act on behalf of the client user logged on the machine on which the application is running, rather than the server user.
If you want the driver to use user credentials other than the server user’s operating system credentials, include code in your application to obtain and pass a javax.security.auth.Subject used for authentication as shown in the following example.
import javax.security.auth.Subject;
import javax.security.auth.login.LoginContext;
import java.sql.*;
// The following code creates a javax.security.auth.Subject instance
// used for authentication. Refer to the Java Authentication
// and Authorization Service documentation for details on using a
// LoginContext to obtain a Subject.
LoginContext lc = null;
Subject subject = null;
try {
lc = new LoginContext("JaasSample", new TextCallbackHandler());
lc.login();
subject = lc.getSubject();
}
catch (Exception le) {
... // display login error
}
// This application passes the javax.security.auth.Subject
// to the driver by executing the driver code as the subject
Connection con =
(Connection) Subject.doAs(subject, new PrivilegedExceptionAction() {
public Object run() {
Connection con = null;
try {
Class.forName("com.ddtek.jdbc.db2.DB2Driver");
String url = "jdbc:bea:db2://myServer:50000;
DatabaseName=jdbc";
con = DriverManager.getConnection(url);
}
catch (Exception except) {
... //log the connection error
Return null;
}
return con;
}
});
// This application now has a connection that was authenticated with
// the subject. The application can now use the connection.
Statement stmt = con.createStatement();
String sql = "select * from employee";
ResultSet rs = stmt.executeQuery(sql);
... // do something with the results
To use Kerberos authentication, the application user first must obtain a Kerberos Ticket Granting Ticket (TGT) from the Kerberos server. The Kerberos server verifies the identity of the user and controls access to services using the credentials contained in the TGT.
If the application uses Kerberos authentication from a Windows client, the application user does not need to explicitly obtain a TGT. Windows Active Directory automatically obtains a TGT for the user.
If the application uses Kerberos authentication from a UNIX or Linux client, the user must explicitly obtain a TGT. To explicitly obtain a TGT, the user must log onto the Kerberos server using the kinit command. For example, the following command requests a TGT from the server with a lifetime of 10 hours, which is renewable for 5 days:
kinit -l 10h -r 5d user
where user
is the application user.
Refer to your Kerberos documentation for more information about using the kinit command and obtaining TGTs for users.
Set the AuthenticationMethod
property to client. See Using the AuthenticationMethod Property for more information about setting a value for this property.
The DB2 driver now supports SSL encryption for DB2 V5R3 and higher for iSeries. SSL secures the integrity of your data by encrypting information and providing authentication. The DB2 driver supports both SSL server authentication and SSL client authentication.
See SSL Encryption for more information.
Note: | Data encryption may adversely affect performance because of the additional overhead (mainly CPU usage) required to encrypt and decrypt data. |
Note: | Connection hangs can occur when the driver is configured for SSL and the database server does not support SSL. You may want to set a login timeout using the LoginTimeout property to avoid problems when connecting to a server that does not support SSL. |
EncryptionMethod
property to SSL.TrustStore
and TrustStore
properties or their corresponding Java system properties (javax.net.ssl.trustStore
and javax.net.ssl.trustStorePassword
, respectively).ValidateServerCertificate
property to true.HostNameInCertificate
property to a host name to be used to validate the certificate. The HostNameInCertificate
property provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.KeyStore
and KeyStorePassword
properties or their corresponding Java system properties (javax.net.ssl.keyStore
and javax.net.ssl.keyStorePassword
, respectively). KeyPassword
property to the key password.The EncryptionMethod connection property supports a new value, SSL, that enables SSL encryption. New connection properties that control how the driver implements SSL encryption are:
Table 3-5 describes these connection properties.
{noEncryption | DBEncryption | requestDBEncryption | SSL} . Determines whether data is encrypted and decrypted when transmitted over the network between the driver and database server. The DB2 driver supports the following encryption methods:
If set to
DBEncryption , data is encrypted using DES encryption if the database server supports it. If the database server does not support DES encryption, the connection fails and the driver throws an exception. This value is supported for DB2 for Linux/UNIX/Windows and DB2 for z/OS.
If set to
requestDBEncryption , data is encrypted using DES encryption if the database server supports it. If the database server does not support DES encryption, the driver attempts to establish an unencrypted connection. This value is supported for DB2 for Linux/UNIX/Windows and DB2 for z/OS.
If set to
SSL , data is encrypted using SSL. If the database server does not support SSL, the connection fails and the driver throws an exception. This value is supported for DB2 V5R3 and higher for iSeries.
See Configuring SSL Encryption for more information about configuring data encryption.
|
|||
{
host_name | #SERVERNAME# }. Specifies a host name for certificate validation when SSL encryption is enabled (EncryptionMethod=SSL ) and validation is enabled (ValidateServerCertificate=tru e). This property is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
If a host name is specified, the driver compares the specified host name to the DNSName value of the
SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName , the driver compares the host name with the Common Name (CN) part of the certificate’s Subject name. If the values do not match, the connection fails and the driver throws an exception.
If
#SERVERNAME# is specified, the driver compares the server name specified in the connection URL or data source of the connection to the DNSName value of the SubjectAlternativeName in the certificate. If a DNSName value does not exist in the SubjectAlternativeName or if the certificate does not have a SubjectAlternativeName, the driver compares the host name to the CN parts of the certificate’s Subject name. If the values do not match, the connection fails and the driver throws an exception.
NOTE: If multiple CN parts are present, the driver validates the host name against each CN part. If any one validation succeeds, a connection is established.
|
|||
Specifies the password used to access the individual keys in the keystore file when SSL is enabled using the EncryptionMethod property and SSL client authentication is enabled on the database server. This property is useful if any of the keys in the keystore file have a different password than the keystore file.
|
|||
Specifies the directory of the keystore file to be used when SSL is enabled using the
EncryptionMethod property and SSL client authentication is enabled on the database server. The keystore file contains the certificates that the client sends to the server in response to the server’s certificate request.
|
|||
Specifies the password used to access the keystore file when SSL is enabled using the
EncryptionMethod property and SSL client authentication is enabled on the database server. The keystore file contains the certificates that the client sends to the server in response to the server’s certificate request.
|
|||
Specifies the directory of the truststore file to be used when SSL is enabled using the
EncryptionMethod property and server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.
|
|||
Specifies the password used to access the truststore file when SSL is enabled using the
EncryptionMethod property and server authentication is used. The truststore file contains a list of the Certificate Authorities (CAs) that the client trusts.
|
|||
{
true | false }. Determines whether the driver validates the certificate sent by the database server when SSL encryption is enabled (EncryptionMethod=SSL ). When using SSL server authentication, any certificate sent by the server must be issued by a trusted Certificate Authority (CA). Allowing the driver to trust any certificate returned from the server even if the issuer is not a trusted CA is useful in test environments because it eliminates the need to specify truststore information on each client in the test environment.
If set to
true (the default), the driver validates the certificate sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. If the HostNameInCertificate property is specified, the driver also validates the certificate using a host name. The HostNameInCertificate property is optional and provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
|
To ensure that catalog methods function correctly when the CatalogSchema
property is set to a schema other than the default schema, views for the catalog tables listed in Table 3-6 must exist in the specified schema. The views that are required depend on your DB2 database.
See SQL Escape Sequences for JDBC for information about SQL escape sequences supported by the DB2 driver.
The DB2 driver supports the isolation levels listed in Table 3-7. JDBC isolation levels are mapped to the appropriate DB2 transaction isolation levels as shown. The default isolation level is Read Committed
.
No Commit1
|
|
1Supported for DB2 iSeries versions that do not enable journaling. |
The DB2 driver supports scroll-insensitive result sets and updatable result sets.
Note: | When the DB2 driver cannot support the requested result set type or concurrency, it automatically downgrades the cursor and generates one or more SQLWarnings with detailed information. |
To use distributed transactions through JTA with the DB2 driver, you must use one of the following database versions:
Retrieving and updating Blobs is supported by the DB2 driver with the following databases:
Retrieving and updating Clobs is supported by the DB2 driver with all supported DB2 databases. The DB2 driver supports Clobs up to a maximum of 2 GB with the following DB2 databases:
The DB2 driver supports retrieving and updating Clobs up to a maximum of 32 KB with all other supported DB2 databases.
Retrieving and updating DBClobs is supported by the DB2 driver with the following databases:
The DB2 driver uses the native DB2 batch mechanism. By default, the methods used to set the parameter values of a batch performed using a PreparedStatement must match the database data type of the column with which the parameter is associated.
DB2 servers do not perform implicit data conversions, so specifying parameter values that do not match the column data type causes the DB2 server to generate an error. For example, to set the value of a Blob parameter using a stream or byte array when the length of the stream or array is less than 32 KB, you must use the setObject()
method and specify the target JDBC type as BLOB; you cannot use the setBinaryStream()
or setBytes()
methods.
To remove the method-type restriction, set the BatchPerformanceWorkaround
property to true. For example, you can use the setBinaryStream()
or setBytes()
methods to set the value of a Blob parameter regardless of the length of the stream or array; however, the parameter sets may not be executed in the order they were specified. Performance may be decreased because the driver must convert the parameter data to the correct data type and re-execute the statement.
Notes: | When you create a data source in the Administration Console, the Administration Console sets the BatchPeformanceWorkaround connection property to true by default. |
Note: | For data sources used as a JMS JDBC store that use the WebLogic Type 4 JDBC driver for DB2, the BatchPerformanceWorkaround property must be set to true. |
The DB2 driver supports returning parameter metadata as described in this section.
The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:
For DB2 v7x for Linux/UNIX/Windows and DB2 V5R1 for iSeries, the DB2 driver supports returning parameter metadata for the following forms of Insert and Update statements:
where operator
is any of the following SQL operators: =, <, >, <=, >=, and <>.
The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:
For DB2 v7x for Linux/UNIX/Windows and DB2 V5R1 for iSeries, the DB2 driver supports returning parameter metadata for Select statements that contain parameters in ANSI SQL 92 entry-level predicates, for example, such as COMPARISON, BETWEEN, IN, LIKE, and EXISTS predicate constructs. Refer to the ANSI SQL reference for detailed syntax.
Parameter metadata can be returned for a Select statement if one of the following conditions is true:
SELECT * FROM foo WHERE bar > ?
SELECT * FROM foo WHERE (SELECT x FROM y
WHERE z = 1) < ?
The following Select statements show further examples for which parameter metadata can be returned:
SELECT col1, col2 FROM foo WHERE col1 = ? and col2 > ?
SELECT ... WHERE colname = (SELECT col2 FROM t2
WHERE col3 = ?)
SELECT ... WHERE colname LIKE ?
SELECT ... WHERE colname BETWEEN ? and ?
SELECT ... WHERE colname IN (?, ?, ?)
SELECT ... WHERE EXISTS(SELECT ... FROM T2 WHERE col1 < ?)
ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:
SELECT * FROM t1 WHERE col = ? ORDER BY 1
Joins are supported. For example:
SELECT * FROM t1,t2 WHERE t1.col1 = ?
Fully qualified names and aliases are supported. For example:
SELECT a, b, c, d FROM T1 AS A, T2 AS B WHERE A.a = ?
and B.b = ?"
The DB2 driver supports returning parameter metadata for stored procedure arguments.
If your application requires table name information, the DB2 driver can return table name information in ResultSet metadata for Select statements. By setting the ResultSetMetaDataOptions
property to 1, the DB2 driver performs additional processing to determine the correct table name for each column in the result set when the ResultSetMetaData.getTableName()
method is called. Otherwise, the getTableName()
method may return an empty string for each column in the result set.
The table name information that is returned by the DB2 driver depends on whether the column in a result set maps to a column in a table in the database. For each column in a result set that maps to a column in a table in the database, the DB2 driver returns the table name associated with that column. For columns in a result set that do not map to a column in a table (for example, aggregates and literals), the DB2 driver returns an empty string.
The Select statements for which ResultSet metadata is returned may contain aliases, joins, and fully qualified names. The following queries are examples of Select statements for which the ResultSetMetaData.getTableName()
method returns the correct table name for columns in the Select list:
SELECT id, name FROM Employee
SELECT E.id, E.name FROM Employee E
SELECT E.id, E.name AS EmployeeName FROM Employee E
SELECT E.id, E.name, I.location, I.phone FROM Employee E,
EmployeeInfo I WHERE E.id = I.id
SELECT id, name, location, phone FROM Employee,
EmployeeInfo WHERE id = empId
SELECT Employee.id, Employee.name, EmployeeInfo.location,
EmployeeInfo.phone FROM Employee, EmployeeInfo
WHERE Employee.id = EmployeeInfo.id
The table name returned by the driver for generated columns is an empty string. The following query is an example of a Select statement that returns a result set that contains a generated column (the column named "upper").
SELECT E.id, E.name as EmployeeName, {fn UCASE(E.name)}
AS upper FROM Employee E
The DB2 driver also can return schema name and catalog name information when the ResultSetMetaData.getSchemaName()
and ResultSetMetaData.getCatalogName()
methods are called if the driver can determine that information. For example, for the following statement, the DB2 driver returns "test" for the catalog name, "test1" for the schema name, and "foo" for the table name:
SELECT * FROM test.test1.foo
The additional processing required to return table name, schema name, and catalog name information is only performed if the ResultSetMetaData.getTableName()
, ResultSetMetaData.getSchemaName()
, or ResultSetMetaData.getCatalogName()
methods are called.
The DB2 driver supports any JSR 114 implementation of the RowSet interface, including:
J2SE 1.4 or higher is required to use rowsets with the driver.
See http://www.jcp.org/en/jsr/detail?id=114 for more information about JSR 114.
The DB2 driver supports retrieving the values of auto-generated keys. An auto-generated key returned by the DB2 driver is the value of an auto-increment column.
An application can return values of auto-generated keys when it executes an Insert statement. How you return these values depends on whether you are using an Insert statement that contains parameters:
Insert
statement that does not contain any parameters, the DB2 driver supports the following form of the Statement.execute()
and Statement.executeUpdate()
methods to instruct the driver to return values of auto-generated keys: Statement.execute(String
sql
, int
autoGeneratedKeys
)
Statement.execute(String
sql
, int[]
columnIndexes
)
Statement.execute(String
sql
, String[]
columnNames
)
Statement.executeUpdate(String
sql
, int
autoGeneratedKeys
)
Statement.executeUpdate(String
sql
, int[]
columnIndexes
)
Statement.executeUpdate(String
sql
, String[]
columnNames
)
Insert
statement that contains parameters, the DB2 driver supports the following form of the Connection.prepareStatement
method to inform the driver to return the values of auto-generated keys:
An application can retrieve values of auto-generated keys using the Statement.getGeneratedKeys()
method. This method returns a ResultSet object with a column for each auto-generated key.
The new Database connection property can be used as a synonym of the DatabaseName
connection property.
If both the Database and DatabaseName connection properties are specified in a connection URL, the last of either property positioned in the connection URL is used. For example, if your application specifies the following connection URL, the value of the Database connection property would be used instead of the value of the DatabaseName connection property.
jdbc:bea:db2://server1:50000;DatabaseName=jdbc;Database=acct;
User=test;Password=secret
The LocationName connection property is only supported when connecting to DB2 for z/OS or iSeries to specify the name of the DB2 location. Now, your application can use the DatabaseName connection property when you are connecting to DB2 for Linux/UNIX/Windows, z/OS, or iSeries.
When connecting to DB2 for Linux/UNIX/Windows, the DatabaseName connection property specifies the name of the database. When connecting to DB2 for z/OS or iSeries, the DatabaseName connection property specifies the name of the DB2 location.
Table 3-8 and Table 3-9 list these data types and describe how they are mapped to JDBC data types.
See GetTypeInfo, for a description of the data types returned by the getTypeInfo() method.
For more information about using the XML data type, see Returning and Inserting/Updating XML Data.
For information about other data types supported by the DB2 driver, see Data Types.
SQL Procedures now are supported for DB2 v9.1 for z/OS.
The DB2 driver now supports IPv6 for DB2 v9.1 for z/OS.
For more information about IPv6, see Using IP Addresses.