Unless you specify otherwise, statistics are automatically collected and updated during execution of the RELOAD, and RELOAD DBSPACE commands. If you are performing a DATALOAD, statistics will also be automatically collected if you load data into a single empty table with no indexes. The database manager automatically issues an implicit UPDATE STATISTICS statement following the DATALOAD. This can be time-consuming, if the number of active data pages in that DBSPACE is large.
| Note: | This type of automatic collection only updates statistics for columns with indexes. For multicolumn indexes it only updates the leading column. If you want to ensure that all the columns have their statistics up to date, suppress automatic collections and enter UPDATE ALL STATISTICS immediately following a dataload. Refer to page ***. |
The automatic collection of statistics can be suppressed by specifying SET UPDATE STATISTICS OFF in the DBS input file before the DATALOAD. In cases where the database manager will not implicitly issue the UPDATE STATISTICS statement (but rather collects statistics during the load), there is no advantage in explicitly suppressing statistics collection. Otherwise, consider suppressing the UPDATE STATISTICS statement if either of the following conditions apply:
In the case of UNLOAD, a block size greater than 8244 bytes for tape output files is recommended for improved performance. Specify the block size in the CMS FILEDEF command associated with the OUTFILE statement.
When running the DBS utility in multiple user mode to load (INSERT) or unload (SELECT) rows from a database, you may encounter lock escalation. SQL LOCK DBSPACE or LOCK TABLE statements override the automatic locking mechanism; they can be used to avoid deadlock conditions.
A user-issued SQL LOCK statement is useful only during multiple user mode processing for table data in a public dbspace that is not defined with locking at the dbspace level. A user-acquired database lock remains in effect until the end of the logical unit of work in which it was issued.
If you insert many rows into the database with a RELOAD command or a DATALOAD command without the COMMITCOUNT option specified, consider using the SQL LOCK DBSPACE statement to eliminate or reduce lock escalation. An exclusive lock on the dbspace where the tables being loaded are defined does not appreciably increase lock contention and reduces the likelihood of deadlock with another user.
| Note: | An exclusive lock on a table being loaded does not prevent lock escalation and is not recommended. |
You can also avoid lock escalation during multiple user mode DATALOAD processing by issuing a SET AUTOCOMMIT ON command before the DATALOAD command and specifying a sufficiently low COMMITCOUNT value in the DATALOAD INFILE subcommand. Use of DATALOAD COMMITCOUNT processing reduces the likelihood of the locking required by DATALOAD processing delaying other users accessing the table being loaded or other tables in the same dbspace where the table being defined resides. If the target table is in a dbspace defined with ROW level locking, a COMMITCOUNT value of approximately 200 should be sufficiently low. If the dbspace is defined with PAGE locking, the COMMITCOUNT value can be higher (1000, for example) and lock escalation is still avoided. Do not arbitrarily set the COMMITCOUNT value too low because frequent commit points increase DATALOAD run time.
If you are running with an isolation level setting of repeatable read (the default processing mode) and you know that a particular SELECT, DATAUNLOAD, or UNLOAD operation is going to access many rows from one or more tables in the database, lock escalation then normally occurs. You should consider acquiring a SHARE lock on the table(s) being accessed. If all the tables being accessed reside in the same dbspace, you should consider acquiring a SHARE lock on the dbspace being accessed. This action can reduce lock contention and the likelihood that a SELECT, DATAUNLOAD, or UNLOAD causes a deadlock with another user. Other users can modify other tables in the same dbspace where the table being accessed resides.
To obtain the best performance when using the UNLOAD PACKAGE command and the RELOAD PACKAGE command, consider doing the following:
These actions improve performance by preventing interruptions by other users.
PROGRAM is a synonym for PACKAGE. Therefore, UNLOAD or RELOAD PROGRAM, and UNLOAD or RELOAD PACKAGE are equivalent commands.
When unloading or reloading a modifiable package, an exclusive lock is held on the catalog table SYSACCESS. This may cause a performance deterioration for other users wanting to run the exclusively locked package.