SAND CDBMS SQL Reference Guide
System Tables

 

The SAND CDBMS system tables, contained in the SYSTEM schema, store information about all objects in a database (that is, tables, columns, domains, schemas, views, user authorizations, privileges, and constraints), as well as information relating to system routines, errors, and query optimization. The information stored in the system tables is updated continuously by SAND CDBMS to reflect changes in the database state.

Users can access system information indirectly through public views that are based on selected system tables. Non-DBA users may query the views in the PUBLIC schema to retrieve information about database objects that they own, or on which they possess privileges. Users with DBA privileges can query the public views to retrieve information about all database objects. Refer to the section Public Views above for further information about SAND CDBMS public views.

Warning: While the system tables cannot be removed with the DROP command, and the structure of these tables cannot be changed by an ALTER TABLE statement, the data contained in these tables can be modified manually. SAND CDBMS system tables should not be directly altered by any user, including the user DBA. Do not attempt to modify system tables with INSERT, UPDATE, or DELETE statements, as these could unintentionally corrupt the database.


The SAND CDBMS system tables are the following:

AUTHORIZATIONS

DATABASES

Schemas
SCHEMAS

Domains
DOMAINS
DATATYPES

Tables, Columns, Views, and Constraints
TABLES
COLUMNS
*VIEWTEXT
*VIEWSEM
*VIEWCOLS
DEFAULTS
CONSTRAINTS
CONSTRAINTCOLS
FOREIGNKEYS
* populated only if the table is of the type VIEW

Privileges
DBAPRIVS
SCHEMAPRIVS
DOMAINPRIVS
TABPRIVS
COLPRIVS

Errors
ERRORS

Blobs
BLOBS

Remote Tables
CONNECTIONS
REMOTETABS

Partitioned Tables
PARTITIONS
PARTITIONCOLS
PARTITIONTABS
DIMENSIONS

Indexes
JOININDEXES

Dependencies
JOINDEPS
VIEWDEPS

 

User Authorizations

AUTHORIZATIONS table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) user name
authid INTEGER system-generated surrogate ID for the user
defaultDB INTEGER dbid of the default database for the user; if null, the user must specify a database when connecting
defaultSchema INTEGER schemaID of the default schema for the user; if null, the user begins in the PUBLIC schema when connecting
creator INTEGER authid of the creator of the authorization
created TIMESTAMP timestamp when the authorization was created
password BLOB encrypted form of the password associated with the authorization
passwordChange TIMESTAMP timestamp when the password was last changed; if null, password has never been changed
defaultPartN INTEGER

tableID of the user's default partitioned table

 

Databases

DATABASES table  

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) database name
dbid INTEGER system-generated surrogate ID for the database 
path VARCHAR(255) physical path of the location of associated database files
creator INTEGER authid of the creator of the database
created TIMESTAMP timestamp when the database was created

 

 Schemas

SCHEMAS table  

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) schema name
dbid INTEGER dbid of the database that owns the schema
schemaID INTEGER system-generated surrogate ID for the schema
authID INTEGER authid of the owner of the schema
created TIMESTAMP timestamp when the schema was created

 



Domains

DOMAINS table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) domain name (system domains begin with the underscore character: "_")
datatype INTEGER encoded representation of the base type of the data elements associated with the domain
length INTEGER maximum length in bytes permitted for values in the domain (note that this is unrelated to data storage size)
scale SMALLINT for numeric types, the maximum number of digits permitted for instantiations of values in the domain
domainID INTEGER system-generated surrogate ID for the domain
schemaID INTEGER schemaID of the schema to which the domain belongs
default INTEGER handle associated with the expression representing the default value for the domain; if null, there is no default value
defaultSize INTEGER size (in bytes) of the execution-ready form of the default value (if present)
checkName VARCHAR(128) name of the check constraint for the domain; if null, there is no check constraint
check INTEGER handle of the execution-ready form of the check constraint (if present)
checkSize INTEGER size (in bytes) of the execution-ready form of the check constraint (if present)
creator INTEGER authid of the creator of the domain
created TIMESTAMP timestamp when the domain was created
collation INTEGER

collation type for the character domain
 2260: BINARY
 2261: BINARY_UPCASE
 2262: LATIN01
 2263: LATIN01_UPCASE
 2268: LATIN02
 2269: LATIN02_UPCASE
(null if the domain is not a character type, or if the collation type is unknown)

 

DATATYPES table

COLUMN DATA TYPE DESCRIPTION
datatype INTEGER encoded representation of the data type
type VARCHAR(9) name for the data type

 



Tables, Columns, Views, and Constraints

TABLES table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) table name
tableID INTEGER system-generated surrogate ID for the table
schemaID INTEGER schemaID of the schema to which the table belongs
type CHAR(1)

B: base table
L: linked table
N
: snapshot table
R or U : view or partitioned table
S: system table

remarks VARCHAR(80) textual remarks associated with the table
creator INTEGER authid of the creator of the table
created TIMESTAMP timestamp when the table was created
altered TIMESTAMP timestamp when the table was last altered; if null, the table has never been altered

 

COLUMNS table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) column name
colno SMALLINT ordinal position of the column, in terms of order of column definition
storeno INTEGER physical position of the column in storage
datatype INTEGER encoded representation of the base datatype of the column
length INTEGER maximum length in bytes permitted for values in the column (note that this is unrelated to data storage size)
scale INTEGER for numeric data types, number of digits after the decimal point
tableID INTEGER tableID of the table to which the column belongs
domainID INTEGER domainID of the domain with which the column is associated
remarks VARCHAR(80) textual remarks associated with the column
creator INTEGER authid of the creator of the table
created TIMESTAMP timestamp when the column was created
altered TIMESTAMP timestamp when the column was last altered; if null, column has never been altered

 

VIEWTEXT table

note: populated only if the table type is R or U (view or partitioned table)  

COLUMN DATA TYPE DESCRIPTION
viewID INTEGER system-generated ID for the view definition
lineno SMALLINT line number of the view definition text
text VARCHAR(80) view definition in textual form

 

VIEWSEM table

note: populated only if the table type is R or U (view or partitioned table)  

COLUMN DATA TYPE DESCRIPTION
viewID INTEGER system-generated ID for the view definition
exec INTEGER handle to the semantic form of the view
execSize INTEGER size (in bytes) of the semantic form of the view

 

VIEWCOLS table

note: populated only if the table type is R or U (view or partitioned table)  

COLUMN DATA TYPE DESCRIPTION
viewID INTEGER system-generated ID for the view definition
vColName VARCHAR(128) name of the column as defined in the view
vColNo INTEGER ordinal position of the column in the view definition
tableID INTEGER tableID of the base table referenced by the view definition
colNo INTEGER colno of the column in the base table

 

DEFAULTS table  

COLUMN DATA TYPE DESCRIPTION
tableID INTEGER tableID of the table to which the column belongs
colNo INTEGER colno of the column to which the default applies
expr INTEGER handle to the expression representing the default value
exprSize INTEGER size (in bytes) of the expression representing the default value

 

CONSTRAINTS table  

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) constraint name; if the user does not specify a constraint name, the system will automatically generate one
type CHAR(2) PK: primary key
FK: foreign key
UK: unique key
NN: not null
constraintID INTEGER system-generated ID for the constraint
schemaID INTEGER schemaID of the schema to which the table owning the constraint belongs
tableID INTEGER tableID of the table with which the constraint is associated
enabled CHAR(1) Y: the constraint is being enforced
N: the constraint is not being enforced
enforceID INTEGER ID of the QEP that enforces the constraint
creator INTEGER authid of the creator of the constraint
created TIMESTAMP timestamp when the constraint was created

 

CONSTRAINTCOLS table  

COLUMN DATA TYPE DESCRIPTION
constraintID INTEGER constraintID of the constraint definition
tableID INTEGER tableID of the table with which the constraint is associated
colno INTEGER colno of the column participating in the constraint
keyseq SMALLINT ordinal position of the column in the constraint definition; null if the constraint is a check constraint

 

FOREIGNKEYS table  

COLUMN DATA TYPE DESCRIPTION
constraintID INTEGER constraintID of the FOREIGN KEY constraint definition
refConstraintID INTEGER constraintID of the referenced PRIMARY KEY or UNIQUE constraint
onUpdate CHAR(1)

The rule for operations involving updates on referenced columns:
C: CASCADE
D: SET DEFAULT
N: SET NULL
R: RESTRICT

onDelete CHAR(1) The rule for operations involving deletes on referenced columns:
C
: CASCADE
D: SET DEFAULT
N: SET NULL
R: RESTRICT
updateQEPID INTEGER QEPid of the QEP enforcing the update rule on the referencing constraint (-1 if there is no QEP)
deleteQEPID INTEGER QEPid of the QEP enforcing the delete rule on the referencing constraint (-1 if there is no QEP)
orphanQEPID INTEGER QEPid of the QEP enforcing the parent-child relationship of the foreign key when inserts are executed on the child table (-1 if there is no QEP)

 



Privileges

DBAPRIVS table  

COLUMN DATA TYPE DESCRIPTION
grantor INTEGER authid of the user who granted the privilege
grantee INTEGER authid of the user who received the privilege
granted TIMESTAMP timestamp when the privilege was granted
grantable CHAR(1) Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the grantee

 

SCHEMAPRIVS table  

COLUMN DATA TYPE DESCRIPTION
grantor INTEGER authid of the user who granted the privilege
grantee INTEGER authid of the user who received the privilege
schemaID INTEGER schemaID of the schema to which the privileges apply
privilege CHAR(1) S: select
D: delete
U: update
I: insert
R: references
A: owner
grantable CHAR(1) Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the grantee
granted TIMESTAMP timestamp when the privilege was granted

 

DOMAINPRIVS table  

COLUMN DATA TYPE DESCRIPTION
grantor INTEGER authid of the user who granted the privilege
grantee INTEGER authid of the user who received the privilege
domainID INTEGER domainID of the domain to which the privileges apply
privilege CHAR(1) G: usage
grantable CHAR(1) Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the grantee
granted TIMESTAMP timestamp when the privilege was granted

 

TABPRIVS table  

COLUMN DATA TYPE DESCRIPTION
grantor INTEGER authid of the user who granted the privilege
grantee INTEGER authid of the user who received the privilege
TableID INTEGER tableID of the table to which the privileges apply
privilege CHAR(1) S: select
D: delete
U: update
I: insert
R: references
grantable CHAR(1) Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the grantee
granted TIMESTAMP timestamp when the privilege was granted

 

COLPRIVS table  

COLUMN DATA TYPE DESCRIPTION
grantor INTEGER authid of the user who granted the privilege
grantee INTEGER authid of the user who received the privilege
tableID INTEGER tableID of the table containing the column to which the privileges apply
colno SMALLINT colno of the column to which the privileges apply
privilege CHAR(1) S: select
I: insert
U: update
R: references
grantable CHAR(1) Y: the privilege can be granted to other users by the grantee
N: the privilege cannot be granted to other users by the grantee
granted TIMESTAMP timestamp when the privilege was granted

 



Errors

ERRORS table  

COLUMN DATA TYPE DESCRIPTION
errorCode SMALLINT internal error code
sqlState CHAR(5) associated sqlstate value
message VARCHAR(128) text of the error message

 



Blobs

BLOBS table  

COLUMN DATA TYPE DESCRIPTION
blobID INTEGER a unique identifier associated with the BLOB object
blobSize INTEGER the size (in bytes) of the BLOB object
blobData BLOB the BLOB object itself

 



Remote Tables

CONNECTIONS table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) the connection object name
connectionID INTEGER system-generated surrogate ID for the connection
hostName VARCHAR(128) the name of the computer where the remote database is running
dbName VARCHAR(128) the remote database name
authName VARCHAR(128) the user name that will be used to connect to the remote database
password BLOB the password for the authName user
created TIMESTAMP the date and time when the connection object was created

 

REMOTETABS table

COLUMN DATA TYPE DESCRIPTION
tableID INTEGER tableID of the local table defined on a remote table
connectionID INTEGER connectionID of the connection used to connect to the remote database
remoteSchema VARCHAR(128) the schema that contains the remote table
remoteTable VARCHAR(128) the remote table name

 



Partitioned Tables

PARTITIONS table

COLUMN DATA TYPE DESCRIPTION
viewID INTEGER tableID of the partitioned table
type CHAR(2) the partitioning strategy used with this table:
HA: hash
RA: range
RR: round robin
partitionID INTEGER the unique identifier associated with the partition object
creator INTEGER authID of the creator of the partitioned table
created TIMESTAMP the date and time when the partitioned table was created

 

PARTITIONCOLS table

COLUMN DATA TYPE DESCRIPTION
partitionID INTEGER the unique identifier associated with the partition object
viewID INTEGER tableID of the partitioned table
colNo INTEGER ordinal position of the column, in terms of order of column definition
keySeq INTEGER ordinal position of the column in the partition key

 

PARTITIONTABS table

COLUMN DATA TYPE DESCRIPTION
partitionID INTEGER the unique identifier associated with the partition object
tableID INTEGER tableID of a partition sub-table
value CHAR(4056) For partition by range,this is the upper bound for values that go into this partition. The row with the highest PartSeq value is the catch-all partition and has a value of null.

Other partition types have a value of null for each row

partSeq INTEGER ordinal position of the partition, in terms of order of partition definition

 

DIMENSIONS table

COLUMN DATA TYPE DESCRIPTION
tableID INTEGER tableID of the local dimension table
connectionID INTEGER connectionID of the connection used to connect to the remote database
remoteSchema VARCHAR(128) the remote schema to which the dimension table belongs
remoteTable VARCHAR(128) the remote dimension table name

 



Indexes

JOININDEXES table

COLUMN DATA TYPE DESCRIPTION
name VARCHAR(128) the join index name
joinID INTEGER system-generated surrogate ID for the join index
schemaID INTEGER schemaID of the schema to which the join index belongs
created TIMESTAMP the date and time when the join index was created

 



Dependencies

JOINDEPS table

COLUMN DATA TYPE DESCRIPTION
joinID INTEGER joinID of the join index
tableID INTEGER tableID of a table involved in the join index

 

VIEWDEPS table

COLUMN DATA TYPE DESCRIPTION
viewID INTEGER tableID of the view definition
objectID INTEGER system-generated ID for the dependent object