Informix system table

------------------------------------------

The sysusers system directory table describes each set of permissions granted in the database. Each user with permissions in the database should have a row.

The sysusers system directory table includes the following table.

Username nchar (8) Database User Name or role name.
Usertype nchar (1) specifies the database-level permissions:
D = Database Administrator (all permissions)
R = Resource (creating permanent tables and indexes)
G = role
C = join (used in the existing table)
Priority smallint reserved for future use
Password char (8) is reserved for future use

The username column has an index and can only be a unique value. Username can be the role name.

Select * From sysusers;

 

 

------------------------------------------

Ables: a large table in the database;
Syscolumns: Describes the columns of tables in the database;
Sysindexes: Describes the indexes of columns in the database;
Sysfragments: stores segment information of a segmentation index;
Sysfragauth: column-level permission for table recognition;
Sysviews: describes each view defined in the database;
Sysdepend: describes how a view depends on other views and tables;
Syssyntable: defines each synonym and its objects;
Sysconstraints: records the constraints loaded on Database columns;
Sysreferences: lists the reference constraints placed on the database. It creates a row for each reference constraint of the database.
Syscoldepend: records all columns that involve check constraints, including creating a row in the syscoldepend table for each column in the constraint;
Sysprocedures: stores the features of each process in the database;
Sysprocplan: loads two items (Execution Plan, query plan, and ancillary list) required for the execution of the process );
Sysprocauth: Describes the permissions granted to stored procedures;
Systriggers: information about the trigger is loaded;
Sysblobs: determines the storage location of Blob columns;
Sysroleauth: description of the role granted to the user;
Sysobjstate: stores state information about database objects;
 

------------------------------------------

 

Structure of Informix main system tables and meanings of related fields

This section describes the structure of Informix system tables and the meanings of related fields.

Note:
I. instance information
* Sysconfig ---->; onconfig File
* Sysprofile ---->; server statistics
* Syslog ---->; logical log
* Sysvpprof ---->; virtual processor
Create Table "sysmaster". sysconfig {server configuration parameters}
(
Cf_id integer, {unique digit identifier}
Cf_name char (18), {onconfig parameter name}
Cf_flags integer, {flag, 0 = in view}
Cf_original char (256), {value in onconfig at startup}
Cf_effective char (256), {actually used value}
Cf_default char (256) {default value used when the onconfig file is not specified}
);
Grant select on sysconfig to public;

Create Table "sysmaster". sysprofile {server profile information}
(
Name char (13), {profile element name}
Value INTEGER {current value}
);
Grant select on sysprofile to public;

Create Table "sysmaster". syslogs {logical log information}
(
Number smallint, {log file number}
Uniqid integer, {unique ID of the log file}
Size integer, {Log File page}
Used integer, {Log File page}
Is_used integer, {1 is used, 0 is not used}
Is_current integer, {1 is current}
Is_backed_up integer, {1 is backup}
Is_new integer, {1 is new}
Is_archived integer, {1 is an archive}
Is_temp integer, {1 is temporary}
Flags smallint {Log File flag}
);
Grant select on syslogs to public;

Create Table "sysmaster". sysvpprof {virtual processor Information and Statistics}
(
Vpid integer, {virtual processor}
Class char (50), {virtual processor Class Name (CPU, ADM, Lio, Pio, etc )}
Usercpu float, {user time UNIX seconds}
Syscpu float {system time UNIX seconds}
);
Grant select on sysvpprof to public;

 

2. dbspace and block information
* Sysdbspaces ---->; dbspace
* Syschunks ---->; Block
* Syschkio ---->; block I/O
* Syschfree ---->; block Free Space Note: syschfree is an unsupported table.

Create Table "sysmaster". sysdbspaces {dbspace configuration}
(
Dbsnum smallint, {dbspace number}
Name char (18), {dbspace name}
Owner char (8), {dbspace owner}
Fchunk smallint, {First dbspace}
Nchunks smallint, {Number of dbspace blocks}
Is_mirrored integer, {whether dbspace image 1 = Yes, 0 = No}
Is_blobspace integer, {whether dbspace is a large object space}
Is_temp integer, {whether dbspace is temporary, 1 = Yes, 0 = No}
Flags smallint {dbspace flag}
);
Grant select on sysdbspaces to public;

Create Table "sysmaster". syschunks {chunk configuration}
(
Chknum smallint, {chunk number}
Dbsnum smallint, {dbspace number}
Nxchknum smallint, {next block number in dbspace}
Chksize integer, {page in chunk}
Offset integer, {Device page offset}
Nfree integer, {number of unused pages in the block}
Is_offline integer, {whether the chunk is offline, 1 = Yes, 0 = No}
Is_recovering integer, {whether the chunk is restored, 1 = Yes, 0 = No}
Is_blobchunk integer, {whether Chunk is blobchunk, 1 = Yes, 0 = No}
Is_inconsistent integer, {inconsistent chunk, 1 = Yes, 0 = No}
Flags smallint, {block flag converted by bitbal}
Fname char (128), {Device path name}
Mfname char (128), {image device path name}
Moffset integer, {image device offset}
Mis_offline integer, {whether the image is offline, 1 = Yes, 0 = No}
Mis_recovering integer, {whether the image is restored, 1 = Yes, 0 = No}
Mflags smallint {image block flag}
);
Grant select on syschunks to public;

Create Table "sysmaster". syschfree {displays unused space blocks in blocks}
(
Chknum integer, {chunk number block number}
Extnum integer, {extent number in chunk block region number}
Start integer, {physical ADDR of start physical address}
Leng INTEGER {length of extent region length}
);
Create unique index syschfreeidx on syschfree (chknum, extnum );
Revoke all on syschfree from public;
Grant select on syschfree to public;

Create Table "sysmaster". syschkio {block device I/O statistics}
(
Chunknum smallint, {block number}
Reads integer, {read Operations}
Pagesread integer, {Number of read pages}
Writes integer, {write operations}
Pageswritten integer, {Number of write pages}
Mreads integer, {image read Operations}
Mpagesread integer, {Number of image read pages}
Mwrites integer, {image write operations}
Mpageswritten INTEGER {Number of image writes}
);
Grant select on syschkio to public;

Iii. Database and table information
* Sysdatabases ---->; Database
* Tables abnames ---->; Table
* Sysextents ---->; Table Area
* Sysptprof ---->; Table I/O

Create Table "sysmaster". sysdatabase {all database information in the instance}
(
Name char (18), {database name}
Partnum integer, {partition Ables table ID}
Owner char (8), {user name of the creator}
Created integer, {generation date}
Is_logging integer, {No buffer log, 1 = Yes, 0 = No}
Is_buff_log integer, {buffer log, 1 = Yes, 0 = No}
Is_ansi integer, {ANSI database 1 = Yes, 0 = No}
Is_nls integer, {NLS supported, 1 = Yes, 0 = No}
Flags smallint {indicates the log flag}
);
Grant select on sysdatabase to public;

Create Table "sysmaster". systabnames {all tables in the instance}
(
Partnum integer, {table ID for table number}
Dbsname char (18), {database name}
Owner char (8), {table owner}
Tabname char (18), {table name}
Collate char (32) {correlation between collation assoc with database and Gls}
);
Create unique index using abs_pnix on creating abnames (partnum );
Revoke all on datagabnames from public;
Grant select on temporary abnames to public;

Create Table "sysmaster". sysextents {tables in the instance and each region}
(
Dbsname char (18), {database name}
Tabname char (18), {table name}
Start integer, {physical address of this region}
Size INTEGER {length of the Region (page number )}
);
Grant select on sysextents to public;

Create Table "sysmaster". sysptnext {region information}
(
Pe_partnum integer, {partnum for this partition ID of this region}
Pe_extnum smallint, {extent number region number}
Pe_phys integer, {physical address of the physical ADDR for this extent region}
Pe_size integer, {size of this extent length (number of pages )}
Pe_log INTEGER {logical page for start logical page}
);
Create unique index sysptnextidx on sysptnext (pe_partnum, pe_extnum );
Revoke all on sysptnext from public;
Grant select on sysptnext to public;

Create Table "sysmaster". sysptprof {table I/O overview}
(
Dbsname char (18), {database name}
Tabname char (18), {table name}
Partnum integer, {table number}
Lockreqs integer, {lock request}
Lockwts integer, {lock wait}
Deadlks integer, {deadlock}
Lktouts integer, {lock timeout}
Isreads integer, {read}
Iswrites integer, {write}
Isrewrites integer, {rewrite}
Isdeletes integer, {Delete}
Bufreads integer, {buffer read}
Bufwrites integer, {row-based write}
Seqscans integer, {Sequential Scan}
Pagreads integer, {disk read}
Pagwrites INTEGER {disk write}
);
Grant select on sysptprof to public;

Iv. User conversation Information
* Syssessions -------->; dialog data
* Syssesprof -------->; user statistics
* Syslocks -------->; user lock
* Syseswts -------->; wait time

Create Table "sysmaster". syssessions {user conversation and connection information}
(
Sid integer, {dialog number}
Username char (8), {user name}
UID smallint, {user UNIX number}
PID integer, {user process number}
Hostname char (16), {Host name}
Tty char (16), {tty port}
Connected integer, {user connection time}
Feprogram char (16), {program name}
Pooladdr integer, {private conversation pool pointer}
Is_wlatch integer, {flag 1 = Yes, 0 = No, waiting for Lock}
Is_wlock integer, {flag 1 = Yes, 0 = No, waiting for Lock}
Is_wbuff integer, {flag 1 = Yes, 0 = No, waiting for the buffer}
Is_wckpt integer, {flag 1 = Yes, 0 = No, waiting for verification}
Is_wlogbuf integer, {flag 1 = Yes, 0 = No, wait for the log buffer}
Is_wtrans integer, {flag 1 = Yes, 0 = No, waiting for transaction}
Is_monitor integer, {flag 1 = Yes, 0 = No, monitoring process}
Is_incrit integer, {flag 1 = Yes, 0 = No, in the key segment}
State INTEGER {flag}
);
Grant select on syssessions to public;

Create Table "sysmaster". syssesprof {user conversation Performance Statistics}
(
Sid integer, {dialog number}
Lockreqs decimal (16, 0), {requested lock}
Locksheld decimal (16, 0), {lock hold}
Lockwts decimal (16, 0), {lock wait}
Deadlks decimal (16, 0), {deadlock detected}
Lktouts decimal (16, 0), {deadlock timeout}
Logrecs decimal (16, 0), {write logical log records}
Isreads decimal (16, 0), {read}
Iswrites decimal (16, 0), {write}
Isrewrites decimal (16, 0), {rewrite}
Isdeletes decimal (16, 0), {Delete}
Iscommits decimal (16, 0), {submit}
Isrollbacks decimal (16, 0), {undo}
Longtxs decimal (16, 0), {long transaction}
Bufreads decimal (16, 0), {buffer read}
Bufwrites decimal (16, 0), {buffer write}
Seqscans decimal (16, 0), {Sequential Scan}
Pagreads decimal (16, 0), {page read}
Pagwrites decimal (16, 0), {page write}
Total_sorts decimal (16, 0), {total order}
Dsksorts decimal (16, 0), {disk sorting}
Max_sortdiskspace decimal (16, 0), {maximum space used for sorting}
Logspused decimal (16, 0), {currently used log byte}
Maxlogsp decimal (16, 0) {maximum space used by logical logs}
);
Grant select on syssesprof to public;

Create Table "sysmaster". syslocks {lock activity on the server}
(
Dbsname char (18), {database}
Tabname char (18), {table name}
Rowidlk integer, {Number of the index keyword lock}
Keynum smallint, {keyword Number of the index keyword lock}
Type integer, {lock owner dialog ID}
Owner integer, {ID of the first waiting conversation}
Waiter char (4) {lock type}
);
Grant select on syslocks to public;
{*************************************** ****************
Note: The lock types include
* B ------>; byte lock (byte lock)
* Is ------>; intent shared lock (intention shared lock)
* S ------>; shared lock)
* Xs ------>; Repeatable read shared key (Repeatable read shared lock)
* U ------>; update lock (update lock)
* IX ------>; intent exclusive lock (intention exclusive lock)
* Six ------>; shared intent exclusive (exclusive share intention lock)
* X ------>; exclusive lock (exclusive lock)
* XR ------>; repreatable read exclusive (exclusive Repeatable read lock)

Basically, these locks are a combination of three types: Shared locks (s), exclusive locks (x), update locks (u ). The shared lock allows other users to read data, but does not change the data. The exclusive lock prevents other users from sharing data.
The update lock prevents other users from changing the data when updating data.

Objects that can be locked in IDS include:
* Database ------>; when a user opens a database, a shared lock is applied to the database to prevent others from deleting the data in use. This operation is displayed as a lock in the sysmaster database and sysdatabase tables. rowid points to a record containing the database name.
* Table ------>; Table locks are displayed as table locks, rowid is 0, and keynum is 0.
* Page ------>; the page lock is displayed as rowid ending with 00, that is, all rows on the page are locked.
* Row ------>; the row lock displays the actual rowid (not ended with 00 ).
* Key ------>; the key lock is displayed as keynum. If the index of the row needs to be updated, the index of the row is locked.
**************************************** ****************}

Create Table "sysmaster". sysseswts {object waiting status and time}
(
Sid integer, {dialog ID}
Reason char (50), {reason for waiting}
Numwaits integer, {Number of waits for this reason}
Cumtime float, {cumulative wait time for this reason}
Maxtime INTEGER {maximum waiting time for this reason}
);
Grant select on sysseswts to public;
Create Table "sysmaster". systrans {user transaction}
(
Tx_id integer, {transaction table pointer}
Tx_logbeg integer, {logical log number starting the transaction}
Tx_loguniq INTEGER {Current Logical log number used in the transaction}
);
Grant select on systrans to public;

Create Table "sysmaster". syssqexpalin {user query}
(
Sqx_sessionid integer, {SQL statement dialog ID, matching onstat-u output}
Sqx_bufreads integer, {Number of buffer reads completed by SQL statements}
Sqx_pagereads integer, {number of page reads completed by SQL statements}
Sqx_totsorts integer, {Number of sorted SQL statements}
Sqx_estcost integer, {The estimated cost of this query should be the same as the output of set explain. Note: This cost is sometimes high, but it can be used as a reference for looking for bad queries}
Sqx_estrows integer, {check the rows returned by this query, which should be the same as the output of set expalin}
Sqx_seqscan smallint, {1 is returned when the query is a sequential table scan, which usually indicates a problem}
Sqx_srtscan smallint, {1 is returned when the query is a Sort scan}
Sqx_autoindex smallint, {1 is returned when the query is an automatic table index}
Sqx_mrgjoin smallint, {1 is returned when the query is a merged connection}
Sqx_dynhashjoin smallint, {1 is returned when the query is a dynamic hash join}
Sqx_sqlstatement char (32000) {SQL statement text}
);
Grant select on syssqexplain to public;

 

Informix system table