Just for Information

I am redesigning my personal site www.rakeshparwal.com

Posting Issue - For Restarted AE, Dist Status is in ' Posting' forever

Note 1: AE Process restarted in Process Scheduler remains in Posting status forever. According to Oracle, it is a known issue in PT 8.48.08 and has been fixed in PT 8.48.11)

Note 2: Below is just an workaround to let other processes, which stuck up in posting status because of the re-started AE, go posted.

I recently came across a posting issue where lot of AE prcess were in Success/No Success runstatus but not getting posted... They were stuck up in POSTING dist status. Output were getting generated successfully in log_output directory, however, the process were in POSTING distribution status only (few of them were in the same status for 3-4 days)

PeopleTools 8.48:
One way to resolve such issue MIGHT be to upated the Distribution Status of such process instances from POSTING (7) to NOT POSTED (4) in PSPRCSQUE and PSPRCSRQST tables... And then post them again using RE-SEND option.

*** Find the prcs instances with distribution status of POSTING (7):

select PRCSINSTANCE, RUNSTATUS, DISTSTATUS from PSPRCSRQST where DISTSTATUS='7';

select PRCSINSTANCE, RUNSTATUS, DISTSTATUS from PSPRCSQUE where DISTSTATUS='7';

*** Update these process instances' dist status from POSTING(7) to NOT POSTED (4)...

-- update PSPRCSQUE set DISTSTATUS='4' where PRCSINSTANCE IN (prcsinst_1, prcsinst_2, prcsinst_3);

-- update PSPRCSRQST set DISTSTATUS='4' where PRCSINSTANCE IN (prcsinst_1, prcsinst_2, prcsinst_3);

update PSPRCSRQST set DISTSTATUS='4' where PRCSINSTANCE in (select PRCSINSTANCE from PSPRCSRQST where DISTSTATUS='7');

update PSPRCSQUE set DISTSTATUS='4' where PRCSINSTANCE in (select PRCSINSTANCE from PSPRCSQUE where DISTSTATUS='7');

*** Post them again using RE-SEND option.

*** Also make sure all the three tables PSPRCSRQST, PSPRCSQUE, PSPRCSPARMS are in synce:

select count(*) from PSPRCSRQST;

select count(*) from PSPRCSQUE;

select count(*) from PSPRCSPARMS;

*** Values of Distribution Status can be found by using below query:

SELECT FIELDNAME, XLATLONGNAME, FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = 'DISTSTATUS';

FIELDNAME XLATLONGNAME FIELDVALUE

------------------------------------ ------------------------------------------------------------ ----------

DISTSTATUS None 0

DISTSTATUS Scheduled 1

DISTSTATUS Processing 2

DISTSTATUS Generated 3

DISTSTATUS Unable to Post 4

DISTSTATUS Posted to Web 5

DISTSTATUS Delete 6

DISTSTATUS Posting 7

8 record(s) selected.

COBOL - Configuring instance specific CBLBINs

Sharing a file server to multiple instances is a common practice in may PeopleSoft Infrastructures... Many a times scenerio occurs where we want to have applied some bundles or Maintenance Packs to few of these instances... In such cases many of the external objects like SQRs, CRYSTALs, COBOLs etc would be different for bundled and non-bundled environemtns. For such cases, we would require to have a separate directory for COBOL executable other than the shared one or default one.

Here are the setting required to be done for such scenerio:

1) Create new CBLBIN directory for each instance INS1, INS2, INS3, etc under $PS_HOME (or any other prefered location) like $PS_HOME/CBLBIN_INS1, $PS_HOME/CBLBIN_INS2, $PS_HOME/CBLBIN_INS3 etc.

2) ## Copy the compiled COBOLs into new CBLBIN directory i.e., to $PS_HOME/CBLBIN_INS1, $PS_HOME/CBLBIN_INS2, $PS_HOME/CBLBIN_INS3 etc

3) Process Scheduler Settings for instance INS1:
Modify psprcsrv.ubx - COBPATH={$PS_HOME}/CBLBIN_INS1
Modify psprcsrv.env - COBPATH={$PS_HOME}/CBLBIN_INS1
Modify psprcsrv.cfg - in RemoteCall Section RCCBL PRDBIN=%PS_HOME%/CBLBIN_INS1

4) Application Server Settings for instance INS1:
Modify psappsrv.ubx - COBPATH={$PS_HOME}/CBLBIN_INS1
Modify psappsrv.env - COBPATH={$PS_HOME}/CBLBIN_INS1
Modify psappsrv.cfg - in RemoteCall Section RCCBL PRDBIN=%PS_HOME%/CBLBIN_INS1

##Besides this, you may also want to modify psrun.mak and pscbl.mak, so that while compiling the cobols - compiled files get copied, automatically at the time of compilation itself, to the newly created CBLBIN directories. For this, here are the required modifications:

1) Create directory INS1 under $PS_HOME/src/cbl and place all COBOL programs to be compiled for instance INS1 at location $PS_HOME/src/cbl/INS1

2) modify psrun.mak:
Replace $PS_HOME/src/cbl/ by $PS_HOME/src/cbl/INS1/

3) modify pscbl.mak: Update PS_CBLBIN parameter from old or default value $PS_HOME/cblbin TO new location of CBLBIN directory for instance INS1 $PS_HOME/CBLBIN_INS1:

PS_CBLBIN=$PS_HOME/CBLBIN_INS1

4) modify pscbl.mak:
Replace $PS_HOME/src/cbl/ by $PS_HOME/src/cbl/INS1/

Then compile and link the cobols.

UNIX - Extracting text between any Two Lines - Updated

Laurent Schneider suggested one liner using AWK unix command - for my last script Extract_Text.sh... see here:



awk '/^CREATE UNIQUE INDEX/{print "-- Create unique index #"++x}/^CREATE TABLE/{print "-- Create table #"++y}/^CREATE (UNIQUE INDEXTABLE)/,/;/{print}' test.sql




/^CREATE (UNIQUE INDEXTABLE)/,/;/{print}: Searches for CREATE UNIQUE INDEX or CREATE TABLE and prints the lines till the first ";" encounters.



/^CREATE UNIQUE INDEX/{print "-- Create unique index #"++x}: Searches for pattern CREATE UNIQUE INDEX and prints "-- Create unique index # and the number of it's occurance".



/^CREATE TABLE/{print "-- Create table #"++y}: Searches for pattern CREATE TABLE and prints "-- Create unique index # and the number of it's occurance".




This is indeed a better and faster than the while read loop. Below is the modified script for download:




Extract_Text_Modified.sh





NOTE: After downloading this file, please REPLACE &lt ; with < and &gt ; with >
there is no space between &lt and ; or &gt and ;



* Thanks Laurent

UNIX - Extracting text between any Two Lines

I created a script that modifies an sql script in certain logical way. It extracts texts between certain logical lines and sends the output to a new file. You can download the shell script from here and try executing it with the attached test.sql as input. I created this script to alter PeopleSoft Generated AlterByRename sqls into simple CREATE statements.

Extract_Text.sh
usage: ./Extract_text.sh
NOTE: After downloading this file, please REPLACE
&lt ; with <
&gt ; with >
There is no space between &lt and ; or &gt and ; (I've mentained the space because of issue with blogspot)

test.sql

Permission Lists Associated to a User

Here is the query to find the permission lists associated to a user:

SELECT A.OPRID, D.ROLEUSER, B.ROLENAME, B.CLASSID, C.ClASSDEFNDESC
FROM PSOPRDEFN A, PSROLECLASS B, PSCLASSDEFN C, PSROLEUSER D
WHERE A.OPRID=D.ROLEUSER AND B.CLASSID=C.CLASSID AND B.ROLENAME=D.ROLENAME AND A.OPRID= 'USER_NAME'
ORDER BY B.CLASSID

Simple Example of Unix "sed" command

Replacing all occurrences of rakesh to raka in file tst

sed s/rakesh/raka/ tst-1

Replacing all occurences of "raka" in old file tst-1 to "rakesh" in new file tst-2

sed s/raka/rakesh/ < tst-1 > tst-2

$ echo rakesh | sed s/rakesh/raka/

raka

$ echo I am rakesh | sed s/rakesh/raka/

I am raka

$ echo I am rakesh | sed 's/rakesh/raka/'

I am raka

s: Substitute Command

/../../ : Delimiter

rakesh : Regular Expression Pattern Search Pattern

raka : Replacement String

$ sed 's/\/tmp\/rk/\/tmp\/rk\/1/' tst-1

hi rakesh parwal

here is the directory /tmp/rk/1