i've searched days trying find solution , couldn't find i've decided reach out community. i'm windows programmer , i'm not familiar dba programming , commands within as400.
i created several views select statements of production tables. created ibm sql navigator use things because i'm not familiar enough as400 database green screen directly. received complaint director causing problems him views i've created. states views locking tables , as400 not allow him re-org file while in use.
he said if dba wouldnt allow me did. i'm @ loss in i've created thousands of views on years sql server not as400. not allow me create sql view seems me not experienced @ handling issues within database.
i'm asking basic preventing locking of tables re-orgs etc. creating views in sql navigator. created view using iseries sql navigator , adjusted permissions of view after created it. did wrong , there need prevent locking?
update: there asp.net application queries views throw excel reports , emails them out. application closes connection when job completed.
creating view doesn't create lock.
however, reading table directly or through view in ms sql server.
you don't mention how you're reading views, program or sql tool such squirrel or ibm's run sql scripts. if program, need make sure you're closing result sets / connections i've seen lots of bad code doesn't close result sets/connections properly; real problem if connection pooling being used.
assuming result sets , connections being closed properly, other issue what's called pseudo-closed cursors. performance reason, default once system has done full open 3 times given cursor (open data path (odp)), system stop closing when asked to.
this causes problems operation need exclusive access table.
actually, causes problems when operations aren't willing wait system automatically close odp. depending on operation, willingness wait controlled maximum file wait time (waitfile) parameter on table and/or default wait time (dftwait) time of job.
you can manually request system close pseudo-closed odp on table using allocate object command so:
alcobj obj((mylib/mytable *file *excl)) conflict(*rqsrls) for more info on pseudo-close cursors, see ibm doc pseudo closed cursor faq
couple of options
- make sure operations willing wait..
- chgpf file(mylibib/mytable) waitfile(1)
- chgjob dftwait(30)
- manually force pseudo-closed cursors closed using alcobj above
finally, consider stopping use of commands 1980's when went home @ 5pm.
instead of needing use rgzpfm, change table reuse deleted records.
chgpf file(mylib/mytable) reusedlt(*yes) instead of clrpfm, use , sql delete statement.
Comments
Post a Comment