论坛 › informix › informix常用查询

admin 发表于 2020-1-13 18:21:08

informix常用查询

admin 发表于 2020-1-13 18:21:56

dbaccess sysmaster << %%
select env_id,env_name,env_value from sysenv;
%%

admin 发表于 2020-1-13 18:23:19

dbaccess sysmaster << %%
select env_id,env_name,env_value from sysenv;
%%

admin 发表于 2020-1-13 18:24:17

dbaccess sysmaster << %%
select
dbinfo('UTC_TO_DATETIME',sh_boottime) start_time,
current year to second - dbinfo('UTC_TO_DATETIME',sh_boottime) run_time,
sh_maxchunks as maxchunks,
sh_maxdbspaces maxdbspaces,
sh_maxuserthreads maxuserthreads,
sh_maxtrans maxtrans,
sh_maxlocks locks,
sh_nlrus buff_lrus,
sh_longtx longtxs,
dbinfo('UTC_TO_DATETIME',sh_pfclrtime) onstat_z_running_time
from sysshmvals;
%%

admin 发表于 2020-1-13 18:30:06

dbaccess sysmaster << %%
select cf_name,cf_effective,cf_original,cf_default from sysconfig;
%%

admin 发表于 2020-1-13 18:31:07

dbaccess sysmaster << %%
select * from sysprofile;
%%

admin 发表于 2020-1-13 18:31:54

dbaccess sysmaster << %%
select * from syscheckpoint
%%

admin 发表于 2020-1-13 18:32:48

dbaccess sysmaster << %%
select * from sysvpprof;
%%

admin 发表于 2020-1-13 18:33:33

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

admin 发表于 2020-1-13 18:34:20

dbaccess sysmaster << %%
SELECT A.dbsnum as No, trim(B.name) as name,
CASE WHEN (bitval(B.flags,'0x10')>0 AND bitval(B.flags,'0x2')>0)
THEN 'MirroredBlobspace'
WHEN bitval(B.flags,'0x10')>0 THEN 'Blobspace'
WHEN bitval(B.flags,'0x2000')>0 AND bitval(B.flags,'0x8000')>0
THEN 'TempSbspace'
WHEN bitval(B.flags,'0x2000')>0 THEN 'TempDbspace'
WHEN (bitval(B.flags,'0x8000')>0 AND bitval(B.flags,'0x2')>0)
THEN 'MirroredSbspace'
WHEN bitval(B.flags,'0x8000')>0 THEN 'SmartBlobspace'
WHEN bitval(B.flags,'0x2')>0 THEN 'MirroredDbspace'
ELSE 'Dbspace'
END as dbstype,
CASE WHEN bitval(B.flags,'0x4')>0 THEN 'Disabled'
WHEN bitand(B.flags,3584)>0 THEN 'Recovering'
ELSE 'Operational'
END as dbsstatus,
format_units(sum(chksize),max(A.pagesize)) as DBS_SIZE ,
format_units(sum(decode(mdsize,-1,nfree,udfree)),max(A.pagesize)) as free_size,
TRUNC(100-sum(decode(mdsize,-1,nfree,udfree))*100/sum(chksize),2)||'%' as used,
TRUNC(MAX(A.pagesize/1024)) as pgsize,
MAX(B.nchunks) as nchunks
FROM syschktab A, sysdbstab B
WHERE A.dbsnum = B.dbsnum
GROUP BY A.dbsnum,name, 3, 4
ORDER BY A.dbsnum;
%%

admin 发表于 2020-1-13 18:35:05

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

admin 发表于 2020-1-13 18:35:50

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

admin 发表于 2020-1-13 18:36:32

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

admin 发表于 2020-1-13 18:37:20

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

admin 发表于 2020-1-13 18:38:05

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

admin 发表于 2020-1-13 18:38:54

dbaccess sysmaster << %%
SELECT A.number as num, A.uniqid as uid, format_units(A.size,'P') as size,
TRIM( TRUNC(A.used*100/A.size,0)||'%') as used,
d.name as spacename,
TRIM( A.chunk||'_'||A.offset ) as location,
decode(A.filltime,0,'NotFull',
dbinfo('UTC_TO_DATETIME', A.filltime)::varchar(50)) as filltime,
CASE WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x4')>0
THEN 'UsedBackedUp'
WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x2')>0
THEN 'UsedCurrent'
WHEN bitval(A.flags,'0x1') > 0 THEN 'Used'
ELSE hex(A.flags)::varchar(50)
END as flags,
CASE WHEN A.filltime-B.filltime > 0 THEN
format_units(CAST(TRUNC(A.size/(A.filltime-B.filltime),4)
as varchar(20)) ,'P')||'/S'
ELSE ' N/A ' END as pps
FROM syslogfil A, syslogfil B,syschktab c, sysdbstab d
WHERE A.uniqid-1 = B.uniqid
and c.dbsnum = d.dbsnum
and a.chunk=c.chknum
UNION
SELECT A.number as num, A.uniqid as uid, format_units(A.size,'P') as size,
TRIM( TRUNC(A.used*100/A.size,0)||'%') as used,
d.name as spacename,
TRIM( A.chunk||'_'||A.offset ) as location,
decode(A.filltime,0,'NotFull',
dbinfo('UTC_TO_DATETIME', A.filltime)::varchar(50)) as filltime,
CASE WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x4')>0
THEN 'UsedBackedUp'
WHEN bitval(A.flags,'0x1') > 0 AND bitval(A.flags,'0x2')>0
THEN 'UsedCurrent'
WHEN bitval(A.flags,'0x1') > 0 THEN 'Used'
WHEN bitval(A.flags,'0x8') > 0 THEN 'NewAdd'
ELSE hex(A.flags)::varchar(50) END as flags,
'N/A' as pps
FROM syslogfil A ,syschktab c, sysdbstab d
WHERE ( A.uniqid = (SELECT min(uniqid) FROM syslogfil WHERE uniqid > 0)
OR A.uniqid = 0 )
and c.dbsnum = d.dbsnum
and a.chunk=c.chknum
ORDER BY A.uniqid ;
%%

admin 发表于 2020-1-13 18:39:40

dbaccess sysmaster << %%
SELECT
B.name as spacename, A.fname ,
pl_chunk||'_'||pl_offset as location,
format_units(pl_physize,'P') as size,
format_units(pl_phyused,'P') as used,
format_units(pl_bufsize,'P') as bufsize,
format_units(pl_phypos,'P') as start
FROM sysplog p, syschktab A, sysdbstab B
WHERE A.dbsnum = B.dbsnum
and p.pl_chunk=a.chknum;
%%

admin 发表于 2020-1-13 18:40:17

dbaccess sysmaster << %%
select dbsvrnm,nettype,hostname,svcname,options,
svrsecurity,netbuf_size,svrgroup
from syssqlhosts;
%%

admin 发表于 2020-1-13 18:41:01

dbaccess sysmaster << %%
select nc_name::char(8) name, nc_accepted,nc_rejected,nc_reads,nc_writes
from sysnetclienttype
where nc_reads>0;
%%

admin 发表于 2020-1-13 18:41:41

dbaccess sysmaster << %%
select s.sid,s.pid, s.username, s.hostname,q.odb_dbname database,
dbinfo('UTC_TO_DATETIME',s.connected) connection_time,
current - dbinfo('UTC_TO_DATETIME',s.connected)
connected_since
from syssessions s, sysopendb q
where s.sid = q.odb_sessionid
%%

查看完整版本: informix常用查询