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).

An ordinal table doesn't act as MQT.

One of the MQT's features is an ability to automatically route original user's queries to it without an application rewrite.

If the latest is not a big problem or db2 optimizer doesn't want to do the job correctly, or not all the desired original queries can be routed to use it, than yes, an ordinal user maintained table could be the option.

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
------------------------------

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).

Hi Edward,

i don't have a system to test this right now, but I would try activating NOT LOGGED INITIALLY for the MQT at creation time and right before the REFRESH-statement with an ALTER TABLE command

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).

This is something that I was thinking about using, but the issue I have is that we use HADR, and IBM states that NOT LOGGED INITIALLY should not be used if HADR is implemented.  Thank you for your thoughts though, it is very much appreciated. 

I guess I should have added that we use HADR.

Edward Spencer

Hi Edward,

i don't have a system to test this right now, but I would try activating NOT LOGGED INITIALLY for the MQT at creation time and right before the REFRESH-statement with an ALTER TABLE command

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).

This is something that I was thinking about using, but the issue I have is that we use HADR, and IBM states that NOT LOGGED INITIALLY should not be used if HADR is implemented.  Thank you for your thoughts though, it is very much appreciated. 

I guess I should have added that we use HADR.

Edward Spencer

Hi Edward,

i don't have a system to test this right now, but I would try activating NOT LOGGED INITIALLY for the MQT at creation time and right before the REFRESH-statement with an ALTER TABLE command

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).

Hi Edward,

i don't have a system to test this right now, but I would try activating NOT LOGGED INITIALLY for the MQT at creation time and right before the REFRESH-statement with an ALTER TABLE command

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).