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.

re: DB2 crash?!?

"Jurgen Haan" <jurgen@fake.dom> wrote in message
news:427090cf$0$150$e4fe514c@news.xs4all.nl...[color=blue]
> 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.[/color]

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.

re: DB2 crash?!?

Mark A wrote:[color=blue]
>
> 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.
>[/color]

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-

re: DB2 crash?!?

Jurgen Haan wrote:[color=blue]
> Mark A wrote:
>[color=green]
>>
>> 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.[/color]
>
>
> 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-
>[/color]
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

re: DB2 crash?!?

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

re: DB2 crash?!?

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-

re: DB2 crash?!?

"Jurgen Haan" <jurgen@fake.dom> wrote in message
news:4270ce16$0$160$e4fe514c@news.xs4all.nl...[color=blue]
> Ok... you asked for it....
>
>[/color]
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.

re: DB2 crash?!?

Jurgen Haan wrote:[color=blue]
> 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[/color]

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

re: DB2 crash?!?

Mark A wrote:[color=blue]
> "Jurgen Haan" <jurgen@fake.dom> wrote in message
> news:4270ce16$0$160$e4fe514c@news.xs4all.nl...
>[color=green]
>>Ok... you asked for it....
>>
>>[/color]
>
> 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.
>
>[/color]

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

-R-

re: DB2 crash?!?

Mark A wrote:[color=blue]
> "Jurgen Haan" <jurgen@fake.dom> wrote in message
> news:4270ce16$0$160$e4fe514c@news.xs4all.nl...
>[color=green]
>>Ok... you asked for it....
>>
>>[/color]
>
> 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.
>
>[/color]

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

-R-

re: DB2 crash?!?

Jurgen Haan wrote:[color=blue]
> Mark A wrote:
>[color=green]
>> "Jurgen Haan" <jurgen@fake.dom> wrote in message
>> news:4270ce16$0$160$e4fe514c@news.xs4all.nl...
>>[color=darkred]
>>> Ok... you asked for it....
>>>
>>>[/color]
>>
>> 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.
>>[/color]
>
> Question: why can the DFT_MON_TIMESTAMP parameter be set to OFF?
> What use has it in OFF position?[/color]

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.

re: DB2 crash?!?

"Ian" <ianbjor@mobileaudio.com> wrote in message
news:42727d85_3@newsfeed.slurp.net...[color=blue]
>
> 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.[/color]

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.

re: DB2 crash?!?

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:
[color=blue]
> Mark A wrote:
>[color=green]
>>
>> 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.[/color]
>
>
> 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-
>[/color]

re: DB2 crash?!?

Larry wrote:[color=blue]
> 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
>[/color]

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.

re: DB2 crash?!?

Mark A wrote:[color=blue]
> "Ian" <ianbjor@mobileaudio.com> wrote in message
> news:42727d85_3@newsfeed.slurp.net...
>[color=green]
>>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.[/color]
>
>
> 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.
>
>[/color]

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-