SQL 9010 Error after abend on submitted file copy due to incorrect trigger
I did RSTOBJ from a production library to a test library using and created a trigger that I did not realize existed. The next copy I tried to do to the test file that had this incorrect trigger over it caused the copy job to lock up with many "Access Plan not Updated" errors since the trigger was firing for every record copied. Now the file is locked so that I cannot delete the trigger but when I try WRKOBJLCK, DSPRCDLCK listed in the help no jobs are listed. What can I do to get rid of the file lock so I can delete the trigger and refresh the test file once again?
Software/Hardware used:
iSeries V7R1
Software/Hardware used:
iSeries V7R1
ASKED:
November 5, 2012 10:17 PM
UPDATED:
November 6, 2012 12:44 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
Michael Tidmarsh 
11,405 pts. ,
Rowena 30 pts.
11,405 pts. ,
Rowena 30 pts. To see all answers submitted to the Answer Wiki: View Answer History.
How do you know that the file is locked? — Tom
if you look for object lock with WRKOBJLCK and see nothing, check for member locks with an F6. If you find a job write down the info and then end job either by WRKACTJOB. If that does not work then you may need to try ENDJOBABN to end job abnormally. If you cannot find the job listed it may be a phantom job. We’ve had a few of these jobs ourselves. You can usually end these using Operations Navigator and going to the active jobs there. Hope it helps
Ops Nav says the file is locked when I try to delete the trigger, but I was able to rename the file that has the incorrect trigger, so the error is maybe just in SQL? I then restored from backup tape using RSTOBJ *FILE
Normal
0
false
false
false
EN-US
JA
X-NONE
MicrosoftInternetExplorer4
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:”";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Times New Roman”,”serif”;}
MBROPT and ALWOBJDIF both (*all) and once that was done did a copy of file data from LIVE to TEST and we seem to be in good shape. The bad trigger is still sitting there(disabled of course), but it looks like I will have to IPL to get it freed up to delete. As far as jobs go i think the trigger would have tried to spawn an SQL stored procedure call for each record that was being copied to the file. I don’t see any jobs at all when I try WRKOBJLCK + F6. In Ops Nav-Active Jobs I only see four with my username and they match what i have open. There are six jobs from 11/4, when i submitted the job i abended, but they are Qzdasoinit jobs. I will look at the joblogs on those. Thank you for your help!
i even goofed up my posting. sorry. will try to avoid special characters.
Ops Nav says the file is locked when I try to delete the trigger…
How? That is, what is the actual message? What are you clicking in OpsNav? Are you drilling into Database down to Triggers, then right-clicking the trigger?
It’s possible that it’s a SQL lock, but the path to the lock is important. It’s possible that running ALCOBJ with CONFLICT(*RQSRLS) specified will be enough. Or ENDHOSTSVR *DATABASE ENDACTCNN(*DATABASE) might do it. Or maybe some other action is needed.
The sequence that resulted in the condition is not clear.
The next copy I tried to do to the test file…
What is a “copy”? Was the RSTOBJ the first “copy”? Did you run RSTOBJ again for a second “copy”? Or did the file description get restored, and then you ran CPYF to “copy” data into the restored file? Or was data copied across your network after the RSTOBJ?
Once the actual sequence is known, it might be possible to work through the lock. It’s likely, though, that additional questions will be asked about the specific file attributes and about the trigger itself.
Tom