DB2 "Database log is full" problem solved

It's a little easier to change the control center directly, right--and configure--and log file size, the number of primary log files, and the number of secondary log files in the database name.

Command line Db2cmd is also available

DB2 Update DB CFG for Mymakro using LOGFILSIZ 512--Log file size

DB2 Update DB CFG for Mymakro using LOGPRIMARY 20--Primary log

DB2 Update DB CFG for Mymakro using LOGSECOND5 10--Secondary log

will not take effect until all connections to this database have been disconnected.

[@[email protected]]

Error that the transaction log for the DB2 database is full when the batch process is executed, the workaround

Number of secondary log files (LOGSECOND) = 25

Changed path to log file (Newlogpath) =

Log file Path = D:db2node0000sql00

003SQLOGDIR

Overflow log path (overflowlogpath) =

Mirror log path (mirrorlogpath) =

First activity log file = S0000005. LOG

Full block log on disk (blk_log_dsk_ful) = NO

Percentage of maximum active log space used by transactions (max_log) = 0

Number of activity log files for 1 active UOW (num_log_span) = 0

Group implementation count (MINCOMMIT) = 1

Percentage of log files recovered before soft checkpoint (SOFTMAX) = 100

Enabled log retention for recovery (logretain) = RECOVERY

User Exit for logging enabled (USEREXIT) = OFF

HADR Database role = Standard

HADR Local Host name (hadr_local_host) =

HADR Local Service name (HADR_LOCAL_SVC) =

HADR Remote Host name (hadr_remote_host) =

HADR Remote Service name (HADR_REMOTE_SVC) =

HADR instance name for remote server (hadr_remote_inst) =

HADR Timeout value (hadr_timeout) = 120

HADR Log Write synchronization mode (hadr_syncmode) = Nearsync

First log archiving method (LOGARCHMETH1) = Logretain

Logarchmeth1 option (LOGARCHOPT1) =

Second log archiving method (LOGARCHMETH2) = OFF

Logarchmeth2 option (LOGARCHOPT2) =

Failover Log Archive path (failarchpath) =

Retry Log archive number of errors (numarchretry) = 5

Log Archive retry delay (seconds) (archretrydelay) = 20

Vendor option (vendoropt) =

Automatic restart enabled (AutoRestart) = On

Index re-creation time and redo index build (indexrec) = SYSTEM (RESTART)

Record page during index build (logindexbuild) = OFF

Default number of Loadrec sessions (dft_loadrec_ses) = 1

Number of database backups to keep (num_db_backups) = 12

Restore history Retention Time (days) (REC_HIS_RETENTN) = 366

TSM Management Class (Tsm_mgmtclass) =

TSM node name (tsm_nodename) =

TSM owner (Tsm_owner) =

TSM password (tsm_password) =

Automatic maintenance (auto_maint) = OFF

Automatic database backup (auto_db_backup) = OFF

Automatic table maintenance (auto_tbl_maint) = OFF

Auto Runstats (auto_runstats) = OFF

Automatic statistics profiling (auto_stats_prof) = OFF

Automatic profile update (AUTO_PROF_UPD) = OFF

Automatic reassembly (auto_reorg) = OFF

DB2 = Quit

Db20000i QUIT Command completed successfully.

C:>DB2 Connect to Testdatabase

Database connection Information

Database Server = Db2/nt 8.2.4

SQL Authorization id = administ ...

Local Database alias = Testdatabase

Connect to Testdatabase

Database connection Information

Database Server = Db2/nt 8.2.4

SQL Authorization id = administ ...

Local Database alias = Testdatabase

Update db CFG for testdatabase using LOGFILSIZ 6000

db20000i UPDATE DATABASE CONFIGURATION command completed successfully.

sql1363w one or more parameters that were submitted for immediate modification were not changed dynamically. For these configuration parameters, the changes will not take effect until all applications have been disconnected from the database.

Update db CFG for testdatabase using logprimary 4

db20000i UPDATE DATABASE CONFIGURATION command completed successfully.

sql1363w one or more parameters that were submitted for immediate modification were not changed dynamically. For these configuration parameters, the changes will not take effect until all applications have been disconnected from the database.

Update db CFG for testdatabase using Logsecond 25

db20000i UPDATE DATABASE CONFIGURATION command completed successfully.

C:>db2? sql964 (see error explanation based on error code)

The transaction log for the SQL0964C database is full.

Explain:

All the space in the transaction log has been used.

If you use circular logs with secondary log files, you try to assign and use these logs. When the file

The secondary log cannot be used when there is no more space on the system.

If you use archived logs, the file system does not provide space to contain the new log files.

The statement cannot be processed.

User response:

When you receive this message (SQLCODE), you perform a COMMIT or

ROLLBACK, or retry the operation.

If the concurrent application is updating the database, retry the operation. When another application finishes the thing

May free up log space.

Make more frequent implementation of the operation. If the transaction is not yet implemented, the log space may be freed when the transaction is implemented. When you design your application, you should consider when to implement the updated transaction to prevent the log from being full.

If deadlocks occur, they are checked more frequently. This can be achieved by reducing the database configuration parameter dlchktime. This detects a deadlock and resolves it quickly (via rollback), which frees up the log space. If this happens frequently, increase the database configuration parameters to allow larger log files. Larger log files require more space, but reduce the need for the application to retry the operation. If the sample database is being installed, delete it and install the sample database again.

DB2 "Database log is full" problem solved