Dear member,
I've tested some queries with release DB2 11.1 of the EAP progam.
We upload the testresults in tree uploads: (upload 1/2, upload2/2, upload 2/2a)
We had the following main issues with release 10.5.5.
- problems with queries (joins) on large tables regarding the sort heap memory pools.
- the absence of creating IDENTITY columns in column organized tables
The global results of the tests :
- column organized tables with IDENTITY-columns are possible in DB2 11.1 and all functions, including
"restart ID" are working well.
- the sort memory problems still exists.
I've tested 4 queries, that caused problems in 10.5 with sortheap memory
The same problems (memory overflow on the sortheap memory) occurred in DB2 11.1.
The four queries are tested each with three different sortheap parameters (1.6 Gb, 3.2 Gb and 6.4 GB),
all with a sortheap shared parameter of 32 Gb.
Finally all the tests are running well with a sortheap of 6.4 Gb, but then we can only execute 5 (sort)-queries
in parallel. and no guarantees that other queries are running with that parameter !!
Finally our "wish-list" for column organized tables :
- online schema changes :
- only "alter table add column" clause possible
- no datatype changes, etc. (as in DB2 10.5)
- no LONG datatypes (BLOB, CLOB and LONG VARCHAR)
- no PARTITIONED tables (query data-independence, detach/attach partitions)
- no classic reorg (clustering/ordering the synopsys table ??)
Below a schema of the tested queries, they all ran with a fresh activated database.
These are the four queries, each tested with three different sortheap values and all with a sortheap shared of 32 Gb.
|
|
|
|
|
|
|
|
QUERY
|
ENDED
|
MAX_SORT_CONSUMER_SORTHEAP_TOP
|
MAX_SORT_MEMORY_USED
|
MAX_SORT_SHRHEAP
|
SORTHEAP
|
|
Q11
|
ABEND SORT_MEMORY
|
1600
|
2798
|
2637
|
1600
|
|
|
Q12
|
ABEND SORT_MEMORY
|
3200
|
4701
|
5046
|
3200
|
|
|
Q13
|
SUCCEEDED
|
4483
|
6246
|
6400
|
6400
|
|
|
Q21
|
SUCCEEDED
|
1600
|
4812
|
5118
|
1600
|
|
|
Q22
|
SUCCEEDED
|
3200
|
7851
|
9906
|
3200
|
|
|
Q23
|
SUCCEEDED
|
6400
|
12483
|
19574
|
6400
|
|
|
Q31
|
ABEND SORT_MEMORY
|
1600
|
4641
|
5438
|
1600
|
|
|
Q32
|
ABEND SORT_MEMORY
|
3200
|
8038
|
7596
|
3200
|
|
|
Q33
|
SUCCEEDED
|
4589
|
9445
|
8964
|
6400
|
|
|
Q41
|
SUCCEEDED
|
1600
|
7645
|
4401
|
1600
|
|
|
Q42
|
SUCCEEDED
|
533
|
750
|
3200
|
3200
|
|
|
Q43
|
SUCCEEDED
|
1066
|
746
|
6400
|
6400
|
|
| |
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
|
|
|
|
|
During the tests I've inserted each 2 or 3 seconds a record in a table with the actual sort-parameter values.
The results of the 12 tests, including db2diag.log messages in case of abends, are saved and the actual sort values
during the tests are saved in an Excell sheet.
I'll send all the results in appendixes
Best regards,
Jan Bouwmeester
DBA Achmea The Netherlands
#Db2#Db2EarlyAccessProgram(EAP)Forum