SQL 9010 Error after abend on submitted file copy due to incorrect trigger

30 pts.
Tags:
iSeries v7r1
RSTOBJ
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    How do you know that the file is locked? -- Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    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
    9,700 pointsBadges:
    report
  • Rowena
    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!       
    30 pointsBadges:
    report
  • Rowena
    i even goofed up my posting.  sorry.  will try to avoid special characters.
    30 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following