问题描述

gbase数据库的某张表突然无法查询和新增字段。

问题分析

经过百度查询了一下，发现是锁表导致的。

解决办法

1. 查找被锁住的表

select hex(partnum) from systables where tabname = 'bsp_data_file_reception';

0x0060868C

2. 查找活跃锁的信息（shell命令）

[informix@jcssmaster ~]$ onstat -k | grep 60868C -i
Your evaluation license will expire on 2023-03-17 00:00:00
90845320         0                640109e8           8a089e68         HDR+X    60868c   0           0  

3. 查找用户线索和事务

[informix@jcssmaster ~]$ onstat -u | grep 640109e8
Your evaluation license will expire on 2023-03-17 00:00:00
640109e8         Y-BP--- 46852    informix -        6cc4ca20         0    96    0        0

4. 查看会话信息

[informix@jcssmaster ~]$ onstat -g ses 46852
Your evaluation license will expire on 2023-03-17 00:00:00

GBase 8s Database Server Version 12.10.FC4G1TL -- On-Line -- Up 21 days 22:06:07 -- 19971048 Kbytes

session           effective                            #RSAM    total      used       dynamic 
id       user     user      tty      pid      hostname threads  memory     memory     explain 
46852    informix -         -        0        localhos 1        147456     112080     off 

Program :
/E:/devSoft/gbase/GBaseDataStudio_8.5.19.0_Windows_x86_64/GBaseDataStudio_8.5.19.0_Windows_x86_64/x86_64/GBaseDataStudio/configuration/org.eclipse.osgi/bundles/12/1/.cp/lib/ifxjdbc.jarorg.eclipse.core.internal.jobs/Worker

tid      name     rstcb            flags    curstk   status
50731    sqlexec  640109e8         Y-BP---  3936     cond wait  netnorm   -

Memory pools    count 2
name         class addr              totalsize  freesize   #allocfrag #freefrag 
46852        V     c7517040         143360     34568      129        18        
46852*O0     V     97edc040         4096       808        1          1         

name           free       used           name           free       used      
overhead       0          6576           scb            0          144       
opentable      0          10272          filetable      0          2112      
ru             0          600            log            0          16536     
temprec        0          33952          keys           0          624       
gentcb         0          1640           ostcb          0          2944      
sqscb          0          24056          sql            0          72        
hashfiletab    0          552            osenv          0          2088      
sqtcb          0          9256           fragman        0          656       

sqscb info
scb              sqscb            optofc   pdqpriority optcompind  directives
665c21c0         cd46e028         0        0           2           1         

Sess       SQL            Current            Iso Lock       SQL  ISAM F.E. 
Id         Stmt type      Database           Lvl Mode       ERR  ERR  Vers  Explain    
46852      -              mztjsdm            LC  Not Wait   0    0    9.28  Off        

Last parsed SQL statement :
  SELECT ODB_DBName FROM SysMaster:SysOpenDB WHERE ODB_IsCurrent = 'Y' AND
    ODB_SessionID = DBINFO('sessionid')

5. 断开指定用户的连接

[informix@jcssmaster ~]$ onmode -z 46852
Your evaluation license will expire on 2023-03-17 00:00:00

执行中遇到的问题

1. onstat: 未找到命令

解决办法：

1. 切换用户，从root切换到informix

[root@jcssmaster ~]# su informix

2. 刷新gbase配置文件

[informix@jcssmaster root]$ cd /home/informix
[informix@jcssmaster ~]$ ls
dbimport.out                         InitGBaseDB_202203171819.log  InitGBaseDB_202203172000.log  profile.gbaseserver
GBaseInit_informix_V1.15_for-225.sh  InitGBaseDB_202203171821.log  profile.]
[informix@jcssmaster ~]$ source profile.gbaseserver

2.断开指定用户连接后，执行drop table等命令仍然报错

解决办法：重启数据库

1. 关闭所有连接数据库的服务、客户端
2. 关闭数据库

onmode -ky

3. 启动数据库

oninit -vy