If your institution’s users schedule queries using the output type of Email, there is a way to find out who receives those emailed files via query. This may be crucial in the event of an employment change, or just for the purpose of verification of recipients.
Write a query using records PMN_PRCSLIST and PRCSRUNCNTLEOPT
- PMN_PRCSLIST – Process Monitor Process List
- Fields
- RUNCNTLID – run control ID set up when the query was scheduled
- PTNONUNPRCSID – query name
- Other fields as desired, such as RECURNAME, OUTDESTTYPE, and OUTDESTFORMAT (file format to be emailed)
- Criteria
- PRCSNAME = PSQUERY
- OUTDESTTYPE = 5 (Email)
- PRCSRUNCNTLEOPT – Run Control Rec - Tools
- Fields
- RQST_TEXT
- If more than one email address is listed, all will appear in this field as a combined list, much how they are entered into the scheduled distribution list
- Criteria
- All auto-join criteria between the two records
- RQSTTEXTTYPE = 1 (Email Address)
- If the Distinct checkbox is checked, add an expression on the field RQST_TEXT, to convert it to a character field, and use the expression as a field (otherwise, you'll get CLOBBED!)
- This example shows four run controls for four scheduled queries, their recurrences, the output type and format, and the email addresses receiving the output.
- Depending on the email address types used (for example, at one institution, the user’s email address alias is used, however, the alias address is not stored in PS), an email record can be joined to PRSCRUNCNTLEOPT, joining EMAIL_ADDR to RQSTTEXTTYPE. A Name record can be joined to the email record to identify the users by name.