DB2 Version 9.7 for Linux, UNIX, and Windows

LIST PACKAGES/TABLES command

Lists packages or tables associated with the current database.

Authorization

For the system catalog SYSCAT.PACKAGES (LIST PACKAGES) and SYSCAT.TABLES (LIST TABLES), one of the following is required:

sysadm
sysctrl
sysmaint
sysmon
dbadm
CONTROL privilege
SELECT privilege.

Required connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command syntax


>>-LIST--+-PACKAGES-+--+------------------------------+--------->
         '-TABLES---'  |      .-USER----------------. |   
                       '-FOR--+-ALL-----------------+-'   
                              +-SCHEMA--schema-name-+     
                              '-SYSTEM--------------'     

>--+-------------+---------------------------------------------><
   '-SHOW DETAIL-'

Command parameters

FOR

If the FOR clause is not specified, the packages or tables for USER are listed.

ALL: Lists all packages or tables in the database.
SCHEMA schema-name: Lists all packages or tables in the database for the specified schema only.
SYSTEM: Lists all system packages or tables in the database.
USER: Lists all user packages or tables in the database for the current user.

SHOW DETAIL

If this option is chosen with the LIST TABLES command, the full table name and schema name are displayed. If this option is not specified, the table name is truncated to 30 characters, and the ">" symbol in the 31st column represents the truncated portion of the table name; the schema name is truncated to 14 characters and the ">" symbol in the 15th column represents the truncated portion of the schema name. If this option is chosen with the LIST PACKAGES command, the full package schema (creator), version and bound by authid are displayed, and the package unique_id (consistency token shown in hexadecimal form). If this option is not specified, the schema name and bound by ID are truncated to 8 characters and the ">" symbol in the 9th column represents the truncated portion of the schema or bound by ID; the version is truncated to 10 characters and the ">" symbol in the 11th column represents the truncated portion of the version.

Examples

The following is sample output from LIST PACKAGES:

                                Bound     Total                       Isolation
Package    Schema    Version    by        sections     Valid  Format  level     Blocking
---------- --------- ---------- --------- ------------ ------ ------- --------- --------
F4INS      USERA     VER1       SNOWBELL           221 Y      0       CS        U    
F4INS      USERA     VER2.0     SNOWBELL           201 Y      0       RS        U    
F4INS      USERA     VER2.3     SNOWBELL           201 N      3       CS        U    
F4INS      USERA     VER2.5     SNOWBELL           201 Y      0       CS        U    
PKG12      USERA                USERA               12 Y      3       RR        B
PKG15      USERA                USERA               42 Y      3       RR        B
SALARY     USERT     YEAR2000   USERT               15 Y      3       CS        N

The following is sample output from LIST TABLES:

Table/View         Schema           Type       Creation time
------------------ ---------------- ---------- ----------------------------
DEPARTMENT         SMITH            T          1997-02-19-13.32.25.971890
EMP_ACT            SMITH            T          1997-02-19-13.32.27.851115
EMP_PHOTO          SMITH            T          1997-02-19-13.32.29.953624
EMP_RESUME         SMITH            T          1997-02-19-13.32.37.837433
EMPLOYEE           SMITH            T          1997-02-19-13.32.26.348245
ORG                SMITH            T          1997-02-19-13.32.24.478021
PROJECT            SMITH            T          1997-02-19-13.32.29.300304
SALES              SMITH            T          1997-02-19-13.32.42.973739
STAFF              SMITH            T          1997-02-19-13.32.25.156337

  9 record(s) selected.

Usage notes

LIST PACKAGES and LIST TABLES commands are available to provide a quick interface to the system tables.

The following SELECT statements return information found in the system tables. They can be expanded to select the additional information that the system tables provide.

 
   select tabname, tabschema, type, create_time
   from syscat.tables
   order by tabschema, tabname;
 
   select pkgname, pkgschema, pkgversion, unique_id, boundby, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   order by pkgschema, pkgname, pkgversion;
 
   select tabname, tabschema, type, create_time
   from syscat.tables
   where tabschema = 'SYSCAT'
   order by tabschema, tabname;
 
   select pkgname, pkgschema, pkgversion, unique_id, boundby, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = 'NULLID'
   order by pkgschema, pkgname, pkgversion;
 
   select tabname, tabschema, type, create_time
   from syscat.tables
   where tabschema = USER
   order by tabschema, tabname;
 
   select pkgname, pkgschema, pkgversion, unique_id, boundby, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = USER
   order by pkgschema, pkgname, pkgversion;