Author: Ivan Solovyev

Enabling COBOL SQL Tracing

Posted on | by Ivan Solovyev

Set the following trace options in the override parameters of the process definition to trace SQL executed by a COBOL SQL process. To access the Process Definition page, select PeopleTools, Process Scheduler, Processes. On the Override Options tab, select “Override” in the Parameter List drop-down and enter the following in the Parameters field:

%%PRCSNAME%% %%DBTYPE%%/%%DBNAME%%/%%OPRID%%/%%OPRPSWD%%/%%RUNCNTLID%%/%%INSTANCE%%/255/%%DBFLAG%%
Enabling COBOL SQL Tracing

Save the process definition and run the COBOL SQL process. The trace files can be retrieved in the Process Monitor.

Updating LASTUPDDTTM Field

Posted on | by Ivan Solovyev

In the example below, the SQL UPDATE statement is used to update the LASTUPDDTTM field along with a few other fields.

UPDATE PS_JOBCODE_TBL
SET US_SOC_CD = ‘ABC’, LASTUPDOPRID = ‘PS’, LASTUPDDTTM = TO_TIMESTAMP(TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD-HH24.MI.SS'), 'YYYY-MM-DD-HH24.MI.SS.FF’)
WHERE JOBCODE = ‘XYZ’;

Enabling Application Engine Tracing

Posted on | by Ivan Solovyev

Set the following trace options in the override parameters of the process definition to trace SQL and PeopleCode executed by an Application Engine program. To access the Process Definition page, select PeopleTools, Process Scheduler, Processes. On the Override Options tab, select “Append” in the Parameter List drop-down and enter the following in the Parameters field:

-TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 131
Enabling Application Engine Tracing

Save the process definition and run the Application Engine program. The trace files can be retrieved in the Process Monitor.

Searching for Strings in PeopleCode Programs

Posted on | by Ivan Solovyev

To search for strings in PeopleCode programs, you can use the following SQL query. Before running the query, replace string {SEARCH_STRING} with the string that you want to find.

SELECT A.OBJECTID1, A.OBJECTVALUE1, A.OBJECTID2, A.OBJECTVALUE2, A.OBJECTID3, A.OBJECTVALUE3, A.OBJECTID4, A.OBJECTVALUE4, A.OBJECTID5, A.OBJECTVALUE5, A.OBJECTID6, A.OBJECTVALUE6, A.OBJECTID7, A.OBJECTVALUE7, A.PROGSEQ, A.LASTUPDDTTM, A.LASTUPDOPRID, B.PCTEXT
FROM PSPCMPROG A, PSPCMTXT B
WHERE A.OBJECTID1 = B.OBJECTID1
AND A.OBJECTVALUE1 = B.OBJECTVALUE1
AND A.OBJECTID2 = B.OBJECTID2
AND A.OBJECTVALUE2 = B.OBJECTVALUE2
AND A.OBJECTID3 = B.OBJECTID3
AND A.OBJECTVALUE3 = B.OBJECTVALUE3
AND A.OBJECTID4 = B.OBJECTID4
AND A.OBJECTVALUE4 = B.OBJECTVALUE4
AND A.OBJECTID5 = B.OBJECTID5
AND A.OBJECTVALUE5 = B.OBJECTVALUE5
AND A.OBJECTID6 = B.OBJECTID6
AND A.OBJECTVALUE6 = B.OBJECTVALUE6
AND A.OBJECTID7 = B.OBJECTID7
AND A.OBJECTVALUE7 = B.OBJECTVALUE7
AND A.PROGSEQ = B.PROGSEQ
AND B.PCTEXT LIKE '%{SEARCH_STRING}%';

Finding Navigation Path to a PeopleSoft Component

Posted on | by Ivan Solovyev

You can use the following SQL query to find navigation path to a PeopleSoft component if all you have is the component name. Before running the query, replace string {COMPONENT_NAME} with the name of the component (enclose it in single quotes).

SELECT DISTINCT RTRIM(REVERSE(
SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL), ' > ')), ' > ') PATH
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE'
AND PORTAL_PRNTOBJNAME = 'PORTAL_ROOT_OBJECT'
START WITH PORTAL_URI_SEG2 = {COMPONENT_NAME}
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME;