DB_CLASS

Represents information of the classes for which the current user has access authorization in the 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.
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.

Definition

CREATE VCLASS db_class (class_name, owner_name, class_type, is_system_class, partitioned, is_reuse_oid_class)

AS

 

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');

Example 1

The following is an example of retrieving 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'

Note All examples of system catalog classes have been written in the csql utility. In this example, the user option is omitted (if omitted, the default user is PUBLIC). If not otherwise specified, --no-auto-commit (No auto-commit mode) and -u (Specify the user dba) options are used.

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

Example 2

The following is an example of retrieving 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 is an example of retrieving system classes that can be accessed by the current user 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_root'

  'db_serial'

  'db_user'