In an attempt to get away from a VERY long running view, I opted to move to using an MQT, populating it with the data from that view.  However, when I try to perform the refresh, it fails with "The transaction log for the database is full".  I know how to increase the logs, and I will do so if there is no other recourse, but I thought I would ask the community if there is something better to do that what I am doing.

This is for our Data Warehouse, and the MQT statement will gather over 112 million rows of data to refresh the table with.  That process, itself takes hours before failing.  Am I wrong?  Is there something better to do than change the View to an MQT?  I just feel that this process of refreshing the MQT is taking way to long.

Any ideas would be very appreciated.

(Just for added context, I have performed hours of analysis to improve the performance of the view and have added multiple indexes to improve it, but it is still horribly slow.  and unfortunately, I am running DB2 on Windows....and I am not happy about that either).

You may alter your mqt to user-maintained and load it with its query with ADMIN_CMD.

Look at the syntax with `query-statement` at the link below.

It should help with log full...

https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-load-using-admin-cmd#sdx-synid_query-statement

In an attempt to get away from a VERY long running view, I opted to move to using an MQT, populating it with the data from that view.  However, when I try to perform the refresh, it fails with "The transaction log for the database is full".  I know how to increase the logs, and I will do so if there is no other recourse, but I thought I would ask the community if there is something better to do that what I am doing.

This is for our Data Warehouse, and the MQT statement will gather over 112 million rows of data to refresh the table with.  That process, itself takes hours before failing.  Am I wrong?  Is there something better to do than change the View to an MQT?  I just feel that this process of refreshing the MQT is taking way to long.

Any ideas would be very appreciated.

(Just for added context, I have performed hours of analysis to improve the performance of the view and have added multiple indexes to improve it, but it is still horribly slow.  and unfortunately, I am running DB2 on Windows....and I am not happy about that either).

You may replace the MQT with a manually maintained table (acts like a MQT), then use staging and LOAD for data refresh.

You may alter your mqt to user-maintained and load it with its query with ADMIN_CMD.

Look at the syntax with `query-statement` at the link below.

It should help with log full...

https://www.ibm.com/docs/en/db2/11.5.x?topic=commands-load-using-admin-cmd#sdx-synid_query-statement

In an attempt to get away from a VERY long running view, I opted to move to using an MQT, populating it with the data from that view.  However, when I try to perform the refresh, it fails with "The transaction log for the database is full".  I know how to increase the logs, and I will do so if there is no other recourse, but I thought I would ask the community if there is something better to do that what I am doing.

This is for our Data Warehouse, and the MQT statement will gather over 112 million rows of data to refresh the table with.  That process, itself takes hours before failing.  Am I wrong?  Is there something better to do than change the View to an MQT?  I just feel that this process of refreshing the MQT is taking way to long.

Any ideas would be very appreciated.

(Just for added context, I have performed hours of analysis to improve the performance of the view and have added multiple indexes to improve it, but it is still horribly slow.  and unfortunately, I am running DB2 on Windows....and I am not happy about that either).

In an attempt to get away from a VERY long running view, I opted to move to using an MQT, populating it with the data from that view.  However, when I try to perform the refresh, it fails with "The transaction log for the database is full".  I know how to increase the logs, and I will do so if there is no other recourse, but I thought I would ask the community if there is something better to do that what I am doing.

This is for our Data Warehouse, and the MQT statement will gather over 112 million rows of data to refresh the table with.  That process, itself takes hours before failing.  Am I wrong?  Is there something better to do than change the View to an MQT?  I just feel that this process of refreshing the MQT is taking way to long.

Any ideas would be very appreciated.

(Just for added context, I have performed hours of analysis to improve the performance of the view and have added multiple indexes to improve it, but it is still horribly slow.  and unfortunately, I am running DB2 on Windows....and I am not happy about that either).

------------------------------
Edward Spencer
------------------------------