Non-Shared Temp Tables Cleanup

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Some of the processes that depend on shared table instances , can experience a dramatic differences in performance, in case all of the instances available are being used, or remaining locked by some orphan or failed process. One way to resolve this problem is to go online and check if tables should be “released”. Here is a set of SQLs that would allow you to do the same thing and depending on you requirements – allow to automate this process, which could prove critical during the times of high usage:

-- Shared Temp Table instance control table
SELECT * FROM PS_TEMP_TBL_ASGNM
WHERE TEMP_TBL_NAME = 'table_name'
WITH UR

-- Get tables that were locked by processes that ran 200 instance numbers before
SELECT * FROM PS_TEMP_TBL_ASGNM
WHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200
FROM psfssys.psprcsrqst WHERE runstatus = '9')
with ur



-- RELEASE ALL TEMP TABLE LOCKS
-- FROM PROCESS INSTANCES THAT ARE LESS THAN 200 THAN THE LAST SUCCESSFUL ONE
UPDATE ps_temp_tbl_asgnm
SET in_use_sw = 'N', PROCESS_INSTANCE = 0
WHERE in_use_sw = 'Y' AND process_instance < (SELECT max (prcsinstance) - 200
FROM psfssys.psprcsrqst
WHERE runstatus = '9')


Let me know if you have any questions

Comments
  1. Trybik |
  2. Trybik |
  3. Iouri Chadour |
  4. Iouri Chadour |

I really want to know what you think