System Catalog

You can easily get various schema information from the SQL statement by using the system catalog virtual class. For example, you can get the following schema information by using the catalog virtual class.

-- Classes that refer to the 'b_user' class
SELECT class_name
FROM db_attribute
WHERE domain_class_name = 'db_user';

-- The number of classes that the current user can access
SELECT COUNT(*)
FROM db_class;

-- Attribute of the 'db_user' class
SELECT attr_name, data_type
FROM db_attribute
WHERE class_name = 'db_user';

System Catalog Classes

To define a catalog virtual class, define a catalog class first. The figure below shows catalog classes to be added and their relationships. The arrows represent the reference relationship between classes, and the classes that start with an underline (_) are catalog classes.

Added catalog classes represent information about all classes, attributes and methods in the database. Catalog classes are made up of class composition hierarchy and designed to have OIDs of catalog class instances for cross reference.

_db_class

Represents class information. An index for class_name is created.

Attribute Name	Data Type	Description
class_of	object	A class object. Represents a meta information object for the class stored in the system.
class_name	VARCHAR(255)	Class name
class_type	INTEGER	0 for a class, and 1 for a virtual class
is_system_class	INTEGER	0 for a user-defined class, and 1 for a system class
owner	db_user	Class owner
inst_attr_count	INTEGER	The number of instance attributes
class_attr_count	INTEGER	The number of class attributes
shared_attr_count	INTEGER	The number of shared attributes
inst_meth_count	INTEGER	The number of instance methods
class_meth_count	INTEGER	The number of class methods
collation_id	INTEGER	Collation id
tde_algorithm	INTEGER	TDE encryption algorithm 0: NONE, 1: AES, 2: ARIA
sub_classes	SEQUENCE OF _db_class	Class one level down
super_classes	SEQUENCE OF _db_class	Class one level up
inst_attrs	SEQUENCE OF _db_attribute	Instance attribute
class_attrs	SEQUENCE OF _db_attribute	Class attribute
shared_attrs	SEQUENCE OF _db_attribute	Shared attribute
inst_meths	SEQUENCE OF _db_method	Instance method
class_meths	SEQUENCE OF _db_method	Class method
meth_files	SEQUENCE OF _db_methfile	File path in which the function for the method is located
query_specs	SEQUENCE OF _db_queryspec	SQL definition statement for a virtual class
indexes	SEQUENCE OF _db_index	Index created in the class
comment	VARCHAR(2048)	Comment to describe the class
partition	SEQUENCE of _db_partition	Partition information

The following example shows how to retrieve all sub classes under the class owned by user ‘PUBLIC’ (for the child class female_event in the result, see the example in ADD SUPERCLASS Clause).

SELECT class_name, SEQUENCE(SELECT class_name FROM _db_class s WHERE s IN c.sub_classes)
FROM _db_class c
WHERE c.owner.name = 'PUBLIC' AND c.sub_classes IS NOT NULL;

  class_name            sequence((select class_name from _db_class s where s in c.sub_classes))
============================================
  'event'               {'female_event'}

Note

All examples of system catalog classes have been written in the csql utility. In this example, –no-auto-commit (inactive mode of auto-commit) and -u (specifying user DBA) options are used.

% csql --no-auto-commit -u dba demodb

_db_attribute

Represents attribute information. Indexes for class_of, attr_name and attr_type are created.

Attribute Name	Data Type	Description
class_of	_db_class	Class to which the attribute belongs
attr_name	VARCHAR(255)	Attribute name
attr_type	INTEGER	Type defined for the attribute. 0 for an instance attribute, 1 for a class attribute, and 2 for a shared attribute.
from_class_of	_db_class	If the attribute is inherited, the super class in which the attribute is defined is specified. Otherwise, NULL is specified.
from_attr_name	VARCHAR(255)	Inherited attribute. If an attribute name has changed to resolve a name conflict, the original name define in the super class is specified. Otherwise, NULL is specified.
def_order	INTEGER	Order of attributes in the class. Begins with 0. If the attribute is inherited, the order is the one defined in the super class. For example, if class y inherits attribute a from class x and a was first defined in x, def_order becomes 0.
data_type	INTEGER	Data type of the attribute. One of the values specified in the “Data Types Supported by CUBRID” table below.
default_value	VARCHAR(255)	Default value. Stores as a character string regardless of data types. If there is no default value, NULL. If the default value is NULL, NULL is used. If the data type is an object, ‘volume id | page id | slot id’ is used. If the data type is a collection, ‘{element 1, element 2, … is used.
domains	SEQUENCE OF _db_domain	Domain information of the data type
is_nullable	INTEGER	0 if a not null constraint is configured, and 1 otherwise.
comment	VARCHAR(1024)	Comment to describe the attribute.

Data Types Supported by CUBRID

Value	Meaning	Value	Meaning
1	INTEGER	22	NUMERIC
2	FLOAT	23	BIT
3	DOUBLE	24	VARBIT
4	STRING	25	CHAR
5	OBJECT	31	BIGINT
6	SET	32	DATETIME
7	MULTISET	33	BLOB
8	SEQUENCE	34	CLOB
9	ELO	35	ENUM
10	TIME	36	TIMESTAMPTZ
11	TIMESTAMP	37	TIMESTAMPLTZ
12	DATE	38	DATETIMETZ
18	SHORT	39	DATETIMELTZ

Character Sets Supported by CUBRID

Value	Meaning
0	US English - ASCII encoding
2	Binary
3	Latin 1 - ISO 8859 encoding
4	KSC 5601 1990 - EUC encoding
5	UTF8 - UTF8 encoding

The following example shows how to retrieve user classes (from_class_of.is_system_class = 0) among the ones owned by user ‘PUBLIC’.’

SELECT class_of.class_name, attr_name
FROM _db_attribute
WHERE class_of.owner.name = 'PUBLIC' AND from_class_of.is_system_class = 0
ORDER BY 1, def_order;

class_of.class_name   attr_name
============================================
  'female_event'        'code'
  'female_event'        'sports'
  'female_event'        'name'
  'female_event'        'gender'
  'female_event'        'players'

_db_domain

Represents domain information. An index for object_of is created.

Attribute Name	Data Type	Description
object_of	object	Attribute that refers to the domain, which can be a method parameter or domain
data_type	INTEGER	Data type of the domain (a value in the “Value” column of the “Data Types Supported by CUBRID” table in _db_attribute)
prec	INTEGER	Precision of the data type. 0 is used if the precision is not specified.
scale	INTEGER	Scale of the data type. 0 is used if the scale is not specified.
class_of	_db_class	Domain class if the data type is an object, NULL otherwise.
code_set	INTEGER	Character set (value of table “character sets supported by CUBRID” in _db_attribute) if it is character data type. 0 otherwise.
collation_id	INTEGER	Collation id
enumeration	SEQUENCE OF STRING	String printed enumeration type definition
set_domains	SEQUENCE OF _db_domain	Domain information about the data type of collection element if it is collection data type. NULL otherwise.

_db_charset

Represents charset information.

Attribute Name	Data type	Description
charset_id	INTEGER	Charset ID
charset_name	CHARACTER VARYING(32)	Charset name
default_collation	INTEGER	Default collation ID
char_size	INTEGER	One character’s byte size

_db_collation

The information on collation.

Attribute Name	Data Type	Description
coll_id	INTEGER	Collation ID
coll_name	VARCHAR(32)	Collation name
charset_id	INTEGER	Charset ID
built_in	INTEGER	Built-in or not while installing the product (0: Not built-in, 1: Built-in)
expansions	INTEGER	Expansion support (0: Not supported, 1: Supported)
contractions	INTEGER	Contraction support (0: Not supported, 1: Supported)
uca_strength	INTEGER	Weight strength
checksum	VARCHAR(32)	Checksum of a collation file

_db_method

Represents method information. Indexes for class_of and meth_name are created.

Attribute Name	Data Type	Description
class_of	_db_class	Class to which the method belongs
meth_type	INTEGER	Type of the method defined in the class. 0 for an instance method, and 1 for a class method.
from_class_of	_db_class	If the method is inherited, the super class in which it is defined is used otherwise NULL
from_meth_name	VARCHAR(255)	If the method is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used otherwise NULL
meth_name	VARCHAR(255)	Method name
signatures	SEQUENCE OF _db_meth_sig	C function executed when the method is called

The following example shows how to retrieve class methods of the class with a class method (c.class_meth_count > 0), among classes owned by user ‘DBA.’

SELECT class_name, SEQUENCE(SELECT meth_name
                            FROM _db_method m
                            WHERE m in c.class_meths)
FROM _db_class c
WHERE c.owner.name = 'DBA' AND c.class_meth_count > 0
ORDER BY 1;

  class_name            sequence((select meth_name from _db_method m where m in c.class_meths))
============================================
  'db_serial'           {'change_serial_owner'}
  'db_authorizations'   {'add_user', 'drop_user', 'find_user', 'print_authorizations', 'info', 'change_owner', 'change_trigg
r_owner', 'get_owner'}
  'db_authorization'    {'check_authorization'}
  'db_user'             {'add_user', 'drop_user', 'find_user', 'login'}
  'db_root'             {'add_user', 'drop_user', 'find_user', 'print_authorizations', 'info', 'change_owner', 'change_trigg
r_owner', 'get_owner', 'change_sp_owner'}

_db_meth_sig

Represents configuration information of C functions on the method. An index for meth_of is created.

Attribute Name	Data Type	Description
meth_of	_db_method	Method for the function information
arg_count	INTEGER	The number of input arguments of the function
func_name	VARCHAR(255)	Function name
return_value	SEQUENCE OF _db_meth_arg	Return value of the function
arguments	SEQUENCE OF _db_meth_arg	Input arguments of the function

_db_meth_arg

Represents method argument information. An index for meth_sig_of is created.

Attribute Name	Data Type	Description
meth_sig_of	_db_meth_sig	Information of the function to which the argument belongs
data_type	INTEGER	Data type of the argument (a value in the “Value” column of the “Data Types Supported by CUBRID” in _db_attribute)
index_of	INTEGER	Order of the argument listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument.
domains	SEQUENCE OF _db_domain	Domain of the argument

_db_meth_file

Represents information of a file in which a function is defined. An index for class_of is created.

Attribute Name	Data Type	Description
class_of	_db_class	Class to which the method file information belongs
from_class_of	_db_class	If the file information is inherited, the super class in which it is defined is used otherwise, NULL
path_name	VARCHAR(255)	File path in which the method is located

_db_query_spec

Represents the SQL statement of a virtual class. An index for class_of is created.

The data type of attribute ‘spec’ is VARCHAR (4096) for prior versions including 10.1 Patch 3.

Attribute Name	Data Type	Description	Classification (10.1 Only)
class_of	_db_class	Class information of the virtual class
spec	VARCHAR(1073741823)	SQL definition statement of the virtual class	10.1 Patch 4 or later
	VARCHAR(4096)		10.1 Patch 3 or earlier

_db_index

Represents index information. An index for class_of is created.

Attribute Name	Data Type	Description
class_of	_db_class	Class to which to index belongs
index_name	varchar(255)	Index name
is_unique	INTEGER	1 if the index is unique, and 0 otherwise.
key_count	INTEGER	The number of attributes that comprise the key
key_attrs	SEQUENCE OF _db_index_key	Attributes that comprise the key
is_reverse	INTEGER	1 for a reverse index, and 0 otherwise.
is_primary_key	INTEGER	1 for a primary key, and 0 otherwise.
is_foreign_key	INTEGER	1 for a foreign key, and 0 otherwise.
filter_expression	VARCHAR(255)	The conditions of filtered indexes
have_function	INTEGER	1 for a function index, and 0 otherwise.
comment	VARCHAR (1024)	Comment to describe the index
status	INTEGER	Index status

The following example shows how to retrieve names of indexes that belong to the class.

SELECT class_of.class_name, index_name
FROM _db_index
ORDER BY 1;

  class_of.class_name   index_name
============================================
  '_db_attribute'       'i__db_attribute_class_of_attr_name'
  '_db_auth'            'i__db_auth_grantee'
  '_db_class'           'i__db_class_class_name'
  '_db_domain'          'i__db_domain_object_of'
  '_db_index'           'i__db_index_class_of'
  '_db_index_key'       'i__db_index_key_index_of'
  '_db_meth_arg'        'i__db_meth_arg_meth_sig_of'
  '_db_meth_file'       'i__db_meth_file_class_of'
  '_db_meth_sig'        'i__db_meth_sig_meth_of'
  '_db_method'          'i__db_method_class_of_meth_name'
  '_db_partition'       'i__db_partition_class_of_pname'
  '_db_query_spec'      'i__db_query_spec_class_of'
  '_db_stored_procedure'  'u__db_stored_procedure_sp_name'
  '_db_stored_procedure_args'  'i__db_stored_procedure_args_sp_name'
  'athlete'             'pk_athlete_code'
  'db_serial'           'pk_db_serial_name'
  'db_user'             'i_db_user_name'
  'event'               'pk_event_code'
  'game'                'pk_game_host_year_event_code_athlete_code'
  'game'                'fk_game_event_code'
  'game'                'fk_game_athlete_code'
  'history'             'pk_history_event_code_athlete'
  'nation'              'pk_nation_code'
  'olympic'             'pk_olympic_host_year'
  'participant'         'pk_participant_host_year_nation_code'
  'participant'         'fk_participant_host_year'
  'participant'         'fk_participant_nation_code'
  'record'              'pk_record_host_year_event_code_athlete_code_medal'
  'stadium'             'pk_stadium_code'

_db_index_key

Represents key information on an index. An index for index_of is created.

Attribute Name	Data Type	Description
index_of	_db_index	Index to which the key attribute belongs
key_attr_name	VARCHAR(255)	Name of the attribute that comprises the key
key_order	INTEGER	Order of the attribute in the key. Begins with 0.
asc_desc	INTEGER	1 if the order of attribute values is descending, and 0 otherwise.
key_prefix_length	INTEGER	Length of prefix to be used as a key
func	VARCHAR(255)	Functional expression of function based index

The following example shows how to retrieve the names of index that belongs to the class.

SELECT class_of.class_name, SEQUENCE(SELECT key_attr_name
                                     FROM _db_index_key k
                                     WHERE k in i.key_attrs)
FROM _db_index i
WHERE key_count >= 2;

  class_of.class_name   sequence((select key_attr_name from _db_index_key k where k in
i.key_attrs))
============================================
  '_db_partition'       {'class_of', 'pname'}
  '_db_method'          {'class_of', 'meth_name'}
  '_db_attribute'       {'class_of', 'attr_name'}
  'participant'         {'host_year', 'nation_code'}
  'game'                {'host_year', 'event_code', 'athlete_code'}
  'record'              {'host_year', 'event_code', 'athlete_code', 'medal'}
  'history'             {'event_code', 'athlete'}

_db_auth

Represents user authorization information of the class. An index for the grantee is created.

Attribute Name	Data Type	Description
grantor	db_user	Authorization grantor
grantee	db_user	Authorization grantee
class_of	_db_class	Class object to which authorization is to be granted
auth_type	VARCHAR(7)	Type name of the authorization granted
is_grantable	INTEGER	1 if authorization for the class can be granted to other users, and 0 otherwise.

Authorization types supported by CUBRID are as follows:

• SELECT

• INSERT

• UPDATE

• DELETE

• ALTER

• INDEX

• EXECUTE

The following example shows how to retrieve authorization information defined in the class db_trig.

SELECT grantor.name, grantee.name, auth_type
FROM _db_auth
WHERE class_of.class_name = 'db_trig';

  grantor.name          grantee.name          auth_type
==================================================================
  'DBA'                 'PUBLIC'              'SELECT'

_db_data_type

Represents the data type supported by CUBRID (see the “Data Types Supported by CUBRID” table in _db_attribute).

Attribute Name	Data Type	Description
type_id	INTEGER	Data type identifier. Corresponds to the “Value” column in the “Data Types Supported by CUBRID” table.
type_name	VARCHAR(9)	Data type name. Corresponds to the “Meaning” column in the “Data Types Supported by CUBRID” table.

The following example shows how to retrieve attributes and type names of the event class.

SELECT a.attr_name, t.type_name
FROM _db_attribute a join _db_data_type t ON a.data_type = t.type_id
WHERE class_of.class_name = 'event'
ORDER BY a.def_order;

  attr_name             type_name
============================================
  'code'                'INTEGER'
  'sports'              'STRING'
  'name'                'STRING'
  'gender'              'CHAR'
  'players'             'INTEGER'

_db_partition

Represents partition information. Indexes for class_of and pname are created.

Attribute Name	Data Type	Description
class_of	_db_class	OID of the parent class
pname	VARCHAR(255)	Parent - NULL
ptype	INTEGER	0 - HASH 1 - RANGE 2 - LIST
pexpr	VARCHAR(255)	Parent only
pvalues	SEQUENCE OF	Parent - Column name, Hash size RANGE - MIN/MAX value : - Infinite MIN/MAX is stored as NULL LIST - value list
comment	VARCHAR(1024)	Comment to describe the partition

_db_stored_procedure

Represents Java stored procedure information. An index for sp_name is created.

Attribute Name	Data Type	Description
sp_name	VARCHAR(255)	Stored procedure name
sp_type	INTEGER	Stored procedure type (function or procedure)
return_type	INTEGER	Return value type
arg_count	INTEGER	The number of arguments
args	SEQUENCE OF _db_stored_procedure_args	Argument list
lang	INTEGER	Implementation language (currently, Java)
target	VARCHAR(4096)	Name of the Java method to be executed
owner	db_user	Owner
comment	VARCHAR (1024)	Comment to describe the stored procedure

_db_stored_procedure_args

Represents Java stored procedure argument information. An index for sp_name is created.

Attribute Name	Data Type	Description
sp_name	VARCHAR(255)	Stored procedure name
index_of	INTEGER	Order of the arguments
arg_name	VARCHAR(255)	Argument name
data_type	INTEGER	Data type of the argument
mode	INTEGER	Mode (IN, OUT, INOUT)
comment	VARCHAR (1024)	Comment to describe the argument

db_user

Attribute Name	Data Type	Description
name	VARCHAR(1073741823)	User name
id	INTEGER	User identifier
password	db_password	User password. Not displayed to the user.
direct_groups	SET OF db_user	Groups to which the user belongs directly
groups	SET OF db_user	Groups to which the user belongs directly or indirectly
authorization	db_authorization	Information of the authorization owned by the user
triggers	SEQUENCE OF object	Triggers that occur due to user actions
comment	VARCHAR (1024)	Comment to describe the user

Function Names

• set_password ()

• set_password_encoded ()

• set_password_encoded_sha1 ()

• add_member ()

• drop_member ()

• print_authorizations ()

• add_user ()

• drop_user ()

• find_user ()

• login ()

db_authorization

Attribute Name	Data Type	Description
owner	db_user	User information
grants	SEQUENCE OF object	Sequence of {object for which the user has authorization, authorization grantor of the object, authorization type}

Method Name

• check_authorization (varchar(255), integer)

db_trigger

Attribute Name	Data Type	Description
owner	db_user	Trigger owner
name	VARCHAR(1073741823)	Trigger name
status	INTEGER	1 for INACTIVE, and 2 for ACTIVE. The default value is 2.
priority	DOUBLE	Execution priority between triggers. The default value is 0.
event	INTEGER	0 is set for UPDATE, 1 for UPDATE STATEMENT, 2 for DELETE, 3 for DELETE STATEMENT, 4 for INSERT, 5 for INSERT STATEMENT, 8 for COMMIT, and 9 for ROLLBACK.
target_class	object	Class object for the trigger target class
target_attribute	VARCHAR(1073741823)	Trigger target attribute name. If the target attribute is not specified, NULL* is used.
target_class_attribute	INTEGER	If the target attribute is an instance attribute, 0 is used. If it is a class attribute, 1 is used. The default value is 0.
condition_type	INTEGER	If a condition exist, 1; otherwise NULL.
condition	VARCHAR(1073741823)	Action condition specified in the IF statement
condition_time	INTEGER	1 for BEFORE, 2 for AFTER, and 3 for DEFERRED if a condition exists; NULL, otherwise.
action_type	INTEGER	1 for one of INSERT, UPDATE, DELETE, and CALL, 2 for REJECT, 3 for INVALIDATE_TRANSACTION, and 4 for PRINT.
action_definition	VARCHAR(1073741823)	Execution statement to be triggered
action_time	INTEGER	1 for BEFORE, 2 for AFTER, and 3 for DEFERRED.
comment	VARCHAR (1024)	Comment to describe the trigger

db_ha_apply_info

A table that stores the progress status every time the applylogdb utility applies replication logs. This table is updated at every point the applylogdb utility commits, and the accumulative count of operations are stored in the *_counter column. The meaning of each column is as follows:

Column Name	Column Type	Description
db_name	VARCHAR(255)	Name of the database stored in the log
db_creation_time	DATETIME	Creation time of the source database for the log to be applied
copied_log_path	VARCHAR(4096)	Path to the log file to be applied
committed_lsa_pageid	BIGINT	The page id of commit log lsa reflected last. Although applylogdb is restarted, the logs before last_committed_lsa are not reflected again.
committed_lsa_offset	INTEGER	The offset of commit log lsa reflected last. Although applylogdb is restarted, the logs before last_committed_lsa are not reflected again.
committed_rep_pageid	BIGINT	The page id of the replication log lsa reflected last. Check whether the reflection of replication has been delayed or not.
committed_rep_offset	INTEGER	The offset of the replication log lsa reflected last. Check whether the reflection of replication has been delayed or not.
append_lsa_page_id	BIGINT	The page id of the last replication log lsa at the last reflection. Saves append_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log.
append_lsa_offset	INTEGER	The offset of the last replication log lsa at the last reflection. Saves append_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log.
eof_lsa_page_id	BIGINT	The page id of the replication log EOF lsa at the last reflection. Saves eof_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log.
eof_lsa_offset	INTEGER	The offset of the replication log EOF lsa at the last reflection. Saves eof_lsa of the replication log header that is being processed by applylogdb at the time of reflecting the replication. Checks whether the reflection of replication has been delayed or not at the time of reflecting the replication log.
final_lsa_pageid	BIGINT	The pageid of replication log lsa processed last by applylogdb. Checks whether the reflection of replication has been delayed or not.
final_lsa_offset	INTEGER	The offset of replication log lsa processed last by applylogdb. Checks whether the reflection of replication has been delayed or not.
required_page_id	BIGINT	The smallest page which should not be deleted by the log_max_archives parameter. The log page number from which the replication will be reflected.
required_page_offset	INTEGER	The offset of the log page from which the replication will be reflected.
log_record_time	DATETIME	Timestamp included in replication log committed in the slave database, i.e. the creation time of the log
log_commit_time	DATETIME	The time of reflecting the last commit log
last_access_time	DATETIME	The final update time of the db_ha_apply_info catalog
status	INTEGER	Progress status (0: IDLE, 1: BUSY)
insert_counter	BIGINT	Number of times that applylogdb was inserted
update_counter	BIGINT	Number of times that applylogdb was updated
delete_counter	BIGINT	Number of times that applylogdb was deleted
schema_counter	BIGINT	Number of times that applylogdb changed the schema
commit_counter	BIGINT	Number of times that applylogdb was committed
fail_counter	BIGINT	Number of times that applylogdb failed to be inserted/updated/deleted/committed and to change the schema
start_time	DATETIME	Time when the applylogdb process accessed the slave database

dual

The dual class is a one-row, one-column table that is used as a dummy table. It is used to select a constant, expression, or pseudo column such as SYS_DATE or USER. Pseudo columns can be provided as functions in CUBRID. More details and examples are in Operators and Functions. However, it is not mandatory to have FROM clause when selecting a constant, expression, or pseudo column because dual class will be referenced automatically. Like other system catalog classes, dual class is created to be owned by dba but dba can only execute SELECT operation. Unlike other system catalog classes, however, any user can execute SELECT operation on dual class.

Attribute Name	Data Type	Description
dummy	VARCHAR(1)	Value used for dummy purpose only

The following example shows the result which ran the query that select pseudo column after inputting “;plan detail” or “SET OPTIMIZATION LEVEL 513;” in CSQL (Viewing Query Plan). This shows the dual class is referenced automatically even if there is no FROM clause.

SET OPTIMIZATION LEVEL 513;
SELECT SYS_DATE;

Join graph segments (f indicates final):
seg[0]: [0]
Join graph nodes:
node[0]: dual dual(1/1) (loc -1)

Query plan:

  sscan
    class: dual node[0]
    cost:  1 card 1

Query stmt:

select  SYS_DATE  from dual dual

=== <Result of SELECT Command in Line 1> ===

        SYS_DATE
      ============
        11/26/2020

System Catalog Virtual Class

General users can only see information of classes for which they have authorization through system catalog virtual classes. This section explains which information each system catalog virtual class represents, and virtual class definition statements.

DB_CLASS

Represents information of classes for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
class_name	VARCHAR(255)	Class name
owner_name	VARCHAR(255)	Name of class owner
class_type	VARCHAR(6)	‘CLASS’ for a class, and ‘VCLASS’ for a virtual class
is_system_class	VARCHAR(3)	‘YES’ for a system class, and ‘NO’ otherwise.
tde_algorithm	VARCHAR(32)	TDE encryption algorithm
partitioned	VARCHAR(3)	‘YES’ for a partitioned group class, and ‘NO’ otherwise.
is_reuse_oid_class	VARCHAR(3)	‘YES’ for a REUSE_OID class, and ‘NO’ otherwise.
collation	VARCHAR(32)	Collation name
comment	VARCHAR(2048)	Comment to describe the class

The following example shows how to retrieve classes owned by the current user.

SELECT class_name
FROM db_class
WHERE owner_name = CURRENT_USER;

  class_name
======================
  'stadium'
  'code'
  'nation'
  'event'
  'athlete'
  'participant'
  'olympic'
  'game'
  'record'
  'history'
  'female_event'

The following example shows how to retrieve virtual classes that can be accessed by the current user.

SELECT class_name
FROM db_class
WHERE class_type = 'VCLASS';

  class_name
======================
  'db_stored_procedure_args'
  'db_stored_procedure'
  'db_partition'
  'db_trig'
  'db_auth'
  'db_index_key'
  'db_index'
  'db_meth_file'
  'db_meth_arg_setdomain_elm'
  'db_meth_arg'
  'db_method'
  'db_attr_setdomain_elm'
  'db_attribute'
  'db_vclass'
  'db_direct_super_class'
  'db_class'

The following example shows how to retrieve system classes that can be accessed by the current user(PUBLIC user).

SELECT class_name
FROM db_class
WHERE is_system_class = 'YES' AND class_type = 'CLASS'
ORDER BY 1;

  class_name
======================
  'db_authorization'
  'db_authorizations'
  'db_ha_apply_info'
  'db_root'
  'db_serial'
  'db_user'
  'dual'

DB_DIRECT_SUPER_CLASS

Represents the names of super classes (if any) of the class for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
class_name	VARCHAR(255)	Class name
super_class_name	VARCHAR(255)	super class name

The following example shows how to retrieve super classes of the female_event class (see ADD SUPERCLASS Clause).

SELECT super_class_name
FROM db_direct_super_class
WHERE class_name = 'female_event';

  super_class_name
======================
  'event'

The following example shows how to retrieve super classes of the class owned by the current user (PUBLIC user).

SELECT c.class_name, s.super_class_name
FROM db_class c, db_direct_super_class s
WHERE c.class_name = s.class_name AND c.owner_name = user
ORDER BY 1;

  class_name            super_class_name
============================================
  'female_event'        'event'

DB_VCLASS

Represents SQL definition statements of virtual classes for which the current user has access authorization to a database.

The data type of attribute ‘vclass_def’ is VARCHAR (4096) for prior versions including 10.1 Patch 3.

Attribute Name	Data Type	Description	Classification (10.1 Only)
vclass_name	VARCHAR(255)	Virtual class name
vclass_def	VARCHAR(1073741823)	SQL definition statement of the virtual class	10.1 Patch 4 or later
	VARCHAR(4096)		10.1 Patch 3 or earlier
comment	VARCHAR(2048)	Comment to describe the virtual class

The following example shows how to retrieve SQL definition statements of the db_class virtual class.

SELECT vclass_def
FROM db_vclass
WHERE vclass_name = 'db_class';

  vclass_def
======================
  'SELECT [c].[class_name], CAST([c].[owner].[name] AS VARCHAR(255)), CASE [c].[class_type] WHEN 0 THEN 'CLASS' WHEN 1 THEN 'VCLASS' ELSE 'UNKNOW' END, CASE WHEN MOD([c].[is_system_class], 2) = 1 THEN 'YES' ELSE 'NO' END, CASE WHEN [c].[sub_classes] IS NULL THEN 'NO' ELSE NVL((SELECT 'YES' FROM [_db_partition] [p] WHERE [p].[class_of] = [c] and [p].[pname] IS NULL), 'NO') END, CASE WHEN MOD([c].[is_system_class] / 8, 2) = 1 THEN 'YES' ELSE 'NO' END FROM [_db_class] [c] WHERE CURRENT_USER = 'DBA' OR {[c].[owner].[name]} SUBSETEQ (  SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{[t].[g].[name]}), SET{})  FROM [db_user] [u], TABLE([groups]) AS [t]([g])  WHERE [u].[name] = CURRENT_USER) OR {[c]} SUBSETEQ (  SELECT SUM(SET{[au].[class_of]}) FROM [_db_auth] [au]  WHERE {[au].[grantee].[name]} SUBSETEQ (  SELECT SET{CURRENT_USER} + COALESCE(SUM(SET{[t].[g].[name]}), SET{})  FROM [db_user] [u], TABLE([groups]) AS [t]([g])  WHERE [u].[name] = CURRENT_USER) AND  [au].[auth_type] = 'SELECT')'

DB_ATTRIBUTE

Represents the attribute information of a class for which the current user has access authorization in the database.

Attribute Name	Data Type	Description
attr_name	VARCHAR(255)	Attribute name
class_name	VARCHAR(255)	Name of the class to which the attribute belongs
attr_type	VARCHAR(8)	‘INSTANCE’ for an instance attribute, ‘CLASS’ for a class attribute, and ‘SHARED’ for a shared attribute.
def_order	INTEGER	Order of attributes in the class. Begins with 0. If the attribute is inherited, the order is the one defined in the super class.
from_class_name	VARCHAR(255)	If the attribute is inherited, the super class in which it is defined is used. Otherwise, NULL.
from_attr_name	VARCHAR(255)	If the attribute is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used. Otherwise, NULL.
data_type	VARCHAR(9)	Data type of the attribute (one in the “Meaning” column of the “Data Types Supported by CUBRID” table in _db_attribute)
prec	INTEGER	Precision of the data type. 0 is used if the precision is not specified.
scale	INTEGER	Scale of the data type. 0 is used if the scale is not specified.
charset	VARCHAR (32)	charset name
collation	VARCHAR (32)	collation name
domain_class_name	VARCHAR(255)	Domain class name if the data type is an object. NULL otherwise.
default_value	VARCHAR(255)	Saved as a character string by default, regardless of data types. If no default value is specified, NULL is stored. If a default value is NULL, it is displayed as ‘NULL’. An object data type is represented as ‘volume id | page id | slot id’ while a set data type is represented as ‘{element 1, element 2, … }’.
is_nullable	VARCHAR(3)	‘NO’ if a not null constraint is set, and ‘YES’ otherwise.
comment	VARCHAR(1024)	Comment to describe the attribute.

The following example shows how to retrieve attributes and data types of the event class.

SELECT attr_name, data_type, domain_class_name
FROM db_attribute
WHERE class_name = 'event'
ORDER BY def_order;

  attr_name             data_type             domain_class_name
==================================================================
  'code'                'INTEGER'             NULL
  'sports'              'STRING'              NULL
  'name'                'STRING'              NULL
  'gender'              'CHAR'                NULL
  'players'             'INTEGER'             NULL

The following example shows how to retrieve attributes of the female_event class and its super class.

SELECT attr_name, from_class_name
FROM db_attribute
WHERE class_name = 'female_event'
ORDER BY def_order;

  attr_name             from_class_name
============================================
  'code'                'event'
  'sports'              'event'
  'name'                'event'
  'gender'              'event'
  'players'             'event'

The following example shows how to retrieve classes whose attribute names are similar to name, among the ones owned by the current user. (The user is PUBLIC.)

SELECT a.class_name, a.attr_name
FROM db_class c join db_attribute a ON c.class_name = a.class_name
WHERE c.owner_name = CURRENT_USER AND attr_name like '%name%'
ORDER BY 1;

  class_name            attr_name
============================================
  'athlete'             'name'
  'code'                'f_name'
  'code'                's_name'
  'event'               'name'
  'female_event'        'name'
  'nation'              'name'
  'stadium'             'name'

DB_ATTR_SETDOMAIN_ELM

Among attributes of the class to which the current user has access authorization in the database, if an attribute’s data type is a collection (SET, MULTISET, SEQUENCE), this macro represents the data type of the element of the collection.

Attribute Name	Data Type	Description
attr_name	VARCHAR(255)	Attribute name
class_name	VARCHAR(255)	Name of the class to which the attribute belongs
attr_type	VARCHAR(8)	‘INSTANCE’ for an instance attribute, ‘CLASS’ for a class attribute, and ‘SHARED’ for a shared attribute.
data_type	VARCHAR(9)	Data type of the element
prec	INTEGER	Precision of the data type of the element
scale	INTEGER	Scale of the data type of the element
code_set	INTEGER	Character set if the data type of the element is a character
domain_class_name	VARCHAR(255)	Domain class name if the data type of the element is an object

If the set_attr attribute of class D is of a SET (A, B, C) type, the following three records exist.

Attr_name	Class_name	Attr_type	Data_type	Prec	Scale	Code_set	Domain_class_name
‘set_attr’	‘D’	‘INSTANCE’	‘SET’	0	0	0	‘A’
‘set_attr’	‘D’	‘INSTANCE’	‘SET’	0	0	0	‘B’
‘set_attr’	‘D’	‘INSTANCE’	‘SET’	0	0	0	‘C’

The following example shows how to retrieve collection type attributes and data types of the city class (the city table defined in Containment Operators is created).

SELECT attr_name, attr_type, data_type, domain_class_name
FROM db_attr_setdomain_elm
WHERE class_name = 'city';

  attr_name             attr_type             data_type             domain_class_name
==============================================================================

'sports'              'INSTANCE'            'STRING'              NULL

DB_CHARSET

Represents charset information.

Attribute name	Data type	Description
charset_id	INTEGER	Charset ID
charset_name	CHARACTER VARYING(32)	Charset name
default_collation	CHARACTER VARYING(32)	Default collation name
char_size	INTEGER	One character’s byte size

DB_COLLATION

The information on collation.

Attribute Name	Data Type	Description
coll_id	INTEGER	Collation ID
coll_name	VARCHAR(255)	Collation name
charset_name	VARCHAR(255)	Charset name
is_builtin	VARCHAR(3)	Built-in or not while installing the product(Yes, No)
has_expansions	VARCHAR(3)	Having expansion or not(Yes, No)
contractions	INTEGER	Whether to include abbreviation
uca_strength	VARCHAR(255)	Weight strength (Not applicable, Primary, Secondary, Tertiary, Quaternary, Identity, Unknown)

DB_METHOD

Represents method information of a class for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
meth_name	VARCHAR(255)	Method name
class_name	VARCHAR(255)	Name of the class to which the method belongs
meth_type	VARCHAR(8)	‘INSTANCE’ for an instance method, and ‘CLASS’ for a class method.
from_class_name	VARCHAR(255)	If the method is inherited, the super class in which it is defined is used otherwise NULL
from_meth_name	VARCHAR(255)	If the method is inherited and its name is changed to resolve a name conflict, the original name defined in the super class is used otherwise NULL
func_name	VARCHAR(255)	Name of the C function for the method

The following example shows how to retrieve methods of the db_user class.

SELECT meth_name, meth_type, func_name
FROM db_method
WHERE class_name = 'db_user'
ORDER BY meth_type, meth_name;

  meth_name             meth_type             func_name
==================================================================
  'add_user'            'CLASS'               'au_add_user_method'
  'drop_user'           'CLASS'               'au_drop_user_method'
  'find_user'           'CLASS'               'au_find_user_method'
  'login'               'CLASS'               'au_login_method'
  'add_member'          'INSTANCE'            'au_add_member_method'
  'drop_member'         'INSTANCE'            'au_drop_member_method'
  'print_authorizations'  'INSTANCE'            'au_describe_user_method'
  'set_password'        'INSTANCE'            'au_set_password_method'
  'set_password_encoded'  'INSTANCE'            'au_set_password_encoded_method'
  'set_password_encoded_sha1'  'INSTANCE'            'au_set_password_encoded_sha1_method'

DB_METH_ARG

Represents the input/output argument information of the method of the class for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
meth_name	VARCHAR(255)	Method name
class_name	VARCHAR(255)	Name of the class to which the method belongs
meth_type	VARCHAR(8)	‘INSTANCE’ for an instance method, and ‘CLASS’ for a class method.
index_of	INTEGER	Order in which arguments are listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument.
data_type	VARCHAR(9)	Data type of the argument
prec	INTEGER	Precision of the argument
scale	INTEGER	Scale of the argument
code_set	INTEGER	Character set if the data type of the argument is a character.
domain_class_name	VARCHAR(255)	Domain class name if the data type of the argument is an object.

The following example shows how to retrieve input arguments of the method of the db_user class.

SELECT meth_name, data_type, prec
FROM db_meth_arg
WHERE class_name = 'db_user';

  meth_name             data_type                    prec
=========================================================
  'append_data'         'STRING'               1073741823

DB_METH_ARG_SETDOMAIN_ELM

If the data type of the input/output argument of the method of the class is a set, for which the current user has access authorization in the database, this macro represents the data type of the element of the set.

Attribute Name	Data Type	Description
meth_name	VARCHAR(255)	Method name
class_name	VARCHAR(255)	Name of the class to which the method belongs
meth_type	VARCHAR(8)	‘INSTANCE’ for an instance method, and ‘CLASS’ for a class method.
index_of	INTEGER	Order of arguments listed in the function definition. Begins with 0 if it is a return value, and 1 if it is an input argument.
data_type	VARCHAR(9)	Data type of the element
prec	INTEGER	Precision of the element
scale	INTEGER	Scale of the element
code_set	INTEGER	Character set if the data type of the element is a character
domain_class_name	VARCHAR(255)	Domain class name if the data type of the element is an object

DB_METH_FILE

Represents information of a file in which the method of the class for which the current user has access authorization in the database is defined.

Attribute Name	Data Type	Description
class_name	VARCHAR(255)	Name of the class to which the method file belongs
path_name	VARCHAR(255)	File path in which the C function is defined
from_class_name	VARCHAR(255)	Name of the super class in which the method file is defined if the method is inherited, and otherwise NULL

DB_INDEX

Represents information of indexes created for the class for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
index_name	VARCHAR(255)	Index name
is_unique	VARCHAR(3)	‘YES’ for a unique index, and ‘NO’ otherwise.
is_reverse	VARCHAR(3)	‘YES’ for a reversed index, and ‘NO’ otherwise.
class_name	VARCHAR(255)	Name of the class to which the index belongs
key_count	INTEGER	The number of attributes that comprise the key
is_primary_key	VARCHAR(3)	‘YES’ for a primary key, and ‘NO’ otherwise.
is_foreign_key	VARCHAR(3)	‘YES’ for a foreign key, and ‘NO’ otherwise.
filter_expression	VARCHAR(255)	Conditions of filtered indexes
have_function	VARCHAR(3)	‘YES’ for function based and ‘NO’ otherwise.
comment	VARCHAR(1024)	Comment to describe the index

The following example shows how to retrieve index information of the class.

SELECT class_name, index_name, is_unique
FROM db_index
ORDER BY 1;

  class_name            index_name            is_unique
==================================================================
  'athlete'             'pk_athlete_code'     'YES'
  'city'                'pk_city_city_name'   'YES'
  'db_serial'           'pk_db_serial_name'   'YES'
  'db_user'             'i_db_user_name'      'NO'
  'event'               'pk_event_code'       'YES'
  'female_event'        'pk_event_code'       'YES'
  'game'                'pk_game_host_year_event_code_athlete_code'  'YES'
  'game'                'fk_game_event_code'  'NO'
  'game'                'fk_game_athlete_code'  'NO'
  'history'             'pk_history_event_code_athlete'  'YES'
  'nation'              'pk_nation_code'      'YES'
  'olympic'             'pk_olympic_host_year'  'YES'
  'participant'         'pk_participant_host_year_nation_code'  'YES'
  'participant'         'fk_participant_host_year'  'NO'
  'participant'         'fk_participant_nation_code'  'NO'
  'record'              'pk_record_host_year_event_code_athlete_code_medal'  'YES'
  'stadium'             'pk_stadium_code'     'YES'
...

DB_INDEX_KEY

Represents the key information of indexes created for the class for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
index_name	VARCHAR(255)	Index name
class_name	VARCHAR(255)	Name of the class to which the index belongs
key_attr_name	VARCHAR(255)	Name of attributes that comprise the key
key_order	INTEGER	Order of attributes in the key. Begins with 0.
asc_desc	VARCHAR(4)	‘DESC’ if the order of attribute values is descending, and ‘ASC’ otherwise.
key_prefix_length	INTEGER	The length of prefix to be used as a key
func	VARCHAR(255)	Functional expression of function based index

The following example shows how to retrieve index key information of the class.

SELECT class_name, key_attr_name, index_name
FROM db_index_key
ORDER BY class_name, key_order;

 'athlete'             'code'                'pk_athlete_code'
 'city'                'city_name'           'pk_city_city_name'
 'db_serial'           'name'                'pk_db_serial_name'
 'db_user'             'name'                'i_db_user_name'
 'event'               'code'                'pk_event_code'
 'female_event'        'code'                'pk_event_code'
 'game'                'host_year'           'pk_game_host_year_event_code_athlete_code'
 'game'                'event_code'          'fk_game_event_code'
 'game'                'athlete_code'        'fk_game_athlete_code'
...

DB_AUTH

Represents authorization information of classes for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
grantor_name	VARCHAR(255)	Name of the user who grants authorization
grantee_name	VARCHAR(255)	Name of the user who is granted authorization
class_name	VARCHAR(255)	Name of the class for which authorization is to be granted
auth_type	VARCHAR(7)	Name of the authorization type granted
is_grantable	VARCHAR(3)	‘YES’ if authorization for the class can be granted to other users, and ‘NO’ otherwise.

The following example how to retrieve authorization information of the classes whose names begin with db_a.

SELECT class_name, auth_type, grantor_name
FROM db_auth
WHERE class_name like 'db_a%'
ORDER BY 1;

  class_name            auth_type             grantor_name
==================================================================
  'db_attr_setdomain_elm'  'SELECT'             'DBA'
  'db_attribute'           'SELECT'             'DBA'
  'db_auth'                'SELECT'             'DBA'
  'db_authorization'       'EXECUTE'            'DBA'
  'db_authorization'       'SELECT'             'DBA'
  'db_authorizations'      'EXECUTE'            'DBA'
  'db_authorizations'      'SELECT'             'DBA'

DB_TRIG

Represents information of a trigger that has the class for which the current user has access authorization to a database, or its attribute as the target.

Attribute Name	Data Type	Description
trigger_name	VARCHAR(255)	Trigger name
target_class_name	VARCHAR(255)	Target class
target_attr_name	VARCHAR(255)	Target attribute. If not specified in the trigger, NULL
target_attr_type	VARCHAR(8)	Target attribute type. If specified, ‘INSTANCE’ is used for an instance attribute, and ‘CLASS’ is used for a class attribute.
action_type	INTEGER	1 for one of INSERT, UPDATE, DELETE, and CALL, 2 for REJECT, 3 for INVALIDATE_TRANSACTION, and 4 for PRINT.
action_time	INTEGER	1 for BEFORE, 2 for AFTER, and 3 for DEFERRED.
comment	VARCHAR(1024)	Comment to describe the trigger.

DB_PARTITION

Represents information of partitioned classes for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
class_name	VARCHAR(255)	Class name
partition_name	VARCHAR(255)	Partition name
partition_class_name	VARCHAR(255)	Partitioned class name
partition_type	VARCHAR(32)	Partition type (HASH, RANGE, LIST)
partition_expr	VARCHAR(255)	Partition expression
partition_values	SEQUENCE OF	RANGE - MIN/MAX value - For infinite MIN/MAX, NULL LIST - value list
comment	VARCHAR(1024)	Comment to describe the partition

The following example shows how to retrieve the partition information currently configured for the participant2 class.

SELECT * from db_partition where class_name = 'participant2';

  class_name            partition_name        partition_class_name         partition_type   partition_expr        partition_values
====================================================================================================================================
  'participant2'        'before_2000'         'participant2__p__before_2000'  'RANGE'       'host_year'           {NULL, 2000}
  'participant2'        'before_2008'         'participant2__p__before_2008'  'RANGE'       'host_year'           {2000, 2008}

DB_STORED_PROCEDURE

Represents information of Java stored procedure for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
sp_name	VARCHAR(255)	Stored procedure name
sp_type	VARCHAR(16)	Stored procedure type (function or procedure)
return_type	VARCHAR(16)	Return value type
arg_count	INTEGER	The number of arguments
lang	VARCHAR(16)	Implementing language (currently, Java)
target	VARCHAR(4096)	Name of the Java method to be executed
owner	VARCHAR(256)	Owner
comment	VARCHAR(1024)	Comment to describe the stored procedure

The following example shows how to retrieve Java stored procedures owned by the current user.

SELECT sp_name, target from db_stored_procedure
WHERE sp_type = 'FUNCTION' AND owner = CURRENT_USER;

  sp_name               target
============================================
  'hello'               'SpCubrid.HelloCubrid() return java.lang.String'
  'sp_int'              'SpCubrid.SpInt(int) return int'

DB_STORED_PROCEDURE_ARGS

Represents argument information of Java stored procedure for which the current user has access authorization to a database.

Attribute Name	Data Type	Description
sp_name	VARCHAR(255)	Stored procedure name
index_of	INTEGER	Order of the arguments
arg_name	VARCHAR(256)	Argument name
data_type	VARCHAR(16)	Data type of the argument
mode	VARCHAR(6)	Mode (IN, OUT, INOUT)
comment	VARCHAR(1024)	Comment to describe the argument

The following example shows how to retrieve arguments the ‘phone_info’ Java stored procedure in the order of the arguments.

SELECT index_of, arg_name, data_type, mode
FROM db_stored_procedure_args
WHERE sp_name = 'phone_info'
ORDER BY index_of;

     index_of  arg_name              data_type             mode
===============================================================
            0  'name'                'STRING'              'IN'
            1  'phoneno'             'STRING'              'IN'

Catalog Class/Virtual Class Authorization

Catalog classes are created to be owned by dba. However, dba can only execute SELECT operations. If dba executes operations such as UPDATE / DELETE, an authorization failure error occurs. General users cannot execute queries on system catalog classes.

Although catalog virtual classes are created to be owned by dba, all users can perform the SELECT statement on catalog virtual classes. Of course, UPDATE / DELETE operations on catalog virtual classes are not allowed.

Updating catalog classes/virtual classes is automatically performed by the system when users execute a DDL statement that creates/modifies/deletes a class/attribute/index/user/authorization.

Querying on Catalog

To query on catalog classes, you must convert identifiers such as class, virtual class, attribute, trigger, method and index names to lowercases, and create them. Therefore, you must use lowercases when querying on catalog classes. But, DB user name is changed as uppercases and stored into db_user system catalog table.

CREATE TABLE Foo(name varchar(255));
SELECT class_name, partitioned FROM db_class WHERE class_name = 'Foo';

There are no results.

SELECT class_name, partitioned FROM db_class WHERE class_name = 'foo';

  class_name   partitioned
============================
  'foo'       'NO'

CREATE USER tester PASSWORD 'testpwd';
SELECT name, password FROM db_user;

  name                  password
============================================
  'DBA'                 NULL
  'PUBLIC'              NULL
  'TESTER'              db_password