Serge Rielau wrote:
> I'll send you some slides as a PDF. They may give you some ideas.
>
> Cheers
> Serge
Hmm... an invalid gmail address? Well.. ping me if you want slides...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Sorry, but google does not let me use a gmail account for some reason.
But I'm interested in the slides, I'll email you from my business
account.

And indeed it is used as a queue.
Maybe you can explain the following snapshot from the dynamic SQL:
It's a snapshot taken with a 120 seconds interval (after reset):

Number of executions = 13
Number of compilations = 0
Worst preparation time (ms) = 4
Best preparation time (ms) = 1
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
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
Buffer pool index logical reads = 192060
Buffer pool index physical reads = 192041
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 58.367505
Total user cpu time (sec.ms) = 5.380000
Total system cpu time (sec.ms) = 9.150000
Statement text = (SELECT MIN(EVENT_SEQ) + ? FROM
FMC.HS_AUDIT_TRAIL)

The table looks likes this and has > 20 miljoen rows.

------------------------------------------------
-- DDL Statements for table "FMC "."HS_AUDIT_TRAIL"
------------------------------------------------

CREATE TABLE "FMC "."HS_AUDIT_TRAIL" (
"EVENT_SEQ" DECIMAL(16,0) NOT NULL WITH DEFAULT 0 ,
"CREATED" TIMESTAMP ,
"EVENT" INTEGER ,
"TEMPL_VALID_FROM" TIMESTAMP ,
"PROCESS_NAME" VARCHAR(63) ,
"PROCESS_ID" VARCHAR(64) ,
"TOP_LVL_PROC_NAME" VARCHAR(63) ,
"TOP_LVL_PROC_ID" VARCHAR(64) ,
"PARENT_PROC_NAME" VARCHAR(63) ,
"PARENT_PROC_ID" VARCHAR(64) ,
"PROC_TEMPL_NAME" VARCHAR(32) ,
"BLOCK_NAMES" VARCHAR(254) ,
"USER_NAME" VARCHAR(32) ,
"SECOND_USER_NAME" VARCHAR(32) ,
"ACTIVITY_NAME" VARCHAR(32) ,
"ACTIVITY_TYPE" INTEGER ,
"ACTIVITY_STATE" INTEGER ,
"COMMAND_PARAMETERS" VARCHAR(1024) ,
"PROGRAM_NAME" VARCHAR(32) ,
"ACTIVITY_RC" INTEGER ,
"ASSOCIATED_OBJECT" VARCHAR(64) ,
"OBJECT_DESCRIPTION" VARCHAR(254) ,
"SECOND_ACT_NAME" VARCHAR(32) ,
"EXTERNAL_CONTEXT" VARCHAR(254) )
IN "HFX" ;
ALTER TABLE "FMC "."HS_AUDIT_TRAIL" VOLATILE CARDINALITY;

-- DDL Statements for indexes on Table "FMC "."HS_AUDIT_TRAIL"

CREATE INDEX "FMC "."HS_AT_SEQ_INDEX" ON "FMC
"."HS_AUDIT_TRAIL"
("EVENT_SEQ" ASC);


There are no trigger on the table.
After doing a full reorg from the index only the snapshot looked like
this:


Number of executions = 55
Number of compilations = 0
Worst preparation time (ms) = 12
Best preparation time (ms) = 0
Internal rows deleted = 0
Internal rows inserted = 0
Rows read = 0
Internal rows updated = 0
Rows written = 0
Statement sorts = 0
Statement sort overflows = 0
Total sort time = 0
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
Buffer pool index logical reads = 9130
Buffer pool index physical reads = 0
Buffer pool temporary index logical reads = 0
Buffer pool temporary index physical reads = 0
Total execution time (sec.ms) = 0.120709
Total user cpu time (sec.ms) = 0.000000
Total system cpu time (sec.ms) = 0.000000
Statement text = (SELECT MIN(EVENT_SEQ) + ? FROM
FMC.HS_AUDIT_TRAIL)

Even after the reorg each query takes an average of 166 i/o's. Must be
a big index tree.
Don't know how I can find out exactly how big the tree is in size and
levels.

The number of levels is contained in the syscat.indexes tables as
documented in Appendix D fo the SQL Reference Vol 1. This value is
updated after you run runstats command on the index.