Retain Temp Table Data

If you've ever worked on a complicated app engine program that uses temp tables, then you'll know how useful it can be to retain the temp table data for analysis. Unfortunately, by design app engine programs truncate the temp table instance they are going to use at the start and end of the program when they finish successfully. However, just because an app engine runs successfully does not mean it did what was expected!

In these situations, there is a way to keep this data. I call it a FOP step (short for Fail On Purpose). Basically, it involves a step with a SQL action added at the very end of an application engine program that deliberately fails. This stops the app engine program from performing the final truncate of temp tables, allowing you to view the data in them.

Here's a screenshot of the step and action:

fop-step.png

What goes in the SQL? Well, you can have anything that will cause an error, but here's my suggestion:

SELECT %Table(ATEMP_TBL), %Table(BTEMP_TBL), %Table(CTEMP_TBL)
FROM FOP!

Hopefully the table FOP! doesn't exist in your database so this will throw an error. The selects are the useful part. In this format, they will display the instances of the temp tables being used in your .stdout file.

Basically, something that looks like this:

SELECT PS_ATEMP_TBL4, PS_BTEMP_TBL2, PS_CTEMP_TBL1 FROM FOP!

Which tells you exactly which tables to go searching for your data.

For this to work, make you add the FOP as the very last step in your application engine program.

Also, I'd suggest adding a step like this and making it inactive if you are developing a new app engine program that uses temp tables. That way, when debugging is required, it can be made active. Of course, doing all of the above will cause your app engine to go to No Success in the process monitor (which is what we expect), and if restart is enabled, you will need to restart the abended instance (and not start a new one). Also keep in mind that the temporary tables may be cleared when you re-run or restart the application engine. So be careful if you want to keep data between runs.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License