Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2014
    Posts
    4

    Unhappy Unanswered: Inactive log files in the active log path but they are not able archive

    Hi All,

    I'm new to DB2 and I'm using DB2 9.7.07 and AIX 7.1

    I have a database named PLIDB, and its active log files are configured to store it in /db2/db4/actlog/NODE0000/ file system and the LOGARCHMETH1 has been enabled as USEREXIT. I have a file system for storing the archive log files (/db2/db4archlog). But the issue my active log file system is going to get filled and the archive log file system is not growing.

    There are lot of Inactive log files in the active log path but they are not able archive.

    Please see the below db cfg of my database and extend your help.

    $ db2 get db cfg for PLIDB

    Database Configuration for Database PLIDB

    Database configuration release level = 0x0d00
    Database release level = 0x0d00

    Database territory = us
    Database code page = 1208
    Database code set = utf-8
    Database country/region code = 1
    Database collating sequence = IDENTITY
    Alternate collating sequence (ALT_COLLATE) =
    Number compatibility = OFF
    Varchar2 compatibility = OFF
    Date compatibility = OFF
    Database page size = 32768

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Statement concentrator (STMT_CONC) = OFF

    Discovery support for this database (DISCOVER_DB) = ENABLE

    Restrict access = NO
    Default query optimization class (DFT_QUERYOPT) = 5
    Degree of parallelism (DFT_DEGREE) = 1
    Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
    Default refresh age (DFT_REFRESH_AGE) = 0
    Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN

    Backup pending = NO

    All committed transactions have been written to disk = NO
    Rollforward pending = NO
    Restore pending = NO

    Multi-page file allocation enabled = YES

    Log retain for recovery status = RECOVERY
    User exit for logging status = YES

    Self tuning memory (SELF_TUNING_MEM) = ON
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(3561995)
    Database memory threshold (DB_MEM_THRESH) = 10
    Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(215097)
    Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
    Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(8207)
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(1805)
    Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(361)

    Database heap (4KB) (DBHEAP) = AUTOMATIC(2624)
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
    Log buffer size (4KB) (LOGBUFSZ) = 256
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 87679
    Buffer pool size (pages) (BUFFPAGE) = 1000
    SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(8192)
    Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
    Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
    Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

    Interval for checking deadlock (ms) (DLCHKTIME) = 10000
    Lock timeout (sec) (LOCKTIMEOUT) = -1

    Changed pages threshold (CHNGPGS_THRESH) = 80
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(15)
    Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(3)
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = YES
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

    Track modified pages (TRACKMOD) = YES

    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

    Max number of active applications (MAXAPPLS) = AUTOMATIC(40)
    Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
    Max DB files open per application (MAXFILOP) = 61440

    Log file size (4KB) (LOGFILSIZ) = 24576
    Number of primary log files (LOGPRIMARY) = 30
    Number of secondary log files (LOGSECOND) = 40
    Changed path to log files (NEWLOGPATH) =
    Path to log files = /db2/db4/actlog/NODE0000/
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file = S0003007.LOG
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Block non logged operations (BLOCKNONLOGGED) = NO
    Percent max primary log space by transaction (MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
    Log retain for recovery enabled (LOGRETAIN) = RECOVERY
    User exit for logging enabled (USEREXIT) = ON

    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 of remote server (HADR_REMOTE_INST) =
    HADR timeout value (HADR_TIMEOUT) = 120
    HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
    HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0

    First log archive method (LOGARCHMETH1) = USEREXIT
    Options for logarchmeth1 (LOGARCHOPT1) =
    Second log archive method (LOGARCHMETH2) = OFF
    Options for logarchmeth2 (LOGARCHOPT2) =
    Failover log archive path (FAILARCHPATH) =
    Number of log archive retries on error (NUMARCHRETRY) = 5
    Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
    Vendor options (VENDOROPT) =

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
    Log pages during index build (LOGINDEXBUILD) = OFF
    Default number of loadrec sessions (DFT_LOADREC_SES) = 1
    Number of database backups to retain (NUM_DB_BACKUPS) = 12
    Recovery history retention (days) (REC_HIS_RETENTN) = 366
    Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF

    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

    Automatic maintenance (AUTO_MAINT) = ON
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = ON
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF

    Auto-Revalidation (AUTO_REVAL) = DEFERRED
    Currently Committed (CUR_COMMIT) = ON
    CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
    Enable XML Character operations (ENABLE_XMLCHAR) = YES
    WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
    Monitor Collect Settings
    Request metrics (MON_REQ_METRICS) = BASE
    Activity metrics (MON_ACT_METRICS) = BASE
    Object metrics (MON_OBJ_METRICS) = BASE
    Unit of work events (MON_UOW_DATA) = NONE
    Lock timeout events (MON_LOCKTIMEOUT) = NONE
    Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
    Lock wait events (MON_LOCKWAIT) = NONE
    Lock wait event threshold (MON_LW_THRESH) = 5000000
    Number of package list entries (MON_PKGLIST_SZ) = 32
    Lock event notification level (MON_LCK_MSG_LVL) = 1

    SMTP Server (SMTP_SERVER) =
    SQL conditional compilation flags (SQL_CCFLAGS) =
    Section actuals setting (SECTION_ACTUALS) = NONE
    Connect procedure (CONNECT_PROC) =

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, what does the log of your userexit program or script say?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2014
    Posts
    4

    Unhappy Inactive log files in the active log path but they are not able archive

    Hi n_i,

    I'm getting the below error message at the db2diag.log and please help:

    2014-10-09-18.04.08.116419+330 E3307337182A378 LEVEL: Info
    PID : 9765086 TID : 37012 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 37012 EDUNAME: db2logmgr (CBSFPDB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3108
    DATA #1 : <preformatted>
    Started archive for log file S0000331.LOG.

    2014-10-09-18.04.08.181638+330 E3307337561A525 LEVEL: Error
    PID : 9765086 TID : 37012 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 37012 EDUNAME: db2logmgr (CBSFPDB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgUserexitLogAdminMsg, probe:1140
    MESSAGE : ADM1832E DB2 was unable to find the user exit program when archiving
    log file "S0000331.LOG" from "/db2/db3/actlog/NODE0000/" for database
    "CBSFPDB". The error code was "24".

    2014-10-09-18.04.08.183490+330 E3307338087A434 LEVEL: Warning
    PID : 9765086 TID : 37012 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 37012 EDUNAME: db2logmgr (CBSFPDB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3150
    MESSAGE : ADM1848W Failed to archive log file "S0000331.LOG" to "USEREXIT"
    from "/db2/db3/actlog/NODE0000/".

    2014-10-09-18.04.08.183802+330 E3307338522A435 LEVEL: Error
    PID : 9765086 TID : 37012 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 37012 EDUNAME: db2logmgr (CBSFPDB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgArchiveLogFile, probe:3155
    DATA #1 : <preformatted>
    Failed to archive log file S0000331.LOG to USEREXIT from /db2/db3/actlog/NODE0000/ return code 24.

    2014-10-09-18.04.08.183952+330 I3307338958A450 LEVEL: Warning
    PID : 9765086 TID : 37012 PROC : db2sysc 0
    INSTANCE: db2inst1 NODE : 000
    EDUID : 37012 EDUNAME: db2logmgr (CBSFPDB) 0
    FUNCTION: DB2 UDB, data protection services, sqlpgRetryFailedArchive, probe:4780
    MESSAGE : Still unable to archive log file 331 due to rc 24 for LOGARCHMETH1
    using method 4 and target /db2/db3/archlog/.

  4. #4
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    The message in the db2diag seems clear: ADM1832E DB2 was unable to find the user exit program when archiving. If you've told db2 to use a userexit (which is a legacy way to do archival), and you've not provided a userexit, then db2 won't be able to archive logs.

    If you don't have a USEREXIT program, or you don't know what it is, or why it is needed, then think about setting your LOGARCHMETH1 to DISK:/db2/db4archlog . The on-line documentation gives all the details, so read that first. You will need a brief service outage for this.

  5. #5
    Join Date
    Feb 2014
    Posts
    4

    Red face Inactive log files in the active log path but they are not able archive

    Hi db2mor / All,

    I have changed the LOGARCHMETH1 to the DISK:/db2/db4/archlog path and then restarted the instance and now it is working fine.

    Thanks a lot for your assistance

Posting Permissions

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