执行选择查询时的DB2性能问题 [英] DB2 performance issue while executing select query

问题描述

我有这个查询：

SELECT INVOICE_NUMBER, INVOICE_SEQ_NUMBER, FILE_NUMBER, MAX(INVOICE_SEQ_NUMBER) OVER (PARTITION BY INVOICE_NUMBER) AS MAX_INV_SEQ_NUM
FROM (SELECT A.INVOICE_NUMBER, A.INVOICE_SEQ_NUMBER, B.FILE_NUMBER,
             DENSE_RANK() OVER (ORDER BY A.INVOICE_NUMBER) as seqnum
      FROM TABLE1 A JOIN
           TABLE2 B 
           ON A.INVOICE_NUMBER = B.INVOICE_NUMBER AND 
              A.INVOICE_SEQ_NUMBER = B.INVOICE_SEQ_NUMBER
     ) t
WHERE seqnum <= 500;

在表中有10000条记录时工作正常，但我们今天增加了更多（+ 30k）并进行了计划向表中添加更多（+ 250-300k）。现在我收到此错误：

It was working fine with 10000 records in the tables but we added more today(+30k) and planing to add even more(+250-300k) to the table. Now I am getting this error:

DB2 SQL Error: SQLCODE=-905, SQLSTATE=57014, SQLERRMC=ASUTIME;000000000007;000000009000;SYSTEM PARAMETER, DRIVER=3.65.77

查询是否不正确或是否针对表进行了优化有大量记录？我们应该如何处理呢？为某些列编制索引或重组查询？

Is the query not correct or is not optimized for tables with big number of records? How we should handle this? Index some columns or restructure the query?

更新：显然，我对添加到表中的记录数有误，目前在TABLE1中有200万条记录，而在TABLE2中有400万条记录，而不是我最初提到的30k。

UPDATE: apparently, I was mistaken about number of records added to the table, at this moment we have 2 million records in TABLE1 and 4 million records in TABLE2, instead of 30k I mentioned initially.

推荐答案

SQL0905N，针对主机查询超出了ASUTIME资源限制

您遇到了资源限制问题。

You hit a resource limitation problem.

问题

此文档提供以下故障排除信息：
应用程序对DB2 UDB z / OS版数据库运行查询时，
遇到错误：SQL0905N由于超出资源
限制，执行失败。资源名称= ASUTIME ...

This document provides troubleshooting information for when your application runs a query against a DB2 UDB for z/OS database and encounters the error: SQL0905N Unsuccessful execution due to resource limit being exceeded. Resource name = "ASUTIME"...

原因

资源正在超出的是在z /OS®表SYSIBM.DSNRLST01的DB2®
Universal Database™（DB2 UDB）中找到的ASUTIME参数。
ASUTIME指定每个
动态SQL语句允许的最大处理器服务单位（例如SELECT，UPDATE，INSERT或DELETE）。

The resource being exceeded is the ASUTIME parameter found in the DB2® Universal Database™ (DB2 UDB) for z/OS® table SYSIBM.DSNRLST01. ASUTIME specifies the maximum processor service units allowed per dynamic SQL statement (for example, SELECT, UPDATE, INSERT or DELETE).

问题详细信息，完整错误消息的示例如下：

Problem Details An example of the full error message is as follows:

COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2] SQL0905N
由于超出资源限制，执行失败。资源
名称= ASUTIME，限制= 000000000184 CPU秒（ 000005206074
服务单位）源自 SYSIBM.DSNRLST01。 SQLSTATE = 57014

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0905N Unsuccessful execution due to resource limit being exceeded. Resource name = "ASUTIME", limit = "000000000184" CPU seconds ("000005206074" service units) derived from "SYSIBM.DSNRLST01". SQLSTATE=57014

答案

重写复杂的查询，以减少处理器服务单位是消耗
或增加ASUTIME参数。除了动态SQL之外，ASUTIME参数
还适用于存储过程。

Rewrite the complex query so that less processor service units are consumed or increase the ASUTIME parameter. The ASUTIME parameter applies to stored procedures in addition to dynamic SQL.

这篇关于执行选择查询时的DB2性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！