Documentation updates

Administration: Implementation

Automatic client reroute considerations for cataloging on a DB2 Connect 7server

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

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

Local system account support (Windows)

Applications running under the context of the local system account (LSA) 7are 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 7qualifier of the SAM-compatible user ID is the NetBIOS style name which has 7a maximum length of 15 characters. This feature is not supported on Windows 7ME.

Additional information for Kerberos support

Administration: Performance

New communication registry variable

The DB2TCP_CLIENT_RCVTIMEOUT registry variable has been added in version 78.2.

New performance variable

The DB2_LARGE_PAGE_MEM performance variable has been added in 7version 8.2.

      cat /proc/meminfo

      HugePages_Total:   200
7      HugePages_Free:    200
7      Hugepagesize:    16384 KB

Change to default value for DB2_ENABLE_BUFPD registry variable

The default value for the DB2_ENABLE_BUFPD registry variable has been changed 6to ON.

DB2NTNOCACHE registry variable is deprecated

Functionality previously achieved through DB2NTNOCACHE can be achieved 7at the table space level by specifying the NO FILE SYSTEM CACHING clause on 7the CREATE TABLESPACE or the ALTER TABLESPACE statement. Refer to SQL Reference for details on usage. The DB2NTNOCACHE registry variable 7will 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 7tables can be defined for one user, and aliases can be defined for each additional 7user using the same name to point to the defined tables. Alternatively, the 7explain tables can be defined under the SYSTOOLS schema. The Explain facility 7will default to the SYSTOOLS schema if no other explain tables or aliases 7are found under the user's session ID for dynamic SQL, or the statement authorization 7ID for static SQL. Each user sharing the common explain tables must have insert 7permission on those tables. Read permission for the common explain tables 7should 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. 7Consider how you expect to use the captured information when you request explain 7data.

Additional return codes from db2CfgGet API, collate_info parameter

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

7

Configuration Type

7

Database 7

7

Parameter Type

7

Informational 7

7

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

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

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

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

You can specify the collating sequence at database creation time.

Administration: Planning

XA function supported by DB2 Universal Database

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

7
• Asynchronous services 7

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

7
• Registration 7

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

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

  DB2 UDB does not support transaction migration 7between 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 7xa_switch_t to return the XA switch structure to the TM. Other than 7the addresses of various XA functions, the following fields are returned:

7

Field

7

Value

7

name

7

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

7

flags

7

For db2xa_switch TMREGISTER | TMNOMIGRATE is set 7

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

7

For db2xa_switch_static TMNOMIGRATE 7is set

7

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

7

7

version

7

Must be zero. 7

7

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) 7access to the RM's xa_ routines. An RM switch 7uses a structure called xa_switch_t. The switch contains the RM's name, 7non-NULL pointers to the RM's XA entry points, a flag, and a version number.

UNIX-based systems

DB2 UDB's switch can be obtained through either of the following two ways:

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

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

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

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

  7

     struct xa_switch_t * SQL_API_FN  db2xacic( )
  7   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 7implies that a Windows NT application using this structure must reference 7it in one of three ways:

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

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

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

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

7
• By calling db2xacic or db2xacicst 7

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

  7

  struct xa_switch_t * SQL_API_FN  db2xacic( )
  7struct 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 7on any DB2 UDB platform. Be sure to link your application with the appropriate 7library.

   #include <stdio.h>
7   #include <xa.h>
7
7   struct xa_switch_t * SQL_API_FN  db2xacic( );
7
7   #ifdef DECLSPEC_DEFN
7   extern __declspec(dllimport) struct xa_switch_t db2xa_switch;
7   #else
7   #define db2xa_switch (*db2xa_switch)
7   extern struct xa_switch_t db2xa_switch;
7   #endif

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

Activating conversion tables for code pages 923 and 924

The following table contains a list of all the code page conversion table 7files that are associated with code pages 923 and 924. Each file is of the 7form XXXXYYYY.cnv or ibmZZZZZ.ucs, where XXXXX is the source code page number 7and YYYY is the target code page number. The file ibmZZZZZ.ucs supports 7conversion 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 7to support the euro currency symbol. If you want to disable euro symbol support, 7download the conversion table file indicated in the column titled "Conversion 7table files".

API Reference

New field for SQLEDBDESC structure

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

7

Field name

7

Unsigned char sqlfscaching 7

7

Description

7

File system caching 7

7

Values

7

7

7

0

7

File system caching is ON for the current table space 7

7

1

7

File system caching is OFF for the current table space 7

7

other

7

File system caching is ON for the current table space 7

7

7

7

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

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

Application development: Building and Running Applications

Customizing precompile and bind options for SQL procedures

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

db2set DB2_SQLROUTINE_PREPOPTS=<options>

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

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

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

The compile option "-m64" is required for a 64-bit instance on DB2 UDB 7for Linux on PowerPC^(R) for building DB2 C/C++ applications and routines.

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

The compile and link command shown in the DB2 UDB Version 8.2 documentation 7for building stored procedures using Micro Focus COBOL on HP-UX is incorrect. 7 The compile command contained in the actual script sqllib/samples/cobol_mf/bldrtn is correct. The compile and link commands 7are now combined into one single command, using the -y option to 7specify 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 7time on HP-UX is Micro Focus Server Express 2.2 - 7Service Pack 1 plus Fix Pack Fixpack22.02_14 7for HP-UX PA-RISC 11.x (32/64bit). This Fix Pack is available from the 7Micro Focus Support Line Web site at http://supportline.microfocus.com.

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

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

Application development: Call Level Interface (CLI)

MapBigintCDefault CLI/ODBC configuration keyword

7

Keyword description:

7

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

7

db2cli.ini keyword syntax:

7

MapBigintCDefault = 0 | 1 | 2 7

7

Default setting:

7

The default C type representation for BIGINT data is SQL_C_BIGINT. 7

7

Usage notes:

7

  7

7

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

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

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

DescribeOutputLevel CLI/ODBC configuration keyword

7

Keyword description:

7

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

7

db2cli.ini keyword syntax:

7

DescribeOutputLevel = 0 | 1 | 2 | 3 7

7

Default setting:

7

Request the describe information listed in level 2 of Table 16. 7

7

Usage notes:

7

  7

7

This keyword controls the amount of information the CLI driver requests 7on a prepare or describe request. By default, when the server receives a 7describe request, it returns the information contained in level 2 of Table 16 for the result set columns. An application, however, might 7not need all of this information or might need additional information. Setting 7the DescribeOutputLevel keyword to a level that suits the needs of the client 7application might improve performance because the describe data transferred 7between the client and server is limited to the minimum amount that the application 7requires. If the DescribeOutputLevel setting is set too low, it might impact 7the functionality of the application (depending on the application's requirements). 7 The CLI functions to retrieve the describe information might not fail in 7this case, but the information returned might be incomplete. Supported settings 7for DescribeOutputLevel are:

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

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

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

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

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

Application development: Programming Client Applications

db2secFreeToken function removed

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

Security plug-ins

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

Security plug-in APIs

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

Security plug-in naming conventions (UNIX, Linux)

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

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

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

On all other UNIX and Linux platforms, .so is 7the 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 7of .a or .so. The 7mechanism used to load the plug-in library depends on which extension 7is used:

7

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

7

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

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

7

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

7

7

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

7

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

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

7

7

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

Implicit ending of transactions in stand-alone applications

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

Rerouting DB2 Universal JDBC driver clients

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

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

After the database administrator specifies the alternate server location 7on a particular database at the server instance, the alternate server location 7is returned back to the client at connect time. If communication is lost, 7the DB2 Universal JDBC driver can reestablish the connection using the alternate 7server information that had been returned from the server.

The activeServerListJNDIName DataSource property provides additional client 7reroute support at the client. The activeServerListJNDIName property has two 7functions:

7
• Allows alternate server information to persist across JVMs
7
• Provides an alternate server location in case the first connection to 7the database server fails

The activeServerListJNDIName property identifies a JNDI reference to a 7DB2ActiveServerList instance in a JNDI repository of alternate server information. 7After a successful connection to the primary server, the alternate server 7information that is provided by activeServerListJNDIName is overwritten by 7the information from the server.

Alternate server information received from the DB2 LUW server is stored 7in transient memory of the driver. If the JNDI store is defined, alternate 7server information is also updated there. The DB2 Universal JDBC driver attempts 7to propagate the updated information to the JNDI store after failover.

DB2ActiveServerList is a serializable Java bean with two properties: alternateServerName 7and alternatePortNumber. getXXX and setXXX methods are defined for each property. 7The Java bean is as follows:

package com.ibm.db2.jcc;
7public class DB2ActiveServerList implements java.io.Serializable,
7  javax.naming.Referenceable
7{
7  public String[] alternateServerName;
7  public synchronized void 
7    setAlternateServerName(String[] alternateServer);
7  public String[] getAlternateServerName();
7  public int[] alternatePortNumber;
7  public synchronized void 
7    setAlternatePortNumber(int[] alternatePortNumberList);
7  public int[] getAlternatePortNumber();
7}

A newly established failover connection is configured with the original 7DataSource properties, except for the server name and port number. In addition, 7any DB2 UDB special registers that were modified during the original connection 7are reestablished in the failover connection.

When a communication failure occurs, the DB2 Universal JDBC driver first 7attempts recovery to the original server. Reconnection to the original server 7is called failback. If failback fails, the driver attempts to connect to the 7alternate location (failover). After a failover or failback connection is 7reestablished, the driver throws a java.sql.SQLException to the application 7with SQLCODE -4498, which indicates to the application that a failover or 7failback occurred and the transaction failed. The application can then retry 7its transaction.

Customizing the DB2 Universal JDBC driver configuration properties

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

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

property=value

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

Application development: Programming Server Applications

Common language run time (CLR) routine execution control modes (EXECUTION 7CONTROL clause)

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

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

7
• SAFE
7
• FILEREAD
7
• FILEWRITE
7
• NETWORK
7
• UNSAFE

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

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

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

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

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

Maximum decimal precision and scale in common language run time (CLR) 7routines

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

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

Command Reference

db2inidb - Initialize a mirrored database command

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

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

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

Usage note for the db2iupdt command

Starting with version 8.2, when you update a DB2 UDB instance with the db2iupdt command, you must first stop any DB2 processes 7running against that instance.

db2pd - Monitor and troubleshoot DB2 command

Additional parameters for the db2pd command 7include:

7

-hadr

7

Reports High Availability Disaster Recovery information. Descriptions 7of each reported element can be found in the High availability disaster recovery 7section of the System Monitor Guide and Reference . 7

7

-utilities

7

Reports Utility information. Descriptions of each reported element 7can be found in the Utilities section of the System 7Monitor Guide and Reference . 7

7

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.

ATTACH command

The USER parameter of the ATTACH command specifies the authentication identifier. 7When attaching to a DB2 UDB instance on a Windows operating system, the user 7name can be specified in a format compatible with Microsoft Windows NT Security 7Account Manager (SAM). The qualifier must be a NetBIOS style name, which has 7a 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 7documentation, 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

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

db2updv8 - Update database to version 8 current level command

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

7
• Introduces new routines shipped with the current database manager
7
• Catalogs the ODBC/CLI/JDBC schema procedures as trusted procedures to 7improve performance and scalability
7
• Fixes two typographical errors in the SYSPROC.SNAPSHOT_QUIESCERS table 7function's return parameters: 7
  7
  • OUIESCER_TBS_ID corrected to QUIESCER_TBS_ID
  7
  • OUIESCER_STATE corrected to QUIESCER_STATE
7
• Fixes LOB columns' column descriptors such that "logged" bit is 7consistent with the catalog tables
7
• Creates SYSIBM.SYSREVTYPEMAPPINGS view
7
• Updates the view definitions for SYSSTAT.COLUMNS and SYSSTAT.TABLES
7
• Updates the SYSCOLDIST.DISTCOUNT column to be updateable
7
• Updates the SYSINDEXES.TBSPACEID column such that column flags are not 7set
7
• 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 theVersion 8.2 Release Notes for a complete listing 7of functions
7
• Alters 33 SYSPROC snapshot UDFs to THREADSAFE: 7
  7
  • SNAPSHOT_DBM
  7
  • SNAPSHOT_FCM
  7
  • SNAPSHOT_FCMNODE
  7
  • SNAPSHOT_SWITCHES
  7
  • SNAPSHOT_APPL_INFO
  7
  • SNAPSHOT_APPL
  7
  • SNAPSHOT_STATEMENT
  7
  • SNAPSHOT_LOCKWAIT
  7
  • SNAPSHOT_AGENT
  7
  • SNAPSHOT_SUBSECT
  7
  • SNAPSHOT_DATABASE
  7
  • SNAPSHOT_BP
  7
  • SNAPSHOT_LOCK
  7
  • SNAPSHOT_TABLE
  7
  • SNAPSHOT_DYN_SQL
  7
  • SNAPSHOT_TBS
  7
  • SNAPSHOT_TBS_CFG
  7
  • SNAPSHOT_QUIESCERS
  7
  • SNAPSHOT_CONTAINER
  7
  • SNAPSHOT_RANGES
  7
  • SNAPSHOT_TBREORG
  7
  • HEALTH_DBM_INFO
  7
  • HEALTH_DBM_HI
  7
  • HEALTH_DBM_HI_HIS
  7
  • HEALTH_DB_INFO
  7
  • HEALTH_DB_HI
  7
  • HEALTH_DB_HI_HIS
  7
  • HEALTH_TBS_INFO
  7
  • HEALTH_TBS_HI
  7
  • HEALTH_TBS_HI_HIS
  7
  • HEALTH_CONT_INFO
  7
  • HEALTH_CONT_HI
  7
  • HEALTH_CONT_HI_HIS

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

db2updv8 -d sample

Data Recovery and High Availability

Cross-platform backup and restore support

DB2 UDB supports cross-platform backup and restore operations. You can 7restore databases created on a DB2 UDB Version 8 32-bit Windows platform to 7a DB2 UDB Version 8 64-bit Windows platform, or the reverse. You can restore 7databases created on a DB2 UDB Version 8 32-bit Linux x86 platform to a DB2 7UDB Version 8 64-bit Linux x86-64 or IA64 platform, or the reverse. You can 7restore databases created on DB2 UDB Version 8 AIX, HP-UX, Linux PPC, Linux 7zSeries, or the Solaris Operating Environment platforms, in 32-bit or 64-bit, 7to DB2 UDB Version 8 AIX, HP-UX, Linux PPC, Linux zSeries, or Solaris Operating 7Environment 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 761 440 bytes

Tivoli Storage Manager

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

7
• 64-bit Solaris systems which require TSM client API Version 4.2.1.
7
• 64-bit Windows NT operating systems which require TSM client API Version 75.1.
7
• 732-bit Linux for iSeries and pSeries^(TM) which requires at minimum 7TSM client API Version 5.1.57
7
• 764-bit Linux for iSeries and pSeries which requires at minimum 7TSM client API Version 5.2.27
7
• 64-bit Linux on AMD Opteron systems which require a minimum TSM client 7API Version 5.2.0.
7
• Linux for zSeries 64-bit which requires a minimum TSM client API Version 75.2.2.

Value restrictions for the HADR local host and local service parameters

When specifying values for the high availability disaster recovery (HADR) 7local host and local service parameters (HADR_LOCAL_SVC and HADR_REMOTE_SVC) 7while preparing an update database configuration command 7, the values must be ports that are not in use for any other service. If the 7parameters are being configured using the UNIX or Linux command line, the 7values 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 7on the standby database because the containers are not available, the primary 7database 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 7administration log on the standby database when you are creating new table 7spaces.

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

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

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

The statement should read as follows:

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

HADR does not support raw logs

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

Data Warehouse Center

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 6default count limit is 1000 records. Typically, each job that you run creates 612 to 15 log records. Set the purge limit to a number that meets your needs 6by 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 UDB Load step now allows a view or a table to be used as the source 4to 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 4must be selected.

Unicode warehouse control database migration and limitations

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

When you migrate a warehouse control database from a version of the Data 7Warehouse Center that is before Version 8.2, the Data Warehouse Center Control 7Database Management tool runs the db2move command 7to move the data to a new Unicode control database. During this process, 7windows 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 7of the date in the Modified column has been 7updated. The date in the Modified column is 7displayed in the format MM/DD/YYYY and includes the time. For example, 06/17/2003 2:47:15 PM. This change in the date format ensures that sorting 7objects on the Modified column functions properly. 7This update applies to most lists of Data Warehouse Center objects that are 7shown in the Navigator and Details views, such as:

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

Defining statistical transformers in the Data Warehouse Center

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

Prerequisite for the iSeries warehouse agent

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

PTF SI13558

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

DB2 .NET Data Provider

DB2Connection.ConnectionString property

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

7

CurrentSchema

7

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

7

DB2 Connect

New security scenario

A new security scenario has been added for APPC connections:

7

Authentication

7

GSSPLUGIN 7

7

Security

7

none 7

7

Validation

7

GSS API security plugin mechanism 7

7

Corrections to diagrams

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

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

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

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

Development Center

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 UDB 7database from within the Development Center using the DB2 Universal Type 72 and Type 4 drivers. However, if you attempt to use one of these drivers 7to connect to an iSeries server, or to a DB2 UDB server that is Version 8.1 7or earlier, you will see the following error message:

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

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

GUI Tools

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

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

Default write to table output generation (Create Event Monitor)

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

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

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

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

Information Catalog Center

Configuration sample scripts

The ICCConfig.jacl and ICCConfig.properties sample scripts are provided with the Information 7Catalog Center for the Web with the DB2 Embedded Application Server. You can 7use these sample scripts to configure the Information Catalog Center for the 7Web 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 7Embedded Application Server, if your metadata contains URLs that access files 7on the server, you must map the URLs to the correct location by using aliases 7in the Web server configuration. You must also map the help and copyright 7links. If you use the DB2 Embedded Application Server, a Web server must be 7configured correctly and running for these links to work even though you don't 7need to plug in to a Web server.

Installation and Configuration Supplement

Application server for DB2

The application server for DB2 no longer supports remote administration 7or stored procedures.

The updated topics are as follows:

Enabling the application server for DB2

Enabling the database does the following:

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

Starting the application server for DB2 locally

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

Starting the application server for DB2 remotely

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

Stopping the application server for DB2 locally

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

Stopping the application server for DB2 remotely

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

Uninstalling the application server for DB2

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

DB2 Embedded Application Server enablement

The enabled database of a DB2 Embedded Application Server must be located 7in a 32-bit instance Any databases accessed from the DB2 Embedded Application 7Server 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 7CLASSPATH variable during DB2 Web Tools deployment. All dependencies, including 7those for XML parser and transformer, are now deployed with the web module 7and are expected to be loaded from the WEB-INF\lib directory according to the J2EE specification. This change affects two 7information topics:

7
• Deploying DB2 Web Tools on WebLogic application servers
7
• 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 7the Web Command Center and the Web Health Center) on BEA WebLogic 7.0. These 7tools run as Web applications on a Web server to provide access to DB2 servers 7through Web browsers.

Deploying DB2 Web Tools on other application servers

This task describes how to deploy and configure DB2 Web Tools (including 7the Web Command Center and the Web Health Center) on other application servers 7such as Tomcat 4.0 and Macromedia JRun 4.0. These tools run as Web applications 7on 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 7distributions of Linux with a 2.6 kernel. Direct I/O on block devices is an 7alternative way to specify device containers for direct disk access or for 7raw I/O. The performance of Direct I/O is equivalent to the raw character 7device method. DB2 UDB enables Direct I/O while opening the table space when 7the CREATE TABLESPACE statement specifies a block device name for the container 7path. Previously, the same performance was achieved using the raw I/O method, 7which required binding the block device to a character device using the raw utility.

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

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

Although the raw I/O method is still supported by DB2 UDB, it has been 7deprecated and support for it might be removed from future kernels.

DB2 Information Center daemon (Linux, UNIX)

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

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

These files are installed in the following locations:

7

AIX

7

/etc/db2icd 7

/var/db2/v81/db2ic.conf

7

7

HP

7

/sbin/init.d/db2icd 7

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

7

7

Solaris Operating Environment

7

/etc/init.d/db2icd 7

/var/db2/v81/db2ic.conf

7

7

Linux

7

/etc/init.d/db2icd 7

/var/db2/v81/db2ic.conf

7

7

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

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

INIT_DIR/db2icd restart

INIT_DIR/db2icd status

Response file installation error codes

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

7

3010

7

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

7

Required user accounts for installation of DB2 servers (Windows)

7

Increase quotas

7

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

7

User rights granted by the DB2 installer - Debug Programs

7

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

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

7

7

Asynchronous I/O support (Linux)

Asynchronous I/O (AIO) support is now available on Linux (2.6 and some 72.4 kernels) for raw devices and O_DIRECT file systems. AIO improves page 7cleaner 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 7that supports AIO. Users must also run the db2set DB2LINUXAIO=true command and restart DB2 UDB.

Message Reference

ADM message updates

ADM12504E

The description for message ADM12504E incorrectly implies that the instance 7name on the primary database should match the instance name on the standby 7database.

7

ADM12504E

7

Unable to establish HADR primary-standby connection because the DB2 7instance names do not match. Correct the HADR_REMOTE_INST configuration 7parameter. 7

7

It is not a HADR requirement for the instance name on the primary to match 7the instance name on the standby.

CLP message updates

DB21015E

The Command Line Processor backend process request queue or input queue 7was not created within the timeout period.

7

Explanation

7

Either the values for the DB2BQTRY and DB2BQTIME environment variables 7need to be increased or the command line processor back-end program "db2bp" 7cannot be started. The "db2bp" program must reside in the correct database 7manager install path and users must have execute permission on the file. 7 7

On UNIX platforms, ensure that the file system has enough file blocks 7and inodes.

7

7

User Response

7

Correct the error and resubmit the command. 7

7

DBI message updates

DBI1060E

Invalid package name <"pkg-name">

5

Explanation:

5

An incorrect name has been entered. The package either does not exist 5or the name has been entered incorrectly. 5

5

User Response:

5

Check to see if the name of the given package exists on the distribution 5media. If so, examine the name to see if it has been misspelled. All package 5names should be in lowercase. 5

5

SQL message additions

SQL0121N

The target name "<name>" is specified more than once for assignment 7in the same SQL statement.

7

Explanation

7

The same target name "<name>" is specified more than once as an 7 OUT or INOUT argument of a CALL statement, or in the list of columns of an 7 INSERT statement, the left hand side of assignments in the SET clause of 7an UPDATE statement, or the left hand side of the assignment statement. The 7 target name identifies a column, SQL parameter, SQL variable, or new transition 7variable. 7

Note that this error may occur when updating or inserting into 7a view where more than one column of the view is based on the same column 7of a base table.

7

The statement cannot be processed.

7

7

User Response

7

Correct the syntax of the statement so each name is specified only once. 7 7

sqlcode : -121

7

7

SQL0270N

Function not supported (Reason code = "<reason-code>")

7

Explanation

7

74 Updating a status field in the recovery history file by timestamp 7is not allowed. 7

7

User Response

7

74 Update the status field in the recovery history file by EID only. 7

7

7

Explanation

7

75 Automatic statistics collection is not supported on a multiple 7database partition system, on a system where SMP is enabled, or on a federated 7system. 7

7

User Response

7

75 Disable automatic statistics collection for this database by setting 7the database configuration parameters AUTO_STATS_PROF and AUTO_PROF_UPD to 7OFF. 7

Or change the system to one that is on a single database partition, 7where SMP is not enabled, and that is not federated.

7

7

SQL0494W

The number of result sets is greater than the number of locators.

7

Explanation

7

The number of result set locators specified on the ASSOCIATE LOCATORS 7statement is less than the number of result sets returned by the stored procedure. 7The first "n" result set locator values are returned, where "n" is the number 7of result set locator variables specified on the SQL statement. 7

The SQL 7statement is successful.

7

7

User Response

7

Increase the number of result set locator variables specified on the 7SQL statement. 7

7

sqlcode : +494

sqlstate : 01614

SQL1227N

7

Explanation:

7

7

7

Reason code 4

7

Buffer size entries of PAGE_FETCH_PAIRS must be ascending in value. 7Also, for 32-bit instances, any buffer size value in a PAGE_FETCH_PAIRS entry 7cannot be greater than the number of pages in the table or 524287, whichever 7is lower. For 64-bit instances, any buffer size value in a PAGE_FETCH_PAIRS 7entry cannot be greater than the number of pages in the table or 2147483674. 7

7

7

7

SQL1768N

Unable to start HADR. Reason code = "<reason-code>"

7

Explanation:

7

9 The database is configured to use raw logs. 7

7

User Response:

7

9 Reconfigure the database to use only file system storage for the 7log files, instead of using raw i/o (direct disk access) devices. Refer to 7the discussion of the logpath and newlogpath database configuration parameters in Administration Guide: Performance. 7

7

SQL1790W

A default table space could not be found with a page size of at least 7"<page size>".

7

Explanation

7

The procedure NNSTAT was not able to create the SYSPROC.FED_STATS table 7to keep a history of the statement that was run. The table space with sufficient 7page size (at least "<pagesize>") could not be found. 7

7

User Response

7

Ensure that a table space with a page size of at least "<pagesize>" 7exists. 7

7

sqlcode: +1790

sqlstate: 01670

SQL1791N

The specified server definition, schema, or nickname object-name does not 7exist.

7

Explanation

7

The procedure NNSTAT accepts a server definition, schema, and nickname 7as input and one or more of these objects, including object-name, could not 7be found. 7

7

User Response

7

Specify an existing server definition, schema, or nickname and resubmit 7the statement. 7

7

sqlcode: -1791

sqlstate: 42704

SQL2316W

The Runstats command string for the statistics profile has exceeded the 7maximum size. The Runstats command string will be truncated to the maximum 7size and stored in the catalog table SYSIBM.SYSTABLE.

7

Explanation

7

The maximum size of the STATISTICS_PROFILE column is 32768 bytes. If 7the size of the Runstats command string for the statistics profile is greater 7than this amount, the Runstats command string is truncated to the maximum 7size. 7

The utility continues processing.

7

7

User Response

7

Refer to the STATISTICS_PROFILE column in the catalog table SYSIBM.SYSTABLES 7to review the statistics profile. If the existing statistics profile is not 7desired, reissue the RUNSTATS utility and specify the UPDATE PROFILE or the 7UPDATE PROFILE ONLY option to modify the profile. Refer to the RUNSTATS utility 7documentation for information about the utility options. 7

7

SQL3705N

The buffer size parameter specified is not valid. The buffer size must 7be specified as 0 or be between 8 and 250000 inclusive. For multiple buffers, 7the total buffer size must not exceed 250000.

SQL20290N

The SQL statement references routine "<routine-name>" (specific name 7"<specific-name>") which cannot be run on partition "<partition-number>".

7

Explanation

7

The routine "<routine-name>" (specific name "<specific-name>") 7was called with an invalid partition number "<partition-number>". 7

7

User Response

7

Specify -1 for the partition number parameter to run the routine on 7the current partition. 7

7

sqlcode: -20290

sqlstate: 560CA

SQL22025N

An invalid input argument was specified for the reorgchk stored procedure.

7

Explanation

7

Only 'T' and 'S' are supported for the first argument of the stored 7procedure. If 'T' is specified as the first argument, the second argument 7to the stored procedure must be a fully qualified table name as <schema.table-name>. 7

7

User Response

7

Resubmit with valid input arguments. 7

7

SQL27994W

The length of a special register default column is smaller than the target 7column length. Load may truncate the values corresponding to this column 7(<column-num>).

7

Explanation

7

The clause SESSION_USER, CURRENT_USER, SYSTEM_USER, or CURRENT_SCHEMA 7has been specified for column "<column-num>", but this column was either 7defined with a length that is less than 128 bytes (SESSION_USER only) or 7the column-value inserted into this column may exceed the target length after 7codepage conversion. Load may truncate the column-value. 7

7

User Response

7

If your system standards would not allow a user ID (SESSION_USER) to 7exceed the length of the column, then this warning may be ignored. To prevent 7this warning from occurring, the length of the column must be at least 128 7bytes. If codepage conversion resulted in the growth of this register default 7column, increase the length of the column to accommodate the growth. 7

7

Query Patroller

Create Explain tables before running Query Patroller historical data 5generator

When running the historical data generator for Query Patroller, if the 5Explain tables do not already exist, the generator will create them for you. 5However, it is strongly recommended that you create the Explain tables before 5running the historical data generator. When you create the Explain tables 5be sure you create them on the same partition. Actively creating the Explain 5tables on the same partition improves the performance of the Explain facility. 5This 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 5over Time (Historical Analysis) report shows a status of Ran unsuccessfully for a query, historical data has not been generated 5for that query. Therefore, the query will not appear in any historical analysis 5reports or graphs. As documented in version 8, to determine why the query 5was unsuccessful, you can examine the qpuser.log file.

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

Abnormal shutdown of the historical data generator

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

6
• DB2 UDB stops unexpectedly
6
• Issuing a db2stop force command
6
• Issuing a killdb2 command

When the historical data generator shuts down abnormally, you must issue 6the 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 6is being run against.

Dynamic query class updates

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

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

Nested query behavior

Nested queries cannot be queued. Instead, a nested query will run immediately 6if 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 6can be queued:

6
• Queries containing static SQL with host variables
6
• Queries containing an identity value function (IDENTITY_VAL_LOCAL) or 6a 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 7to a remote desktop that is running the Query Patroller Center, the Submission 7Preferences window might appear blank. For the Submission Preferences window 7to display properly, you must use a resolution higher than 640x480.

New group support for query submissions

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

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

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

Query Patroller schedule limitations

When working with schedules in the Query Patroller Center, you can use 7the Schedule window to save schedules to a file and import them later. If 7you have a schedule that you saved using FixPak 6 or earlier, you cannot import 7the schedule using version 8.2 or later. This limitation is due to the change 7in 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 7submitted the query originally.

Creating an alias for a result table

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

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

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

Removing orphaned result table aliases

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

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

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

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

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

Quick Beginnings

Verifying that your databases are ready for migration

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

7

Prerequisites

7

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

7

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

Common Criteria certification for DB2 UDB

The authoritative information for DB2 UDB configurations that have been 7certified 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 7programming for DB2 Spatial Extender. For a description of the steps that 7the sample program takes to create a spatially-enabled database and perform 7spatial analysis on data in that database, refer to the topic titled "The 7DB2 Spatial Extender sample program". This topic is in the Information Center 7and the Spatial Extender and Geodetic Extender User's 7Guide and Reference.

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

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

7

Windows

7

7

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

7

7

Linux and UNIX

7

7

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

7

7

SQL Administrative Routines

Column expansion for snapshot UDFs

The size of some VARCHAR and CLOB columns in snapshot UDFs have changed 7to match the maximum database object name standards.

SQL Reference

CONNECT statement

When connecting to Windows Server explicitly, the TO authorization-name or the USER host-variable can be specified using the Microsoft Windows NT Security Account Manager 7(SAM)-compatible name. The qualifier must be a NetBIOS style name, which has 7a maximum length of 15 characters. For example, 'Domain\User'.

Schema used by the Explain facility

The Explain facility uses the following IDs as the schema to use in qualifying 7the explain tables to populate:

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

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

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

String representations of datetime values

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

System monitor

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 run time rollback process

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

7

Unit of work rollback

7

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

7

Savepoint rollback

7

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

7

The information provided is the start time of the rollback event, the total 7work 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 7back for the transaction or savepoint.

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

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

            Application Snapshot
7
7Application handle         = 6
7Application status         = Rollback Active
7      Start Time           = 02/20/2004 12:49:27.713720
7      Completed Work       = 1024000 bytes
7      Total Work           = 4084000 bytes
7
7            Application Snapshot
7
7Application handle         = 10
7Application status         = Rollback to Savepoint
7      Start Time           = 02/20/2004 12:49:32.832410
7      Completed Work       = 102400 bytes
7      Total Work           = 2048000 bytes

XML Extender

Configuring MQ XML UDFs with XML Extender

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

XML Extender environment variable DB2DXX_MIN_TMPFILE_SIZE

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

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