Batch Query Resource Limit Exceeded and Stack Overflow Errors

 

Problem Statement:

When running a query in batch receive one of the two errors:
ERROR: 57011 Resource Limit Exceeded When input values are exceeded
ERROR: java.lang.StackOverflowError

The number of literals in the conditions is greater than the amount that Query can handle.  The limit will vary,  IBM limit for the number of literals allowed is 1024. 

Solution:

The number of literals in the conditions is greater than the amount that Query can handle.

Workaround: 
This was to have been an enhancement to raise the limit from 1024 literals to 2048.  Unfortunately IBM had a problem with this.  As a workaround they suggested modifying the QAQQINI file to not send Parameter Markers.  Each literal is considered a Parameter.  IBM also sends a Parameter Marker when processing SQL requests.  The Parameter Marker is not needed.

Steps to change the Parameter Marker.

1. On the iSeries that is getting the StackOverflowError run the command below, and make note of what library QAQQINI is in.

WRKOBJ *ALL/QAQQINI *ALL

2. Change the job CCSID to 37, by running command below.

CHGJOB CCSID(37)

3. Start Interactive SQL by running command: STRSQL
    
4. Run UPDATE command to change the Parameter Marker. NOTE: Change QSYS, to the library name noted above in step 1.

UPDATE QSYS/QAQQINI SET QQVAL='*NO' WHERE QQPARM='PARAMETER_MARKER_CONVERSION'

5. Recycle the ShowCase Server after the above steps have been completed.

Ref#: 1475519