Results 1 to 10 of 10
  1. #1
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1

    Answered: DB2 Reorg and Transactional logs

    Hello,
    We run an offline reorg job every week for past few years, but suddenly we have been getting some issues during that time. Past 2months we notice that lots and lots of transactional logs are being generated and filling up the filesystem. The filesystem becomes 100% filled and messages and dumped in the db2diag.log. But it resolves on its own by the time we come in and check. This time it led to a crash!
    Can someone suggest a way to fix this? I am not really sure if expanding the filesystem will fix the issue. Details are below.

    DB2 Version -
    DB21085I Instance "pcolm1in" uses "64" bits and DB2 code release "SQL09058"
    with level identifier "06090107".
    Informational tokens are "DB2 v9.5.0.8", "s110603", "IP23263", and Fix Pack
    "8".
    Product is installed at "/opt/IBM/db2/V9.5".

    OS - AIX 7.1

    Reorg command - ( We run a runstats , then reorg for around 20 tables and a runstats again. It takes around 3 hours for the full process)
    reorg table SCHEMA.TABLENAME index INDEXNAME allow read access use TEMPSPACE1 INDEXSCAN longlobdata
    DB2 Transactional log filesystem -
    /dev/pcolm1dlvg1l02 10.00 8.47 16% 47 1% /db2/log/pcolm1in ===> this is the usual state, gets 100% filled during reorg.

    We use TSM to archive the logs.

    LOGBUFSZ (4KB) 99
    LOGFILSIZ (4KB) 10000
    LOGPRIMARY 40
    LOGSECOND -1

    Many thanks!
    Abhi

  2. Best Answer
    Posted by mpaul

    "If you don't have HADR enabled, I think you don't need LOGINDEXBUILD enabled, it logs all index rebuild activity.

    if you disable it check the below statement from knowledge center


    When you set the logindexbuild configuration parameter to Off, index objects are marked invalid while rolling forward through index creation, recreation, or reorganization operations. You must re-create indexes after the rolling forward operation completes and the database is restarted. Index re-creation depends on the value of the indexrec configuration parameter and if you issue an explicit RESTART DATABASE command


    if you disable your logging will reduce.

    regds
    paul"


  3. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Does this problem happen only on a single table or multiple tables (you mention 20 tables)?

    You gave no facts about volumes (row counts) per table and average-row-length, but your log files seem very small and you have enabled infinite secondary logging. So most likely those secondary logs are being heavily used. You should check if seclogs are being used even when reorg is not running - you want your primary log capacity to be adequate for the daily workload with no dependency on seclogs except for occasional situations.

    You've not described what changed BEFORE this symptom began. DDL changes on the tables concerned? Volume changes? Workload changes?

    You've also not described whether there are any other databases in the same DB2-instance that are activated at the time of the reorg that might contribute to log consumption.

    Apart from disk full messages in db2diag, are there any other symptoms when the reorg starts?

    If you have a non production system, with the same versions, and similar workload, you should consider upgrading to the final fixpack of V9.5 (i.e. fixpack 10), or better if you persuade the business to migrate to a currently supported version of DB2.

  4. #3
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1
    db2mor, Thanks for your response.
    1. The largest table has around 37million rows, smallest with 11 rows.
    2. This is the only database in this instance and nothing else runs during that time.
    3. We did do some changes around 2months back, we updated some columns from Varchar to Char. We dropped and recreated the tables, indexes with new column definitions and loaded the data back. (This was the fastest method in our testing). But no workload or volume changes.
    4. We did not find anything unusual in the logs. It just had the Index rebuild messages. Many transaction logs were created during the whole reorg/runstats window, so lots of filesystem filled messages were found. Slowly they were archived and it was back to normal.
    5. The secondary logs are not most of the time, such issues are only generated during this runstats/reorg window.

    I appreciate your help on this, only after seeing your message I realized the DDL changes. I will also look into that and see if I can find anything else.

    Thanks !
    Abhi

  5. #4
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1
    I checked the latest diag log and the ones from before the DDL changes were made.
    "Active log * has not been archived" keeps repeating till the reorg completes on the huge table. (In both current logs and the old diag logs from before the ddl changes)


    2016-08-12-02.51.25.583214+720 E172441A509 LEVEL: Warning
    PID : 9502772 TID : 3876 PROC : db2sysc 0
    INSTANCE: pcolm1in NODE : 000 DB : PCOLM1DB
    EDUID : 3876 EDUNAME: db2loggr (PCOLM1DB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgadf, probe:100
    MESSAGE : ADM1550W The active log space exceeds the LOGPRIMARY DB
    configuration parameter. ROLLBACK may be slow if log files have to
    be retrieved from archive.

    2016-08-12-02.51.35.583898+720 I172951A394 LEVEL: Info
    PID : 9502772 TID : 3876 PROC : db2sysc 0
    INSTANCE: pcolm1in NODE : 000 DB : PCOLM1DB
    EDUID : 3876 EDUNAME: db2loggr (PCOLM1DB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgadf, probe:630
    DATA #1 : <preformatted>
    Active log S0032638.LOG has not been archived.
    Last edited by abhivelu; 08-14-16 at 23:33.

  6. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    If I had your situtation I would increase LOGPRIMARY and LOGFILSIZ significantly (so that the primary log capacity was at least the size of the largest table being reorg'd).

  7. #6
    Join Date
    Aug 2016
    Posts
    20
    Provided Answers: 1
    Is HADR is configured ?

    LOGINDEXBUILD is the parameter is enabled ?

    20 tables have any sql replication setup ?

    what is the primary and secondary u configured

    select * from sysibmadm.LOG_UTILIZATION , can you give the output

    regds
    Paul
    Last edited by mpaul; 08-16-16 at 00:12.

  8. #7
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1
    Hi Paul,
    No HADR or replication is setup.
    Log pages during index build (LOGINDEXBUILD) = ON


    DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
    -------------------------------------------------------------------------------------------------------------------------------- ----------------------- -------------------- ---------------------- --------------------- --------------
    PCOLM1DB - 262661 - 278833 0

    ---------------------
    LOGBUFSZ (4KB) 99
    LOGFILSIZ (4KB) 10000
    LOGPRIMARY 40
    LOGSECOND -1


    Thanks
    Abhi
    Last edited by abhivelu; 08-16-16 at 00:21.

  9. #8
    Join Date
    Aug 2016
    Posts
    20
    Provided Answers: 1
    If you don't have HADR enabled, I think you don't need LOGINDEXBUILD enabled, it logs all index rebuild activity.

    if you disable it check the below statement from knowledge center


    When you set the logindexbuild configuration parameter to Off, index objects are marked invalid while rolling forward through index creation, recreation, or reorganization operations. You must re-create indexes after the rolling forward operation completes and the database is restarted. Index re-creation depends on the value of the indexrec configuration parameter and if you issue an explicit RESTART DATABASE command


    if you disable your logging will reduce.

    regds
    paul

  10. #9
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1
    Thanks Paul, I will test with LOGINDEXBUILD disabled.

    Cheers,
    Abhi

  11. #10
    Join Date
    May 2016
    Location
    Wellington
    Posts
    22
    Provided Answers: 1
    Thank you Paul, it worked perfectly!

    Regards, Abhi

Tags for this Thread

Posting Permissions

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