0

I have this 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;

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?

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.

Foxy
  • 416
  • 8
  • 18
  • Looks like that error means your resource limits have exceeded. I would suggest limiting and paging through results. Are you hosting locally or in the cloud? Might be a way to increase memory. And yes, indexing would help. – Isaac Vidrine Jul 03 '19 at 20:30
  • Time is a resource and the query exceeded the limit. You join tables A and B but what condition are you looking for? Is there a way to limit the result set (and speed up the query)? – Jim Castro Jul 03 '19 at 21:19
  • I would restructure the query and also check the explain plan, esp the join on the INVOICE_NUMBER, INVOICE_SEQ_NUMBER pair... is the cardanality of that estimated well? – Paul Vernon Jul 03 '19 at 22:45
  • @IsaacVidrine I used dates, but even then it can only get for two days maximum. If I try more it gives me same error. Tables have records for last 5 years. I can't increase memory and it is not an option. Indexing is already done. – Foxy Jul 05 '19 at 15:50
  • @Foxy the result set from your query is too large, you need to limit and page through query results. So fetch like 100 rows at a time, process them and then go on to the next 100 rows. Paging should always be used when queries can return massive data sets. Otherwise, youll run into issues with memory at the database level or application level. Imagine trying to read 1 million rows into memory of your application? Even if it doesnt fail in the db, your application will probably crash. Therefore, **paging** is extremely important for scaling. – Isaac Vidrine Jul 05 '19 at 16:11

2 Answers2

0

SQL0905N, ASUTIME resource limit exceeded for query against mainframe

You hit a resource limitation problem.

Question

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"...

Cause

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 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

Answer

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.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
0

I ran into this with a DB2 query that was simply returning a count of records. I could run the select statement, but not a count with the same joins. It was a simple query with only a few joins. I was able to run the same query using a different DB2 driver.

shonthom
  • 41
  • 4
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/31261651) – Ethan Mar 16 '22 at 03:22