[SQL] DB2 AS400 Table partitioning - execution plan for specific partition

Due to constantly increasing number of data stored in our system running on DB2 AS400 7.3 database we decided to introduce table partitioning on some tables. While creation of partitioned tables works fine, we have some problems with analizing execution plans for our queries. Lets assume the simplest table with around 50mln records, partitioned by column YEAR (range 2020 - 2024). Each partition contains lets say 10mln records. In table QSYS2.SYSPARTITIONSTAT we can see confirmation that partitions are created properly (the amount of data in each partition is good as well). We want to test the mechanism of partitioned table by running the simplest possible query:

select * from dbschema.PARTITIONED_TABLE where YEAR = 2023.


So we want to ask for the data from only one specific partition (by partitioning key YEAR).

We thought that DB2 will optimize the query execution plan and only one partition will be accessed by that query. Instead in execution plan generated by System and Navigator (Run and Explain) we can see, that SQE makes table scan and all members of the tables where analized (Member of table being queried - *ALL).



Do we miss something or DB2 AS400 simply do not have that kind of mechanism, that allows to skip searching in partitions which partition keys do not fulfill conditions specified in where clause? Is there any chance to check which partitions where exactly taken for query execution? Maybe there is a system table or a command that allows to observe that.

One of our goal is also to optimize the queries, so we thought that table partitioning can help us but we are not so sure now. What worked for us, was to make alias for specific partition and then use alias in the query. But that is not a solution for our system.

Thanks for your help.

Continue reading...