常用脚本与sql

##　通过onstat -d输出获取dbs使用情况

```bash
cat <<! 
DBSID |DBSNAME        |PAGESIZE  |TOTAL_GB  |USED_GB
----------------------------------------------------
!

onstat -d |awk '
{
        if($3~/^0x*/)  #dbs
        {
                a[$2]=$6;
                c[$2]=$NF;
        }
        if($NF~/^\/.*/) #chunk
        {
                b[$3]+=($5-$6)*a[$3]/1000/1024/1024;
                d[$3]+=$5*a[$3]/1000/1024/1024
        }
}
END\
{
        for(i in b)
        {
                printf "%-6d %-15s %-10d %-10.2f %.2f\n" ,i,c[i],a[i],d[i],b[i];
        }
}'|sort -nk1
```

## 统计数据库库大小

```sql
--单位KB
SELECT   n.dbsname DATABASE,
         SUM(i.ti_nptotal * i.ti_pagesize)/1024 TOTAL_SIZE,
         SUM(i.ti_npused * i.ti_pagesize)/1024  USED_SIZE,
         SUM(i.ti_npdata * i.ti_pagesize)/1024  DATA_SIZE,
         SUM((i.ti_npused - i.ti_npdata) * i.ti_pagesize)/1024 INDEX_SIZE,
         SUM((i.ti_nptotal- i.ti_npused) * i.ti_pagesize)/1024 FREE_SIZE
FROM     systabnames n, systabinfo i, sysdatabases d
WHERE    d.name='xxxx' --xxxx替换成库名，删除该条件则统计所有库
AND  n.partnum = i.ti_partnum
AND      n.dbsname = d.name
GROUP BY 1;
```

## 统计一天内每小时日志数

```sh
d1=$1 # May 1
d2=$2 # May 2
tail -20000 /home/informix/tmp/online.log| sed -n '/'"$d1"' .*2024/,/'"$d2"' .*2024/ p'|\
        awk -F\: '
        {
                if($0~/Complete,.*\.$/ )
                {
                 a[$1]+=1
                }
        }\
        END\
        {
                for(i in a)
                {
                 print i,a[i]
                }
        }'|sort -nk1

```
## 查看启动时间
``` sql
select 
 dbinfo('UTC_TO_DATETIME',sh_boottime) start_time, 
 current year to second - dbinfo('UTC_TO_DATETIME',sh_boottime) run_time
 from sysmaster:sysshmvals;
```

## 获取主键字段名
```sql
select colname from systables a,sysconstraints b,sysindexes c,syscolumns d
where a.tabname='table_name'
and a.tabid=b.tabid
and a.tabid=c.tabid
and a.tabid=d.tabid
and b.constrtype='P'
and b.idxname=c.idxname
and (
colno=part1 or colno=part2 or colno=part3 or colno=part4 
or colno=part5 or colno=part6 or colno=part7 or colno=part8 
or colno=part9 or colno=part10 or colno=part11 or colno=part12 
or colno=part13 or colno=part14 or colno=part15 or colno=part16 
);
```
## 获取唯一索引与其字段名

```sql
select i.idxname,c.colname from sysindexes i,systables t,syscolumns c
where
t.tabname='tabname'
and 
	t.tabid=i.tabid  and i.idxtype='U' and c.tabid=t.tabid and (
colno=part1 or colno=part2 or colno=part3 or colno=part4
or colno=part5 or colno=part6 or colno=part7 or colno=part8
or colno=part9 or colno=part10 or colno=part11 or colno=part12
or colno=part13 or colno=part14 or colno=part15 or colno=part16)
order by 1;
```
## 取逻辑日志最早时间

```sh
onstat -l|awk '/C-/ {a=$4};/ total/ {b=a-$1+1} END {print "onlog -n "b"|grep BEGIN|head"}'|sh
```
## 查看库位于哪个dbspace
```txt
echo "select name,dbinfo('dbspace',partnum) from sysdatabases where name='xxdb';"|dbaccess sysmaster
```