Managing Changes to the Number of Instances of Temporary Tables used in Application Engine Programs

When you run multiple copies of either the same Application Engine program, or different Application Engines that happen to use the same temporary work records, you need to worry about how many instances of the program or programs are likely to run concurrently, and hence how many instances of the temporary table to build.

But how do you manage these tables when you change the number of instances in the Application Engine properties?

How many tables are built for each Temporary Record?

The number of instances of each Application Engine program is set in the properties for that program (and stored on the PeopleTools table PSAEAPPLDEFN in the column TEMPTBLINSTANCES).

This screenshot is the Application Engine Program Properties for TL_TIMEADMIN. It is delivered configured for up to 10 concurrent instances.


The number of tables that are built for each Temporary Record is the sum of the instances in all the Application Engine programs in which the record is used, plus the number of Global Instances (in set up on the PeopleTools Options page.

Let’s take the record TL_PROF_LIST as an example. It is defined as a Temporary Record in 8 different Application Engine programs (in my HCM8.9 demo system).

SELECT a.recname, a.ae_applid, b.temptblinstances
FROM   psaeappltemptbl a, psaeappldefn b
WHERE  a.ae_applid = b.ae_applid
AND    a.recname = 'TL_PROF_LIST'
ORDER BY a.recname
/

RECNAME         AE_APPLID    TEMPTBLINSTANCES
--------------- ------------ ----------------
TL_PROF_LIST    TL_AGG_SECTN                1
TL_PROF_LIST    TL_OUTTCD                   5
TL_PROF_LIST    TL_PUB_TM1                  5
TL_PROF_LIST    TL_SCHHRSRPT                5
TL_PROF_LIST    TL_SCHRES_AE                9
TL_PROF_LIST    TL_ST_LIB                   5
TL_PROF_LIST    TL_TIMEADMIN               10
TL_PROF_LIST    TL_TRPROFILE               10

So, across all the programs 50 temporary tables are required.

SELECT * FROM pstemptblcntvw 
WHERE  recname = 'TL_PROF_LIST'
/

RECNAME         TEMPTBLINSTANCES
--------------- ----------------
TL_PROF_LIST                  50

The system has three global instances.

SELECT temptblinstances
FROM   psoptions
/

TEMPTBLINSTANCES
----------------
               3

So, Application Designer will build 54 tables based on this record.

• 3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.
• 50 ‘private’ instances for the number of requested instances of the Application Engines. The table names will be suffixed with numbers 4 to 53
• 1 ‘shared’ version without a suffix (in other words the usual name) which is used if there is no instance no already allocated to a process instance.

And this is the top and bottom of the script to build the tables only generated by Application Designer.

CREATE TABLE PS_TL_PROF_LIST (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
…
CREATE TABLE PS_TL_PROF_LIST53 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables that have not been built but that should be built?

If you increase the number of instances of temporary tables on an Application Engine program then you may need to build the extra temporary tables. This query reports the missing tables.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecdefn
        WHERE rownum <= 100) n
,      psoptions o
WHERE  r.recname = c.recname
AND    n.n <= c.temptblinstances+o.temptblinstances
AND NOT EXISTS(
       SELECT 'x'
       FROM   user_tables t
       WHERE  t.table_name = 
            DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                   r.sqltablename) ||DECODE(n.n,0,'',n.n)
        )
ORDER BY 1,2
/

I dropped PS_TL_PROF_LIST42, but I also increased the number of instance of TL_TIMEADMIN from 10 to 11. So the query reports that instances 42 and 54 of this table are missing, and all the other temporary tables for TL_TIMEADMIN also report a missing instance.

RECNAME           INSTANCE TEMPTBLINSTANCES
--------------- ---------- ----------------
…
TL_PMTCH_TMP1           19               19
TL_PMTCH_TMP2           19               19
TL_PROF_LIST            42               54
TL_PROF_LIST            54               54
TL_PROF_WRK             38               38
TL_PT_FINAL             29               29
…

The remedy is very simple. Application Designer will build a script for just the missing tables.

CREATE TABLE PS_TL_PROF_LIST42 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables built that should not be built?

If you have reduced the number of temporary tables, then you may need to drop the excess tables.

This query reports tables that are beyond the number required.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
,      t.table_name
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecfield
        WHERE rownum <= 100
       ) n
,      user_tables t
,      psoptions o
WHERE  r.recname = c.recname
AND    t.table_name = 
          DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                 r.sqltablename) ||DECODE(n.n,0,'',n.n)
AND    n.n > c.temptblinstances+o.temptblinstances
ORDER BY r.recname, n.n
/

I built the missing tables for the previous example, but then I reduced the number of instances on TL_TIMEADMIN back to 10. Now, the query reports that there is an extra table for each record beyond the number defined.

RECNAME         INSTANCE TEMPTBLINSTANCES TABLE_NAME
------------- -------- ---------------- ------------------
…
TL_PMTCH_TMP1       19               18 PS_TL_PMTCH_TMP119
TL_PMTCH_TMP2       19               18 PS_TL_PMTCH_TMP219
TL_PROF_LIST        54               53 PS_TL_PROF_LIST54
TL_PROF_WRK         38               37 PS_TL_PROF_WRK38
TL_PT_FINAL         29               28 PS_TL_PT_FINAL29
…

The problem is that Application Designer will not generate the DDL to drop any of these tables. If you reduce the number of temporary table instances, you will have to drop these tables yourself. However, now, it is easy to adjust the above query to produce the DDL to drop the tables.

SELECT 'DROP TABLE '||t.table_name||' PURGE;' cmd
FROM   …

The query then produces these commands.

…
DROP TABLE PS_TL_PMTCH_TMP119 PURGE;
DROP TABLE PS_TL_PMTCH_TMP219 PURGE;
DROP TABLE PS_TL_PROF_LIST54 PURGE;
DROP TABLE PS_TL_PROF_WRK38 PURGE;
DROP TABLE PS_TL_PT_FINAL29 PURGE;
…

The scripts in this posting can be downloaded from my website.