You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Delete Obsolete Queries

1 reply [Last post]
Guest's picture
Guest

Hello

We have more than 5000 Private and public queries in Production Database. We are upgrading from PT 8.18, PS HRMS 8.3 to PT - 8.49, PS HRMS 9.0. We would like find what are the obsolete queries or the queries are not using from long time (both Private and Public) in my current database (People Tools 8.18). I am just wondering how can I find above queries and delete them.

Lepa's picture
User offline. Last seen 2 years 6 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Delete Obsolete Queries

There isn't an easy way to find out when was a query last run or how many total times did it run in PeopleTools 8.12 not until 8.4x!
In PT 8.4x PeopleSoft added two crucial query tables that provides query related statistics. The tables are PSQRYSTATS and PSQRYEXECLOG.

Finding query statistics in PT8.12 is going to be a challenge but once you do find them (if you do) then deleting them would be a matter of executing some DMLs. Here is a function that I use in an application engine to accomplish that:

Function DeleteQuery(&sQueryName As string)
   SQLExec("DELETE FROM PSQRYDEFN WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYSELECT WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYRECORD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELD WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFIELDLANG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYCRITERIA WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXPR WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBIND WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYBINDLANG WHERE QRYNAME=:1", &sQueryName);
/*Tables for PT8.4x*/
   SQLExec("DELETE FROM PSQRYSTATS  WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYEXECLOG WHERE QRYNAME=:1", &sQueryName);
   SQLExec("DELETE FROM PSQRYFAVORITES WHERE QRYNAME=:1", &sQueryName);
End-Function;

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog