select name, is_logging, is_case_insens from sysdatabases;
操作演示
[informix@vm84145 dbs]$ dbaccess sysmaster -
Database selected.
> select name, is_logging, is_case_insens from sysdatabases;
name sysmaster
is_logging 1
is_case_insens 0
name sysutils
is_logging 1
is_case_insens 0
name sysuser
is_logging 1
is_case_insens 0
name sysadmin
is_logging 1
is_case_insens 0
name testdb
is_logging 1
is_case_insens 0
name tapedb
is_logging 1
is_case_insens 0
6 row(s) retrieved.
>
database sysmaster;
select * from sysdbslocale;
操作演示
[informix@vm84145 dbs]$ dbaccess - -
> database sysmaster;
Database selected.
> select * from sysdbslocale;
dbs_dbsname sysmaster
dbs_collate en_US.819
dbs_dbsname sysutils
dbs_collate en_US.819
dbs_dbsname sysuser
dbs_collate en_US.819
dbs_dbsname sysadmin
dbs_collate en_US.819
dbs_dbsname testdb
dbs_collate zh_CN.57372
dbs_dbsname tapedb
dbs_collate zh_CN.57372
6 row(s) retrieved.
>
database <db_name>;
select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
操作演示
> database tapedb;
Database selected.
> select tabid, tabname, tabtype from systables where tabid >= 100 and tabtype = 'T';
tabid 100
tabname t_dept
tabtype T
tabid 101
tabname t_employee
tabtype T
2 row(s) retrieved.
>
database <db_name>;
select colname,coltype,colno from syscolumns where tabid = <tab_id>;
--查询所有SinoDB数据表字段类型
select a.tabname,b.colname,b.coltype,
case b.coltype
when '0' then 'CHAR'
when '1' then 'SMALLINT'
when '2' then 'INTEGER'
when '3' then 'FLOAT'
when '4' then 'SMALLFLOAT'
when '5' then 'DECIMAL'
when '6' then 'SERIAL'
when '7' then 'DATE'
when '8' then 'MONEY'
when '9' then 'NULL'
when '10' then 'DATETIME'
when '11' then 'BYTE'
when '12' then 'TEXT'
when '13' then 'VARCHAR'
when '14' then 'INTERVAL'
when '15' then 'NCHAR'
when '16' then 'NVARCHAR'
when '17' then 'INT8'
when '18' then 'SERIAL8'
when '19' then 'SET'
when '20' then 'MULTISET'
when '21' then 'LIST'
when '22' then 'Unnamed ROW'
when '40' then 'LVARCHAR'
when '41' then 'CLOB'
when '43' then 'BLOB'
when '44' then 'BOOLEAN'
when '256' then 'CHAR'
when '257' then 'SMALLINT'
when '258' then 'INTEGER'
when '259' then 'FLOAT'
when '260' then 'REAL'
when '261' then 'DECIMAL'
when '262' then 'SERIAL'
when '263' then 'DATE'
when '264' then 'MONEY'
when '266' then 'DATETIME'
when '267' then 'BYTE'
when '268' then 'TEXT'
when '269' then 'VARCHAR'
when '270' then 'INTERVAL'
when '271' then 'NCHAR'
when '272' then 'NVARCHAR'
when '273' then 'INT8'
when '274' then 'SERIAL8'
when '275' then 'SET'
when '276' then 'MULTISET'
when '277' then 'LIST'
when '278' then 'Unnamed ROW'
when '296' then 'LVARCHAR'
when '297' then 'CLOB'
when '298' then 'BLOB'
when '299' then 'BOOLEAN'
when '4118' then 'Named ROW'
end as coltypename, b.colno from systables a left join syscolumns b on a.tabid=b.tabid where a.tabid>99 and a.tabtype='T' order by a.tabname,b.colno;
操作演示
> database testdb;
Database selected.
> select colname,coltype,colno from syscolumns where tabid =289;
colname f_employeeid
coltype 2
colno 1
colname f_deptid
coltype 2
colno 2
colname f_employeename
coltype 13
colno 3
colname f_birthdate
coltype 7
colno 4
4 row(s) retrieved.
操作演示
> select a.tabname,b.colname,b.coltype,
case b.coltype
when '0' then 'CHAR'
when '1' then 'SMALLINT'
when '2' then 'INTEGER'
when '3' then 'FLOAT'
when '4' then 'SMALLFLOAT'
when '5' then 'DECIMAL'
when '6' then 'SERIAL'
when '7' then 'DATE'
when '8' then 'MONEY'
when '9' then 'NULL'
when '10' then 'DATETIME'
when '11' then 'BYTE'
when '12' then 'TEXT'
when '13' then 'VARCHAR'
when '14' then 'INTERVAL'
when '15' then 'NCHAR'
when '16' then 'NVARCHAR'
when '17' then 'INT8'
when '18' then 'SERIAL8'
when '19' then 'SET'
when '20' then 'MULTISET'
when '21' then 'LIST'
when '22' then 'Unnamed ROW'
when '40' then 'LVARCHAR'
when '41' then 'CLOB'
when '43' then 'BLOB'
when '44' then 'BOOLEAN'
when '256' then 'CHAR'
when '257' then 'SMALLINT'
when '258' then 'INTEGER'
when '259' then 'FLOAT'
when '260' then 'REAL'
when '261' then 'DECIMAL'
when '262' then 'SERIAL'
when '263' then 'DATE'
when '264' then 'MONEY'
when '266' then 'DATETIME'
when '267' then 'BYTE'
when '268' then 'TEXT'
when '269' then 'VARCHAR'
when '270' then 'INTERVAL'
when '271' then 'NCHAR'
when '272' then 'NVARCHAR'
when '273' then 'INT8'
when '274' then 'SERIAL8'
when '275' then 'SET'
when '276' then 'MULTISET'
when '277' then 'LIST'
when '278' then 'Unnamed ROW'
when '296' then 'LVARCHAR'
when '297' then 'CLOB'
when '298' then 'BLOB'
when '299' then 'BOOLEAN'
when '4118' then 'Named ROW'
end as coltypename, b.colno from systables a left join syscolumns b on a.tabid=b.tabid where a.tabid=289 and a.tabtype='T' order by a.tabname,b.colno;
tabname t_employee
colname f_employeeid
coltype 2
coltypename INTEGER
colno 1
tabname t_employee
colname f_deptid
coltype 2
coltypename INTEGER
colno 2
tabname t_employee
colname f_employeename
coltype 13
coltypename VARCHAR
colno 3
tabname t_employee
colname f_birthdate
coltype 7
coltypename DATE
colno 4
4 row(s) retrieved.
>
database <db_name>;
select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';
操作演示
informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> create view v_user as select * from t_user;
View created.
> select tabname,tabtype from systables where tabid >= 100 and tabtype = 'V';
tabname bld_registered
tabtype V
tabname bldi_provided
tabtype V
tabname bldi_required
tabtype V
tabname v_user
tabtype V
4 row(s) retrieved.
>
database <db_name>;
select tabid, idxname,tabid,idxtype from sysindexes where tabid = <tab_id>;
操作演示
> create index idx_user_name on t_user(f_username asc);
Index created.
> select tabid, idxname,tabid,idxtype from sysindexes where tabid =130;
tabid 130
idxname idx_user_name
tabid 130
idxtype D
1 row(s) retrieved.
>
database <db_name>;
select * from systriggers;
create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);
create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);
create trigger trg_sale_insert insert on t_sale
referencing new as new_item
for each row
(
insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
);
操作演示
> create trigger trg_sale_insert insert on t_sale
> referencing new as new_item
> for each row
> (
> insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
> );
Trigger created.
> select * from systriggers;
trigid 9
trigname trg_sale_insert
owner informix
tabid 184
event I
old
new new_item
mode O
collation zh_CN.57372
1 row(s) retrieved.
>
database <db_name>;
select procname, procid from sysprocedures where procname like '<key_word>%';
create procedure up_useradd(userid int, username varchar(20))
insert into t_user values(userid, username);
end procedure;
select procname, procid from sysprocedures where procname like 'up_%';
操作演示
> create procedure up_useradd(userid int, username varchar(20))
> insert into t_user values(userid, username);
> end procedure;
Routine created.
> select procname, procid from sysprocedures where procname like 'up_%';
procname up_useradd
procid 676
1 row(s) retrieved.
>
database <db_name>;
select * from sysprocedures where procname like '<key_word>%';
database testdb;
create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username
define i int;
define userid int;
define username varchar(20);
for i = 1 to user_num
let userid = i;
let username = concat('user_', to_char(i));
return userid, username with resume;
end for;
end function;
select * from sysprocedures where procname like 'fn_user%';
操作演示
> create function fn_user_add(user_num int)
> returning int as userid, varchar(20) as username
>
> define i int;
> define userid int;
> define username varchar(20);
>
> for i = 1 to user_num
> let userid = i;
> let username = concat('user_', to_char(i));
>
> return userid, username with resume;
> end for;
>
> end function;
Routine created.
> select * from sysprocedures where procname like 'fn_user%';
procname fn_user_add
owner informix
procid 677
mode O
retsize 207
symsize 829
datasize 879
codesize 152
numargs 1
isproc f
specificname
externalname
paramstyle I
langid 2
paramtypes integer
variant t
client f
handlesnulls t
iterator t
percallcost 0
commutator
negator
selfunc
internal f
class
stack
parallelizable f
costfunc
selconst 0.00
collation zh_CN.57372
procflags 0
1 row(s) retrieved.
>
database <db_name>;
select * from syssyntable where tabname is not null;
select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
from
(select * from syssyntable where tabname is null) a
inner join systables b
on a.tabid = b.tabid
inner join systables c
on a.btabid = c.tabid;
操作演示
> database testdb;
Database closed.
Database selected.
> create synonym syn_user for t_user;
Synonym created.
> select a.tabid, a.btabid, b.tabname as syn_name, c.tabname as tab_name
> from
> (select * from syssyntable where tabname is null) a
> inner join systables b
> on a.tabid = b.tabid
> inner join systables c
> on a.btabid = c.tabid;
tabid 185
btabid 130
syn_name syn_user
tab_name t_user
1 row(s) retrieved.
>
drop table if exists t_dept;
create table t_dept(f_deptid int primary key constraint pk_dept_deptid,
f_deptname varchar(20) not null unique constraint uni_dept_deptname);
drop table if exists t_employee;
create table t_employee(f_employeeid int primary key constraint pk_employee_employeeid,
f_deptid int references t_dept(f_deptid),
f_employeename varchar(20) not null,
f_showname varchar(20) not null unique constraint uni_employee_showname,
f_age int default 18 check (f_age >0 and f_age <= 120),
f_employeedate date default today);
database <db_name>;
select * from sysconstraints where constrtype = 'P';
操作演示
> select * from sysconstraints where constrtype = 'P';
constrid 47
constrname pk_dept_deptid
owner informix
tabid 186
constrtype P
idxname 186_47
collation zh_CN.57372
constrid 50
constrname pk_employee_employeeid
owner informix
tabid 187
constrtype P
idxname 187_50
collation zh_CN.57372
2 row(s) retrieved.
>
database <db_name>;
select * from sysconstraints where constrtype = 'R';
操作演示
> select * from sysconstraints where constrtype = 'R';
constrid 52
constrname r187_52
owner informix
tabid 187
constrtype R
idxname 187_52
collation zh_CN.57372
1 row(s) retrieved.
>
database <db_name>;
select * from sysconstraints where constrtype = 'U';
操作演示
> select * from sysconstraints where constrtype = 'R';
constrid 52
constrname r187_52
owner informix
tabid 187
constrtype R
idxname 187_52
collation zh_CN.57372
1 row(s) retrieved.
> select * from sysconstraints where constrtype = 'U';
constrid 48
constrname uni_dept_deptname
owner informix
tabid 186
constrtype U
idxname 186_48
collation zh_CN.57372
constrid 51
constrname uni_employee_showname
owner informix
tabid 187
constrtype U
idxname 187_51
collation zh_CN.57372
2 row(s) retrieved.
>
database <db_name>;
select * from sysconstraints where constrtype = 'N';
操作演示
> select * from sysconstraints where constrtype = 'N';
...
constrid 49
constrname n186_49
owner informix
tabid 186
constrtype N
idxname
collation zh_CN.57372
constrid 53
constrname n187_53
owner informix
tabid 187
constrtype N
idxname
collation zh_CN.57372
constrid 54
constrname n187_54
owner informix
tabid 187
constrtype N
idxname
collation zh_CN.57372
20 row(s) retrieved.
>
database <db_name>;
select * from sysdefaults where tabid = <tab_id>;
操作演示
[informix@vm84145 ~]$ dbaccess testdb -
Database selected.
> select * from sysdefaults where tabid =187;
tabid 187
colno 5
type L
default AAAAEg 18
class T
tabid 187
colno 6
type T
default
class T
2 row(s) retrieved.
>
database <db_name>;
select * from sysconstraints where constrtype = 'C';
select * from syschecks where type = 'T' and constrid = <constr_id>;
操作演示
> select * from sysconstraints where constrtype = 'C';
constrid 55
constrname c187_55
owner informix
tabid 187
constrtype C
idxname
collation zh_CN.57372
1 row(s) retrieved.
> select * from syschecks where type = 'T' and constrid = 55;
constrid type seqno checktext
55 T 0 ((f_age > 0 ) AND (f_age <= 120
55 T 1 ) )
2 row(s) retrieved.
>
database sysmaster;
select * from sysdbspaces;
操作演示
> database sysmaster;
Database closed.
Database selected.
> select first 3 dbsnum, name, pagesize, fchunk, nchunks, is_temp, is_blobspace, is_sbspace from sysdbspaces;
dbsnum 1
name rootdbs
pagesize 2048
fchunk 1
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
dbsnum 2
name llogdbs
pagesize 2048
fchunk 2
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
dbsnum 3
name plogdbs
pagesize 2048
fchunk 3
nchunks 1
is_temp 0
is_blobspace 0
is_sbspace 0
3 row(s) retrieved.
>
database sysmaster;
select * from syschunks;
操作演示
> database sysmaster;
Database closed.
> select first 3 chknum, dbsnum, pagesize, chksize, nfree, fname from syschunks;
chknum 1
dbsnum 1
pagesize 2048
chksize 1024000
nfree 1009559
fname /home/informix/dbs/rootdbs
chknum 2
dbsnum 2
pagesize 2048
chksize 1024000
nfree 47
fname /home/informix/dbs/llogdbs
chknum 3
dbsnum 3
pagesize 2048
chksize 1024000
nfree 2
fname /home/informix/dbs/plogdbs
3 row(s) retrieved.
>
database sysmaster;
select * from sysvplst;
操作演示
> select vpid, pid, classname, thread_run, thread_idle from sysvplst;
vpid pid classname thread_run thread_idle
1 3053 cpu 24.77661746059 275564.1477024
2 3062 adm 0.00 0.00
3 3063 lio 0.000418087716 0.100223771208
4 3065 pio 0.000555485284 0.10019635524
5 3066 aio 0.054207225992 273358.2413387
6 3067 msc 0.006060485746 275059.2120034
7 3068 fifo 0.00076016436 0.100376504263
8 3069 cpu 28.74923207994 275540.3046463
9 3070 soc 275586.0738465 0.100419417083
10 3071 soc 274694.4578440 0.100389878383
11 3072 aio 0.017750250403 273353.2636599
12 3073 aio 0.00900443298 273353.1706911
13 6782 aio 0.006078412603 259220.2667469
14 12103 aio 0.003254824265 237617.6812542
14 row(s) retrieved.
>
database sysmaster;
select * from sysplog;
操作演示
> select * from sysplog;
pl_mutex 1142149720
pl_b1mutex 1142149848
pl_b1condition 1142149976
pl_b1used 0
pl_b1copied 0
pl_b1buffer 1156868096
pl_b1wtlist 0
pl_b2mutex 1142150112
pl_b2condition 1142150240
pl_b2used 0
pl_b2copied 0
pl_b2buffer 1158985728
pl_b2wtlist 0
pl_curbp 1142149848
pl_otherbp 1142150112
pl_bufsize 1024
pl_stamp 2168940
pl_chunk 3
pl_offset 53
pl_physize 1023945
pl_phypos 76648
pl_phyused 11
pl_phyarch 0
1 row(s) retrieved.
>
database sysmaster;
select * from syslogs;
操作演示
> select first 3 * from syslogs;
number 7
uniqid 0
size 102390
used 0
is_used 0
is_current 0
is_backed_up 0
is_new 0
is_archived 0
is_temp 0
is_pre_dropped 0
flags 0
number 8
uniqid 0
size 102390
used 0
is_used 0
is_current 0
is_backed_up 0
is_new 1
is_archived 0
is_temp 0
is_pre_dropped 0
flags 8
number 9
uniqid 0
size 102390
used 0
is_used 0
is_current 0
is_backed_up 0
is_new 1
is_archived 0
is_temp 0
is_pre_dropped 0
flags 8
3 row(s) retrieved.
>
database sysmaster;
select * from syssessions;
操作演示
> select first 3 * from syssessions;
sid 71
username informix
uid 1001
pid 32726
hostname vm84145
tty /dev/pts/1
connected 1646047933
feprogram /home/informix/sinodb/bin/dbaccess
pooladdr 1221664832
is_wlatch 0
is_wlock 0
is_wbuff 0
is_wckpt 0
is_wlogbuf 0
is_wtrans 0
is_monitor 0
is_incrit 0
state 524289
sid 27
username informix
uid 1001
pid 0
hostname
tty
connected 1645784104
feprogram
pooladdr 1225199680
is_wlatch 0
is_wlock 0
is_wbuff 0
is_wckpt 0
is_wlogbuf 0
is_wtrans 0
is_monitor 0
is_incrit 0
state 524289
sid 28
username informix
uid 1001
pid 0
hostname
tty
connected 1645784104
feprogram
pooladdr 1221500992
is_wlatch 0
is_wlock 0
is_wbuff 0
is_wckpt 0
is_wlogbuf 0
is_wtrans 0
is_monitor 0
is_incrit 0
state 524289
3 row(s) retrieved.
>
select * from sysusers;
操作演示
> select * from sysusers;
username informix
usertype D
priority 9
password
defrole
username public
usertype C
priority 5
password
defrole
2 row(s) retrieved.
>