I've searched for days trying to find a solution and couldn't find any so I've decided to reach out to the community. I'm a Windows Programmer and I'm not familiar with the DBA programming and commands within the AS400.
I created several views that are just simply select statements of production tables. They were created with IBM SQL navigator which I like to use for most things because I'm not familiar enough with the AS400 to do it from the database green screen directly. I received a complaint by a director that I was causing some problems for him with the views I've created. He states my views are locking the tables and the AS400 will not allow him to re-org the file while it was in use.
He said if he were a DBA he wouldnt allow me to do what I did. I'm at a loss in that I've created thousands of views over the years but with SQL Server not AS400. Not allow me to create a SQL view seems to me like someone whose not very experienced at handling this issues within the database.
I'm asking for some basic help to preventing the locking of tables for re-orgs etc. by creating views in sql navigator. I created the view using iSeries SQL Navigator and simply adjusted the permissions of the view after I created it. Did I do something wrong and is there something I need to do to prevent locking?
UPDATE: There is a ASP.NET application that queries the views to throw into an Excel reports and emails them out. That application closes the connection when the job is completed.
Creating a view doesn't create a lock.
However, reading a table directly or through a view does just like it would in MS SQL Server.
You don't mention how you're reading the views, from a program or from some a SQL tool such as SQuirreL or IBM's Run SQL Scripts. If from a program, you need to make sure you're properly closing the result sets / connections I've seen lots of bad code that doesn't close result sets/connections properly; a real problem if connection pooling is also being used.
Assuming result sets and connections are being closed properly, the other issue is what's called pseudo-closed cursors. For performance reason, by default once the system has done a full open 3 times for a given cursor (open data path (ODP)), the system will stop fully closing it when asked to.
This causes problems for operation that need exclusive access to the table.
Actually, it only causes problems when the operations aren't willing to wait for the system to automatically fully close the ODP. Depending on the operation, the willingness to wait is controlled by the Maximum file wait time (WAITFILE) parameter on the table and/or the Default wait time (DFTWAIT) time of the job.
You can also manually request that the system fully close any pseudo-closed ODP on a table by using the Allocate Object command like so:
ALCOBJ OBJ((MYLIB/MYTABLE *FILE *EXCL)) CONFLICT(*RQSRLS)
For more info on pseudo-close cursors, see this IBM doc Pseudo Closed Cursor FAQ
Couple of options
Finally, consider stopping the use of commands from the 1980's when everybody went home at 5pm.
Instead of needing to use RGZPFM, change the table to reuse deleted records.
CHGPF FILE(MYLIB/MYTABLE) REUSEDLT(*YES)
Instead of CLRPFM, use and SQL DELETE statement.