DB2查看执行过长的SQL

朋友遇到一个DB2问题，DB2占用了大量的临时表空间，db2pd -d testdb -act发现有大量的SQL长时间运行，为了测试该问题，写了一个简单的处理问题的流程。
1.创建测试表

https://www.cndba.cn/hbhe0316/article/4916

CREATE TABLE
    FUND_DAILY_INCOME
    (
        ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
        ACCOUNT_NO VARCHAR(32),
        CREATED_TIME TIMESTAMP,
        FUND_CODE VARCHAR(8),
        SHARE_HOLDING VARCHAR(15),
        INCOME_DATE VARCHAR(10),
        INCOME_AMOUNT BIGINT,
        MERGE_FLAG VARCHAR(1),
        OFFSET_FLAG VARCHAR(1),
        UPDATE_TIME TIMESTAMP,
        COMPANY_CODE VARCHAR(16),
        ALL_INCOME DECIMAL(19,0) DEFAULT 0,
        HOLDING_INCOME DECIMAL(19,0) DEFAULT 0,
        PLAT_ID BIGINT DEFAULT 10000,
        SERVICE_TYPE VARCHAR(2) DEFAULT '01',
        SERVICE_CODE VARCHAR(32) DEFAULT '10000',
        PRIMARY KEY (ID)
    );

2.创建测试存储过程

https://www.cndba.cn/hbhe0316/article/4916

https://www.cndba.cn/hbhe0316/article/4916 https://www.cndba.cn/hbhe0316/article/4916

[db2inst1@node02 ~]$ db2 -td@ -vf 1.sql
[db2inst1@node02 ~]$ cat 1.sql 
BEGIN   
   DECLARE v_date  date;
   DECLARE v_date_str varchar(20);
   DECLARE str varchar(20);
   DECLARE i  INTEGER;
   DECLARE val  INTEGER;
   declare    sqlStr   varchar(1000);
   set  v_date = to_date( '2014-01-01' ,'yyyy-mm-dd');
   set v_date_str =  to_char( v_date ,'yyyy-mm-dd') ;
   REPEAT
              set i = 1 ;
              REPEAT      
               SET sqlStr =' INSERT INTO FUND_DAILY_INCOME (  ACCOUNT_NO, CREATED_TIME,  FUND_CODE, SHARE_HOLDING, INCOME_DATE,  INCOME_AMOUNT, MERGE_FLAG, OFFSET_FLAG, UPDATE_TIME, COMPANY_CODE  ) VALUES '
               ||  '  (  ''1'', current timestamp , ''1'', ''1'',  '''||  v_date_str ||''' , 0, ''1'', ''1'', current timestamp , ''1'' ) ';
               EXECUTE IMMEDIATE sqlStr;   
               set i = i +1;
               UNTIL ( i > 1010000000)
               END REPEAT;
       set v_date =   v_date +1 day;
       set v_date_str = to_char( v_date ,'yyyy-mm-dd');
       set str =  to_char( v_date ,'yyyymmdd');
       set val = integer(str);
   UNTIL ( val > 20150101)
   END REPEAT;
END
@

3.查看正在执行的SQLhttps://www.cndba.cn/hbhe0316/article/4916

[db2inst1@node02 ~]$ db2pd -d testdb -act

Database Member 0 -- Database TESTDB -- Active -- Up 0 days 00:34:45 -- Date 2021-10-12-16.32.27.821453

Active Statement List:
Address            AppHandl [nod-index] UOW-ID     StmtID     AnchID StmtUID    EffISO      EffLockTOut EffDegree   EntryTime           StartTime           LastRefTime        
0x00007FCBE1F7BC20 7        [000-00007] 58         90956      573    2          1           -1          1           Tue Oct 12 16:32:27 Tue Oct 12 16:32:27 Tue Oct 12 16:32:27
0x00007FCBE1F7F6C0 7        [000-00007] 58         1          931    1          1           -1          1           Tue Oct 12 16:32:20 Tue Oct 12 16:32:20 Tue Oct 12 16:32:20

4.查看agentid为7的SQL

[db2inst1@node02 ~]$ db2 get snapshot for application agentid 7

            Application Snapshot

Application handle                         = 7
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Application country/region code            = 1
DUOW correlation token                     = *LOCAL.db2inst1.211012075742
Application name                           = db2bp
Application ID                             = *LOCAL.db2inst1.211012075742
Sequence number                            = 00059
TP Monitor client user ID                  =
TP Monitor client workstation name         =
TP Monitor client application name         = CLP 1.sql
TP Monitor client accounting string        =

Connection request start timestamp         = 10/12/2021 15:57:42.602917
Connect request completion timestamp       = 10/12/2021 15:57:43.809901
Application idle time                      = Not Collected
CONNECT Authorization ID                   = DB2INST1
Client login ID                            = db2inst1
Configuration NNAME of client              = node02
Client database manager product ID         = SQL11014
Process ID of client application           = 4224
Platform of client application             = LINUXAMD64
Communication protocol of client           = Local Client

Inbound communication address              = *LOCAL.db2inst1

Database name                              = TESTDB
Database path                              = /db2data/db2inst1/NODE0000/SQL00001/MEMBER0000/
Client database alias                      = TESTDB
Input database alias                       =
Last reset timestamp                       =
Snapshot timestamp                         = 10/12/2021 16:32:44.043410
Authorization level granted                =
   User authority:
      DBADM authority
      SECADM authority
      DATAACCESS authority
      ACCESSCTRL authority
   Group authority:
      SYSADM authority
      CREATETAB authority
      BINDADD authority
      CONNECT authority
      IMPLICIT_SCHEMA authority
Coordinator member number                  = 0
Current member number                      = 0
Coordinator agent process or thread ID     = 23
Current Workload ID                        = 1
Agents stolen                              = 0
Agents waiting on locks                    = 0
Maximum associated agents                  = 1
Priority at which application agents work  = 0
Priority type                              = Dynamic

Lock timeout (seconds)                     = -1
Locks held by application                  = 0
Lock waits since connect                   = 0
Time application waited on locks (ms)      = Not Collected
Deadlocks detected                         = Not Collected
Lock escalations                           = 0
Exclusive lock escalations                 = 0
Number of Lock Timeouts since connected    = 0
Total time UOW waited on locks (ms)        = Not Collected

Total sorts                                = 0
Total sort time (ms)                       = Not Collected
Total sort overflows                       = 0

Buffer pool data logical reads             = Not Collected
Buffer pool data physical reads            = Not Collected
Buffer pool temporary data logical reads   = Not Collected
Buffer pool temporary data physical reads  = Not Collected
Buffer pool data writes                    = Not Collected
Buffer pool index logical reads            = Not Collected
Buffer pool index physical reads           = Not Collected
Buffer pool temporary index logical reads  = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes                   = Not Collected
Buffer pool xda logical reads              = Not Collected
Buffer pool xda physical reads             = Not Collected
Buffer pool temporary xda logical reads    = Not Collected
Buffer pool temporary xda physical reads   = Not Collected
Buffer pool xda writes                     = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Time waited for prefetch (ms)              = Not Collected
Unread prefetch pages                      = Not Collected
Direct reads                               = Not Collected
Direct writes                              = Not Collected
Direct read requests                       = Not Collected
Direct write requests                      = Not Collected
Direct reads elapsed time (ms)             = Not Collected
Direct write elapsed time (ms)             = Not Collected

Number of SQL requests since last commit   = 0
Commit statements                          = 3
Rollback statements                        = 55
Dynamic SQL statements attempted           = 236518
Static SQL statements attempted            = 58
Failed statement operations                = 56
Select SQL statements executed             = 1
Xquery statements executed                 = 0
Update/Insert/Delete statements executed   = 236460
DDL statements executed                    = 1
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds                 = 0
Internal rows deleted                      = 0
Internal rows inserted                     = 0
Internal rows updated                      = 0
Internal commits                           = 1
Internal rollbacks                         = 0
Internal rollbacks due to deadlock         = 0
Binds/precompiles attempted                = 0
Rows deleted                               = 0
Rows inserted                              = 236459
Rows updated                               = 0
Rows selected                              = 1
Rows read                                  = 12537
Rows written                               = 236466

UOW log space used (Bytes)                 = Not Collected
Previous UOW completion timestamp          = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp                        = Not Collected
UOW stop timestamp                         = Not Collected
UOW completion status                      = Not Collected

Open remote cursors                        = 0
Open remote cursors with blocking          = 0
Rejected Block Remote Cursor requests      = 0
Accepted Block Remote Cursor requests      = 1
Open local cursors                         = 0
Open local cursors with blocking           = 0
Total User CPU Time used by agent (s)      = 4.522643
Total System CPU Time used by agent (s)    = 0.000000
Host execution elapsed time                = Not Collected

Package cache lookups                      = 236521
Package cache inserts                      = 374
Application section lookups                = 474448
Application section inserts                = 371
Catalog cache lookups                      = 3363
Catalog cache inserts                      = 42
Catalog cache overflows                    = 0
Catalog cache high water mark              = 0

Workspace Information


Most recent operation                      = Static Rollback
Most recent operation start timestamp      = Not Collected
Most recent operation stop timestamp       = Not Collected
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Number of OLAP functions                   = 0
Number of OLAP function overflows          = 0

Memory usage for application:

  Memory Pool Type                         = Application Heap
     Current size (bytes)                  = 262144
     High water mark (bytes)               = 262144
     Configured size (bytes)               = 1048576

Agent process/thread ID                    = 23
  Agent Lock timeout (seconds)             = -1
  Memory usage for agent:

    Memory Pool Type                       = Other Memory
       Current size (bytes)                = 327680
       High water mark (bytes)             = 655360
       Configured size (bytes)             = 16656289792

6.收集快照https://www.cndba.cn/hbhe0316/article/4916

https://www.cndba.cn/hbhe0316/article/4916

https://www.cndba.cn/hbhe0316/article/4916

https://www.cndba.cn/hbhe0316/article/4916

[db2inst1@node02 ~]$ db2 get snapshot for all on testdb > /tmp/1.out
查看里面的内容
Application handle                         = 7
Application status                         = UOW Executing
Status change time                         = Not Collected
Application code page                      = 1208
Application country/region code            = 1
DUOW correlation token                     = *LOCAL.db2inst1.211012084818
Application name                           = db2bp
Application ID                             = *LOCAL.db2inst1.211012084818
Sequence number                            = 00002
TP Monitor client user ID                  =
TP Monitor client workstation name         =
TP Monitor client application name         = CLP 1.sql
TP Monitor client accounting string        =

7.force掉该agentidhttps://www.cndba.cn/hbhe0316/article/4916

[db2inst1@node02 ~]$ db2 "force application (7)"
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

[db2inst1@node02 ~]$ db2 terminate
DB20000I  The TERMINATE command completed successfully.

版权声明：本文为博主原创文章，未经博主允许不得转载。

DB2