informix-系统视图

/****************************************************************************/
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.