How and When Temporary Tables are Re-used and Removed?

By -
How and when Temporary Tables are Re-used

Concurrent Runs of the same Application Engine (AE) program will have different Parameters and each will use it's own respective set of Dedicated Temporary Tables  - If a dedicated set is available.

If no dedicated set of temp tables are available, then the Base Table is used, as long as the Program Properties are set to 'Continue', not 'Abort' in Application Designer for the specific AE program.

Any AE that goes No Success keeps it's dedicated set of Tempoary Tables locked and unavailable for subsequent use until the original AE has either been Restarted and has run to Success, or if the row is deleted from Process Monitor.

If the AE program was run outside of the Process Scheduler at command prompt, then the navigation of;

PeopleTools, Application Engine, Manage Abends

can be used to free up (delete) the set of dedicated temp tables.

How to manually remove Temporary Tables

Occasionally it is not possible to delete an AE process request from the Process Monitor, or release the temp tables with the Manage Abends function. The could occur when users forget to delete the process and the process purge process runs, for example. If this does occur, release the temp tables associated with a particular request by doing the following rteps:

 1. Identify which Process Instance needs to have dedicated set of Temporary Tables deleted.

This SQL can be used to help identify the row in the AE Run control table;

SELECT PROCESS_INSTANCE, OPRID , RUN_CNTL_ID, AE_APPLID, RUN_DTTM FROM PS_AERUNCONTROL ORDER BY PROCESS_INSTANCE;

 This SQL can be used to help identify the row(s) in the AE temp table manager table;

SELECT PROCESS_INSTANCE, RECNAME, CURTEMPINSTANCE, OPRID, RUN_CNTL_ID, AE_APPLID, RUN_DTTM FROM PS_AETEMPTBLMGR ORDER BY PROCESS_INSTANCE;

 2. If the same Run Control ID will be used, delete the specific row identified from the AE Run control table;

DELETE FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE={value}

 3. Delete the specific row(s) from the AE temp table manager table;

DELETE FROM PS_AETEMPTBLMGR WHERE PROCESS_INSTANCE={value}

Note: The Process Scheduler does not need to be stopped during this cleanup because only specific Process Instance is removed. 

Comments

  1. AppsianApril 14, 2021 at 5:31 AM

    Thank you for sharing your blog, seems to be useful information can’t wait to dig deep!

    ReplyDelete

Post a Comment

Popular posts from this blog

Peoplesoft SFTP

By -
Image
Transferring a file using SFTP to specified target location: (Thanks to Jawahar who helped me in this article) A lot has been mentioned about sFTP support in PT 8.51. Below gives clear idea on SFTP. Are there any specific pre-requirements for using file transfer protocols? Yes. Though FTP can be used directly by giving the URL as a string to the peoplecode method, other protocols (ftps, sftp, http and https) require the URL to be passed as a URL identifier only. When an URL identifier is created, URL properties link on the page gets enabled. Users need to provide the URL properties specific to the protocol they are using on this "URL properties" page. The URL properties differ based on the protocol choosen. Once URL Identifier is ready, it can be used in any of the file attachment peoplecode methods just like the existing way.   What are the authentication methods supported for sftp? Password and Public Key authentication are supported.
Read more

How to find the Java Version being used by WebLogic

By -
1. Open the setEnv.sh/cmd (On PT8.51 and lower, located in <PS_HOME>/webserv/<DOMAIN_NAME>/bin On PT8.52 and higher, located in <PS_CFG_HOME>/webserv/<DOMAIN_NAME>/bin) 2. Check JAVA_HOME variable if JAVA_HOME variable is commented out, Go to 3. set PS_HOME=C:/psft/ora/pt849 set BEA_HOME=C:/Bea set WL_HOME=c:/bea/weblogic92 set DOMAIN_NAME=peoplesoft1  @REM Set Javahome via commEnv.cmd call %WL_HOME%\common\bin\commEnv.cmd >nul 2>nul  @REM JAVA_HOME is set via commEnv.sh, to override set it here. SET JAVA_HOME=%BEA_HOME%\jdk150_1 3. If JAVA_HOME variable is commented out, JAVA_HOME is set via commEnv.sh/cmd. Open the commEnv.sh/cmd (located in <WL_HOME>/common/bin) and check JAVA_HOME @rem Reset JAVA Home set  JAVA_HOME=c:\bea\jrockit_150_12 FOR %%i IN ("%JAVA_HOME%") DO SET JAVA_HOME=%%~fsi 4. Go to directory path specified by JAVA_HOME. Run "java -version" command. -bash-3.2$ ./bin/java -ve
Read more

Peoplesoft Error: All Processing Suspended: Restart OPRID=PS, RUNID=RUN01, PI=5000(108,503)

By mymithraa -
All Processing Suspended: Restart OPRID=PS, RUNID=RUN01, PI=5000(108,503) What is this about and what should be done? This error can occur when Application Engine programs are restartable and run via the Process Scheduler, and the last run was No Success - And an attempt re-run, rather than re-start will produce error as well The cause of this message is that previously this same AE program was run using the same OPRID=PS & RUNID=RUN01. It was not Successful on initial run, and now an attempt to RUN the same program has occurred, instead of an attempt to RESTART the original that failed. Whether this error Happened in Development??? This message happened while the Customer was in Development phase of this AE program. It could have happened when this AE was in Production as well. However, what to do would have completely different emphasis depending on if this happens in Development or Productions. During the development cycle, we would expect that this AE would have to run many tim
Read more