db2cat - System catalog analysis command

Analyzes the contents of packed descriptors. Given a database name and other qualifying information, this command queries the system catalogs for information and format the results. It must be issued on the server.

Authorization

None

Required Connection

None

Command syntax

Command parameters

-d dbname

dbname is the name of the database for which the command queries the system catalogs.

-h

Displays usage information.

-l

Turns on case sensitivity for the object name.

-n name

Specifies the name of the object.

-o outfile

Specifies the name of the output file.

-p descriptor

Specifies the name of the packed descriptor (pd) to display where descriptor is one of the following values:

check: Display table check constraints packed descriptor.
controls: Displays packed descriptors for all enabled permissions and masks for a given table.
rel: Display referential integrity constraint packed descriptor.
table: Display table packed descriptor. This includes the inline length if at least one exists for the table.
summary: Display summary table packed descriptor.
syscontrols: Displays the packed descriptor for a given mask or permission.
trig: Display table trigger packed descriptor.
view: Display view packed descriptor.
variable: Display a global variable packed descriptor.
remote: Display remote nonrelational data sources packed descriptor.
ast: Display materialized query table packed descriptor.
routine: Display routine packed descriptor.
sysplan: Display package packed descriptor.
datatype: Display a structured type packed descriptor.
sequence: Display sequence packed descriptor.
esri: Display key transformation thread and index extension packed descriptor.
event: Display event monitor packed descriptor.
server: Display server packed descriptor.
auth: Display privileges held by this grantee on this object.
workload: Helps dump workload. See usage notes below.
threshold: Helps dump threshold packed descriptors. See usage notes below.
systenants: Display contents of systenants catalog.
sysboot: Display sysboot contents.
model: display sysibm.sysaimodels packed descriptors

-vi versionID

Specifies the version ID of the package packed descriptor or version of the model-packed descriptor. -vi is only valid when -p sysplan or -p model is specified. If versionID is omitted, the default is the empty string (for -p model, the empty string retrieves all model versions).

-s schema

Specifies the name of the object schema. See usage notes below.

-t

Displays terminal output.

-tn tenant

Specifies the name of the tenant that you want to use for this invocation. If not specified, the default SYSTEM tenant is assumed.

-v

Validates packed descriptor. This parameter is only valid for table-packed descriptors.

-x

Validates table space extent size in catalogs (does not require a table name).

-cb

Cleans orphan rows from SYSCAT.BUFFERPOOLDBPARTITIONS (does not require a table name).

-ch

Find all orphan rows.

-cl

Remove all orphan rows.

-cfd

Find incorrect collation ID.

Examples

Example 1

The following command prints the packed descriptor information for the table splt_tb1 with schema raguk from database testdb to terminal:

 db2cat -d testdb -s raguk -t -n splt_tb1

Example 2

The following commands use the percent sign (%) along with -n to dump all packed descriptors:

db2cat -d testdb -p workload -n %

db2cat -d testdb -p threshold -n %

Example 3

The following commands use workload or threshold with -n to dump the packed descriptor for a specific workload or threshold:

db2cat -d testdb -p workload -n MYWORKLOAD

db2cat -d testdb -p threshold -n MYTHRESHOLD

Note:

db2cat is the recommended command for finding and removing orphan rows from your database.

To find orphan rows in the sample database, run the following command:
```
db2cat -d sample -ch
```
To remove orphan rows from the sample database, run the following command:
```
db2cat -d sample -cl
```

Note: For Db2 12.1 fix pack 2 and later, db2cat utility can identify and fix incorrect collation ID.

To find incorrect collation ID, run the following command:

db2cat -d <dbname> -cfd IDENTITY|UNIQUE819

To fix incorrect collation ID, contact IBM support for instructions.

Usage notes

The options -d and -n are mandatory when running the db2cat command.
Table name and table schema may be supplied in LIKE predicate form, which allows percent sign (%) and underscore (_) to be used as pattern matching characters to select multiple sources with one invocation.
Prompting occurs for all fields that are not supplied or are incompletely specified (except for the -h and -l options).
If -o is specified without a file name, and -t is not specified, you are prompted for a file name (the default name is db2cat.out).
If neither -o nor -t is specified, you are prompted for a file name (the default is terminal output).
If -o and -t are both specified, the output is directed to the terminal.
Providing a workload name or threshold name with -n will dump the packed descriptor for the output.
The percent sign (%) should be used with -n to dump all the packed descriptors.