DBMSINFO Function
DBMSINFO is a SQL function that returns a string containing information about the current session. Use this function in a terminal monitor or in an embedded SQL application.
The DBMSINFO function is used in a SELECT statement as follows:
SELECT DBMSINFO('request_name')
where 'request_name' is one of those described in Request Names for DBMSINFO Function.
Dbmsinfo Examples
To see the version of Vector runtime you are using, enter:
SELECT DBMSINFO('_VERSION');
The DBMSINFO function can be used in WHERE clauses in SELECT statements. For example:
EXEC SQL SELECT dept FROM employee
WHERE ename=DBMSINFO('USERNAME');
Request Names for DBMSINFO Function
Valid request_names for the DBMSINFO function are as follows:
autocommit_state
Returns 1 if autocommit is on and 0 if autocommit is off.
_bintim
Returns the current time and date in an internal format, represented as the number of seconds since January 1, 1970 00:00:00 GMT.
_bio_cnt
Returns the number of I/Os to and from the front-end client (application) that created your session.
cache_dynamic
Returns Y if cache_dynamic is on; otherwise returns N.
charset
Returns the value of II_CHARSETxx, the character set setting for the installation.
collation
Returns the collation sequence defined for the database associated with the current session. This returns blanks if the database is using the collation sequence of the machine’s native character set, such as ASCII or EBCDIC.
connect_time_limit
Returns the session connect time limit or -1 if there is no connect time limit.
create_procedure
Returns Y if the session has create_procedure privileges in the database or N if the session does not.
create_table
Returns Y if the session has create_table privileges in the database or N if the session does not.
_cpu_ms
Returns the CPU time for the session in milliseconds.
current_priv_mask
Returns the decimal number representing a mask of internal privilege bits currently enabled for the user.
cursor_default_mode
Returns the default mode for the cursor.
cursor_limit
Returns the maximum number of cursors that a session may have open at one time.
cursor_update_mode
Returns the mode of the current user.
database
Returns the database name.
datatype_major_level
Returns -2147483648 unless the user data types are in use.
datatype_minor_level
Returns 0 unless the user data types are in use.
date_format
Returns the current date format setting (set on II_DATE_FORMAT or by the SET statement).
date_type_alias
Returns the value of the date_alias parameter for your session: INGRESDATE, ANSIDATE or NONE.
dba
Returns the user name of the database owner.
db_admin
Returns Y if the session has db_admin privileges, and N if the session does not have db_admin privileges.
db_cluster_node
Returns the machine you are connected to. Valid even if not clustered.
db_count
Returns the number of distinct databases opened.
db_real_user_case
Returns lower, upper, or mixed.
dbms_bio
Returns the cumulative non-disk I/O's performed by the server hosting session.
dbms_cpu
Returns the cumulative CPU time for the DBMS Server, in milliseconds, for all connected sessions.
dbms_dio
Returns the cumulative disk I/O's performed by the server hosting session.
db_delimited_case
Returns LOWER if delimited identifiers are translated to lower case, UPPER if delimited identifiers are translated to upper case, or MIXED if the case of delimited identifiers is not translated.
db_name_case
Returns LOWER if regular identifiers are translated to lower case or UPPER if regular identifiers are translated to upper case.
db_privileges
Returns a decimal integer which represents a bit mask of Subject privileges.
db_tran_id
Returns the 64 bit internal transaction ID as two decimal numbers.
decimal_format
Returns the current decimal format setting (set on II_DECIMAL or by the SET statement) for the session.
_dio_cnt
Returns the number of disk I/O requests for your session.
_et_sec
Returns the elapsed time since the start of your session, in seconds.
flatten_aggregate
Returns Y if the DBMS Server is configured to flatten queries involving aggregate subselects; otherwise, returns N. (Query flattening options are specified when the DBMS Server is started.)
flatten_singleton
Returns Y if the DBMS Server is configured to flatten queries involving singleton subselects; otherwise, returns N. (Query flattening options are specified when the DBMS Server is started.)
group
Returns the group identifier of the session or blanks if no group identifier is in effect.
idle_time_limit
Returns the session idle time limit or -1 if there is no idle time limit.
ima_server
Equivalent to IMA registration exp.gwf.gwm.glb.this_server, which returns the listen address of the attached server.
ima_session
Returns the internal session ID in decimal format.
ima_vnode
Equivalent to IMA registration exp.gwf.gwm.glb.def_vnode configuration value if set for the connected server. If not set, defaults to the local host name.
initial_user
Returns the user identifier in effect at the start of the session.
language
Returns the language used in the current session to display messages and prompts.
lock_wait
Returns the cumulative time in milliseconds that a session has spent waiting for locks.
lockmode
Returns Y if the user possesses lockmode database privileges or N if the user lacks these privileges.
lp64
Returns Y if 64 bit pointers are in use, or N if 32 bit addresses are used.
maxconnect
Returns the current connect time limit, as set by the set maxconnect statement, or the initial value if no connect time limit has been set.
maxcost
Returns the value specified in the last set maxcost statement. If no previous set maxcost statement was issued or if set nomaxcost was specified last, this returns the same value as the request name query_io_limit.
maxcpu
Returns the value specified in the last set maxcpu statement. If no previous set maxcpu statement was issued or if set nomaxcpu was specified last, this returns the same value as the request name query_io_limit.
maxidle
Returns the current idle time limit, as set with the set maxidle statement, or the initial value if no idle time limit has been set.
maxio
Returns the value specified in the last set maxio statement. If no previous set maxio statement was issued or if set nomaxio was specified last, this returns the same value as the request name query_io_limit.
maxquery
Same as maxio.
maxrow
Returns the value specified in the last set maxrow statement. If no previous set maxrow statement was issued or if set nomaxrow was specified last, this returns the same value as the request name query_row_limit.
maxpage
Returns the value specified in the last set maxpage statement. If no previous set maxpage statement was issued or if set nomaxpage was specified last, this returns the same value as the request name query_io_limit.
max_page_size
Returns the size of the largest enable page cache in bytes.
max_priv_mask
Returns the decimal number representing a mask of internal privilege bits for which privileges the user might possess if all his/her privileges were enabled.
max_tup_len
Returns the maximum width for a tuple that does not span pages. This depends on max_page_size.
money_format
Returns the current money format setting (set on II_MONEY_FORMAT or by the SET statement).
money_prec
Returns the current money precision setting (set on II_MONEY_PREC or by the SET statement).
on_error_state
Returns the current setting for transaction error handling: rollback transaction or rollback statement. To set transaction error handling, use the set session with on_error statement.
open_count
Returns the number of times the database was opened.
page_size_2k
Returns Y if this size cache is enabled.
page_size_4k
Returns Y if this size cache is enabled.
page_size_8k
Returns Y if this size cache is enabled.
page_size_16k
Returns Y if this size cache is enabled.
page_size_32k
Returns Y if this size cache is enabled.
page_size_64k
Returns Y if this size cache is enabled.
pagetype_v1
Returns Y if this page type is supported.
pagetype_v2
Returns Y if this page type is supported.
pagetype_v3
Returns Y if this page type is supported.
pagetype_v4
Returns Y if this page type is supported.
pagetype_v5
Returns Y if this page type is supported.
partition_spec_required
Returns VECTOR if a partitioning specification is required on CREATE TABLE; otherwise NONE.
_patch
Returns the current patch number.
_pfault_cnt
Returns the number of page faults for the server.
query_cost_limit
Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_cpu_limit
Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_flatten
Returns Y if the query flattening is in effect or N if the query flattening is not in effect.
query_io_limit
Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_language
Returns sql or quel.
query_page_limit
Returns the session value for query_io_limit or -1 if no limit is defined for the session.
query_row_limit
Returns the session value for query_row_limit or -1 if no limit is defined for the session.
role
Returns the role identifier of the session or blanks if no role identifier is in effect.
security_audit_log
Returns the name of the current security auditing log file if it is enabled and the user has maintain_audit privileges, otherwise, remains blank.
security_audit_state
Returns the current security audit state. The following values are returned:
(blank) – Security auditing is not available
STOP – Security auditing is stopped
SUSPEND – Security auditing is suspended
ACTIVE – Security auditing is active
security_priv
Returns Y if the effective user has the security privilege or N if the effective user does not have the security privilege.
select_syscat
Returns Y if the session has select_syscat privilege or N if the session does not have select_syscat privilege.
server_class
Returns the server class for the session.
server_class_desc
Returns a description of the server class: RMS Gateway Server, Ingres Star Server, or Ingres DBMS Server.
session_id
Returns the internal session identifier in hexadecimal.
session_priority
Returns the current session priority.
session_priority_limit
Returns the highest session priority that can be set, or an empty string if no session priority limit applies.
session_schema
Returns the current schema name for the session.
session_user
Returns the current effective user ID of the session.
string_truncation
Returns the current setting for this session.
system_user
Returns the system user ID.
terminal
Returns the terminal address.
timeout_abort
Returns Y or N, indicating that the database privilege GRANT TIMEOUT_ABORT is either enabled or disabled for the session.
transaction_state
Returns 1 if currently in a transaction and returns 0 if not currently in a transaction.
tup_len_2k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_4k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_8k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_16k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_32k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
tup_len_64k
Returns the largest tuple for this page size or returns 0 if the cache for this page size is turned off.
ucollation
Returns Unicode collation. The default is "udefault."
unicode_level
Returns the Unicode level for this database. Returns 0 if there is no Unicode level, otherwise returns 1 if the database is created with ‑i or ‑n flag.
unicode_normalization
Returns blank if the database does not support Unicode or does not perform normalization. Returns NFC if the database supports Normalization Form C. Returns NFD if the database supports Normalization Form D.
update_rowcnt
Returns qualified if inquire_sql(rowcount) returns the number of rows that qualified for change by the last query, or changed if inquire_sql(rowcount) returns the number of rows that were actually changed by the last query.
update_syscat
Returns Y if the effective user is allowed to update system catalogs or N if the effective user is not allowed to update system catalogs.
username
Returns the user name of the current user.
_version
Returns the Vector runtime number.
wlm_audit
Returns Y if workload management auditing is enabled, N if not.
wlm_config
Returns the name of the active workload management configuration.
wlm_enabled
Returns Y is workload management is enabled, N if not.
x100_host
Returns the IP address of the node running the x100 server process.