Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

Please DB2_SELECTIVITY=MODEL_JOIN_CARD ON

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

thank you,

is it compatible with db2set -im or i need to restart the instance ?

Best regards,

Please DB2_SELECTIVITY=MODEL_JOIN_CARD ON

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

Hello,

Using models for equality joins is on by default in 12.1.3, so "MODEL_JOIN_CARD ON" is the default behaviour, and to disable, use "MODEL_JOIN_CARD OFF". So, by default, as long as the AI Query Optimizer is enabled in 12.1.3, the models will be considered for equality joins. If you're not sure if it's already on or whether you have trained models for the tables involved in the join, you can check out the following blog from my colleague Brandon with lots of great details:

https://community.ibm.com/community/user/blogs/brandon-frendo/2024/12/10/simplify-query-perf-tuning-db2-ai-query-optimizer

Hope this helps!

Liam.

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

Hello, thank you Liam for theses explication. 

did MODEL_JOIN_CARD is considered for multi equality joins ?  select * from tab1 a join tab2 b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 ?

in explain plan how can i find that MODEL_JOIN_CARD had been used ?

we are running you dataware preprod on 12.1.2 for 3 month now, upgraded it 12.1.3 last Weekend.

we are planning to upgrade our prod from 11.5.9 to 12.1.3 at then end of this month. i'm making the final test to be sur that there is regression.

by the way for my point of view the killer functionally of the 12.1 is the new memory management in hash joins, we saw lot of workload improvement. if MODEL_JOIN_CARD support multi-equlity joins i guess that it will improve many use case in our workload.

if i may, it will be good if in the next updated, you add the ability to trigger MODEL creation or update by runstats and add the possibiliy to create many model in parallel

Best regards,

NB: 4TB database with 100% BLU tables

Hello,

Using models for equality joins is on by default in 12.1.3, so "MODEL_JOIN_CARD ON" is the default behaviour, and to disable, use "MODEL_JOIN_CARD OFF". So, by default, as long as the AI Query Optimizer is enabled in 12.1.3, the models will be considered for equality joins. If you're not sure if it's already on or whether you have trained models for the tables involved in the join, you can check out the following blog from my colleague Brandon with lots of great details:

https://community.ibm.com/community/user/blogs/brandon-frendo/2024/12/10/simplify-query-perf-tuning-db2-ai-query-optimizer

Hope this helps!

Liam.

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,

Hi Malek,

If there are multiple equality joins in the statement, each pair will be separately considered for AI join cardinality estimation. For the particular query you show (two tables, multiple columns from only those 2 tables being joined - as opposed to columns being joined from multiple different tables), we are also exploring further improvements for upcoming releases (e.g. rather than just treating each join as independent, to also intelligently determine correlation information from other join columns in the same table). The current models and functionality in 12.1.3 give the most potential benefit when you also have one or more local predicates on at least one of the tables being joined - since then they can more accurately determine the impact of the local predicates on the join column distributions.

For how to see the impact from using the models for join cardinality estimation, the only way right now is to run the query with and without MODEL_JOIN_CARD, and compare the plans of each. Brandon's blog shows how this approach can be done using per-query optimizer hints, using DB2_SELECTIVITY=MODEL_PRED_SEL ON or OFF, that exact same approach can be used except with MODEL_JOIN_CARD set to ON or OFF (instead of MODEL_PRED_SEL, which only deals with using the model for multiple local predicates). We are looking at how we might be able to show the influence of AI Query Optimizer join cardinality estimates in the explain output for future releases.

We're also currently putting the finishing touches on a technical blog (to be released shortly) that also highlights some performance results we saw in-house when we enabled the AI Query Optimizer for join cardinality - and hopefully this feature can enable strong improvements in your workloads as well.

And finally, your request to have a way to manually trigger model creation is probably the most-requested single enhancement we've received, and we are looking at how to support this. We are considering a separate DDL statement, something like 'TRAIN SYSTEM MODEL ...' that can allow you to specify which table to train a model on, as well as optionally which columns are the most important to include in the model.

I look forward to hearing about your experiences enabling AI join cardinality in your workload!

Best regards,

Liam.

Hello, thank you Liam for theses explication. 

did MODEL_JOIN_CARD is considered for multi equality joins ?  select * from tab1 a join tab2 b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 ?

in explain plan how can i find that MODEL_JOIN_CARD had been used ?

we are running you dataware preprod on 12.1.2 for 3 month now, upgraded it 12.1.3 last Weekend.

we are planning to upgrade our prod from 11.5.9 to 12.1.3 at then end of this month. i'm making the final test to be sur that there is regression.

by the way for my point of view the killer functionally of the 12.1 is the new memory management in hash joins, we saw lot of workload improvement. if MODEL_JOIN_CARD support multi-equlity joins i guess that it will improve many use case in our workload.

if i may, it will be good if in the next updated, you add the ability to trigger MODEL creation or update by runstats and add the possibiliy to create many model in parallel

Best regards,

NB: 4TB database with 100% BLU tables

Hello,

Using models for equality joins is on by default in 12.1.3, so "MODEL_JOIN_CARD ON" is the default behaviour, and to disable, use "MODEL_JOIN_CARD OFF". So, by default, as long as the AI Query Optimizer is enabled in 12.1.3, the models will be considered for equality joins. If you're not sure if it's already on or whether you have trained models for the tables involved in the join, you can check out the following blog from my colleague Brandon with lots of great details:

https://community.ibm.com/community/user/blogs/brandon-frendo/2024/12/10/simplify-query-perf-tuning-db2-ai-query-optimizer

Hope this helps!

Liam.

Hello, 

DB2 12.1.3 support cardinality models for join predicate selectivity estimation (Changed functionality in Db2 12.1.3)  the documentation dont say if we need to put:

DB2_SELECTIVITY="MODEL_JOIN_CARD" or DB2_SELECTIVITY="MODEL_JOIN_CARD=YES"

i'am looking for the limit of MODEL_JOIN_CARD.

Best regards,