I have a big SQL processing several big tables (10 to 25millions rows among these tables). The SQL runs for about 1hour everytime and abends with the message: "Error during SQL execution: (DA0003): [Exception: DBD, [IBM][CLI Driver][DB2/LINUX] SQL0954C Not enough storage is available in the application heap to process the statement. SQLSTATE=57011 State: 57011] The following data providers have not been successfully refreshed: Query 1 with CPS_Service. (DMA0007)."

We increased our DB CFG APPLHEAPSZ 512 to 1024 to 4096 and the error continues to occur.

I am afraid that we may not be able to further increase APPLHEAPSZ. Even if we could, there's no guaranttee it will solve the problem and I don't think it is the best way to resolve my problem permanently. So I come to you experts for help.

Could someone with experience dealing with this kind of problem please let me know what or how to go about resolving this problem.

Any and all suggestions will be appreciated. I am already analyzing the SQL to see if something could be done differently.