Default DB2 DB and DBM variables are set pretty well and logical. The only manual adjusting can occur when you’re trying to overload transactions a bit. For example by copying tables.
Task = Copying table (10 milions of records) within same schema (in our case ADDRESS -> ADDRESSX).
We can use some GUI to fasten our operation or just do a copy of structure + data:
CREATE TABLE DB2INST1.ADDRESSX LIKE DB2INST.ADDRESS;
INSERT INTO DB2INST1.ADDRESSX SELECT * FROM DB2INST1.ADDRESS;
In optimal case, that’s all (and of course working in default settings for smaller tables).
Solution 1 called „Hooray, here we go“
—
After few seconds this approach is stopped by error message (RazorSQL environment):
Solution 1 final standing „Oh my, DB2 sucks, that´s terrible!“
Solution 2 called „I can read and think a little bit“
—
[db2inst1@rhel65db2105 j]$ db2 ? 57011
SQLSTATE 57011: Virtual storage or database resource is not available.
Ok, still can be anything, let’s try most talkative command and his output 🙂
[db2inst1@rhel65db2105 j]$ db2 ? sql-964
SQL0964C The transaction log for the database is full.
Explanation:
All space in the transaction log is being used.
If a circular log with secondary log files is being used, an attempt has
been made to allocate and use them. When the file system has no more
space, secondary logs cannot be used.
If an archive log is used, then the file system has not provided space
to contain a new log file.
This message can also be returned when the database has used almost all
of the possible log sequence numbers. The database manager identifies
database log records using a unique identifier, called a log sequence
number (LSN). When transactions cause database logs to be written, the
database uses new LSN values.
The application cannot perform any transactions that cause the database
to write log records.
The statement cannot be processed.
User response:
Execute a COMMIT or ROLLBACK on receipt of this message (SQLCODE) or
retry the operation.
If the database is being updated by concurrent applications, retry the
operation. Log space may be freed up when another application finishes a
transaction.
Issue more frequent commit operations. If your transactions are not
committed, log space may be freed up when the transactions are
committed. When designing an application, consider when to commit the
update transactions to prevent a log full condition.
If deadlocks are occurring, check for them more frequently. This can be
done by decreasing the database configuration parameter DLCHKTIME. This
will cause deadlocks to be detected and resolved sooner (by ROLLBACK)
which will then free log space.
If the condition occurs often, increase the database configuration
parameter to allow a larger log file. A larger log file requires more
space but reduces the need for applications to retry the operation.
Transaction configuration parameters which may need to be adjusted are
LOGFILSIZ, LOGPRIMARY, LOGSECOND. For more information about the
transaction log search the DB2 Information Center using phrases such as
„transaction log“.
If installing the sample database, drop it and install the sample
database again.
If this message was returned because the database used nearly all of the
possible LSN values, you can reset the LSN values to zero by doing the
following:
1. unload all data from the database
2. drop and recreate the database
3. load all the data
sqlcode: -964
sqlstate: 57011
That‘ s something i really DO LIKE on IBM products – documentation and error codes 🙂 Just first sentence was enough to identify issue: „All space in the transaction log is being used.„.
Simply – copying table command serves well for suffocating transaction logs. If you don’t know what transaction log is, please look at very old but still active! article.
Where can I set higher values for „something“ related? Ok, you have to adjust few database configuration values (it’s strictly logical that instance (DBM) is above these database transaction problems). If you are not familiar with parameters name, you can list everything „LOG“ related:
[db2inst1@rhel65db2105 ~]$ db2 get db cfg for sakila | grep '(LOG'
Log buffer size (4KB) (LOGBUFSZ) = 2150
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 13
Number of secondary log files (LOGSECOND) = 12
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Log pages during index build (LOGINDEXBUILD) = OFF
Log DDL Statements (LOG_DDL_STMTS) = NO
Log Application Information (LOG_APPL_INFO) = NO
You can spy through documentation to find which parameters are related to this issue. I can promise that you can focus just for these highlighted „big three“ 🙂
If you have good hardware (including free disk space), don’t waste your time with big math and just raise values:
[db2inst1@rhel65db2105 ~]$ db2 update database configuration for sakila using LOGFILSIZ 50000;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@rhel65db2105 ~]$ db2 update database configuration for sakila using LOGPRIMARY 40;
DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@rhel65db2105 ~]$ db2stop force
10/08/2014 13:02:27 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
After db2start it’s counting with new values. I have performed tables copying and now I can check how many log file I have (remember 13 files as original value?).
[db2inst1@rhel65db2105 LOGSTREAM0000]$ pwd
/sak/db2inst1/NODE0000/SQL00001/LOGSTREAM0000
[db2inst1@rhel65db2105 LOGSTREAM0000]$ ll
total 102600
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000000.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000001.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000002.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000003.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000004.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000005.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000006.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000007.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000008.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000009.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000010.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000011.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000012.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000013.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000014.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000015.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000016.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000017.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000018.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000019.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000020.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000021.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000022.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:06 S0000023.LOG
-rw——- 1 db2inst1 db2iadm1 4202496 Oct 8 14:29 S0000024.LOG
Size around 4MB, 25 files.
Solution 2 final standing „Interesting!“
Best practice = study DB2 parameters precisely, it will save you your time + nerves 🙂
-a-