DB2 Error in db2diag.log

DB_N00b · #1 (**permalink**) 03-04-10, 05:02

Hello friends,

I would like to ask for help. My db2diag.log shows once a day this error

Code:

2010-03-04-06.07.23.965939+060 I45619361C382      LEVEL: Warning
PID     : 1474702              TID  : 1           PROC : db2agent (idle) 0
INSTANCE: db2inst2             NODE : 000         DB   : WPSDB
APPHDL  : 0-454                APPID: *LOCAL.db2inst2.100304050722
FUNCTION: DB2 UDB, database utilities, sqlubSetupJobControl, probe:2041
MESSAGE : Starting an online db backup.

2010-03-04-06.07.42.210166+060 I45619744C394      LEVEL: Error
PID     : 2908282              TID  : 1           PROC : db2loggw (WPSDB) 0
INSTANCE: db2inst2             NODE : 000         DB   : WPSDB   
FUNCTION: DB2 UDB, data protection, sqlpgasn2, probe:2950
RETCODE : ZRC=0x871000E8=-2028994328=SQLPG_LOGR_RECREATE_LOGFILE
          "Loggw cannot find log file, need loggr to recreate it"

2010-03-04-06.07.42.611087+060 I45620139C356      LEVEL: Warning
PID     : 1474702              TID  : 1           PROC : db2agent (idle) 0
INSTANCE: db2inst2             NODE : 000         DB   : WPSDB
APPHDL  : 0-454                APPID: *LOCAL.db2inst2.100304050722
FUNCTION: DB2 UDB, database utilities, sqlubcka, probe:130
MESSAGE : Backup Complete.

I already turned the autocommit on, because I have thought this would fix my problem. But it didnt help at all. Does anyone have some ideas about my DB2 Problem? I am thankful for each post. By the way, I am using DB2 Version 8

dr_te_z · #2 (**permalink**) 03-04-10, 06:25

It happens during your backup. Can you send your backup command? Also your dbm & db configs?
Can we trust that you do not touch db2 transaction log files with O.S. commands?

DB_N00b · #3 (**permalink**) 03-04-10, 06:48

Command of the backup

"/bin/su – db2inst1 -c "db2 backup database $database online to $backup_path"

nvk@vhv · #4 (**permalink**) 03-04-10, 09:05

The Backup-Command is using user db2inst1 (Instance db2inst1???). The Entries from the db2diag.log is from Instance db2inst2.
The dbm and db cfg might help to identify the issue.

db2 get dbm cfg

db2 get db cfg for wpsdb

DB_N00b · #5 (**permalink**) 03-04-10, 09:17

Yeah my mistake.

I ment to say db2inst2 ... so thats not supposed to be the problem :-)

DB_N00b · #6 (**permalink**) 03-04-10, 09:19

(0)db2inst2@r1029 1# db2 get dbm cfg

Database Manager Configuration

Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level = 0x0a00

CPU speed (millisec/instruction) (CPUSPEED) = 3.188323e-07
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02

Max number of concurrently active databases (NUMDB) = 15
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =

Default charge-back account (DFT_ACCOUNT_STR) =

Java Development Kit installation path (JDK_PATH) = /usr/java14

Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst2/sqllib/db2dump

Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = ON

SYSADM group name (SYSADM_GROUP) = DB2GRP1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

Default database path (DFTDBPATH) = /emc

Database monitor heap size (4KB) (MON_HEAP_SZ) = 512
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024

Sort heap threshold (4KB) (SHEAPTHRES) = 20000

Directory cache support (DIR_CACHE) = YES

Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 400
Agent pool size (NUM_POOLAGENTS) = 200(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 10
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS

Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
Initial number of fenced processes (NUM_INITFENCED) = 0

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

Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) = r10291
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =

TCP/IP Service name (SVCENAME) = db2c_db2inst2
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 4096
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC

Node connection elapse time (sec) (CONN_ELAPSE) = 10
Max number of node connection retries (MAX_CONNRETRIES) = 5
Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

db2start/db2stop timeout (min) (START_STOP_TIME) = 10

DB_N00b · #7 (**permalink**) 03-04-10, 09:22

(0)db2inst2@r1029 233# db2 get db cfg for wpsdb

Database Configuration for Database wpsdb

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = us
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = BINARY
Alternate collating sequence (ALT_COLLATE) =
Database page size = 8192

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

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

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

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

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 2400
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 32
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 1000

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 16384
Default application heap (4KB) (APPLHEAPSZ) = 4096
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = 30

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

Track modified pages (TRACKMOD) = OFF

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

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 5
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 4000
Number of primary log files (LOGPRIMARY) = 12
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files = /var/log/db2/db2inst2/SQLOGDIR/WPSDB/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0001304.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active 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) = 100
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

HADR database role = PRIMARY
HADR local host name (HADR_LOCAL_HOST) = r1029
HADR local service name (HADR_LOCAL_SVC) = 52021
HADR remote host name (HADR_REMOTE_HOST) = r1031
HADR remote service name (HADR_REMOTE_SVC) = 52121
HADR instance name of remote server (HADR_REMOTE_INST) = db2inst2
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = LOGRETAIN
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) = RESTART
Log pages during index build (LOGINDEXBUILD) = ON
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

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
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

nvk@vhv · #8 (**permalink**) 03-04-10, 10:22

Ok,

there are two Issues in you DB-Cfg:

1.
Path to log files = /var/log/db2/db2inst2/SQLOGDIR/WPSDB/NODE0000/
You should not put the Transaction-Logs of a database into /var/log. It's used by the OS and other applications. Maybe there's a job running which is deleting old files. If an active logfile is deleted the database will be corrupted.

2.
First log archive method (LOGARCHMETH1) = LOGRETAIN
Archived logfiles will remain in the same directory. If you remove old logfile there's always the risk of deleting an active logfile. I'ld suggest to use DISK:/other/directory.
Archived logfiles will be moved to the specified directory. There's less risk when you delete archived logfiles.

Hth,
nvk

DB_N00b · #9 (**permalink**) 03-04-10, 10:37

Ok I will try that. Thank you buddy!

db2girl · #10 (**permalink**) 03-04-10, 16:35

Do you delete any logs from the active log dir? I mean logs that have a sequence number higher that the first active log. Online backup will truncate the active log and db2 will want to use the next one. In your case, db2 finds that the next log it wants to use is no longer there so it will recreate it. To stop this message, try restarting the db (if possible).

dr_te_z · #11 (**permalink**) 03-05-10, 01:50

Quote:

Originally Posted by DB_N00b

Command of the backup"/bin/su – db2inst1 -c "db2 backup database $database online to $backup_path"

One more thing: When you make an online backup always use the "include logs" parameter. A backup image like that is useless unless you've got the right set of logifiles for the unavoidable rollforward. With this option activated those (minimum required) logfiles are included in the backup-image itself and can be a life-saver.....

DB_N00b · #12 (**permalink**) 03-05-10, 03:09

Quote:

Originally Posted by nvk@vhv

Ok,

First log archive method (LOGARCHMETH1) = LOGRETAIN
Archived logfiles will remain in the same directory. If you remove old logfile there's always the risk of deleting an active logfile. I'ld suggest to use DISK:/other/directory.
Archived logfiles will be moved to the specified directory. There's less risk when you delete archived logfiles.

Hth,
nvk

That seems to be a good idea. How do I save the Logfile somewhere else? Please give me a clue. Thank you