Command Reference
|
|
|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).
|
|
|Note:
|All formulas are based on statistics. The statistics and
|the results from these formulas are estimates only. You should review the
|results of REORGCHK with this information in mind.
|
|
|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.
|
|Example
|For a classic (offline) REORG TABLE like the default in DB2 Universal Database
|(UDB) Version 7, enter the following command
| db2 reorg table employee index empid allow no access indexscan
| longlobdata
|
|Note:
|The defaults are different in DB2 UDB
|Version 8.
|
|Usage notes
|DB2 UDB provides two methods of reorganizing tables: classic and inplace.
| In general, classic table reorganization is faster, but should be used only
|if your applications function without write access to tables during the reorganization.
| If your environment does not allow this restriction, although inplace reorganization
|is slower, it can occur in the background while normal data access continues.
|Classic table reorganization provides the fastest table reorganization,
|especially if you do not need to reorganize LOB or LONG data. In addition,
|indexes are rebuilt in perfect order after the table is reorganized. Read-only
|applications can access the original copy of the table except during the last
|phases of the reorganization, in which the permanent table replaces the shadow
|copy of the table and the indexes are rebuilt.
|Inplace table reorganization is slower and does not ensure perfectly ordered
|data, but it can allow applications to access the table during the reorganization.
| In addition, inplace table reorganization can be paused and resumed later
|by anyone with the appropriate authority by using the schema and table name.
|
|Updated restrictions
|The REORG utility does not support the use of nicknames.
|
|- The REORG TABLE command is not supported for declared temporary tables.
|- The REORG TABLE command cannot be used on views.
|- Reorganization of a table is not compatible with range-clustered tables,
|because the range area of the table always remains clustered.
|- An offline table reorganization does not allow the starting of an online
|backup operation of a table space in which the table resides while the reorganization
|is happening.
|- An online table reorganization of an SMS table does not allow the starting
|of an online backup operation of a table space in which the table resides
|while the reorganization is happening.
|- An online table reorganization of a DMS table does allow the starting
|of an online backup operation of a table space in which the table resides
|while the reorganization is happening. There may be lock waits of the reorganization
|operation during the truncate phase.
|- REORG TABLE cannot use an index that is based on an index extension.
|
|
|BACKUP DATABASE command
|
|Usage notes
|Note the following restrictions:
|
|- The starting of an online backup operation of a DMS table space is allowed
|when a table within the same table space is being reorganized online. There
|may be lock waits of the reorganization operation during the truncate phase.
|- The starting of an online backup operation of an SMS table space is not
|allowed when a table within the same table space is being reorganized online.
|Both operations require an exclusive lock.
Migrating Databases
Restrictions
Version 8 documentation ambiguously states that no database migration is
required if the database has been migrated to a DB2 UDB Version 8 FixPak level.
To be specific, database migration is not required between fixpaks once the
database is at a Version 8 level (Version 8.1 or 8.2 or a subsequent fixpak).
There are changes to the database directory file structure in Version 8.2
and migration is automatically performed for you when you move from Version
7 or Version 8.1 to Version 8.2. However, if you go back from Version 8.2
to Version 8.1, you must run db2demigdbd to restore
the database directory file structure. Failing to do so will result in error
SQL10004 when you try to access the database.
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(TM) 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(R) processes running against that instance.
New parameter for the db2sqljcustomize command
The db2sqljcustomize command has a new parameter.
db2sqljcustomize - DB2 SQLJ Profile Customizer command
- -storebindoptions
- Stores the value of the -bindoptions and -staticpositioned values in the serialized profile. If these values are not specified
when invoking the dbsqljbind tool, the values stored in the serialized profile
are used. When the Customizer is invoked with .grp file, the values are stored in each individual .ser file. The stored values can be viewed using db2sqljprint tool.
New parameter for the sqlj command
The sqlj command has a new parameter.
sqlj - DB2 SQLJ Translator command
- -db2optimize
- Specifies that the SQLJ translator generates code for a connection context
class that is optimized for DB2 Universal Database. This option optimizes
the code for the user defined context but not for the default context. When
you run the SQLJ translator with this option, the DB2 Universal JDBC driver file db2jcc.jar must be in the CLASSPATH for compiling the generated Java(TM) application.
Monitor and troubleshoot command (db2pd) updates
The Monitor and troubleshoot DB2 command (db2pd) retrieves information
from the DB2 UDB memory sets. The db2pd system command has been enhanced in
the following ways:
New -hadr parameter
Introduced at Version 8.2 (equivalent to Version
8.1 FixPak 7), the -hadr parameter reports
High Availability Disaster Recovery information. Descriptions of each reported
element can be found in the High availability disaster recovery section of
the System Monitor Guide and Reference
New -utilities parameter
Introduced at Version 8.2 (equivalent to Version
8.1 FixPak 7), the -utilities parameter
reports Utility information. Descriptions of each reported element can be
found in the Utilities section of the System Monitor
Guide and Reference.
New -activestatements parameter
Introduced at Version 8.2.2 (equivalent to Version
8.1 FixPak 9), the -activestatements
parameter returns information about active statements. The following information
is returned:
- AppHandl
- The application handle using the active statement, including the node
and the index.
- UOW-ID
- For the application associated with AppHandl, the unit of work identifier in which the statement became active.
- StmtID
- The statement identifier within the unit of work.
- AnchID
- The identifier for the package cache dynamic SQL hash anchor.
- StmtUID
- The identifier for the package cache dynamic SQL statement unique within
the hash anchor.
- EffISO
- The effective isolation level of the statement.
- EffLockTOut
- The effective lock timeout value for the statement.
- EffDegree
- The effective degree of parallelism of the statement.
- StartTime
- The time the statement started.
- LastRefTime
- The last time the statement was referenced by the application.
New wait option for the -locks parameter
Starting with Version 8.2.2 (equivalent to Version
8.1 FixPak 9), you can specify the wait option with the -locks parameter to return only locks with a waiting
status and locks that are being waited on.
New fields returned by the -applications parameter
Starting with Version 8.2.2 (equivalent to Version
8.1 FixPak 9), the -applications parameter
returns four new fields:
- C-AnchID
- This new field is the identifier for the package cache dynamic SQL hash
anchor of the current statement. The value for this field is 0 if no current
statement exists. The current hash anchor identifier is populated only when
the application is executing a request related to the statement, such as a
FETCH request on a cursor. Once the request processing is complete, the
values are set to 0. The value is also set to 0 for static SQL statements.
- C-StmtUID
- This new field is the package cache dynamic SQL statement unique identifier
within the hash anchor of the current statement. The value for this field
is 0 if no current statement exists. The current statement unique identifier
is populated only when the application is in the process of executing a
request related to the statement. Once the request execution is complete,
the values are set to 0. The value is also set to 0 for static SQL statements.
- L-AnchID
- This new field is the package cache dynamic SQL hash anchor identifier
of the application's last executed statement. The value for this field is
0 if no last executed statement exists, such as before any dynamic SQL statement
is executed. The value is also set to 0 for static SQL statements. The last
anchor hash identifier is populated after each request execution is complete
and survives until the completion of the next associated request .
- L-StmtUID
- This new field is the package cache dynamic SQL statement unique identifier
within the hash anchor of the application's last executed statement. The
value for this field is 0 if no last executed statement exists or if the
last statement executed was static SQL. The last statement identifier is
populated after the current request completes and survives until the completion
of the next request.
Update to the SET CLIENT command
The SET(TM) CLIENT command specifies connection
settings for the back end process.
The command parameter SYNCPOINT for this command is ignored as of Version
8. SYNCPOINT continues to be included for backward compatibility.
Update to the PRECOMPILE command
The PRECOMPILE command processes an application program source file containing
embedded SQL statements. A modified source file is produced, containing host
language calls for the SQL and, by default, a package is created in the database.
The command parameter SYNCPOINT for this command is ignored as of Version
8. SYNCPOINT is continued to be included for backward compatibility.
Update to the UPDATE HISTORY FILE command
Updates the location, device type, or comment in a history file entry.
The command parameter STATUS specifies a new status for an entry.
Previous documentation incorrectly states that the STATUS command parameter
can have a value of "I" to mark the entry as inactive. Valid values
are:
- A
- Marks the entry as active.
- E
- Marks the entry as expired.
Updates to the EXPORT and IMPORT commands
The complete "Required connection " subsection for the EXPORT and
IMPORT commands is as follows:
Required connection
Database. If implicit connect is enabled, a connection to the default database
is established. Utility access to Linux(TM), UNIX(R), or Windows(R) database servers from Linux, UNIX, or Windows clients must be a direct connection
through the engine and not through a DB2 Connect(TM) gateway or loop back.
Update to the LOAD command
The complete information for the INDEXING MODE parameter's AUTOSELECT value
is as follows:
INDEXING MODE
- AUTOSELECT
- The load utility will automatically decide between REBUILD or INCREMENTAL
mode. The decision is based on the amount of data being loaded and the depth
of the index tree. Information relating to the depth of the index tree is
stored in the index object. RUNSTATS is not required to populate this information.
AUTOSELECT is the default indexing mode.
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:
Table 30. Valid file type modifiers for load: All file formats
Modifier |
Description |
generatedoverride |
This modifier instructs the load utility to accept user-supplied data
for all generated columns in the table (contrary to the normal rules for
these types of columns). This is useful when migrating data from another database
system, or when loading a table from data that was recovered using the RECOVER
DROPPED TABLE option on the ROLLFORWARD DATABASE command. When this modifier
is used, any rows with no data or NULL data for a non-nullable generated column
will be rejected (SQL3116W).
Note:
When this modifier is used,
the table will be placed in CHECK PENDING state. To take the table out of
CHECK PENDING state without verifying the user-supplied values, issue the
following command after the load operation:
SET INTEGRITY FOR < table-name > GENERATED COLUMN
IMMEDIATE UNCHECKED To take the table out of CHECK PENDING state and
force verification of the user-supplied values, issue the following command
after the load operation:
SET INTEGRITY FOR < table-name > IMMEDIATE CHECKED.
This modifier cannot be used with either the generatedmissing or the generatedignore modifier. |
usedefaults |
If a source column for a target table column has been specified, but
it contains no data for one or more row instances, default values are loaded.
Examples of missing data are:
- For DEL files: two adjacent column delimiters (",,") or
two adjacent column delimiters separated by an arbitrary number of spaces
(", ,") are specified for a column value.
- For DEL/ASC/WSF files: A row that does not have enough columns,
or is not long enough for the original specification.
Note:
For ASC
files, NULL column values are not considered explicitly missing, and a default
will not be substituted for NULL column values. NULL column values are represented
by all space characters for numeric, date, time, and /timestamp columns, or
by using the NULL INDICATOR for a column of any type to indicate the column
is NULL. Without this option, if a source column contains no data for a row instance,
one of the following occurs:
- For DEL/ASC/WSF files: If the column is nullable, a NULL is loaded. If
the column is not nullable, the utility rejects the row.
|
File type modifiers for the import utility
The description for the "usedefaults" and "codepage=x" modifiers
have been updated as follows:
Table 31. Valid file type modifiers for import: All file formats
Modifier |
Description |
usedefaults |
If a source column for a target table column has been specified, but
it contains no data for one or more row instances, default values are loaded.
Examples of missing data are:
- For DEL files: two adjacent column delimiters (",,") or
two adjacent column delimiters separated by an arbitrary number of spaces
(", ,") are specified for a column value.
- For DEL/ASC/WSF files: A row that does not have enough columns,
or is not long enough for the original specification.
Note:
For ASC
files, NULL column values are not considered explicitly missing, and a default
will not be substituted for NULL column values. NULL column values are represented
by all space characters for numeric, date, time, and /timestamp columns, or
by using the NULL INDICATOR for a column of any type to indicate the column
is NULL. Without this option, if a source column contains no data for a row instance,
one of the following occurs:
- For DEL/ASC/WSF files: If the column is nullable, a NULL is loaded. If
the column is not nullable, the utility rejects the row.
|
Table 32. Valid file type modifiers for import: ASCII file formats (ASC/DEL)
Modifier |
Description |
codepage=x |
x is an ASCII character string. The value is
interpreted as the code page of the data in the output data set. Converts
character data from this code page to the application code page during the
import operation.
The following rules apply:
- For pure DBCS (graphic) mixed DBCS, and EUC, delimiters are restricted
to the range of x00 to x3F, inclusive.
- nullindchar must specify symbols included in the standard ASCII
set between code points x20 and x7F, inclusive. This refers to ASCII symbols
and code points.
Notes:
- The codepage modifier cannot be used with the lobsinfile modifier.
- If data expansion occurs when the code page is converted from the application
code page to the database code page, the data might be truncated and loss
of data can occur.
|
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(R) Windows NT(R) 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
The UPDATE HISTORY FILE command updates the location, device type, comment,
or status in a history file entry.
Updated command syntax
>>-UPDATE HISTORY--+-FOR--object-part-+--WITH------------------->
'-EID--eid---------'
>--+-LOCATION--new-location--DEVICE TYPE--new-device-type-+----><
+-COMMENT--new-comment---------------------------------+
'-STATUS--new-status-----------------------------------'
Updated command parameters
- FOR object-part
- Specifies the identifier for the history entry to be updated. It is
a timestamp with an optional sequence number from 001 to 999.
Note:
Cannot be used to update entry status. To update
the entry status, specify an EID instead.
- STATUS new-status
- Specifies a new status for an entry. Only backup entries can have their
status updated. Valid values are:
- A
- Active. Most entries are active.
- I
- Inactive. Backup images that are no longer on the active log chain
become inactive.
- E
- Expired. Backup images that are no longer required because there are
more than NUM_DB_BACKUPS active images are flagged as expired.
- D
- Backup images that are no longer available for recovery should be marked
as having been deleted.
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
- 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
Required connection
Database. This command automatically establishes a connection to the specified
database.
Command syntax
>>-db2updv8-- -d--database-name--------------------------------->
>--+----------------------------+--+-----+---------------------><
'- -u--userid-- -p--password-' '- -h-'
Command parameters
- -d database-name
- Specifies the name of the database to be updated.
- -u userid
- Specifies the user ID.
- -p password
- Specifies the password for the user.
- -h
- Displays help information. When this option is specified, all other
options are ignored, and only the help information is displayed.
Example
After installing the current level (a FixPak or a new version), update
the system catalog in the sample database by issuing the following command:
db2updv8 -d sample
Usage notes
- This command can be used only on a database running DB2 Universal Database Version 8.1.2 or later. If the command is issued more than once, no
errors are reported and each of the catalog updates is applied only once.
- To enable the new built-in functions, all applications must disconnect
from the database and the database must be deactivated if it has been activated.
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.
Authorization
You must have access to the DIAGPATH directory.
Command syntax
>>-db2xprt--+----------+--+----+--+----+--infile--+---------+--><
+-/p--path-+ '-/m-' '-/n-' '-outfile-'
'-/v-------'
Command parameters
- /p path
- A semicolon (;) separated path that points to the location
or locations where the binary files and PDB files are located.
- /v
- Displays version information.
- /m
- Formats a memory dump along with the rest of the trap file.
- /n
- Format data without regard to line number information.
- infile
- Specifies the input file.
- outfile
- Specifies the output file.
[ Top of Page |Previous Page | Next Page | Contents ]