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