Recital Developer Center / Technical Articles /Using System Tables in Recital 9


Using System Tables in Recital 9
Related Links


Recital Home
Recital Product Family
Recital Terminal Developer
Recital Visual Developer
Recital Database Server
Recital Web Developer

 
    
    
   

In this article Yvonne Milne details on how to use the System Tables in Recital 9.

Yvonne Milne
May, 2005

Overview

System Tables are system defined read-only tables. You can query these tables using the SELECT statement. The following is an alphabetical reference of the System Tables available.

Table Remarks
SysActiveUsers Description of currently active users on the system

SysBestRowIdentifier

Description of a tables optimal set of columns that uniquely identifies a row

SysCatalogs

Catalog names available in the database
SysColumnConstraints Description of the constraints for a table's columns

SysColumnPrivileges

Description of the access rights for a tables columns

SysColumns

Description of the table columns available in the catalog

SysCrossReference

Description of how one table imports the keys of another table

SysExportedKeys

Description of the foreign key columns that reference the primary key columns

SysImportedKeys

Description of the primary key columns that are referenced by the foreign key

SysIndexInfo

Description of a tables indices and statistics
SysIOStats Facility for monitoring table and index file I/O operations
SysLogging System Logging information

SysPrimaryKeys

Description of the primary key columns in the table

SysProcedureColumns

Description of the input, output and results associated with certain stored procedures available

SysProcedures

Description of the stored procedures available in the catalog

SysSchemas

Schema names available in the database
SysTableContraints Description of the constraints for each table available in the catalog

SysTablePrivileges

Description of the access rights for each table available in the catalog

SysTables

Description of the tables available in the catalog

SysTableTypes

Table types available in the database system

SysTypeInfo

Description of all data types supported by the database

SysUDTs

Description of the user-defined types (UDTs) defined in the schema

SysVersionColumns

Description of the columns in a table that are automatically updated when any row is updated

SysActiveUsers

Description of currently active users on the system.

Column Data Type Width Description

ACTIVE

L

1

True if Active, False if exited uncleanly

GID

N

8

Group ID

UID

N

8

User ID

PID

N

8

Process ID

USER_NAME

C

25

User name

TERMINAL

C

25

Terminal name

CLIENT

C

25

Client connection
DATE D 4 Login date

TIME

C

8

Login time

SysBestRowIdentifier

Description of a tables optimal set of columns that uniquely identifies a row.

Column Data Type Width Description

SCOPE

N

1

Actual scope of result

COLUMN_NAME

C

30

Column name

DATA_TYPE

N

2

SQL data type

TYPE_NAME

C

30

Data source dependent type name

COLUMN_SIZE

N

3

Precision

BUFFER_LENGTH

N

1

Reserved

DECIMAL_DIGITS

N

2

Scale

PSEUDO_COLUMN

N

1

Is this a pseudo column?

SysCatalogs

Catalog names available in the database.

Column Data Type Width Description

TABLE_CAT

C

30

Catalog name

SysColumnConstraints

Description of the constraints for a table's columns.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

COLUMN_NAME

C

32

Column name

CONSTRAINT_NAME

C

30

Constraint Name (see below)

CONSTRAINT

C

100

Constraint

Constraint names:

  • AUTOINC NEXT
  • AUTOINC STEP
  • CALCULATED
  • CHECK
  • CHOICES
  • DEFAULT
  • ERROR
  • HELP
  • NOT NULL
  • NULL SUPPORT
  • PICTURE
  • RANGE
  • RECALCULATE
  • WHEN

SysColumnPrivileges

Description of the access rights for a tables columns.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

COLUMN_NAME

C

32

Column name

GRANTOR

C

30

Grantor of access

GRANTEE

C

100

Grantee of access

PRIVILEGE

C

30

Name of access (SELECT, INSERT etc)

IS_GRANTABLE

C

3

YES if grantee is permitted to grant to others; NO if not; .NULL. if unknown.

SysColumns

Description of the table columns available in the catalog.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

COLUMN_NAME

C

25

Column name

DATA_TYPE

N

2

SQL data type

TYPE_NAME

C

30

Data source dependent type name

COLUMN_SIZE

N

3

Precision

BUFFER_LENGTH

L

1

Reserved

DECIMAL_DIGITS

N

2

Scale

NUM_PREC_RADIX

N

2

Radix (typically either 10 or 2)

NULLABLE

N

2

Is NULL allowed?

REMARKS

C

25

Comment describing column

COLUMN_DEF

C

30

Default value

SQL_DATA_TYPE

N

1

Reserved

SQL_DATETIME_SUB

N

1

Reserved

CHAR_OCTET_LENGTH

N

1

For char types the maximum number of bytes in the column

ORDINAL_POSITION

N

4

Index of column in table (starting at 1)

IS_NULLABLE

C

3

NO means column definitely does not allow NULL values; YES means the column might allow NULL values. An empty string means unknown.

SysCrossReference

Description of how one table imports the keys of another table.

Column Data Type Width Description

PKTABLE_CAT

C

30

Primary key table catalog

PKTABLE_SCHEM

C

30

Primary key table schema

PKTABLE_NAME

C

30

Primary key table name

PKCOLUMN_NAME

C

30

Primary key column name

FKTABLE_CAT

C

30

Foreign key table catalog being exported

FKTABLE_SCHEM

C

30

Foreign key table schema being exported

FKTABLE_NAME

C

30

Foreign key table name being exported

FKCOLUMN_NAME

C

30

Foreign key column name being exported

KEY_SEQ

N

2

Sequence number within foreign key

UPDATE_RULE

N

2

What happens to foreign key when primary is updated

DELETE_RULE

N

2

What happens to the foreign key when primary is deleted

FK_NAME

C

30

Foreign key name

PK_NAME

C

30

Primary key name

DEFERRABILITY

N

2

Can the evaluation of foreign key constraints be deferred until commit?

SysExportedKeys

Description of the foreign key columns that reference the primary key columns.

Column Data Type Width Description

PKTABLE_CAT

C

30

Primary key table catalog

PKTABLE_SCHEM

C

30

Primary key table schema

PKTABLE_NAME

C

30

Primary key table name

PKCOLUMN_NAME

C

30

Primary key column name

FKTABLE_CAT

C

30

Foreign key table catalog being exported

FKTABLE_SCHEM

C

30

Foreign key table schema being exported

FKTABLE_NAME

C

30

Foreign key table name being exported

FKCOLUMN_NAME

C

30

Foreign key column name being exported

KEY_SEQ

N

2

Sequence number within foreign key

UPDATE_RULE

N

2

What happens to foreign key when primary is updated

DELETE_RULE

N

2

What happens to the foreign key when primary is deleted

FK_NAME

C

30

Foreign key name

PK_NAME

C

30

Primary key name

DEFERRABILITY

N

2

Can the evaluation of foreign key constraints be deferred until commit?

SysImportedKeys

Description of the primary key columns that are referenced by the foreign key.

Column Data Type Width Description

PKTABLE_CAT

C

30

Primary key table catalog being imported

PKTABLE_SCHEM

C

30

Primary key table schema being imported

PKTABLE_NAME

C

30

Primary key table name being imported

PKCOLUMN_NAME

C

30

Primary key column name being imported

FKTABLE_CAT

C

30

Foreign key table catalog

FKTABLE_SCHEM

C

30

Foreign key table schema

FKTABLE_NAME

C

30

Foreign key table name

FKCOLUMN_NAME

C

30

Foreign key column name

KEY_SEQ

N

2

Sequence number within foreign key

UPDATE_RULE

N

2

What happens to foreign key when primary is updated

DELETE_RULE

N

2

What happens to the foreign key when primary is deleted

FK_NAME

C

30

Foreign key name

PK_NAME

C

30

Primary key name

DEFERRABILITY

N

2

Can the evaluation of foreign key constraints be deferred until commit?

SysIndexInfo

Description of a tables indices and statistics.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

NON_UNIQUE

L

1

Can index values be non-unique?

INDEX_QUALIFIER

C

30

Index catalog

INDEX_NAME

C

30

Index name

TYPE

N

2

Index type

ORDINAL_POSITION

N

3

Column sequence number within index

COLUMN_NAME

C

30

Column name

ASC_OR_DESC

C

1

Column sort sequence, A is ascending, D is descending

CARDINALITY

N

10

The number of rows in the table or the number of unique values in the index, depending on the index type

PAGES

N

2

The number of pages used for the table or the number of pages used for the current index, depending on the index type

FILTER_CONDITION

C

30

Filter condition

SysIOStats

Facility for monitoring table and index file I/O operations.

Column Data Type Width Description

TABLE_NAME

C

30

Table name

READS

N

10

# of row reads for table

UPDATES

N

10

# of row updates for table

DELETES

N

10

# of row deletes for table

RECALLS

N

10

# of row recalls for table

INSERTS

N

10

# of row inserts for table

DCACHEREADS

N

10

# of cache reads for table

DCACHEWRITES

N

10

# of cache writes for table

INDEXREADS

N

10

# of index reads for table

INDEXWRITES

N

10

# of index writes for table

ICACHEREADS

N

10

# of index cache reads for table
ICACHEWRITES N 10 # of index cache writes for table

SysLogging

System Logging information. When SET SYSLOGGING is ON internal system logging is performed while the process is running.  The information logged can be used to find performance problems or track down system errors.

Column Data Type Width Description

LEVEL

N

1

Logging level: 0-FATAL, 1-ERROR, 2-WARNING, 3-INFORMATION, 4-LOGON/LOGOFF

PID

N

8

Process ID

USER_NAME

C

30

User name

DATE

D

4

Date logged

TIME

C

8

Time logged

FILE_NAME

C

10

Internal

LINE_NUMBER

N

6

Internal

OS_ERROR_NUMBER

N

4

OS error number

PRODUCT_NAME

C

28

Name of logging product

PATCH_LEVEL

C

30

Patch level of logging product

COMPILE_DATETIME

C

20

Compile date of logging product
MESSAGE C 100 Textual information

SysPrimaryKeys

Description of the primary key columns in the table.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

COLUMN_NAME

C

30

Column name

KEY_SEQ

N

2

Sequence number within primary key

PK_NAME

C

30

Primary key name

SysProcedureColumns

Description of the input, output and results associated with certain stored procedures available.

Column Data Type Width Description

PROCEDURE_CAT

C

100

Procedure catalog

PROCEDURE_SCHEM

C

30

Procedure schema

PROCEDURE_NAME

C

30

Procedure name

COLUMN_NAME

C

30

Column/parameter name

COLUMN_TYPE

N

1

Kind of column/parameter

DATA_TYPE

N

2

SQL data type

TYPE_NAME

C

30

SQL type name

PRECISION

N

2

Precision

LENGTH

N

4

Length in bytes of data

SCALE

N

2

Scale

RADIX

N

1

Radix

NULLABLE

N

1

Can it contain NULL

REMARKS

C

30

Comment describing parameter/column

SysProcedures

Description of the stored procedures available in the catalog.

Column Data Type Width Description

PROCEDURE_CAT

C

100

Procedure catalog

PROCEDURE_SCHEM

C

30

Procedure schema

PROCEDURE_NAME

C

30

Procedure name

R1

L

1

Reserved

R2

L

1

Reserved

R3

L

1

Reserved

REMARKS

C

30

Comment describing procedure

PROCEDURE_TYPE

N

1

Kind of procedure

SysSchemas

Schema names available in the database.

Column Data Type Width Description

TABLE_SCHEM

C

30

Table schema

SysTableContraints

Description of the constraints for each table available in the catalog.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

CONSTRAINT_NAME

C

30

Constraint name (see below)

CONSTRAINT

C

100

Constraint

Constraint names:

  • CHECK
  • CLOSE
  • DELETE
  • ERROR
  • INSERT
  • OPEN
  • ROLLBACK
  • UPDATE

SysTablesPrivileges

Description of the access rights for each table available in the catalog.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

GRANTOR

C

30

Grantor of access

GRANTEE

C

100

Grantee of access

PRIVILEGE

C

30

Name of access (SELECT, INSERT etc)

IS_GRANTABLE

C

3

YES if grantee is permitted to grant to others; NO if not; .NULL. if unknown.

SysTables

Description of the tables available in the catalog.

Column Data Type Width Description

TABLE_CAT

C

30

Table catalog

TABLE_SCHEM

C

30

Table schema

TABLE_NAME

C

30

Table name

TABLE_TYPE

C

15

Table type

REMARKS

C

100

Comment describing table

SysTableTypes

Table types available in the database system.

Column Data Type Width Description

TABLE_TYPE

C

15

Table type

SysTypeInfo

Description of all data types supported by the database.

Column Data Type Width Description

TYPE_NAME

C

30

Type name

DATA_TYPE

N

3

SQL data type

PRECISION

N

3

Maximum precision

LITERAL_PREFIX

C

1

Prefix used to quote a literal

LITERAL_SUFFIX

C

1

Suffix used to quote a literal

CREATE_PARAMS

C

30

Parameters used in creating the type

NULLABLE

N

1

Can you use NULL for this type?

CASE_SENSITIVE

L

1

Is it case sensitive?

SEARCHABLE

N

1

Can you use WHERE based on this type?

UNSIGNED_ATTRIBUTE

L

1

Is it unsigned?

FIXED_PREC_SCALE

L

1

Can it be a money value?

AUTO_INCREMENT

L

1

Can it be used for an auto-increment value?

LOCAL_TYPE

C

30

Localized version of type name

MINIMUM_SCALE

N

2

Minimum scale supported

MAXIMUM_SCALE

N

2

Maximum scale supported

SQL_DATA_TYPE

N

1

Reserved

SQL_DATETIME_SUB

N

1

Reserved

NUM_PREC_RADIX

N

2

Usually 2 or 10

SysUDTs

Description of the user-defined types (UDTs) defined in the schema.

Column Data Type Width Description

TYPE_CAT

C

30

Type catalog

TYPE_SCHEM

C

30

Type schema

TYPE_NAME

C

30

Type name

CLASS_NAME

C

30

Class name

DATA_TYPE

N

2

SQL data type

REMARKS

C

30

Comment describing type

SysVersionColumns

Description of the columns in a table that are automatically updated when any row is updated.

Column Data Type Width Description

SCOPE

N

1

Reserved

COLUMN_NAME

C

30

Column name

DATA_TYPE

N

2

SQL data type

TYPE_NAME

C

30

Data source-dependent type name

COLUMN_SIZE

N

3

Precision

BUFFER_LENGTH

N

1

Length of column value in bytes

DECIMAL_DIGITS

N

2

Scale

PSEUDO_COLUMN

N

1

Is this a pseudo column?

Copyright © 2005 Recital Corporation. All rights reserved.
Terms of Use Privacy Policy Contact Us Site Map