Store procedure keeping lock on PF

[kpmac]

Re: Store procedure keeping lock on PF

The lock is still on Customer_U_Name.

[Mercury]

Re: Store procedure keeping lock on PF

Since you've specified SET OPTION COMMIT =*NONE and INSERT WITH NC, can you remove or comment out the COMMIT and give it a new try pls ?

[jtemple]

Re: Store procedure keeping lock on PF

Just taking a quick glance, I see that the cursor you closed doesn't reference customer_u_name. However, you're issuing multiple selects against customer_u_name without a cursor. My suggestion is to assign a specific cursor to each open of customer_u_name and then close those cursors at the end of the procedure and see if the file is closed.

[kpmac]

Re: Store procedure keeping lock on PF

Bh? . . . . . . . . . .

[littlepd]

Re: Store procedure keeping lock on PF

This procedure is puer sql. There is absolutely no RPG or CL . . . involved with this procedure. However, I do have SET OPTION COMMIT = *NONE in the procedure header. Unfortunately, the lock remains.

So, does this mean that you are not running the RUNSQLSTM command over a source member containing the CREATE PROCEDURE statements, and generating a CLE-type object on your System i which you can then execute from other SQL commands?

[kpmac]

Re: Store procedure keeping lock on PF

I create the procedure using the runs SQL scripts tool inside of Ops Navigator. The prcedure gets called via a commercial ETL tool we have.

However, the object remains locked when I call the procudre from any SQL interface.

[littlepd]

Re: Store procedure keeping lock on PF

1-800-ibm-serv

[kpmac]

Re: Store procedure keeping lock on PF

Finally, its fixed!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I just spent 2 hours and 11 minutes on the phone with IBM tech support. They kept telling me that I would have to send n all my tables and allow them to do a recreate of my environment. I refused and walked through the procudure statement by statement. Apparently in V5r2 there is a problem where the system does not release on object lock on a file after an ALTER command is issued with a COMMIT Level of *NONE.

The Solution was to journal the file and set the commit level to something other than *NONE.

2 hours and 11 minutes on an alter.

The lesson learned is Stick to your guns and never let the support reps off the phone.

[jamief]

Re: Store procedure keeping lock on PF

congrats on solving the issue.........good luck with your new environment!!!

jamie

[Mercury]

Re: Store procedure keeping lock on PF

This solution is unfindable without testing.

[gcraill]

Re: Store procedure keeping lock on PF

Finally, its fixed!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I just spent 2 hours and 11 minutes on the phone with IBM tech support. .......
The lesson learned is Stick to your guns and never let the support reps off the phone.

Way to go !!!

I keep getting "Working as designed" from IBM, and a link to submit a new feature request from their website - kind of a DIY approach to customer service ...