iSeries SQL0904 Resource limit exceeded

5 pts.
Tags:
AS/400
iSeries
SSIS
I have written RPG/ILE stored procedures to validate and write data to various physical files with data extracted from MSSQL tables using SSIS and MS Visual Studio 2013 and utilizing the Native OLE DBIBM DB2 for i IBMDA400 OLE DB Provider. My problem the extract job fails when trying to import large amounts of data (in excess of 18,000 rows). A secondary problem arising from the "Resource Limit Exceeded" error is a failure of the iSeries to rollback the file changes even though the iSeries programs run under commitment control and the SSIS package encapsulates the program calls with a commitment boundary. The SQL data rolls back but the iSeries data does not. Separate testing of the stored procedures using iSeries Ops Navigator under commitment control do successfully rollback when failure scenarios are simulated. The iSeries stored procedures return a result set to the SSIS package at the completion of each row of data received with a success/fail flag to signify completion of the validation process and write to the physical files. It seems the resource error is something to do with an accumulation of open result sets but there does not appear a facility with RPG/ILE to close the result set. Each stored procedure ends with the statement:  EXEC SQL Set result sets array :ResultSetHDR FOR :#Rows ROWS; and the stored procedures are created similar to:
CREATE PROCEDURE (library)/(name) (  
  IN stocktakecode          CHAR (12)   ,  
  IN locationcode           CHAR (12)   ,  
  IN stocktaketypeind       CHAR ( 1)   ,  
  IN companycode            CHAR (12)   ,  
  IN oldLocationReference   CHAR (12)   ,  
  IN freezedate             CHAR (19)   ,  
  IN registernumber         CHAR ( 2)   ,  
  IN usercode               CHAR (32)   )  
RESULT SETS 1                              
LANGUAGE RPGLE NOT DETERMINISTIC           
CONTAINS SQL                               
EXTERNAL NAME (library)/(module_name(procedure_name)) 
PARAMETER STYLE SQL


Software/Hardware used:
iSeries V6R1. MSSQL Server 2014.

Answer Wiki

Thanks. We'll let you know when a new response is added.

its better to find on google .

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: