Positional Delete / Update with Dynamic SQL Cursor

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

Hi All,

I was writing a COBOL code, that reads a SQL Select statement from a file. Then creates a dynamic cursor with ROWSET POSITIONING to faciliate multirow fetch. The cursor is then prepared and opened. NEXT ROWSET is fetched and displayed.
The code is working till here.
Next, I am adding a positional DELETE / UPDATE on the ROWSET to either delete the rows or update them at one go.
the problem starts here. Even though the COBOL Compile / DB2 Precomile works fine, the BIND fails with SQLCODE -504.
I am not sure how to solve this. When I am changing the Dynamic to a STATIC cursor in the program, the positional DELETE works fine.

Is it like, I can not use a positional DELETE with a Dynamic CURSOR?
When I do the same in REXX (without ROWSET POSITIONING, as DSNREXX still does not support multirow fetch), it works fine, where as REXX DB2 execution is Dynamic in nature.

here are the SQLCODE in my COBOL. The DB2 version used in our shop is V8

Dynamic Cursor:

gylbharat · Posted: Sat May 18, 2013 1:39 am

Cursor cursor-name was referenced in a positioned UPDATE or DELETE statement which is not a supported operation for an allocated cursor....

I think multi row delete is not possible.

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

@ gylbharat , Multirow delete is possiible. As I said, when I change the dynamic to a static SQL cursor. It works fine.
Again, as I said, RE XX, which uses dynamic SQLs, is doing the positional delete for single fetch. With the current option of DSNREXX, multirow fetch is not possible.

I think with the COBOL program, I am missing some bind parameter. Not sure though.

Does any one else have any thought?

Pandora-Box · Posted: Sun May 19, 2013 2:28 am

I Would certainly ask why you need program for delete?

Why not use just DSNTEP2?

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

Hi Pandora Box, There are mainly 2 reasons for not using DSNTEP2.

First, there are few intermediate business logics to be applied on the fetched records
Second, The number of record is huge. Deleting them at one go will exceed the DB2 logsize limit and will cause an S04E. That is why I will also have to apply intermediate commits while deleting through a program.

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

Guys, I was finally successful in implementing a solution. A very easy one :-)

In my prior code, even though the cursor was declared dynamic, the position delete was still a Static statement. I changed everything to dynamic, and it worked fine.

Anuj Dhawan · Posted: Tue May 21, 2013 3:16 pm

Thanks for telling what worked for you. As SQLCODE=-504 says that CURSOR NAME cursor-name IS NOT DECLARED, which didn't really trigger 'this slow-processor' for a better suggestion.

If possible can you please share the code which worked for you.

Mainak_Dalal · New User Joined: 05 May 2010 Posts: 19 Location: USA

Anuj, As I said in my last post, I changed all the SQL codes to dynamic. The Static DELETE statement was converted to a COBOL string and an EXECUTE IMMEDIATE was issued on that string. It resolved the issue.

Anuj Dhawan · Posted: Wed May 22, 2013 5:36 pm

Thanks Mainak.