Do You Need More Temporary Table Instances?

Do You Need More Temporary Table Instances?

When an Application Engine loads a program prior to execution, it attempts to allocate an instance of each temporary record specified in the program to itself. If the allocation of a table fails because there are no available instances, Application Engine is will use the shared instance (unless the program is configured to abort if non-shared tables cannot be assigned). In this case it will write an entry to the message log to warn that the shared instance of the record has been used.

When processes use the shared tables performance is likely to be degraded by contention on the table. The%TruncateTable() PeopleCode macro generates aDELETE by process instance on the shared table instead of aTRUNCATE command.

The problem is that unless you look in the message log, you will not know that this is happening. However, it easy to write a query that will look at the message log table and report whenever this has occurred.

select  p.message_parm recname, r.prcsname,  count(*) occurances,  max(l.dttm_stamp_sec) last_occurance,  max(p.process_instance) process_instancefrom  ps_message_log l,  ps_message_logparm p left outer join psprcsrqst r on r.prcsinstance = p.process_instancewhere  l.message_set_nbr = 108and    l.message_nbr = 544and    p.process_instance = l.process_instanceand    p.message_seq = l.message_seqand    l.dttm_stamp_sec >= sysdate - 7group by p.message_parm, r.prcsnameorder by 1,2/

This report tells you which programs failed to allocated instances of which record, how many times that has happened within the last 7 days.

            Processes Unable to Allocate Non-Shared Temporary Record                                                                  LastRecord          Process                  Last                   ProcessName            Name         Occurrences Occurrence            Instance--------------- ------------ ----------- ------------------- ----------TL_ABS_WRK      TL_TIMEADMIN           4 08:24:39 01/01/2009      12345TL_ATTND_HST1   TL_TIMEADMIN          10 08:23:40 01/01/2009      12345TL_COMP_BAL     TL_TIMEADMIN          11 08:23:40 01/01/2009      12345...

NB: Just because an Application Engine could not allocate a non-shared table, does not automatically imply that you need more instances of that record. It could be that other processes had failed, but the temporary tables are still allocated to the process until the process is either restarted and completes successfully, or the process is deleted or cancelled.

You might choose to create some spare instances of records to allow for failed processes, but if you do not clear failed processes you will eventually run out of instances.