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
EXTERNAL NAME (library)/(module_name(procedure_name))
PARAMETER STYLE SQL
Software/Hardware used: iSeries V6R1. MSSQL Server 2014.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.
Your password has been sent to:email@example.com
To follow this tag...
Thanks! We'll email you when relevant content is added and updated.
Share this item with your network: