本文档详细介绍了 Informix 数据库的多个系统视图，包括 systables, sysviews, sysindexes, syscolumns, sysconstraints, sysreferences, sysfragments, syssequences, sysprocedures, sysprocbody, systriggers 和 systrigbody。例如，`systables` 视图展示了表的类型，如表、视图等；`sysindexes` 视图包含索引信息，如索引名、类型和分区；`syscolumns` 视图列出表的列名、列号和数据类型。此外，还展示了如何查询这些视图的示例。"
105882617,7611440,基于JUnit的单元测试实践,"['软件测试', 'JUnit框架', '测试用例设计', '自动化测试', '代码覆盖率']
/****************************************************************************/
informix 系统视图
/****************************************************************************/
1 systables
2 sysviews
3 sysindexes
4 syscolumns
5 sysconstraints
6 sysreferences
7 sysfragments
8 syssequences
9 sysprocedures
10 sysprocbody
11 systriggers
12 systrigbody
1 tabid,tabtype
tabtype:
T = Table
v E = External Table
v V = View
v Q = Sequence
v P = Private synonym
v S = Public synonym
> select tabid,tabtype from systables where tabname='p$kpi_t_roletodept';
tabid tabtype
934 T
1 row(s) retrieved.
2 viewtext
> select viewtext from sysviews where tabid=71 order by seqno;
viewtext create view "informix".sysindexes (idxname,owner,tabid,idxtype,cluste
red,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part
11,part12,part13,part14,part15,part16,levels,leaves,nunique,clust) as
select x0.idxname ,x0.owner ,x0.tabid ,x0.idxtyp
viewtext e ,x0.clustered ,ikeyextractcolno(x0.indexkeys ,0 ),ikeyextractcolno(
x0.indexkeys ,1 ),ikeyextractcolno(x0.indexkeys ,2 ),ikeyextractcolno
(x0.indexkeys ,3 ),ikeyextractcolno(x0.indexkeys ,4 ),ikeyextractcoln
o(x0.indexkeys ,5 ),ikeyextractcolno(x0.indexkeys
viewtext ,6 ),ikeyextractcolno(x0.indexkeys ,7 ),ikeyextractcolno(x0.indexkey
s ,8 ),ikeyextractcolno(x0.indexkeys ,9 ),ikeyextractcolno(x0.indexke
ys ,10 ),ikeyextractcolno(x0.indexkeys ,11 ),ikeyextractcolno(x0.inde
xkeys ,12 ),ikeyextractcolno(x0.indexkeys ,13 ),i
viewtext keyextractcolno(x0.indexkeys ,14 ),ikeyextractcolno(x0.indexkeys ,15
),x0.levels ,x0.leaves ,x0.nunique ,x0.clust from "informix".sysindic
es x0 ;
4 row(s) retrieved.
3 idxname,idxtype,part1-part16
> select idxname ,tabid,idxtype ,part1,part2,part3 from sysindexes where tabid=8;
idxname btabid
tabid 8
idxtype -
part1 1
part2 0
part3 0
idxname dtabid
tabid 8
idxtype -
part1 3
part2 0
part3 0
2 row(s) retrieved.
4 colname,colno,coltype
coltype:
0 = CHAR
1 = SMALLINT
2 = INTEGER
3 = FLOAT
4 = SMALLFLOAT
5 = DECIMAL
6 = SERIAL 1
7 = DATE
8 = MONEY
9 = NULL
10 = DATETIME
11 = BYTE
12 = TEXT
13 = VARCHAR
14 = INTERVAL
15 = NCHAR
16 = NVARCHAR
17 = INT8
18 = SERIAL8 1
19 = SET
20 = MULTISET
21 = LIST
22 = ROW (unnamed)
23 = COLLECTION
40 = Variable-length opaque type 2
41 = Fixed-length opaque type 2
43 = LVARCHAR (client-side only)
45 = BOOLEAN
52 = BIGINT
53 = BIGSERIAL 1
2061 = IDSSECURITYLABEL 2
4118 = ROW (named)
> select colname,colno,coltype from syscolumns where tabid=8;
colname btabid
colno 1
coltype 2
colname btype
colno 2
coltype 0
colname dtabid
colno 3
coltype 2
colname dtype
colno 4
coltype 0
4 row(s) retrieved.
5 constrname,constrtype,idxname
constrtype:
C = Check constraint
v N = Not NULL
v P = Primary key
v R = Referential
v T = Table
v U = Unique
> select constrname,constrtype,idxname from sysconstraints where tabid=121;
constrname u121_9
constrtype P
idxname 121_9
1 row(s) retrieved.
6 constrid,primary,ptabid,delrule
> select constrid,primary,ptabid,delrule from sysreferences limit 6;
constrid primary ptabid delrule
1070 34 147 R
1071 31 144 R
1072 1 113 R
1073 1 113 R
1074 35 148 R
1075 40 152 R
7 fragtype,indexname,colno,strategy,evalpos,exprtext,partition
strategy:
R = Round-robin distribution strategy
E = Expression-based distribution strategy
I = IN DBSPACE clause specifies a storage location
as part of distribution strategy
N = raNge-iNterval (or rolliNg wiNdow)
distribution strategy
L = List distribution strategy
T = Table-based distribution strategy
H = table is a subtable within a table Hierarchy
> select fragtype,indexname,colno,strategy,evalpos,exprtext,partition from sysfragments;
fragtype I
indexname 3251_1561
colno 0
strategy I
evalpos 0
exprtext
partition ifmis_jsx
fragtype I
indexname idx_p$400_acct4
colno 0
strategy I
evalpos 0
exprtext
partition ifmis_jsx
1293 row(s) retrieved.
8 seqid,tabid
> select * from syssequences;
seqid 1
tabid 3174
start_val
inc_val 1
min_val 1
max_val 9223372036854775807
cycle 0
restart_val
cache 20
order 1
1 row(s) retrieved.
> select tabid,tabname from systables where tabid=3174;
tabid 3174
tabname secu_seq_fq
1 row(s) retrieved.
9 procname,procid,mode,isproc
mode:
D or d = DBA
O or o = Owner
P or p = Protected
R or r = Restricted
T or t = Trigger
isproc:
t = procedure
f = function
> select * from sysprocedures;
procname dict_synchronize_down
owner informix
procid 3054
mode O
retsize 42
symsize 7899
datasize 123615
codesize 5172
numargs 0
isproc t
specificname
externalname
paramstyle I
langid 2
paramtypes
variant t
client f
handlesnulls t
iterator f
percallcost 0
commutator
negator
selfunc
internal f
class
stack
parallelizable f
costfunc
selconst 0.00
collation zh_CN.57372
procflags 0
1783 row(s) retrieved.
10 procid,seqno,data
datakey:
A = Routine alter SQL (will not change this value
after update statistics)
D = Routine user documentation text
E = Time of creation information
L = Literal value (that is, literal number or quoted
string)
P = Interpreter instruction code (p-code)
R = Routine return value type list
S = Routine symbol table
T = Routine text creation SQL
> select data from sysprocbody where datakey='T' AND procid = 2000;
CREATE PROCEDURE tr_P$GBSPF_T_ALTRECORD_pro()
REFERENCING OLD AS OLD NE
W AS NEW for P$GBSPF_T_ALTRECORD;
define global v_pmYear varchar(3
2) default '2015';
define global v_pmDivID varchar(32) default '2300'
;
if(inserting) then
let new
.province = nvl(new.province,v_pmdivid);
let new.year = nvl(new.year,v
_pmYear);
let NEW.DBVERSION = CASE WHEN TO_CHAR(NEW.DBVERSION, '%Y-%m-%
d') = '2012-01-01' THEN TO_DATE('2012-01-01', '%Y-%m-%d') ELSE SYSDATE EN
D;
end if;
if(updating) then
if(TO_CHAR(NEW.DBVERSION,'%Y-%m-%d') ='2012-01-01') then
RETURN ;
end if;
let NEW.DBVERSION=SYSDATE;
end if;
end procedure;
11 trigid,trigname,tabid,event
event:
D = Delete trigger
I = Insert trigger
U = Update trigger
S = Select trigger
d = INSTEAD OF Delete trigger
i = INSTEAD OF Insert trigger
u = INSTEAD OF Update trigger
> select * from systriggers ;
trigid 11
trigname trigger_code_t_queryoperator
owner informix
tabid 231
event I
old
new new
mode O
collation zh_CN.57372
10 row(s) retrieved.
12 datakey,data,seqno
datakey:
A = ASCII text for the body, triggered actions
B = Linearized code for the body
D = English text for the header, trigger definition
H = Linearized code for the header
S = Linearized code for the symbol table
> select datakey,data from systrigbody where trigid=1958 and datakey in('A','D') ORDER BY datakey desc;
datakey D
data create trigger "informix".tr_p$fasp_t_causer_update update on "informi
x".p$fasp_t_causer
datakey A
data for each row
(
execute procedure "informix".tr_p$f
asp_t_causer_pro() with trigger references );
2 row(s) retrieved.
informix 系统视图
/****************************************************************************/
1 systables
2 sysviews
3 sysindexes
4 syscolumns
5 sysconstraints
6 sysreferences
7 sysfragments
8 syssequences
9 sysprocedures
10 sysprocbody
11 systriggers
12 systrigbody
1 tabid,tabtype
tabtype:
T = Table
v E = External Table
v V = View
v Q = Sequence
v P = Private synonym
v S = Public synonym
> select tabid,tabtype from systables where tabname='p$kpi_t_roletodept';
tabid tabtype
934 T
1 row(s) retrieved.
2 viewtext
> select viewtext from sysviews where tabid=71 order by seqno;
viewtext create view "informix".sysindexes (idxname,owner,tabid,idxtype,cluste
red,part1,part2,part3,part4,part5,part6,part7,part8,part9,part10,part
11,part12,part13,part14,part15,part16,levels,leaves,nunique,clust) as
select x0.idxname ,x0.owner ,x0.tabid ,x0.idxtyp
viewtext e ,x0.clustered ,ikeyextractcolno(x0.indexkeys ,0 ),ikeyextractcolno(
x0.indexkeys ,1 ),ikeyextractcolno(x0.indexkeys ,2 ),ikeyextractcolno
(x0.indexkeys ,3 ),ikeyextractcolno(x0.indexkeys ,4 ),ikeyextractcoln
o(x0.indexkeys ,5 ),ikeyextractcolno(x0.indexkeys
viewtext ,6 ),ikeyextractcolno(x0.indexkeys ,7 ),ikeyextractcolno(x0.indexkey
s ,8 ),ikeyextractcolno(x0.indexkeys ,9 ),ikeyextractcolno(x0.indexke
ys ,10 ),ikeyextractcolno(x0.indexkeys ,11 ),ikeyextractcolno(x0.inde
xkeys ,12 ),ikeyextractcolno(x0.indexkeys ,13 ),i
viewtext keyextractcolno(x0.indexkeys ,14 ),ikeyextractcolno(x0.indexkeys ,15
),x0.levels ,x0.leaves ,x0.nunique ,x0.clust from "informix".sysindic
es x0 ;
4 row(s) retrieved.
3 idxname,idxtype,part1-part16
> select idxname ,tabid,idxtype ,part1,part2,part3 from sysindexes where tabid=8;
idxname btabid
tabid 8
idxtype -
part1 1
part2 0
part3 0
idxname dtabid
tabid 8
idxtype -
part1 3
part2 0
part3 0
2 row(s) retrieved.
4 colname,colno,coltype
coltype:
0 = CHAR
1 = SMALLINT
2 = INTEGER
3 = FLOAT
4 = SMALLFLOAT
5 = DECIMAL
6 = SERIAL 1
7 = DATE
8 = MONEY
9 = NULL
10 = DATETIME
11 = BYTE
12 = TEXT
13 = VARCHAR
14 = INTERVAL
15 = NCHAR
16 = NVARCHAR
17 = INT8
18 = SERIAL8 1
19 = SET
20 = MULTISET
21 = LIST
22 = ROW (unnamed)
23 = COLLECTION
40 = Variable-length opaque type 2
41 = Fixed-length opaque type 2
43 = LVARCHAR (client-side only)
45 = BOOLEAN
52 = BIGINT
53 = BIGSERIAL 1
2061 = IDSSECURITYLABEL 2
4118 = ROW (named)
> select colname,colno,coltype from syscolumns where tabid=8;
colname btabid
colno 1
coltype 2
colname btype
colno 2
coltype 0
colname dtabid
colno 3
coltype 2
colname dtype
colno 4
coltype 0
4 row(s) retrieved.
5 constrname,constrtype,idxname
constrtype:
C = Check constraint
v N = Not NULL
v P = Primary key
v R = Referential
v T = Table
v U = Unique
> select constrname,constrtype,idxname from sysconstraints where tabid=121;
constrname u121_9
constrtype P
idxname 121_9
1 row(s) retrieved.
6 constrid,primary,ptabid,delrule
> select constrid,primary,ptabid,delrule from sysreferences limit 6;
constrid primary ptabid delrule
1070 34 147 R
1071 31 144 R
1072 1 113 R
1073 1 113 R
1074 35 148 R
1075 40 152 R
7 fragtype,indexname,colno,strategy,evalpos,exprtext,partition
strategy:
R = Round-robin distribution strategy
E = Expression-based distribution strategy
I = IN DBSPACE clause specifies a storage location
as part of distribution strategy
N = raNge-iNterval (or rolliNg wiNdow)
distribution strategy
L = List distribution strategy
T = Table-based distribution strategy
H = table is a subtable within a table Hierarchy
> select fragtype,indexname,colno,strategy,evalpos,exprtext,partition from sysfragments;
fragtype I
indexname 3251_1561
colno 0
strategy I
evalpos 0
exprtext
partition ifmis_jsx
fragtype I
indexname idx_p$400_acct4
colno 0
strategy I
evalpos 0
exprtext
partition ifmis_jsx
1293 row(s) retrieved.
8 seqid,tabid
> select * from syssequences;
seqid 1
tabid 3174
start_val
inc_val 1
min_val 1
max_val 9223372036854775807
cycle 0
restart_val
cache 20
order 1
1 row(s) retrieved.
> select tabid,tabname from systables where tabid=3174;
tabid 3174
tabname secu_seq_fq
1 row(s) retrieved.
9 procname,procid,mode,isproc
mode:
D or d = DBA
O or o = Owner
P or p = Protected
R or r = Restricted
T or t = Trigger
isproc:
t = procedure
f = function
> select * from sysprocedures;
procname dict_synchronize_down
owner informix
procid 3054
mode O
retsize 42
symsize 7899
datasize 123615
codesize 5172
numargs 0
isproc t
specificname
externalname
paramstyle I
langid 2
paramtypes
variant t
client f
handlesnulls t
iterator f
percallcost 0
commutator
negator
selfunc
internal f
class
stack
parallelizable f
costfunc
selconst 0.00
collation zh_CN.57372
procflags 0
1783 row(s) retrieved.
10 procid,seqno,data
datakey:
A = Routine alter SQL (will not change this value
after update statistics)
D = Routine user documentation text
E = Time of creation information
L = Literal value (that is, literal number or quoted
string)
P = Interpreter instruction code (p-code)
R = Routine return value type list
S = Routine symbol table
T = Routine text creation SQL
> select data from sysprocbody where datakey='T' AND procid = 2000;
CREATE PROCEDURE tr_P$GBSPF_T_ALTRECORD_pro()
REFERENCING OLD AS OLD NE
W AS NEW for P$GBSPF_T_ALTRECORD;
define global v_pmYear varchar(3
2) default '2015';
define global v_pmDivID varchar(32) default '2300'
;
if(inserting) then
let new
.province = nvl(new.province,v_pmdivid);
let new.year = nvl(new.year,v
_pmYear);
let NEW.DBVERSION = CASE WHEN TO_CHAR(NEW.DBVERSION, '%Y-%m-%
d') = '2012-01-01' THEN TO_DATE('2012-01-01', '%Y-%m-%d') ELSE SYSDATE EN
D;
end if;
if(updating) then
if(TO_CHAR(NEW.DBVERSION,'%Y-%m-%d') ='2012-01-01') then
RETURN ;
end if;
let NEW.DBVERSION=SYSDATE;
end if;
end procedure;
11 trigid,trigname,tabid,event
event:
D = Delete trigger
I = Insert trigger
U = Update trigger
S = Select trigger
d = INSTEAD OF Delete trigger
i = INSTEAD OF Insert trigger
u = INSTEAD OF Update trigger
> select * from systriggers ;
trigid 11
trigname trigger_code_t_queryoperator
owner informix
tabid 231
event I
old
new new
mode O
collation zh_CN.57372
10 row(s) retrieved.
12 datakey,data,seqno
datakey:
A = ASCII text for the body, triggered actions
B = Linearized code for the body
D = English text for the header, trigger definition
H = Linearized code for the header
S = Linearized code for the symbol table
> select datakey,data from systrigbody where trigid=1958 and datakey in('A','D') ORDER BY datakey desc;
datakey D
data create trigger "informix".tr_p$fasp_t_causer_update update on "informi
x".p$fasp_t_causer
datakey A
data for each row
(
execute procedure "informix".tr_p$f
asp_t_causer_pro() with trigger references );
2 row(s) retrieved.
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
