Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: Help with "DB2 log is full"

    Hi All,
    I have 3 primary logs and 20 secondary logs in my db cfg and I am using circular logging. I got a error msgs today (12/12) between 12 o'clock to 13 o'clock. But I don't understandard why giving the following facts:

    1. some logs were not reused today. They have old modified date:
    -bash-2.05b$ ls -l /db/db2data/logs/ah_01/ah3d_02/NODE0000
    total 921200
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:19 S0000000.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:13 S0000001.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:17 S0000002.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:19 S0000003.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:19 S0000004.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:19 S0000005.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 10 15:19 S0000006.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 01:46 S0000007.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 03:04 S0000008.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 04:32 S0000009.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 07:04 S0000010.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 10:18 S0000011.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 12:54 S0000012.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 15:06 S0000013.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 17:36 S0000014.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 20:08 S0000015.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 11 22:10 S0000016.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 00:44 S0000017.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 02:49 S0000018.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 05:23 S0000019.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 07:23 S0000020.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 10:32 S0000021.LOG
    -rw------- 1 ah_01 ah_01 40968192 Dec 12 13:01 S0000022.LOG
    -rw------- 1 ah_01 ah_01 512 Nov 29 12:34 SQLLPATH.TAG

    Could it be a long running tranaction started on Dec 10 and still not committed?

    2. The file system is not full:
    -bash-2.05b$ df -m
    Filesystem 1M-blocks Used Available Use% Mounted on
    /dev/rootvg/root 2016 365 1549 20% /
    /dev/sda2 99 16 78 17% /boot
    /dev/rootvg/home 6048 974 4767 17% /home
    /dev/rootvg/opt 15119 5203 9149 37% /opt
    /dev/rootvg/tmp 2016 93 1822 5% /tmp
    /dev/rootvg/usr 6048 1930 3812 34% /usr
    /dev/rootvg/var 1008 187 771 20% /var
    /dev/dbvg/oradata 1412114 1213389 126994 91% /db/oradata
    /dev/dbvg/db2data 604760 81274 492767 15% /db/db2data
    /dev/dbvg/backups 1032124 562017 417678 58% /db/backups
    /dev/rootvg/logs 10080 756 8812 8% /db/oradata/logs
    lindb6:/db/backups 844772 223192 578669 28% /lindb6_backups

    So how can I determine the problem? Your input is highly appreciated.
    Last edited by DBA-Jr; 12-12-05 at 15:22.

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    In the db2diag.log you also get a HANDLE of the long running transaction which should be removed to free the log space. You can "db2 list application show detail" command to identify the app. causing the problem. Then if you
    db2 "force application(H)" where H is the handle(typically a 2 or 3 digit number on our system) you will be OK.
    mota

  3. #3
    Join Date
    Mar 2005
    Posts
    108
    Thank you Mota. So I guess the following msg in db2diag.log identifies the application handle which is a long running application:
    ADM1823E The active log is full and is held by application handle "458".

    Since I already killed all applications, I won't be able to test it.
    Appreciate your advice.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to tell the application developers the 3 rules of life.

    You can never be too thin, too rich, or commit too often.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2004
    Posts
    49
    Quote Originally Posted by Marcus_A
    You need to tell the application developers the 3 rules of life.

    You can never be too thin, too rich, or commit too often.
    Hi Marcus,

    If you are not commit often, you will get 'transaction log full' error - as per my understanding. In my case we are loading 'N' GB data, so we used to give commit atleast for 40,000 - 60,000 rows.

    With Regards

    Bala

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •