Documentation updates

Revision marks indicate text that has been added or changed. A vertical bar ( | ) indicates information that was added or changed for Version 8.2 FixPak 4 (equivalent to Version 8.1 FixPak 11).

Administration: Implementation

Automatic client reroute configuration (DB2_MAX_CLIENT_CONNRETRIES |and DB2_CONNRETRIES_INTERVAL)

By default, the automatic client reroute feature retries the connection |to a database repeatedly for up to 10 minutes. It is, however, possible to |configure the exact retry behavior using one or both of the following two |registry variables:

|
• DB2_MAX_CLIENT_CONNRETRIES: The maximum number of connection retries attempted |by automatic client reroute.
|
• DB2_CONNRETRIES_INTERVAL: The sleep time between consecutive connection |retries, in number of seconds.

If DB2_MAX_CLIENT_CONNRETRIES is set, but DB2_CONNRETRIES_INTERVAL is |not, DB2_CONNRETRIES_INTERVAL defaults to 30.

If DB2_MAX_CLIENT_CONNRETRIES is not set, but DB2_CONNRETRIES_INTERVAL |is set, DB2_MAX_CLIENT_CONNRETRIES defaults to 10.

If neither DB2_MAX_CLIENT_CONNRETRIES nor DB2_CONNRETRIES_INTERVAL is set, |the automatic client reroute feature reverts to its default behavior described |previously.

Note:

Users of Type 4 connectivity with the DB2^(R) Universal JDBC Driver |should use the following two datasource properties to configure automatic |client rerouting:

|
• maxRetriesForClientReroute: Use this property to limit the number of |retries if the primary connection to the server fails. This property is only |used if the retryIntervalClientReroute property is also set.
|
• retryIntervalForClientReroute: Use this property to specify the amount |of time (in seconds) to sleep before retrying again. This property is only |used if the maxRetriesForClientReroute property is also set.

DB2TIMEOUT registry variable clarification

The DB2TIMEOUT registry variable is no longer supported. This setting was |used to control the timeout period for Windows^(R) 3.x and Macintosh clients |during long SQL queries. This feature was disabled by default.

Directories created during table space container creation

When creating table space containers, DB2 UDB creates any directory levels |that do not exist.

For example, if a container is specified as /project/user_data/container1, and the directory /project does not exist, then DB2 UDB |creates the directories /project and /project/user_data.

Starting with DB2 UDB V8.2, FixPak 4, any directories created by DB2 UDB |are created with PERMISSION 700. This means that only the owner has read, |write, and execute access.

When creating multiple instances, note the following scenario:

|
1. Using the same directory structure as above, suppose that directory levels /project/user_data do not exist.
|
2. user1 creates an instance, named user1 by default, then creates |a database, and then creates a table space with /project/user_data/container1 as one of its containers.
|
3. user2 creates an instance, named user2 by default, then creates |a database, and then attempts to create a table space with /project/user_data/container2 as one of its containers.

Because DB2 UDB created directory levels /project/user_data with |PERMISSION 700 from the first request, user2 does not have access to these |directory levels and cannot create container2 in those directories. | In this case, the CREATE TABLESPACE operation fails.

There are two methods to resolve this conflict:

|
1. Create the directory /project/user_data before creating the |table spaces and set the permission to whatever access is needed for both |user1 and user2 to create the table spaces. If all levels of a table space |directory exist, DB2 UDB does not modify the access.
|
2. After user1 creates /project/user_data/container1, set the |permission of /project/user_data to whatever access is needed |for user2 to create the table space.

Automatic storage

The format of the names for the containers has changed in such a way that the table space ID and the container ID have also changed. The new format is:

<storage path>/<instance>/NODE####
/T#######
/C#######.<EXT>

where:

• /T####### is the table space ID. For example, T0000003.
• /C####### is the container ID. For example, C0000012.

Defining a generated column on an existing table

Starting with DB2^(R) Universal Database Version 8.2.2 (equivalent to Version 8.1 FixPak 9), generated columns can be used in unique indexes.

Generated columns cannot be used in constraints, referential constraints, primary keys, and global temporary tables. A table created with LIKE and materialized views does not inherit generated column properties.

Aggregate registry variables

When you have set DB2WORKLOAD=SAP, the user table space SYSTOOLSPACE and the user temporary table space SYSTOOLSTEMPSPACE are not automatically created. These table spaces are used for tables created automatically by the following wizards, utilities, or functions:

• Automatic maintenance
• Design advisor
• Control Center database information panel
• SYSINSTALLOBJECTS stored procedure, if the table space input parameter is not specified
• GET_DBSIZE_INFO stored procedure

Without the SYSTOOLSPACE and SYSTOOLSTEMPSPACE table spaces, you cannot use these wizards, utilities, or functions.

To be able to use the wizards, utilities, or functions, do either of the following:

• Manually create SYSTOOLSPACE (on the catalog node only, if using DPF). For example:

     CREATE REGULAR TABLESPACE SYSTOOLSPACE 
        IN IBMCATGROUP 
        MANAGED BY SYSTEM 
        USING ('SYSTOOLSPACE')

• Call SYSINSTALLOBJECTS to create objects, specifying a valid table space, for each of the following tool names: "DB2AC", "POLICY" and "STMG_DBSIZE_INFO"

After completing at least one of these choices, create a user temporary table space (also on the catalog node only, if using DPF). For example:

   CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE 
      IN IBMCATGROUP 
      MANAGED BY SYSTEM 
      USING ('SYSTOOLSTMPSPACE')

Once the table space SYSTOOLSPACE and the temporary table space SYSTOOLSTEMPSPACE are created, you can use the wizards, utilities, or functions mentioned earlier.

Authentication considerations for remote clients

The authentication type DATA_ENCRYPT_CMP is designed to allow clients from a previous release that do not support data encryption to connect to a server using SERVER_ENCRYPT authentication instead of DATA_ENCRYPT. This authentication does not work when the following three statements are true:

• The client level is Version 7.2.
• The gateway level is Version 8 FixPak 7 or later.
• The server is Version 8 FixPak 7 or later.

In this case, the client cannot connect to the server. To allow the connection, you must either upgrade your client to Version 8, or have your gateway level at Version 8 FixPak 6 or earlier.

Direct I/O (DIO) and concurrent I/O (CIO) support

Direct I/O (DIO) improves memory performance because it bypasses caching at the file system level. This process reduces CPU overhead and makes more memory available to the database instance.

Concurrent I/O (CIO) includes the advantages of DIO and also relieves the serialization of write accesses.

DB2 Universal Database^(TM) (UDB) supports DIO and CIO on AIX^(R); and DIO on HP-UX, Solaris Operating Environment, Linux^(TM), and Windows^(R).

The keywords NO FILE SYSTEM CACHING and FILE SYSTEM CACHING are part of the CREATE and ALTER TABLESPACE SQL statements to allow you to specify whether DIO or CIO is to be used with each table space. When NO FILE SYSTEM CACHING is in effect, DB2^(R) UDB attempts to use concurrent I/O wherever possible. In cases, where CIO is not supported (for example, if JFS is used), DIO is used instead.

For more information, refer to the article "Improve database performance on file system containers in IBM^(R) DB2 UDB Stinger using Concurrent I/O on AIX" located at the following URL:

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408lee/

Distributor technology and automatic client rerouting

The following information is part of the Administration Guide: Implementation Appendix B "Using automatic client rerouting":

The DB2 Universal Database for Linux, UNIX^(R), and Windows automatic client reroute feature allows client applications to recover from a loss of communication with the server by automatically reestablishing the database connection from the client to the server, so that the application can continue to work with minimal interruption.

When a client to server connection fails, the client's requests for reconnection are distributed to a defined set of systems by a distributor or dispatcher, such as WebSphere^(R) EdgeServer

You may be using Distributor Technology in an environment similar to the following:

Client --> Distributor Technology --> (DB2 Connect^(TM) Server 1 or DB2 Connect Server 2) --> DB2 z/OS^(R)

where:

• The Distributor Technology component has a TCP/IP host name of DThostname
• The DB2 Connect Server 1 has a TCP/IP host name of GWYhostname1
• The DB2 Connect Server 2 has a TCP/IP host name of GWYhostname2
• The DB2 z/OS server has a TCP/IP host name of zOShostname

The client is catalogued using DThostname in order to utilize the distributor technology to access either of the DB2 Connect Servers. The intervening distributor technology makes the decision to use GWYhostname1 or GWYhostname2. Once the decision is made, the client has a direct socket connection to one of these two DB2 Connect gateways. Once the socket connectivity is established to the chosen DB2 Connect server, you have a typical client to DB2 Connect server to DB2 z/OS connectivity.

For example, assume the distributor chooses GWYhostname2. This produces the following environment:

Client --> DB2 Connect Server 2 --> DB2 z/OS

The distributor does not retry any of the connections if there is any communication failure. If you want to enable the Automatic Client Reroute feature for a database in such an environment, the alternate server for the associated database or databases in the DB2 Connect Server (DB2 Connect Server 1 or DB2 Connect Server 2) should be set up to be the distributor (DThostname). Then, if DB2 Connect Server 1 locks up for any reason, Automatic Client Reroute is triggered and client connection is retried with the distributor as both primary and alternate server. This option allows you to combine and maintain the distributor capabilities with the DB2 Automatic Client Reroute feature. Setting the alternate server to a host other than the distributor host name will still provide the clients with the Automatic Client Reroute feature. However, the clients will establish direct connections to the defined alternate server and bypass the distributor technology, which eliminates the distributor and the value that it brings.

Automatic Client Reroute will intercept the following sqlcodes:

• sqlcode -20157
• sqlcode -1768 (reason code = 7)

Automatic client reroute considerations for cataloging on a DB2 Connect server

Consider the following two items involving alternate server connectivity with DB2 Connect server:

• The first consideration involves using DB2 Connect server for providing access to a host or iSeries^(TM) database on behalf of both remote and local clients. In such situations, confusion can arise regarding alternate server connectivity information in a system database directory entry. To minimize this confusion, consider cataloging two entries in the system database directory to represent the same host or iSeries database. Catalog one entry for remote clients and catalog another for local clients.
• Secondly, the alternate server information that is returned from a target server is kept only in cache. If the DB2 process is terminated, the cache information, and therefore the alternate server information, is lost.

Local system account support (Windows)

Applications running under the context of the local system account (LSA) are supported on all Windows platforms, except Windows ME.

Two-part user ID support

The CONNECT statement and ATTACH command support two-part user IDs. The qualifier of the SAM-compatible user ID is the NetBIOS style name which has a maximum length of 15 characters. This feature is not supported on Windows ME.

Kerberos authentication details

Additional information for Kerberos support

Administration: Performance

Comparison of the DB2_FORCE_FCM_BP registry variable in 32-bit and |64-bit environments

When you enable the DB2_FORCE_FCM_BP registry variable there is one fewer |shared memory segments available for other uses, particularly for database |buffer pools. Enabling the DB2_FORCE_FCM_BP registry variable thus reduces |the maximum size of database buffer pools. Note that because of the large |number of available shared memory segments in a 64-bit environment, this reduction |in the number of shared memory segments should be an issue only in 32-bit |environments.

RUNSTATS recommended after table creation

When a table is first created, system catalog statistics are set to -1 |to indicate that the table has no statistics. Until statistics are gathered, |DB2 UDB uses default values for SQL statement compilation and optimization. | Updating the table or index statistics might fail if the new values are inconsistent |with the default values. Therefore, run the runstats command on a table or index before manually updating statistics for |either.

New reason code for SQL1169N

SQL error message SQL1169N has a new reason code 5 to indicate that a column |of an explain table is too small.

|
• Explanation for reason code 5: A column of an explain table is too small. |The db2diag.log will contain messages identifying the schema name, table name |and column name as well as the column size required to contain the data generated |by the Explain facility.
|
• User Response: Decide whether the required column size can be accommodated |in your table and database. If it can, increase the column size using the |ALTER TABLE statement.

Optimization strategies for MDC tables

The following text is an update to Administration |Guide: Performance, Chapter 6. Understanding the SQL compiler.

MDC roll out may be used even if a RID index is part of the optimization |plan regardless of the presence of a WHERE clause in the DELETE statement. |As a result, when listing the conditions that must be met to allow roll out |and the use of a more efficient way to delete rows, the condition that a "RID index was not chosen by the optimizer to find the rows to be deleted, |unless there is no WHERE clause in the DELETE statement" should be removed.

Further, you are able to tell if MDC roll out is in effect because db2expln output shows the phrase "Cell Delete". | Note that db2exfmt does not show this information.

The following text is an update to Appendix A. |DB2 Registry and Environment Variables:

The description of DB2_MDC_ROLLOUT should be changed such that the condition |that a "RID index was not chosen by the optimizer to find the rows to be |deleted, unless there is no WHERE clause in the DELETE statement" should |be removed from the list.

NEWLOGPATH, MIRRORPATH, and OVERFLOWLOGPATH configuration parameter |description clarification

If you update the newlogpath, mirrorpath, or overflowlogpath configuration parameter values in a DB2 UDB Enterprise Server Edition |environment, the node number will be appended to the path name regardless |of the number of nodes on the system. This applies to both single-partition |and multi-partition systems in a DB2 UDB Enterprise Server Edition environment.

DB2_COLLECT_TS_REC_INFO default value

The default value for DB2_COLLECT_TS_REC_INFO is ON. In DB2 UDB V 8.1 FixPak 7, the default value for the DB2_COLLECT_TS_REC_INFO |registry variable was changed to ON. The current |documentation incorrectly specifies the default for this variable as OFF.

The Governor utility

A governor instance consists of a front-end utility and one or more daemons. Each instance of the governor that you start is specific to an instance of the database manager. By default, when you start the governor a governor daemon starts on each partition of a partitioned database. However, you can specify that a daemon be started on a single partition that you want to monitor.

1. When the governor is active, its snapshot requests might affect database manager performance. To improve performance, increase the governor wake-up interval to reduce its CPU usage.
2. Governor daemons issue LOCAL snapshots to the local instance while running. Therefore, any rules that contain setlimit clauses are applied to output from LOCAL snapshot output rather than the aggregated result from GLOBAL snapshots.

Each governor daemon collects information about the applications that run against the database. The governor daemon then checks this information against the rules that you specify in the governor configuration file for this database.

Choosing a table reorganization method

When considering in-place table reorganization (instead of classic table reorganization), be aware that in-place table reorganization requires more log space.

Because in-place table reorganization logs its activities so that recovery is possible after an unexpected failure, it requires more log space than classic reorganization.

It is possible that in-place reorganization will require log space equal to several times the size of the reorganized table. The amount of required space depends on the number of rows that are moved and the number and size of the indexes on the table.

Recommendation: Choose in-place table reorganization for 24x7 operations with minimal maintenance windows.

An online table reorganization of a DMS table allows the starting of an online backup operation of a table space in which the table resides while the reorganization is happening. There may be lock waits of the reorganization operation during the truncate phase.

Refer to the REORG TABLE syntax descriptions for detailed information about executing these table reorganization methods.

Large page support for FCM memory (AIX 5L 64-bit)

On AIX^(R) 5L 64-bit, the DB2_LARGE_PAGE_MEM registry variable now supports the keyword FCM.

By default, on AIX^(R) 5L^(TM) 64-bit, FCM memory is in the DBMS memory set. However, when the registry variable DB2_FORCE_FCM_BP is enabled, FCM memory is in its own memory set. On AIX 5L^(TM) 64-bit, DB2_LARGE_PAGE_MEM supports the specification of the DBMS memory set. When FCM memory is in the DBMS memory set, and large page support is enabled for that memory set, FCM memory will be in large pages. When FCM memory is in its own memory set, the FCM keyword must be added to the value of the DB2_LARGE_PAGE_MEM registry variable to enable large pages for FCM memory.

DB2_RESOURCE_POLICY registry variable accepts a new element

Starting with DB2 Universal Database^(TM) (UDB) Version 8.2.2 (equivalent to Version 8.1 FixPak 9), the configuration file specified by the DB2_RESOURCE_POLICY registry variable accepts a SCHEDULING_POLICY element. The SCHEDULING_POLICY element can be used on some platforms to select:

• The operating system scheduling policy that is used by the DB2 UDB server
  Note:
  The capability to set an operating system scheduling policy extends to DB2 for AIX^(R). It was previously available only for DB2 UDB for Windows^(R) using the DB2NTPRICLASS registry variable.
• The operating system priorities that are used by individual DB2 server agents

The registry variables DB2PRIORITIES and DB2NTPRICLASS can be used separately to control the operating system scheduling policy and set DB2 agent priorities.

However, the specification of a SCHEDULING_POLICY element in the resource policy configuration file provides a single place to specify both the scheduling policy and the associated agent priorities.

<RESOURCE_POLICY>
   <SCHEDULING_POLICY>
      <POLICY_TYPE>SCHED_FIFO2</POLICY_TYPE>
      <PRIORITY_VALUE>60</PRIORITY_VALUE>

      <EDU_PRIORITY>
         <EDU_NAME>db2loggr</EDU_NAME>
         <PRIORITY_VALUE>56</PRIORITY_VALUE>
      </EDU_PRIORITY>

      <EDU_PRIORITY>
         <EDU_NAME>db2loggw</EDU_NAME>
         <PRIORITY_VALUE>56</PRIORITY_VALUE>
      </EDU_PRIORITY>
   </SCHEDULING_POLICY>
</RESOURCE_POLICY>

<RESOURCE_POLICY>
   <SCHEDULING_POLICY>
      <POLICY_TYPE>HIGH_PRIORITY_CLASS</POLICY_TYPE>
   </SCHEDULING_POLICY>
</RESOURCE_POLICY>

New system environment variables (Linux)

The DB2_MAPPED_BASE and DB2DBMSADDR system environment variables have been added at FixPak 8.

Use of these registry variables is only recommended for advanced users.

New communication registry variable

The DB2TCP_CLIENT_RCVTIMEOUT registry variable has been added in Version 8.2.

New performance variable

The DB2_LARGE_PAGE_MEM performance variable has been added in Version 8.2.

      cat /proc/meminfo

      HugePages_Total:   200
      HugePages_Free:    200
      Hugepagesize:    16384 KB

SQL compiler variables

The following update applies to the topic "SQL compiler variables" in Appendix A "DB2 registry and environment variables" of the Administration Guide: Performance:

When either or both of the DB2 compiler variables DB2_MINIMIZE_LISTPREFETCH and DB2_INLIST_TO_NLJN, are set to ON, they remain active even if REOPT(ONCE) is specified.

Configuration parameter updates

Following are the updates to the configuration parameter documentation:

authentication - Authentication type

util_impact_lim - Instance impact policy

sysadm_group, sysmaint_group, sysctrl_group, sysmon_group

estore_seg_sz - Extended storage memory segment size

hadr_timeout - HADR timeout value

locklist - Maximum storage for lock list

num_db_backups - Number of database backups

SQLDBCONF database configuration parameter file

After migrating to DB2 Universal Database (UDB) Version 8.2 from Version 8.1, DB2 UDB uses a new 16 KB database configuration parameter file named SQLDBCONF. (In Version 8.1, the database configuration parameter file was only 4 KB and named SQLDBCON).

Change to the DB2_HASH_JOIN default value

As of Version 8.1 the registry variable DB2_HASH_JOIN is set to ON by default.

The hash-join variable should be used, but it needs to be tuned to get the best performance.

Hash-join performance is best if you can avoid hash loops and overflow to disk. To tune hash-join performance, estimate the maximum amount of memory available for the sheapthres parameter , then tune the sortheap parameter. Increase its value until you avoid as many hash loops and disk overflows as possible, but do not reach the limit specified by the sheapthres parameter.

For more information, see the "Join methods" topic in the Administration Guide: Performance manual.

DB2NTNOCACHE registry variable is deprecated

Functionality previously achieved through DB2NTNOCACHE can be achieved at the table space level by specifying the NO FILE SYSTEM CACHING clause on the CREATE TABLESPACE or the ALTER TABLESPACE statement. Refer to SQL Reference for details on usage. The DB2NTNOCACHE registry variable will be removed in a future release.

Explain tables and organization of explain information

Explain tables might be common to more than one user. However, the explain tables can be defined for one user, and aliases can be defined for each additional user using the same name to point to the defined tables. Alternatively, the explain tables can be defined under the SYSTOOLS schema. The Explain facility will default to the SYSTOOLS schema if no other explain tables or aliases are found under the user's session ID for dynamic SQL, or the statement authorization ID for static SQL. Each user sharing the common explain tables must have insert permission on those tables. Read permission for the common explain tables should also be limited, typically to users who analyze the explain information.

Guidelines for capturing explain information

Explain data is captured if you request it when an SQL statement is compiled. Consider how you expect to use the captured information when you request explain data.

Additional return codes from db2CfgGet API, collate_info parameter

The collating information parameter can only be displayed using the db2CfgGet API. It cannot be displayed through the command line processor or the Control Center.

Configuration Type

Database

Parameter Type

Informational

This parameter provides 260 bytes of database collating information. The first 256 bytes specify the database collating sequence, where byte "n" contains the sort weight of the code point whose underlying decimal representation is "n" in the code page of the database.

The last 4 bytes contain internal information about the type of the collating sequence. The last 4 bytes of collate_info is an integer. The integer is sensitive to the endian order of the platform. The possible values are:

• 0 - The sequence contains non-unique weights
• 1 - The sequence contains all unique weights
• 2 - The sequence is the identity sequence, for which strings are compared byte for byte.
• 3 - The sequence is NLSCHAR, used for sorting characters in a TIS620-1 (code page 874) Thai database.
• 4 - The sequence is IDENTITY_16BIT, which implements the "CESU-8 Compatibility Encoding Scheme for UTF-16: 8-Bit" algorithm as specified in the Unicode Technical Report #26 available at the Unicode Technical Consortium Web site at http://www.unicode.org.
• X'8001' - The sequence is UCA400_NO, which implements the UCA (Unicode Collation Algorithm) based on the Unicode Standard version 4.00, with normalization implicitly set to ON.
• X'8002' - The sequence is UCA400_LTH, which implements the UCA (Unicode Collation Algorithm) based on the Unicode Standard version 4.00, and sorts all Thai characters as per the Royal Thai Dictionary order.
• X'8003' - The sequence is UCA400_LSK. which implements the UCA (Unicode Collation Algorithm) based on the Unicode Standard version 4.00, and sorts all Slovakian characters properly.

If you use this internal type information, you need to consider byte reversal when retrieving information for a database on a different platform.

You can specify the collating sequence at database creation time.

Automatic setting of default prefetch size and update defaults

Starting with DB2 Universal Database (UDB) Version 8.2, you can use AUTOMATIC prefetch size for a table space. DB2 UDB automatically updates the prefetch size when the number of containers changes for the table space.

The syntax of the DB2_PARALLEL_IO registry variable is expanded to recognize containers with different I/O parallelism characteristics. Through the expanded syntax, containers for different table spaces can have different I/O parallelism characteristics. The I/O parallelism characteristic of each table space is used when a prefetch size of AUTOMATIC is specified for the table space. If the DB2_PARALLEL_IO registry variable is enabled but the expanded syntax identifying specific I/O parallelism characteristics for table spaces is not used, a default level of parallelism is assumed. The default level is RAID 5 (6+1).

The prefetch size information used by the optimizer is refreshed only when an ALTER TABLESPACE statement that changes the prefetch size of a table space or changes the number of containers (using ADD/DROP/BEGIN NEW STRIPE SET/ADD TO NEW STRIPE SET) is issued. If the number of physical disks per container registry settings changes, an ALTER TABLESPACE <table space name> PREFETCHSIZE AUTOMATIC statement should be issued to refresh the optimizer information (unless an ALTER TABLESPACE statement that refreshes the optimizer information is already issued).

If a table space is redirected or restored to use a different number of containers, refresh the optimizer information by issuing an ALTER TABLESPACE <table space name> PREFETCHSIZE AUTOMATIC statement. If there are multiple stripe sets within a table space, the maximum number of containers among the stripe sets is used to calculated the prefetch size. If the calculated prefetch size exceeds the maximum size (32 767 pages), the largest multiple of the number of containers that is smaller than the maximum is used as the prefetch size.

In a DB2 UDB Enterprise Server Edition environment, if a table space uses an AUTOMATIC prefetch size, the prefetch size might be different on different database partitions. This situation can exist because different database partitions can have different numbers of containers used for calculating the prefetch size. To generate the query access plan, the optimizer uses the prefetch size from the first partition in a database partition group.

Administration: Planning

Range-clustered tables

A range-clustered table cannot be created in a database having more than one partition.

Catalog table space design

When creating a database, three table spaces are defined, including the SYSCATSPACE table space for the system catalog tables. The page size that becomes the default for all table spaces is set when the database is created. If a page size greater than 4096 or 4 K is chosen, the page size for the catalog tables is restricted to a row size that it would have if the catalog table space had a page size of 4 K. The default database page size is stored as an informational database configuration parameter called pagesize.

Supported territory codes and code pages

In Appendix B, "National language support (NLS)" in Administration Guide: Planning, the topic called "Supported territory codes and code pages" has tables for each territory. Two tables require updates:

China (PRC), territory identifier: CN

The code page for the Linux GBK row in the "China (PRC), territory identifier: CN" table should be changed from 1383 to 1386.

That is, the row should now read:

1386     D-4     GBK     86     zh_CN.GBK     Linux(R)

Japan, territory identifier: JP

The table for " Japan, territory identifier: JP" has been revised.

The following locale name should be removed:

954      D-1      eucJP   81       japanese      Solaris

Here is the revised table:

XA function supported by DB2 Universal Database

DB2 Universal Database (UDB) supports the XA91 specification defined in X/Open CAE Specification Distributed Transaction Processing: The XA Specification, with the following exceptions:

• Asynchronous services

  The XA specification allows the interface to use asynchronous services, so that the result of a request can be checked at a later time. The database manager requires that the requests be invoked in synchronous mode.

• Registration

  The XA interface allows two ways to register an RM: static registration and dynamic registration. DB2 UDB supports both dynamic and static registration. DB2 UDB provides two switches:

  • db2xa_switch for dynamic registration
  • db2xa_switch_static for static registration
• Association migration

  DB2 UDB does not support transaction migration between threads of control.

XA switch usage and location

As required by the XA interface, the database manager provides a db2xa_switch and a db2xa_switch_static external C variable of type xa_switch_t to return the XA switch structure to the TM. Other than the addresses of various XA functions, the following fields are returned:

Field

Value

name

The product name of the database manager. For example, DB2 UDB for AIX.

flags

For db2xa_switch TMREGISTER | TMNOMIGRATE is set

Explicitly states that DB2 UDB uses dynamic registration, and that the TM should not use association migration. Implicitly states that asynchronous operation is not supported.

For db2xa_switch_static TMNOMIGRATE is set

Explicitly states that DB2 UDB uses dynamic registration, and that the TM should not use association migration. Implicitly states that asynchronous operation is not supported.

version

Must be zero.

Using the DB2 Universal Database XA switch

The XA architecture requires that a Resource Manager (RM) provide a switch that gives the XA Transaction Manager (TM) access to the RM's xa_ routines. An RM switch uses a structure called xa_switch_t. The switch contains the RM's name, non-NULL pointers to the RM's XA entry points, a flag, and a version number.

Linux- and UNIX-based systems

The switch for DB2 Universal Database (UDB) can be obtained through either of the following two ways:

• Through one additional level of indirection. In a C program, this can be accomplished by defining the macro:

     #define db2xa_switch (*db2xa_switch)
     #define db2xa_switch_static (*db2xa_switch)

  prior to using db2xa_switch or db2xa_switch_static.
• By calling db2xacic or db2xacicst

  DB2 UDB provides these APIs, which return the address of the db2xa_switch or the db2xa_switch_static structure. This function is prototyped as:

     struct xa_switch_t * SQL_API_FN  db2xacic( )
     struct xa_switch_t * SQL_API_FN  db2xacicst( )

With either method, you must link your application with libdb2.

Windows NT

The pointer to the xa_switch structure, db2xa_switch, or db2xa_switch_static is exported as DLL data. This implies that a Windows NT^(R) application using this structure must reference it in one of three ways:

• Through one additional level of indirection. In a C program, this can be accomplished by defining the macro:

  #define db2xa_switch (*db2xa_switch)
  #define db2xa_switch_static (*db2xa_switch)

  prior to using db2xa_switch or db2xa_switch_static.
• If using the Microsoft^(R) Visual C++ compiler, db2xa_switch or db2xa_switch_static can be defined as:

  extern __declspec(dllimport) struct xa_switch_t db2xa_switch
  extern __declspec(dllimport) struct xa_switch_t db2xa_switch_static

• By calling db2xacic or db2xacicst

  DB2 UDB provides this API, which returns the address of the db2xa_switch or db2xa_switch_static structure. This function is prototyped as:

  struct xa_switch_t * SQL_API_FN  db2xacic( )
  struct xa_switch_t * SQL_API_FN  db2xacicst( )

With any of these methods, you must link your application with db2api.lib.

Example C Code

The following code illustrates the different ways in which the db2xa_switch or db2xa_switch_static can be accessed via a C program on any DB2 UDB platform. Be sure to link your application with the appropriate library.

   #include <stdio.h>
   #include <xa.h>

   struct xa_switch_t * SQL_API_FN  db2xacic( );

   #ifdef DECLSPEC_DEFN
   extern __declspec(dllimport) struct xa_switch_t db2xa_switch;
   #else
   #define db2xa_switch (*db2xa_switch)
   extern struct xa_switch_t db2xa_switch;
   #endif

main( )
   {
      struct xa_switch_t *foo;
      printf ( "%s \n", db2xa_switch.name );
      foo = db2xacic();
      printf ( "%s \n", foo->name );
      return ;
   }

Updated TPM and tp_mon_name values for xa_open string formats

The Internal settings column in the following table has been updated to reflect settings for the thread of control (TOC).

TOC is the entity to which all DB2 UDB XA connections are bound:

• When set to a value of T, all DB2 UDB XA connections formed under a particular OS thread are unique to that thread. Multiple threads cannot share DB2 UDB connections. Each OS thread has to form its own set of DB2 UDB XA connections.
• When set to a value of P, all DB2 UDB XA connections are unique to the OS Process and all XA connections can be shared between OS threads.

AXLIB=libEncServer (for Windows)
     =/usr/lpp/encina/lib/libEncServer
        (for Linux and UNIX systems)
HOLD_CURSOR=T
CHAIN_END=T
SUSPEND_CURSOR=F
TOC=T

AXLIB=libEncServer (for Windows)
     =/usr/lpp/encina/lib/libEncServer
        (for Linux and UNIX systems)
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F
TOC=T

AXLIB=mqmax 
(for Windows)
     =/usr/mqm/lib/libmqmax_r.a 
(for AIX threaded applications)
     =/usr/mqm/lib/libmqmax.a 
(for AIX non-threaded applications)
     =/opt/mqm/lib/libmqmax.so 
(for Solaris)
     =/opt/mqm/lib/libmqmax_r.sl 
(for HP threaded applications)
     =/opt/mqm/lib/libmqmax.sl 
(for HP non-threaded applications)
     =/opt/mqm/lib/libmqmax_r.so 
(for Linux threaded applications)
     =/opt/mqm/lib/libmqmax.so 
(for Linux non-threaded applications)
HOLD_CURSOR=F
CHAIN_END=F
SUSPEND_CURSOR=F
TOC=P

AXLIB=somtrx1i (for Windows)
     =libsomtrx1
        (for Linux and UNIX systems)
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F
TOC=T

AXLIB=ibmsfDB2
HOLD_CURSOR=F
CHAIN_END=T
SUSPEND_CURSOR=F
TOC=T

AXLIB=libtux
HOLD_CURSOR=F
CHAIN_END=F
SUSPEND_CURSOR=F
TOC=T

Activating conversion tables for code pages 923 and 924

The following table contains a list of all the code page conversion table files that are associated with code pages 923 and 924. Each file is of the form XXXXYYYY.cnv or ibmZZZZZ.ucs, where XXXXX is the source code page number and YYYY is the target code page number. The file ibmZZZZZ.ucs supports conversion between code page ZZZZZ and Unicode.

Conversion table files for euro-enabled code pages

The following tables list the conversion tables that have been enhanced to support the euro currency symbol. If you want to disable euro symbol support, download the conversion table file indicated in the column titled "Conversion table files".

API Reference

Log record structure clarification

This documentation update applies to Note #3 for Table 103. Add/Delete/Non-update Long Field Record Log Record Structure in |the section entitled Long Field Manager Log Records in the topic entitled DB2 UDB log records. | The text for Note #3 should read:

3. Long field data length in 512-byte sectors (actual data length is recorded |as the first 4 bytes of LF descriptor, which is logged in the subsequent insert/delete/update |log record as part of the formatted user data record ). The value of this |field is always positive. The long field manager never writes log records |for zero length long field data that is being inserted, deleted, or updated.

db2Backup API oBackupsize parameter

In DB2 DB2 V8, the oBackupsize parameter from the db2Backup API accounts |for full backups and not for delta or incremental backups. The oBackupsize |parameter represents the size of the backup image (in MB).

SYNCPOINT option support

The SYNCPOINT option for the sqlesetc, sqleqryc, and sqlaprep APIs is ignored as of Version 8; it is available only for backward compatibility.

New field for SQLEDBDESC structure

In the sqlecrea API, a new field has been added to support Direct I/O.

Field name

Unsigned char sqlfscaching

Description

File system caching

Values

0

File system caching is ON for the current table space

1

File system caching is OFF for the current table space

other

File system caching is ON for the current table space

Correction to new field in the SQLB-TBSPQRY-DATA structure

A new field, unsigned char fsCaching, has been added in the SQLB-TBSPQRY-DATA structure. This new field supports Direct I/O. Although the size of the reserved bit is documented as 32-bit, the correct size is 31-bit.

Application development: Building and Running Applications

Dynamic reconfiguration samples (AIX)

A new sample file, ibm_db2_sln_upart_smt, is available with DB2 Universal Database^(TM) (UDB) Version 8.2 FixPak 3 (equivalent to V8.1 Fixpak 10). The following table shows the name and description of the sample file.

The ibm_db2_sln_upart_smt sample script is in the sqllib/samples/DLPAR directory on DB2 UDB for AIX.

Linux supported development software

For installation details, refer to "Linux 2.6 kernel installation images" in the DB2 UDB Version 8.2.2 Release Notes (New in this release section).

The following tables describe the DB2 Linux architecture support as of the release of FixPak 9. Check the preceding validate Web site for updates to this support:

   http://www.ibm.com/db2/linux/validate

DB2 UDB for Linux 2.6 kernel-based Linux distributions for Intel^(R) x86 supports the following programming languages and compilers:

C

GNU/Linux gcc version 3.3

C++

GNU/Linux g++ version 3.3

COBOL

Micro Focus COBOL Server Express Version 2.2 with Service Pack 1

Java^(TM)

IBM^(R) Developer Kit and Runtime Environment for Linux, Java^(TM) 2 Technology Edition, Version 1.3.1 and 1.4.1 Service Release 1, 32-bit version

Note:

DB2 UDB installs the latest supported version of the developer kit if it is not already installed, unless the DB2 UDB installation is an update of a previous DB2 UDB Version 8 installation. If a previous DB2 UDB Version 8 installation is being updated, the supported developer kit must be manually installed from the CD-ROM.

Perl

Perl 5.004_04 or later, DBI 0.93 or later

PHP

PHP 4.3.4 or later

REXX

Object REXX Interpreter for Linux Version 2.1

A 32-bit instance on DB2 UDB for 2.6 kernel-based Linux distributions on x86-64 supports the following programming languages and compilers:

C

GNU/Linux gcc version 3.3

Note:

The "-m32" compiler option must be used to generate 32-bit applications or routines (stored procedures and user-defined functions).

C++

GNU/Linux g++ version 3.3

Notes:

1. These versions of the GNU/Linux g++ compiler do not accept integer parameters for some fstream functions. Consult the compiler documentation for more information.
2. The "-m32" compiler option must be used to generate 32-bit applications or routines (stored procedures and user-defined functions).

Java

IBM^(R) Developer Kit and Runtime Environment for Linux x86, Java 2 Technology Edition, Version 1.3.1 Service Release 4, 32-bit version, and Version 1.4.1 Service Release 1, 32-bit version.

Note:

DB2 UDB installs the latest supported version of the developer kit if it is not already installed, unless the DB2 UDB installation is an update of a previous DB2 UDB Version 8 installation. If a previous DB2 UDB Version 8 installation is being updated, the supported developer kit must be manually installed from the CD-ROM.

Perl

Perl 5.8

PHP

PHP 4.3.4 or later

A 64-bit instance on DB2 UDB for 2.6 kernel-based distributions for Linux on x86-64 supports the following programming languages and compilers:

C

GNU/Linux gcc version 3.3

C++

GNU/Linux g++ version 3.3

Note:

These versions of the GNU/Linux g++ compiler do not accept integer parameters for some fstream functions. Consult the compiler documentation for more information.

Java

DB2 UDB does not currently support any 64-bit Java Developer Kit for Linux on x86-64.

Perl

Perl 5.8

PHP

PHP 4.3.4 or later

Customizing precompile and bind options for SQL procedures

The precompile and bind options for SQL procedures can be customized by setting the instance-wide DB2 registry variable, DB2_SQLROUTINE_PREPOPTS with the command:

db2set DB2_SQLROUTINE_PREPOPTS=<options>

In addition to the options documented at Version 8.2, the REOPT option is allowed:

BLOCKING {UNAMBIG | ALL | NO}
DATETIME {DEF | USA | EUR | ISO | JIS | LOC}
DEGREE {1 | degree-of-parallelism | ANY}
DYNAMICRULES {BIND | RUN}
EXPLAIN {NO | YES | ALL}
EXPLSNAP {NO | YES | ALL}
FEDERATED {NO | YES}
INSERT {DEF | BUF}
ISOLATION {CS | RR | UR | RS | NC}
QUERYOPT optimization-level
REOPT {ALWAYS | NONE | ONCE}
VALIDATE {RUN | BIND}

C/C++ compile option required (Linux on POWER 64-bit)

The compiler option "-m64" is required when using gcc/g++ to build C/C++ applications and routines for a 64-bit instance on DB2 Universal Database for Linux on POWER.

The compiler option "-q64" is required when using xlc/xlC to build C/C++ applications and routines for a 64-bit instance on DB2 Universal Database for Linux on POWER.

Compile and link command for Micro Focus COBOL stored procedures (HP-UX)

The compile and link command shown in the DB2 Universal Database Version 8.2 documentation for building stored procedures using Micro Focus COBOL on HP-UX is incorrect. The compile command contained in the actual script sqllib/samples/cobol_mf/bldrtn is correct. The compile and link commands are now combined into one single command, using the -y option to specify that the desired output is a shared library.

Minimum supported version of Micro Focus COBOL (HP-UX)

The minimum supported version of the Micro Focus COBOL compiler and run time on HP-UX is Micro Focus Server Express 2.2 - Service Pack 1 plus Fix Pack Fixpack22.02_14 for HP-UX PA-RISC 11.x (32/64bit). This Fix Pack is available from the Micro Focus Support Line Web site at http://supportline.microfocus.com .

Setting environment variables for Micro Focus COBOL stored procedures (Windows)

In order to run Micro Focus COBOL external routines on Windows, it is necessary to ensure that the Micro Focus COBOL environment variables are permanently set as system variables.

Application development: Call Level Interface (CLI)

Trusted_Connection CLI/ODBC configuration keyword

|

Keyword description:

|

Allow a connection to be made with the current authenticated user. |

|

Syntax:

|

Trusted_Connection=Yes | |

Note:

|

This keyword has no effect if |set in the db2cli.ini file. It should instead |be provided in the connection string to SQLDriverConnect().

|

|

Default setting:

|

DB2 CLI uses the user ID and password information provided in the connection |string to SQLDriverConnect(), not the current authenticated user. |

|

Usage notes:

|

|

DB2 CLI applications that connect to a database typically connect using |the function SQLDriverConnect(). One of the input arguments for this function |is the DriverCompletion value, which determines when a window will |be opened. The following are the values of DriverCompletion :

|

|
• SQL_DRIVER_PROMPT: A dialog is always initiated.
|
• SQL_DRIVER_COMPLETE: A dialog is only initiated if there is insufficient |information in the connection string.
|
• SQL_DRIVER_COMPLETE_REQUIRED: A dialog is only initiated if there is |insufficient information in the connection string. Only mandatory information |is requested. The user is prompted for required information only.
|
• SQL_DRIVER_NOPROMPT: The user is not prompted for any information. A |connection is attempted with the information contained in the connection string. |If there is not enough information, SQL_ERROR is returned.

| |

Note:

|

More details on DriverCompletion can be found |in the documentation for SQLDriverConnect().

|

Some applications, |for example, those in a Kerberos environment, might require that a user be |able to connect to a DB2 UDB server without providing a user ID or password. |If the application uses the SQL_DRIVER_NO_PROMPT option on the SQLDriverConnect() call, |the connection is attempted without the user authentication. This keyword |is then not required.

|

In the case where a third party application is |involved and the prompt level used by the application is something other than |SQL_DRIVER_NO_PROMPT, DB2 CLI opens a window to request the missing information. |Setting Trusted_Connection to Yes, by providing it to the input connection |string for SQLDriverConnect() ("Trusted_Connection=Yes"), causes DB2 CLI to |ignore any user ID or password string (including blank strings) from the connection |string and ignore the prompt level of the connection function. DB2 CLI uses |the current authenticated user to attempt the connection to the database. |If the connection attempt fails, the user is prompted for the user ID and |password.

|

This keyword is used only in the connection string for SQLDriverConnect(); |setting it in the db2cli.ini file has no effect.

|

|

Diagnostic table update for SQLDescribeParam function (CLI)

The SQLDescribeParam() function returns the description of a parameter marker associated with a prepared SQL statement.

The diagnostics table has been updated with SQLSTATE HYC00.

Asynchronous execution of Call Level Interface

The DB2 Call Level Interface (DB2 CLI) can run a subset of functions asynchronously. The DB2 CLI driver returns control to the application after calling the function but before that function has finished executing. The function returns SQL_STILL_EXECUTING each time it is called until it is finished running, at which point it returns a different value (for example, SQL_SUCCESS).

Asynchronous execution is beneficial only on single-threaded operating systems. Applications that run on multithreaded operating systems should execute functions on separate threads. Asynchronous execution is possible for those functions that normally send a request to the server and then wait for a response. Rather than waiting, a function executing asynchronously returns control to the application. The application can then perform other tasks, or return control to the operating system, and use an interrupt to repeatedly poll the function until a return code other than SQL_STILL_EXECUTING is returned.

Support for asynchronous execution of CLI is included in DB2 Universal Database (UDB), starting with Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8). For documentation of this feature, refer to the DB2 UDB Version 7 Information Center at http://publib.boulder.ibm.com/infocenter/db2v7luw/index.jsp. All of the information in the Version 7 documentation applies to Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8) and later. The DB2 Version 8 Information Center does not contain any documentation of this feature.

SQL_ATTR_PING_DB connection attribute

SQLBindParameter function (CLI)

In the documentation for the SQLBindParameter function, the description in the Input parameter section is incorrect. The correct description is as follows:

Input parameter

A 0 ColumnSize means that DB2 CLI will use the maximum length for the SQL type provided as the size of the column or stored procedure parameter. DB2 CLI will perform any necessary conversions using this size.

SQLMoreResults function (CLI)

In the documentation for the SQLMoreResults function, the SQL_ATTR_ROW_ARRAY_SIZE statement attribute is referred to incorrectly. The correct statement attribute is SQL_ATTR_PARAMSET_SIZE. The Usage section should read as follows:

This function is used to return multiple results set in a sequential manner upon the execution of:

• A parameterized query with an array of input parameter values specified with the SQL_ATTR_PARAMSET_SIZE statement attribute and SQLBindParameter()

Additional environment attributes

In addition to being CLI connection attributes, the following attributes are also supported as CLI environment attributes:

• SQL_ATTR_INFO_ACCTSTR
• SQL_ATTR_INFO_APPLNAME
• SQL_ATTR_INFO_USERID
• SQL_ATTR_INFO_WRKSTNNAME

For information on these attributes, refer to the CLI connection attributes documentation in the DB2 Information Center or in the CLI Guide and Reference Volume 2 .

Dynamic scrollable cursors requirement

To perform updates and deletions on rows in a dynamic scrollable cursor's result set, the UPDATE or DELETE statement must include all the columns of at least one unique key in the base table. This can be the primary key or any other unique key.

RetCatalogAsCurrServer CLI/ODBC configuration keyword

Keyword description:

Catalog functions return the CURRENT SERVER value instead of the null value for the catalog columns.

db2cli.ini keyword syntax:

RetCatalogAsCurrServer= 0 | 1

Default setting:

If the target DBMS returns null for the catalog columns, the CURRENT SERVER value will not be substituted.

Usage notes:

If the catalog functions for the target DBMS return a null value for the catalog columns, setting RetCatalogAsCurrServer to 1 causes the DBMS to return the CURRENT SERVER value instead.

• 0 = Catalog functions return the null value for the catalog columns (default).
• 1 = Catalog functions return the CURRENT SERVER value, instead of the null value, for the catalog columns.

For example, assume the catalog function SQLTables() returns a result set where the values in the TABLE_CAT column are null values. Setting RetCatalogAsCurrServer to 1 causes the DBMS to return the CURRENT SERVER value in the TABLE_CAT column.

Note:

This keyword is supported by DB2 UDB for Linux, UNIX^(R) and Windows^(R) Version 8.2 FixPak 3 (equivalent to Version 8.1 FixPak 10) and later.

ReceiveTimeout CLI/ODBC configuration keyword

Keyword description:

Specify the time in seconds to wait for a reply from the server on an established connection before terminating the attempt and generating a communication timeout error.

db2cli.ini keyword syntax:

ReceiveTimeout = 0 | 1 | 2 | ... | 32767

Default setting:

The client waits indefinitely for a reply from the server on an established connection.

Equivalent connection attribute:

SQL_ATTR_RECEIVE_TIMEOUT

Usage notes:

The default value of 0 indicates that the client waits indefinitely for a reply. The receive timeout has no effect during connection establishment; it is only supported for TCP/IP and is ignored for any other protocol.

SQL_ATTR_RECEIVE_TIMEOUT connection attribute

SQL_ATTR_RECEIVE_TIMEOUT (DB2 CLI v8)

A 32-bit integer value that is the number of seconds a client waits for a reply from a server on an established connection before terminating the attempt and generating a communication timeout error. The default value of 0 indicates the client waits indefinitely for a reply. The receive timeout has no effect during connection establishment; it is only supported for TCP/IP, and is ignored for any other protocol. Supported values are integers from 0 to 32767.

Reopt CLI/ODBC configuration keyword

Keyword description:

Enable query optimization or reoptimization of SQL statements that have special registers or parameter markers.

db2cli.ini keyword syntax:

Reopt = 2 | 3 | 4

Default setting:

No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for special registers or parameter markers.

Equivalent statement and connection attribute:

SQL_ATTR_REOPT

Usage notes:

Optimization occurs by using the values available at query execution time for the special registers or parameter markers instead of the default estimates that are chosen by the compiler. The valid values of the keyword are:

• 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for the special registers or parameter markers. The default "NULLID " package set is used to execute dynamic SQL statements.
• 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution time, when the query is executed for the first time. The "NULLIDR1" package set, which is bound with the REOPT ONCE bind option, is used.
• 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at query execution time every time the query is executed. The "NULLIDRA" package set, which is bound with the REOPT ALWAYS bind option, is used.

The "NULLIDR1" and "NULLIDRA" are reserved package set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These package sets have to be explicitly created with the following commands:

db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA

If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.

SQL_ATTR_REOPT statement and connection attribute

SQL_ATTR_REOPT (DB2 CLI v8)

A 32-bit integer value that enables query optimization for SQL statements that contain special registers or parameter markers. Optimization occurs by using the values available at query execution time for special registers or parameter markers, instead of the default estimates that are chosen by the compiler. The valid values of the attribute are:

• 2 = SQL_REOPT_NONE. This is the default. No query optimization occurs at query execution time. The default estimates chosen by the compiler are used for the special registers or parameter markers. The default "NULLID " package set is used to execute dynamic SQL statements.
• 3 = SQL_REOPT_ONCE. Query optimization occurs once at query execution time, when the query is executed for the first time. The "NULLIDR1" package set, which is bound with the REOPT ONCE bind option, is used.
• 4 = SQL_REOPT_ALWAYS. Query optimization or reoptimization occurs at query execution time every time the query is executed. The "NULLIDRA" package set, which is bound with the REOPT ALWAYS bind option, is used.

The "NULLIDR1" and "NULLIDRA" are reserved package set names, and when used, REOPT ONCE and REOPT ALWAYS are implied respectively. These package sets have to be explicitly created with these commands:

db2 bind db2clipk.bnd collection NULLIDR1
db2 bind db2clipk.bnd collection NULLIDRA

SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.

CurrentPackageSet CLI/ODBC configuration keyword

Keyword description:

Issues the SET CURRENT PACKAGESET statement after every connection.

db2cli.ini keyword syntax:

CurrentPackageSet = schema name

Default setting:

The clause is not appended.

Equivalent connection attribute:

SQL_ATTR_CURRENT_PACKAGE_SET

Usage notes:

This option issues the SET CURRENT PACKAGESET SQL statement with the CurrentPackageSet value after every connection to a database. By default this clause is not appended.

The SET CURRENT PACKAGESET SQL statement sets the schema name (collection identifier) that is used to select the package to use for subsequent SQL statements.

CLI/ODBC applications issue dynamic SQL statements. Using this option you can control the privileges used to run these statements:

• Choose a schema to use when running SQL statements from CLI/ODBC applications.
• Ensure the objects in the schema have the desired privileges and then rebind accordingly.
• Set the CurrentPackageSet option to this schema.

The SQL statements from the CLI/ODBC applications will now run under the specified schema and use the privileges defined there.

The following package set names are reserved: "NULLID ", "NULLIDR1", "NULLIDRA".

If both the Reopt and CurrentPackageSet keywords are specified, CurrentPackageSet takes precedence.

SQL_ATTR_CURRENT_PACKAGE_SET connection attribute

SQL_ATTR_CURRENT_PACKAGE_SET (DB2 CLI v5)

A null-terminated character string that indicates the schema name (collection identifier) that is used to select the package for subsequent SQL statements. Setting this attribute causes the SET CURRENT PACKAGESET SQL statement to be issued. If this attribute is set before a connection, the SET CURRENT PACKAGESET SQL statement will be issued at connection time.

CLI/ODBC applications issue dynamic SQL statements. Using this connection attribute, you can control the privileges used to run these statements:

• Choose a schema to use when running SQL statements from CLI/ODBC applications.
• Ensure the objects in the schema have the desired privileges and then rebind accordingly. This typically means binding the CLI packages (sqllib/bnd/db2cli.lst) using the COLLECTION <collid> option. Refer to the BIND command for further details.
• Set the CURRENTPACKAGESET option to this schema.

The SQL statements from the CLI/ODBC applications will now run under the specified schema and use the privileges defined there.

Setting the CLI/ODBC configuration keyword CURRENTPACKAGESET is an alternative method of specifying the schema name.

The following package set names are reserved: "NULLID ", "NULLIDR1", "NULLIDRA".

SQL_ATTR_REOPT and SQL_ATTR_CURRENT_PACKAGE_SET are mutually exclusive, therefore, if one is set, the other is not allowed.

MapBigintCDefault CLI/ODBC configuration keyword

Keyword description:

Specify the default C type of BIGINT columns and parameter markers.

db2cli.ini keyword syntax:

MapBigintCDefault = 0 | 1 | 2

Default setting:

The default C type representation for BIGINT data is SQL_C_BIGINT.

Usage notes:

 

MapBigintCDefault controls the C type that is used when SQL_C_DEFAULT is specified for BIGINT columns and parameter markers. This keyword should be used primarily with Microsoft applications, such as Microsoft Access, which cannot handle 8-byte integers. Set MapBigintCDefault as follows:

• 0 - for the default SQL_C_BIGINT C type representation
• 1 - for an SQL_C_CHAR C type representation
• 2 - for an SQL_C_WCHAR C type representation

This keyword affects the behavior of CLI functions where SQL_C_DEFAULT might be specified as a C type, such as SQLBindParameter(), SQLBindCol(), and SQLGetData().

DescribeOutputLevel CLI/ODBC configuration keyword

Keyword description:

Set the level of output column describe information that is requested by the CLI driver during prepare or describe requests.

db2cli.ini keyword syntax:

DescribeOutputLevel = 0 | 1 | 2 | 3

Default setting:

Request the describe information listed in level 2 of Table 25.

Usage notes:

 

This keyword controls the amount of information the CLI driver requests on a prepare or describe request. By default, when the server receives a describe request, it returns the information contained in level 2 of Table 25 for the result set columns. An application, however, might not need all of this information or might need additional information.

Setting the DescribeOutputLevel keyword to a level that suits the needs of the client application might improve performance because the describe data transferred between the client and server is limited to the minimum amount that the application requires. If the DescribeOutputLevel setting is set too low, it might impact the functionality of the application (depending on the application's requirements). The CLI functions to retrieve the describe information might not fail in this case, but the information returned might be incomplete.

Supported settings for DescribeOutputLevel are:

• 0 - no describe information is returned to the client application
• 1 - describe information categorized in level 1 (see Table 25) is returned to the client application
• 2 - (default) describe information categorized in level 2 (see Table 25) is returned to the client application
• 3 - describe information categorized in level 3 (see Table 25) is returned to the client application

The following table lists the fields that form the describe information that the server returns when it receives a prepare or describe request. These fields are grouped into levels, and the DescribeOutputLevel CLI/ODBC configuration keyword controls which levels of describe information the CLI driver requests.

SQL_DESC_COUNT
SQL_COLUMN_COUNT
SQL_DESC_TYPE
SQL_DESC_CONCISE_TYPE
SQL_COLUMN_LENGTH
SQL_DESC_OCTET_LENGTH
SQL_DESC_LENGTH
SQL_DESC_PRECISION
SQL_COLUMN_PRECISION
SQL_DESC_SCALE
SQL_COLUMN_SCALE
SQL_DESC_DISPLAY_SIZE
SQL_DESC_NULLABLE
SQL_COLUMN_NULLABLE
SQL_DESC_UNSIGNED
SQL_DESC_SEARCHABLE
SQL_DESC_LITERAL_SUFFIX
SQL_DESC_LITERAL_PREFIX
SQL_DESC_CASE_SENSITIVE
SQL_DESC_FIXED_PREC_SCALE

all fields of level 1 and:
SQL_DESC_NAME
SQL_DESC_LABEL
SQL_COLUMN_NAME
SQL_DESC_UNNAMED
SQL_DESC_TYPE_NAME
SQL_DESC_DISTINCT_TYPE
SQL_DESC_REFERENCE_TYPE
SQL_DESC_STRUCTURED_TYPE
SQL_DESC_USER_TYPE
SQL_DESC_LOCAL_TYPE_NAME
SQL_DESC_USER_DEFINED_
         TYPE_CODE

all fields of levels 1
and 2 and:
SQL_DESC_BASE_COLUMN_NAME
SQL_DESC_UPDATABLE
SQL_DESC_AUTO_UNIQUE_VALUE
SQL_DESC_SCHEMA_NAME
SQL_DESC_CATALOG_NAME
SQL_DESC_TABLE_NAME
SQL_DESC_BASE_TABLE_NAME

Application development: Programming Client Applications

DB2 Universal JDBC Driver Type 4 connectivity to DB2 for VM/VSE is |not supported

The DB2 Universal JDBC Driver does not support type 4 connectivity to DB2 |for VM/VSE databases. The topics titled "Setting up the Windows Java environment" |and "Installing the DB2 Universal JDBC Driver" in the Application |Development Guide: Programming Client Applications, and the DB2 UDB Information |Center incorrectly state that the DB2 Universal JDBC Driver supports type |4 connectivity to DB2 for VM/VSE databases.

DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing

Java applications that use DB2 Universal JDBC Driver type 4 connectivity to access DB2 UDB for z/OS^(R) servers can take advantage of its connection concentrator and Sysplex workload balancing functions.

These functions are similar to the connection concentrator and Sysplex workload balancing functions of DB2 Connect.

The DB2 Universal JDBC Driver connection concentrator can reduce the resources that DB2 UDB for z/OS^(R) database servers require to support large numbers of client applications by letting many connection objects use the same physical connection, which reduces the total number of physical connections to the database server.

DB2 Universal JDBC Driver Sysplex workload balancing can improve availability of a data-sharing group because the driver gets frequent status information about the members of a data-sharing group. The driver uses this information to determine the data-sharing member to which the next transaction should be routed. With Sysplex workload balancing, the DB2 UDB for z/OS server and Workload Manager for z/OS (WLM) ensure that work is distributed efficiently among members of the data-sharing group and that work is transferred to another member of a data-sharing group if one member has a failure.

The DB2 Universal JDBC Driver uses transport objects and a global transport objects pool to support the connection concentrator and Sysplex workload balancing. There is one transport object for each physical connection to the database server. When you enable the connection concentrator and Sysplex workload balancing, you set the maximum number of physical connections to the database server at any point in time by setting the maximum number of transport objects.

At the driver level, you set limits on the number of transport objects using DB2 Universal JDBC Driver configuration properties.

At the connection level, you enable and disable the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing and set limits on the number of transport objects using DataSource properties.

You can monitor the global transport objects pool in either of the following ways:

• Using traces that you start using DB2 Universal JDBC Driver configuration properties
• Using an application programming interface

DB2 Universal JDBC Driver configuration properties for connection concentrator and Sysplex workload balancing

Each of the following configuration properties is used for connection concentrator and Sysplex workload balancing

db2.jcc.dumpPool

Specifies the types of statistics that are written for global transport pool events, in addition to the summary statistics that are written. The global transport pool is used for the connection concentrator and Sysplex workload balancing.

The data type of db2.jcc.dumpPool is integer (int.). The db2.jcc.dumpPoolStatisticsOnSchedule and db2.jcc.dumpPoolStatisticsOnScheduleFile configuration properties must also be set for writing statistics before any statistics are written.

You can specify one or more of the following types of statistics with the db2.jcc.dumpPool property:

• DUMP_REMOVE_OBJECT (hexadecimal: X'01', decimal: 1)
• DUMP_GET_OBJECT (hexadecimal: X'02', decimal: 2)
• DUMP_WAIT_OBJECT (hexadecimal: X'04', decimal: 4)
• DUMP_SET_AVAILABLE_OBJECT (hexadecimal: X'08', decimal: 8)
• DUMP_CREATE_OBJECT (hexadecimal: X'10', decimal: 16)
• DUMP_SYSPLEX_MSG (hexadecimal: X'20', decimal: 32)
• DUMP_POOL_ERROR (hexadecimal: X'80', decimal: 128)

To trace more than one type of event, add the values for the types of events that you want to trace. For example, suppose that you want to trace DUMP_GET_OBJECT and DUMP_CREATE_OBJECT events. The numeric equivalents of these values are 2 and 16, so you specify 18 for the db2.jcc.dumpPool value.

The default is 0, which means that only summary statistics for the global transport pool are written.

db2.jcc.dumpPoolStatisticsOnSchedule

Specifies how often, in seconds, global transport pool statistics are written to the file that is specified by the db2.jcc.dumpPoolStatisticsOnScheduleFile configuration property. The global transport pool is used for the connection concentrator and Sysplex workload balancing.

The default is -1, which means that global transport pool statistics are not written.

db2.jcc.dumpPoolStatisticsOnScheduleFile

Specifies the name of the file to which global transport pool statistics are written. The global transport pool is used for the connection concentrator and Sysplex workload balancing.

If the db2.jcc.dumpPoolStatisticsOnScheduleFile configuration property is not specified, global transport pool statistics are not written.

db2.jcc.maxTransportObjectIdleTime

Specifies the amount of time, in seconds, that an unused transport object stays in a global transport object pool before it can be deleted from the pool. Transport objects are used for the connection concentrator and Sysplex workload balancing.

The default value for the db2.jcc.maxTransportObjectIdleTime configuration property is 60. Setting db2.jcc.maxTransportObjectIdleTime to a value less than 0 causes unused transport objects to be deleted from the pool immediately. This action is not recommended because it can cause severe performance degradation.

db2.jcc.maxTransportObjectWaitTime

Specifies the maximum amount of time, in seconds, that an application waits for a transport object if the db2.jcc.maxTransportObjects value has been reached. Transport objects are used for the connection concentrator and Sysplex workload balancing. When an application waits for longer than the db2.jcc.maxTransportObjectWaitTime value, the global transport object pool throws an SQLException.

The default value for the db2.jcc.maxTransportObjectWaitTime configuration property is -1. Any negative value means that applications wait forever.

db2.jcc.maxTransportObjects

Specifies the upper limit for the number of transport objects in a global transport object pool for the connection concentrator and Sysplex workload balancing. When the number of transport objects in the pool reaches the db2.jcc.maxTransportObjects value, transport objects that have not been used for longer than the db2.jcc.maxTransportObjectIdleTime value are deleted from the pool.

The default value for the db2.jcc.maxTransportObjects configuration property is -1, which means that there is no limit to the number of transport objects in the global transport object pool.

db2.jcc.minTransportObjects

Specifies the lower limit for the number of transport objects in a global transport object pool for the connection concentrator and Sysplex workload balancing. When a JVM is created, there are no transport objects in the pool. Transport objects are added to the pool as they are needed. After the db2.jcc.minTransportObjects value is reached, the number of transport objects in the global transport object pool never goes below the db2.jcc.minTransportObjects value for the lifetime of that JVM.

The default value for the db2.jcc.minTransportObjects configuration property is 0. Any value less than or equal to 0 means that the global transport object pool can become empty.

DB2 Universal JDBC Driver DataSource properties for connection concentrator and Sysplex workload balancing

Each of the following DB2 Universal JDBC Driver DataSource properties is used for connection concentrator and Sysplex workload balancing

enableConnectionConcentrator

Indicates whether the connection concentrator function of the DB2 Universal JDBC Driver is enabled. The connection concentrator function is available only for connections to DB2 UDB for z/OS servers.

The data type of the enableConnectionConcentrator property is boolean. The default is false. However, if enableSysplexWLB is set to true, the default is true.

enableSysplexWLB

Indicates whether the Sysplex workload balancing function of the DB2 Universal JDBC Driver is enabled. The Sysplex workload balancing function is available only for connections to DB2 UDB for z/OS servers.

The data type of the enableSysplexWLB property is boolean. The default is false. However, if enableSysplexWLB is set to true, enableConnectionConcentrator is set to true by default.

maxTransportObjects

Specifies the maximum number of transport objects that can be used for all connections with the associated DataSource object. Transport objects are used for the connection concentrator and Sysplex workload balancing. The maxTransportObjects value is ignored if the enableConnectionConcentrator or enableSysplexWLB properties are not set to enable the use of the connection concentrator or Sysplex workload balancing.

The data type of this property is integer (int.).

If the maxTransportObjects value has not been reached and a transport object is not available in the global transport objects pool, the pool creates a new transport object. If the maxTransportObjects value has been reached, the application waits for the amount of time that is specified by the db2.jcc.maxTransportObjectWaitTime configuration property. After that amount of time has elapsed, if there is still no available transport object in the pool, the pool throws an SQLException.

The maxTransportObjects property does not override the db2.jcc.maxTransportObjects configuration property. The maxTransportObjects property has no effect on connections from other DataSource objects. If the maxTransportObjects value is larger than the db2.jcc.maxTransportObjects value, maxTransportObjects does not increase the db2.jcc.maxTransportObjects value.

The default value for the maxTransportObjects property is -1, which means that the number of transport objects for the DataSource is limited only by the db2.jcc.maxTransportObjects value for the driver..

Example of enabling the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing functions in WebSphere Application Server

The following procedure is an example of enabling the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing functions with WebSphere^(R) Application Server.

Methods for monitoring DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing functions

To monitor the DB2 Universal JDBC Driver connection concentrator and Sysplex workload balancing functions, you need to monitor the global transport objects pool. You can monitor the global transport objects pool in either of the following ways:

• Using traces that you start by setting DB2 Universal JDBC Driver configuration properties
• Using an application programming interface

Configuration properties for monitoring the global transport objects pool

The db2.jcc.dumpPool, db2.jcc.dumpPoolStatisticsOnSchedule, and db2.jcc.dumpPoolStatisticsOnScheduleFile configuration properties control tracing of the global transport objects pool.

For example, the following set of configuration property settings cause Sysplex error messages and dump pool error messages to be written every 60 seconds to a file named /home/WAS/logs/srv1/poolstats:

db2.jcc.dumpPool=DUMP_SYSPLEX_MSG|DUMP_POOL_ERROR
db2.jcc.dumpPoolStatisticsOnSchedule=60
db2.jcc.dumpPoolStatisticsOnScheduleFile=/home/WAS/logs/srv1/poolstats

An entry in the pool statistics file looks like this:

time Scheduled PoolStatistics npr:2575 nsr:2575 lwroc:439 
hwroc:1764 coc:372 aooc:362 rmoc:362 nbr:2872 tbt:857520 tpo:10

The meanings of the fields are:

npr

The total number of requests that the DB2 Universal JDBC Driver has made to the pool since the pool was created.

nsr

The number of successful requests that the DB2 Universal JDBC Driver has made to the pool since the pool was created. A successful request means that the pool returned an object.

lwroc

The number of objects that were reused but were not in the pool. This can happen if a connection object releases a transport object at a transaction boundary. If the connection object needs a transport object later, and the original transport object has not been used by any other connection object, the connection object can use that transport object.

hwroc

The number of objects that were reused from the pool.

coc

The number of objects that the DB2 Universal JDBC Driver created since the pool was created.

aooc

The number of objects that exceeded the idle time that was specified by the db2.jcc.maxTransportObjectIdleTime configuration property and were deleted from the pool.

rmoc

The number of objects that have been deleted from the pool since the pool was created.

nbr

The number of requests that the DB2 Universal JDBC Driver made to the pool that the pool blocked because the pool had reached its maximum capacity. A blocked request might be successful if an object is returned to the pool before the db2.jcc.maxTransportObjectWaitTime configuration value is exceeded and an exception is thrown.

tbt

The total time in milliseconds for requests that were blocked by the pool. This time can be much larger than the elapsed execution time of the application if the application uses multiple threads.

tpo

The number of objects that are currently in the pool.

Application programming interfaces for monitoring the global transport objects pool

You can write applications to gather statistics on the global transport objects pool. Those applications create objects in the DB2PoolMonitor class and invoke methods to retrieve information about the pool.

For example, the following code creates an object for monitoring the global transport objects pool:

import com.ibm.db2.jcc.DB2PoolMonitor;
DB2PoolMonitor transportObjectPoolMonitor =  
	DB2PoolMonitor.getPoolMonitor (DB2PoolMonitor.TRANSPORT_OBJECT);

After you create the DB2PoolMonitor object, you can use the following methods to monitor the global transport objects pool.

getMonitorVersion

Format:

public int getMonitorVersion()

Retrieves the version of the DB2PoolMonitor class that is shipped with the DB2 Universal JDBC Driver.

totalRequestsToPool

Format:

public int totalRequestsToPool()

Retrieves the total number of requests that the DB2 Universal JDBC Driver has made to the pool since the pool was created.

successfullRequestsFromPool

Format:

public int successfullRequestsFromPool()

Retrieves the number of successful requests that the DB2 Universal JDBC Driver has made to the pool since the pool was created. A successful request means that the pool returned an object.

numberOfRequestsBlocked

Format:

public int numberOfRequestsBlocked()

Retrieves the number of requests that the DB2 Universal JDBC Driver made to the pool that the pool blocked because the pool had reached its maximum capacity. A blocked request might be successful if an object is returned to the pool before the db2.jcc.maxTransportObjectWaitTime configuration value is exceeded and an exception is thrown.

totalTimeBlocked

Format:

public long totalTimeBlocked()

Retrieves the total time in milliseconds for requests that were blocked by the pool. This time can be much larger than the elapsed execution time of the application if the application uses multiple threads.

lightWeightReusedObjectCount

Format:

public int lightWeightReusedObjectCount()

Retrieves the number of objects that were reused but were not in the pool. This can happen if a connection object releases a transport object at a transaction boundary. If the connection object needs a transport object later, and the original transport object has not been used by any other connection object, the connection object can use that transport object.

heavyWeightReusedObjectCount

Format:

public int heavyWeightReusedObjectCount()

Retrieves the number of objects that were reused from the pool.

createdObjectCount

Format:

public int createdObjectCount()

Retrieves the number of objects that the DB2 Universal JDBC Driver created since the pool was created.

agedOutObjectCount

Format:

public int agedOutObjectCount()

Retrieves the number of objects that exceeded the idle time that was specified by the db2.jcc.maxTransportObjectIdleTime configuration property and were deleted from the pool.

removedObjectCount

Format:

public int removedObjectCount()

Retrieves the number of objects that have been deleted from the pool since the pool was created.

totalPoolObjects

Format:

public int totalPoolObjects()

The number of objects that are currently in the pool.

OleDbReportIsLongForLongTypes CLI/ODBC configuration keyword

The OleDbReportIsLongForLongTypes keyword is supported by the following database servers:

• DB2 UDB for z/OS Version 6 with PTF UQ93891
• DB2 UDB for z/OS Version 7 with PTF UQ93889
• DB2 UDB for z/OS Version 8 with PTF UQ93890
• DB2 UDB for Linux, UNIX and Windows Version 8.2 (equivalent to Version 8.1 FixPak 7) and later

Keyword description:

Makes OLE DB flag LONG data types with DBCOLUMNFLAGS_ISLONG.

db2cli.ini keyword syntax:

OleDbReportIsLongForLongTypes = 0 | 1

Equivalent statement attribute:

SQL_ATTR_REPORT_ISLONG_FOR_LONGTYPES_OLEDB

Default setting:

LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA) do not have the DBCOLUMNFLAGS_ISLONG flag set, which may cause the columns to be used in the WHERE clause.

Usage notes:

 

OLE DB's client cursor engine and OLE DB .NET Data Provider's CommandBuilder generate update and delete statements based on column information provided by the IBM DB2 OLE DB Provider. If the generated statement contains a LONG type in the WHERE clause, the statement will fail because LONG types cannot be used in a search with an equality operator. Setting the keyword OleDbReportIsLongForLongTypes to 1 will make the IBM DB2 OLE DB Provider report LONG types (LONG VARCHAR, LONG VARCHAR FOR BIT DATA, LONG VARGRAPHIC and LONG VARGRAPHIC FOR BIT DATA) with the DBCOLUMNFLAGS_ISLONG flag set. This will prevent the long columns from being used in the WHERE clause.

OleDbSQLColumnsSortByOrdinal CLI/ODBC configuration keyword

The OleDbSQLColumnsSortByOrdinal keyword is supported by the following database servers:

• DB2 UDB for z/OS Version 6 with PTF UQ93891
• DB2 UDB for z/OS Version 7 with PTF UQ93889
• DB2 UDB for z/OS Version 8 with PTF UQ93890
• DB2 UDB for Linux, UNIX and Windows Version 8.2 (equivalent to Version 8.1 FixPak 7) and later

Keyword description:

Makes OLE DB's IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS) return a row set sorted by the ORDINAL_POSITION column.

db2cli.ini keyword syntax:

OleDbSQLColumnsSortByOrdinal = 0 | 1

Equivalent statement attribute:

SQL_ATTR_SQLCOLUMNS_SORT_BY_ORDINAL_OLEDB

Default setting:

IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS) returns the row set sorted by the columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME.

Usage notes:

 

The Microsoft OLE DB specification requires that IDBSchemaRowset::GetRowset(DBSCHEMA_COLUMNS) returns the row set sorted by the columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. The IBM DB2 OLE DB Provider conforms to the specification. However, applications that use the Microsoft ODBC Bridge provider (MSDASQL) have been typically coded to get the row set sorted by ORDINAL_POSITION. Setting the OleDbSQLColumnsSortByOrdinal keyword to 1 will make the provider return a row set sorted by ORDINAL_POSITION.

DB2 Data Source property group for the IBM DB2 OLE DB Provider

The IBM DB2 OLE DB Provider has added a new property group: DB2 Data Source. The property set for DB2 Data Source is DBPROPSET_DB2DATASOURCE.

The GUID for the property set is {0x8a80412a,0x7d94,0x4fec,{0x87,0x3e,0x6c,0xd1,0xcd,0x42,0x0d,0xcd}}

DBPROPSET_DB2DATASOURCE has three properties:

• DB2PROP_REPORTISLONGFORLONGTYPES
• DB2PROP_RETURNCHARASWCHAR
• DB2PROP_SORTBYORDINAL

#define DB2PROP_REPORTISLONGFORLONGTYPES 4
Property group: DB2 Data Source 
Property set: DB2PROPSET_DATASOURCE
Type: VT_BOOL
Typical R/W: R/W
Description: Report IsLong for Long Types

#define DB2PROP_RETURNCHARASWCHAR 2
Property group: DB2 Data Source 
Property set: DB2PROPSET_DATASOURCE
Type: VT_BOOL
Typical R/W: R/W
Description: Return Char as WChar

#define DB2PROP_SORTBYORDINAL 3
Property group: DB2 Data Source 
Property set: DB2PROPSET_DATASOURCE
Type: VT_BOOL
Typical R/W: R/W
Description: Sort By Ordinal

Incorrect URL syntax in the DB2Binder syntax diagram

In the topic "Installing the DB2 Universal JDBC Driver", the DB2Binder syntax diagram incorrectly defines the URL syntax for the DB2 Universal JDBC Driver. The correct representation of the URL syntax for DB2Binder is shown in the following diagram:

>>-java--com.ibm.db2.jcc.DB2Binder------------------------------>
 
>---url jdbc:db2://server-+---------+-/database----------------->
                          '-:--port-'
 
>---user user-ID---password password--+---------------+--------->
                                      '--size integer-'
 
>--+-----------------------------+------------------------------>
   '--collection collection-name-'
 
>--+-------------------------------+--+-------+----------------><
   |              .-,------------. |  '--help-'
   |              V              | |
   '--tracelevel ---trace-option-+-'
 

Rerouting DB2 Universal JDBC driver clients

The automatic client reroute feature in DB2 Universal Database (UDB) for Linux, UNIX, and Windows allows client applications to recover from a loss of communication with the server so that they can continue to work with minimal interruption.

Whenever a server locks up, each client that is connected to that server receives a communication error, which terminates the connection and results in an application error. When availability is important, you should have a redundant setup or failover support. (Failover is the ability of a server to take over operations when another server fails.) In either case, the DB2 Universal JDBC driver client attempts to reestablish the connection to a new server, or to the original server, which might be running on a failover node. When the connection is reestablished, the application receives an SQLException that informs it of the transaction failure, but the application can continue with the next transaction.

package com.ibm.db2.jcc;
public class DB2ClientRerouteServerList 
  implements java.io.Serializable,
  javax.naming.Referenceable
{
  public String[] alternateServerName;
  public synchronized void 
    setAlternateServerName(String[] alternateServer);
  public String[] getAlternateServerName();
  public int[] alternatePortNumber;
  public synchronized void 
    setAlternatePortNumber(int[] alternatePortNumberList);
  public int[] getAlternatePortNumber();
  
  public synchronized void 
    setPrimaryServerName (String primaryServerName);
  public String getPrimaryServerName ();
  public synchronized void setPrimaryPortNumber (int primaryPortNumber)
  public int getPrimaryPortNumber (); 
}

Customizing the DB2 Universal JDBC driver configuration properties

The DB2 Universal JDBC driver configuration properties let you set property values that have driver-wide scope. Those settings apply across applications and DataSource instances. You can change the settings without having to change application source code or DataSource characteristics.

Each DB2 Universal JDBC driver configuration property setting is of the following form:

property=value

If the configuration property begins with db2.jcc.override, the configuration property is applicable to all connections and overrides any Connection or DataSource property with the same property name. If the configuration property begins with db2.jcc or db2.jcc.default, the configuration property value is a default. Connection or DataSource property settings override that value.

db2secFreeToken function removed

The db2secFreeToken function (Free memory held by token) is no longer part of the db2secGssapiServerAuthFunctions_1 user authentication plug-in API.

Deploy custom security plug-ins carefully

The integrity of your DB2 Universal Database (UDB) installation can be compromised if the deployment of security plug-ins are not adequately coded, reviewed, and tested. DB2 UDB takes precautions against many common types of failures, but it cannot guarantee complete integrity when user-written security plug-ins are deployed.

Security plug-ins

If you are using your own customized security plug-in, you can use a user ID of up to 255 characters on a connect statement issued through the CLP or a dynamic SQL statement.

Security plug-in APIs

For the db2secGetGroupsForUser, db2secValidatePassword, and db2secGetAuthIDs APIs, the input parameter, dbname, can be null and its corresponding length input parameter, dbnamelen, will be set to 0.

Security plug-in naming conventions (Linux and UNIX)

.so is now accepted as a file name extension for user-written security plug-in libraries on all Linux and UNIX platforms.

On AIX, security plug-in libraries can have an extension of .a or .so. If both versions of the plug-in library exist, the .a version is used.

For HP-UX on PA-RISC, security plug-in libraries can have an extension of .sl or .so. If both versions of the plug-in library exist, the .sl version is used.

On all other Linux and UNIX platforms, .so is the only supported file name extension for security plug-in libraries.

Restrictions on security plug-in libraries

On AIX, security plug-in libraries can have a file name extension of .a or .so. The mechanism used to load the plug-in library depends on which extension is used:

Plug-in libraries with a file name extension of .a

Plug-in libraries with file name extensions of .a are assumed to be archives containing shared object members. These members must be named shr.o (32-bit) or shr64.o (64-bit). A single archive can contain both the 32-bit and 64-bit members, allowing it to be deployed on both types of platforms.

For example, to build a 32-bit archive style plug-in library:

  xlc_r -qmkshrobj -o shr.o MyPlugin.c -bE:MyPlugin.exp
  ar rv MyPlugin.a shr.o

Plug-in libraries with a file name extension of .so

Plug-in libraries with file name extensions of .so are assumed to be dynamically loadable shared objects. Such an object is either 32-bit or 64-bit, depending on the compiler and linker options used when it was built. For example, to build a 32-bit plug-in library:

  xlc_r -qmkshrobj -o MyPlugin.so MyPlugin.c -bE:MyPlugin.exp

On all platforms other than AIX, security plug-in libraries are always assumed to be dynamically loadable shared objects.

GSS-API plug-in support for DB2 Universal JDBC Driver

With the release of DB2 UDB Version 8.2 for Linux, UNIX, Windows, you can |create your own authentication mechanisms in the form of plug-ins (loadable |libraries). The DB2 UDB engine loads and accesses these plug-ins to perform |user authentication. In order to support customer applications written in |Java, DB2 Universal JDBC Driver provides security plug-in support in DB2 UDB |V8.2, FixPak 4.

For Java applications using the DB2 Universal JDBC Driver to perform plug-in |authentication, users need to implement their own plug-in by extending the |abstract class com.ibm.db2.jcc.DB2JCCPlugin and setting the following properties:

|
• User ID and password: |these are the user credentials to be used for authentication
|
• Plug-in name: this specifies the name of the plug-in |module on the server side
|
• Security mechanism: this must be set to plug-in |security
|
• Plug-in: this specifies the plug-in module on |the client application side

Note the following example:

   java.util.Properties properties = new java.util.Properties();
|   properties.put("user", "db2admin");
|   properties.put("password", "admindb2");
|   properties.put("pluginName", "gssapi_simple");
|   properties.put("securityMechanism",
|   new String(""+com.ibm.db2.jcc.DB2BaseDataSource.PLUGIN_SECURITY+""));
|   properties.put("plugin", new JCCSimpleGSSPlugin());
|   Connection con = java.sql.DriverManager.getConnection(url, properties);

GSS-API security plug-ins do not support multiple-flow authentication

GSS-API authentication is limited to flowing one token from the client to the server and one token from the server to the client. These tokens are obtained from gss_init_sec_context() on the client and from gss_accept_sec_context() on the server. GSS-API plug-ins attempting additional flows will generate a security plug-in unexpected error, causing the connection to fail.

GSS-API security plug-ins do not support message encryption and signing

Message encryption and signing is not available in GSS-API security plug-ins.

Implicit ending of transactions in stand-alone applications

All application terminations (normal and abnormal) implicitly roll back outstanding units of work, regardless of operating system.

Distributed transaction support

In the What's new documentation for DB2 Universal Database (UDB) Version 8.2, the Distributed transaction support information for the DB2 Universal JDBC driver improvements section has incorrect information. The last sentence of this section is incorrect. The correct information is:

As of Version 8.2, DB2 UDB provides support for distributed transaction processing that conforms to the XA specification. This support implements the Java 2 Platform Enterprise Edition (J2EE) Java Transaction Service (JTS) and Java Transaction API (JTA) specifications.

Application development: Programming Server Applications

Multiple result set common language runtime (CLR) procedures

The maximum number of result sets that can be returned by a common language runtime (CLR) procedure is limited. The limit is determined by the maximum number of DB2DataReader objects that the DB2 .NET data provider can simultaneously support having open within a connection. Concurrent active data reader support enables multiple DB2DataReader objects to be opened within a connection. Therefore multiple result sets can be returned from a CLR procedure.

Common language runtime (CLR) routine execution control modes (EXECUTION CONTROL clause)

As a database administrator or application developer, you might want to protect the assemblies associated with your DB2 Universal Database (UDB) external routines from unwelcome tampering by restricting the actions of routines at runtime. DB2 .NET CLR routines support the specification of an execution control mode that identifies what types of actions a routine is allowed to perform at runtime. At runtime, DB2 UDB can detect if the routine attempts to perform actions beyond the scope of its specified execution control mode, which can be helpful when determining whether an assembly has been compromised.

To set the execution control mode of a CLR routine, specify the optional EXECUTION CONTROL clause in the CREATE statement for the routine. Valid modes are:

• SAFE
• FILEREAD
• FILEWRITE
• NETWORK
• UNSAFE

To modify the execution control mode in an existing CLR routine, execute the ALTER PROCEDURE or ALTER FUNCTION statement.

If the EXECUTION CONTROL clause is not specified for a CLR routine, by default the CLR routine runs using the most restrictive execution control mode, SAFE. Routines that are created with this execution control mode can only access resources that are controlled by the database manager. Less restrictive execution control modes allow a routine to access files on the local file system (FILEREAD or FILEWRITE) or on the network. The execution control mode UNSAFE specifies that no restrictions are to be placed on the behavior of the routine. Routines defined with UNSAFE execution control mode can execute binary code.

These control modes represent a hierarchy of allowable actions, and a higher-level mode includes the actions that are allowed below it in the hierarchy. For example, execution control mode NETWORK allows a routine to access files on the network, files on the local file system, and resources that are controlled by the database manager. Use the most restrictive execution control mode possible and avoid using the UNSAFE mode.

If DB2 UDB detects at runtime that a CLR routine is attempting an action outside of the scope of its execution control mode, DB2 UDB returns an error (SQLSTATE 38501).

The EXECUTION CONTROL clause can only be specified for LANGUAGE CLR routines. The scope of applicability of the EXECUTION CONTROL clause is limited to the .NET CLR routine itself, and does not extend to any other routines that it might call.

Maximum decimal precision and scale in common language runtime (CLR) routines

The DECIMAL data type in DB2 Universal Database (UDB) is represented with a precision of 31 digits and a scale of 28 digits. The .NET CLR System.Decimal data type is limited to a precision of 29 digits and a scale of 28 digits. Therefore, DB2 UDB external CLR routines must not assign a value greater than (2^96)-1, the highest value that can be represented using a 29 digit precision and a 28 digit scale, to a System.Decimal data type variable. DB2 UDB raises a runtime error (SQLSTATE 22003, SQLCODE -413) if such an assignment occurs.

When a routine CREATE statement is executed, if a DECIMAL data type parameter is defined with a scale greater than 28, DB2 UDB raises an error (SQLSTATE 42611, SQLCODE -604).

Command Reference

db2licm - License Management Tool Command parameter clarification

This documentation update applies to the -p parameter |of the db2licm command. The keyword MEASURED is no longer valid and is ignored by DB2.

RESTORE DATABASE command TSM example

The example for TSM (example 6) is missing single quotes around the TSM |options and should state:

|
• Specifying a delimited string:

db2 restore db sample use TSM options "'-fromnode bar -fromowner dmcinnis'"

db2demigdbd usage examples

The following examples of using db2demigdbd are |shown organized by operating system.

To demigrate system database directory files on UNIX and Linux:

 db2demigdbd $HOME/sqllib 1 

To demigrate the local system database directory files on UNIX and Linux: |

 db2demigdbd  ~/user/NODE0000  1 

To demigrate the system and all local database directory files in the instance |on UNIX and Linux:

 db2demigdbd  $HOME/sqllib  2 

To demigrate system database directory files on Windows:

 db2demigdbd  d:\sqllib\db2  1 

where db2 is the current instance.

To demigrate the local system database directory files on Windows: |

 db2demigdbd  d:\db2\NODE0000  1 

To demigrate the system and all local database directory files in the instance |on Windows:

 db2demigdbd  d:\sqllib\db2 2 

Usage Notes:

Once you have run the db2updv8 command to |update the system catalogs to the current Version 8 level, falling back to |V 8.1 is not supported.

Additional information:

For an example of how to run the command in a DPF (Database Partitioning |Feature) environment, see the technote located at http://www.ibm.com/support/docview.wss?rs=71&uid=swg21217949

db2ckbkp command usage note correction

The '-p' option displays the number of pages of each object type. However, |the output for the db2ckbkp command with the '-p' |option does not show the number of pages for all different object types if |the backup was done for DMS table spaces data. In this case, it only displays |the total of all pages as SQLUDMSTABLESPACEDATA. |The value of zero is displayed for SQLUDMSLOBDATA and SQLUDMSLONGDATA for DMS table spaces.

Here is a sample output section of db2ckbkp -p with |the backup done for DMS table spaces data:

      SQLUDMSTABLESPACEDATA (0c):  87 
|      SQLUDMSLOBDATA (0e):          0 
|      SQLUDMSLONGDATA (0f):         0                              

Naming conventions for database objects and for IDs

The following conventions that apply when naming database manager objects, such as |databases and tables.

|
• Character strings that represent names of database manager objects can contain any |of the following characters: a-z, A-Z, 0-9, @, #, and $.
|
• Unless otherwise noted, you can type names in lowercase letters; however, |the database manager processes them as if they were uppercase. |

  The exception to this |convention is character strings that represent names under the Systems Network |Architecture (SNA). Many values are case sensitive, such as logical unit names |(partner_lu and local_lu). The name must be entered exactly as it appears |in the SNA definitions that correspond to those terms.

|
• A database name or database alias is a unique character string containing |from one to eight letters, numbers, or keyboard characters from the set described |in the preceding information. |

  Databases are cataloged in the system and |in local database directories by their aliases in one field, and by their |original name in another. For most functions, the database manager uses the name entered |in the alias field of the database directories. The exceptions are CHANGE |DATABASE COMMENT and CREATE DATABASE, where a directory path must be specified.

|
• The name or the alias name of a table or a view is an SQL identifier that |is a unique character string 1 to 128 characters in length. Column names can |be 1 to 30 characters in length. |

  A fully qualified table name consists |of the schema.tablename. The schema is the unique |user ID under which the table was created. The schema name for a declared |temporary table must be SESSION.

|
• Local aliases for remote nodes that are to be cataloged in the node directory |cannot exceed eight characters in length.
|
• The first character in the string must be an alphabetic character, @, #, |or $; it cannot be a number or the letter sequences SYS, DBM, or IBM.

The following conventions apply when naming user IDs and authentication |IDs.

|
• Character strings that represent names of database manager objects can contain any |of the following: a-z, A-Z, 0-9, @, #, and $.
|
• User IDs and groups may also contain any of the following additional |characters when supported by the security plug-in: _, !, %, (, ), {, }, -, ., ^.
|
• User IDs and groups containing any of the following characters | must be delimited with quotations when entered through the command line processor: !, %, (, ), {, }, -, ., ^,
|
• The first character in the string must be an alphabetic character, @, #, |or $; it cannot be a number or the letter sequences SYS, DBM, or IBM.
|
• Authentication IDs cannot exceed 30 characters on Windows 32-bit operating systems and 8 characters |on all other operating systems.
|
• Group IDs cannot exceed 30 characters in length.

REORGCHK command

The index statistics formulas used within the REORGCHK command have been revised. The new formulas and their explanations are:

• Formula F5:

  100 * (KEYS * (ISIZE + LEAF_REC_OVERHEAD) + (CARD - KEYS) 
              * DUPKEYSIZE ) 
      / ((NLEAF - NUM EMPTY LEAFS - 1) * 
         (INDEXPAGESIZE - 96) > MIN(50, (100 - PCTFREE))

  where LEAF_REC_OVERHEAD = 9 and DUPKEYSIZE = 5.

  A reorganization is recommended if there is more than 50 percent free space in the index, or more than PCTFREE% free space in the index if PCTFREE is greater than 50. This formula is checked only if the value of NLEAF - NUM EMPTY LEAFS - 1 is greater than 0. (One is subtracted from the value of NLEAF because the last leaf page allocated is usually not filled.)

• Formula F6:

  (100 - PCTFREE) * 
       [ Floor((100 - min(10, PCTFREE)) / 100 * (INDEXPAGESIZE - 96) 
               / (ISIZE + NONLEAF_REC_OVERHEAD)) ** (NLEVELS - 2)] 
       * (INDEXPAGESIZE - 96) / 
         (KEYS * (ISIZE + LEAF_REC_OVERHEAD) 
               + (CARD - KEYS) * DUPKEYSIZE) < 100

  where NONLEAF_REC_OVERHEAD = 12.

  To determine if recreating the index would result in a tree having fewer levels. This formula checks the ratio between the amount of space in an index tree that has one less level than the current tree, and the amount of space needed. If a tree with one less level could be created and still leave PCTFREE available, then a reorganization is recommended. The actual number of index entries should be more than 90% (or 100 - PCTFREE) of the number of entries an NLEVELS - 1 index tree can handle (only checked if NLEVELS > 1).

REORG INDEXES/TABLE command

Reorganizes an index or a table.

The REORG INDEXES ALL FOR TABLE table-name option reorganizes all indexes that are defined on a table by rebuilding the index data into unfragmented, physically contiguous pages. If you specify the CLEANUP ONLY option of the index option, cleanup is performed without rebuilding the indexes. If you attempt to use this command against indexes on declared temporary tables, the SQLSTATE 42995 error will be returned.

The REORG TABLE table-name option reorganizes a table by reconstructing the rows to eliminate fragmented data, and by compacting information. The reorganization of the table is done by one of two methods:

Classic

Allows only limited read-only access to the data during the first phases of the reorganization.

Inplace

Allows access to the data throughout the reorganization but without being able to ensure perfectly ordered data. Inplace table reorganization is allowed only on tables with type-2 indexes and without extended indexes.

When you choose either type of reorganization, the indexes for the table are rebuilt after the table is reorganized. However, with the inplace method, the index will be imperfectly reorganized and may require that you reorganize the indexes later to reduce index fragmentation and to reclaim index object space.

   db2 reorg table employee index empid allow no access indexscan 
      longlobdata

BACKUP DATABASE command

Migrating Databases

db2inidb - Initialize a mirrored database command

Do not issue the db2 connect to database command prior to issuing the db2inidb database as mirror command.

Attempting to connect to a split mirror database prior to initializing it erases the log files needed for roll forward recovery.

The connect sets your database back to the state it was in when you suspended the database. If the database is marked as consistent at the time of the suspend, DB2 Universal Database concludes there is no need for crash recovery and empties the logs for future use. If this situation occurs, attempting to rollforward causes a SQL4970 error.

Usage note for the db2iupdt command

Starting with Version 8.2, when you update a DB2 Universal Database instance with the db2iupdt command, you must first stop any DB2 processes running against that instance.

New parameter for the db2sqljcustomize command

The db2sqljcustomize command has a new parameter.

New parameter for the sqlj command

The sqlj command has a new parameter.

Monitor and troubleshoot command (db2pd) updates

The Monitor and troubleshoot DB2 command (db2pd) retrieves information from the DB2 UDB memory sets. The db2pd system command has been enhanced in the following ways:

New -hadr parameter

Introduced at Version 8.2 (equivalent to Version 8.1 FixPak 7), the -hadr parameter reports High Availability Disaster Recovery information. Descriptions of each reported element can be found in the High availability disaster recovery section of the System Monitor Guide and Reference

New -utilities parameter

Introduced at Version 8.2 (equivalent to Version 8.1 FixPak 7), the -utilities parameter reports Utility information. Descriptions of each reported element can be found in the Utilities section of the System Monitor Guide and Reference.

New -activestatements parameter

Introduced at Version 8.2.2 (equivalent to Version 8.1 FixPak 9), the -activestatements parameter returns information about active statements. The following information is returned:

AppHandl

The application handle using the active statement, including the node and the index.

UOW-ID

For the application associated with AppHandl, the unit of work identifier in which the statement became active.

StmtID

The statement identifier within the unit of work.

AnchID

The identifier for the package cache dynamic SQL hash anchor.

StmtUID

The identifier for the package cache dynamic SQL statement unique within the hash anchor.

EffISO

The effective isolation level of the statement.

EffLockTOut

The effective lock timeout value for the statement.

EffDegree

The effective degree of parallelism of the statement.

StartTime

The time the statement started.

LastRefTime

The last time the statement was referenced by the application.

New wait option for the -locks parameter

Starting with Version 8.2.2 (equivalent to Version 8.1 FixPak 9), you can specify the wait option with the -locks parameter to return only locks with a waiting status and locks that are being waited on.

New fields returned by the -applications parameter

Starting with Version 8.2.2 (equivalent to Version 8.1 FixPak 9), the -applications parameter returns four new fields:

C-AnchID

This new field is the identifier for the package cache dynamic SQL hash anchor of the current statement. The value for this field is 0 if no current statement exists. The current hash anchor identifier is populated only when the application is executing a request related to the statement, such as a FETCH request on a cursor. Once the request processing is complete, the values are set to 0. The value is also set to 0 for static SQL statements.

C-StmtUID

This new field is the package cache dynamic SQL statement unique identifier within the hash anchor of the current statement. The value for this field is 0 if no current statement exists. The current statement unique identifier is populated only when the application is in the process of executing a request related to the statement. Once the request execution is complete, the values are set to 0. The value is also set to 0 for static SQL statements.

L-AnchID

This new field is the package cache dynamic SQL hash anchor identifier of the application's last executed statement. The value for this field is 0 if no last executed statement exists, such as before any dynamic SQL statement is executed. The value is also set to 0 for static SQL statements. The last anchor hash identifier is populated after each request execution is complete and survives until the completion of the next associated request .

L-StmtUID

This new field is the package cache dynamic SQL statement unique identifier within the hash anchor of the application's last executed statement. The value for this field is 0 if no last executed statement exists or if the last statement executed was static SQL. The last statement identifier is populated after the current request completes and survives until the completion of the next request.

Update to the SET CLIENT command

The SET^(TM) CLIENT command specifies connection settings for the back end process.

The command parameter SYNCPOINT for this command is ignored as of Version 8. SYNCPOINT continues to be included for backward compatibility.

Update to the PRECOMPILE command

The PRECOMPILE command processes an application program source file containing embedded SQL statements. A modified source file is produced, containing host language calls for the SQL and, by default, a package is created in the database.

The command parameter SYNCPOINT for this command is ignored as of Version 8. SYNCPOINT is continued to be included for backward compatibility.

Update to the UPDATE HISTORY FILE command

Updates the location, device type, or comment in a history file entry.

The command parameter STATUS specifies a new status for an entry.

Previous documentation incorrectly states that the STATUS command parameter can have a value of "I" to mark the entry as inactive. Valid values are:

A

Marks the entry as active.

E

Marks the entry as expired.

Updates to the EXPORT and IMPORT commands

The complete "Required connection " subsection for the EXPORT and IMPORT commands is as follows:

Update to the LOAD command

The complete information for the INDEXING MODE parameter's AUTOSELECT value is as follows:

File type modifiers for the load utility

The SET INTEGRITY command in the description for the "generatedoverride" modifier has been updated.

The description for the "usedefaults" modifier has also been updated.

The updates are as follows:

SET INTEGRITY FOR < table-name > GENERATED COLUMN 
  IMMEDIATE UNCHECKED

SET INTEGRITY FOR < table-name > IMMEDIATE CHECKED.

File type modifiers for the import utility

The description for the "usedefaults" and "codepage=x" modifiers have been updated as follows:

ATTACH command

The USER parameter of the ATTACH command specifies the authentication identifier. When attaching to a DB2 Universal Database instance on a Windows operating system, the user name can be specified in a format compatible with Microsoft Windows NT Security Account Manager (SAM). The qualifier must be a NetBIOS style name, which has a maximum length of 15 characters. For example, domainname\username.

RECOVER DATABASE command

In the Examples section of the RECOVER DATABASE Command for Version 8.2 documentation, timestamps are incorrectly formatted as yyyy:mm:dd:hh:mm:ss.

The correct format is yyyy-mm-dd-hh.mm.ss

UPDATE HISTORY FILE command

>>-UPDATE HISTORY--+-FOR--object-part-+--WITH------------------->
                   '-EID--eid---------'
 
>--+-LOCATION--new-location--DEVICE TYPE--new-device-type-+----><
   +-COMMENT--new-comment---------------------------------+
   '-STATUS--new-status-----------------------------------'
 

db2updv8 - Update database to version 8 current level command

This command updates the system catalogs in a database to support the current |level in the following ways:

|
• Introduces new routines shipped with the current database manager
|
• Catalogs the ODBC/CLI/JDBC schema procedures as trusted procedures to |improve performance and scalability
|
• Fixes two typographical errors in the SYSPROC.SNAPSHOT_QUIESCERS table |function's return parameters: |
  |
  • OUIESCER_TBS_ID corrected to QUIESCER_TBS_ID
  |
  • OUIESCER_STATE corrected to QUIESCER_STATE
|
• Fixes LOB columns' column descriptors such that "logged" bit is |consistent with the catalog tables
|
• Creates SYSIBM.SYSREVTYPEMAPPINGS view
|
• Updates the view definitions for SYSSTAT.COLUMNS and SYSSTAT.TABLES
|
• Updates the SYSCOLDIST.DISTCOUNT column to be updateable
|
• Updates the SYSINDEXES.TBSPACEID column such that column flags are not |set
|
• Fixes parameter lengths of 17 table functions. Refer to the Column expansion for snapshot UDFs topic in the Documentation updates | SQL Administrative Routines section of the Version 8.2 Release Notes for a complete listing |of functions
|
• Enables the following built-in routines: |
  |
  • HEALTH_DBM_INFO
  |
  • HEALTH_DBM_HI
  |
  • HEALTH_DBM_HI_HIS
  |
  • HEALTH_DB_INFO
  |
  • HEALTH_DB_HI
  |
  • HEALTH_DB_HI_HIS
  |
  • HEALTH_TBS_INFO
  |
  • HEALTH_TBS_HI
  |
  • HEALTH_TBS_HI_HIS
  |
  • HEALTH_CONT_INFO
  |
  • HEALTH_CONT_HI
  |
  • HEALTH_CONT_HI_HIS
  |
  • GET_WRAP_CFG_C
  |
  • LIST_FILES_C
  |
  • ODBC_DISCOVERY
  |
  • HEALTH_HI_REC
  |
  • EXEC_DB2_SCRIPT
  |
  • HEALTH_HI_REC
  |
  • EXEC_DB2_SCRIPT
  |
  • ADMIN_LIST_HIST
  |
  • GET_DBM_CONFIG
|
• Alters 33 SYSPROC snapshot UDFs to THREADSAFE: |
  |
  • SNAPSHOT_DBM
  |
  • SNAPSHOT_FCM
  |
  • SNAPSHOT_FCMNODE
  |
  • SNAPSHOT_SWITCHES
  |
  • SNAPSHOT_APPL_INFO
  |
  • SNAPSHOT_APPL
  |
  • SNAPSHOT_STATEMENT
  |
  • SNAPSHOT_LOCKWAIT
  |
  • SNAPSHOT_AGENT
  |
  • SNAPSHOT_SUBSECT
  |
  • SNAPSHOT_DATABASE
  |
  • SNAPSHOT_BP
  |
  • SNAPSHOT_LOCK
  |
  • SNAPSHOT_TABLE
  |
  • SNAPSHOT_DYN_SQL
  |
  • SNAPSHOT_TBS
  |
  • SNAPSHOT_TBS_CFG
  |
  • SNAPSHOT_QUIESCERS
  |
  • SNAPSHOT_CONTAINER
  |
  • SNAPSHOT_RANGES
  |
  • SNAPSHOT_TBREORG
  |
  • HEALTH_DBM_INFO
  |
  • HEALTH_DBM_HI
  |
  • HEALTH_DBM_HI_HIS
  |
  • HEALTH_DB_INFO
  |
  • HEALTH_DB_HI
  |
  • HEALTH_DB_HI_HIS
  |
  • HEALTH_TBS_INFO
  |
  • HEALTH_TBS_HI
  |
  • HEALTH_TBS_HI_HIS
  |
  • HEALTH_CONT_INFO
  |
  • HEALTH_CONT_HI
  |
  • HEALTH_CONT_HI_HIS
|
• Changes the parameter lengths of the following functions: |
  |
  • SQLCACHE_SNAPSHOT
  |
  • SNAPSHOT_APPL_INFO
  |
  • SNAPSHOT_STATEMENT
  |
  • SNAPSHOT_LOCKWAIT
  |
  • SNAPSHOT_DATABASE
  |
  • SNAPSHOT_BP
  |
  • SNAPSHOT_LOCK
  |
  • SNAPSHOT_TABLE
  |
  • SNAPSHOT_TBREORG
  |
  • SNAPSHOT_SUBSECT
  |
  • SNAPSHOT_DYN_SQL
  |
  • HEALTH_DBM_INFO
  |
  • HEALTH_DBM_HI
  |
  • HEALTH_DBM_HI_HIS
  |
  • HEALTH_DB_INFO
  |
  • HEALTH_DB_HI
  |
  • HEALTH_DB_HI_HIS
  |
  • DB_PARTITIONS
  |
  • GROUPS_FOR_USER
  |
  • DB_PARTITION
|
• Changes the following functions to use schema db2schema instead of the obsolete db2schem and |to enable them to modify SQL data: |
  |
  • SQLTABLES
  |
  • SQLTABLEPRIVILEGES
  |
  • SQLSTATISTICS
  |
  • SQLSPECIALCOLUMNS
  |
  • SQLPROCEDURES
  |
  • SQLPROCEDURECOLS
  |
  • SQLPRIMARYKEYS
  |
  • SQLFOREIGNKEYS
  |
  • SQLCOLUMNS
  |
  • SQLCOLPRIVILEGES
|
• Changes the following functions to use schema db2schema instead of the obsolete db2schem : |
  |
  • SQLUDTS
  |
  • SQLGETTYPEINFO

|>>-db2updv8-- -d--database-name--------------------------------->
| 
|>--+----------------------------+--+-----+---------------------><
|   '- -u--userid-- -p--password-'  '- -h-'
| 

db2updv8 -d sample

Formatting trap files (Windows)

A new tool, db2xprt.exe, is available to let you format trap files (*.TRP). This tool formats DB2 Universal Database's binary trap files into a human readable ASCII file. Trap files are located in the instance directory (DB2INSTPROF) by default or in the diagnostic data directory path if the DIAGPATH database manager configuration parameter is set.

>>-db2xprt--+----------+--+----+--+----+--infile--+---------+--><
            +-/p--path-+  '-/m-'  '-/n-'          '-outfile-'
            '-/v-------'
 

Data Movement

Bind files used by the export, import and load utilities

A new bind file called db2uImpInsUpdate.bnd has been added to the import utility, with a default isolation level of Read Stability (RS). This bind file is used by the Import Utility only during INSERT_UPDATE. The import utility's INSERT, REPLACE and CREATE options still use the db2uimpm.bnd file.

The db2uImpInsUpdate.bnd bind file cannot be bound with the INSERT BUF option. Attempting to perform IMPORT INSERT_UPDATE while db2uImpInsUpdate.bnd is bound with INSERT BUF causes the import utility to fail, resulting in the following error:

SQL3525: The "INSERT_UPDATE" option is incompatible with the "INSERT BUF BIND ON DB2UIMPINSUPDATE.BND" option.

Using import with buffered inserts

Version 8.2 of the Data Movement Utilities Guide and Reference states:

The buffered inserts feature cannot be used in conjunction with import operations in which the INSERT_UPDATE parameter is specified. A new bind file (db2uimpm2.bnd) is introduced to enforce this restriction.

Due to the introduction of a new bind file, the statement should be:

The buffered inserts feature cannot be used in conjunction with import operations in which the INSERT_UPDATE parameter is specified. A new bind file (db2uImpInsUpdate.bnd) is introduced to enforce this restriction.

Index information lost when the import utility is used

You can use the import utility to recreate a table that was saved through the export utility.

The Data Movement topic "Using import to recreate an exported table" outlines attributes of the original table are not retained. In addition to the attributes which have already been documented, the following attributes are not retained:

• Index information:
  • Include columns (if any)
  • Index name, if the index is a primary-key index
  • Descending order, if the index is a primary-key index (Ascending is the default)

Data Recovery and High Availability

Backup overview

Note the following restrictions:

• The starting of an online backup operation of a DMS table space is allowed when a table within the same table space is being reorganized online. There may be lock waits of the reorganization operation during the truncate phase.
• The starting of an online backup operation of an SMS table space is not allowed when a table within the same table space is being reorganized online. Both operations require an exclusive lock.

Using backup

The following restrictions apply to the backup utility:

• A table space backup operation and a table space restore operation cannot be run at the same time, even if different table spaces are involved.
• If you want to be able to do rollforward recovery in a partitioned database environment, you must regularly back up the database on the list of nodes, and you must have at least one backup image of the rest of the nodes in the system (even those that do not contain user data for that database). Two situations require the backed-up image of a database partition at a database partition server that does not contain user data for the database:
  • You added a database partition server to the database system after taking the last backup, and you need to do forward recovery on this database partition server.
  • Point-in-time recovery is used, which requires that all database partitions in the system are in rollforward pending state.
  • Online backup operations for DMS table spaces are incompatible with the following operations:
    • load
    • Reorganization (offline only); (online reorganization of a table in the table space being backed up can be used, although there may be lock waits during the truncate phase of the reorganization operation).
    • drop table space
    • table truncation
    • index creation
    • not logged initially (used with the CREATE TABLE and ALTER TABLE statements)

High availability disaster recovery overview

When performing the START HADR, STOP HADR, or TAKEOVER HADR commands, the corresponding error codes might be generated: SQL01767N, SQL01769N, or SQL01770N with a reason code of 98. The reason code indicates that there is no installed license for HADR on the server where the command was run. To correct the problem, install a valid HADR license using db2licm or install a version of the server that contains a valid HADR license as part of its distribution.

Cross-platform backup and restore support

DB2 Universal Database (UDB) supports cross-platform backup and restore operations.

You can restore databases created on a DB2 UDB Version 8 32-bit Windows platform to a DB2 UDB Version 8 64-bit Windows platform, or the reverse.

You can restore databases created on a DB2 UDB Version 8 32-bit Linux x86 platform to a DB2 UDB Version 8 64-bit Linux x86-64 or IA64 platform, or the reverse.

You can restore databases created on DB2 UDB Version 8 AIX, HP-UX, Linux PPC, Linux zSeries, or the Solaris Operating Environment platforms, in 32-bit or 64-bit, to DB2 UDB Version 8 AIX, HP-UX, Linux PPC, Linux zSeries, or Solaris Operating Environment platforms (32-bit or 64-bit).

Backing up to tape (Linux)

The maximum block size limit for 3480 and 3490 tape devices on Linux is 61 440 bytes

Tivoli Storage Manager

When calling the BACKUP DATABASE or RESTORE DATABASE commands, you can specify that you want to use the Tivoli^(R) Storage Manager (TSM) product to manage database or table space backup or restore operation. The minimum required level of TSM client API is Version 4.2.0, except on the following:

• 64-bit Solaris systems which require TSM client API Version 4.2.1.
• 64-bit Windows NT operating systems which require TSM client API Version 5.1.
• 32-bit Linux for iSeries and pSeries^(R) which requires at minimum TSM client API Version 5.1.5
• 64-bit Linux for iSeries and pSeries which requires at minimum TSM client API Version 5.3
• 64-bit Linux on AMD Opteron systems which require a minimum TSM client API Version 5.2.0.
• Linux for zSeries 64-bit which requires a minimum TSM client API Version 5.2.2.

Value restrictions for the HADR local host and local service parameters

When specifying values for the high availability disaster recovery (HADR) local host and local service parameters (HADR_LOCAL_SVC and HADR_REMOTE_SVC) while preparing an update database configuration command , the values must be ports that are not in use for any other service. If the parameters are being configured using the Linux or UNIX command line, the values should be also set in the /etc/services file.

Additional system requirements for high availability disaster recovery

If you create a table space on the primary database and log replay fails on the standby database because the containers are not available, the primary database does not receive an error message stating that the log replay failed.

To check for log replay errors, you must monitor the db2diag.log and the administration log on the standby database when you are creating new table spaces.

If a takeover operation occurs, the new table space that you created is not available on the new primary database. To recover from this situation, restore the table space on the new primary database from a backup image.

In the following example, table space MY_TABLESPACE is restored on database MY_DATABASE before it is used as the new primary database:

1. db2 connect to my_database
2. db2 list tablespaces show detail
   Note:
   Run the db2 list tablespaces show detail command to show the status of all table spaces and to obtain the table space ID number required for Step 5.
3. db2 stop hadr on database my_database
4. db2 "restore database my_database tablespace (my_tablespace) online redirect"
5. db2 "set tablespace containers for my_tablespace_ID_# ignore rollforward container operations using (path '/my_new_container_path/')"
6. db2 "restore database my_database continue"
7. db2 rollforward database my_database to end of logs and stop tablespace "(my_tablespace)"
8. db2 start hadr on database my_database as primary

Non-replicated operations for high availability disaster recovery

Version 8.2 documentation states:

BLOBs and CLOBs are not replicated; however, the space for them will be allocated on the standby database.

The statement should read as follows:

Non-logged BLOBs and CLOBs are not replicated; however, the space for them will be allocated on the standby database.

HADR does not support raw logs

High availability disaster recovery (HADR) does not support the use of raw I/O (direct disk access) for database log files. If HADR is started with the START HADR command, or if the database is restarted with HADR configured, and raw logs are detected, the associated command will fail with SQL1768N reason code "9".

Fault monitor and health monitor comparison

The health monitor and the fault monitor are tools that work on a single |database instance. The health monitor uses health indicators to evaluate the health of specific aspects of database manager performance |or database performance. A health indicator measures the health of some aspect |of a specific class of database objects, such as a table space. Health indicators |can be evaluated against specific criteria to determine the health of that |class of database object. In addition, health indicators can generate alerts |to notify you when an indicator exceeds a threshold or indicates a database |object is in a non-normal state

By comparison, the fault monitor is solely responsible for keeping the |instance it is monitoring up and running. If the DB2 UDB instance it is monitoring |terminates unexpectedly, the fault monitor restarts the instance. The fault |monitor is not available on Windows.

Turning off fault monitoring

To turn off fault monitoring for the database instance DB2INST1, type the |following command from a DB2 UDB command window:

   db2fm -i db2inst1 -f no

To confirm that fault monitor is no longer running for DB2INST1, type the |following command on UNIX systems:

   ps -ef|grep -i fm

On Linux systems, type the following command:

   ps auxw|grep -i fm

An entry that shows db2fmd and DB2INST1 indicates that the fault monitor |is still running on that instance. To turn off the fault monitor, type the |following command as the instance owner:

   db2fm -i db2inst1 -D

Data Warehouse Center

Setting up the warehouse client environment on AIX and Solaris operating |systems

On AIX and Solaris operating systems, you must set your locale correctly |prior to running the warehouse client.

To set your locale, type the following command from a command line: export LC_ALL=<locale>

Note the following example: export LC_ALL=Ja_JP where Ja_JP is your local locale.

If this environment variable is not set, then GUI applications might not |run correctly and can result in Java errors.

Updating the database manager configuration before installing warehouse transformers

In the DB2 Warehouse Manager Standard Edition Installation Guide, Version 8.2, the topic "Updating the database manager configuration before installing warehouse transformers" incorrectly instructs you to update the SDK path parameter. You must update the JDK path parameter.

Use the DB2 Command Line Processor to update the database manager configuration for the target DB2 instance before you install warehouse transformers.

VW_NETRC environment variable added to the Data Warehouse Center (UNIX)

Starting with Version 8.2 FixPak 10 (equivalent to Version 8.1 FixPak 3) of the Data Warehouse Center, you can set the VW_NETRC environment variable on UNIX operating systems, except for on Linux. Setting the VW_NETRC environment variable to off allows you to manually maintain the .netrc file. Refer to your operating system's documentation for information about the correct format for the .netrc file.

Running concurrent FTP user-defined programs might corrupt the .netrc file. If you want to run concurrent FTP user-defined program steps, you can set the VW_NETRC environment variable to off (VW_NETRC=OFF). Add this environment variable to your agent daemon profile for remote agents and to the IWH.environment file for your default or local agent.

DB2 Data Warehouse Center requires Unicode format database

After installing DB2 Universal Database Version 8.1 FixPak 7 or later, you need to run the Warehouse Control Database Management tool to create a new warehouse control database that is in Unicode format.

Defining DB2 warehouse sources and targets

The following update affects two Data Warehouse Center topics:

• Defining DB2 warehouse sources
• Defining a warehouse target

By default, when you define a warehouse source or a warehouse target, the number of tables that are returned is 250. However, you can use the new environment variable VWS_MAX_TABLELIST to set the number of tables that are returned. The maximum number of tables that can be returned is 40 000. This number might be less depending on the size of the table names in the list. It is recommended that you specify a number much smaller than 40 000.

Updates to the Business Intelligence tutorial

Setting the purge limit for warehouse log files

The log file holds records until a designated count limit is reached. The default count limit is 1000 records. Typically, each job that you run creates 12 to 15 log records. Set the purge limit to a number that meets your needs by updating the Purge log when total records equal field on the Server tab of the Warehouse Properties page.

Data Warehouse Center support for CURSOR load

The DB2 Universal Database Load step now allows a view or a table to be used as the source to the step, resulting in a LOAD FROM CURSOR.

In order to map columns in the wizard for CURSOR load, the Map columns based on column positions found in the input file radio button must be selected.

Unicode warehouse control database migration and limitations

Starting with Version 8.2 of the Data Warehouse Center, the warehouse control database must be a Unicode database. If you have a Unicode warehouse control database from a version of the Data Warehouse Center that is before Version 8.2, you still must create a new Unicode control database by using the Warehouse Control Database Management tool.

When you migrate a warehouse control database from a version of the Data Warehouse Center that is before Version 8.2, the Data Warehouse Center Control Database Management tool runs the db2move command to move the data to a new Unicode control database. During this process, windows appear that show the progress of the db2move command. This migration path only occurs once.

The Data Warehouse Center does not support Unicode on Sybase servers.

Change in date format for Modified column

In the details view of the main Data Warehouse Center window, the format of the date in the Modified column has been updated. The date in the Modified column is displayed in the format for your locale and includes the time. This change in the date format ensures that sorting objects on the Modified column functions properly. This update applies to most lists of Data Warehouse Center objects that are shown in the Navigator and Details views, such as:

• Subjects
• Processes
• Warehouse schemas
• Warehouse agent sites
• Programs
• Steps
• Data resources
• Users
• User groups

Defining statistical transformers in the Data Warehouse Center

To perform a statistical transformation of your data, define the statistical transformer that you want to use.

Preparing to install warehouse agents

When installing DB2 Warehouse Manager, the warehouse server, warehouse |client, and all of the warehouse agents must be at the same version and fix |pack level.

Prerequisite for the iSeries warehouse agent

To use an iSeries warehouse agent for DB2 Warehouse Manager on V5R2 and V5R3 systems, the following PTF is required:

PTF SI13558

This database PTF enables the CLI on iSeries to handle Unicode data.

DB2 .NET Data Provider

Concurrent active data reader support

The DB2 .NET Data Provider now supports the use of concurrent active data readers. This means that you can concurrently access data from multiple DB2DataReader instances that use the same DB2Connection instance. Each DB2DataReader instance must be associated with its own DB2Command instance. In order to use the associated DB2Command instance for any other purpose, you must explicitly call the DB2DataReader.Close method.

DB2Connection.ConnectionString property

There is an additional keyword for the DB2Connection.ConnectionString property:

CurrentSchema

The schema to be used after a successful connection. Upon a successful connection, a SET CURRENT SCHEMA statement is sent to the DB2 server. This allows the application to name SQL objects without having to qualify them by a schema name.

DB2 Connect

Change to authentication type negotiation through a gateway

Starting with DB2 Connect^(TM) Version 8.2.2 (equivalent to Version 8.1 FixPak 9) the gateway is no longer a passive participant during authentication negotiation. Instead, the gateway takes an active role. The authentication type specified in the database directory entry at the gateway overrides the authentication type cataloged at the client. The client, gateway, and server must all specify compatible types. If the cataloged authentication type at the gateway has not been specified in the database directory entry, SERVER authentication will be the default type requested of the server. However, negotiation will still take place between the client and server if the server does not support SERVER authentication. This behavior is in contrast to the client which defaults to SERVER_ENCRYPT if an authentication type has not been specified.

The authentication type cataloged at the gateway is not used if DB2NODE or the SQL_CONNECT_NODE option of the Set Client API has been set at the client. In these cases negotiation is still strictly between the client and the server.

A server with SERVER_ENCRYPT specified as the authentication type in the database manager configuration no longer accepts connections or attachments from clients that request SERVER authentication.

New security scenario

A new security scenario has been added for APPC connections:

Authentication

GSSPLUGIN

Security

none

Validation

GSS API security plugin mechanism

Corrections to diagrams

The following DB2 Connect Enterprise Edition topics have diagrams that are incorrect:

• Accessing host or iSeries DB2 data using DB2 Connect Enterprise Edition
• Accessing DB2 data from the Web using Java

The following table outlines corrections for diagrams in the "Accessing host or iSeries DB2 data using DB2 Connect Enterprise Edition" topic.

The following table outlines corrections for diagrams in the "Accessing DB2 data from the Web using Java" topic.

Development Center

DB2 Development Center Version 8.2 requires level 9.2.9 of the Distributed Debugger

DB2 Development Center Version 8.2 now requires Version 9.2.9 of the IBM Distributed Debugger. If you do not have Version 9.2.9 of the Distributed Debugger installed, you cannot debug Java stored procedures using the Development Center.

Version 9.2.9 of the Distributed Debugger no longer supports Solaris Operating Environments.

For more information about the Distributed Debugger, visit to the Distributed Debugger Web site at http://www.ibm.com/software/awdtools/debugger.

Length restriction in the DB2 Development Center Change Variable Range window

When you are changing the length of a variable using the Change Variable Range dialog in DB2 Development Center, there is a maximum length of 1024 bytes. This limitation is currently documented in an English-only message.

Restrictions to DB2 Universal type 2 and type 4 drivers

In Version 8.2, support was added to allow users to connect to a DB2 Universal Database (UDB) database from within the Development Center using the DB2 Universal Type 2 and Type 4 drivers. However, if you attempt to use one of these drivers to connect to an iSeries server, or to a DB2 UDB server that is Version 8.1 or earlier, you will see the following error message:

Connection to <database> failed. 
IBM DB2 Universal driver (JCC) not found.

Refer to the topic titled "JDBC drivers" in the DB2 Information Center for additional information on which drivers to use in order to avoid this error.

GUI Tools

Modifying the statement termination character

Starting with DB2 Universal Database (UDB) Version 8.2 FixPak 1 (equivalent to Version 8.1 FixPak 8), you can modify the statement termination character within a script that is being run in the command line processor (CLP) or the Command Editor. This on the fly modification is similar to the method currently available in DB2 UDB for OS/390. The following example shows how the termination character can be changed after each statement:

   connect to gilroy user newton using password;
   select * from newton.department;
   --#SET TERMINATOR :
   select * from newton.employee:
   --#SET TERMINATOR @
   select * from newton.department@
   --#SET TERMINATOR ;
   select * from newton.department;
   --#SET TERMINATOR &
   terminate&

The ability to change the termination character is important when a script contains compound statements. In the following example, DB2 UDB assumes that the first ; that it encounters in the compound CREATE TRIGGER statement is the termination character for the whole CREATE TRIGGER statement. However, this is not the case. It is only meant to be the termination character for one of the statements inside the compound CREATE TRIGGER statement.

  
   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END;
   CONNECT RESET;
   TERMINATE;

The following example shows how the statement termination character can be modified within the script to achieve the desired results.

   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   --#SET TERMINATOR @
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END@
   --#SET TERMINATOR ;
   CONNECT RESET;

If you do not need your scripts to run locally on DB2 for OS/390 or your DB2 UDB scripts do not connect to OS/390, then using --#SET TERMINATOR is not the recommended method for modifying statement termination characters. Instead you should use the existing -tdX or ;-- options.

The -tdX option allows you to specify the termination character when calling a script using a CLP command. The 'X' represents the character being used as the statement termination character. For example, in the command:

   db2 -tvf test.txt -td&

The & will be used as the statement termination character when running the script in the test.txt file. If that script contained the compound CREATE TRIGGER statement, it would be written as:

   CONNECT TO SAMPLE&
   DROP TRIGGER newton.NWTTRIGGER&
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');
      insert into newton.nwttable values( -1, '-1');
   END&
   CONNECT RESET&
   TERMINATE&

The script containing the compound CREATE TRIGGER statement can also be rewritten using the ;-- option as follows:

   CONNECT TO SAMPLE;
   DROP TRIGGER newton.NWTTRIGGER;
   CREATE TRIGGER newton.NWTTRIGGER AFTER DELETE 
   ON newton.NWTTABLE FOR EACH ROW MODE DB2SQL 
   BEGIN ATOMIC
      insert into newton.nwttable values(0,'0');--
      insert into newton.nwttable values( -1, '-1');--
   END;
   CONNECT RESET;
   TERMINATE;

Database unavailable status in the database details pane of the Control Center

You can use the Control Center's details pane to view information about your databases. Selecting a database in the object tree or contents pane displays a summary of its state. In certain situations database information might be unavailable. Some reasons for this unavailability are described in the following table.

Default write to table output generation (Create Event Monitor)

A Generate button has been added to the Output options dialog, which is launched from the Create Event Monitor window. Clicking the Generate button generates the default write to table output option. This output is equivalent to the syntax generated by the db2evtbl command.

The generated option shows the user which tables and data elements will be included when the event monitor is created. Users can modify the command to suit their needs.

The generated syntax is based on the event monitor name and event types specified in the Create Event Monitor window. Specify the event monitor name and event types before generating the output option syntax.

If the event monitor name or event types change after the generation of the output option, a message displays to remind the user to regenerate the output option before creating the event monitor. If the output option is not regenerated, event tables will be generated based on the event monitor name that was previously specified.

Information Catalog Center

Configuration sample scripts

The ICCConfig.jacl and ICCConfig.properties sample scripts are provided with the Information Catalog Center for the Web with the DB2 Embedded Application Server. You can use these sample scripts to configure the Information Catalog Center for the Web with WebSphere Application Server 5. These scripts are located in the sqllib\samples\icweb directory.

Web server configuration

When configuring the Information Catalog Center for the Web with the DB2 Embedded Application Server, if your metadata contains URLs that access files on the server, you must map the URLs to the correct location by using aliases in the Web server configuration. You must also map the help and copyright links. If you use the DB2 Embedded Application Server, a Web server must be configured correctly and running for these links to work even though you don't need to plug in to a Web server.

Installation and Configuration Supplement

DB2 node configuration file - db2nodes.cfg

The resourcesetname parameter is only supported on AIX, HP-UX, Solaris Operating Environment and Linux.

On Linux operating systems, the resourcesetname column defines a number that corresponds to a Non-Uniform Memory Access (NUMA) node on the system. The numactl system utility must be available in addition to a 2.6 kernel with NUMA policy support. Refer to the man page for numact1 for more information about NUMA support on Linux operating systems.

DB2NOLIOAIO registry variable replaced by DB2LINUXAIO (Linux)

The DB2 Universal Database (UDB) registry variable DB2NOLIOAIO is deprecated as of Version 8.2.2 (equivalent to Version 8.1 FixPak 9). For Linux users, the DB2NOLIOAIO registry variable has been replaced with DB2LINUXAIO.

• To enable asynchronous I/O (AIO) support on Linux, run the following command:

    db2set DB2LINUXAIO=true

  and restart DB2 UDB.
  Note:
  To use AIO, users must install libaio-0.3.98 or later and have a kernel that supports AIO.
• To disable AIO support on Linux, run the following command:

    db2set DB2LINUXAIO=false

  and restart DB2 UDB.

Application server for DB2 UDB

The application server for DB2 Universal Database (UDB) no longer supports remote administration or stored procedures.

The updated topics are as follows:

Enabling the application server for DB2 UDB

Enabling the database does the following:

• Connects to a specified database
• Creates and populates metadata tables
• Updates the DBM CFG parameters, JDK_PATH, and JAVA_HEAP_SZ
• Installs the DB2 Web Services Application.

Starting the application server for DB2 UDB locally

The application server should be started using the fenced user ID for systems creating web services in a .NET environment or running XML Metadata Registry (XMR) only.

Starting the application server for DB2 UDB remotely

This section is removed. The application server for DB2 UDB no longer supports remote administration.

Stopping the application server for DB2 UDB locally

The application server should be stopped using the fenced user ID for systems creating web services in a .NET environment or running XML Metadata Registry (XMR) only.

Stopping the application server for DB2 UDB remotely

This section is removed. The application server for DB2 UDB no longer supports remote administration.

Uninstalling the application server for DB2 UDB

This section is removed. The application server for DB2 UDB no longer supports remote administration.

DB2 Embedded Application Server enablement

The enabled database of a DB2 Embedded Application Server must be located in a 32-bit instance Any databases accessed from the DB2 Embedded Application Server can be located in 32-bit or 64-bit instances.

Deploying DB2 Web Tools

Application servers using JDK 1.4 no longer require customization of the CLASSPATH variable during DB2 Web Tools deployment. All dependencies, including those for XML parser and transformer, are now deployed with the web module and are expected to be loaded from the WEB-INF\lib directory according to the J2EE specification. This change affects two information topics:

• Deploying DB2 Web Tools on WebLogic application servers
• Deploying DB2 Web Tools on other application servers

The updated topics are as follows:

Deploying DB2 Web Tools on WebLogic application servers

This task describes how to deploy and configure DB2 Web Tools (including the Web Command Center and the Web Health Center) on BEA WebLogic 7.0. These tools run as Web applications on a Web server to provide access to DB2 servers through Web browsers.

Deploying DB2 Web Tools on other application servers

This task describes how to deploy and configure DB2 Web Tools (including the Web Command Center and the Web Health Center) on other application servers such as Tomcat 4.0 and Macromedia JRun 4.0. These tools run as Web applications on a Web server to provide access to DB2 servers through Web browsers.

Direct I/O on block devices (Linux)

Direct I/O is now supported on both file systems and block devices for distributions of Linux with a 2.6 kernel. Direct I/O on block devices is an alternative way to specify device containers for direct disk access or for raw I/O. The performance of Direct I/O is equivalent to the raw character device method. DB2 Universal Database (UDB) enables Direct I/O while opening the table space when the CREATE TABLESPACE statement specifies a block device name for the container path. Previously, the same performance was achieved using the raw I/O method, which required binding the block device to a character device using the raw utility.

CREATE TABLESPACE dms1
MANAGED BY DATABASE
USING (DEVICE '/dev/sda5' 11170736)

CREATE TABLESPACE dms1
MANAGED BY DATABASE 
USING (DEVICE '/dev/raw/raw1' 11170736)

Although DB2 UDB still supports the method of using the raw utility for raw I/O, recent distributions have deprecated this feature and may remove it in the future. The preferred method is to use the new method by specifying the devices directly.

DB2 Information Center daemon (Linux and UNIX)

The DB2 Information Center daemon is responsible for the control of the DB2 documentation server. The daemon, which is part of the DB2 Information Center installation, is composed of two files:

• db2icd - the initialization script
• db2ic.conf - the configuration file

These files are installed in the following locations:

AIX

/etc/db2icd

/var/db2/v81/db2ic.conf

HP

/sbin/init.d/db2icd

/var/opt/db2/v81/db2ic.conf

Solaris Operating Environment

/etc/init.d/db2icd

/var/db2/v81/db2ic.conf

Linux

/etc/init.d/db2icd

/var/db2/v81/db2ic.conf

Starting or stopping the Information Center daemon (AIX, Solaris Operating Environment, HP, Linux)

The only time you should need to start or stop the daemon manually is when you want to change the configuration variables for the daemon. Normally, the daemon is started at system startup, according to the run levels created during the installation of the DB2 Information Center.

INIT_DIR/db2icd restart

INIT_DIR/db2icd status

Response file keywords and sample files

To install the DB2 Information Center Version 8.2 using a response file, use the following information:

Response file keyword (PROD)

INFORMATION_CENTER

Response file name

db2doce.rsp

Response file installation error codes

The following error code is for Windows only and is not applicable to Linux and UNIX operating systems.

3010

The installation is successful, however a system restart is required to complete the installation.

Required user accounts for installation of DB2 servers (Windows)

Increase quotas

The Increase quotas user right has been changed to Adjust memory quotas for a process on the Windows XP and Windows Server 2003 operating systems.

User rights granted by the DB2 installer - Debug Programs

The DB2 installation program does not grant the Debug Programs user right. The DB2 installer grants the following user rights:

• Act as part of the operating system
• Create token object
• Lock pages in memory
• Log on as a service
• Increase quotas
• Replace a process level token

Asynchronous I/O support (Linux)

Asynchronous I/O (AIO) support is now available on Linux (2.6 and some 2.4 kernels) for raw devices and O_DIRECT file systems. AIO improves page cleaner performance. You can enable or disable AIO on Linux by issuing the db2set command.

To use AIO, users must install libaio-0.3.98 or later and have a kernel that supports AIO. Users must also run the db2set DB2LINUXAIO=true command and restart DB2 Universal Database.

db2ln command modified to create DB2 UDB 64-bit library links (Linux and UNIX)

In previous levels of DB2 Universal Database (UDB) Version 8, the db2ln command created certain DB2 links under /usr/lib and /usr/include. On platforms where both 32-bit and 64-bit DB2 UDB instances are supported, these links point to library files or include files under DB2DIR/lib64 or DB2DIR/include64 by default, where DB2DIR is the directory where DB2 UDB Version 8 is installed. If the default is not desired, you can specify the bit width by running the db2ln command with the -w flag:

db2ln -w 32|64

This prevents DB2 UDB 32-bit instances from coexisting with 64-bit instances on some platforms.

Starting with DB2 UDB Version 8.2, the db2ln command creates DB2 64-bit library links on those platforms in appropriate directories. In this case, the -w flag is only used for populating /usr/include. When the db2ln command creates the links for DB2 UDB library files, both 32-bit and 64-bit links are created on supported platforms. This allows both 32-bit instances and 64-bit instances to exist and run at the same time.

On some Linux distributions, the libc development rpm comes with the /usr/lib/libdb2.so or /usr/lib64/libdb2.so library. This library is used for Sleepycat Software's Berkeley DB implementation and is not associated with IBM's DB2 UDB. However, this file prevents the db2ln command and the db2rmln command from functioning. The db2ln command does not overwrite the file and the db2rmln command does not remove the file. In this case, to compile applications using DB2 UDB, compiling and linking processes need to provide a full path to DB2 UDB's headers and libraries, respectively. This is the recommended method because it allows compiling and linking against multiple releases of DB2 UDB on the same computer.

See the DB2 UDB Version 8.2 Installation and Configuration manual for details about restrictions using the db2ln command.

Query Patroller

Query class behavior update

A warning message is returned when one of the following tasks is performed through the Query Patroller Center or Query Patroller command line:

• Adding a query class
• Removing a query class
• Updating the maximum cost of a query for a query class

The warning message is:

DQP1024W  Creation, change, or removal of a query class will not 
          take effect until the Query Patroller server is restarted.

Similarly, the DB2 Query Patroller^(TM) Guide: Installation, Administration, and Usage, Version 8.2, states that you must restart the Query Patroller server after creating, changing, or removing query classes for your changes to take effect.

The message and the statement in the guide are no longer accurate. The three query class tasks listed previously will take effect immediately unless there are queued or running queries. If there are queued or running queries, including newly submitted queries, the query class changes will take effect when the queued or running queries complete. If you do not want to wait for all queued and running queries to complete, you must restart the Query Patroller server.

Definition updates for managed query states

The Canceled and Done query status meanings are updated as follows:

Canceled

The query was canceled, through either the Query Patroller Center or the Query Patroller command line, by the administrator, submitter, or an operator whose profile has the MONITORING privilege with edit authority. Only running, held, released, and queued queries can be canceled.

Done

The query completed successfully.

Note:

Although the query itself completed without error, the application may receive an error if the completion was caused by an external event, such as a DB2 force application.

Create Explain tables before running Query Patroller historical data generator

When running the historical data generator for Query Patroller, if the Explain tables do not already exist, the generator will create them for you. However, it is strongly recommended that you create the Explain tables before running the historical data generator. When you create the Explain tables be sure you create them on the same partition. Actively creating the Explain tables on the same partition improves the performance of the Explain facility. This improvement increases the performance of the historical data generator.

Checking Query Patroller log files for historical analysis

If the Explain Run column of the Query Activity over Time (Historical Analysis) report shows a status of Ran unsuccessfully for a query, historical data has not been generated for that query. Therefore, the query will not appear in any historical analysis reports or graphs. As documented in Version 8, to determine why the query was unsuccessful, you can examine the qpuser.log file.

In addition to examining the qpuser.log file, you should examine the qpdiag.log file.

Abnormal shutdown of the historical data generator

If you run the historical data generator and shut it down in an abnormal way, you will receive an error the next time you attempt to run the historical data generator. Examples of abnormal shutdown include:

• DB2 Universal Database stops unexpectedly
• Issuing a db2stop force command
• Issuing a killdb2 command

When the historical data generator shuts down abnormally, you must issue the following command before attempting to rerun the historical data generator:

    qp -d database generate historical_data stop

where database identifies the database that the command is being run against.

Dynamic query class updates

Certain query class operations no longer require Query Patroller to be stopped and restarted to take effect.

In the table that follows, an active query is a query whose status is Running or Queued.

Nested query behavior

Nested queries cannot be queued. Instead, a nested query will run immediately if it exceeds a threshold that would normally cause it to be queued.

Limitations by SQL statement type

Contrary to previous documentation, the queries with the following statements can be queued:

• Queries containing static SQL with host variables
• Queries containing an identity value function (IDENTITY_VAL_LOCAL) or a sequence value function, such as NEXT VALUE FOR or PREVIOUS VALUE FOR

Resolution limitation when using the Terminal Services Client

When using the Terminal Services Client at resolution 640x480 to connect to a remote desktop that is running the Query Patroller Center, the Submission Preferences window might appear blank. For the Submission Preferences window to display properly, you must use a resolution higher than 640x480.

New group support for query submissions

Starting in Version 8.2, DB2 Universal Database (UDB) supports user groups beyond operating system groups. Therefore, there is a slight change in the Submitter Profile to Use drop-down list in the Query Submission Preferences window of the Query Patroller Center.

If you are logged in, but do not have either DBADM authority or Edit privilege for Query Patroller user administration, you can only add or update a submission preference for yourself. In this case, the Submitter Profile to Use drop-down list contains existing submitter profiles of the DB2 UDB groups that you belong to, instead of just the operating system groups that you belong to.

If you are logged in, and have either DBADM authority or Edit privilege for Query Patroller user administration, you can add or update submission preferences for other users. In this case, the Submitter Profile to Use drop-down list contains all existing group submitter profiles.

Query Patroller schedule limitations

When working with schedules in the Query Patroller Center, you can use the Schedule window to save schedules to a file and import them later. If you have a schedule that you saved using FixPak 6 or earlier, you cannot import the schedule using Version 8.2 or later. This limitation is due to the change in serialization between JDK levels introduced with DB2 UDB Version 8.2.

Authorization required to use RUN IN BACKGROUND QUERY command

To run the RUN IN BACKGROUND QUERY command, you must be the submitter who submitted the query originally.

Creating an alias for a result table

As of Query Patroller Version 8.1 FixPak 5, Query Patroller stopped creating result tables in the schema that matched the authorization ID of the submitter of the query. Instead, Query Patroller started creating result tables in a common DB2QPRT schema. To allow result tables to be referenced using the schema of the submitter, Query Patroller Version 8.2 introduces an option to automatically create an alias for each new result table that Query Patroller creates. The result table is created in the DB2QPRT schema and the alias is created in a schema that matches the submitter's authorization ID.

To turn this option on or off, issue the UPDATE QP_SYSTEM command with the CREATE_RESULT_TABLE_ALIASES option:

>>-UPDATE QP_SYSTEM USING--------------------------------------->
 
>--+-DEFAULT------------------------------+--------------------><
   '-CREATE_RESULT_TABLE_ALIASES--+-'Y'-+-'
                                  '-'N'-'
 

Removing orphaned result table aliases

Aliases created with CREATE_RESULT_TABLE_ALIASES option are automatically dropped when a result table is dropped. However, there are two situations in which a result table may be dropped without the corresponding alias being dropped.

• When the result table is dropped manually without using the qp command line or Query Patroller Center.
• When the result table is dropped using the qp command line or Query Patroller Center under the authority of an operator who is not the submitter of the query and does not have DBADM authority.

To clean up aliases that have no corresponding result tables, a new command, REMOVE RESULT_TABLE_ALIASES, has been created. This command is automatically executed whenever result tables are purged as part of the Query Patroller scheduled result table purging process. The REMOVE RESULT_TABLE_ALIASES command obtains the list of aliases to purge using the following query:

with a as (select tabschema, tabname from syscat.tables 
           where type = 'A' and tabname like 'QUERY%_RESULTS'), 
     t as (select tabname from syscat.tables 
           where type = 'T' and tabname like 'QUERY%_RESULTS')
  select all tabschema, tabname from a 
  where not exists (select * from t where t.tabname=a.tabname)

>>-REMOVE RESULT_TABLE_ALIASES---------------------------------><
 

Fenced user ID requires write access qpdiag.log file and path

Query Patroller uses some fenced stored procedures which may log entries to the qpdiag.log file. Therefore, the fenced user ID must have access to write to the qpdiag.log file and the path where the qpdiag.log file resides.

Quick Beginnings

Updating the DB2 Information Center installed on your computer or intranet |server

To update your locally-installed DB2 Information Center, download the |latest DB2 documentation FixPak from http://www.ibm.com/software/data/db2/udb/support/downloadv8_docfix.html. However, DB2 documentation FixPaks are released infrequently and may |not reflect the most current level of the DB2 documentation. The latest version |of the DB2 documentation is always the version of the DB2 Information Center |hosted at http://publib.boulder.ibm.com/infocenter/db2help/

Memory requirements

At a minimum, DB2 Universal Database (UDB) requires 256 MB of RAM. For a system running just DB2 UDB and the DB2 GUI tools, a minimum of 512MB of RAM memory is required. However, 1GB of RAM memory is recommended for improved performance. These requirements do not include any additional memory requirements for other software that is running on your system.

When determining memory requirements, be aware of the following:

• For DB2 client support, these memory requirements are for a base of 5 concurrent client connections. You will need an additional 16 MB of RAM per 5 client connections.
• Memory requirements are affected by the size and complexity of your database system, as well as by the extent of database activity and the number of clients accessing your system.
• On Linux operating systems, a SWAP space that is at least twice as large as the amount of RAM is recommended but not required.

Clarification of DB2 UDB client support

The "DB2 clients" topic in the 8.1 version of the DB2 Quick Beginnings for Clients guide states the following:

DB2 |clients can connect to DB2 servers two releases later or one release earlier |than the client's release level, as well as to servers at the same release |level.

An amendment to that statement is as follows:

While connections from |Version N clients to Version N + 2 servers are possible in some environments, |the DB2 support team will only provide support for this configuration as long |as Version N is still in service. Once Version N is withdrawn from service, |this configuration is no longer supported by the DB2 support team. DB2 Version |7 clients connecting to a DB2 Version 8 server is no longer supported by the |DB2 support team because Version 7 has been withdrawn from service.

Modifying kernel parameters (Linux)

Before installing DB2 UDB, you should consider updating your Linux kernel parameters. DB2 Universal Database (UDB) automatically raises the IPC limits where necessary. You might want to raise these limits higher depending on your particular needs.

Modifying kernel parameters (Solaris Operating Environment)

The following information is an amendment to the "Modifying kernel parameters (Solaris Operating Environment)" topic in the Quick Beginnings for DB2 Servers guide:

For DB2 Universal Database (UDB) to operate properly, it is recommended that you update your system's kernel configuration parameters. You can use the db2osconf utility to suggest recommended kernel parameters.

To use the db2osconf command, you must first install DB2 UDB. The db2osconf utility can only be run from $DB2DIR/bin.

You must restart your system after modifying kernel parameters.

DB2 Universal Database Express Edition Version 8.2 Basics manual available for download

IBM DB2 Universal Database Express (DB2 UDB Express) is the newest member of the DB2 Universal Database Version 8 product family. It combines the power, function, and reliability of the IBM award-winning DB2 UDB relational database with simplicity in packaging, installation, and deployment at a minimal investment cost to meet the data management needs of small and medium businesses.

DB2 UDB Express is designed for customers with minimal in-house database skills who need an easy-to-install database integrated into their application software solutions. It is a multi-user version of DB2 UDB that supports local and remote applications in stand-alone and local area network (LAN) environments.

For further information on DB2 UDB Express, download Quick Beginnings for DB2 Express Edition and DB2 Universal Database Express Edition Version 8.2 Basics from the DB2 UDB product manuals Web page at http://www.ibm.com/software/data/db2/udb/support/manualsv8.html

Verifying that your databases are ready for migration

The following prerequisites section is documented in the 8.2 version of the topic that explains how to verify that your databases are ready for migration:

Prerequisites

Ensure that the migration.log file, found in the instance owner's home directory, contains the following text: Version of DB2CKMIG being run: VERSION 8.

This prerequisite is in fact a post-migration step performed at the end of the procedure.

Common Criteria certification for DB2 UDB

The authoritative information for DB2 UDB configurations that have been certified for Common Criteria can be found at http://niap.nist.gov/cc-scheme

Spatial Extender

Verifying the Spatial Extender installation

The runGseDemo sample program can be used to become familiar with application programming for DB2 Spatial Extender. For a description of the steps that the sample program takes to create a spatially-enabled database and perform spatial analysis on data in that database, refer to the topic titled "The DB2 Spatial Extender sample program". This topic is in the Information Center and the Spatial Extender and Geodetic Extender User's Guide and Reference.

DB2 Spatial Extender provides another sample program, seBankDemoRunBankDemo, that demonstrates how to add spatial capabilities to an existing information system.

For more information about both sample programs, see the README files in the following directories:

Windows

~\sqllib\samples\spatial
~\sqllib\samples\spatial\bank

Linux and UNIX

~/sqllib/spatial
~/sqllib/spatial/bank

SQL Administrative Routines

Incorrect column name documented in result set for SNAP_GET_DYN_SQL table function

The "SNAP_GET_DYN_SQL table function" topic in the DB2 Information Center Version 8.2.2 (equivalent to Version 8.1 FixPak 9) incorrectly documents the result set for the SNAP_GET_DYN_SQL table function.

One of the columns is incorrectly documented as STMT_TXT.

The correct name for the output column is STMT_TEXT.

Snapshot monitor table functions have version-specific views

Version-specific views have been defined on the following snapshot monitor table functions, introduced in DB2 Universal Database Version 8.2.2 (equivalent to Version 8.1 FixPak 9):

• SNAP_GET_CONTAINER
• SNAP_GET_DB
• SNAP_GET_DYN_SQL
• SNAP_GET_STO_PATHS
• SNAP_GET_TAB
• SNAP_GET_TBSP
• SNAP_GET_TBSP_PART

The version-specific views are as follows:

• SYSCATV82.SNAPCONT
• SYSCATV82.SNAPDB
• SYSCATV82.SNAPDYNSQL
• SYSCATV82.SNAPSTOPATHS
• SYSCATV82.SNAPTAB
• SYSCATV82.SNAPTBSPACE
• SYSCATV82.SNAPTBSPACEPART

Because there is no guarantee that the result tables from the snapshot monitor table functions will remain unchanged from release to release, it is recommended that you use the version-specific views if you want guaranteed result tables. Each view contains all of the columns from the result table of its associated snapshot monitor table function.

GET_DB_CONFIG procedure requires an 8K minimum page size for a user temporary table space

The GET_DB_CONFIG procedure requires a user temporary table space with a page size of at least 8K.

The documented example that shows you how to use the GET_DB_CONFIG procedure should be replaced by the following example.

Using the command line processor (CLP), change the value of the logretain and the userexit database configuration parameters. Retrieve the original (on disk) and updated (in memory) values by calling the GET_DB_CONFIG procedure.

   UPDATE DB CFG USING LOGRETAIN RECOVERY USEREXIT YES
   CALL SYSPROC.GET_DB_CONFIG()

The following is an example of partial output from this procedure call.

Result set 1
--------------

DBCONFIG_TYPE ... LOGRETAIN   ... USEREXIT...
-------------     -----------     -----------
            0               1               1
            1               0               0

2 record(s) selected.

Return Status = 0

SQL Reference

CREATE PROCEDURE (SQL) statement authorizations

If the authorization ID of the statement has SYSADM authority but not DBADM |authority, this ID is granted implicit DBADM authority for the purpose of |creating the procedure. This applies to the CREATE PROCEDURE statement (SQL) |and not the CREATE PROCEDURE statement (External).

EXPLAIN_DIAGNOSTIC: A new Explain table

The EXPLAIN_DIAGNOSTIC table contains an entry for each diagnostic message produced for a particular instance of an explained statement in the EXPLAIN_STATEMENT table.

The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables and returns formatted messages.

EXPLAIN_DIAGNOSTIC_DATA: A new Explain table

The EXPLAIN_DIAGNOSTIC_DATA table contains message tokens for specific diagnostic messages that are recorded in the EXPLAIN_DIAGNOSTIC table. The message tokens provide additional information that is specific to the execution of the SQL statement that generated the message.

The EXPLAIN_GET_MSGS table function queries the EXPLAIN_DIAGNOSTIC and EXPLAIN_DIAGNOSTIC_DATA Explain tables, and returns formatted messages.

Schema used by the Explain facility

The Explain facility uses the following IDs as the schema when qualifying Explain tables that it is populating:

• The session authorization ID for dynamic SQL
• The statement authorization ID for static SQL

The schema can be associated with a set of Explain tables, or aliases that point to a set of Explain tables under a different schema.

If no Explain tables are found under the schema, the Explain facility checks for Explain tables under the SYSTOOLS schema and attempts to use those tables.

String representations of datetime values

Starting with Version 8.2, "AM" and "PM" can be represented in lowercase or uppercase.

System monitor

Health indicators summary

In the "Health indicators summary" topic in the DB2 Information Center for Version 8.2.2 (equivalent to Version 8.1 FixPak 9), the identifier for the Database automatic storage utilization health indicator is incorrectly documented as db.db_auto_storage_util.

The correct identifier for the Database automatic storage utilization health indicator is db.auto_storage_util.

Decoupled applications list without connection concentrator enabled

It is possible to see decoupled applications when issuing the list applications command even without connection concentrator enabled.

Progress monitoring of the runtime rollback process

Progress monitoring of runtime rollback provides progress information of rollback events using application snapshots. Rollback events are of two types:

Unit of work rollback

Includes explicit (user invoked) and implicit (forced) rollback of the entire transaction.

Savepoint rollback

Includes statement and application level savepoints. Nested savepoints are considered a single unit, using the outermost savepoint.

The information provided is the start time of the rollback event, the total work to be done, and completed work. The work metric is bytes.

Total Work units is the range in the log stream that needs to be rolled back for the transaction or savepoint.

Completed Work units shows the relative position in the log stream that has been rolled back.

Updates to Completed Work are made after every log record is processed. Updates are not performed evenly because log records vary in size.

            Application Snapshot

Application handle         = 6
Application status         = Rollback Active
      Start Time           = 02/20/2004 12:49:27.713720
      Completed Work       = 1024000 bytes
      Total Work           = 4084000 bytes

            Application Snapshot

Application handle         = 10
Application status         = Rollback to Savepoint
      Start Time           = 02/20/2004 12:49:32.832410
      Completed Work       = 102400 bytes
      Total Work           = 2048000 bytes

XML Extender

Parameter change to dxxGenXML, dxxGenXMLClob, dxxRetrieveXML, and dxxRetrieveXMLClob stored procedures

The override parameter's description for the following stored procedures has changed:

• dxxGenXML()
• dxxGenXMLClob()
• dxxRetrieveXML()
• dxxRetrieveXMLClob()

The update is as follows:

Decomposing XML documents into DB2 UDB data

Decomposition uses RDB_node mapping to specify how an XML document is decomposed into DB2 UDB tables by extracting the element and attribute values and storing them in table rows. The values from each XML document are stored in one or more DB2 UDB tables. Each table can have a maximum of 10240 rows decomposed from each document.

For example, if an XML document is decomposed into five tables, each of the five tables can have up to 10240 rows for that particular document. If the table has rows for multiple documents, it can have up to 10240 rows for each document.

Using multiple-occurring elements (elements with location paths that can occur more than once in the XML structure) affects the number of rows . For example, a document that contains an element <Part> that occurs 20 times, might be decomposed as 20 rows in a table. When using multiple occurring elements, consider that a maximum of 10240 rows can be decomposed into one table from a single document.

Decomposing documents larger than 1 MB

You do not need to drop and recreate the stored procedure dxxShredXML to decompose documents that are larger than 1 MB. To decompose documents that are larger than 1 MB, invoke the stored procedure dxxShredXML100MB, which can shred documents up to 100 MB. Although dxxShredXML100MB can handle large documents, you might need to increase other resources for the stored procedure to complete successfully. To invoke the stored procedure through the sample program dxxshrd, use the new flag "-large". For example:

dxxshrd -large mydb xxx.xml

If your version of DB2 Universal Database is earlier than Version 8 FixPak 6, you must run dxxMigv to migrate XML Extender to the current level to run the new stored procedure.

Configuring MQ XML UDFs with XML Extender

You must configure and enable MQ XML user-defined functions (UDFs) before you can use them.

XML Extender environment variable DB2DXX_MIN_TMPFILE_SIZE

DB2 XML Extender can place large documents in temporary files to avoid using a great amount of memory during processing. On systems with large amounts of physical memory, you can avoid moving documents to temporary files, reducing the amount of Input/Output activity. The environment variable DB2DXX_MIN_TMPFILE_SIZE instructs XML Extender to use memory buffers, instead of temporary files, for processing documents smaller than the specified value. The variable is applicable only on the server. If multiple physical nodes participate in a partitioned environment, the variable can be set differently on each node, accurately reflecting the amount of memory installed on each computer. If the environment variable is not set, documents larger than 128 KB will automatically be placed into temporary files during processing. Documents smaller than 128 KB will be processed in memory.

DB2XML.XMLVarchar UDT redefinition

You can redefine the user-defined type (UDT) DB2XML.XMLVarchar up to 32 KB. To change the size of an XMLVarchar UDT, create the UDT before you enable the database for XML Extender.

For more information, see DB2 XML Extender Administration and Programming.