Friday, January 30, 2009

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.

2 comments :

Ganesh said...

Good one.

I want to find the locked and free temprory table instances by query.

how can i do that ?

After an AE goes to Success is their any way to find which temp table insatance that process used.

David Kurtz said...

That is recorded in PS_AETEMPTBLMGR.