informix常用查询

admin

1. dbaccess sysmaster << %%
   
2. select sid,pid, username, hostname
   
3. is_wlatch, -- blocked waiting on a latch
   
4. is_wlock, -- blocked waiting on a locked record or table
   
5. is_wbuff, -- blocked waiting on a buffer
   
6. is_wckpt, -- blocked waiting on a checkpoint
   
7. is_incrit -- session is in a critical section of transaction-- (e.g writting to disk)
   
8. from syssessions
   
9. order by username;
   
10. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select syssesprof.sid,
   
3. lockreqs ,
   
4. locksheld ,
   
5. lockwts ,
   
6. deadlks ,
   
7. lktouts
   
8. from syssesprof, syssessions
   
9. where syssesprof.sid = syssessions.sid
   
10. --And syssesprof.sid=540
    
11. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select
   
3. username,
   
4. sqx_sessionid,
   
5. sqx_sqlstatement
   
6. from sysmaster:syssqexplain, sysmaster:sysscblst
   
7. where sqx_sessionid = sid
   
8. --and sqx_sqlstatement like '%tabname%';
   
9. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select first 25 sqx_estcost,
   
3. sqx_estrows,
   
4. sqx_sqlstatement
   
5. from sysmaster:syssqexplain
   
6. where 1=1
   
7. order by sqx_estcost desc;
   
8. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select tabname,
   
3. sum(pf_rqlock) as locks,
   
4. sum(pf_wtlock) as lockwaits,
   
5. sum(pf_deadlk) as deadlocks
   
6. from sysactptnhdr,systabnames
   
7. where
   
8. systabnames.partnum = sysactptnhdr.partnum
   
9. and dbsname = "demodb"
   
10. --and pf_wtlock >=0
    
11. --and pf_rqlock >=0
    
12. group by tabname
    
13. order by lockwaits desc;
    
14. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select
   
3. dbsname,b.tabname,rowidr,keynum,
   
4. e.txt type,d.sid owner,g.username ownername,f.sid waiter,
   
5. h.username waitname
   
6. from syslcktab a,systabnames b,systxptab c,sysrstcb d,
   
7. sysscblst g,flags_text e, outer ( sysrstcb f , sysscblst h )
   
8. where a.partnum = b.partnum
   
9. and a.owner = c.address
   
10. and c.owner = d.address
    
11. and a.wtlist = f.address
    
12. and d.sid = g.sid
    
13. and e.tabname = 'syslcktab'
    
14. and e.flags = a.type
    
15. and f.sid = h.sid;
    
16. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. SELECT trim(name) dbname,trim(owner) owner,created,
   
3. TRIM(DBINFO('dbspace',partnum)) AS dbspace,
   
4. CASE WHEN is_logging+is_buff_log=1 THEN "Unbuffered logging"
   
5. WHEN is_logging+is_buff_log=2 THEN "Buffered logging"
   
6. WHEN is_logging+is_buff_log=0 THEN "No logging"
   
7. ELSE "" END Logging_mode
   
8. FROM sysdatabases;
   
9. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select t1.dbsname,
   
3. format_units(sum(ti_nptotal),max(ti_pagesize)) allocated_size,
   
4. format_units(sum(ti_npused),max(ti_pagesize)) used_size
   
5. from systabnames t1, systabinfo t2,sysdatabases t3
   
6. where t1.partnum = t2.ti_partnum
   
7. and trim(t3.name)=trim(t1.dbsname)
   
8. group by dbsname
   
9. order by sum(ti_nptotal) desc;
   
10. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select systables.tabname,
   
3. syscolumns.colname,
   
4. sysdistrib.constructed,mode
   
5. from sysdistrib,systables,syscolumns
   
6. where systables.tabid > 99
   
7. and systables.tabid = syscolumns.tabid
   
8. and sysdistrib.tabid=systables.tabid
   
9. and sysdistrib.colno = syscolumns.colno
   
10. group by 1,2,3,4
    
11. order by tabname,colname;
    
12. %%

复制代码