Hello,
I am trying to isolate some performance issues.
The database is DB2 v8.1 running on a Win2000 server with 4 processors and
1gb of RAM.
The application is a GIS application that uses ADO to access the database.
It was not written specifically for DB2 and I think almost all queries are
running as dynamic queries. The performance issue arise when users are
updating data. There may be up to 8 users on the system. After running for a
couple of hours, some users (not all) will experience a really long wait
time (~15 minutes!) before insert/updates/deletes are posted. I assumed this
was some sort of locking issue but looking at the snapshot data I can not
see the problem. However, I am new to DB2 and we do not have an experienced
DBA available so I am sure I am missing something. The database manager and
database parameters are set at the defaults except for:
Audit_Buf_Sz = 1000
Avg_Appls = 15
ApplHeapSz = 20480
CLI settings have autocommit turned off.
Cursors are server side.
These setting changes were uneducated guesses at fixing the problem.
The snapshot is below. Any advice would be greatly appreciated.
Database Snapshot
Database name = UMSII
Database path = C:\DB2\NODE0000 \SQL00002\
Input database alias =
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= NT
Location of the database = Local
First database connect timestamp = 01-19-2004 07:08:52.132012
Last reset timestamp =
Last backup timestamp =
Snapshot timestamp = 01-19-2004 09:35:36.998705
High water mark for connections = 15
Application connects = 34
Secondary connects total = 0
Applications connected currently = 15
Appls. executing in db manager currently = 0
Agents associated with applications = 15
Maximum agents associated with applications= 15
Maximum coordinating agents = 15
Locks held currently = 39
Lock waits = 0
Time database waited on locks (ms) = 0
Lock list memory in use (Bytes) = 14000
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Total sorts = 301
Total sort time (ms) = 0
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = 12569
Buffer pool data physical reads = 7483
Buffer pool temporary data logical reads = 12479
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 7247
Buffer pool data writes = 52
Asynchronous pool data page writes = 45
Buffer pool index logical reads = 9386
Buffer pool index physical reads = 320
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 37
Asynchronous pool index page writes = 36
Total buffer pool read time (ms) = 116
Total buffer pool write time (ms) = 116
Total elapsed asynchronous read time = 48
Total elapsed asynchronous write time = 116
Asynchronous data read requests = 907
Asynchronous index read requests = 0
No victim buffers available = 412
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 16
Dirty page threshold cleaner triggers = 0
Time waited for prefetch (ms) = 1
Unread prefetch pages = 0
Direct reads = 1444
Direct writes = 0
Direct read requests = 641
Direct write requests = 0
Direct reads elapsed time (ms) = 38
Direct write elapsed time (ms) = 0
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Host execution elapsed time = 0.000956
Commit statements attempted = 588
Rollback statements attempted = 13
Dynamic statements attempted = 14591
Static statements attempted = 639
Failed statement operations = 2
Select SQL statements executed = 974
Update/Insert/Delete statements executed = 251
DDL statements executed = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 34
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Rows deleted = 63
Rows inserted = 140
Rows updated = 13
Rows selected = 31268
Rows read = 334580
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 203938034
Log space used by the database (Bytes) = 61966
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 62271
Secondary logs allocated currently = 0
Log pages read = 0
Log pages written = 97
Appl id holding the oldest transaction = 236
Package cache lookups = 1294
Package cache inserts = 462
Package cache overflows = 0
Package cache high water mark (Bytes) = 1593696
Application section lookups = 14629
Application section inserts = 617
Catalog cache lookups = 1332
Catalog cache inserts = 51
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Corresponding shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 261630
Corresponding private overflows = 0
Total private section inserts = 617
Total private section lookups = 1050
Number of hash joins = 1
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
I am trying to isolate some performance issues.
The database is DB2 v8.1 running on a Win2000 server with 4 processors and
1gb of RAM.
The application is a GIS application that uses ADO to access the database.
It was not written specifically for DB2 and I think almost all queries are
running as dynamic queries. The performance issue arise when users are
updating data. There may be up to 8 users on the system. After running for a
couple of hours, some users (not all) will experience a really long wait
time (~15 minutes!) before insert/updates/deletes are posted. I assumed this
was some sort of locking issue but looking at the snapshot data I can not
see the problem. However, I am new to DB2 and we do not have an experienced
DBA available so I am sure I am missing something. The database manager and
database parameters are set at the defaults except for:
Audit_Buf_Sz = 1000
Avg_Appls = 15
ApplHeapSz = 20480
CLI settings have autocommit turned off.
Cursors are server side.
These setting changes were uneducated guesses at fixing the problem.
The snapshot is below. Any advice would be greatly appreciated.
Database Snapshot
Database name = UMSII
Database path = C:\DB2\NODE0000 \SQL00002\
Input database alias =
Database status = Active
Catalog database partition number = 0
Catalog network node name =
Operating system running at database server= NT
Location of the database = Local
First database connect timestamp = 01-19-2004 07:08:52.132012
Last reset timestamp =
Last backup timestamp =
Snapshot timestamp = 01-19-2004 09:35:36.998705
High water mark for connections = 15
Application connects = 34
Secondary connects total = 0
Applications connected currently = 15
Appls. executing in db manager currently = 0
Agents associated with applications = 15
Maximum agents associated with applications= 15
Maximum coordinating agents = 15
Locks held currently = 39
Lock waits = 0
Time database waited on locks (ms) = 0
Lock list memory in use (Bytes) = 14000
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Agents currently waiting on locks = 0
Lock Timeouts = 0
Number of indoubt transactions = 0
Total Private Sort heap allocated = 0
Total Shared Sort heap allocated = 0
Shared Sort heap high water mark = 0
Total sorts = 301
Total sort time (ms) = 0
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = 12569
Buffer pool data physical reads = 7483
Buffer pool temporary data logical reads = 12479
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 7247
Buffer pool data writes = 52
Asynchronous pool data page writes = 45
Buffer pool index logical reads = 9386
Buffer pool index physical reads = 320
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 37
Asynchronous pool index page writes = 36
Total buffer pool read time (ms) = 116
Total buffer pool write time (ms) = 116
Total elapsed asynchronous read time = 48
Total elapsed asynchronous write time = 116
Asynchronous data read requests = 907
Asynchronous index read requests = 0
No victim buffers available = 412
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 16
Dirty page threshold cleaner triggers = 0
Time waited for prefetch (ms) = 1
Unread prefetch pages = 0
Direct reads = 1444
Direct writes = 0
Direct read requests = 641
Direct write requests = 0
Direct reads elapsed time (ms) = 38
Direct write elapsed time (ms) = 0
Database files closed = 0
Data pages copied to extended storage = 0
Index pages copied to extended storage = 0
Data pages copied from extended storage = 0
Index pages copied from extended storage = 0
Host execution elapsed time = 0.000956
Commit statements attempted = 588
Rollback statements attempted = 13
Dynamic statements attempted = 14591
Static statements attempted = 639
Failed statement operations = 2
Select SQL statements executed = 974
Update/Insert/Delete statements executed = 251
DDL statements executed = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 34
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Rows deleted = 63
Rows inserted = 140
Rows updated = 13
Rows selected = 31268
Rows read = 334580
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 203938034
Log space used by the database (Bytes) = 61966
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 62271
Secondary logs allocated currently = 0
Log pages read = 0
Log pages written = 97
Appl id holding the oldest transaction = 236
Package cache lookups = 1294
Package cache inserts = 462
Package cache overflows = 0
Package cache high water mark (Bytes) = 1593696
Application section lookups = 14629
Application section inserts = 617
Catalog cache lookups = 1332
Catalog cache inserts = 51
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Corresponding shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 261630
Corresponding private overflows = 0
Total private section inserts = 617
Total private section lookups = 1050
Number of hash joins = 1
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Comment