473,320 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

DB2 crash?!?

Hey all,

at the company where I work we had a strange situation yesterday.
Our DB2 database locked up, or as it later seemed, the DBM, or some
connection manager.

We couldn't open new connections to the database, but existing
connections remained. So the instances were working fine.

Also, I couldn't connect using IPC, so I was unable to shut the database
down in a clean way.

DB2Diag provided no usable information, nor the OS logs (linux 2.6)
Still not sure if it was caused by the OS or the DB.

Does anyone have a clue on what caused it?
-R-

DB2 8.2 FP8 running on SuSE EE 9.1
2GB Ram, 1 AMD Opteron CPU, Tiny test raid of 80GB.
Nov 12 '05 #1
14 3522
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
Hey all,

at the company where I work we had a strange situation yesterday.
Our DB2 database locked up, or as it later seemed, the DBM, or some
connection manager.

We couldn't open new connections to the database, but existing connections
remained. So the instances were working fine.

Also, I couldn't connect using IPC, so I was unable to shut the database
down in a clean way.

DB2Diag provided no usable information, nor the OS logs (linux 2.6)
Still not sure if it was caused by the OS or the DB.

Does anyone have a clue on what caused it?
-R-

DB2 8.2 FP8 running on SuSE EE 9.1
2GB Ram, 1 AMD Opteron CPU, Tiny test raid of 80GB.


That is definitely not good, but it is not a crash.

If the db2diag.log does not provide any info, you might need to increase the
diagnosis level (dbm parm). But it probably had something to do with
reaching the maximum connections allowed, either by configuration or by
license limits. Next time I would try to cancel (force) some or all
individual application handles to see if I could make new connections after
that.

I don't believe that DB2 was ever supported under SuSE EE 9.1 distributions,
but I could be wrong. But it does make me wonder about the license.
Nov 12 '05 #2
Mark A wrote:

That is definitely not good, but it is not a crash.

If the db2diag.log does not provide any info, you might need to increase the
diagnosis level (dbm parm). But it probably had something to do with
reaching the maximum connections allowed, either by configuration or by
license limits. Next time I would try to cancel (force) some or all
individual application handles to see if I could make new connections after
that.

I don't believe that DB2 was ever supported under SuSE EE 9.1 distributions,
but I could be wrong. But it does make me wonder about the license.


During the lockup, I tried a few things.
First, I closed most of the clients connected. But the conntections
remained (very strange, not closed waiting or anything).
Secondly I just closed the ETH interface.
Still... Connections remained (on a shutdown network interface).

I seemed that a connection manager was in a endless loop, except for the
CPU usage, which was normal.

None of the processes would react on signals, not even a kill -9
everything I started locally (db2 cli, db2stop) would result in a
hanging process, so I couldn't kill the cli, nor the db2stop.
After a few attempts, I used my 1 remaining console to reboot the
machine and cross my fingers no corruption had occured (it didn't).

The license we have is Per CPU (so no user limit).
The set MAX USER = MAX Agent etc.. is 200
Open connections during problem, less than 20.

I will raise the verboseness of the DIAG.
But hope it was a one time problem.

This instance of the database was running for 4 months, and the only
thing that changed was that I recently added a table with 6M+ records.

Still no clue on what happened.

-R-

Nov 12 '05 #3
Jurgen Haan wrote:
Mark A wrote:

That is definitely not good, but it is not a crash.

If the db2diag.log does not provide any info, you might need to
increase the diagnosis level (dbm parm). But it probably had something
to do with reaching the maximum connections allowed, either by
configuration or by license limits. Next time I would try to cancel
(force) some or all individual application handles to see if I could
make new connections after that.

I don't believe that DB2 was ever supported under SuSE EE 9.1
distributions, but I could be wrong. But it does make me wonder about
the license.

During the lockup, I tried a few things.
First, I closed most of the clients connected. But the conntections
remained (very strange, not closed waiting or anything).
Secondly I just closed the ETH interface.
Still... Connections remained (on a shutdown network interface).

I seemed that a connection manager was in a endless loop, except for the
CPU usage, which was normal.

None of the processes would react on signals, not even a kill -9
everything I started locally (db2 cli, db2stop) would result in a
hanging process, so I couldn't kill the cli, nor the db2stop.
After a few attempts, I used my 1 remaining console to reboot the
machine and cross my fingers no corruption had occured (it didn't).

The license we have is Per CPU (so no user limit).
The set MAX USER = MAX Agent etc.. is 200
Open connections during problem, less than 20.

I will raise the verboseness of the DIAG.
But hope it was a one time problem.

This instance of the database was running for 4 months, and the only
thing that changed was that I recently added a table with 6M+ records.

Still no clue on what happened.

-R-

what shows the memory visualizer?
best thing is to upper the diaglog level.
i had some crashes on really good sql statements after i made them
proper everything works fine.
sometimes an ipclean command helps if developers make some terrible
commands than this helps often.
look at your data stack on your system maybe you have not the right
parameters.
http://www-128.ibm.com/developerwork...ex.html#N108DA
just a link for memory settings.

please post you db cfg and your dbm cfg this could help us, too.

cu,

indika
Nov 12 '05 #4
Ok... you asked for it....
Database Manager Configuration

Node type = Database Server with local and remote clients

Database manager configuration release level = 0x0a00

CPU speed (millisec/instruction) (CPUSPEED) = 2.361721e-07

Max number of concurrently active databases (NUMDB) = 8
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) =
/opt/IBMJava2-141

Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =

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

SYSADM group name (SYSADM_GROUP) = DASADM1
SYSCTRL group name (SYSCTRL_GROUP) = DASADM1
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) = /home/db2inst1

Database monitor heap size (4KB) (MON_HEAP_SZ) = 1024
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 1024
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) = 200
Agent pool size (NUM_POOLAGENTS) = 100(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
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) =
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) = db2inst1
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) = 1024
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
+--------------------------------------------------------------------------------------------------------+

Database Configuration for Database

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

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

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 = NO

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) = 1200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 723
Log buffer size (4KB) (LOGBUFSZ) = 65
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) = 500

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) = 1000

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 341
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 256
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 8000

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

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

Track modified pages (TRACKMOD) = ON

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

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

Log file size (4KB) (LOGFILSIZ) = 4096
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files =
/data/database/efdevdb/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000452.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) = 120
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

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

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) = 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

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) = ON
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = ON
Automatic profile updates (AUTO_PROF_UPD) = ON
Automatic reorganization (AUTO_REORG) = ON
Nov 12 '05 #5
And one addition:

This is the first time ever for us that a DB2 database has done this.
I have no idea how to reproduce it.
There were no strange processes connected to the database, none of the
developers were doing weird things.

-R-
Nov 12 '05 #6
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
Ok... you asked for it....

DFT_MON_TIMESTAMP should be ON. It is the only Monitor switch that is ON by
default, so someone turned it off.

I would also significantly increase the size of the log files and also
increase number of log files somewhat.

There are other things that could probably be improved, but these stuck out.
Nov 12 '05 #7
Jurgen Haan wrote:
Ok... you asked for it....
Database Manager Configuration

Node type = Database Server with local and remote clients

Database manager configuration release level = 0x0a00

CPU speed (millisec/instruction) (CPUSPEED) = 2.361721e-07

Max number of concurrently active databases (NUMDB) = 8
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) =
/opt/IBMJava2-141

Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =

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

SYSADM group name (SYSADM_GROUP) = DASADM1
SYSCTRL group name (SYSCTRL_GROUP) = DASADM1
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) = /home/db2inst1

Database monitor heap size (4KB) (MON_HEAP_SZ) = 1024
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 1024
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) = 200
Agent pool size (NUM_POOLAGENTS) = 100(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
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) =
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) = db2inst1
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) = 1024
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
+--------------------------------------------------------------------------------------------------------+
Database Configuration for Database

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

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

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 = NO

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) = 1200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 723
Log buffer size (4KB) (LOGBUFSZ) = 65
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) = 500

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) = 1000

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 341
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 256
Package cache size (4KB) (PCKCACHESZ) = 859
Statistics heap size (4KB) (STAT_HEAP_SZ) = 8000

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

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

Track modified pages (TRACKMOD) = ON

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

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

Log file size (4KB) (LOGFILSIZ) = 4096
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files =
/data/database/efdevdb/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000452.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) = 120
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

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

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) = 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

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


Set this settings maybe this help you.

Max number of existing agents (MAXAGENTS) = 640
Max number of active applications (MAXAPPLS) = AUTOMATIC

Tell me if it works.

Cu,

indika
Nov 12 '05 #8
Mark A wrote:
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
Ok... you asked for it....


DFT_MON_TIMESTAMP should be ON. It is the only Monitor switch that is ON by
default, so someone turned it off.

I would also significantly increase the size of the log files and also
increase number of log files somewhat.

There are other things that could probably be improved, but these stuck out.


?!? Spank my ass with a drumstick!
This would be my fault... I think.

-R-
Nov 12 '05 #9
Mark A wrote:
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
Ok... you asked for it....


DFT_MON_TIMESTAMP should be ON. It is the only Monitor switch that is ON by
default, so someone turned it off.

I would also significantly increase the size of the log files and also
increase number of log files somewhat.

There are other things that could probably be improved, but these stuck out.


Question: why can the DFT_MON_TIMESTAMP parameter be set to OFF?
What use has it in OFF position?

-R-
Nov 12 '05 #10
Ian
Jurgen Haan wrote:
Mark A wrote:
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
Ok... you asked for it....


DFT_MON_TIMESTAMP should be ON. It is the only Monitor switch that is
ON by default, so someone turned it off.

I would also significantly increase the size of the log files and also
increase number of log files somewhat.

There are other things that could probably be improved, but these
stuck out.


Question: why can the DFT_MON_TIMESTAMP parameter be set to OFF?
What use has it in OFF position?


There is no requirement for DFT_MON_TIMESTAMP to be on. It provides
useful monitoring information, but for the "last mile" tuning effort
disabling it will improve performance on the box. It does not make
sense, however, to turn off DFT_MON_TIMESTAMP when some of the other
DFT_MON parameters are ON.

I doubt that this setting is causing your issue.
Nov 12 '05 #11
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42********@newsfeed.slurp.net...

There is no requirement for DFT_MON_TIMESTAMP to be on. It provides
useful monitoring information, but for the "last mile" tuning effort
disabling it will improve performance on the box. It does not make
sense, however, to turn off DFT_MON_TIMESTAMP when some of the other
DFT_MON parameters are ON.

I doubt that this setting is causing your issue.


I never said that it "caused" the problem, but it might help diagnose the
problem.

The other suggestions I offered "probably" did not cause the problem either,
but they are advisable to change.
Nov 12 '05 #12
You don't say anything about DB2 Version/Release/fixpak levels, nor your
os version/release/maintenance levels. You potentially could have
surfaced a bug. If you're not on latest maintenance, might be worth
opening a PMR to see if they can come up with anything. Also, see

http://www-306.ibm.com/software/data...inux/validate/

for Linux requirements.

Larry Edelstein

Jurgen Haan wrote:
Mark A wrote:

That is definitely not good, but it is not a crash.

If the db2diag.log does not provide any info, you might need to
increase the diagnosis level (dbm parm). But it probably had something
to do with reaching the maximum connections allowed, either by
configuration or by license limits. Next time I would try to cancel
(force) some or all individual application handles to see if I could
make new connections after that.

I don't believe that DB2 was ever supported under SuSE EE 9.1
distributions, but I could be wrong. But it does make me wonder about
the license.

During the lockup, I tried a few things.
First, I closed most of the clients connected. But the conntections
remained (very strange, not closed waiting or anything).
Secondly I just closed the ETH interface.
Still... Connections remained (on a shutdown network interface).

I seemed that a connection manager was in a endless loop, except for the
CPU usage, which was normal.

None of the processes would react on signals, not even a kill -9
everything I started locally (db2 cli, db2stop) would result in a
hanging process, so I couldn't kill the cli, nor the db2stop.
After a few attempts, I used my 1 remaining console to reboot the
machine and cross my fingers no corruption had occured (it didn't).

The license we have is Per CPU (so no user limit).
The set MAX USER = MAX Agent etc.. is 200
Open connections during problem, less than 20.

I will raise the verboseness of the DIAG.
But hope it was a one time problem.

This instance of the database was running for 4 months, and the only
thing that changed was that I recently added a table with 6M+ records.

Still no clue on what happened.

-R-

Nov 12 '05 #13
Larry wrote:
You don't say anything about DB2 Version/Release/fixpak levels, nor your
os version/release/maintenance levels. You potentially could have
surfaced a bug. If you're not on latest maintenance, might be worth
opening a PMR to see if they can come up with anything. Also, see

http://www-306.ibm.com/software/data...inux/validate/

for Linux requirements.

Larry Edelstein


The bottom of my initial post read:

DB2 8.2 FP8 running on SuSE EE 9.1
2GB Ram, 1 AMD Opteron CPU, Tiny test raid of 80GB.
Nov 12 '05 #14
Mark A wrote:
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42********@newsfeed.slurp.net...
There is no requirement for DFT_MON_TIMESTAMP to be on. It provides
useful monitoring information, but for the "last mile" tuning effort
disabling it will improve performance on the box. It does not make
sense, however, to turn off DFT_MON_TIMESTAMP when some of the other
DFT_MON parameters are ON.

I doubt that this setting is causing your issue.

I never said that it "caused" the problem, but it might help diagnose the
problem.

The other suggestions I offered "probably" did not cause the problem either,
but they are advisable to change.


I know it wasn't the cause of the problem, so this was a little
offtopic, but still I appreciate people pointing out configuration
problems, even though they probably have nothing to do with the problem
(the configuration problems, that is).

-R-
Nov 12 '05 #15

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

48
by: Joseph | last post by:
Hi I'm writing a commercial program which must be reliable. It has to do some basic reading and writing to and from files on the hard disk, and also to a floppy. I have foreseen a potential...
8
by: Eric Brunel | last post by:
Hi all, I was creating a Tkinter widget in the style of the reversed tabs below Excel worksheets and I stepped in a serious problem: the code I made makes python crash with a seg fault, bus...
0
by: roni | last post by:
hi. i have application written in vb.net + managed c++ dll that call also to unmanaged c++ function. the application crash. i open the dump file of the crash with WinDbg and that's is the...
10
by: xixi | last post by:
i have db2 udb v8.1 on windows 64 bit 2003 server, after db2 server start , i found this in the db2diag.log, is this error? 2004-05-05-15.28.30.780000 Instance:DB2 Node:000...
8
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to me only in a dynamically generated, downloadable...
14
by: JK Peck | last post by:
I have a fairly large Access application that ran correctly in Access 2000. After upgrading to Access 2003 (and recompiling and updating references), it reliably crashes at a certain point. If I...
34
by: NewToCPP | last post by:
Hi, Why does a C/C++ programs crash? When there is access to a null pointer or some thing like that programs crash, but why do they crash? Thanks.
12
by: benjamin.krulewitch | last post by:
I'm debugging an issue with a C program that causes the computer to crash, and I'm attempting to log information immediately before the crash occurs. I us my BKprintLog function (see below) to...
110
by: alf | last post by:
Hi, is it possible that due to OS crash or mysql itself crash or some e.g. SCSI failure to lose all the data stored in the table (let's say million of 1KB rows). In other words what is the worst...
11
by: =?Utf-8?B?R2VyaGFyZA==?= | last post by:
I have run into a situation that if a page/tab that uses the Ajax toolkit (using .net version 3.5) is closed before the Ajax enable controls complete loading, then IE locks up. Does it in both IE7...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.