Using a Stored Procedure to Update data on AS400 DB2 database from VB.NET application

[NorrisPC]

Hello -

I am writing VB.NET applications. We have a SQL Server 2005 environment and also many applications running on the AS400 using DB2 databases. I like to use Stored Procedures any time I do SELECT, UPDATE, DELETE, INSERT into databases.

We have the IBM DB2 for i5/OS .NET Provider V6R1. We use Visual Studio 2005.

I have written a number of stored procedures on the AS400 DB2 which SELECT data. I am having no trouble successfully running these stored procedures (containing SELECT statements) from a VB.NET application.

I ran into difficulty when I tried to call a stored procedure that contains UPDATE statements. I can run the same update statements from the System i Navigator just fine, but when I try to run the stored procedure from VB.NET I receive the following error -

AS400 DB2 Exception SQL7008 DELORDP in SLDATATEST not valid for operation.

DELORDP is my tablename and SLDATATEST is my Library name.

I found a question that someone else posted which asked about journaling and UPDATEs from stored procedures. There was no response to this gentleman's question. I decided to ask our AS400 gurus if they could turn journaling on for the table in question, then I ran my VB.NET application again and this time I did not get an error and when I queried the data, sure enough, it had been updated through my stored procedure.

My question is this - apparently, turning on the journaling worked for me, BUT, is there any OTHER solution besides turning on journaling for tables that we need to be able to UPDATE/DELETE/INSERT through stored procedures?

Thanks in advance for any advice or solutions offered.

Best regards,
Patricia