Db2查询所有处于load pending状态或正在load的表

最新推荐文章于 2023-06-14 20:59:50 发布

匿_名_用_户

最新推荐文章于 2023-06-14 20:59:50 发布

阅读量8k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： DB2 文章标签： Db2 load pending

本文链接：https://blog.youkuaiyun.com/qingsong3333/article/details/88925333

DB2 专栏收录该内容

181 篇文章

订阅专栏

本文介绍如何使用Db2的ADMINTABINFO查询表的load状态，包括如何检查LOAD_STATUS字段了解loadpending的原因，并通过loadquerytable命令获取更多信息。此外，还讨论了在loadpending状态下尝试访问表可能遇到的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Db2中可以使用ADMINTABINFO来查询表的load的状态,如果LOAD_STATUS字段值为PENDING，说明为load pending，如果值为IN_PROGRESS，说明正在做load操作，示例：

qingsong@node01:~$ db2 "select substr(TABSCHEMA,1,20) as TABSCHEMA, substr(TABNAME,1,20) as TABNAME from  SYSIBMADM.ADMINTABINFO where LOAD_STATUS='PENDING'"

TABSCHEMA            TABNAME             
-------------------- --------------------
INST105              T3                  
INST105              T4                  

  2 record(s) selected.

要查看load pending的原因，除了查看db2diag.log，可以尝试使用load query table命令，在下面的例子中，t3是因为load被中断（可能是被force)了，t4是因为load的表所在表空间满了：

qingsong@node01:~$ db2 "load query table inst105.t3"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file 
"/home/inst105/b.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "03/30/2019 
18:46:45.355904".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL1224N  The database manager is not able to accept new requests, has 
terminated all requests in progress, or has terminated the specified request 
because of an error or a forced interrupt.  SQLSTATE=55032

SQL3532I  The Load utility is currently in the "LOAD" phase.


Number of rows read         = 0
Number of rows skipped      = 0
Number of rows loaded       = 0
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 0
Number of warnings          = 0

Tablestate:
  Load Pending
qingsong@node01:~$ db2 "load query table inst105.t4"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3109N  The utility is beginning to load data from file 
"/home/inst105/b.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "03/30/2019 
18:55:36.042533".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL0289N  Unable to allocate new pages in table space "TBS2".  SQLSTATE=57011

SQL3532I  The Load utility is currently in the "LOAD" phase.


Number of rows read         = 0
Number of rows skipped      = 0
Number of rows loaded       = 0
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 0
Number of warnings          = 0

Tablestate:
  Load Pending

另外，如果尝试访问load pending状态的表，比如runstats、truncate，可能会在诊断日志中报出"ACCESS TABLE WHEN IN RESTRICTED STATE"的错误，例如：

2019-03-30-18.56.22.819074-420 I136437E962           LEVEL: Severe
PID     : 2490                 TID : 140120866940672 PROC : db2sysc 0
INSTANCE: inst105              NODE : 000            DB   : SAMPLE
APPHDL  : 0-32                 APPID: *LOCAL.inst105.190331014725
AUTHID  : INST105              HOSTNAME: node01
EDUID   : 92                   EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, catalog services, sqlrltt0, probe:10
MESSAGE : ZRC=0x8004002E=-2147221458=SQLD_ACCESSRESTRICTED
          "ACCESS TABLE WHEN IN RESTRICTED STATE"
          DIA8070C Attempt to access a table in a restricted state
<...略...>

参考资料：

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.rtn.doc/doc/r0052897.html