PeopleSoft Query Field Metadata

Sometimes you need to query the PeopleSoft for more information such as tracking down what query somebody used when you have query spraw and they only remember some fields in the table or you want to search for custom queries with no parameters.

Two important tables are

  • PSQRYDEFN – Contains information about the definition of the query and summary data.
  • PSQRYFIELD – Contains information about the fields in a query

PSQRYDEFN

  • OPRID – Who created/owns the psquery
  • QRYNAME – Name of the psquery
  • DESCR – The description that was saved for the query
  • VERSION – Version number for the psquery, increments every time a change to the query is saved.
  • QRYTYPE –
  • SELCOUNT – The number of select queries in the ps query. Shows the count of subqueries/unions
  • BNDCOUNT – The number of parameters
  • QRYVALID – If the psquery is valid.
  • LASTUPDDTTM – When the psquery was last updated
  • LASTUPDOPRID – Who last updated the query
  • QRYAPPROVED
  • CREATEOPRID – Who created
  • CREATEDDTTM – When it was first created
  • APPROVEOPRID
  • APPROVEDTTM
  • EXECLOGGING
  • QRYDISABLED
  • QRYFOLDER – The query it is saved to
  • QRYJOINOPTIMIZE – The PeopleSoft row lev el security join optimizer is turned on or not
  • QRYIMGTYPE
  • DESCRLONG

 

PSQRYFIELD

  • OPRID – The owner
  • QRYNAME – Name of the query
  • QRYFLDNAME – Which fields are on the query, uses the table prefix of the record
  • SELNUM
  • FLDNUM – The order of the psquery
  • RECNAME – The name of the record the field is selected from
  • FLDRCDNUM
  • FLDEXPNUM
  • HDGTYPE
  • HEADING – Heading text for the psquery
  • COLUMNNUM
  • GROUPBYNUM
  • ORDERBYNUM – The order in the order by criteria
  • ORDERBYDIR – Direction of order by, populated when descending
  • TTLTYPE
  • SUBTTLNUM
  • USECOUNT
  • XLATFLDNUM
  • XLATTYPE
  • XLATEXPRTYPE
  • XLATFLDNUM
  • XLATEXPNUM
  • AGRREGATEFUNC

How to increase the number of temp table instances available for an Application Engine Program

Sometimes you find the need to increase the number of temp table instances available for an application engine program because you are scheduling more concurrent application engine instances of the specific program than temp tables available.  You may need to review your temp table usage to determine this. Continue reading How to increase the number of temp table instances available for an Application Engine Program

Review Temp Table Usage

Sometimes you notice that you have application engine programs (such as voucher build) that seem to stop using temp table instances and you’re not sure why.  Most likely your temp table instances are all being used by running, suspended, or failed program instances.  You can monitor this from the Review Temp Table Usage page. Continue reading Review Temp Table Usage

Manage Abends – Application Engine

When developing and debugging PeopleSoft Application Engine programs you often have them fail(or abend) as part of the development process.  This can cause your run control to become unusable.  To remedy this you can delete the abended process by navigating to the Manage Abends page and deleting the run control for the failed application engine instance. Continue reading Manage Abends – Application Engine

Type posts for the month of August

The most viewed posts for the month of August.

  1. String vs Stringbuilder Performance
  2. Finding the Most Expensive Queries in SQL Server
  3. Search for text in a directory with Powershell
  4. Read a file with T-SQL
  5. Zipping files with PowerShell

Cycle through monitor brightness with PowerShell

I have one laptop where sometimes the backlight screen brightness gets out of sync between the left and the right side of the screen.  I found that cycling through brightness settings will get them back in sync.  So I wrote a PowerShell script to do this automatically.

It does the following:

  • Reads the current monitor brightness setting
  • Loops through in increments of 10% brightness settings, sleeping 25 ms in between each change.
  • Sets the brightness back to the original setting

 

$monitor = Get-WmiObject -ns root/wmi -class wmiMonitorBrightNessMethods
$currentMonitor = Get-WmiObject -ns root/wmi -class WmiMonitorBrightness
$valueToSetTo = $currentMonitor.CurrentBrightness
for ($i = 0; $i -le 100; $i = $i +10)
 {
 $monitor.WmiSetBrightness(80, $i)
 Start-Sleep -m 25
}
$monitor.WmiSetBrightness(80, $valueToSetTo)

How to View Blocking Transactions from SQL Server Management Studio

Sometimes you have long running transactions blocking other queries you are trying to run in SQL Server causing high application latency.  This is very easy to check on from SQL Server Management Studio.  Checking this is a good troubleshooting step when you find you have data driven pages that are failing to load because the pages are timing out or batch programs that are still running. Continue reading How to View Blocking Transactions from SQL Server Management Studio

How to to add, remove, and reorder glances on Apple Watch

  1. On your iPhone go to the Apple Watch App Photo Jul 29, 5 38 33 PM
  2. Go to GlancesPhoto Jul 29, 5 38 38 PM
  3. Click the Red Minus to remove items from GlancesPhoto Jul 29, 5 38 27 PM
  4. Hold down on the triple lines and drag to reorder
  5. Click the plus next to the Do Not Include items at the bottom of the list to back them appear in your glances.

Continue reading How to to add, remove, and reorder glances on Apple Watch

Read a file with T-SQL

Sometimes you may need to read a file from within SQL Server for a quick loader of files.  If you enable xp_cmdshell you can read the file using simple operating system commands and build a script quickly. Continue reading Read a file with T-SQL

Top Articles July 2015

Below is our top 5 articles for the month of July 2015

  1. Finding the Most Expensive Queries in SQL Server – How to find the most expensive queries that are in history on a SQL Server instance.
  2. Zipping files with PowerShell – Zip a directory with PowerShell script.
  3. String vs StringBuilder Performance VB.Net – Comparison of the performance characteristics of String and StringBuilder in VB.Net
  4. Search for text in files in a directory with PowerShell – Finds text in all files in a directory with a filer on file name as well.
  5. Passbook on Apple Watch  – Quick Passbook instructions for Apple Watch.