Some Thoughts on Application Engine Temporary Tables

The PeopleTools Application Engine (AE) technology has something called “Temporary tables.” This is a certain record type that can be used with AE with concurrent processing. PeopleBooks does an excellent job of documenting and explaining the usage of temporary tables. They are great, they work great, and they can be powerful.

However, I often see developers use them when they are not needed. When you use the “Temporary Table” record type it brings with it a bunch of other headaches.

• If you need to alter a table they can be a pain at times.
• Additionally, if you need to go back and look at historical data because your process did something unexpected the temporary table results generally will not be there.

The general criteria for using temporary tables is that you plan on running more than one version of the same application engine at the same time for different sets of data. In my experience using AE, concurrent processing is needed in the minority of the cases. Often times you are using AE to create an inbound or outbound interface or to do some calculations and that AE will only run once per week or day. If your AE runs once or twice a day or maybe weekly, why use a temporary table and suffer with all those headaches of temporary tables?

In these cases, I would urge you to use a regular table as the “processing table” but still key it by PROCESS_INSTANCE. This will allow you to view the transactional data after the process has run as it will stay around until you delete it. Let’s say your vendor calls you a week after the interface was sent, and they are asking about something strange that come across the file. If you are using a regular table, you can go back and research the data that was generated in the processing table. If you are using a “temporary table” the data will likely be gone.

If you take my approach of using regular tables then you do have to take some precautions. Let’s say you have a “processing tables” keyed by PROCESS_INSTANCE. Your application engine will have to periodically delete data out of these tables. It really depends on what you are doing with your process, and you have to use your best judgement. If it is a fairly low volume table with less than 10,000 rows, I would say have the process periodically purge your table of old rows after a few weeks or months. You can either keep an “inserted date” field on the processing table, or it can be derived from cross-referencing the process instance back to the process request table. This delete can be done at the very beginning or end of your process.

I have found that using a real “processing table” instead of a temporary table makes my life much easier when supporting production. Of course this only applies if you are not doing high-volume concurrent processing.