An Excerpt from the FA-PAG 2017 April/May Newsletter:
Spring Cleaning for Queries?
It is springtime for the northern hemisphere and this has been a traditional time for airing out spaces after the long dreary winter and clearing away flotsam. In that spirit, I recommend a spring cleaning for queries.
Do you have a lot of old queries hanging around but don’t know how to decide which ones to delete and then how to organize them? Here are a few tips that can roll themselves into a nice project for a Friday afternoon when your brain is tired…
A couple of records to check for and/or ask to have added to your Query Access:
- PSQRYDEFN – A very handy record that shows information like the UserID that created the record and whether it has been assigned to a Query Folder (more on Query Folders in just a bit). Important Note: If the UserID in the CREATEOPRID field is populated with “PS” or “PPLSOFT” it is a delivered query - don’t delete it. Other ways to identify delivered queries are those with names beginning with “SFA” (Student Financial Aid) or “QA” (Quality Assurance).
- PSQRYSTATS – If your institution has Query stats turned ON this record will give you all sorts of good information like when the query was last run, how long it takes to run, etc. If there are queries that haven’t been run for over X number of months (or years!) you might want to consider deleting them. A query does not have to be run directly from Query Manager for this record to be updated. Even when a Query is run in a schedule, or to drive a 3CEngine or PopUpdate process the stats are updated.
Option 1 – for institutions with Query Stats turned ON. You might start with a very simple query of your queries. Join these two records and display from:
- PSQRYDEFN – CREATEOPRID
- PSQRYSTATS – OPRID, QRYNAME, LASTEXECDTTM, EXECCOUNT
If your naming convention for financial aid start with “FA” you could add the criterion:
QRYNAME like “FA%”
Sort in order of last executed…
Here is the SQL for Option 1:
SELECT DISTINCT A.OPRID, A.QRYNAME, B.CREATEOPRID, TO_CHAR(CAST((A.LASTEXECDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.EXECCOUNT, B.QRYFOLDER FROM PSQRYSTATS A, PSQRYDEFN B WHERE ( A.QRYNAME LIKE 'FA%' AND A.OPRID = B.OPRID AND A.QRYNAME = B.QRYNAME) ORDER BY 4
Push this information out to a spreadsheet. Decide which queries you want to delete.
Now begins the part that’s good for a Friday afternoon…
- Copy and paste the name of each query you want to delete into the search field of Query Manager.
- Check the box to the left of the query name.
- Select “Delete Selected” from the list next to the “Action” dropdown.
- Click Go
For this next section thanks go to Cathy Smith of Duke University for reminding me of “Query Folders” just in time for this newsletter.
You have cleared away the flotsam queries but are feeling that your Spring Cleaning isn’t quite complete. You want to organize things before you leave this project. You can assign your queries to “Folders” to make them easier to find later on.
Decide on folder names before you begin. Consider the following:
- Do you want/need to maintain a naming convention for Financial Aid query folders?
- The maximum number of characters is 18.
To file an existing query to a folder (there is no advance setup for query folders):
- Click the “Properties” link at the bottom of the page
- Consider typing in a Description if none exists
- Type in the folder name
- Consider adding a Query Definition
- Click OK
- Click Save
To file a new query to a folder, before you save:
- Click the “Properties” link at the bottom of the page
- Enter your query name
- Consider typing in a Description
- Type in the folder name
- Consider adding a Query Definition
- Click OK
- Click Save
Option 2 – for institutions with Query Stats turned OFF. Again, start with a very simple query of your queries. Just one record, but more fields:
If your naming convention for financial aid start with “FA” you could add the criterion:
QRYNAME like “FA%”
Sort in order of last update date/time…
Here is the SQL for Option 2:
SELECT A.OPRID, A.QRYNAME, A.DESCR, A.VERSION, A.QRYTYPE, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.LASTUPDOPRID, A.CREATEOPRID, TO_CHAR(CAST((A.CREATEDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.QRYFOLDER FROM PSQRYDEFN A WHERE ( A.QRYNAME LIKE 'FA%') ORDER BY 6
Push this information out to a spreadsheet. It may take longer to determine which queries you want to delete. Again, do not delete queries where the User is “PS” or the Last User to update the query is “PPLSOFT.” Those are the delivered queries.
You might consider sending a quick email to the Last User to update the query. Ask them to review the query and to let you know whether it can be deleted. One way to do this (thanks again to Cathy!) would be to have the person “file” the query into one of the folders. If they have not added a folder name to the query by a certain date let them know it will be deleted.
Now begins the part that’s good for a Friday afternoon…
- Run your query again.
- Push the results out to a spreadsheet.
- Copy and paste the name of each “unfiled” query into the search field of Query Manager.
- Check the box to the left of the query name.
- Select “Delete Selected” from the list next to the “Action” dropdown.
- Click Go
And when you have finished, the flotsam will be cleared away and you will have a well-organized library of queries. And in the chaotic world of financial aid having anything that is clear of flotsam and well-organized is a rare thing indeed!
Have additional suggestions? Post them to this blog!