SHOW-SQL-DB-CATALOG-ENTRIES

This statement displays entries from the SQL database catalog.

Scope of validity

DBH administration

See also

DBH start statement ADD-SQL-DATABASE-CATALOG-LIST on "ADD-SQL-DATABASE-CATALOG-LIST"

Function

The SHOW-SQL-DB-CATALOG-ENTRIES statement allows you to display entries in the SQL database catalog. The following appears for each entry:

• the logical database name

• the physical database name

• in addition, the version number of the SESAM backup copy of the catalog space, if the database is a SESAM backup copy of a database

• the access mode (Class) permitted for this database (READ, WRITE, ADMIN and REPLICATION)

• the DB user ID of the database (USERID)

• the status of the database (Status) with additional information (Info)

  The following statuses are possible:

  • STATUS = ACTIVE, which means the database is open during the current DBH session

  • STATUS = CLOSED, which means the database is closed after PREPARE-FOREIGN-COPY with physical closure of the database files

  • STATUS = FREE, which means the database is physically closed and unlocked

  • STATUS = LOCKED, which means the database is not available during the current session because of a lock.
    You receive additional information with this status as to why the database is not available. If there is a DMS error, you receive the DMS error key.
    If the problem (e.g. DMS error) has been eliminated, you can use the SET-SQL-DB-CATALOG-STATUS administration statement to set the status of the database to ACTIVE.

  • STATUS = RECOVER, i.e. the database is currently in a state of recovery. The status of the database changes automatically to ACTIVE at the end of the recovery if the recovery was successful, or it changes to LOCKED if it could not be completed successfully.

  • STATUS = REORG, i.e. the database is currently in a state of reorganization. The status of the database changes automatically to ACTIVE at the end of the reorganization if the reorganization was successful, or it changes to LOCKED if it could not be completed successfully.

  • STATUS = REFRESH, i.e. the database is currently in a state of refresh. The status of the database changes automatically to ACTIVE at the end of the refresh if the refresh was successful, or it changes to LOCKED if it could not be completed successfully.

• A timestamp in the format YYYY-MM-dd hh:mm:ss which specifies when the status of the database was determined.

SHOW-SQL-DB-CATALOG-ENTRIES

SELECT = *ALL / *LOGICAL(...) / *PHYSICAL(...)
	*LOGICAL(...)
		|	CATALOG-NAME = <filename 1..18 without-cat-user-gen-vers>
	*PHYSICAL(...)
		|	PHYS-CATALOG-NAME = <filename 1..18 without-cat-user-gen-vers>
,STRUCTURE-OUTPUT = *NONE / *SYSINF / <structured-name 1..20>(...)
	<structured-name 1..20>(...)
		|	WRITE-MODE = *REPLACE / *EXTEND

Operands

SELECT =

Specifies which entries from the SQL database catalog are to be displayed.

SELECT = *ALL
Specifies that all entries from the SQL database catalog are to be displayed.

SELECT = *LOGICAL(...)
Specifies that those entries containing the specified CATALOG-NAME are to be displayed.

CATALOG-NAME = <filename 1..18 without-cat-user-gen-vers>
Logical name of the database whose entry is displayed. If the database is a SESAM backup copy, the number of the SESAM backup copy of the catalog space is also output on the output medium.

SELECT = *PHYSICAL(...)

Specifies that those entries containing the specified PHYS-CATALOG-NAME are to be displayed.

PHYS-CATALOG-NAME = <filename 1..18 without-cat-user-gen-vers>

Physical name of the database whose entry is displayed. If the database is a SESAM backup copy, the number of the SESAM backup copy of the catalog space is also output on the output medium.

STRUCTURE-OUTPUT = *NONE / *SYSINF / <structured-name 1..20>

(WRITE-MODE = *REPLACE / *EXTEND)

In S procedures, specifies whether the information is to be output in S variables (see section “Syntax for output in S variables”).

Output in S variables

Output information	Name of the S variable	T	Contents
Logical database name	var(*LIST).CATALOG	S	<filename 1..18>
Physical database name	var(*LIST).PHYSICAL-NAME	S	<filename 1..18>
Version number of the SESAM backup copy	var(*LIST).COPY-NUMBER	S	<integer 1..999999>
Access mode	var(*LIST).ACCESS-MODE	S	WRITE / READ / ADMIN / REPL
DB user ID of the database	var(*LIST).USERID	S	<name 1..8>
Database status	var(*LIST).STATE	S	ACTIVE / FREE / LOCKED / REORG / RECOVER / REFRESH / CLOSED
Additional information	var(*LIST).INFORMATION	S	SQL-STATE: xxxxx / DMS-ERROR: xxxx / CC-NUMBER: xx/xx/xx / '' (void)
Timestamp	var(*LIST).TIMESTAMP	S	yyyy-mm-dd hh:mm:ss

Example

Output to the screen after entering the SHOW-SQL-DB-CATALOG-ENTRIES administration statement:

%SHOW-SQL-DB-CATALOG-ENTRIES
<catalog-name>       | Copy-|Class| UserID   | Status  | Info                   
  <physical-name>    |  Nr  |     |          |         |
------------------------------------------------------------------------------- 
DCCAT                |      |ADMIN| SQLDB112 | ACTIVE  | 
  DB112.G6           |      |     |          |         | <date> <time>
DB6LOKG              |      |ADMIN| SQLDB112 | ACTIVE  | 
  CATG6              |      |     |          |         | <date> <time>
% <date> <time> SES7215 END OF OUTPUT                                    
%// 

Administration command in ISP format and at the CALL DML interface

The OPT,DBC-INFO administration command corresponds to the SHOW-SQL-DB-CATALOG-ENTRIES administration statement here.

OPT,DBC-INFO[,supplement]

supplement ::= {C[ATALOG]=catalog-name|P[HYSICAL-NAME]=physical-name}

If you do not specify the supplement parameter, the whole SQL database catalog is displayed.