informix常用查询

admin

admin

1. dbaccess sysmaster << %%
   
2. select env_id,env_name,env_value from sysenv;
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select env_id,env_name,env_value from sysenv;
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select
   
3. dbinfo('UTC_TO_DATETIME',sh_boottime) start_time,
   
4. current year to second - dbinfo('UTC_TO_DATETIME',sh_boottime) run_time,
   
5. sh_maxchunks as maxchunks,
   
6. sh_maxdbspaces maxdbspaces,
   
7. sh_maxuserthreads maxuserthreads,
   
8. sh_maxtrans maxtrans,
   
9. sh_maxlocks locks,
   
10. sh_nlrus buff_lrus,
    
11. sh_longtx longtxs,
    
12. dbinfo('UTC_TO_DATETIME',sh_pfclrtime) onstat_z_running_time
    
13. from sysshmvals;
    
14. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select cf_name,cf_effective,cf_original,cf_default from sysconfig;
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select * from sysprofile;
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select * from syscheckpoint
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select * from sysvpprof;
   
3. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select vpid,classname class,pid,round(usecs_user,2) user_cpu,round(usecs_sys,2) sys_cpu,num_ready,
   
3. total_semops,total_busy_wts,total_yields,total_spins,vp_cache_size,vp_cache_allocs
   
4. from sysvplst ;
   
5. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. SELECT A.dbsnum as No, trim(B.name) as name,
   
3. CASE WHEN (bitval(B.flags,'0x10')>0 AND bitval(B.flags,'0x2')>0)
   
4. THEN 'MirroredBlobspace'
   
5. WHEN bitval(B.flags,'0x10')>0 THEN 'Blobspace'
   
6. WHEN bitval(B.flags,'0x2000')>0 AND bitval(B.flags,'0x8000')>0
   
7. THEN 'TempSbspace'
   
8. WHEN bitval(B.flags,'0x2000')>0 THEN 'TempDbspace'
   
9. WHEN (bitval(B.flags,'0x8000')>0 AND bitval(B.flags,'0x2')>0)
   
10. THEN 'MirroredSbspace'
    
11. WHEN bitval(B.flags,'0x8000')>0 THEN 'SmartBlobspace'
    
12. WHEN bitval(B.flags,'0x2')>0 THEN 'MirroredDbspace'
    
13. ELSE 'Dbspace'
    
14. END as dbstype,
    
15. CASE WHEN bitval(B.flags,'0x4')>0 THEN 'Disabled'
    
16. WHEN bitand(B.flags,3584)>0 THEN 'Recovering'
    
17. ELSE 'Operational'
    
18. END as dbsstatus,
    
19. format_units(sum(chksize),max(A.pagesize)) as DBS_SIZE ,
    
20. format_units(sum(decode(mdsize,-1,nfree,udfree)),max(A.pagesize)) as free_size,
    
21. TRUNC(100-sum(decode(mdsize,-1,nfree,udfree))*100/sum(chksize),2)||'%' as used,
    
22. TRUNC(MAX(A.pagesize/1024)) as pgsize,
    
23. MAX(B.nchunks) as nchunks
    
24. FROM syschktab A, sysdbstab B
    
25. WHERE A.dbsnum = B.dbsnum
    
26. GROUP BY A.dbsnum,name, 3, 4
    
27. ORDER BY A.dbsnum;
    
28. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select name dbspace, sum(chksize) allocated, sum(nfree) free,
   
3. round(((sum(chksize) - sum(nfree))/sum(chksize))*100,2) pcused
   
4. from sysmaster:sysdbspaces d, sysmaster:syschunks c
   
5. where d.dbsnum = c.dbsnum
   
6. group by name order by 4 desc,name;
   
7. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select distinct t.dbsname database, d.name dbspace, t.tabname
   
3. from sysmaster:sysdbstab d, sysmaster:syschunks c, sysmaster:sysextents t
   
4. where t.chunk = c.chknum and c.dbsnum=d.dbsnum
   
5. and t.dbsname not like 'sys%' and t.dbsname != 'onpload'
   
6. and t.tabname not like 'sys%' and d.name ='rootdbs' ;
   
7. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select d.name dbspace, fname[1,125] chunk_name,
   
3. sum(pagesread) diskreads, sum(pageswritten) diskwrites,
   
4. sum(pagesread)+sum(pageswritten) disk_rwes
   
5. from sysmaster:syschkio c, sysmaster:syschunks k, sysmaster:sysdbspaces d
   
6. where d.dbsnum = k.dbsnum and k.chknum  = c.chunknum  --# c.chknum
   
7. group by 1, 2 order by 5 desc;
   
8. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select d.name,d.pagesize, t.fname,t.chksize,t.nfree
   
3. from syschunks t,sysdbspaces d
   
4. where t.dbsnum=d.dbsnum and d.is_temp=1 order by 1;   
   
5. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. SELECT A.chknum as num, B.name as spacename,
   
3. trunc(A.pagesize/1024)||' KB' as pgsize,
   
4. format_units(A.offset, A.pagesize) as off,
   
5. format_units(A.chksize, A.pagesize) as size,
   
6. format_units(decode(A.mdsize,-1,A.nfree,A.udfree),A.pagesize) as free,
   
7. TRUNC(100 - decode(A.mdsize,-1,A.nfree,A.udfree)*100/A.chksize,2 )|| '%' as used,
   
8. A.fname
   
9. FROM syschktab A, sysdbstab B
   
10. WHERE A.dbsnum = B.dbsnum
    
11. order by B.dbsnum;
    
12. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. SELECT A.number as num, A.uniqid as uid, format_units(A.size,'P') as size,
   
3. TRIM( TRUNC(A.used*100/A.size,0)||'%') as used,
   
4. d.name as spacename,
   
5. TRIM( A.chunk||'_'||A.offset ) as location,
   
6. decode(A.filltime,0,'NotFull',
   
7. dbinfo('UTC_TO_DATETIME', A.filltime)::varchar(50)) as filltime,
   
8. CASE WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x4')>0
   
9. THEN 'UsedBackedUp'
   
10. WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x2')>0
    
11. THEN 'UsedCurrent'
    
12. WHEN bitval(A.flags,'0x1') > 0 THEN 'Used'
    
13. ELSE hex(A.flags)::varchar(50)
    
14. END as flags,
    
15. CASE WHEN A.filltime-B.filltime > 0 THEN
    
16. format_units(CAST(TRUNC(A.size/(A.filltime-B.filltime),4)
    
17. as varchar(20)) ,'P')||'/S'
    
18. ELSE ' N/A ' END as pps
    
19. FROM syslogfil A, syslogfil B,syschktab c, sysdbstab d
    
20. WHERE A.uniqid-1 = B.uniqid
    
21. and c.dbsnum = d.dbsnum
    
22. and a.chunk=c.chknum
    
23. UNION
    
24. SELECT A.number as num, A.uniqid as uid, format_units(A.size,'P') as size,
    
25. TRIM( TRUNC(A.used*100/A.size,0)||'%') as used,
    
26. d.name as spacename,
    
27. TRIM( A.chunk||'_'||A.offset ) as location,
    
28. decode(A.filltime,0,'NotFull',
    
29. dbinfo('UTC_TO_DATETIME', A.filltime)::varchar(50)) as filltime,
    
30. CASE WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x4')>0
    
31. THEN 'UsedBackedUp'
    
32. WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x2')>0
    
33. THEN 'UsedCurrent'
    
34. WHEN bitval(A.flags,'0x1') > 0 THEN 'Used'
    
35. WHEN bitval(A.flags,'0x8') > 0 THEN 'NewAdd'
    
36. ELSE hex(A.flags)::varchar(50) END as flags,
    
37. 'N/A' as pps
    
38. FROM syslogfil A ,syschktab c, sysdbstab d
    
39. WHERE ( A.uniqid = (SELECT min(uniqid) FROM syslogfil WHERE uniqid > 0)
    
40. OR A.uniqid = 0 )
    
41. and c.dbsnum = d.dbsnum
    
42. and a.chunk=c.chknum
    
43. ORDER BY A.uniqid ;
    
44. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. SELECT
   
3. B.name as spacename, A.fname ,
   
4. pl_chunk||'_'||pl_offset as location,
   
5. format_units(pl_physize,'P') as size,
   
6. format_units(pl_phyused,'P') as used,
   
7. format_units(pl_bufsize,'P') as bufsize,
   
8. format_units(pl_phypos,'P') as start
   
9. FROM sysplog p, syschktab A, sysdbstab B
   
10. WHERE A.dbsnum = B.dbsnum
    
11. and p.pl_chunk=a.chknum;
    
12. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select dbsvrnm,nettype,hostname,svcname,options,
   
3. svrsecurity,netbuf_size,svrgroup
   
4. from syssqlhosts;
   
5. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select nc_name::char(8) name, nc_accepted,nc_rejected,nc_reads,nc_writes
   
3. from sysnetclienttype
   
4. where nc_reads>0;
   
5. %%

复制代码

admin

1. dbaccess sysmaster << %%
   
2. select s.sid,s.pid, s.username, s.hostname,q.odb_dbname database,
   
3. dbinfo('UTC_TO_DATETIME',s.connected) connection_time,
   
4. current - dbinfo('UTC_TO_DATETIME',s.connected)
   
5. connected_since
   
6. from syssessions s, sysopendb q
   
7. where s.sid = q.odb_sessionid
   
8. %%

复制代码