Retrieval of System Tables

#### Search Results

Search

# Retrieval of System Tables

Important
Before you use the Retrieval of System Tables function, refer to LISTSQL and Explain Functions in the section Special Requirements for Natural Tools for Db2 in Installing Natural for Db2 on z/OS.

The Db2 system tables provide information on the contents of your Db2 system. The Retrieval of System Tables function enables you to:

- display information on Db2 objects without coding SQL queries;

- easily access related objects, such as indexes of a table.

The Db2 objects supported by the Retrieval of System Tables function are database, tablespace, table, index, column, plan, check constraints, statistic tables, package, and DBRM (database request module), as well as access rights to and relationships between these objects.

Db2 objects are presented in one of the following two ways:

- As selection lists, where all objects are of the same type, and where commands can be issued to display related objects.

- You can list databases, tables, plans, and packages by name. From the database listings, you can invoke listings of the tablespaces or tables of a database. From the table listing, you can invoke listings of the columns and indexes of a table. From the plan listing, you can invoke listings of the DBRMs of a plan, of the package list of a plan, of the tables and indexes used by a plan, and of the systems which are enabled or disabled for a plan. From the package listing, you can invoke listings of the tables and indexes used in a package and of the systems which are enabled or disabled for a package. From the database, table, plan, or package listings, you can also investigate who is authorized to access a Db2 object. In addition, the User Authorization menu enables you to list all existing access rights by user ID.

- As reports, which merely contain information on different types of Db2 objects, and where only browse commands can be issued.

The most important browse commands can also be issued via PF keys; see Editing within the Natural Tools for Db2.

This section covers the following topics:

- Invoking the Retrieval of System Tables Function

- List Databases

- List Tablespaces

- List Plans

- Commands Allowed on Plans

- List Packages

- List Tables

- User Authorizations

- List Statistic Tables

## Invoking the Retrieval of System Tables Function

To invoke the Retrieval of System Tables function

- On the Natural Tools for Db2 Main Menu, enter function code `R`.

  The Retrieval of System Tables screen is displayed:

        16:31:56              ***** NATURAL TOOLS FOR DB2 *****             2006-05-25
                                  - Retrieval of System Tables -

                             Code  Function            Parameter

                              D    List Databases      Database
                              K    List Packages       Collection, Name
                              P    List Plans          Plan
                              T    List Tables         Tbreator, Tbname
                              U    User Authorizations
                              S    Statistic Tables
                              ?    Help
                              .    Exit

                      Code .. _    Database Name ....... ________________________
                                   Package Collection .. _____________________________
                                   Package Name ........ _____________________________
                                   Plan Name ........... ________________________
                                   Table Creator ....... _____________________________
                                   Table Name .......... _____________________________

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help  Setup Exit                                                  Canc

  With PF2 (Setup) the maximum length of one column and the number of fixed characters when scrolling left may be specified. The default values for both parameters may be changed in the `CONFIG` subprogram in library `SYSDB2.`

  When a column value is longer than the maximum length, it will be truncated and marked with a greater than sign (>) in the case of strings truncated at the right end or a less than sign (<) in the case of numbers truncated at the left end.

  Note, that for further commands on a line, for example, the line command `I`, only the visible value can be taken as input. This means that commands on lines will fail, when values for further processing are truncated.

        16:31:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
                                  - Retrieval of System Tables -

                             Code  Function            Parameter
                                            +------Retrieval of System Tables------+
                              D    List Dat !                                      !
                              K    List Pac ! Maximum length of columns ... ____8  !
                              P    List Pla ! Number of fixed characters .. ____0  !
                              T    List Tab !                                      !
                              U    User Aut !                                      !
                              S    Statisti +--------------------------------------+
                              ?    Help
                              .    Exit

                      Code .. _    Database Name ....... ________________________
                                   Package Collection .. _____________________________
                                   Package Name ........ _____________________________
                                   Plan Name ........... ________________________
                                   Table Creator ....... _____________________________
                                   Table Name .......... _____________________________

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help  Setup Exit                                                  Canc

  The following functions are available:

  Code | Description
  --- | ---
  `D` | Lists databases defined in the Db2 catalog.
  `K` | Lists packages defined in the Db2 catalog.
  `P` | Lists plans defined in the Db2 catalog.
  `S` | Statistic tables.
  `T` | Lists tables defined in the Db2 catalog.
  `U` | Provides information on which user(s) can access which Db2 objects.

  The following parameters must be specified as selection criteria:

  Parameter | Description
  --- | ---
  `Database Name` | The name of the database to be listed. Asterisk notation (*) for range specification is possible. The `Database Name` parameter is relevant to the List Databases function only.
  `Package Collection` | The collection of the package to be listed. Asterisk notation (*) for range specification is possible. The `Package Collection` parameter is relevant to the List Packages function only.
  `Package Name` | The name of the package to be listed. Asterisk notation (*) for range specification is possible. The `Package Name` parameter is relevant to the List Packages function only.
  `Plan Name` | The name of the plan to be listed. Asterisk notation (*) for range specification is possible. The `Plan Name` parameter is relevant to the List Plans function only.
  `Table Creator` | The name of the creator of the table(s) to be listed. Asterisk notation (*) for range specification is possible. The `Table Creator` parameter is relevant to the List Tables function only.
  `Table Name` | The name of the table to be listed. Asterisk notation (*) for range specification is possible. The `Table Name` parameter is relevant to the List Tables function only.

## List Databases

To invoke the List Databases function

1. On the Retrieval of System Tables screen, enter function code `D`.

2. Specify the name of the database(s) to be listed.

   - If a value followed by an asterisk is specified, all databases defined in the Db2 catalog whose names begin with this value are listed.

   - If asterisk notation is specified only, all databases defined in the Db2 catalog are listed.

         16:32:24              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
          DATABASES *                               S 01     Row 0 of 25 Columns 001 059
          ====>                                                        Scroll ===>  PAGE
             DATABASE CREATOR     STOGROUP BPOOL     DBID CREATEDBY ROSHARE TIMESTAMP GR
          ** ******************************* top of data *******************************
          __ DEMO     DEFAULT     SYSDEFLT BP0        269 DEFAULT           0001-01-0>
          __ DEMODB   SAG2        SYSDEFLT BP0        273 SAG2              0001-01-0>D8
          __ DEVELOP  SAG         DEVELOP  BP0        260 SAG               0001-01-0>DB
          __ ECHDB01  SAG2        SYSDEFLT BP0        272 SAG2              0001-01-0>
          __ EFGDB    SAG         SYSDEFLT BP0        263 SAG               0001-01-0>
          __ HBUTST   SAG2        SYSDEFLT BP0        275 SAG2              0001-01-0>
          __ PLANTAB  SAG2        SYSDEFLT BP0        270 SAG2              0001-01-0>
          __ Predict  SAG2        SYSDEFLT BP0        262 SAG2              0001-01-0>
          __ QA       SAG2        SYSDEFLT BP0        265 SAG2              0001-01-0>
          __ SAGDB04  SYSIBM      SYSDEFLT BP0          4 SYSIBM            0001-01-0>
          __ SAGDB06  SYSIBM                            6 SYSIBM            0001-01-0>
          __ SAGDB07  SAG1        SYSDEFLT BP0          7 SAG1              0001-01-0>
          __ SAGDDF   SAG1        SYSDEFLT BP0        257 SAG1              0001-01-0>
          __ SAGRLST  SAG1        SYSDEFLT BP0        256 SAG1              0001-01-0>
          __ SAG8D22A SAG1        SAG8G220 BP0        258 SAG1              0001-01-0>
          __ SAG8D22P SAG1        SAG8G220 BP0        259 SAG1              0001-01-0>

         Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
               Help        Exit        Rfind        -     +           <      >   Canc

   The following line commands are available on the database listing screen. Line commands are entered in front of the desired database(s):

   Command | Description
   --- | ---
   `I` | Displays information on a database.
   `S` | Selects a database to be used with main commands (see below).
   `U` | Unselects a database.
   `AU` | Displays information on access rights to a database.
   `TB` | Displays all tables defined in a database.
   `TS` | Displays all tablespaces defined in a database.

   The listings of tables or tablespaces displayed as a result of the `TB` or `TS` command can be used for further processing, whereas the contents of the screens displayed as a result of the `AU` or `I` command are for information purposes only.

   A list of all line commands available with the List Database function can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed databases.

   The commands `AU`, `TB`, and `TS` can also be used as main commands. Main commands are entered in the command line of the database list screen and apply to all databases previously selected with the line command `S`.

   A further main command is the `INFO` command, which is the equivalent of the `I` line command, but displays information on all previously selected databases. Instead of being displayed, all information resulting from the `I` or `INFO` commands can also be marked for printing. Even if already displayed, information can be printed by issuing the `PRINT` command.

         16:32:24              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
          DATABASES *                               S 01     Row 0 of 25 Columns 001 059
          ====>                                                        Scroll ===>  PAGE
             DATABASE CREATOR     STOGROUP BPOOL     DBID CREATEDBY ROSHARE TIMESTAMP GR
          ** **** +---------------------------------------------------------+ **********
          I_ DEMO !                                                         ! 01-01-0>
          __ DEMO !                 Select what to display                  ! 01-01-0>D8
          __ DEVE !                                                         ! 01-01-0>DB
          __ ECHD !                                                         ! 01-01-0>
          __ EFGD !                  _ authorizations for database          ! 01-01-0>
          __ HBUT !                  _ tablespaces in database              ! 01-01-0>
          __ PLAN !                  _ tables      in database              ! 01-01-0>
          __ PRED !                                                         ! 01-01-0>
          __ QA   !                                                         ! 01-01-0>
          __ SAGD !                                                         ! 01-01-0>
          __ SAGD !                 Mark  _ to print output                 ! 01-01-0>
          __ SAGD !                                                         ! 01-01-0>
          __ SAGD +---------------------------------------------------------+ 01-01-0>
          __ SAGRLST  SAG1        SYSDEFLT BP0        256  SAG1             0001-01-0>
          __ SAG8D22A SAG1        SAG8G220 BP0        258  SAG1             0001-01-0>
          __ SAG8D22P SAG1        SAG8G220 BP0        259  SAG1             0001-01-0>

         Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
               Help        Exit        Rfind        -     +            <     >   Canc

   A list of all main commands available with the List Database function can be invoked as a window by entering the help character, that is, a question mark (?), in the command line of the database list screen.

## List Tablespaces

The function to list tablespaces is not part of the Retrieval of System Tables main menu.

To list tablespaces

- Issue the "TS" command on the database listing screen only.

  A tablespace listing screen is displayed, for example:

        16:35:07              ***** NATURAL TOOLS FOR DB2 *****             2006-05-25
         TABLESPACES IN DATABASE DB2DEMO           S 02      Row 0 of 2 Columns 032 075
         ====>                                                       Scroll ===>  PAGE
            DATABASE NAME        CREATOR  BPOOL    PGSIZE PARTITIONS NTABLES SEGSIZE LO
         ** ******************************* top of data *******************************
         __ DB2DEMO  AUTOMOBI    SAG      BP0           4          0       1       0 A
         __ DB2DEMO  EMPLOYEE    SAG      BP0           4          0       1       0 A
         ** ***************************** bottom of data ******************************

  The following line commands are available on the tablespace listing screen. Line commands are entered in front of the desired tablespace(s):

  Command | Description
  --- | ---
  `I` | Displays information on a tablespace.
  `S` | Selects a tablespace to be used with main commands.
  `U` | Unselects a tablespace.
  `PT` | Displays all partitions of a tablespace.
  `TB` | Displays all tables defined in a tablespace.

  The listings of tables displayed as a result of the `TB` command can be used for further processing, whereas the listings resulting from the `I` and `PT` commands are for information purposes only.

  A list of all line commands available on the tablespace listing screen can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed tablespaces.

  The commands `PT` and `TB` can also be used as a main commands entered on the command line of the tablespace listing screen. Main commands apply to all tablespaces previously selected with the line command `S`.

  A further main command is the `INFO` command, which is the equivalent of the `I` line command, but displays information on all previously selected tablespaces. Instead of being displayed, all information resulting from the `I` or `INFO` commands can also be marked for printing. Even if already displayed, information can be printed by issuing the `PRINT` command.

        16:35:07              ***** NATURAL TOOLS FOR DB2 *****             2006-05-25
         TABLESPACES IN DATABASE DB2DEMO           S 02      Row 0 of 2 Columns 032 075
         ====>                                                        Scroll ===>  PAGE
            DATABASE NAME        CREATOR  BPOOL    PGSIZE PARTITIONS NTABLES SEGSIZE LO
         ** **** +---------------------------------------------------------+ **********
         __ DB2D !                                                         !       0 A
         __ DB2D !                 Select what to display                  !       0 A
         ** **** !                                                         ! **********
                 !                                                         !
                 !                                                         !
                 !                  _ partitions of tablespace             !
                 !                  _ tables     in tablespace             !
                 !                                                         !
                 !                                                         !
                 !                                                         !
                 !                 Mark  _ to print output                 !
                 !                                                         !
                 +---------------------------------------------------------+

        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help        Exit        Rfind        -     +           <     >    Canc

  A list of all main commands available on the tablespace listing screen can be invoked as a window by entering the help character, that is, a question mark (?), in the command line of the screen.

## List Plans

To invoke the List Plans function

- On the Retrieval of System Tables screen, enter function code `P`.

  The name of the plan(s) to be listed must be specified.

  - If a value followed by an asterisk is specified, all plans defined in the Db2 catalog whose names begin with this value are listed.

  - If asterisk notation is specified only, all plans defined in the Db2 catalog are listed.

  Press Enter.

        16:37:59              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
         PLAN *                                    S 01     Row 0 of 80 Columns 023 075
         ====>                                                        Scroll ===>  PAGE
            PLAN     CREATOR     VALIDATE ISO ACQUIRE REL VALID OPER EXPLAIN   PLSIZE
         ** ******************************* top of data *******************************
         __ CAFPLAN  SAG3        R        S   U       C   Y     Y    N           2472
         __ SAGEDCL  SAG1        R        S   U       C   Y     Y    N           1992
         __ SAGESPCS SAG1        R        S   U       C   Y     Y    N           1992
         __ SAGESPRR SAG1        R        R   U       C   Y     Y    N           1992
         __ SAGTIA22 SAG1        R        S   U       C   Y     Y    N           1992
         __ SAG8BH22 SAG1        R        S   U       C   Y     Y    N           2296
         __ SAG8CC22 SAG1        R        S   U       C   Y     Y    N           4376
         __ SAG8IC22 SAG1        R        S   U       C   Y     Y    N           4264
         __ SAG8SC22 SAG1        R        S   U       C   Y     Y    N           2296
         __ SAGPLA   SAG         R        S   U       C   Y     Y    N           2648
         __ TREPH01  SAG4        B        S   U       C   A     Y    N           2168
         __ TREPLANC SAG2        R        S   U       C   N     Y    N           4560
         __ TREPLANG SAG2        R        S   U       C   N     Y    N           8976
         __ TREPLANO SAG2        R        S   U       C   N     Y    N           8976
         __ TREPLANT SAG2        R        S   U       C   Y     Y    N           2472
         __ TREPLAN1 SAG2        R        S   U       C   N     Y    N           3248

        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help        Exit        Rfind        -     +           <     >    Canc

## Commands Allowed on Plans

The following line commands are available on the plan listing screen. Line commands are entered in front of the desired plan(s):

Command | Description
--- | ---
`I` | Displays information on a plan.
`S` | Selects a plan to be used with main commands.
`U` | Unselects a plan.
`AU` | Displays information on access rights to a plan.
`DR` | Displays all DBRMs contained in a plan.
`IX` | Displays all indexes used by a plan.
`PK` | Displays the package list of a plan.
`SY` | Displays the systems enabled or disabled for a plan.
`TB` | Displays tables used in a plan.

The listing displayed as a result of the `DR`, `IX`, `PK`, or `TB` command can be used for further processing, whereas the contents of the screens displayed as a result of the `I`, `AU`, or `SY` command are for information purposes only.

A list of all line commands available with the List Plans function can be invoked as a window by entering the help character "?" in front of any of the listed plans.

The commands `AU`, `DR`, `IX`, `PK`, `SY`, and `TB` can also be used as main commands, which are entered on the command line of the plan listing screen and apply to all plans previously selected with the line command `S`.

The `INFO` main command, which is the equivalent of the `I` line command, displays information on the DBRMs and their SQL statements contained in the plans previously selected. As with the List Database function, information resulting from the `I` or `INFO` commands can be printed, too.

      16:37:59              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       PLAN *                                    S 01     Row 0 of 80 Columns 023 075
       ====>                                                        Scroll ===>  PAGE
          PLAN     CREATOR     VALIDATE ISO ACQUIRE REL VALID OPER EXPLAIN   PLSIZE
       ** **** +---------------------------------------------------------+ **********
       I_ CAFP !                                                         !     2472
       __ SAGE !                 Select what to display                  !     1992
       __ SAGE !                                                         !     1992
       __ SAGE !              _ DBRMs of plan                            !     1992
       __ SAGT !              _ package list of plan                     !     1992
       __ SAG8 !              _ systems enabled or disabled for plan     !     2296
       __ SAG8 !              _ tables  referenced in plan               !     4376
       __ SAG8 !              _ indexes used in plan                     !     4264
       __ SAG8 !              _ authorizations for plan                  !     2296
       __ SAGP !                                                         !     2648
       __ TREP !                 Mark  _ to print output                 !     2168
       __ TREP !                                                         !     4560
       __ TREP +---------------------------------------------------------+     8976
       __ TREPLANO SAG2        R        S   U       C   N     Y    N           8976
       __ TREPLANT SAG2        R        S   U       C   Y     Y    N           2472
       __ TREPLAN1 SAG2        R        S   U       C   N     Y    N           3248

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

A list of all main commands available with the List Plans function can be invoked as a window by entering the help character, that is, a question mark (?), in the command line of the plan list screen.

### DBRMs of Plan

If you issue the `DR` command on the plan listing screen, a list of all DBRMs bound into the selected plan(s) is displayed.

      16:40:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       DBRMS OF PLAN SAGTEST                     S 02      Row 0 of 3 Columns 033 075
       ====>                                                        Scroll ===>  PAGE
          PLAN     DBRM     TIMESTAMP    CREATOR  TIME     DATE     PDS NAME QUOTE CO
       ** ******************************* top of data *******************************
       __ SAGTEST  TEST1    148C251A1>   SAG      16:24:10 07-10-05 DB2.V42.>N     N
       __ SAGTEST  TEST2    148C251A1>   SAG      16:24:42 07-10-05 DB2.V42.>N     N
       __ SAGTEST  TEST3    148C251A1>   SAG      16:25:15 07-10-05 DB2.V42.>N     N
       ** ***************************** bottom of data ******************************

#### Commands Allowed on DBRMs

The following line commands are available on the DBRM listing screen. Line commands are entered in front of the desired DBRM(s):

Command | Description
--- | ---
`I` | Displays information on a DBRM.
`S` | Selects a DBRM to be used with main commands.
`U` | Unselects a DBRM.

A list of all line commands available on the DBRM listing screen can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed DBRMs.

The only main command that applies to DBRMs is the `INFO` command, which is the equivalent of the `I` line command, but displays information on all previously selected DBRMs. Instead of being displayed, all information resulting from the `I` or `INFO` commands can also be marked for printing. Even if already displayed, information can be printed by issuing the `PRINT` command.

      16:40:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       DBRMS OF PLAN SAGTEST                     S 02      Row 0 of 3 Columns 033 075
       ====>                                                        Scroll ===>  PAGE
          PLAN     DBRM     TIMESTAMP    CREATOR  TIME     DATE     PDS NAME QUOTE CO
       ** **** +---------------------------------------------------------+***********
       I_ SAGT !                                                         ! .>N     N
       __ SAGT !                 Select what to display                  ! .>N     N
       __ SAGT !                                                         ! .>N     N
       ** **** !                                                         !***********
               !                                                         !
               !                  _ Plans referencing DBRM               !
               !                  _ SQL statements of DBRM               !
               !                                                         !
               !                                                         !
               !                                                         !
               !                 Mark  _ to print output                 !
               !                                                         !
               +---------------------------------------------------------+

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

### Indexes Used in Plan

If you issue the `IX` command on either the plan listing screen or the table listing screen, a list of all indexes used in the selected plan(s) or table(s) is displayed.

      16:40:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       INDEXES OF PLAN SAGTEST                   S 02      Row 0 of 3 Columns 033 075
       ====>                                                        Scroll ===>  PAGE
          CREATOR  INDEX NAME    CREATOR  TABLE NAME COLCNT UNIQ CLSTRNG CLSTRD -RATI
       ** ******************************* top of data *******************************
       __ SAGCRE   XDEPT1        SAGCRE   DEPT            1 P    N       Y         10
       __ SAGCRE   XEMP1         SAGCRE   EMP             1 P    Y       Y         10
       __ SAGCRE   XEMP2         SAGCRE   EMP             1 D    N       N          4
       ** ***************************** bottom of data ******************************

#### Commands Allowed on Indexes

The following line commands are available on the index listing screen. Line commands are entered in front of the desired index(es):

Command | Description
--- | ---
`I` | Displays information on an index.
`S` | Selects an index to be used with main commands.
`U` | Unselects an index.
`CO` | Displays all columns of an index.
`PT` | Displays the partitions of an index.

The listings of columns displayed as a result of the `CO` or `PT` command cannot be used for further processing. Like the display resulting from the `I` command, they are for information purposes only.

A list of all line commands available on the index listing screen can be invoked as a window by entering the help character "?" in front of any of the listed indexes.

The commands `CO` and `PT` can be used as main commands, too, and entered in the command line of the index listing screen. If so, all columns of all indexes previously selected with the line command `S` are displayed.

A further main command is the `INFO` command, which is the equivalent of the line command `I`, but displays information on all previously selected indexes. Instead of being displayed, all information resulting from the `I` or `INFO` commands can also be marked for printing. Even if already displayed, information can be printed by issuing the `PRINT` command.

      16:40:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       INDEXES OF PLAN SAGTEST                   S 02      Row 0 of 3 Columns 033 075
       ====>                                                        Scroll ===>  PAGE
          CREATOR  INDEX NAME    CREATOR  TABLE NAME COLCNT UNIQ CLSTRNG CLSTRD -RATI
       ** **** +---------------------------------------------------------+ **********
       I_ SAGC !                                                         !         10
       __ SAGC !                 Select what to display                  !         10
       __ SAGC !                                                         !          4
       ** **** !                                                         ! **********
               !                  _ columns  of index                    !
               !                  _ portions of index                    !
               !                  _ plans using index                    !
               !                  _ packages using index                 !
               !                                                         !
               !                                                         !
               !                 Mark  _ to print output                 !
               !                                                         !
               +---------------------------------------------------------+

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

A list of all main commands available on the index listing screen can be invoked as a window by entering the help character "?" in the command line of the screen.

### Package List of Plan

If you issue the `PK` command on the plan listing screen, a list of all entries in the package list of the selected plan(s) is displayed.

      16:40:56              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       PACKAGE LIST FOR PLAN SAGTEST             S 02      Row 0 of 3 Columns 033 075
       ====>                                                        Scroll ===>  PAGE
          PLANNAME LOCATION COLLID      NAME     SEQNO  TIMESTAMP IBM
       ** *********************** top of data ************************
       __ SAGTEST           SAGCOLLE>   *            1  2007-10-0>N
       __ SAGTEST           SAG_STAT>   *            2  2007-10-0>N
       ** ***************************** bottom of data ***************

#### Commands Allowed on Package List Entries

The following line commands are available on the package list screen. Line commands are entered in front of the desired package list entry:

Command | Description
--- | ---
`I` | Displays information on a package list entry.
`S` | Selects a package list entry to be used with main commands.
`U` | Unselects a package list entry.
`PK` | Displays all packages of a package list entry.

The listing of packages as a result of the `PK` command can be used for further processing, whereas the display resulting from the `I` command is for information purposes only.

A list of all line commands available with a package list can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed entries.

The command `PK` can also be used as main command, which is entered in the command line of the above screen and applies to all package list entries previously selected with the line command `S`.

## List Packages

To invoke the List Packages function

- On the Retrieval of System Tables screen, enter function code `K`.

  The collection and name of the package(s) to be listed can be specified.

  If a value followed by an asterisk is specified, all packages defined in the Db2 catalog whose collections/names begin with this value are listed.

  If asterisk notation is specified only, all packages defined in the Db2 catalog are listed.

  Press Enter.

        11:06:11              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
         PACKAGE *.*                               S 01    Row 34 of 65 Columns 041 075
         ====>                                                        Scroll ===>  PAGE
            COLLID   NAME        CONTOKEN CONTOKEN (HEX) OWNER    CREATOR  QUALIFIER
         __ SAGQCATV SAGQVPLN    ?  l?F   148C409316C673>SAG      SAG      SAG
         __ SAGQCATV SAGQVPPA    ?k  ? ?? 149270680F77E0>SAG      SAG      SAG
         __ SAGQCATV SAGQVRAS    ?   ??=? 148C409B09097E>SAG      SAG      SAG
         __ SAGQCATV SAGQVREL    ?   ??y0 148C409C06DFA8>SAG      SAG      SAG
         __ SAGQCATV SAGQVREV    ? ? ?v?  148CDFAD16A51F>SAG      SAG      SAG
         __ SAGQCATV SAGQVRIL    ?  s ?B  148C40A20329C2>SAG      SAG      SAG
         __ SAGQCATV SAGQVROO    ? ?  A y 148CDFAF03C18E>SAG      SAG      SAG
         __ SAGQCATV SAGQVSCA    ?  u??S  148C40A409DEE2>SAG      SAG      SAG
         __ SAGQCATV SAGQVSQL    ?    ??? 148C40AB001D3F>SAG      SAG      SAG
         __ SAGQCATV SAGQVSTM    ?   ? 7q 148C40AD078CF7>SAG      SAG      SAG
         __ SAGQCATV SAGQVSTO    ?   ? ?  148C40B409681E>SAG      SAG      SAG
         __ SAGQCATV SAGQVTAB    ?   ? +U 148C40B61F024E>SAG      SAG      SAG
         __ SAGQCATV SAGQVTAS    ?   ?  d 148C40B80874FF>SAG      SAG      SAG
         __ SAGQCATV SAGQVTBA    ?   ?  ? 148C40BB1854EC>SAG      SAG      SAG
         __ SAGQCATV SAGQVTBC    ?   ?d ? 148C40BD1684EC>SAG      SAG      SAG
         __ SAGQCATV SAGQVTBP    ?   ?    148C40BF07AE9D>SAG      SAG      SAG
         __ SAGQCATV SAGQVTBS    ?     ?? 148C40CA034928>SAG      SAG      SAG

        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help        Exit        Rfind        -     +           <     >    Canc

#### Commands Allowed on Packages

The following line commands are available on the package listing screen. Line commands are entered in front of the desired package(s):

Command | Description
--- | ---
`I` | Displays information on a package.
`S` | Selects a package to be used with main commands.
`U` | Unselects a package.
`AU` | Displays information on access rights to a package.
`IX` | Displays all indexes used by a package.
`SY` | Displays all systems enabled or disabled for a package.
`TB` | Displays all tables used by a package.

The listings of indexes or tables displayed as a result of the `IX` or `TB` command can be used for further processing, whereas the displays resulting from the `AU`, `SY`, or `I` command are for information purposes only.

A list of all line commands available with the List Packages function can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed packages.

The commands `AU`, `IX`, `SY`, and `TB` can also be used as main commands, which are entered in the command line of the table listing screen and apply to all tables previously selected with the line command `S`.

The `INFO` main command, which is the equivalent of the `I` line command, displays information on all tables previously selected. All information resulting from the `I` or `INFO` commands can also be printed.

      11:06:11              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       PACKAGE *.*                               S 01    Row 34 of 65 Columns 041 075
       ====>                                                        Scroll ===>  PAGE
          COLLID   NAME        CONTOKEN CONTOKEN (HEX) OWNER    CREATOR  QUALIFIER
       i_ SAGQ +---------------------------------------------------------+ G
       __ SAGQ !                                                         ! G
       __ SAGQ !                 Select what to display                  ! G
       __ SAGQ !                                                         ! G
       __ SAGQ !           _ systems enabled or disabled for package     ! G
       __ SAGQ !           _ tables  referenced in package               ! G
       __ SAGQ !           _ indexes used in package                     ! G
       __ SAGQ !           _ statements of package                       ! G
       __ SAGQ !           _ authorizations on package                   ! G
       __ SAGQ !                                                         ! G
       __ SAGQ !                                                         ! G
       __ SAGQ !                 Mark  _ to print output                 ! G
       __ SAGQ !                                                         ! G
       __ SAGQ +---------------------------------------------------------+ G
       __ SAGQCATV SAGQVTBC    ?   ?d ? 148C40BD1684EC>SAG      SAG      SAG
       __ SAGQCATV SAGQVTBP    ?   ?    148C40BF07AE9D>SAG      SAG      SAG
       __ SAGQCATV SAGQVTBS    ?     ?? 148C40CA034928>SAG      SAG      SAG

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

A list of all main commands available with the List Packages function can be invoked as a window by entering the help character, that is, a question mark (?), in the command line of the packages list screen.

## List Tables

To invoke the List Tables function

- On the Retrieval of System Tables screen, enter function code `T`.

  The creator and name of the table(s) to be listed can be specified.

  - If a value followed by an asterisk is specified, all tables defined in the Db2 catalog whose creator/name begins with this value are listed.

  - If asterisk notation is specified only, all tables defined in the Db2 catalog are listed.

  Press Enter.

        16:42:58              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
         TABLE SAG*.*                              S 01   Row 34 of 361 Columns 036 075
         ====>                                                        Scroll ===>  PAGE
            CREATOR  TABLE NAME    TYPE COLCOUNT KEYCOLS RECLEN DATABASE TSNAME       C
         ** ******************************* top of data *******************************
         __ SAGCRE   ACT           T           3       1     38 SAG8D22A ACT
         __ SAGCRE   DEPT          T           4       1     59 SAG8D22A SAG8S2
         __ SAGCRE   EACT          T           5       0     54 SAG8D22A SAG8S2
         __ SAGCRE   EDEPT         T           6       0     75 SAG8D22A SAG8S2
         __ SAGCRE   EEMP          T          16       0    123 SAG8D22A SAG8S2
         __ SAGCRE   EEPA          T           8       0     52 SAG8D22A SAG8S2
         __ SAGCRE   EMP           T          14       1    107 SAG8D22A SAG8S2
         __ SAGCRE   EMPPROJACT    T           6       0     36 SAG8D22A EMPPRO
         __ SAGCRE   EPROJ         T          10       0     86 SAG8D22A SAG8S2
         __ SAGCRE   EPROJACT      T           7       0     45 SAG8D22A SAG8S2
         __ SAGCRE   PROJ          T           8       1     70 SAG8D22A PROJ
         __ SAGCRE   PROJACT       T           5       3     29 SAG8D22A PROJAC
         __ SAGCRE   TCONA         T           5       0   4056 SAG8D22P SAG8S2
         __ SAGCRE   TDSPTXT       T           3       0     91 SAG8D22P SAG8S2
         __ SAGCRE   TOPTVAL       T          11       0    354 SAG8D22P SAG8S2
         __ SAGCRE   VACT          V           3       0      0 SAG8D22A ACT

        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help        Exit        Rfind        -     +           <     >    Canc

#### Commands Allowed on Tables

The following line commands are available on the table listing screen. Line commands are entered in front of the desired table(s):

Command | Description
--- | ---
`I` | Displays information on a table.
`S` | Selects a table to be used with main commands.
`U` | Unselects a table.
`AU` | Displays information on access rights to a table.
`CO` | Displays all columns of a table.
`IX` | Displays all indexes on a table.
`CC` | Checks constraints.

The listings of indexes displayed as a result of the `IX` command can be used for further processing, whereas the listings of columns resulting from the `CO` command, as well as the displays resulting from the `AU` or `I` command, are for information purposes only.

A list of all line commands available with the List Tables function can be invoked as a window by entering the help character, that is, a question mark (?), in front of any of the listed tables.

The commands `AU`, `CO`, and `IX` can also be used as main commands, which are entered in the command line of the table listing screen and apply to all tables previously selected with the line command `S`.

The `INFO` main command, which is the equivalent of the `I` line command, displays information on all tables previously selected. All information resulting from the `I` or `INFO` commands can also be printed.

      16:42:58              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
       TABLE SAG*.*                              S 01   Row 34 of 361 Columns 036 075
       ====>                                                        Scroll ===>  PAGE
          CREA +---------------------------------------------------------+          C
       ** **** !                                                         ! **********
       I_ SAGC !                                                         !
       __ SAGC !                 Select what to display                  ! S2
       __ SAGC !                                                         ! S2
       __ SAGC !  _ columns  of table/view    _ referential constraints  ! S2
       __ SAGC !  _ synonyms of table/view    _ authorized  users        ! S2
       __ SAGC !  _ plans    using table/view                            ! S2
       __ SAGC !  _ packages using table/view _ indexes  of table        ! S2
       __ SAGC !  _ views    using table/view _ columns  of indexes      ! RO
       __ SAGC !  _ base tables of view       _ plans using indexes      ! S2
       __ SAGC !  _ definition  of view       _ packages using indexes   ! S2
       __ SAGC !  _ check conditions of table                            !
       __ SAGC !                                                         ! AC
       __ SAGCR!                 Mark  _ to print output                 ! S2
       __ SAGCR+---------------------------------------------------------+ S2
       __ SAGCRE   TOPTVAL       T          11       0    354 SAG8D22P SAG8S2
       __ SAGCRE   VACT          V           3       0      0 SAG8D22A ACT

      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help        Exit        Rfind        -     +           <     >    Canc

A list of all main commands available with the List Tables function can be invoked as a window by entering the help character, that is, a question mark (?), in the command line of the table listing screen.

## User Authorizations

To invoke the User Authorization function

- On the Retrieval of System Tables screen, enter function code `U` and press Enter.

  The Retrieval of User Authorizations menu is displayed:

        16:44:51              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
                              - Retrieval of User Authorizations -

                             Code Function                Parameter

                              C   Column   Authorizations Grantee
                              D   Database Authorizations Grantee
                              K   Package  Authorizations Grantee
                              P   Plan     Authorizations Grantee
                              R   Resource Authorizations Grantee
                              T   Table    Authorizations Grantee
                              U   User     Authorizations Grantee
                              ?   Help
                              .   Exit

                      Code .. _   Grantee .. _________________________________

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
              Help        Exit                                                  Canc

  The following functions are available:

  Code | Description
  --- | ---
  `C` | Displays the columns which can be accessed by the specified grantee.
  `D` | Displays the databases which can be accessed by the specified grantee.
  `K` | Displays the packages which can be accessed by the specified grantee.
  `P` | Displays the plans which can be accessed by the specified grantee.
  `R` | Displays the resources which can be accessed by the specified grantee.
  `T` | Displays the tables which can be accessed by the specified grantee.
  `U` | Displays the system privileges of the specified grantee.

  The following parameter must be specified:

  Parameter | Description
  --- | ---
  `Grantee` | A list of all existing Db2 objects of the specified object type to which the specified grantee has access is displayed.

## List Statistic Tables

To invoke the List Statistic Tables function

- On the Retrieval of System Tables screen, enter function code `S` and press Enter.

  The Retrieval of Statistic Tables menu is displayed:

        16:38:47              ***** NATURAL TOOLS FOR DB2 *****             2007-10-05
                                - Retrieval of Statistic Tables -

                              Code Function              Parameter

                               C   List SYSCOLSTATS      Creator, Name
                               D   List SYSCOLDISTSTATS  Creator, Name
                               I   List SYSINDEXSTATS    Index Owner, Name
                               T   List SYSTABSTATS      Creator, Name
                               ?   Help
                               .   Exit

                       Code .. _   Index Owner ......... _____________________________
                                   Index Name .......... _____________________________
                                   Table Creator ....... _____________________________
                                   Table Name .......... _____________________________

        Command ===>
        Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--
              Help        Exit                                                  Canc

  The following functions are available:

  Code | Description
  --- | ---
  `C` | Displays the partitioned statistics for columns in a partitioned table space.
  `D` | Displays the distribution of the values of the first column of a partitioned index.
  `I` | Displays the statistics for a partitioned index.
  `T` | Displays the statistics for a partitioned table space.

  The following parameters must be specified:

  Parameter | Description
  --- | ---
  `Table Creator` | The name of the creator of the table for which the statistics are to be displayed.
  `Table Name` | The name of the table for which the statistics are to be displayed.
  `Index Owner` | The name of the owner of the index for which the index statistics are to be displayed.
  `Index Name` | The name of the index for which the index statistics are to be displayed.

### On this page

- Invoking the Retrieval of System Tables Function
- List Databases
- List Tablespaces
- List Plans
- Commands Allowed on Plans
- List Packages
- List Tables
- User Authorizations
- List Statistic Tables