Sysibm Table For Auto Generated Key

Posted on by

When using IBM DB2, there may come a time where you wish to view the structure of your database or even of specific tables. In this brief tutorial, we’ll explore two different methods of examining your database tables in more detail to see just what the schema really looks like.

The SYSIBM.SYSTABLES table contains one row for each table, view, or alias. SYSIBM.SYSTABLES table. Number of columns in the primary key of the table. The value is.

  1. Sep 22, 2006  how to get the last generated value of an identity column for a given table? DB2 Database Forums on Bytes.
  2. The Element The element is used to specify properties for auto generated keys (from identity field or sequences). If you specify this element, Abator will generate an appropriate element inside the generated element in the SQL map.
  3. Auto-Generated Keys Support. 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. How you return those values depends on whether you are using an Insert statement that contains parameters.
  4. After an XML table is created, query on TYPESCHEMA of some generated auxiliary XML info columns in SYSIBM.SYSCOLUMNS IBM PK71129: GARBAGE VALUE IS FOUND IN COLUMN 'TYPESCHEMA' OF SYSIBM.SYSCOLUMNS AFTER CREATING AN XML TABLE.

Using SYSIBM Catalogs

The first method for examining your database table structure makes use of the handy SYSIBM catalogs provided by DB2, which contain a wide array of meta information about the database and the tables therein.

Specifically, we can utilize either SYSIBM.SYSTABLES or SYSIBM.SYSCOLUMNS. For generic meta data about the table itself, you’ll likely wish to query SYSIBM.SYSTABLES, but if you’re specifically looking for the schema (column names, data types, etc), you’ll instead need to use SYSIBM.SYSCOLUMNS.

A basic query using .SYSCOLUMNS would look something like this:

The column names we’re selecting are fairly self-explanatory, with perhaps the exception of SCALE which defines the number of fractional digits for DECIMAL column types.

For example, if we wanted to return a query of column information about our books table, we’d just replace MyTableName with books:

Using the DESCRIBE Command

For a simpler query that forgoes control over the data returned in the result set, you may also use the DESCRIBE command.

DESCRIBE essentially displays the meta data contained within the assorted SYSIBM catalogs mentioned above, but only does so based on the type of DESCRIBE query you execute. You can ask DB2 to DESCRIBE information about tables, indexes, columns, and even specific SELECT statement queries, if so desired.

For our purposes, we’ll use DESCRIBE to once again retrieve the details of a specific table, which is performed with this simple query:

Thus, if we want to retrieve information about our books table that is part of our bookstore schema, we’d simply execute the following query:

The results will look something like this, with similar information to that of our manual SYSIBM.SYSCOLUMNS query:

Viewing Table Indexes

In addition to the basic column information above, you may also wish to view the indices that are part of your table. Thankfully, there is a simple option with the DESCRIBE keyword to query this data quite easily:

The result set is a detailed list of all indices connected to that table, like so:

There you have it. With one or two simple queries, you can get the full details of your table schema while using IBM DB2.

This script outputs the google search URL required for search on edocs documentation.Sysibm Table For Auto Generated Key

WebLogic Type 4 JDBC Drivers

Auto-generated Definition

Note: The BEA WebLogic Type 4 JDBC DB2 driver is available in the WebLogic Server 8.1 Service Pack 2 and Service Pack 3 releases. It is not available with the WebLogic Server 8.1 GA, Service Pack 1, and Service Pack 4 releases. A patch is available for WebLogic Server Service Pack 4 at the following URL: http://dev2dev.bea.com/products/wlplatform81/patch/wlplat81sp4_db2_patch.jsp

The following sections describe how to configure and use the BEA WebLogic Type 4 JDBC driver for DB2:

The BEA WebLogic Type 4 JDBC driver for DB2 (the 'DB2 driver') supports:

  • DB2 Universal Database (UDB) v7.x, v8.1, and v8.2 on Linux, UNIX, and Windows via DRDA
  • DB2 UDB v7.x and v8.1 for z/OS via DRDA
  • DB2 UDB V5R1, V5R2, and V5R3 for iSeries via DRDA

Note: This documentation uses the following terms to describe the different DB2 versions:

  • 'DB2 for Linux/UNIX/Windows' refers to all versions of DB2 on Linux, UNIX, and Windows platforms.
  • 'DB2 for z/OS' refers to all versions of DB2 on z/OS platforms.
  • 'DB2 for iSeries' refers to all versions of DB2 on iSeries platforms.

The driver class for the BEA WebLogic Type 4 JDBC DB2 driver is:

Use these driver classes when configuring a JDBC connection pool in your WebLogic Server domain.

To connect to a DB2 database, use the appropriate URL format:

Sysibm Table For Auto Generated Key
  • DB2 on Windows NT, Windows 2000, Windows 2003, Windows XP, UNIX, Linux, and Linux/s3901:
  • DB2 on OS/390, z/OS, iSeries, and AS/4001:

Table 2-1 lists the JDBC connection properties supported by the DB2 driver, and describes each property. You can use these connection properties in a JDBC connection pool configuration in your WebLogic Server domain. To specify a property, use the following form in the JDBC connection pool configuration:

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 connection pool.

Table 2-1 DB2 Connection Properties

Property

Description

AddToCreateTable

OPTIONAL

A string that is automatically added to all Create Table statements. This field is primarily for users who need to add an 'in database' clause.

AllowImplicitResultSetCloseForXA

OPTIONAL

{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.

Note: Problems have been noted when using this mechanism. As a workaround, you should add AllowImplicitResultSetCloseForXA=false to the properties in your connection pool configuration.

The default is true.

AlternateID

OPTIONAL

Sets the default DB2 schema used by unqualified SQL identifiers to the specified value. The value must be a valid DB2 schema.

BatchPerformanceWorkaround

OPTIONAL

{true false}. For DB2 UDB 8.1, the native DB2 batch mechanism is used. This property determines whether certain restrictions are enforced to facilitate data conversions.

  • When set to false, the methods used to set the parameter values of a batch operation performed using a PreparedStatement must match the database data type of the column the parameter is associated with. This is because DB2 servers do not perform implicit data conversions.

  • When set to true, this restriction is removed; however, parameter sets may not be executed in the order they were specified.

The default is false.

See Performance Workaround for Batch Inserts and Updates for more information.

Note: For connection pools used as a JMS JDBC store that use the WebLogic Type 4 JDBC driver for DB2, the BatchPerformanceWorkaround property must be set to true.

CatalogIncludesSynonyms

OPTIONAL

{true false}. When set to true, synonyms are included in the result sets returned from the following DatabaseMetaData methods: getColumns, getProcedureColumns, and getIndexInfo. When set to false, synonyms are omitted from result sets.

The default is true.

CatalogSchema

OPTIONAL

The DB2 schema to use for catalog functions. The value must be the name of a valid DB2 schema.

The default is SYSCAT for DB2 UDB, SYSIBM for DB2 OS/390, and QSYS2 for DB2 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 Using a Non-Default Schema for Catalog Methods for the required views of catalog tables.

CharsetFor65535

OPTIONAL

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 Java Virtual Machine, for example, CharsetFor65535=CP950. This property has no effect when writing data to character columns defined with CCSID 65535.

CodePageOverride

OPTIONAL Monster hunter generations key quests story.

A code page to be used to convert Character and Clob data. The specified code page overrides the default database code page. 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 Java Virtual Machine, for example, CodePageOverride=CP950.

CollectionId

OPTIONAL

The collection (group of packages) to which the package is bound.

This property is ignored for DB2 UDB.

The default is NULLID.

ConnectionRetryCount

OPTIONAL

The number of times the driver retries connection attemptsuntil 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.

The default is 0.

ConnectionRetryDelay

OPTIONAL

The number of seconds the driver will wait between connection retry attempts when ConnectionRetryCount is set to a positive integer.

The default is 3.

CreateDefaultPackage

OPTIONAL

{true false}. Determines whether the default package should be created. For DB2 OS/390 and DB2 iSeries, the package is created in the collection specified by the CollectionId property. This would be used if the package does not yet exist.

For more information about creating DB2 packages, see Creating a DB2 Package.

The default is false.

DatabaseName

The name of the database to which you want to connect (used with UDB).

DynamicSections

OPTIONAL

Specifies the number of statements that the DB2 driver package can prepare for a single user.

The default is 200.

Grantee

OPTIONAL

Specifies the name of the schema to which you want to grant EXECUTE privileges for DB2 packages. This property is ignored if the GrantExecute property is set to false.

See Creating a DB2 Package for more information about creating DB2 packages.

The default is PUBLIC.

GrantExecute

OPTIONAL

{true false}. Determines whether EXECUTE privileges for DB2 packages are granted to a schema other than the one used to create them. If set to true, EXECUTE privileges are granted to the schema specified by the Grantee property. If set to false, EXECUTE privileges are not granted to another schema.

See Creating a DB2 Package for more information about creating DB2 packages.

The default is true.

InsensitiveResultSetBufferSize

{-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.

The default is 2048 (KB)

LocationName

The name of the DB2 location that you want to access (used with OS/390 and iSeries).

LoginTimeout

OPTIONAL

The maximum time in seconds that attempts to create a database connection will wait. A value of 0 specifies that the timeout is the default system timeout if there is one; otherwise it specifies that there is no timeout.

PackageOwner

OPTIONAL

Specifies the owner of DB2 packages.

See Creating a DB2 Package for more information about creating DB2 packages.

The default is NULL.

Password

A case-sensitive password used to connect to your DB2 database. A password is required only if security is enabled on your database. If so, contact your system administrator to get your password.

PortNumber

OPTIONAL

The TCP port on which the database server listens for connections. The default is 50000.

ReplacePackage

OPTIONAL

{true false}. Specifies whether the current bind process should replace an existing DB2 package. On DB2 UDB, this property must be used in conjunction with CreateDefaultPackage.

For more information about creating DB2 packages, see Creating a DB2 Package.

The default is false.

SecurityMechanism

OPTIONAL

{ClearText EncryptedPassword EncryptedUIDPassword}. Determines the security method the driver uses to authenticate the user to the DB2 server when establishing a connection. If the specified authentication method is not supported by the DB2 server, the connection fails and the driver generates an exception.

If set to ClearText, the driver sends the password in clear text to the DB2 server for authentication.

If set to EncryptedPassword, the driver sends an encrypted password to the DB2 server for authentication.

If set to EncryptedUIDPassword, the driver sends an encrypted user ID and password to the DB2 server for authentication.

The default is ClearText.

Requires JDK 1.4 or higher.

SendStreamAsBlob

OPTIONAL

{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 stream data that is less than 32K bytes can be inserted into a Long Varchar for Bit Data column, which has a maximum length of 32K bytes, or a Blob column. 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 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 stream passed into the driver is resettable. Sending binary stream data that is less than 32K bytes in length initially as a Blob significantly improves performance if the Insert or Update column is a Blob column.

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.

The default is false.

ServerName

The name or IP address of the database server.

StripNewlines

OPTIONAL

{true false}. Specifies whether new-line characters in a SQL statement are sent to the DB2 server. When StripNewlines=true, the DB2 driver removes all new-line characters from SQL statements.

The default is true.

UseCurrentSchema

OPTIONAL

{true false}. Specifies whether results are restricted to the tables in the current schema if a DatabaseMetaData.getTables call is called without specifying a schema or if the schema is specified as the wildcard character %. Restricting results to the tables 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 method are restricted to tables in the current schema. If set to false, results of the getTables method are not restricted.

The default is false.

User

The case-sensitive user name used to connect to your DB2 database.

WithHoldCursors

OPTIONAL

{true false}. Determines whether the cursor stays open on commit—either DB2 closes all open cursors (Delete cursors) after a commit or leaves them open (Preserve cursors). If set to true, the cursor behavior is Preserve. If set to false, the cursor behavior is Delete. Rolling back a transaction closes all cursors regardless of how this property is specified.

The default is true.


Table 2-2 lists configuration attributes for a sample WebLogic Server connection pool that uses the non-XA version of the WebLogic Type 4 DB2 JDBC driver. Table 2-3 lists configuration attributes for a sample WebLogic Server connection pool that uses the XA version of the driver.

Table 2-2 Connection Pool Attributes Using the Non- XA WebLogic Type 4 DB2 JDBC Driver

Attribute

Value

URL

jdbc:bea:db2://host:port

Driver Class Name

weblogic.jdbc.db2.DB2Driver

Properties

user=username
PortNumber=port
ServerName=host
DatabaseName=dbname
batchPerformanceWorkaround=true
AllowImplicitResultSetCloseForXA=false

Password

password

Target

server or cluster name


Table 2-3 Connection Pool Attributes Using the XA WebLogic Type 4 DB2 JDBC Driver

Attribute

Value

URL

jdbc:bea:db2://host:port

Driver Class Name

weblogic.jdbcx.db2.DB2DataSource

Properties

Press Install button3. Free warcraft 3 cd key generator 2020.

user=username
PortNumber=port
ServerName=host
DatabaseName=dbname
batchPerformanceWorkaround=true
AllowImplicitResultSetCloseForXA=false

SupportsLocalTransaction

true (required only for local transactions)

Password

password

Target

server or cluster name

Setting the following connection properties for the DB2 driver as described in the following list can improve performance for your applications:

CatalogIncludesSynonyms

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.1 or v7.2 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.1 and v8.2 for Linux/UNIX/Windows.

CatalogSchema

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 Using a Non-Default Schema for Catalog Methods for views for catalog tables that must exist in the specified schema.

InsensitiveResultSetBufferSize

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.

MaxPooledStatements

To improve performance, the driver's own internal prepared statement pooling should be enabled when the driver does not run from within an application server or from within another application that does not provide its own prepared statement pooling. When the driver's internal prepared statement pooling is enabled, the driver caches a certain number of prepared statements created by an application. For example, if the MaxPooledStatements property is set to 20, the driver caches the last 20 prepared statements created by the application. If the value set for this property is greater than the number of prepared statements used by the application, all prepared statements are cached.

ResultSetMetaDataOptions

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.

SendStreamAsBlob

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.

StripNewLines

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. In this case, the StripNewlines property should be set to false.

UseCurrentSchema

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:

  1. Obtain the 'Relational Database' value by executing the WRKRDBDIRE command on AS/400.
  2. You should see output similar to the following:

  3. In the Java client, set up the Properties object with 'user' and 'password' DB2 connection properties (see DB2 Connection Properties).
  4. In Driver.connect(), specify the following string and the Properties object as parameters:
  5. 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:

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.

Note: 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.

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:

  • Manually force the DB2 driver to create a package using the WebLogic Server dbping utility. See Creating a DB2 Package Using dbping.
  • Automatically create a package by setting specific connection properties in the connection URL. See Creating a DB2 Package Using Connection Properties.

Note: Your user ID must have CREATE PACKAGE privileges on the database, or your database administrator must create packages for you.

Your user ID (the user ID listed in the JDBC connection pool configuration) must be the owner of the package.

Sysibm Table For Auto Generated Key Fob

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.

Creating a DB2 Package Using dbping

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.

Creating a DB2 Package Using Connection Properties

You can create a DB2 package automatically by specifying specific connection properties in the initial connection URL. Table 2-4 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 connection pools because every connection in the connection pool uses the same URL and connection properties. When a JDBC connection pool with multiple connections is created, the package would be recreated when each database connection is created.

Table 2-4 Connection Properties for an Initial Connection URL When Creating DB2 Packages

Property

Database

PackageCollection=collection_name
(where collection_nameis the name of the collection or library to which DB2 packages are bound)

DB2 for z/OS and iSeries

CreateDefaultPackage=true

DB2 for Linux/UNIX/Windows, z/OS, and iSeries

ReplacePackage=true

DB2 for Linux/UNIX/Windows

DynamicSections=x
(where x is a positive integer)

DB2 for Linux/UNIX/Windows, z/OS, and iSeries


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.

Example for DB2 for Linux/UNIX/Windows:

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.

Example for DB2 for z/OS and iSeries:

The following URL creates DB2 packages with 400 dynamic sections.

Notes About Increasing Dynamic Sections in the DB2 Package

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:

  • Creating DB2 packages with a large number of dynamic sections may exhaust certain server resources. In particular, you may need to increase the database parameter PCKCACHE_SZ to allow the larger packages to be created.
  • The creation of more dynamic sections will slow down the initial creation of the DB2 package.
  • Using DB2 packages with a large number of dynamic sections may impact application performance. If a small number of sections are in use at one time, there will be no impact on the application. If a large number of sections are in use at one time, the performance of the application may decrease because the database will expend resources to check all open sections for locks.
  • As the number of open sections increases, so does the likelihood that a deadlock situation may occur.
  • If your application is mostly executing select statements, it is best to operate in the default mode of automatically committing the database. Dynamic sections are not freed in the DB2 package until the database is committed even if the statements are closed in the application. In this mode the database will commit every time a SQL statement is executed and free all of the sections that were opened. If you need to operate in a manual commit mode, then it is advisable to commit the database as often as possible to ensure that all server resources are freed in a timely manner.
  • Statements cached in the WebLogic Server prepared statement cache will keep sections in use so that the prepared statements can be reused.
  • The DB2 server has a limit on dynamic sections. It is possible to try to create more sections than the server will allow you to create.

Table 2-5 lists the data types supported by the DB2 driver and how they are mapped to JDBC data types.

Table 2-5 DB2 Data Types

DB2 Data Type

JDBC Data Type

Bigint1

BIGINT

Blob2

BLOB

Char

CHAR

Char for Bit Data

BINARY

Clob

CLOB

Date

DATE

DBClob3

CLOB

Decimal

DECIMAL

Double

DOUBLE

Float

FLOAT

Integer

INTEGER

Long Varchar

LONGVARCHAR

Long Varchar for Bit Data

LONGVARBINARY

Numeric

NUMERIC

Real

REAL

Rowid4

VARBINARY

Smallint

SMALLINT

Time

TIME

Timestamp

TIMESTAMP

Varchar

VARCHAR

Varchar for Bit Data

VARBINARY


1. Supported only for DB2 v8.1 and v 8.2 for Linux/UNIX/Windows.
2. Supported 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).
3. Supported 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).
4. Supported only for DB2 for z/OS, and DB2 V5R2 and V5R3 for iSeries.

See GetTypeInfo for more information about data types.

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 2-6 must exist in the specified schema. The views that are required depend on your DB2 database.

Table 2-6 Catalog Tables for DB2

Database

Catalog Tables

DB2 for Linux/UNIX/Windows

SYSCAT.TABLES
SYSCAT.COLUMNS
SYSCAT.PROCEDURES
SYSCAT.PROCPARAMS
SYSCAT.COLAUTH
SYSCAT.TABAUTH
SYSCAT.KEYCOLUSE
SYSCAT.INDEXES
SYSCAT.INDEXCOLUSE
SYSCAT.REFERENCES
SYSCAT.SYSSCHEMATA
SYSCAT.TYPEMAPPINGS
SYSCAT.DBAUTH

DB2 for z/OS

SYSIBM.SYSTABCONST
SYSIBM.SYSTABLES
SYSIBM.SYSSYNONYMS
SYSIBM.SYSCOLUMNS
SYSIBM.SYSPROCEDURES
SYSIBM.SYSROUTINES
SYSIBM.SYSPARMS
SYSIBM.SYSCOLAUTH
SYSIBM.SYSTABAUTH
SYSIBM.SYSKEYS
SYSIBM.SYSINDEXES
SYSIBM.SYSRELS
SYSIBM.SYSFOREIGNKEYS
SYSIBM.SYSSCHEMAAUTH
SYSIBM.SYSDBAUTH

DB2 for iSeries

QSYS2.SYSCST
QSYS2.SYSKEYCST
QSYS2.SYSPROCS
QSYS2.SYSPARMS
QSYS2.SYSTABLES
QSYS2.SYSSYNONYMS
QSYS2.SYSCOLUMNS
QSYS2.SQLTABLEPRIVILEGES
QSYS2.SYSKEYS
QSYS2.SYSINDEXES
QSYS2.SYSREFCST


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 2-7. JDBC isolation levels are mapped to the appropriate DB2 transaction isolation levels as shown. The default isolation level is Read Committed.

Table 2-7 Supported Isolation Levels

Db2 Sysibm

JDBC Isolation Level

DB2 Isolation Level

None

No Commit1

Read Committed

Cursor Stability

Read Uncommitted

Uncommitted Read

Repeatable Read

Read Stability

Serializable

Repeatable Read


1. Supported for DB2 iSeries versions that do not enable journaling.

The DB2 driver supports scroll-insensitive result sets and updatable result sets.

Sysibm Table For Auto Generated Key Replacement

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, DB2 v8.1 or v8.2 for Linux/UNIX/Windows is required.

Retrieving and updating Blobs is supported by the DB2 driver with the following databases:

  • DB2 v8.1 and v8.2 for Linux/UNIX/Windows
  • DB2 for z/OS
  • DB2 V5R2 and V5R3 for iSeries

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:

  • DB2 v8.1 and v8.2 for Linux/UNIX/Windows
  • DB2 for z/OS
  • DB2 V5R2 and V5R3 for iSeries

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:

  • DB2 v8.1 and v8.2 for Linux/UNIX/Windows
  • DB2 7.x for z/OS
  • DB2 V5R2 and V5R3 for iSeries

For DB2 v8.1 and v8.2 for Linux/UNIX/Windows, DB2 for z/OS, and DB2 for iSeries, 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.

Notes: When you create a connection pool in the Administration Console, the Administration Console sets the BatchPeformanceWorkaround connection property to true by default.

For connection pools 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.

Insert and Update Statements

The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:

  • DB2 v8.1 and v8.2 for Linux/UNIX/Windows
  • DB2 for z/OS
  • DB2 V5R2 and V5R3 for iSeries

For all other supported DB2 databases, the DB2 driver supports returning parameter metadata for the following forms of Insert and Update statements:

  • INSERT INTO foo VALUES (?, ?, ?)
  • INSERT INTO foo (col1, col2, col3) VALUES (?, ?, ?)
  • UPDATE foo SET col1=?, col2=?, col3=? WHERE col1 operator ? [{AND OR} col2 operator ?]

where operator is any of the following SQL operators: =, <, >, <=, >=, and <>.

Select Statements

The DB2 driver supports returning parameter metadata for all types of SQL statements with the following DB2 databases:

  • DB2 v8.1 and v8.2 for Linux/UNIX/Windows
  • DB2 for z/OS
  • DB2 V5R2 and V5R3 for iSeries

For all other supported DB2 databases, 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:

  • The statement contains a predicate value expression that can be targeted against the source tables in the associated FROM clause. For example:
  • In this case, the value expression 'bar' can be targeted against the table 'foo' to determine the appropriate metadata for the parameter.
  • The statement contains a predicate value expression part that is a nested query. The nested query's metadata must describe a single column. For example:

The following Select statements show further examples for which parameter metadata can be returned:

ANSI SQL 92 entry-level predicates in a WHERE clause containing GROUP BY, HAVING, or ORDER BY statements are supported. For example:

Joins are supported. For example:

Fully qualified names and aliases are supported. For example:

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:

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').

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:

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:

  • CachedRowSets
  • FilteredRowSets
  • WebRowSets
  • JoinRowSets
  • JDBCRowSets

Seehttp://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.

How you return those values depends on whether you are using an Insert statement that contains parameters:

  • When using an Insert statement that does not contain any parameters, the DB2 driver supports the following form of the Statement.execute and Statement.executeUpdate methods to inform the driver to return the values of auto-generated keys:
    • Statement.execute (String sql, int autoGeneratedKeys)
    • Statement.executeUpdate (String sql, int autoGeneratedKeys)
  • When using an 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:
    • Connection.prepareStatement (String sql, int autoGeneratedKeys)

The application fetches the values of generated keys from the driver using the Statement.getGeneratedKeys method.

Due to a defect in DB2 servers, problems may occur when using a driver patch that is equal to, or greater than the patch levels above, if connecting to DB2 UDB on UNIX, Windows, or Linux where the server version is earlier than DB2 v8 FixPak 11. If you have connecton problems, you may need to include the following driver property in your configuration:

Use the following table to provide guidance on when to use ServerReleaseLevel:

Table 2-8 Usage Guidelines for ServerReleaseLevel=SQL08020

Server Version

Driver Version

Pre 3.4.72

3.4.72 or higher

Pre 3.5.14

3.5.14 or higher

Pre-DB2 v8 FP11

Unavailable/Not required

May need to be used

Unavailable/Not required

May need to be used

DB2 v8 FP11 or higher

Unavailable/Not required

Do not use

Unavailable/Not required

Do not use


For more information, see IBM's support and download site at DB2 UDB Version 8.1 FixPak 11 (also known as Version 8.2 FixPak 4).