Home / server / Questions / 591048
Accepted
user1340582
Asked: 2014-04-25 03:44:19 +0800 CST

IBM DB2 9.7, cannot increase LOGFILSIZ or LOGPRIMARY values

  • 772

I am using IBM DB2 9.7 LUW database. My current database configuration values for Circular logging is as follows:

LOGFILSIZ: 1024
LOGPRIMARY: 13
LOGSECONDARY: 15

I get error that transaction logs are full, therefore I need to increase the log size. I tried with the following:

LOGFILSIZ: 4000
LOGPRIMARY: 10
LOGSECONDARY: 20

I also restarted database. The LOGSECONDARY was changed to 20, but the LOGFILSIZ remained at 1024 and LOGPRIMARY remained at 13.

Am I changing the correct values for my problem, and why don't the values change even though I restarted the database?

log-files

2 Answers

  1. Best Answer

    You are changing the correct parameters, but it sounds as though you did not actually restart the database, which would explain why the LOGSECOND was effective immediately, but LOGPRIMARY and LOGFILSIZ have not yet been changed. You can see what the current and deferred (pending) values for these configuration parameters are with the command GET DATABASE CONFIGURATION FOR <yourdb> SHOW DETAIL. I suspect you'll see that the deferred values for LOGPRIMARY and LOGFILSIZ are 10 and 4000, respectively.

    The easiest way to ensure that you fully recycle a DB2 database is to stop and restart the entire instance (using db2stop force and db2start commands). After that you may, optionally, activate the database using the ACTIVATE DATABASE statement.

    You do not have to shut down the entire DB2 instance, though. To properly restart a DB2 database, you have to deactivate it (i.e. cause the database to deallocate all memory assigned to it). You would do this by killing all connections to the database (using the FORCE APPLICATION statement) and then (if necessary) issuing the DEACTIVATE DATABASE statement. You can verify that the database is completely stopped by issuing the LIST ACTIVE DATABASES command – if your database is not in the list that's returned, it's stopped.

    Any new connection to the database after this (or ACTIVATE DATABASE) will cause DB2 to start the database in question, at which point it will allocate the log files with the new parameters.

    • 1

  2. You must make sure the changes are effective in both disk and memory. You may check this using the db2pd command (released with version 8.2) or using GET DATABASE CONFIGURATION command as mentioned by Ian above.

    The major difference is that db2pd command does not require a connection to database, where as GET DATABASE CONFIGURATION FOR dbname SHOW DETAIL does require a connection(note the SHOW DETAIL clause, this requires a connection - not the standard GET DATABASE CONFIGURATION FOR dbname command)

    db2pd -d dbname dbcfg | egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND'

    This will list the below 3 values-

    Description                             Memory Value                Disk Value
Log file size (4KB)                         (LOGFILSIZ) = 1024                       4000
Number of primary log files                (LOGPRIMARY) = 13                         10
Number of secondary log files               (LOGSECOND) = 20                         20

    You may note that the LOGSECOND changes are effective at both the disk and memory immediately, whereas the other 2 require a DB restart for the change to be effective in memory - and hence for the DB to actually have it in use.

    And for restart, you may follow standard order - deactivate DB, shutdown instance, issue a clean up of resources, start the instance and then activate DB and verify the above values are same on both disk and memory.

    • db2 deactivate db dbname
    • db2stop
    • ipclean -a
    • db2start
    • db2 activate db dbname
    • 1