Hello people,
Thanks to Serge, PM, Knut and a host of others I have now successfully
completed my very first SQL Stored Procedure. Takes some 13mins or so
to run, but that was expected as it does a fairly significant number
of small single select and inserts from various cursors. I am very
pleased with the results and can find no errors after a few days
debugging a couple of never ending loops.
Interestingly though, while processing it registers virtually nil CPU
usage, somthing in the order of 5-7% at best which confuses me to be
honest. In truth the selects are all very basic, no joins, on small
tables. One cursor has to store 28000 records and a 'while' statement
then loops through those opening 3 other nested 'while' cursors.
I had a problem with the log thrashing but turned that off at the
table via not logging which solved that. Disk activity is fine as I
stood in fornt of the server while it was running and the disks are
only active say every 4 secs or so and only very very slightly at
that. Windows monitor shows nothing excessive, I ran all the monitor
switches and viewed virtually all of the avaible keys via that and can
find nothing that would indicate a problem at all. Indeed there
doesnt appear to be one .. heh
I was just wondering why such a small cpu utilization is all.
Ive pasted the results of the various snapshots .. sorry for the size.
Could some kind soul take a look and see if anything glaring stands
out.
Many thanks,
Tim
I know it is an SMS database, but for now that will have to suffice :)
Database Snapshot
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
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 = Not Collected
Last reset timestamp = Not Collected
Last backup timestamp = Not Collected
Snapshot timestamp = 03-03-2004
15:07:39.408808
High water mark for connections = 3
Application connects = 5
Secondary connects total = 0
Applications connected currently = 1
Appls. executing in db manager currently = 0
Agents associated with applications = 6
Maximum agents associated with applications= 8
Maximum coordinating agents = 3
Locks held currently = 3
Lock waits = 6
Time database waited on locks (ms) = 46284
Lock list memory in use (Bytes) = 4640
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 = 2556
Total sorts = 497348
Total sort time (ms) = 178365
Sort overflows = 0
Active sorts = 0
Buffer pool data logical reads = 8750371
Buffer pool data physical reads = 1
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 1422
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 6892085
Buffer pool index physical reads = 379
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 1533
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
LSN Gap cleaner triggers = 0
Dirty page steal cleaner triggers = 0
Dirty page threshold cleaner triggers = 0
Time waited for prefetch (ms) = 0
Unread prefetch pages = 0
Direct reads = 352
Direct writes = 3584
Direct read requests = 86
Direct write requests = 14
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 24002381
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 = Not Collected
Commit statements attempted = 60
Rollback statements attempted = 1
Dynamic statements attempted = 984930
Static statements attempted = 7371115
Failed statement operations = 1
Select SQL statements executed = 5331310
Update/Insert/Delete statements executed = 2317654
DDL statements executed = 104
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 6
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Rows deleted = 2178099
Rows inserted = 2267288
Rows updated = 0
Rows selected = 8530280
Rows read = 16117740
Binds/precompiles attempted = 0
Log space available to the database (Bytes)= 394402379
Log space used by the database (Bytes) = 13597621
Maximum secondary log space used (Bytes) = 0
Maximum total log space used (Bytes) = 30677107
Secondary logs allocated currently = 0
Log pages read = 0
Log pages written = 3491
Appl id holding the oldest transaction = 11
Package cache lookups = 9096876
Package cache inserts = 10376
Package cache overflows = 0
Package cache high water mark (Bytes) = 4072217
Application section lookups = 10507392
Application section inserts = 52265
Catalog cache lookups = 20580
Catalog cache inserts = 39
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 19215742
Corresponding shared overflows = 0
Total shared section inserts = 5401
Total shared section lookups = 236232
Private high water mark = 1425616
Corresponding private overflows = 0
Total private section inserts = 46864
Total private section lookups = 2295176
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
db2 => GET SNAPSHOT FOR ALL BUFFERPOOLS
Bufferpool Snapshot
Bufferpool name = IBMDEFAULTBP
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Input database alias =
Snapshot timestamp = 03-03-2004
15:10:49.592594
Buffer pool data logical reads = 314
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 488
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 352
Direct writes = 0
Direct read requests = 86
Direct write requests = 0
Direct reads elapsed time (ms) = 0
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
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 3
Alter bufferpool information:
Pages left to remove = 0
Current size = 10000
Post-alter size = 10000
Bufferpool Snapshot
Bufferpool name = RAWDATA
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Input database alias =
Snapshot timestamp = 03-03-2004
15:10:49.592594
Buffer pool data logical reads = 6565153
Buffer pool data physical reads = 1
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 0
Buffer pool index logical reads = 1911061
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 0
Buffer pool index writes = 0
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
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
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 1
Alter bufferpool information:
Pages left to remove = 0
Current size = 10000
Post-alter size = 10000
Bufferpool Snapshot
Bufferpool name = RESULTS
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Input database alias =
Snapshot timestamp = 03-03-2004
15:10:49.592594
Buffer pool data logical reads = 2184904
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 1422
Buffer pool index logical reads = 4980536
Buffer pool index physical reads = 379
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Asynchronous pool data page reads = 0
Asynchronous pool data page writes = 0
Buffer pool index writes = 1533
Asynchronous pool index page reads = 0
Asynchronous pool index page writes = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 3584
Direct read requests = 0
Direct write requests = 14
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 24002381
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
Unread prefetch pages = 0
Vectored IOs = 0
Pages from vectored IOs = 0
Block IOs = 0
Pages from block IOs = 0
Physical page maps = 0
Node number = 0
Tablespaces using bufferpool = 1
Alter bufferpool information:
Pages left to remove = 0
Current size = 25000
Post-alter size = 25000
db2 => GET SNAPSHOT FOR APPLICATIONS ON DBNAME
Application Snapshot
Application handle = 11
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Application country/region code = 1
DUOW correlation token = *LOCAL.DB2.0115C3193403
Application name = db2bp.exe
Application ID = *LOCAL.DB2.0115C3193403
Sequence number = 0055
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = Not Collected
Connect request completion timestamp = Not Collected
Application idle time = 0
CONNECT Authorization ID = DB2ADMIN
Client login ID = DB2ADMIN
Configuration NNAME of client =
Client database manager product ID = SQL08014
Process ID of client application = 1772
Platform of client application = NT
Communication protocol of client = Local Client
Inbound communication address = *LOCAL.DB2
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Client database alias = DBNAME
Input database alias = DBNAME
Last reset timestamp = Not Collected
Snapshot timestamp = 03-03-2004
15:16:52.675489
The highest authority level granted =
Direct DBADM authority
Direct CREATETAB authority
Direct BINDADD authority
Direct CONNECT authority
Direct CREATE_NOT_FENC authority
Direct LOAD authority
Direct IMPLICIT_SCHEMA authority
Direct CREATE_EXT_RT authority
Direct QUIESCE_CONN authority
Indirect SYSADM authority
Indirect DBADM authority
Indirect CREATETAB authority
Indirect BINDADD authority
Indirect CONNECT authority
Indirect CREATE_NOT_FENC authority
Indirect IMPLICIT_SCHEMA authority
Indirect LOAD authority
Indirect CREATE_EXT_RT authority
Indirect QUIESCE_CONN authority
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 1696
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 7
Priority at which application agents work = 0
Priority type = Dynamic
Locks held by application = 2
Lock waits since connect = 3
Time application waited on locks (ms) = 1
Deadlocks detected = 0
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = 0
Total sorts = 497340
Total sort time (ms) = 178365
Total sort overflows = 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
Buffer pool data logical reads = 8750371
Buffer pool data physical reads = 1
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool data writes = 1422
Buffer pool index logical reads = 6892085
Buffer pool index physical reads = 379
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Buffer pool index writes = 1533
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Time waited for prefetch (ms) = 0
Unread prefetch pages = 0
Direct reads = 352
Direct writes = 3584
Direct read requests = 86
Direct write requests = 14
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 24002381
Number of SQL requests since last commit = 0
Commit statements = 54
Rollback statements = 0
Dynamic SQL statements attempted = 984906
Static SQL statements attempted = 7371108
Failed statement operations = 1
Select SQL statements executed = 5331303
Update/Insert/Delete statements executed = 2317654
DDL statements executed = 104
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 2178099
Rows inserted = 2267288
Rows updated = 0
Rows selected = 8525127
Rows read = 16111061
Rows written = 4445387
UOW log space used (Bytes) = 302
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status =
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 378627
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = Not Collected
Total System CPU Time used by agent (s) = Not Collected
Host execution elapsed time = Not Collected
Package cache lookups = 9096865
Package cache inserts = 10371
Application section lookups = 10507354
Application section inserts = 52250
Catalog cache lookups = 20549
Catalog cache inserts = 25
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 19215742
Total shared overflows = 0
Total shared section inserts = 5397
Total shared section lookups = 236228
Private high water mark = 1425616
Total private overflows = 0
Total private section inserts = 46853
Total private section lookups = 2295159
Most recent operation = Execute
Most recent operation start timestamp = Not Collected
Most recent operation stop timestamp = Not Collected
Agents associated with the application = 6
Number of hash joins = 0
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Statement type = Dynamic SQL Statement
Statement = Execute
Section number = 1
Application creator = NULLID
Package name = SQLC2E03
Consistency Token = AAAAAJHR
Package Version ID =
Cursor name = SQLCUR1
Statement database partition number = 0
Statement start timestamp = Not Collected
Statement stop timestamp = Not Collected
Elapsed time of last completed stmt(sec.ms)= Not Collected
Total user CPU time = Not Collected
Total system CPU time = Not Collected
SQL compiler cost estimate in timerons = 1
SQL compiler cardinality estimate = 1
Degree of parallelism requested = 1
Number of agents working on statement = 1
Number of subagents created for statement = 1
Statement sorts = 0
Total sort time = 0
Sort overflows = 0
Rows read = 0
Rows written = 0
Rows deleted = 0
Rows updated = 0
Rows inserted = 0
Rows fetched = 0
Buffer pool data logical reads = 2
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Buffer pool index logical reads = 2
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Blocking cursor = NO
Dynamic SQL statement text:
CALL ASSC_RULE_TO_SUBJECT()
Memory usage for application:
Database partition number = 0
Agent process/thread ID = 1696
Memory Pool Type = Application Heap
Current size (bytes) = 1785856
High water mark (bytes) = 1851392
Maximum size allowed (bytes) = 2326528
Database partition number = 0
Agent process/thread ID = 2784
Memory Pool Type = Application Heap
Current size (bytes) = 1900544
High water mark (bytes) = 1900544
Maximum size allowed (bytes) = 2326528
Database partition number = 0
Agent process/thread ID = 2332
Memory Pool Type = Application Heap
Current size (bytes) = 1900544
High water mark (bytes) = 1966080
Maximum size allowed (bytes) = 2326528
Database partition number = 0
Agent process/thread ID = 2712
Memory Pool Type = Application Heap
Current size (bytes) = 1900544
High water mark (bytes) = 1966080
Maximum size allowed (bytes) = 2326528
db2 =>
db2 => GET SNAPSHOT FOR TABLES ON DBNAME
Table Snapshot
First database connect timestamp = Not Collected
Last reset timestamp = Not Collected
Snapshot timestamp = 03-03-2004 15:22:00.473889
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Input database alias = DBNAME
Number of accessed tables = 28
Table List
Table Schema = SYSIBM
Table Name = SYSSECTION
Table Type = Catalog
Rows Read = 10
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_01
Table Type = User
Rows Read = 244590
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_15
Table Type = User
Rows Read = 22380
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_06
Table Type = User
Rows Read = 94920
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_09
Table Type = User
Rows Read = 103560
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = SYSIBM
Table Name = SYSTABLESPACES
Table Type = Catalog
Rows Read = 152
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = SYSIBM
Table Name = SYSTABLES
Table Type = Catalog
Rows Read = 152
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_17
Table Type = User
Rows Read = 370080
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_04
Table Type = User
Rows Read = 88710
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_18
Table Type = User
Rows Read = 496200
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_05
Table Type = User
Rows Read = 359460
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_22
Table Type = User
Rows Read = 412380
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_10
Table Type = User
Rows Read = 128580
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_21
Table Type = User
Rows Read = 175320
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_11
Table Type = User
Rows Read = 313680
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_07
Table Type = User
Rows Read = 767220
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_08
Table Type = User
Rows Read = 905100
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_20
Table Type = User
Rows Read = 149760
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_19
Table Type = User
Rows Read = 505140
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_14
Table Type = User
Rows Read = 219900
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_13
Table Type = User
Rows Read = 294120
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_12
Table Type = User
Rows Read = 337260
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = CHRM_02
Table Type = User
Rows Read = 291690
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = DBNAME_MARKERS
Table Type = User
Rows Read = 211822
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = DBNAME_MARKER_DES
Table Type = User
Rows Read = 211822
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = SIG_RULE_CONDTNS
Table Type = User
Rows Read = 543084
Rows Written = 0
Overflows = 0
Page Reorgs = 0
Table Schema = DB2ADMIN
Table Name = SIG_ASSC_RULE_SUBJECT
Table Type = User
Rows Read = 93873
Rows Written = 231458
Overflows = 0
Page Reorgs = 421
Table Schema = DB2ADMIN
Table Name = SIG_TMP_SUBJECT_ID
Table Type = User
Rows Read = 4646198
Rows Written = 3113710
Overflows = 0
Page Reorgs = 15556
db2 =>
db2 => GET SNAPSHOT FOR TABLESPACES ON DBNAME
Tablespace Snapshot
First database connect timestamp = Not Collected
Last reset timestamp = Not Collected
Snapshot timestamp = 03-03-2004
15:23:11.370381
Database name = DBNAME
Database path = D:\DB2\NODE0000\SQL00003\
Input database alias = DBNAME
Number of accessed tablespaces = 5
Tablespace name = SYSCATSPACE
Tablespace ID = 0
Tablespace Type = System managed space
Tablespace Content Type = Any data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Tablespace Prefetch size (pages) = 32
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 18049
Number of usable pages = 18049
Number of used pages = 18049
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name =
D:\DB2\CONTAINERS\DBNAME\CATALOGS
Container ID = 0
Container Type = Path
Total Pages in Container = 18049
Usable Pages in Container = 18049
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = 314
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 0
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 488
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 352
Direct writes = 0
Direct read requests = 86
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of 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
Tablespace name = TEMPSPACE1
Tablespace ID = 1
Tablespace Type = System managed space
Tablespace Content Type = System Temporary data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 32
Tablespace Prefetch size (pages) = 32
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 1
Number of usable pages = 1
Number of used pages = 1
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name =
D:\DB2\CONTAINERS\DBNAME\USER\TEMP
ORARY
Container ID = 0
Container Type = Path
Total Pages in Container = 1
Usable Pages in Container = 1
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 0
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of 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
Tablespace name = USERSPACE1
Tablespace ID = 2
Tablespace Type = System managed space
Tablespace Content Type = Any data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 64
Tablespace Prefetch size (pages) = 64
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 1
Number of usable pages = 1
Number of used pages = 1
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name =
D:\DB2\CONTAINERS\DBNAME\USER\TABL
ESPACES
Container ID = 0
Container Type = Path
Total Pages in Container = 1
Usable Pages in Container = 1
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = 0
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 0
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 0
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of 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
Tablespace name = RESULTS
Tablespace ID = 3
Tablespace Type = System managed space
Tablespace Content Type = Any data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 64
Tablespace Prefetch size (pages) = 128
Buffer pool ID currently in use = 3
Buffer pool ID next startup = 3
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 5027
Number of usable pages = 5027
Number of used pages = 5027
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name =
D:\DB2\CONTAINERS\DBNAME\TABLESPAC
ES\RESULTS
Container ID = 0
Container Type = Path
Total Pages in Container = 5027
Usable Pages in Container = 5027
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = 2184904
Buffer pool data physical reads = 0
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 1422
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 4980536
Buffer pool index physical reads = 379
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 1533
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 3584
Direct read requests = 0
Direct write requests = 14
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 24002381
Number of 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
Tablespace name = RAWDATA
Tablespace ID = 4
Tablespace Type = System managed space
Tablespace Content Type = Any data
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 64
Tablespace Prefetch size (pages) = 128
Buffer pool ID currently in use = 2
Buffer pool ID next startup = 2
Tablespace State = 0x'00000000'
Detailed explanation:
Normal
Total number of pages = 1522
Number of usable pages = 1522
Number of used pages = 1522
Minimum Recovery Time =
Number of quiescers = 0
Number of containers = 1
Container Name =
D:\DB2\CONTAINERS\DBNAME\TABLESPAC
ES\RAWDATA
Container ID = 0
Container Type = Path
Total Pages in Container = 1522
Usable Pages in Container = 1522
Stripe Set = 0
Container is accessible = Yes
Buffer pool data logical reads = 6565153
Buffer pool data physical reads = 1
Buffer pool temporary data logical reads = 0
Buffer pool temporary data physical reads = 0
Asynchronous pool data page reads = 0
Buffer pool data writes = 0
Asynchronous pool data page writes = 0
Buffer pool index logical reads = 1911061
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Asynchronous pool index page reads = 0
Buffer pool index writes = 0
Asynchronous pool index page writes = 0
Total buffer pool read time (ms) = 0
Total buffer pool write time (ms) = 0
Total elapsed asynchronous read time = 0
Total elapsed asynchronous write time = 0
Asynchronous data read requests = 0
Asynchronous index read requests = 0
No victim buffers available = 0
Direct reads = 0
Direct writes = 0
Direct read requests = 0
Direct write requests = 0
Direct reads elapsed time (ms) = 0
Direct write elapsed time (ms) = 0
Number of 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
db2 =>