Db2

Db2

Where DBAs and data experts come together to stop operating and start innovating. Connect, share, and shape the AI era with us.


#Data


#Data
 View Only

  • 1.  Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

    Posted Mon January 26, 2026 12:28 PM
    Edited by Kat Jarvis Tue February 03, 2026 02:37 PM

    Join us for an insightful discussion on 27 January, 2025 at 11:00 AM ET. This webinar presents IBM Db2's latest advancement in AI-powered query optimization, introducing AI powered join cardinality estimation, new in version 12.1.3. Building upon the Single Table Cardinality Model released in Db2 12.1, this enhancement addresses critical limitations in traditional join cardinality estimation by leveraging machine learning to understand complex relationships between local and join predicates across tables.


    Key features and benefits include:
    - Reliable performance: some queries execute up to 10 times faster
    - Minimal Resource Consumption: reuse of existing models so that no new models required
    - Automation: reduces query tuning and eliminates the need for statistical views


    In this webinar we will discuss in detail:
    - The importance and challenges of cardinality estimation
    - How Db2 uses neural networks to improve join cardinality estimation
    - Our internal performance test results on TPC-DS and SAP BW workloads
    - How better cardinality estimates, result in improved access plans and superior performance without any tuning required.

     

    Click here to register and add the event to your calendar.

      Where is the link to join? Check your email the day before the event!

       How to get the most out of this webinar:

      • Explore the Db2 Community.

      • Ask questions for the presenters by replying to this discussion post before, during and after the webinar. Lori Strain, Nick Ostan, and Lakshmi Tirunelveli Radhakrishnan will be happy to answer your questions and share insights.

      • Check out upcoming Db2 events here.


      We look forward to seeing you there!



      ------------------------------
      Db2 Community Admin
      ------------------------------



    • 2.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Wed January 28, 2026 08:32 AM

      Hi

      I asked a question yesterday about quantifying the results of the AI Optimiser. I thought that I could do some comparative examination of the access paths by switching off the AI optimiser but it looks as if I have misinterpreted the options. What I am doing is 

      1. extracting the contents of the package cache using db2advis -d dbname -q schema -g -p –noxml
      2. executing db2exfmt against the extracted workload using db2exfmt -d dbname -g TIC -w 2026-01-28% -n % -s % -# 0 -o Workload_2026-01-28_AIOPT_on.db2exfmt
      3. looking in that output I can see many references to "Table Cardinality Model Predicates:"
      4. I then try and switch off the AI Optimiser using UPDATE DATABASE CONFIGURATION FOR AWSSIT USING AUTO_AI_OPTIMIZER OFF;
        UPDATE DATABASE CONFIGURATION FOR AWSSIT USING AUTO_MODEL_DISCOVER OFF;
      5. and re-run the db2exfmt command

      However, there are still references to the Table Cardinality Model Predicates so the existing models are still being evaluated. 

      How should I 'switch off' the use of the existing AI models in order to do this comparative check on traditional and AI cardinality evaluation?

      Regards

      Mark Gillis



      ------------------------------
      Mark Gillis
      Principal Consultant
      Triton Consulting
      ------------------------------

      Original Message


    • 3.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Wed January 28, 2026 09:37 AM

      Hi Mark,

      Perhaps disabling the models will help.

      Simplify Query Performance Tuning with the Db2 AI Query Optimizer - International Db2 Users Group

      There are two ways to disable a model. One way is by providing the name of the model to disable, 
      and the second way is to use the name of the associated table. For example, here are the two ways to disable the model for the EMPLOYEE table:
      ALTER MODEL SYSIBM.SQL241021170053621900 DISABLE;
      ALTER MODEL ON SAMPLE.EMPLOYEE DISABLE;
       
      Of course, in order to use ALTER MODEL, the model must already exist, so disabling the model in this way is a measure that can be useful in situations where an existing model needs to stop being used for all queries that involve the associated table.
      You can see which models are enabled and disabled by running the following query on the SYSCAT.AIOPT_TABLECARDMODELS catalog view:
      SELECT
        VARCHAR(MODELSCHEMA, 25) AS MODELSCHEMA,
        VARCHAR(MODELNAME, 25) AS MODELNAME,
        VARCHAR(TABSCHEMA, 25) AS TABSCHEMA,
        VARCHAR(TABNAME, 25) AS TABNAME,
        ISENABLED
      FROM
        SYSCAT.AIOPT_TABLECARDMODELS
      WHERE
        TABSCHEMA = 'SAMPLE' AND
        TABNAME IN ('EMPLOYEE', 'PROJECT');
      The ISENABLED column is 0 when the model is disabled and 1 when the model is enabled:
       
      To re-enable the model, the same DDL statements can be executed above by replacing the DISABLE clause with ENABLE:
      ALTER MODEL SYSIBM.SQL241021170053621900 ENABLE;
      ALTER MODEL ON SAMPLE.EMPLOYEE ENABLE;
      Best regards, Joachim



      ------------------------------
      Joachim Müller
      ------------------------------

      Original Message


    • 4.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Wed January 28, 2026 10:13 AM

      Hi Mark, Thank you for your questions.  

      The easiest way to disable the AI query optimizer is to use:

      db2set db2_selectivity='MODEL_PRED_SEL OFF'.  If you use the immediate option, you do not need to recycle the instance.  You can also specify the environment variable setting using a optimizer guideline.



      ------------------------------
      Lori Strain
      Db2 Query Compiler Developer
      IBM
      ------------------------------

      Original Message


    • 5.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Wed January 28, 2026 11:16 AM

      Thanks Joachim and Lori. I think the registry variable is the best option as I have hundreds of models now. 

      However I think I have still got some work to do here. My intention was to use db2advis to capture an entire workload and then to run it through db2exfmt with AIOPT enabled, and then switch AIOPT off and run db2exfmt again.

      BUT I think that running db2advis does the evaluation of the access path and db2exfmt merely formats it. So both my db2exfmt executions show AIOPT being used.

      The way around this is to rerun db2advis after AIOPT is switched off but that means you may (and probably will) pick up a different set of queries. I'm still experimenting to see if there is some way to create a 'baseline' workload so that I can push the same queries through the database with AIOPT off and on, and then compare to find differences. If you (or anyone) has any theories on how that might be accomplished, please let me know. Otherwise I will keep digging to see if there is a process that will do what I'm after.

      Regards

      Mark



      ------------------------------
      Mark Gillis
      Principal Consultant
      Triton Consulting
      ------------------------------

      Original Message


    • 6.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Fri January 30, 2026 12:27 PM

      Hi Mark,

      You are correct db2exfmt does not actually capture explains.  It merely formats them.  However, you don't need db2advis to capture the explains.  You can simply issue the command "set current explain mode explain" (https://www.ibm.com/docs/en/db2/11.5.x?topic=statements-set-current-explain-mode) and then execute a list of queries.  Explain mode explain captures the explains without actually executing the queries.



      ------------------------------
      Lori Strain
      Db2 Query Compiler Developer
      IBM
      ------------------------------

      Original Message


    • 7.  RE: Webinar: Experience the Power and Simplicity of the AI Query Optimizer in Db2 12.1.3

      Posted Mon February 02, 2026 05:42 AM

      Hi Lori

      yes, the db2advis isn't really to do with the explains; I need it to capture the current workload from the Package Cache. The customer isn't able to share a definitive set of queries representing their workload, so the best I can do is to capture what is resident in the cache after a 'typical' day and then use that as input. So, the set of steps I have is

      • Extract workload from package cache                
        db2advis -d AWSSIT -q PNET -g -p -noxml 
      • extract SQL from ADVISE_WORKLOAD

      export to Workload_2026-01-29.sql of DEL modified BY NOCHARDEL 
      SELECT RTRIM(STATEMENT_TEXT) CONCAT ';'    SQL
      FROM ADVISE_WORKLOAD
      WHERE workload_name = (select MAX(workload_name) From ADVISE_WORKLOAD )
      AND    COST_BEFORE > 0

      • run SQL in explain mode and capture access paths

      db2 set current explain mode explain
      db2 -tvf  Workload_2026-01-29.sql
      db2 set current explain mode no

      db2exfmt -d AWSSIT -g TIC -w 2026-01-30-11.07% -n % -s % -# 0 -o Workload_2026-01-30_AIOPT_on.db2exfmt
      grep Model Workload_2026-01-30_AIOPT_on.db2exfmt

      • switch AIOPT off

      db2set db2_selectivity='MODEL_PRED_SEL OFF' -immediate

      • repeat above test

      db2 set current explain mode explain
      db2 -tvf  Workload_2026-01-29.sql
      db2 set current explain mode no

      db2exfmt -d AWSSIT -g TIC -w 2026-01-30-13% -n % -s % -# 0 -o Workload_2026-01-30_AIOPT_off.db2exfmt

      and at the end of all that I have two output files, both driven by the same set of queries but one with AIOPT on and with it off, which allows me to do a comparison and locate the statements with Estimated Costs that have changed.

      It's a bit cumbersome but seems to work. Please let me know if you have a better option or if you can see anywhere this could be improved.

      Regards

      Mark



      ------------------------------
      Mark Gillis
      Principal Consultant
      Triton Consulting
      ------------------------------

      Original Message


    Related Content