Helpful SQL for Integration Broker Monitoring

#1 Count of messages older than 90 days.
#2 Messages older than 90 days.
#3 Counts/per message.


SELECT COUNT(*) FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBHDR WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBHDR GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGPUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGPUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT * FROM PSAPMSGSUBCON WHERE CREATEDTTM < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSAPMSGSUBCON GROUP BY IB_OPERATIONNAME;

SELECT COUNT(*) FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT * FROM PSIBLOGHDR WHERE PUBLISHTIMESTAMP < SYSDATE-90;
SELECT DISTINCT(IB_OPERATIONNAME),COUNT(*) FROM PSIBLOGHDR GROUP BY IB_OPERATIONNAME;

8.55 Tile Query

Useful query to find owners of Tiles created in 8.55


SELECT CREATEOPRID,
LASTUPDOPRID,
LASTUPDDTTM,
PTPPB_GROUPLET_ID,
DESCR,
PTPPB_DATATYPE_ID
FROM PS_PTPPB_GROUPLET
ORDER BY CREATEOPRID ASC;

Update PeopleSoft routing destination nodes via SQL

PeopleTools 8.52
We had a situation where we needed to update the routing destination nodes in PeopleSoft via SQL instead of doing it in PIA. Only do this if you aren’t using a WSDL with the routing and its going to a JMS queue. The statements must be run in order.


UPDATE PSIBRTNGSUBDEFN SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE ROUTINGDEFNNAME IN (SELECT ROUTINGDEFNNAME FROM PSIBRTNGDEFN WHERE IB_OPERATIONNAME ='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME');

UPDATE PSRTNGDFNPARM SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE ROUTINGDEFNNAME IN (SELECT ROUTINGDEFNNAME FROM PSIBRTNGDEFN WHERE IB_OPERATIONNAME ='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME');

UPDATE PSIBRTNGDEFN SET RECEIVERNODENAME='NEW_NODE_NAME' WHERE IB_OPERATIONNAME='DEPT_FULLSYNC' AND RECEIVERNODENAME='OLD_NODE_NAME';

Query – PeopleSoft Jobs & processes that took more than 60 minutes to run

The following query can be used to identify any long running processes from the process request table.

It identifies any processes that took more than 60 minutes to run.

WITH GOT_RUN_MINUTES AS
(SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
(SELECT XLATSHORTNAME
FROM PSXLATITEM
WHERE FIELDNAME = 'RUNSTATUS'
AND FIELDVALUE = RUNSTATUS
) AS RUNSTATUS,
BEGINDTTM,
ENDDTTM,
(TO_DATE (TO_CHAR (ENDDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' )-TO_DATE (TO_CHAR (BEGINDTTM, 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ))*(24*60) AS MINUTES
FROM PSPRCSRQST
WHERE RQSTDTTM > SYSDATE-1
ORDER BY RQSTDTTM DESC
)
SELECT SERVERNAMERUN,
PRCSINSTANCE,
ORIGPRCSINSTANCE,
PRCSTYPE,
PRCSJOBNAME,
PRCSNAME,
RUNDTTM,
RQSTDTTM,
OPRID,
RUNSTATUS,
BEGINDTTM,
ENDDTTM,
MINUTES
FROM GOT_RUN_MINUTES
WHERE MINUTES > '60';

Center align data in a PeopleSoft grid using JavaScript

I had a requirement to center align data in a PeopleSoft grid. Instead of messing with the delivered Style Sheets and creating new ones I simply adding an HTML area to my page and used JavaScript to center it.

getElementsByClassName() returns a HTMLCollection so you need to iterate through the results.

Here is the JavaScript example:


var elements = document.getElementsByClassName("PSLEVEL1GRIDODDROW");
for(var i=0, l=elements.length; i<l; i++)
{
elements[i].style.textAlign = "center";
}

Checking for duplicates on a Grid

I recently used this snippit of code and found it useful. I wanted to post it here so I had it for future reference. Credit: http://www.compshack.com/peoplesoft/peoplecode/check-for-data-duplicates-a-grid

/* Check for data duplicates on a grid. */
Local Row &row1, &row2;
Local number &r, &r1;

&rs = GetLevel0().GetRow(1).GetRowset(Scroll.grid_table);

For &r = 1 To &rs.ActiveRowCount
/*Get grid row*/
&row1 = &rs.GetRow(&r);
/*once we have a row, we are going to loop through the grid rows and make sure a specific field value is unique*/
For &r1 = 1 To &rs.ActiveRowCount
&row2 = &rs.GetRow(&r1);
/* if this is a different row, and the field_name value matches then throw an error*/
If &r1 <> &r And
&row1.grid_table.field_name.Value = &row2.grid_table.field_name.Value Then
MessageBox(0, "", 0, 0, "Error. Duplicate values are not allowed.");
End-If;
End-For;
End-For;

Automatic Backup of PeopleCode

A PeopleCode program is automatically saved to a file while you’re working on it. This checkpoint occurs at the following times:
• Every 10 keystrokes.
• On a save command, just prior to the save being executed (in case the save doesn’t actually execute because the code is invalid).
• When another PeopleCode program is selected to be edited (if you have two PeopleCode editor windows open at the same time, and you move from one to the other).

The file is saved to your temp directory (as specified in your environment), in a file with the following name:

PPCMMDDYY_HHMMSS.txt where MMDDYY represents the month, date and year, respectively, of the checkpoint, and HHMMSS represents the hour, minute and second, respectively.

The top of the checkpoint file contains the following information:

[PeopleCode Checkpoint File]

[RECORD.recordname.FIELD.fieldname.METHOD.eventname]

If your PeopleCode program is saved successfully, any checkpoint files associated with that program are automatically deleted.
Example:
C:\Users\username\AppData\Local\Temp\11

Values for AUTHORIZEDACTIONS

The values stored for the AUTHORIZEDACTIONS are:

1 Add
2 Update/Display
3 Add Update/Display
4 Update/Display All
5 Add Update/Display All
6 Update/Display Update/Display All
7 Add Update/Display Update/Display All
8 Correction
9 Add Correction
10 Update/Display Correction
11 Add Update/Display Correction
12 Update/Display All Correction
13 Add Update/Display All Correction
14 Update/Display Update/Display All Correction
15 Add Update/Display Update/Display All Correction
128 Data Entry
129 Add Data Entry
130 Update/Display Data Entry
131 Add Update/Display Data Entry
132 Update/Display All Data Entry
133 Add Update/Display All Data Entry
134 Update/Display Update/Display All Data Entry
135 Add Update/Display Update/Display All Data Entry
136 Correction Data Entry
137 Add Correction Data Entry
138 Update/Display Correction Data Entry
139 Add Update/Display Correction Data Entry
140 Update/Display All Correction Data Entry
141 Add Update/Display All Correction Data Entry
142 Update/Display Update/Display All Correction Data Entry
143 Add Update/Display Update/Display All Correction Data Entry

No contracts were created because the publication did not pass any filters.

“No contracts were created because the publication did not pass any filters.”

If you get this message in your Application Server logs when trying to invoke the “Service Operation Tester” on a Service Operation you need to create a Local to Local routing for your Service Operation because the Application Server doesn’t know where to route your message. Usually you would create a Any to Local routing by default, but the tester needs the Local to Local routing built so it knows what to do with it inside PIA.

Escape Character in PeopleCode

I was working on a project and came across a scenario where I needed to escape a character in PeopleCode. Specifically a double quote. In order to do that you have to just repeat the double quote:

(notice the double quotes prior to and at the end of IdentityFile)

Local integer &execute;
Local string &cmd;

/*SET PS_HOME DIRECTORY*/
&PS_HOME = LTrim(RTrim(GetEnv("PS_HOME"), "/"), "/");

&cmd = "sftp -o ""IdentityFile=/" | &PS_HOME | "/user/sshkeys/private/sftp-key"" sftp@123.123.123.123:/path/* /" | &PS_HOME | "/attachments/";
&execute = Exec(&cmd, %Exec_Synchronous + %FilePath_Absolute);

Evaluate &execute
When = 0
MessageBox(0, "", 30001, 1, "Files moved Successfully.");
When = 1
MessageBox(0, "", 30001, 1, "Files moved Successfully with an exit code of 1.");
When-Other
MessageBox(0, "", 30001, 1, "Exit code did not return 0. &execute = " | &execute);
End-Evaluate;
CommitWork();

Updating xsd and xsl in jDeveloper

When having to update an xsd (field name changes) in a composite in jDeveloper I found its easier to just go to the source and update the xsd and then go to your Transformation xsl as well and change the source. In order to see the design view updated close your Transformation in jDeveloper and re-open it. You will see the updated field names there. This helps so you don’t have to go and re-do your mapping xsl in the Design view.

Setting up PeopleSoft WebLogic SSL Trace

I recently had to trace some SSL between PeopleSoft and a 3rd Party Application. You need to change the setEnv.sh JAVA_OPTIONS_LINUX options as follows.

$PS_HOME/webserv/DOMAIN/bin/setEnv.sh

JAVA_OPTIONS_LINUX="-jrockit -Xss256k -XnoOpt -Xms1024m -Xmx1024m -Djavax.net.debug=all -Dweblogic.security.SSL.verbose=true -Dssl.debug=true -Dtoplink.xml.platform=oracle.toplink.platform.xml.jaxp.JAXPPlatform -Dcom.sun.xml.namespace.QName.useCompatibleSerialVersionUID=1.0 -Dweblogic.security.SSL.protocolVersion=TLS1"

Make sure you start and stop WebLogic after changing it then the trace will output the logs to:

$PS_HOME/webserv/DOMAIN/servers/PIA/logs/

bash: /bin/cp: Argument list too long

bash: /bin/cp: Argument list too long

If you get the above message it means you have too many files to copy and cp doesn’t handle multiple files well.

The way of getting around these errors is to run your command in a loop:

for file in /from_directory/*; do cp -R "$file" /to_directory/; done