Solved

Error "Resource limit exceeded..."

Posted on 2000-05-04
16
5,730 Views
Last Modified: 2012-05-07
I get the following error on running a VFP6 (sp3) executable.
The program reads a dB/2 database on an IBM AS400 (The AS/400 is a CISC machine running on V3R2M0 of the OS/400) using IBM Client Access (Version level 3.1.2). I use an ADO connection in a VB6 ActiveX DLL. . The AS/400 in question is very heavily loaded and I feel that this could/may be causing the error below, but I have no ammunition with which to prove this to the customer. Initially,  I thought the problem was to do with the number of licenses the customer had for query/sql/client access, but their license limits is set to *nomax, so this obviously is not the issue.
The query runs ok for less than 1000 records but the error below occurs when the number is greater than 1000.

Error: OLE IDispatch exception code 0 from dbQuery.Execute: [IBM][Client Access ODBC Driver (32-bit) ][DB2/400 SQL]SQL0904 - Resource limit exceeded…
What causes this?
Thank you all in advance
0
Comment
Question by:93MH1351
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 18

Expert Comment

by:deighton
Comment Utility

You need to find the message on the AS/400 to determine which of the following caused the error.

Find the job that services the SQL call and look at its joblog.

From the AS/400 message files

COMMAND wrkmsgf qsqlmsg


Message ID . . . . . . . . . :   SQL0904                                      
Message file . . . . . . . . :   QSQLMSG                                      
  Library  . . . . . . . . . :     QSYS                                        
                                                                               
Message . . . . :   Resource limit exceeded.                                  
Cause . . . . . :   Resource limit type &1 exceeded.  A list of the limit      
  types follows:                                                              
    -- Type 1 indicates that the user profile storage limit or the machine    
  storage limit was exceeded.                                                  
    -- Type 2 indicates that the machine lock limit was exceeded.              
    -- Type 3 indicates that the query resource limit was exceeded.  For more  
  information see the previously listed message CPD4365.                      
    -- Type 4 indicates that a journal error has occurred.                    
    -- Type 5 indicates that the commit lock limit was exceeded.              
    -- Type 6 indicates that the maximum size of the table has been reached.  
    -- Type 7 indicates that the maximum size of the prepared statement area  
  has been reached.                                

 Recovery  . . . :   Do one of the following: If this is error type 1, contact  
   the security officer to increase the user profile storage limit, or delete  
   some objects to free up storage and then try the request again.              
     -- If this is error type 2, then try the operation when the number of      
   machine locks held has decreased.                                            
     -- If this is error types 3, 4, or 5, see previously listed messages in    
   the job log for recovery information.                                        
     -- If this is error type 6, Some of the rows from this table must be moved
   to another table.                                                            
     -- If this is error type 7, issue a COMMIT or ROLLBACK without the HOLD    
   clause before issuing anymore PREPARE statements.                            
                                                                                                           
                                       
0
 
LVL 3

Expert Comment

by:P_S_Price
Comment Utility
I suspect that you do have a Commit problem. If memory serves me correctly on operating systems that old the Commitment Control Threshold was a maximum of 1000 records (ie you could only have up to 100 records before either issuing a commit or roll back.
0
 

Author Comment

by:93MH1351
Comment Utility
The info given is not detailed enough to solve the problem
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
Have you got the message data up on the AS/400?  

You need to look at this to determine exactly which error scenario you have.

Please let us know!
0
 
LVL 3

Expert Comment

by:P_S_Price
Comment Utility
Ok more Info I suspect that you are accessing the records for update. The AS/400 uses Journalling to support commitment control This, on your version of the operating system supports a maximum of 1000 records in a commit block. IE you can have read 1000 records, max, for update without issuing a commit or roll back but when you read 1001 you get the resource error.You should ensure that your VB ActiveX DLL or whatever you are using to access the AS/400 either does not use a committed transaction set, or issues a commit after each retrieval operation. The dead giveaway here is the 1000 record business. I would suggest that you post details (code) on how you are accessing the data.
0
 

Author Comment

by:93MH1351
Comment Utility
The message below is the CPD4365 message that is coming back on the AS/400 when the PC program bombs out with the SQL0904 message. I have checked the user's joblog in subsystem QZDASOINIT, and it throws up the following message with a reason code 1.
I have no idea what this means.
Does this indicate whether the problem is coming from the AS400 or the PC?

The message below does not give any more detail for reason code 1.
What causes this?
How do I work around it?

Message ID . . . . .. :   CPD4365       Severity . . . . . . . :   40        
Message type . . . . . :   Diagnostic                                          
Date sent  . . . . . . :   08/05/00      Time sent  . . . . . . :   09:28:02  
                                                                               
Message . . .. :   Query resource limit exceeded. Reason code 1.              
Cause . . . . . The type of resource limit exceeded is indicated by reason
  code 1.  The reason codes and their reasons follow:                          
    1 -- More than 1,024 literals.                                            
    2 -- More than 32,768 fields in an internal buffer.                        
    3 -- More than 65,504 bytes in an internal buffer.                        
    4 -- More than 4,194,304 bytes in an internal work space.                  
    5 -- An attempt to create an internal query object exceeded the maximum size for such an object
    6 -- More than 8,000 fields in a record format are required for creation  
  of a temporary result.                                                      
    7 -- More than 32,766 bytes in a record format required for the creation  
                                                                       More...
0
 
LVL 3

Expert Comment

by:P_S_Price
Comment Utility
Ah this may be your SQL request. Please post your code and SQL request.
0
 

Author Comment

by:93MH1351
Comment Utility
Adjusted points from 30 to 50
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:93MH1351
Comment Utility
I'll post all SQL soon but so far I have discovered that an IN clause in the SQL statement has a string 12122 characters in length?????
I am debugging somebody elses work with no comments in the code so please bear with me. You are my only hope!
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
Since your problem occurs once you have 1000+ records, I believe the file could be being processed to produce SQL based on all the records in the file.  

Once the file hits a certain size, the SQL is too large to run.
0
 
LVL 3

Accepted Solution

by:
P_S_Price earned 50 total points
Comment Utility
I am inclined to disagree with deighton, I believe it is your SQL ststement the error that you have posted clearly identifies which resource is breached and an In clause of 12122 characters could quite likely come within that remit!!
0
 
LVL 18

Expert Comment

by:deighton
Comment Utility
P_S_Price

I suspect the SQL statement is being generated in some sort of dynamic way; based on the file he has.  Hence the error being hit at 1000 records.  I agree that the SQL statement ultimatly in use is at fault.

Obviously further details are required.
0
 
LVL 2

Expert Comment

by:xinger
Comment Utility
Appendix A ("SQL Limits") of the SQL Reference manual lists only one item that is limited to 1024:

"Most host variables in an SQL statement:  approximately 1024 (The limit is based on the number of host variables that can fit within the longest SQL statement of 32767 bytes."

At first it seems odd that your error is based upon the number of rows of the query.  Perhaps the program is doing "Multiple-Row FETCH Using a Host Structure Array" (http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AQ802/4.3.1).  Look for a statement in the program like "EXEC SQL FETCH D11 FOR nnnn ROWS INTO :DEPT".  You may have to reduce the "nnnn ROWS" to a lesser number and do this FETCH statement multiple times in order to process all of your data.
0
 

Author Comment

by:93MH1351
Comment Utility
The answer is not the solution but it is very close.
The problem was an 'IN' clause in the SQL statement was too large.
0
 

Author Comment

by:93MH1351
Comment Utility
Comment accepted as answer
0
 

Author Comment

by:93MH1351
Comment Utility
I changed the IN clause to read a table instead of passing a long string of 12122 chars. It works fine.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
A safe way to clean winsxs folder from your windows server 2008 R2 editions
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now