DB2 Version 9.7 for Linux, UNIX, and Windows

Tuning transaction log characteristics

About this task

Before you enable a database for spatial operations, ensure that you have enough transaction log capacity. The default values for the transaction log configuration parameters do not provide sufficient transaction log capacity if your plans include:

Enabling a database for spatial operations in a Windows environment
Using the ST_import_shape stored procedure to import from shape files
Using geocoding with a large commit scope
Running concurrent transactions

If your plans include any of these uses now or in the future, you need to increase the capacity of your transaction log for the database by increasing one or more of the transaction log configuration parameters. Otherwise, you can use the default characteristics.

Recommendation: Refer to the following table for the recommended minimum values for the three transaction log configuration parameters.

Table 1. Recommended minimum values for transaction configuration parameters
Parameter	Description	Default value	Recommended minimum value
LOGFILSIZ	Specifies the log file size as a number of 4-KB blocks	1000	1000
LOGPRIMARY	Specifies how many primary log files are to be preallocated to the recovery log files	3	10
LOGSECOND	Specifies the number of secondary log files	2	2

If the capacity of your transaction log is inadequate, the following error message is issued when you try to enable a database for spatial operations:

GSE0010N Not enough log space is available to DB2.

To increase the value of one or more configuration parameters:

Procedure

Issue the command GET DATABASE CONFIGURATION to find the current value for the LOGFILSIZ, LOGPRIMARY, and LOGSECOND parameters or view the Configure Database window of the DB2® Control Center.
Decide whether to change one, two, or three of the values as indicated in the table above.

Change each value that you want to modify. You can change the values by issuing one or more of the following commands, where db_name identifies your database:

UPDATE DATABASE CONFIGURATION FOR db_name USING LOGFILSIZ 1000

UPDATE DATABASE CONFIGURATION FOR db_name USING LOGPRIMARY 10

UPDATE DATABASE CONFIGURATION FOR db_name USING LOGSECOND 2

Example

What to do next

If the only parameter that you change is LOGSECOND, the change takes effect immediately.

If you change the LOGFILSIZ or LOGPRIMARY parameter, or both:

Disconnect all applications from the database.
If the database was explicitly activated, deactivate the database.

The changes to the LOGFILSIZ or LOGPRIMARY parameters, or both, take effect the next time either the database is activated or a connection to the database is established.