Toad World® Forums

SQL Optimizer for IBM DB2 LUW v4.3 is available


#1

We’re pleased to announce the general availability of SQL Optimizer for IBM DB2 LUW v4.3 today. This is a patch release and fixes a number of customer reported issues. Licensed customers current on maintenance may download the new release from SupportLink. This release will also be included with the Toad for IBM DB2 v5.6 bundles due out in April.


#2

Need some clarification to create MQT my table1 size is around 7TB 1. Whether it will increase in Size compared to actual table? 2. While creating MQT it will impact actual table in performance whether need downtime? 3. While Refreshing MQT immediate refresh it will update only the changed and new records or it will take more time by checking all the records in actual table 4. whether it will increase performance in below scenario Table1 having columns like this (ID-Index created,name,age,place,joindate,creationdate) while fetching records from this it takes only 20 minutes for example based on ID column which index created already but giving condition for non index column it take more and more time. Now Am creating MQT Table1_MQT based on ID and Joiningdate , creationdate with index (joiningdate and creationdate) Now joining two tables as below select a.id,b.joiningdate from table1 a, table1_MQT b where a.id=b.id and joining date between ‘1-jan11’ and ‘31 dec11’ This will increase the query performance? Thanks in advance


#3

Hello ragursr,

Your post seems to me “wild” a little bit, but I hope I understood the point.

1. Whether it will increase in Size compared to actual table?

  • I am not sure what exactly you mean, but MQT actually stores the query results as data, and you can work with the data that is in the MQT instead of the data that is in the underlying tables. The required space depends on how the definition of the MQT looks like.

2. While creating MQT it will impact actual table in performance whether need downtime?

  • Hard to estimate the impact on your system without the knowledge of your system and your load, but the initial load will definitely consume resources.

3. While Refreshing MQT immediate refresh it will update only the changed and new records or it will take more time by checking all the records in actual table

  • An incremental update mechanism is used to synchronize a REFRESH IMMEDIATE MQT whenever an update, delete or insert is done to an underlying table.

4. whether it will increase performance in below scenario
Table1 having columns like this (ID-Index created,name,age,place*,joindate,creationdate) while fetching records from this it takes only 20 minutes for example based on ID column which index created already but giving* condition for non index column it take more and more time. Now Am creating MQT Table1_MQT based on ID and Joiningdate , creationdate with index (joiningdate and creationdate) Now joining two tables as below select a.id,b.joiningdate from table1 a, table1_MQT b where a.id=b.id and joining date between ‘1-jan11’ and ‘31 dec11’ This will increase the query performance?

  • I don’t know the full story behind this, but have you considered shadow tables in this case (Db2 v10.5 FP5 and higher - I think)?

I hope it moves you forward at least a little bit.

Ondrej