When a user or an application program has made a change or a group of related changes to the database, and if the application in question completed successfully, the user or program issues an SQL COMMIT WORK statement to the application server, to commit these changes to the database. If the application did not complete successfully, the user instead issues an SQL ROLLBACK WORK statement, which undoes all the changes made up to the point of the error since the last COMMIT WORK statement, or since the start of the program or session.
A group of SQL statements is called a logical unit of work (LUW). An LUW can be as small as one statement, or as large as an entire application execution (or ISQL session). All SQL statements are executed within an LUW. If no LUW exists when a statement is issued, then the database manager creates one implicitly.
Users working on a VM operating system can take advantage of CMS work units, which allow them to maintain more than one logical unit of work (LUW) at a time. With separate CMS work units, application programs can be independent of one another. For example, a user can run a program, and in the middle of an LUW, have that program call a second program which runs in a separate CMS work unit. When work is committed in the second program, it does not affect the active LUW in the first program.
| Note: | CMS work units require extra processing overhead, so should only be used when necessary. If an application does not need this support, set the WORKUNIT option of the SQLINIT command to NO. |
A checkpoint is an internal operation where the database manager writes modified data and status information to DASD, and writes a summary status record to the log.
When the database manager takes a checkpoint:
A checkpoint is scheduled when:
A checkpoint has two performance implications:
To control the duration between checkpoints, use the CHKINTVL initialization parameter. This parameter specifies how many log pages the database manager will fill before it takes its next checkpoint.
Setting the Time Between Checkpoints: The time between checkpoints depends on the number of modifications you make to the database. If logging is turned on, the database manager writes to the log every time you perform an insert, update, or delete. The more modifications you make, the faster you will reach a checkpoint. If you only perform queries, the database manager may never perform a checkpoint.
We recommend that you adjust the CHKINTVL parameter so that the database manager takes a checkpoint every 10 to 15 minutes. Should you experience a system failure, it should take you no longer than 10 to 15 minutes to restart the database manager once you have recovered your system. If you adjust CHKINTVL so that checkpoints occur less frequently, for example every four hours, it may take up to or more than four hours to restart your database.
Many installations find that the optimum CHKINTVL setting is between 50 and 300. Installations with large, randomly modified databases are in the lower end of that range, and installations with small databases tend to be in the upper end of that range. Large databases having a relatively low frequency of random modifications also tend to be in the upper end of that range.
If you set the CHKINTVL parameter too low, you minimize the risk of filling the log or storage pools. However, while each checkpoint is faster, you increase the overall number of checkpoints.
If you set it too high, you lower the overhead associated with checkpoint processing. However, consider the following adverse affects:
This consideration does not apply if you are doing archiving (LOGMODE=A or L), because in that situation log space is reclaimed only when the database or log is archived.
However, unless your standard workload includes a significant amount of random data modifications over a relatively large area (more than 100MB), you probably will not notice significant delays. The effect is unimportant if the database is small or if there is very little random data modification activity. Bulk sequential data modifications also do not generally cause problems.
Whenever the database manager modifies a page in a storage pool, it creates a new page in the same pool, and keeps the original as a shadow page. Therefore the longer the period between checkpoints, the more modified pages will accumulate in your storage pools. 25% free storage in each storage pool is generally sufficient, refer to Short on Storage Cushion.
Forcing Checkpoints: You can avoid checkpoint processing during peak periods by manually forcing them to occur when required. For example, consider a bank that processes a large number of transactions when its customers are on break for lunch, between 11:00AM and 1:00PM. A checkpoint could lock tellers out of the database for several minutes frustrating both the tellers and the bank's customers.
To avoid a checkpoint, the checkpoint interval is set very high. Just before the lunch rush, an empty dbspace, created for the purpose, is dropped to force a checkpoint. After the rush, the dbspace is recreated and dropped again to force another checkpoint and ensure that the lunch time work is saved to DASD.
If you plan to use this method to control when checkpoints occur, create a plan that specifically indicates when each is to occur and make sure that it is followed. Not performing any checkpoints can cause more performance problems in the long run than you will avoid in the short run.
DB2 VM Data Spaces Support: The DB2 Server DSS feature can make checkpoint processing faster by limiting the number of modified pages in main and expanded storage. When the number of modified pages in a data space exceeds an initialization parameter called the save interval (SAVEINTV), the database manager directs the operating system to save all the modified pages in that data space to DASD. Unlike the save that occurs during checkpoint, the database manager can continue to service users while this is being done. For more information on the save interval, refer to Chapter 6, Data Spaces Support for VM/ESA.
A log is a file maintained on DASD that records all the changes to the database. Each time a DML statement (for example, INSERT, DELETE, UPDATE) is processed by the database manager, the old and new values are written in the log. If any changes to the database must be undone or redone, you can use the log to restore the data to its proper state.
An archive is a copy of data in your database at the time the archive was made. You can archive an entire database, a portion of the database, or even the log. Typically, you use archives to recover from a DASD failure.
You can create three different archives:
Note: By using the DB2 for VSE & VM Data Restore Feature, you can also create incremental archives and restore individual storage pools.
A database must have at least one log. Optionally, you can define a second log to keep a duplicate copy of the database changes. Then, if a DASD failure occurs on one log, the other is still available.
| Note: | To ensure that you really have true dual log protection, each log file or minidisk must reside on a separate DASD volume. For information on this "dual log" option, see the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manuals. |
You can choose from four different log mode values:
| Note: | Before you can use LOGMODE=L, you must create a database archive. |
The database manager automatically initiates a log archive when it is nearly full. While you can wait for this to occur, it is more efficient to perform regular archives yourself, refer to Log Cushion and Automatically Initiated Archives.
When you choose a log mode, decide how much protection you want, and the amount of time you can spend in recovering data.
If you are running in single user mode (SUM) and you do not need to protect your data from either system or DASD failures, specify LOGMODE=N. The application server will run faster because it will not require the extra time to create archives or maintain a log.
If you do not need to protect your data from DASD failures, specify LOGMODE=Y. The application server will run faster because it will not require the extra time to create archives and you can maintain a smaller log.
LOGMODE=Y cannot protect you from DASD failures because the contents of the log are only saved until the next checkpoint. After the checkpoint, the current contents of the log can be overwritten by new changes. If several checkpoints have occurred since your last database archive, you cannot use the contents of the log to recover.
If you must have the ability to recover from DASD failures, choose either mode A or L. With LOGMODE=A an archive of the entire database is created periodically, so you can restore your entire database or individual storage pools by using the latest database archive along with the contents of the current log. With LOGMODE=L, archives are also taken but you can create archives of the database less frequently than with mode A, because you have log archives as well. If a DASD failure occurs, you can restore the entire database or individual storage pools by using the latest database archive, the sequence of log archives that follow it, and the contents of the current log. If you are doing a log archive for the first time you will be prompted to do a database archive first. You will not be prompted again. You must schedule any subsequent archives yourself.
To decide between LOGMODE=A or L, consider:
When you choose a log mode, use it whenever you start the system. Do not change the log mode without thought and planning. If you must do so, you may have to carry out additional procedures. For more information, see the DB2 Server for VM System Administration or the DB2 Server for VSE System Administration manuals.
The SLOGCUSH initialization parameter defines when automatically initiated log-full processing begins. It is expressed in terms of a percentage of the log. When the log fills to the SLOGCUSH value, the database manager aborts the oldest active logical units of work until enough log space is freed to bring the percentage of the log below the SLOGCUSH level.
The ARCHPCT initialization parameter defines when automatically initiated archives will occur. It is also expressed in terms of a percentage of the log. When the log fills to the ARCHPCT value, the database manager forces either a log or database archive depending on whether it is running in LOGMODE A or L.
Ideally, you should never reach SLOGCUSH or ARCHPCT. Log-full processing and automatically initiated archives reduce performance, and often occur during peak workloads, so avoid them by:
| Note: | Checkpoints only free space in the log when you run your application server with LOGMODE=Y. |
Since performing an archive impacts performance less than log-full processing avoid the latter by:
You can use the SHOW LOG operator command to determine if archiving has been enabled and what percentage of the log is full. Also, if archiving is enabled, it displays the percentage of the log remaining before ARCHPCT is reached. If archiving is disabled, it displays the percentage of the log remaining before SLOGCUSH is reached.