A Developer's Notes

Calculate Database Connections Needed For A PeopleSoft Instance

2010-10-05T12:50:00.012+08:00

As a PeopleSoft system administrator or a DBA, chance may be you need to calculate how many database connections are needed for a PeopleSoft database. Below are some tips for the calculation:-

App Server Domain

The following processes connect to the database: PSMONITORSRV, PSANALYTICSRV, PSSAMSRV, PSQRYSRV, PSAPPSRV, PSQCKSRV, PSBRKDSP, PSBRKHND, PSPUBDSP, PSPUBHND, PSSUBDSP, PSSUBHND, PSMCFLOG, PSUQSRV, PSRENSRV (Tip: in psappsrv.ubb, all processes except for PSWATCHSRV with -D parameter connect to the database)

One PSAPPSRV can have 2 db connections if DbFlags is not set to 4(Disable Second DB Connection) or 8(Disable Persistent Secondary DB Connection)

The following processes don't connect to the database: BBL, JSL, JSH, WSL, WSH, PSDBGSRV, JREPSVR, PSWATCHSRV

Process server domain

The following processes connect to the database: PSPRCSRV, PSAESRV, PSDSTSRV, PSMSTPRC, PSMONITORSRV (Tip: in psprcsrv.ubb, all processes with -CD parameter connect to the database)

One PSAESRV can have 2 db connections if DbFlags is not set to 4(Disable Second DB Connection) or 8(Disable Persistent Secondary DB Connection)

The following processes don't connect to the database: BBL

Others

External programs, such as PSRUN (cobol remote call), PSCRRUN.exe(Crystal Reports), PSSQR(SQR report), PSNVS(nVision) etc, also need db connection

Tools/applications used by developers/system administrators, such as pside.exe, psdmt.exe, Oracle client, etc, also need db connection

Quickly Reconfigure App/Process Server Domain

2010-07-14T08:55:00.010+08:00

PeopleSoft system administrators rely on psadmin to reconfigure the application or process server domain. We all have experience of having to go through all the configuration items from psadmin option 14, just to turn on/off one process (let's say, pub/sub server). Actually this is not necessary if you have an understanding of where the configuration items stay - you can bypass psadmin and manually modify those configurations quickly.

All items configurable from psadmin are saved in 3 different files for each server domain:

For application server: psappsrv.ubb, psappsrv.ubx and psappsrv.cfg.
For process server: psprcsrv.ubb, psprcsrv.ubx and psprcs.cfg.

For example, your app server domain's pub/sub server is currently off and you want ti turn it on, you can do the following:

1) Shutdown the server domain first - this is a must
2) (This is no more needed, as UBB file is generated using UBX as a template.)

~~Open psappsrv.ubb, scroll down to the bottom, you will find two lines under 'ubbgen control values:'~~

# [ 6]: {PUBSUB}: FALSE
# [ 7]: {!PUBSUB}: TRUE

~~Swap the FALSE and TRUE so the 2 lines appear as below and save/close the file.~~

# [ 6]: {PUBSUB}: TRUE
# [ 7]: {!PUBSUB}: FALSE

3) Open psappsrv.ubx, search '*PS_DEFINES', under this line you will see

{PUBSUB} Do you want the Publish/Subscribe servers configured (y/n)? [n]:

Change [n] to [y] and save/close the file.

4) Reconfigure - psadmin -c reconfigure -d DOMAIN
5) Restart - psadmin -c boot -d DOMAIN

You will see PUB/SUB server processes are running now.

You can manipulate process server configurations similarly.

Be sure to make a backup of the files before you try with this.

Update:
- 18 Mar 2011: Remote point 2) as it is an unnecessary step.

Make It Easy To Check User's Accessibility To PS Queries

2009-11-09T12:01:00.029+08:00

In PeopleSoft, checking whether a user has access to a PS Query is sometime a very frustrating job, particularly when the query has references to many base records which are added into multiple access groups in different query trees, and these query trees and access groups are granted to different permission lists which are owned by different roles.

Unfortunately, I am the guy who have always been asked 'How come I am not able to view/edit query ... blah blah...' and I have spent too much time and efforts on this. Yesterday I eventually decided something must be done to pull me out of this repetitive and monotonous job, so I wrote the PL/SQL script chk_query_access.sql.

This script asks for 3 parameters:

1) PS Query Name: wildcards (%, _) are accepted. Escape character '\' is allowed too. For example, you can type in full query name N_Q006_SR_LOA, or you can also type in a query name pattern N\_Q00_\_% to check accessibility for queries N_Q001 to N_Q009.

2) User ID: is case-sensitive

3) Verbose Level: ranges from 0 - 3

Level 0: only shows query grant status. This is the default level.


SQL> @d:\SQL\chk_query_access.sql
SQL> SET ECHO OFF
Query Name (wilecard accepted): N_Q006_SR_LOA
User ID: PSTEST
Verbose Level:
0 - Show query grant status (default)
1 - Show query/record grant status
2 - Show query/record grant status and grant paths
3 - Show query/record grant status and all paths
Your Choice(0,1,2,3):
old   5:                v_qryname_pattern PSQRYDEFN.QRYNAME%TYPE        := trim('&prompt_qryname');
new   5:                v_qryname_pattern PSQRYDEFN.QRYNAME%TYPE        := trim('N_Q006_SR_LOA');
old   6:                v_oprid PSOPRDEFN.OPRID%TYPE                    := trim('&prompt_oprid');
new   6:                v_oprid PSOPRDEFN.OPRID%TYPE                    := trim('PSTEST');
old   7:                v_verbose_lvl INTEGER                                   := &prompt_verbose;
new   7:                v_verbose_lvl INTEGER                                   :=          0;
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
===
===  Query 'N_Q006_SR_LOA' granted to 'PSTEST'
===

PL/SQL procedure successfully completed.

Level 1: shows query grant status and record grant status. This is useful when you want to know what record is not granted if the query is not accessible.

...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
>>> Record not granted
-----------------------------------------------------------
>>> Record 3: N_LOA
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' not granted to 'PSTEST'
===
...

Level 2: show query grant status, record grant status, and grant path. At this level the script also shows the whole grant path, eg Record - Query Tree/Access Group - Permission List - Role - User Profile.


...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
[Y] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
>>> Record not granted
-----------------------------------------------------------
>>> Record 3: N_LOA
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_EU -> N_PROG_PLAN_ADMIN_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_IT -> N_PROG_PLAN_ADMIN_QRY_IT -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' not granted to 'PSTEST'
===
...

Level 3: the most detailed verbose, especially useful when a query is not accessible and you need to find out at what position the granting is not done.

...
...
...
===========================================================
=== Checking User(PSTEST)'s access to query 'N_Q006_SR_LOA'
===
-----------------------------------------------------------
>>> Record 1: N_STNT_PERS_VW
[Y] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
-----------------------------------------------------------
>>> Record 2: N_STNT_SUMAC_VW
[N] N_QUERY_TREE_RPT.N_RPTQAG_MOD_RANK -> N_R030_SR_MODULE_RANKING -> N_EXAM_QRY_EU
[N] N_QUERY_TREE_RPT.N_ROGQAG_ENRL_STATS2 -> N_R042_SR_ENROL_STATS2
>>> Record granted
-----------------------------------------------------------
>>> Record 3: N_LOA
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_EU -> N_PROG_PLAN_ADMIN_QRY_EU -> PSTEST
[Y] N_QUERY_TREE_DEN.N_DNQAG_LOA -> N_PROG_PLAN_ADMIN_QRY_IT -> N_PROG_PLAN_ADMIN_QRY_IT -> PSTEST
[Y] N_QUERY_TREE_RPT.N_RPTQAG_BOE_ATTACH -> N_R031_SR_BOE_ATTACHMENTS -> N_EXAM_QRY_EU -> PSTEST
>>> Record granted
===
===  Query 'N_Q006_SR_LOA' granted to 'PSTEST'
===

Updates:

18-Nov-2009: Added check for defnition security.
24-Nov-2009: Added support for command line arguments. Added access group cascading check.

Script Analyzing TraceSQL File And Extracting SQL Statements

2009-11-04T13:54:00.006+08:00

TraceSQL is a great tool for Peoplesoft development debugging and application troubleshooting. But TraceSQL file only logs SQL statements and SQL variable values separately and so is less readable and hard to re-run.

This script is developed to analyze TraceSQL files, filter out unnecessary information, extract SQL statements and replace all SQL variables with the actual values.

For example, for the following contents in a tracesql file:


PSAPPSRV.12271 (951)     1-190    20.22.39    0.008245 Cur#1.12271.CS90SUP RC=0 Dur=0.000238 COM Stmt=SELECT OBJNAME, FLAG, PTCUSTOMFORMAT FROM PSUSEROBJTYPE WHERE MENUNAME = :1 AND PNLGRPNAME = :2 AND PNLNAME = :3 AND OPRID = :4 AND FIELDTYPE = :5
PSAPPSRV.12271 (951)     1-191    20.22.39    0.000013 Cur#1.12271.CS90SUP RC=0 Dur=0.000001 Bind-1 type=2 length=26 value=CALCULATE_TUITION_AND_FEES
PSAPPSRV.12271 (951)     1-192    20.22.39    0.000008 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-2 type=2 length=14 value=ADJ_TERM_PANEL
PSAPPSRV.12271 (951)     1-193    20.22.39    0.000008 Cur#1.12271.CS90SUP RC=0 Dur=0.000001 Bind-3 type=2 length=1 value=
PSAPPSRV.12271 (951)     1-194    20.22.39    0.000006 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-4 type=2 length=2 value=PS
PSAPPSRV.12271 (951)     1-195    20.22.39    0.000010 Cur#1.12271.CS90SUP RC=0 Dur=0.000000 Bind-5 type=18 length=2 value=-1

the script comes out with below SQL:


SELECT OBJNAME, FLAG, PTCUSTOMFORMAT FROM PSUSEROBJTYPE WHERE MENUNAME = 'CALCULATE_TUITION_AND_FEES' AND PNLGRPNAME = 'ADJ_TERM_PANEL' AND PNLNAME = ' ' AND OPRID = 'PS' AND FIELDTYPE = -1;

Script usage: xsql /path/to/tracesql

Update:
- 03-Mar-2010: Bug fix: encapsulated date/time values with quotes. Fixed the issue that the last SQL statement is not outputed.

Enhancements To The Scripts Listing Processes and Memory Usage of PeopleSoft Application Server and Process Scheduler

2009-10-02T15:43:00.016+08:00

In the post released in February I introduced a script that is able to list all processes of a Peoplesoft app server/process scheduler as well as each process' memory usage information.

I have enhanced this script with the following new features:

- For Peoplesoft App Server & Process Scheduler:

= When -m is specified, the script will print CPU usage percentage aside from memory usage.


$ ~/bin/pl -cmh DOMAIN
 PID  PROCESS            VSIZE(m)          RSS(m)     CPU%
 ---  -------            --------          ------     ----
7744  PSBRKHND              104.6            48.3      0.0
27887  PSAPPSRV              585.0           401.7      0.8
7799  PSPUBHND              100.7            24.6      0.0
7685  BBL                    11.6             3.9      0.0
7704  PSSAMSRV               98.2            19.6      0.0
7796  PSBRKDSP              108.6            51.8      0.0
7802  PSPUBDSP              332.7            61.8      0.1
9122  JREPSVR                 9.5             1.0      0.0
9055  JSL(9050)              11.0             1.9      0.0
8030  PSSUBHND              100.6            19.7      0.0
8350  PSSUBDSP              108.6            49.1      0.0
20335  PSAPPSRV              986.7           803.7      0.7
20562  PSAPPSRV              962.3           712.4      1.0
15765  PSAPPSRV             1006.4           805.3      0.5
13737  PSAPPSRV              188.0           157.3      0.8
6362  PSMONITORSRV          103.3            75.6      0.0
21579  PSWATCHSRV             14.6             8.2      0.0
9114  JSH(9053)              52.6            21.1      0.0
9089  JSH(9051)              44.6            16.8      0.1
9107  JSH(9052)              36.6            21.0      0.0

= When -s is specified, the script will print statistical information on the bottom.


$ ~/bin/pl -cmhs DOMAIN
 PID  PROCESS            VSIZE(m)          RSS(m)     CPU%
 ---  -------            --------          ------     ----
7744  PSBRKHND              104.6            48.3      0.0
27887  PSAPPSRV              585.0           401.7      0.1
7799  PSPUBHND              100.7            24.6      0.0
7685  BBL                    11.6             3.9      0.0
7704  PSSAMSRV               98.2            19.6      0.0
7796  PSBRKDSP              108.6            51.8      0.0
7802  PSPUBDSP              332.7            61.8      0.1
9122  JREPSVR                 9.5             1.0      0.0
9055  JSL(9050)              11.0             1.9      0.0
8030  PSSUBHND              100.6            19.7      0.0
8350  PSSUBDSP              108.6            49.1      0.0
20335  PSAPPSRV              986.7           803.7      0.2
20562  PSAPPSRV              962.3           712.4      0.1
15765  PSAPPSRV             1006.4           805.3      0.3
13737  PSAPPSRV              188.0           157.3      0.1
6362  PSMONITORSRV          103.3            75.6      0.0
21579  PSWATCHSRV             14.6             8.2      0.0
9114  JSH(9053)              52.6            21.1      0.0
9089  JSH(9051)              44.6            16.8      0.1
9107  JSH(9052)              36.6            21.0      0.0
 ---  -------            --------          ------     ----
  17  SERVER               4832.3          3245.7      0.9
   5  PSAPPSRV             3728.4          2880.3      0.8
   3  CLIENT                133.8            59.0      0.1

= For app server, ports opened by JSL/JSH/WSL/WSH will be printed.


See above example.

= When -r is specified, the script will print the summary memory and CPU usage of processes of all app servers/process schedulers running on the server. This is useful when you need to monitor server's performance. Actually Oracle recommends that the total resident memory for the entire PS Processes not exceed 70 percent of the total real memory available on the server.


$ ~/bin/pl -r
CATEGORY        COUNT      VSIZE(m)          RSS(m)      RSS%   CPU%
--------        -----     ---------          ------      ----   ----
All                98       37598.7         19232.7      29.3    1.7
PSAPPSRV           28       26224.3         15047.6      22.9    1.1
PSAESRV             0           0.0             0.0       0.0    0.0

- For Peoplesoft Web Server: the script now can print some configuration information and running information (memory and CPU usage) of a web domain.


$ ~/bin/pl -w webdomain
DOMAIN:        webdomain
TYPE:          Single Server
WEBSITES:      server1, server2
HEAP SIZE:     -Xms512m -Xmx512m -XX:MaxPermSize=256m
SERVER:        PIA
 HTTP:        8080
 HTTPS:       8843 enabled
 PID:         19098
 RESOURCES:   VSIZE=893.8m RSS=700.4m CPU=0.2%

The help message:


Usage 1: list process info for a app server and/or a prcs server
   pl [-f] -c|p [-m -h] [-s] 

Options:
   -f force execution even appserv domain doesn't exsit
   -c print processes of application server
   -p print processes of process scheduler
   -m print memory usage (virtual memory and RSS) and CPU usage(%)
   -h print memory usage in human readable format
   -s print summary and statistical info

Usage 2: calculate RAM/CPU usage of all app/prcs processes
   pl -r

Usage 3: list setting and running info from a web domain (Weblogic only)
   pl -w

Currently only Solaris version of the script is available. You can get it from here.

Update on 13-Oct-2009: Some bug fixes. pl -w produces more information.

Manipulating Child Rows in A PeopleSoft Component Through Web Services

2009-05-06T12:06:00.002+08:00

I had this issue when trying to manipulate ID types for a user profile through web service. It was easy to update an existing ID type or to insert a non-existing ID type. For example, look at the following SOAP message:


<soapenv:Body>
   <ns1:Update__CompIntfc__USER_PROFILE xmlns:ns1="http://xmlns.oracle.com/Enterprise/Tools/schemas/M274199.V1">
      <ns1:UserID>COPYUSER2</ns1:UserID>
      <ns1:IDTypes>
         <ns1:IDType>EMP</ns1:IDType>
         <ns1:Attributes>
            <ns1:Fieldname>EmplID</ns1:Fieldname>
            <ns1:Recname>PERSONAL_DATA</ns1:Recname>
            <ns1:AttributeValue>AA0001</ns1:AttributeValue>
            <ns1:AttributeName>EmplID</ns1:AttributeName>
         </ns1:Attributes>
      </ns1:IDTypes>
   </ns1:Update__CompIntfc__USER_PROFILE>
</soapenv:Body>

If ID Type 'EMP' exists for user profile 'TESTUSER', system updates the ID Type's attribute value as 'AA0001'. Or if ID Type 'EMP' doesn't exist with user profile 'TESTUSER', it inserted.

However, this way won't work if we need to remove 'EMP' from 'TESTUSER'. We must adopt an attribute 'CINodeAction' to do the work:


 <soapenv:Body>
    <ns1:Update__CompIntfc__USER_PROFILE xmlns:ns1="http://xmlns.oracle.com/Enterprise/Tools/schemas/M274199.V1">
       <ns1:UserID>TESTUSER</ns1:UserID>
       <ns1:IDTypes CINodeAction="delete">
          <ns1:IDType>EMP</ns1:IDType>
       </ns1:IDTypes>
    </ns1:Update__CompIntfc__USER_PROFILE>

As the matter of fact, we can also set value 'update' or 'insert' to 'CINodeAction' for first and second scenacios stated above, and this makes the SOAP message unambiguous and more understandable.

Attribute 'CINodeAction' is not documented, but can be digged from application package SOAPTOCI.

PS: This tip applies up to PeopleTools 8.49. In to-be-released PeppleTools 8.50, property 'action' has been announced together with some other properties.

Badly-coded PeopleCode Fails Invocation of User-defined methods Through Web Services

2009-02-12T11:01:00.014+08:00

I wrote a user-defined method in a PeopleSoft component interface (i.e. USER_PROFILE) which was exposed as a web services. But when I called this method from a web service client, I received below error:

The key UserID was not found in the request. (158,16017) PT_INTEGRATION.CIDefinition.OnExecute Name:setKeys PCPC:16560 Statement:306Called from:PT_INTEGRATION.CIDefinition.OnExecute Name:invokeUserDefinedFunction Statement:97Called from:PT_INTEGRATION.CIDefinition.OnExecute Name:OnEvent Statement:34

By using PeopleCode debugging, I traced the error to method setKeys of PT_INTEGRATION:CIDefinition and concluded it is the badly-coded PeopleCode that has resulted in the error.

Let's investigate the PeopleCode(partial) of method setKeys:


/* set the keys */
For &i = 1 To &ciKeyCollection.Count

&currentKey = &ciKeyCollection.item(&i);
&keyNotFound = True;

/* is there a corresponding element in the input XML? */

For &j = 1 To &rootNode.ChildNodeCount
/* NOTE: This is the bad code that causes the error! */
If (Upper(&rootNode.GetChildNode(&j).LocalName) = &currentKey.name) Then

   /* the key is present in the XML */
   &keyNotFound = False;

   Local string &keyValue = &rootNode.GetChildNode(&j).NodeValue;

   If (&keyValue = "") Then
      throw CreateException(&ibMsgSetNumber, &emsgKeyValueNotInRequest, "No value found for the key %1 in the request.", &currentKey.name);
   End-If;

   /* set the key data */
   &ciInstance.SetPropertyByName(&currentKey.name, &keyValue);

End-If;
End-For;

If (&keyNotFound) Then
throw CreateException(&ibMsgSetNumber, &emsgKeyNotInRequest, "The key %1 was not found in the request.", &currentKey.name);
End-If;

End-For;

The logic of method setKeys is clear: it tries to search the SOAP request for the component interface search key values and sets key values if the search succeeds, or throws an exception if it fails. For example, the search key of component interface USER_PROFILE 'UserId', method setKeys should be able to extract key value 'TESTUSER1' from SOAP request


<Method__CompIntfc__USER_PROFILE>
 <UserID>TESTUSER1</UserID>
 ...
</Method__CompIntfc__USER_PROFILE>

However, things don't go as expected because of the following statement:


If (Upper(&rootNode.GetChildNode(&j).LocalName) = &currentKey.name) Then

This If-Then statement tries to compare a SOAP node with a search key name. You may have noticed the SOAP node name (&rootNode.GetChildNode(&j).LocalName) is formatted to upper case while the search key name (&currentKey.name) is not. It seems that the programmer assumed that the search key name was always upper case and so required no formatting, but how could he/she made such an arbitary assumption?

For component interface USER_PROFILE which I was working with, since the search key name happens to be 'UserId' instead of 'USERID', above comparison always gives a 'false' and the codes setting key values will never be executed, that is the reason why I saw error "The key %1 was not found in the request."

The resolution is simple, after modifying above statement as


If (Upper(&rootNode.GetChildNode(&j).LocalName) = Upper(&currentKey.name)) Then

the web service has executed properly.

Alternatively, the code can also be


If (&rootNode.GetChildNode(&j).LocalName = &currentKey.name) Then

but it is less safe obviously.

This code persists until PeopleTools rel 8.49.08. Hopefully Oracle will correct it in later release.

Scripts Listing Processes and Memory Usage of PeopleSoft Application Server and Process Scheduler

2009-02-10T17:22:00.012+08:00

Sometimes I want to list all processes belonging to a PeopleSoft app/process domain. Although PSADMIN does provide some options for this purpose, none can satisfy me fully. For example:

./psadmin -c sstatus -d DOMAIN

- No JSH and WSH, also no PID (process id, which is important to me)

./psadmin -c pslist -d DOMAIN

- Shows PID, but no BBL, JSH and WSH

./psadmin -p status -d DBNAME

- No PID

Besides, I am also interested in the memory usage each process, but PSADMIN doesn't provide this kind of information too.

Therefore, I wrote 2 scripts in order to self-help. One is a shell script (view) runnable at Solaris. The other one is a VB script (view) runnable at Windows. A bat file (view) is provided as a wrapper to make the VB script run on and output to DOS commandline, it must reside at the same directory as the VB script.

Both scripts have similar syntax:


pl [-f] {-c-p-c -p} [-m [-h]] instance
Options: -f force execution even appserv domain doesn't exsit
      -c print processes of application server
      -p print processes of process scheduler
      -m print memory usage
      -h print memory usage in human readable format (only applicable to solaris version)

Sample output for running shell script:


$ ~/bin/pl -cmh DOMAIN
PID  PROCESS               VSIZE             RSS
---  -------               -----             ---
6431  PSSUBDSP             107.7M           92.5M
6430  PSPUBDSP             345.2M          161.5M
6397  BBL                   10.9M            8.9M
6407  PSSUBHND              99.7M           84.4M
6446  JREPSVR                8.9M            7.0M
6403  PSAPPSRV             514.7M          306.3M
8105  PSWATCHSRV            15.0M           11.9M
6429  PSBRKDSP             107.7M           92.5M
6402  PSAPPSRV             513.8M          297.5M
6442  JSL                   10.4M            8.4M
6401  PSAPPSRV             533.9M          317.6M
19680  PSMONITORSRV         102.4M           87.2M
6440  WSL                    9.4M            7.6M
6405  PSBRKHND             107.7M           92.5M
6406  PSPUBHND              99.9M           84.6M
6404  PSSAMSRV              97.4M           82.3M
6444  JSH                   12.8M           11.0M
6445  JSH                   16.0M           14.3M
6443  JSH                   16.0M           14.3M

Sample output for running VB script:


C:\WINDOWS\system32>pl -c -p -m DOMAIN
PID     Command         VSize   Working Set
---     -------         -----   -----------
5640    BBL             22.3M   5.8M
1764    PSAPPSRV        114.5M  43.1M
1692    PSAPPSRV        324.5M  81.3M
5856    PSAPPSRV        114.0M  42.5M
2448    PSSAMSRV        104.4M  40.4M
7060    PSANALYTICSRV   107.6M  41.0M
6556    PSANALYTICSRV   107.6M  41.0M
6364    PSANALYTICSRV   107.6M  41.0M
3348    PSDBGSRV        101.5M  37.3M
4592    PSRENSRV        79.4M   17.4M
8112    PSMONITORSRV    101.5M  37.6M
3984    WSL(7000)       24.3M   4.6M
4540    JSL(9000)       25.0M   4.6M
5452    JREPSVR         19.9M   3.9M
7292    PSSAMSRV        100.9M  37.2M
740     PSWATCHSRV      25.1M   6.7M

3840    WSH(7001)       23.9M   4.9M
820     JSH(9001)       24.0M   4.7M
6256    JSH(9002)       26.1M   5.5M
3164    JSH(9003)       24.0M   4.7M
6064    JSH(9004)       26.1M   5.0M
7732    JSH(9005)       24.0M   4.7M

PID     Command         VSize   Working Set
---     -------         -----   -----------
5552    BBL             21.7M   2.7M
5572    PSAESRV         109.9M  14.4M
5616    PSAESRV         109.9M  14.4M
5748    PSPRCSRV        130.8M  21.7M
7112    PSAESRV         303.4M  63.0M
7488    PSDSTSRV        282.0M  50.5M
5812    PSMONITORSRV    100.9M  37.6M


Update on 02/Oct/2009: the enhanced version of pl script (for solaris) is available here

Speed Up PeopleSoft Project Comparison And Migration Using Application Designer Command Line Parameters

2008-08-29T09:14:00.004+08:00

As a PeopleSoft system/data administrator, project comaprison or migration is the regular work I have been undertaking. The usual procedure of doing this is:

1) Logon to app designer with source DB name, user and password

2) Open a project that you want to work on

3) Compare it with the target DB to which you would need to provide username and password so as to logon.

4) After comparing the project, migrate it to the target DB. At this point you need to type in username and password one more time.

It is OK if you only need to work on one or two projects everyday. But imagine you receive dozens of requests per day, in particular during the implementation period?

Fortunately, we are able to make use of app designer command line parameters to automate above procedures.

I hereby list the most important parameters below:

-CT : DB type
-CD : source DB name
-CO : username for source DB
-CP : password for source DB
-TD : target DB name
-TO : username for target DB
-TP : password for target DB
-PJM : name of project to compare
-PJC : name of project to migrate

There are many other parameters that specify comare, copy or report options. Please refer to PeopleBooks for details.

I wrote 2 bat files: pscmp.bat and pscpy.bat, which invoke app designer (pside.exe) with all kinds of parameters mentioned above. The basic usage is:

pscmp <project> <source DB> <source user> <source password> <target DB> <target user> <target password>

and

pscpy <project> <source DB> <source user> <source password> <target DB> <target user> <target password>

Further simpification can be achieved by hard-cording some parameters in the batch scripts. And similarly, you are able to write other scripts to build projects, or just in order to logon without typing in username/password.

Two issues:

1) No parameters provided to customize report filter options. So you still need to logon interactively to change them manually.

2) There may be some definitions that are absent on source but in non-absent state in target. Although this kind of definitions are suposed to be removed from target, they are tagged as 'not upgrade' by default because Peoplesoft would leave them for users to make final desicion. So you also need to logon interactively the tag them as 'upgrade' if you really hope to remove them.

A Script Looking For Processes That Open Specific Ports

2008-07-18T12:39:00.013+08:00

Question: In Solaris, how to know which process opens a specific port?

There is an easy answer in Linux: lsof. However, Solaris doesn't deliver a similar command. So I need a workround to resolve it: using ps command to get pid of all processes, and use pfiles to find ports opened by those processes and match them with the given ports.

I wrote the following script to manage it.

Example of usage:

pp 9000
pp 9000 9001
pp 9000-9010 9100 9200-9201


#!/usr/bin/bash

# The script lists the process that opens given ports

# function printing usage message
help_msg () {
 echo "Usage: pp ... -..."
}

# initialize argument array
aports=""

# function appending a port to $aports
append_arg () {
 if echo " $aports " | grep " $1 " > /dev/null
 then
   return
 fi
 aports=`echo $aports $1`
}

# verify arguments
if [ $# -eq 0 ]
then
 help_msg
 exit 1
fi

# process arguments
for arg in $*
do
 if echo $arg | grep "^[0-9]*$" > /dev/null
 then
   #process single port
   append_arg $arg
 elif echo $arg | grep "^[0-9]*-[0-9]*" > /dev/null
 then
   # process port range (-)
   n1=`echo $arg | cut -d "-" -f1`
   n2=`echo $arg | cut -d "-" -f2`
   if [ $n1 -le $n2 ]
   then
     until [ $n1 -gt $n2 ]
     do
       append_arg $n1
       n1=$((n1 + 1))
     done
   else
     echo "Invalid port:" $arg
   fi
 else
   echo "Invalid port: " $arg
 fi
done

# loop arguments
for port in $aports
do
 echo "Port: $port"
 found=false

 # find processes
 for pid in `ps -ef -o pid | tail +2`
 do
   for pport in `/usr/proc/bin/pfiles $pid 2>/dev/null | grep "sockname:" | cut -d: -f 3`
   do
       if [ $pport -eq $port ]
       then
         found=true
         echo "Process: $pid"
         # echo "Command: " `ps -ef -o pid -o args | grep ^\ *$pid | cut -b7-`
         echo "Command: " `pargs -l $pid`
         #echo $pports
         echo
         break
       fi
   done
 done

 if ! $found
 then
   echo "Not found"
   echo
 fi
done

How To Map A HTTPS-only Web Folder To A Drive

2008-07-10T17:13:00.011+08:00

Question: In Windows XP/2003, how to map a web folder which allows for only HTTPS connections to a drive?

Windows web folder is Microsoft's implementation of WebDAV (Web Distributed Authoring and Versioning). Two WebDAV clients: Web Folders and WebDAV Mini Redirector are integrated and preinstalled with Windows. Based on them, there are usually 2 methods to access a web folder in Windows:

1) Use 'Add Network Place' in 'My Network Places',

This always works regardless of the connection type (HTTP or HTTPS). However, you can't map a web folder opened this way to a drive.

2) Use 'net use' at command line, as shown below:

net use x: http://domain-name/path-to-web-folder

The web folder is mapped to a drive, but this method only works for HTTP connection because of the limitations of WebDAV Mini Redirector:

No support for HTTPS, i.e. no support for secure connections, unless you are using Vista as a client.

No support for declared ports (http://myserver.com:8080/dav/) i.e. your WebDAV server must be using port 80, the default port.

No support for LOCK and UNLOCK commands, i.e. no locking if, for example, two users try to access (open) the same Word document.

Therefore, if a web folder supports only HTTPS connections, and you issue command:

net use x: https://domain-name/path-to-web-folder

you are given an error message something like 'Sysytem error 67 has occurred. The network name cannot be found.'

A software called WebDrive does feature a function mapping a HTTPS web folder to a drive, but it is not free, unfortunately.

Good news is we can do it free, with the help of stunnel - a universal SSL wrapper.

Stunnel is a program that allows you to encrypt arbitrary TCP connections inside SSL available on both Unix and Windows. And it is licensed under GPL. We use it here as a proxy that encrypts a HTTP request to a HTTPS one and submits to WebDAV server. The details are below:

1) Download stunnel Win32 binary from here and install it. The latest release is 4.25.

2) Edit stunnel.conf that is located at 'c:\program Files\stunnel\', make the following changes:

client=yes
verify=0

and add the following section to the end of the file:

[psuedo-https]
accept = 80
connect = domain-name:443
TIMEOUTclose = 0

The 'domain-name' above refers to the WebDAV server's domain name or IP address. Save the changes and start stunnel, now you should be able to map the HTTPS web folder to a drive by issuing command:

net use x: http://localhost/path-to-web-folder

Note:

This solution has been tested to work on Windows XP Pro SP3 and Windows 2003 EE SP2.
Please make sure WebClient service is on, and 'Networking Services' component has been installed with your Windows, otherwise you can't use 'net use' command to connect web folder.
If the WebDAV server requires Windows AD authentication, ie you must provide a user id in format of 'domain\user' and password so as to connect, you must logon to the domain first. My attempt to map a drive while logging on as a local user has failed, even I have forced stunnel to launch using a valid domain user id. The reason is not known yet.

Reference:

Change Shortcut Key for Firefox Download Manager

2008-07-01T13:51:00.011+08:00

Firefox is officially supported by Peoplesoft PIA, and it works fine most of the time. But advanced users may have found they were stuck when trying to invoke system info screen - they hit CTRL-J in FF as they did in IE but saw no system info appeared. Instead, FF popped up a download manager window.

The reason is simple: CTRL-J is being used by FF as a shortcut key for download manager. And unfortunately, FF is not shipping a direct way for user to customize those keys. 'about:config' doesn't enable you to do that.

But there are still ways to tweak it:

1) With add-on. An unofficial FF add-on, 'keyconfig', is available at http://forums.mozillazine.org/viewtopic.php?t=72994. With that you are free to customize shortcut keys for FF, provided you know how to manually edit FF's user preference file (perfs.js). If you can't or don't want, install one more add-on, 'functions for keyconfig' from http://www.pqrs.org/tekezo/firefox/extensions/functions_for_keyconfig/index.html. This enables you to change keys through UI.

2) Without add-on. Knowing where to get a tool is good, but doing it w/o a tool is cool, right?

Let's look at how FF launches download manager first. Explorer %FIREFOX_HOME%\chrome, find the following 2 files:

browser.jar
en-US.jar

Unjar browser.jar into %TEMP%, find browser.xul from extracted files and open it in your text editor, you see a line that looks like

<menuitem id="menu_openDownloads" label="&downloads.label;" key="key_openDownloads" accesskey="&downloads.accesskey;" command="Tools:Downloads"/>

Note the highlighted 'key' attribute, this obviously defines a shortcut key for download manager menu. Remove it to disable the key.

But what if you only want to change the shortcut key rather than disabling it? Let's look at how "key_openDownloads" is defined. In the same browser.xul, you see

<key id="key_openDownloads" key="&downloads.commandkey;" command="Tools:Downloads" modifiers="accel"/>

The highlighted attribute denotes the key value, but how do we know what "&downloads.commandKey;" actually is?

Now extract en-US.jar into %TEMP%, find browser.dtd in which you see

<!ENTITY downloads.commandkey "j">

The truth is out there!. Change 'j' to whatever you prefer, but make sure the new key doesn't conflict with other FF shortcuts.

Stuff the modified browser.xul or/and browser.dtd back into the jar files. Open FF and see whether it works.

This tip applies to both FireFox 2 and 3.

Troubltshoot Error Occurred When Installing Office 2003 SP3 Into Windows VISTA

2007-10-03T12:34:00.000+08:00

I had been trying to install Office 2003 SP3 into my new Thinkpad T61 laptop but kept failing. When trying intallation from Windows Update, it started normally but prompted error on the half way, and never gave a detailed explanation.

I thought it might be Windows Update's fault and so download the separate installer from MS site, but the error persisted, except it gave a detailed error message somethign like 'no sufficient permission to c:\windows\system32\mapisvc.inf'.

I checked the ACL of above mentioned file and found its owner is 'TrustedIntaller', which actually stand for Windows installer service instead of a real group in VISTA. And this file was read-only to the local administrators.

So I made it owned by administrator, and grant write to administrators group. The the SP3 insallation was flying!

Here is the detailed explanation of 'TrustedInstaller'.

Some Useful VIM Commands

2007-08-01T16:30:00.001+08:00

- Go to current directory:

:cd %:p:h

- Covert DOS line feeds to Unix line feeds, or vice versa:

:set fileformat=unix
:w

or

:set fileformat=dos
:w

SQL Server Agent starting problem

2007-07-17T16:41:00.000+08:00

A lot of people(indluding me) had problem starting SQL Agent and saw the following error from Event Viewer:

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

There is a resolution here.

This tip is go to EM->Management->SQL Server Agent->Properties->Connection, change windows authentication to SQL server authentication(you must provide an admin account/password such as sa).

This fault took place because SQLSERVERAGENT service was by default set to started with a 'Local System Account', while local accounts were usually not added into SQL Server logins and granted system admin role.

My Portrait

2007-07-12T10:36:00.000+08:00

My son's work.

A T-SQL script extracting DB schema info

2007-07-12T09:11:00.000+08:00

Sometime I need to get a full view of a MS SQL database's schema details, e.g. user table and its columns, constraints, indexes, etc. And I prefer a plain text output instead of others (let's say XML). Since MS doesn't provide this and I can't find one from web, I wrote a script to implement this on my own:-

TSQL script

The output is tab spearated and can be easily imported into Excel/Access for a better look.

In write this I found TSQL in SQL 2000 is really depressing in constrast to PL/SQL. It event doesn't provide a convenient way of exporting output into an external file, as SPOOL command of PL/SQL does, unless COM is used. More terrible is the lack of support for parameterized cursor as well as 'inline' procedures or functions.

Does SQL 2005 provide any significant enhancements?