SESAM/SQL logs all changes which have been made in the database since a specific SESAM backup copy was created in logging files (see section “Files and tables used for media recovery”).
If the database is located on a DB user ID and the logging files are also to be stored on this user ID, you must make the necessary preparations, see section “Database files and job variables on foreign user IDs”.
It is important to differentiate between the following:
SESAM/SQL logs changes that affect the database's catalog space (i.e. changes arising as a result of utility statements, or of SQL statements used to administer the storage structure, to administer user entries, or to define and administer schemas) in CAT-LOG files (CAT logging).
SESAM/SQL logs changes to user spaces (i.e. changes arising as a result of SQL statements that change data, or CALL DML statements) in DA-LOG files (DA logging).
Enabling logging for a database
When a database is created with the utility statement CREATE CATALOG , the database administrator specifies whether changes to the database are to be logged. Logging is then carried out for the whole of the database, in other words in the catalog space and the associated user spaces. Also, logging can be enabled by activating LOG in COPY CATALOG.
In order to avoid loss of data through system or disk errors, the database should generally be used with logging enabled. Database operation without logging is only to be recommended in the case of test databases or databases for temporary data, provided data loss can be compensated for in a different way, and in the case of databases used primarily or exclusively for retrieval purposes.
Enabling logging for user spaces
If logging is enabled for the database, it is also carried out by default for each user space created with CREATE SPACE. However, the database administrator can opt to disable logging when creating a user space. In addition, he/she can use ALTER SPACE to disable logging for a space originally created with logging enabled.If logging is not enabled for a database, logging cannot be carried out for the database's user spaces.
It can be useful to define a user space without enabling logging if the user space contains nothing but indexes or tables of temporary data.
If an error occurs, the database administrator can recover defective indexes with RECOVER INDEX (see section “Recovering indexes”).
In the case of COPY CATALOG, pure index spaces which are not present in the logical data backup can be excluded from the COPY backup. When a subsequent RECOVER is performed, it is necessary to specify the clause GENERATE INDEX ON NO LOG INDEX SPACE. No backups are then read in for the index spaces. Instead they are reset and recreated.
When deciding whether to operate a user space in which just indexes are located with or without logging, the database administrator should remember that operation without logging is accompanied by a performance gain. However, the regeneration of indexes that are defective or contain errors with RECOVER INDEX takes longer than the recovery of indexes in the context of a general repair process. Administrators should therefore choose the option that best suits the real-life application in question.
Disabling or interrupting logging for user spaces
SESAM/SQL logs each interruption to logging for a user space in the catalog table RECOVERY_UNITS by adding an appropriate entry with a time stamp.
The disabling or suspending of logging by the database administrator
The database administrator can temporarily disable logging for a user space with the aid of the SQL statement ALTER SPACE. This can be useful if a large update run needs to be accelerated.
The database administrator must ensure that the status of the user space as it was when logging was disabled, i.e. immediately prior to the update run disabling logging, can be recovered if an error occurs. Repair can then be carried out simply by repeating the update run. The database administrator can achieve this as follows:
If logging has been suspended for the first time since the creation of the most recent SESAM backup copy of the user space and an error occurs, the database administrator can restore the status of the user space that was current at the time logging was suspended by issuing the utility statement RECOVER TO with the time stamp for the suspension of logging.
If it is not the first time that logging has been suspended since the creation of the most recent SESAM backup copy of the user space, the database administrator must create a SESAM backup copy of the user space with the utility statement COPY immediately before suspending the logging. This SESAM backup copy can be reset with the utility statement RECOVER TO if an error occurs.
After the update run, the database administrator should create another SESAM backup copy of the user space using the utility statement COPY, and re-enable logging for the user space with COPY at the same time. This SESAM backup copy then serves as a starting point for subsequent recovery measures in this user space.
You can also create a foreign copy instead of a SESAM backup copy and logging can be activated with PREPARE-FOREIGN-COPY, see “Database Operation” manual.
Suspension of logging by SESAM/SQL in its own utility statements
SESAM/SQL automatically suspends the logging of the relevant user spaces when executing the utility statements LOAD OFFLINE , IMPORT TABLE, RECOVER INDEX, RECOVER ADJUST, REORG ... NEW ROW_IDS and MIGRATE if a MIGRATE CALL DML ONLY TABLE is not involved. The database administrator should therefore create SESAM backup copies of the relevant user spaces immediately before executing these utility statements. If an error occurs during a LOAD OFFLINE, IMPORT TABLE, MIGRATE, RECOVER INDEX, RECOVER ADJUST or REORG ... NEW ROW_IDS, it will be possible to reset the status that was valid immediately before the utility statement executed, and the statement can be repeated. In the event of a RECOVER following an error in IMPORT TABLE execution, the table may sometimes still be created. However, in this case it must be deleted with DROP TABLE before IMPORT TABLE can be repeated. If a MIGRATE fails to execute correctly, the database administrator must use the SQL statement DROP TABLE to delete manually any table already created by the failed MIGRATE before attempting a second MIGRATE.
The database administrator must again create SESAM backup copies of the relevant user spaces immediately after a LOAD OFFLINE, IMPORT TABLE, MIGRATE, RECOVER INDEX, RECOVER ADJUST or REORG ... NEW ROW_IDS; these backup copies serve as a starting point for continued logging. If no such copies are created, these user spaces will have the state “copy pending”. They will be locked to update statements until the database administrator creates the relevant SESAM backup copies.
Specifying storage devices for logging
The recovery of the catalog space or user spaces after a disk error is only possible if the SESAM backup copies, the CAT-REC file, and the CAT-LOG and DA-LOG files are located on storage devices other than the ones on which the catalog space and the user spaces are located.
When creating the catalog space with the utility statement CREATE CATALOG, the database administrator also defines the storage group and, thus, the storage medium on which SESAM/SQL creates the CAT-REC file and the CAT-LOG file (see section “Creating the database's catalog space”). Once the catalog space is created, the database administrator can define additional storage groups with the SQL statement CREATE STOGROUP.
The database administrator allocates the storage medium used for the DA-LOG files, and any other storage medium possibly required for the CAT-LOG files, via the media table. CAT-LOG files and DA-LOG files can only be created on disks; disks are identified by means of the relevant storage group.