HP 3000 Manuals HP 3000 Manuals
Judging Maintenance Expenses Many database maintenance statements (CREATE, DROP, ALTER, and so on) lock system catalog resources. To maximize concurrency and improve performance, restructuring and creation should take place in single-user mode during off hours when the DBEnvironment is not usually being accessed. Transactions that update the system catalog should be kept as short as possible. Commands that create, grant, add, drop, revoke, or update statistics write to the system catalog, which is locked exclusive at the page level for updates. For example, to improve concurrency, you can divide a transaction that creates and loads a table into two transactions. The first transaction creates the table and then commits work, thus freeing system catalog pages. The second transaction loads the table and then commits work. A chart that shows which SQL statements hold which locks on various system tables is in the appendix, "Locks Held on the System Catalog by SQL Statements." Keep in mind that lock granularity can be set to table, page, or row level. See the "Changing System Table Lock Types" section in this chapter for more details. If you think you are having locking problems with the system catalog, refer to that appendix as you review your transactions.