Big Tables in Campus Solutions

Major challenge that we faced while moving from SQL Server to Oracle was how to get big tables

from SQL Server into Oracle in the fastest time, my time slot was just 14 hours. 

There were like 11 big tables(large volume of data) - and peoplesoft has provided a purge process for only one table.

The below 11 tables were taking like 16 hrs to export and import. 

1. PS_CLASS_ATTENDNCE

2. PS_FEE_CLASS_PRICE - Purged those rows with CLASS_PRICE =0 and drop_dt = null

3. PS_SA_REPORT_RSLT - PeopleSoft (now Oracle) has provided a purge process.

4. PS_CREDIT_HISTORY

5. PS_CALC_MESSAGES

6. PS_STDNT_BUDGET_IT

7. PS_FA_FATERM_AUDIT

8. PS_COMMUNICATION

9. PS_STDNT_FA_tERM

10. PS_ITEM_LINE_SF

11. PS_ITEM_SF_GL_HIST

We used the standby database  which was in sync with the production and started the export and import simultaneously to fit the time window.

Configuring SSL

SSL certificate configuration

The following steps describe how to generate private keys, import certificates into a keystore and how to configure SSL via the WebLogic admin server console.

1.  Access the WebLogic domain files.

Open a DOS window and CD to the Webserve directory under your ps_home.  (e.g. CD C:\pshome\webserv\ ) and then CD to bin folder which contains pskeymanager.cmd  

2.  Create a new private key and certificate request for your server.

1.  To create a new private key and certificate signing request, run pskeymanager.cmd -create

2.  Follow the prompts and specify the information that you normally would when creating a certificate. 

Keystore password: The default keystore password will be "password". It is adviced to be changed and made a note of.

DV - DevendraVaidya

Alias: MyPS (This is what is export over the internet) https://myps.dv.com:/psp/myps/

Common name:  myps.dv.com  (domain)

OU: DVIT

Organization:  Devendra Vaidya Ltd

City: Mumbai

State: Maharashtra

Country: India

 

Take the defaults on the rest of the fields

 

3.  Submit your certificate request to a Certificate Authority (RapidSSL / Verisign/Thwate) to obtain your certificate (a.k.a public key).  

 Once the certificate it received, paste it into a .txt file. (dv.txt)

4.  Obtain the root certificate of the CA which processed your request.

Download the root CA certificate from for eg GeoTrust/ Thwate which ever you go with.

The root that is used to issue Versign/Thwate/ certificates(one you choose) is the root labeled "Equifax Secure Certificate Authority (Base-64 encoded X.509)" (Root 1).  Once the certificate it received, paste it into a .txt file. (cert1.txt)

 

5.  Create a combined certificate.

The certificate received from Versign/Thwate/ (one you choose)  must also contain a copy of the root certificate. Paste the text of cert1.txt into dv.txt after the existing text with no blank lines between the two certs.  Save the file (dv_combined.txt).

 

6.  Place the certificates on the webserver.

Place the files (cert1.txt and dv_combined.txt) into the Weblogic domain directory. (C:\pshome\webserv\dv)

 

 

7.  Import your Certificate Authority's certificate into your keystore.

To import your Certificate Authority's public certificate, which you received from your CA, into your keystore, execute "pskeymanager.cmd -import".    When prompted for an alias, specify "equifax".   This name is simply an alias for this certificate.    When prompted for the certificate file to import, specify the cert1.txt certificate file.

 

8.  Import your certificate into your keystore.

To import your public certificate, which you received from your CA, into your keystore, execute "pskeymanager.cmd -import".      When prompted for an alias specify the same alias you did when you created your private key and cert request in step 2 (e.g. your machine name MyPs).   When prompted for the certificate file to import, specify your combined certificate (dv_combined.txt).

 

9. Restart WebLogic Server

Restart the PIA server either via C:\pshome\webserv\dv\bin\stopPIA.cmd and startPIA.cmd or, if installed as an NT service, " NET START dv

 

10. Logon to the WebLogic Server Administrative Console.

Access the WebLogic Server console at http:///console.    When prompted for a User Name and password, specify the WebLogic system ID and password which you indicated during the PIA install.    If accepted the default ID and password during the PIA install they are 'system' and 'password'.

11.  Access Keystores & SSL configuration from the WebLogic Admin console.

In the graphical domain structure hierarchy on the left navigate the following;

• Click on Servers and select PIA
• Click on "keystores" tab
• Click on "Lock & Edit" button in the left panel.
• Select 'Custom Identity And Custom Trust'  from the keystores dropdown field

12.  Update keystore properties.

Update the following fields and then press the 'save' button.

Click on "Activate changes" button to activate the changes

Field	Value	Comment
Custom Identity Key Store File Name:	keystore/pskey	If a different keystore was specified via pskeymanger -keystore file, specify that  name here.
Custom Identity Key Store Type:	JKS	Always JKS
CustomIdentityKeyStorePass Phrase:	password	See note below regarding password
Confirm CustomIdentityKeyStorePass Phrase:	password	See note below regarding password
Custom Trust Key Store File Name:	keystore/pskey	If a different keystore was specified via pskeymanger -keystore file, specify that  name here.
Custom Trust Key Store Type:	JKS	Always JKS
CustomTrustKeyStorePass Phrase:	password	See note below regarding password
Confirm CustomTrustKeyStorePass Phrase:	password	See note below regarding password

Note regarding passwords:  The default keystore and private key password is 'password'.   This should NEVER be used in a production environment.   A private key's password and a keystore's password can be changed via pskeymanager's -changepassword option.

 

 

13.   Update private key properties.

Click on "SSL" tab

Update the following fields for your private key and then press the 'Save' button.

Field	Value	Comment
Private key Alias:	(e.g machine name)	Alias you specified when you created  the private key for this server in step 4.
passphrase:	password	See note below regarding password
Confirm passphrase:	password	See note below regarding password

 

 

14.  Save your keystore changes.

You MUST click the "Activate changes" button to activate the changes that you have made.

 

15.  Restart WebLogic Server and make sure the SSL port is listening on the configured port.

 

(See step 9.)

 

This information will be displayed when running the startPIA.cmd or you can run netstat –a.

 

 

 

16. Configure the other web server(s).

 

To install this cert on other web servers, just copy the /keystore/pskey file to the second webserver and make the same configurations in the Admin Console to pick the right Private Key Alias. Then restart.

Word of Thanks to Dan Ashburn- We worked together in setting this up successfully. 

Stundent Admin - tables that can/cannot be purged!

Motive : Sharing something I found on Customer Connect - helped me while moving PeopleSoft Campus Solutions from Sql Server to Oracle.

PROBLEM:  Which tables can or cannot be purged in Campus Solution : Student Admin Module

Ultimately, the customer is responsible for their own data when archiving records, and unless the purge process is delivered, this is not  a supported function.  We do have information on some tables that we can share, however, as long as the customer realizes that testing and maintenance of this will not be supported.  Any table not appearing in this solution should be regarded as un-purgeable.

The following table, which is NOT all inclusive, outlines some of the tables which should never be purged without serious analysis and testing.  This table was compiled as a result of a direct request regarding these specific tables, and you should not take this to mean that these are the only tables that need to remain intact.

ITEM_SF
ITEM_LINE_SF
ITEM_XREF
ITEM_DUE_SF
ACCOUNT_SF
TUIT_CALC_TBL
FEE_CLASS_PRICE
BI_BILLING_LINE
STDNT_CAR_TERM
STDNT_ENRL  (except through the delivered process)
STDNT_SESSION

The following tables may be purged through delivered processes:

STDNT_ENRL - (Manage Student Records, Manage Academic Records, Process, Drop Purge) will purge all of the classes in a drop status from PS_STDNT_ENRL.  The process is documented in Peoplebooks.

PS_SA_REPORT_RSLT

The following tables can be purged as indicated:

PS_STDNT_CARTRM_PD after Consolidated Statistics has been run

BI_EMPLID_TEMP - After any billing run
BI_ACCT_TEMP - After any billing run
BI_ACCT_ITM_TMP - After any billing run
CREDIT_HISTORY of all but the most current effective dated rows for each student
ITEM_SF_GL_HIST - if the following table is purged
SF_ACCTG_LN - after journals are processed and data no longer needed for historical purposes

COMM_SCTN _DATA  in conjunction with the following table
COMM_SCTN_HDR. (see res 200732040)

PS_SF_ACCTG_LN_OLD after upgrade has been completed

Sundry Helpful tips

A Few Sundry Tips

1. Get long column built as CLOB columns (oracle 10g)

Set database_options column value in PSSTATUS table as 2.

2. Clearing archieve logs

RMAN target sys/pwd

delete noprompt archievelog all;

crosscheck archievelog all

3. Tablespace not appearing in Application Designer.

setspace.sqr / sync PSRECTBLSPC and PSTBLSPCCAT tables.

4. Cobols run out - insufficient memory (8.49.08)

Fixed in 8.49.10 tools.

5. After export of peoplesoft database from SQL Server using DataMover , one may get

" Unable to retrieve current DDL space name " error while altering tables.

for this check a few tools tables like PSSTATUS , PSACCESSPRFL etc. Some details my be

lower case. eg . PSSTATUS . OWNERID column might have ownerid in lower case (sysadm).

In Oracle , if one checks DBA_TABLES.OWNER, sysadm would be in upper case.

Update PSSTATUS.OWNERID to upper case and you should be able to alter tables through

Application Designer.

6. Some Good sites

peoplesoftguys.com/index.php?S=psstatus

go-faster.co.uk

psftdba.com

7. Query sizing - Max Fetch Size

8. PSSTATUS is an important table. If you want peoplesoft to use new oracle datatypes , set

the database_options field to 2 , else leave it as 0 to use old datatypes.

9. Building Search Index - This gets built on the peoplesoft process scheduler home. So inspite of running the process , search will not work. This happens if application server and process

scheudler are hosted on different box. Need to copy "search" from process scheduler box to

application server box.

10. Useful Queries

select

prcsinstance , prcsname ,servernamerqst "Srv Name" ,

to_char(rundttm, 'DD-MON-YYYY:HH:MI:SS') "Run Time" ,

to_char(rqstdttm, 'DD-MON-YYYY:HH:MI:SS')"Request Time",

to_char(lastupddttm, 'DD-MON-YYYY:HH:MI:SS') "Last Updated" ,

to_char(begindttm, 'DD-MON-YYYY:HH:MI:SS') "Process Started",

runstatus

from sysadm.PSPRCSRQST

where to_date(RQSTDTTM,'DD-MON-YYYY:HH:MI:SS') >

to_date((SYSDATE-15/720),'DD-MON-YYYY:HH:MI:SS')

and runstatus = 7

order by rundttm,rqstdttm,lastupddttm,prcsname,servernamerqst;

select OPRID ,PRCSINSTANCE ,PRCS,PRCSNAME ,PRCSTYPE,

decode(RUNSTATUS, 3, 'ERROR', 10, 'NO SUCCESS', 14, 'POSTING', 7, 'INITIATED', 12, 'UNABLE 2 POST', 4, 'HOLD', 8, 'CANCELLED', 'UNKNOWN') RUNSTATUS ,

RUNSTATUS STATUS ,to_char(RUNDTTM, 'DD-MON-YYYY:HH:MI:SS') Run_Time from sysadm.PSPRCSRQST

where RUNSTATUS not in (9,5,2,7,6,16)

and RUNDTTM > (sysdate-(15/1440)) order by PRCSINSTANCE;

Many more to come. Will keep udating.

11. Installing PIA as nt service in multiple server

here PIA, PIA1, PIA2,PSOL

will be created.

installntservice.cmd

installntservice.cmd PIA

installntservice.cmd PSOL so on.

we have to run startweblogicadmin.cmd before

12. Compare Reports not generating html -

go to regedit - hkey current user - software - peoplesoft - pside - dword upgHTMLoutput

the value should be 1 and not a 0. Change and it see htmls being generated.

13. Tuxedo Installation timesout -

go to regedit - hkey local machine - bea system and delete tuxedo folder

try to install again

14. Nvision reports stay in processing mode. check the task manager for excel.exe / psnvs.exe Many times you will have to kill the excel to kick off the nvsion reports.

15. How do you see the configuration used by your PIA?

https://:/psp//?cmd=viewconfig&pwd=dayoff

dayoff is the default passowrd - webprofile- custom properties tab

16. Clearing Web Server Cache

https://:/psp//?cmd=purge&pwd=dayoff

Using this method purges only in memory webserver cache

Returning from Denver - july 27

After 3 long weeks , I will be returning home .. sweet home !!

Had a wonderful trip I must say !!! Successfully upgraded the PeopleSoft for WestWood Online Colleges and I am really very happy about it. All the sinking stomachs , cold feet were put to rest. It has been a wonderful experience technology wise.
Alta staff really to wonderful care of us all through those final upgrade 4 days.. breakfast , lunch , dinner along with their technical inputs. They stacked up snacks and juices to keep us up all energized. Thanks to Patrick , Heidi, Sockie. All that definately made us extremely comfortable.
As always has been a wonderful experience working with Sanket.
Now am looking forward to the database migration project. That too is going to be a different / unique experience.

So , with just one day in hand i.e. tomorrow , I hope to catch up with some site seeing in and around Denver. Downtown and the mountain ride.

Reached Denver , Co

Almost been a year since my last visit to US of A. After visiting most of the software hubs (Silicon valley, New York, chicago , Atlanta) I was very curious about coming over to Denver , Colorado. Was lucky to be coming in a summer, somehow never liked the snow.
As my flight was descending at the denver airport , the view from the top was quite different. No lush greens or buildings. Most of the other places I visited before , Chicago being the best, where u see Sears tower, navy pier.
But Mountain view I get from my hotel room, it's really beautiful..and relaxing.. how I wish I get a chance to do a little bit trekking.
I am booked at La Quinta , something similar to Candlewood. Had to wait for almost an hour for the Boulder Super Shuttle to take me to LaQuinta. The only advantage of putting up in la Quinta , westwood online building is just adjacent.
Ah! that reminds me .... we going live from that 10th. My second peoplesoft upgrade project going live, another feather. But two totally different experiences and totally newteachings.. so I have rolled up my sleeves.. have the confidence and the knowledge and preparedness for uncertainties.
Coming back ..had a difficult task to keeping myself awake, so went shopping at target , watched bruce willis's DIE HARD .. HANS episode...and god know when I hit the pillow, at 1.30 am my sleep broke by scream ...it was the TV.. i had dozed off watching die hard!some shopping at TARGET grocerry ..
I was happy I was able to come on saturday ... today i am going to relax all day.... tomorrow onwards it's all work work and work ..
I have brought a few brochures from the hotel lobby.. hoping I get a chance to see this beautiful place.

PeopleSoft Cobol application server

The MFLicense.exe self-extracting zip file contains the following two updates, that need to be applied to your system(s);
Update 1 - ASCII.DIR and EBCDIC.DIR - Micro Focus NetExpress 4.0 COBOL
Compiler Directives
ASCII.DIR and EBCDIC.DIR contain the Micro Focus NetExpress 4.0 COBOL Compiler Directives required for compiling PeopleSoft COBOL Applications. The ASCII.DIR and EBCDIC.DIR files need to be installed on the systems that will be used for COMPILING PeopleSoft COBOL programs.
The ASCII.DIR and EBCDIC.DIR files need to be installed into EACH \src\cbl\win32 directory,on a system that will use the Micro Focus NetExpress 4.0 compiler
Update 2 - Micro Focus NetExpress 4.0 Application Server License Database The Micro Foucs NetExpress 4.0 Application Server License Database needs to be installedon EACH system that will run PeopleSoft COBOL applications.
Typically, PeopleSoft COBOL application programs are run on PeopleSoft Application ServerSystems and PeopleSoft Batch ( Process Scheduler ) Systems.
_____________________________________________________________ Installation of Updates: _____________________________________________________________
The installation of the Updates is a 3 step process,
1. Run MFLicense.exe. MFLicense.exe is a self-extracting zip file that will put the Updates in the C:\MFLicense-Extract directory, by default. Please refer to Section I, below, for instructions on how to run MFLicense.exe 2. Install Update 1, on your Development systems Note! Development systems are systems where you COMPILE your PeopleSoft COBOL Applications. Please refer to Section II, below, for instructions to install Update 1.
3. Install Update 2, on your Run-Time Systems. Note! Run-Time systems are the sytem where PeopleSoft COBOL applications are run. The Run-Time systems are the systems where your PeopleSoft Application Server and PeopleSoft Batch Server ( Process Scheduler ) run. Please refer to Section III, below, for instructions to install Update 2.

______________________________________________________________
SECTION I - Running MFLicense.exe to extract Updates 1 and 2 ______________________________________________________________

I. Extracting Files from the MFLicense.exe self-extracting zip file
1. Open a CMD box by doing the following; 1.a. Windows Logo Key + R This will open the "Run" dialog box. 1.b. Enter cmd in the Open field 1.c. Click the "OK" button and a CMD box will pop-up
2. Create a directory to copy the MFLicense.EXE file to For Example: From the CMD box, create the directory C:\MFLicense-Extract using the following command; C:>mkdir C:\MFLicense-Extract 3. Copy the MFLicense.EXE file from the CD drive to the directory C:\MFLicense-Extract
For Example: C:>copy X:\MFLicense.EXE C:\MFLicense-Extract\.
Note: Where X: is the drive letter assigned to your CD device
4. Change Directories to the C:\MFLicense-Extract directory For Example: C:\>cd /d C:\MFLicense-Extract
5. List the directory of C:\MFLicense-Extract via the dir command, The following files should be displayed; For Example: C:\MFLicense-Extract>dir Volume in drive C is PS Volume Serial Number is 0426-0234
Directory of C:\MFLicense-Extract
07/18/2005 03:44p . 07/18/2005 03:44p .. 07/18/2005 03:42p 840,599 MFLicense.EXE 1 File(s) 840,599 bytes 2 Dir(s) 10,245,515,264 bytes free

6. Extract Updates 1 and 2 by running MFLicense.EXE. For Example: C:\MFLicense-Extract>MFLicense.EXE
7. A pop-up dialog window will prompt you for the installation directory;
Enter c:\MFLicense-Extract, then click the "Extract" button.
8. A pop-up dialog window will inform you went the Extraction is complete. Click the "OK" button to complete the Extraction.
9. The C:\MFLicense-Extract directory should now contain the folllowing files, which can be verified by running a directory listing;
For Example:
Volume in drive C is PS Volume Serial Number is 8C7E-CAEA
Directory of C:\MFLicense-Extract
07/18/2005 03:44p . 07/18/2005 03:44p .. 07/18/2005 10:08a 267 ASCII.DIR 01/27/2004 03:17p 8,342 ASLMF.LNG 01/27/2004 03:17p 163,840 aslmpclocate.exe 01/27/2004 03:17p 323,584 aslmpcsilent.exe 04/14/2005 04:26a 69,697 CBLINTS.DLL 04/14/2005 04:26a 540,737 CBLRTSS.DLL 04/14/2005 04:26a 41,025 CBLVIOS.DLL 04/14/2005 04:26a 24,642 COB32API.DLL 07/18/2005 10:08a 267 EBCDIC.DIR 07/18/2005 03:42p 840,599 MFLicense.EXE 06/02/2005 11:09a 262,144 MFSetup.exe 01/27/2004 03:17p 448 prodfile 07/18/2005 03:46p 10,254 README 01/27/2004 03:17p 1 semfile 03/25/2005 07:58a 5,268 setupMF.bat 15 File(s) 2,291,115 bytes 2 Dir(s) 1,804,538,368 bytes free

______________________________________________________________
SECTION II - Installing Update 1 - ASCII.DIR and EBCDIC.DIR ______________________________________________________________
II. PeopleSoft Delivered Compiler Directives Need to be updated, for Compiling PeopleSoft COBOL Programs with Micro Focus NetExpress 4.0 COBOL Compiler.
This step is required when using the Micro Focus NetExpress 4.0 COBOL Compiler, to compile PeopleSoft COBOL applications.
1. PeopleSoft Uses Specific COBOL Compiler Directives for Micro Focus NetExpress 4.0 compiler, which are present in ASCII.DIR & EBCDIC.DIR
2. Open a CMD box by doing the following;
2.a. Windows Logo Key + R This will open the "Run" dialog box. 2.b. Enter cmd in the Open field 2.c. Click the "OK" button and a CMD box will pop-up 2.d. Backup Delivered ASCII.DIR & EBCDIC.DIR as follows : copy \src\cbl\win32\ASCII.DIR \src\cbl\win32\ASCII.BAK copy \src\cbl\win32\EBCDIC.DIR \src\cbl\win32\EBCDIC.BAK
Note : The above step has to be performed for all locations, where the COBOL Programs will be compiled with the Micro Focus NetExpress 4.0 Compiler.
2.e. copy C:\MFLicense-Extract\*.DIR \src\cbl\win32\. /A/V/Y
Note: is the High level directory for your PeopleTools installation


________________________________________________________________________ ____________________
SECTION III - Installing Update 2 - NetExpress 4.0 Applications Server License Database ________________________________________________________________________ ____________________

III. Installing the Micro Focus NetExpress 4.0 Applications Server License Database on PeopleSoft Run-Time Systems. This License is required for running COBOL programs compiled with Net Express 4.0.
1. Prior to installing the Micro Focus NetExpress 4.0 Applications Server License Database, you MUST install the PeopleSoft COBOL applications on your Run-Time system.
Note: Attempting to install the Micro Focus Net Express 4.0 Application Server License Database will fail, if the PeopleSoft COBOL applicatons have not been installed and compiled!
2. At this point you need to set the environment variable %PS_HOME% to the directory where your PeopleSoft Software is installed
For Example:
If your PeopleSoft Applications were installed in the directory C:\PEOPLESOFT-HR880, the PS_HOME environment variable would be set as follows: C:\MFLicense-Extract>set PS_HOME=C:\PEOPLESOFT-HR880
3. Echo the PS_HOME environment variable %PS_HOME% to insure it has been set correctly.
For Example:
C:\MFLicense-Extract>echo %PS_HOME% C:\PEOPLESOFT-HR880

4. Installing the Micro Focus Net Express Application Server License Database To install the License Database run the program setupMF
For Example: C:\MFLicense-Extract>setupMF When setupMF runs, the Windows Registry will be updated with the following registry entry;
\HKEY_LOCAL_MACHINE\SOFTWARE\Micro Focus\ASLMF
In addition to the registry entry, the files mfasdb, prodfile and semfile will be created in the directory C:\psft-mf-nx-as-license If desired, an alternate license directory, other than C:\psft-mf-nx-as-license, may be specified for mfasdb, prodfile and semfile. This is done by specifying the directory's complete path as an argument to the setupMF command.
Option 1, below, illustrates the default invocation of setupMF.
Option 2, below, illustrates the invocation of setupMF with an alternative License Directory specified.
To install the Micro Focus Net Express 4.0 Application Server License Database, choose either Option 1 or Option 2. Option 1: Run setupMF, without any parameters, to generate and populate the C:\psft-mf-nx-as-license directory.
For example:
C:\MFLicense-Extract>setupMF 1 file(s) copied. MFSetup - %PS_HOME% Directory EXISTS - C:\PEOPLESOFT-HR880 MFSetup - This utility program will setup the appropriate MFSetup - Micro Focus Application Server Licensing to MFSetup - permit you to run your Peoplesoft applications. ok
MFSetup - Micro Focus Application Server Licensing Setup completed!
Option 2: Running setupMF with the directory path you desire to generate and populate the License Database in a directory different than C:\psft-mf-nx-as-license.
In the following example the directory C:\APPLICATION-LICENSE-DATABASE is specified
For Example: C:\MFLicense-Extract> setupMF C:\APPLICATION-LICENSE-DATABASE 1 file(s) copied. MFSetup - %PS_HOME% Directory EXISTS - C:\PEOPLESOFT-HR880 MFSetup - This utility program will setup the Appropriate MFSetup - Micro Focus Application Server Licensing to MFSetup - permit you to run your Peoplesoft applications. ok MFSetup - Micro Focus Application Server Licensing Setup completed!

5. The license database directory C:\psft-mf-nx-as-license or the alternate directory you specified, should contain the following files;
C:\psft-mf-nx-as-license>dir Volume in drive C is PS Volume Serial Number is 0426-0234 Directory of C:\psft-mf-nx31sp1-as-license 08/12/2004 02:17p . 08/12/2004 02:17p .. 08/12/2004 02:17p 1,194 mfasdb 01/27/2004 03:17p 448 prodfile 08/12/2004 02:17p 0 semfile 3 File(s) 1,642 bytes 2 Dir(s) 7,402,060,800 bytes free

Lord Balaji's another abode near Pune

Where in Maharashtra ?
Narayanpur - 36 kms from intersection of Sinhgad road and Banglore Pune Bypass.
How do we get there ?
36 kms is not a long distance from pune; two , four wheeler or any Publictransport buses going towards Satara should be good I guess.
It's absolutely beautiful temple and I must say absolutely well kept.
They say it's a replica of Tirupati, unfortunately I have not yet got a chance to see Balaji at Tirupati , I must thank Venkateshwara group who has played a major role in building and managing this temple here.
We started at 7 am on 3rd May, and in like 40 minutes we are at the door steps of the temple. Right from the entry everything seemed to be well organised. The guards guided us to the parking area and to the footwear stand. Luckily since it was pretty early (around 7.45 am) the temple was not much crowded.
As soon as we started to get into the temple aarti(morning prayers) started so we had to waittill it was over in about 25-30 mins. We were let inside the temple through the metal detectors. A need to put metal detectors at God's Footsteps ....hurts soooo much.. I really prayed to Balaji to put some thought into the anti social elements who put the authorities to fix the detectors.
Any way , but the darshan was unexpectedly very nice, I have heard and experienced , all this big and rich temples do not allow you to stand in front of the idol for more than half a minute even..But today I really felt nice to stand and have a good look an the idol and capture it (in my mind...).
Inside the temple everything was so clean and well kept , I was really happy to see it , the authorities there were monitoring the cleaning workers and directing them if needed. Marble flooring , celing bore beautiful designs on them. Every pillar well painted and dust free . Colours were all well maintained. Every idol within the temple was so well decorated. Even the donation boxes are well designed. That time I realised how environment colours your mind and mood.
On our way out , we were given the typical prasad that I have been having from everyone who visited Tirupathi, rice and milk and the ladoo.
We sat outside and had some prasad . On our way out we visit kuber(lord of wealth) temple. Not many temples have Lord Kuber's idol too nor have I ever visited a temple devoted only to Lord Kuber.
And then we were back to the daily routine .. bugs, fixes , configurations, coding, but somehow today I did not get a dragged feeling most software labourers get when you are required to work on a weekend to meet the deadlines.....you did not promise.

Pu.La. parat hone nahi

For the last few days me and Sandhya(my better half) have been saying .. it's been a while that we have not heard pu.la.
So on this day of gudipadwa after the pooja and gudi sthapana we decided to play the video for Chitale Master , 100% Pestonkaka.
Towards the end of Chitale Master katha ,a hoarding that stood at the Lakdi Pool chowk flashed before me .. Parat Pu.La hone nahi ....... Never will we have some like him ever again.. That thought brought tears to my eyes...
What a personality !
What appeals to me is the simplicity with which he put the day to day things our life his compositions, stories.
The ocean of knowledge on every topic that he has handled. You can take from the laundry fellow "PARit", chitale master, pestonkaka , sakharam , gulacha ganapati.
His contributions to the marathi cinema. His expanse over other languages.
Let me use the words he used for Sakharam Ghatane --
"SARASWATI TYANCHYA JEEBHE VAR NACHATE !!" , Kiti pustakanchi pane pu.la ni swataha khalaliastil..His observational capabilities and putting it in front of the audience with such ease..
I skipped a heart beat when one of my NON-MAHARASTRIAN (not from the state of Maharastra) colleagues asked me who Pu.La.Deshpande was!!!
Are they not missing something in life !!!
But the reality lies.. PARAT PU.LA HONE NAHI!!!

Fine prints

1. Process Schduler - http unable to transfer reports.

Report nodes and related settings are set as expected.

* Authentication needs to be set in Intergration Broker for http transfer to happen.
PeopleTools - intergration Broker - node - authentication - choose password.

2. Authority to search is given through Portal Admin menu - portal search component

3. Tools 8.58 onwards psadmi - client status shows j pool instead of users that have logged into
the system.

To see users modify web.xml - from webserv../site/..../PORTAL/WEB-INF directory.

4. For building search process scheduler / application server should be on the same box. Portal
Index process builds search index in pshome/data/search directory.

5. sp_dbcmptlevel is used for backward compatibility in SQL Server.

generate insert stmt for mismatching tables

rem - struc_no_match_tbl is a table that has list of tables whose structure does not match
DECLARE @TBLNM VARCHAR(50)
DECLARE C2 CURSOR FOR
SELECT RECNAME FROM _NO_MATCH_TBL
OPEN C2
FETCH NEXT FROM C2 INTO @TBLNM
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FN VARCHAR(30),@CL VARCHAR(8000),@SL VARCHAR(8000),@CNTR INT ,@FT INT

Declare C1 CURSOR for SELECT FIELDNAME FROM PSRECFIELDALL WHERE RECNAME = @TBLNM
OPEN C1
FETCH NEXT FROM C1 INTO @FN
SELECT @CL = ' '
SELECT @SL = ' '
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CNTR = COUNT(*) FROM sourcedatabase..PSRECFIELDALL WHERE RECNAME = @TBLNM AND FIELDNAME = @FN;
SELECT @FT = FIELDTYPE FROM PSDBFIELD WHERE FIELDNAME = @FN
SELECT @CL = @CL + @FN +',';
IF (@CNTR<>1)
IF @FT <> 2
SELECT @SL =@SL + ''' '''+','
ELSE
SELECT @SL = @SL +CHAR(0)+','
else
SELECT @SL = @SL + @FN + ',';
FETCH NEXT FROM C1 INTO @FN
END;
SELECT @CL =SUBSTRING(@CL,1,LEN(@CL)-1)
SELECT @SL =SUBSTRING(@SL,1,LEN(@SL)-1)


PRINT 'INSERT INTO PS_'+@TBLNM +'('+@CL+')'+'SELECT '+ @SL +' FROM '+'UCP83..PS_'+@TBLNM

CLOSE C1
DEALLOCATE C1

FETCH NEXT FROM C2 INTO @TBLNM
END;

CLOSE C2
DEALLOCATE C2

Superstition - Are we lagging behind ?

Superstition is believing in something which cannot be explained by human reasoning or physical laws. We are now in the 21st Century and I get surprised about the prevalence of various types of superstitions in the society we live in , on a large scale. The other day one of the well known surgeon in Pune was very much perturbed and concerned over the adherence to the superstitions by the well educated people in various faculties like medicine,engineering , IT etc, when they themselves or near relations are hospitalized. He found that the kins keep photographs of some God or Saintly Guru in the room, perform pooja and stuff. To his dismay , most of such lot is from the young educated generation. We may be tech savy, financially and socially improved over many countries, but our mind-set towards superstition remains unchanged to a great extent.

Superstitious attitude is not a monopoly of rural , uneducated class who carry out their vocation, family affairs that they inherited. Superstitions are secular. All religions in the world are in the grab of superstitions. Every category of workforce is behind them without discrimination from bosses down to the workman. Most of us in the society are of fickle minded nature.

People do not take the risk to go against the rudimentary customs and so superstitious. As it is feared one may have to face all odds and unwanted situations. Almost everyone of us leave our homes remembering the almighty and praying that the day goes by smoothly.
In short, however technologically, scientifically one may be educated to face the 21st century challenges, the mind-set remains the age old as far as superstitions go.... like the good omen and bad omen continue to haunt one's mind.

Why talk about others, I visited Gajanan Maharaj Math near Parvati, Pune. today 23rd Feb 2008 , @5.30 am . I go there because I get the feel good factor , I have faith. Am I superstitious or "Nastik". Are you Superstititious ? I take - it's purely personal and will vary with every soul!!

These were our thoughts (me an my father) I spend daily 10 mins remembering the almighty and he probably spends 10 times my time and we both try to put science and logic behind many things but !!! .

A Thread Ceremony - Munja

As I sat, on the couch watching the rituals of my nephew's thread ceremony ,came to me many
thoughts , actually questions.

Today, a kid's world is filled with Tom and Jerry, Diego, Nick JR, Power Rangers ..
Where does this Thread ceremony fit in ?

My nephew , sitting besides my sister and her husband who were actually performing the rituals, was keeping himself entertained by making monkey faces to my son and many other similar mischievous moves. Sitting glued to one place seemed to be an herculean task. I know it's a difficult task for most of 8 yr olds..nothing wrong with it.


So, What is this Thread Ceremony , why and when is it performed ?
To put make it short, MANY MANY DECADES ago, education of the kids would start after this ceremony was perfomed, typically after attaining the age of 8. The kid would leave his home and go to a "boarding school", Gurukul as it was know then .
He would stay there for the next 12 yrs.
The kid was expected to give a helping at that school and get knowledge in return.

But here, my nephew knew the concept but, he also knew that he would not be going to any
Gurukul /boarding school and be away from his parents. Tension Free !

I tried to tease him by telling him that you will be sent to a boarding school but he was
sure that it would never happen and he would still play with his Power Rangers toys and
watch those cartoons sitting on the cosy couches of his cosy home.


Imagine the dialogs between mother and son of those day
son, now on , Guru is the one you should look at for all the guidance
son, serve your Guru well so that you are blessed with abundance of knowledge
son, you have to take care of yourself hereon and many such notes.
The son too must be realizing the gravity, that suddenly from tomorrow the loving cover
is going to go away and I am not going to have things going my way..
What must be going through their minds ? Tension, Anxiety , Scare

In todays world -
mother would rather tell her son -
get ready for competition , now a days i see 3-4 yr old kids competing with each other..
competition is good but not at that age.. they are so innocent
get ready for the loads of studies, demand for performance
get ready to face the stress
get ready for the traffic
get ready to stand in queues for every thing you want, right from getting into a bus,train,
buying rice, to getting ration card, admission to your desired college , jobs

But all this too calls for the parents to start providing for their education , it's started
becoming costlier by the day.

But the other thing to note is that now a days a lot of money too is spent on such ceremonies.

Let me leave with a thought- would spend a handsome amount on thread ceremony or would you prefer to invest for his future consumption.

FieldType in PSPNLFIELD

0- Label 6- LongEditBox 12- PushButton
1- Frame 7- CheckBox 13- ScndaryPage (subpnlname)
2- GroupBox 8- RadioButton 14- TreeControl
3- StaticImage 9- Image 15- Gridcontrol
4- EditBox 10- ScrollBar 16- PushButton (Gotomenu,pnlgrpname)
5- ComboBox 11- SubPage 18- SecPage (subpnlname)

19- PSGrid
20- Tree
21- PushButton (calls Subpnlname)
23- HorzRule (horizontal line)
24- TabSeperator
25- HTML Area
26- PushButton (hyperlink to PromptAction)
27- Scroll Area

Script for Inserting setup data

--struc_no_match_tbl has list of tables that have structural changes between PeopleSoft 8 and 9 -- This script creates insert statement for inserting setup data from PeopleSoft 8 database to 9.
DECLARE @TBLNM VARCHAR(50)
DECLARE C2 CURSOR FOR
SELECT RECNAME FROM SVD9..STRUC_NO_MATCH_TBL
OPEN C2
FETCH NEXT FROM C2 INTO @TBLNM
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @FN VARCHAR(30),@CL VARCHAR(8000),@SL VARCHAR(8000),@CNTR INT ,@FT INT

Declare C1 CURSOR for SELECT FIELDNAME FROM PSRECFIELDALL WHERE RECNAME = @TBLNM
OPEN C1
FETCH NEXT FROM C1 INTO @FN
SELECT @CL = ' '
SELECT @SL = ' '
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CNTR = COUNT(*) FROM UCP83..PSRECFIELDALL WHERE RECNAME = @TBLNM AND FIELDNAME = @FN;
SELECT @FT = FIELDTYPE FROM PSDBFIELD WHERE FIELDNAME = @FN
SELECT @CL = @CL + @FN +',';
IF (@CNTR<>1)
IF @FT <> 2
SELECT @SL =@SL + ''' '''+','
ELSE
SELECT @SL = @SL +CHAR(0)+','
else
SELECT @SL = @SL + @FN + ',';
FETCH NEXT FROM C1 INTO @FN
END;
SELECT @CL =SUBSTRING(@CL,1,LEN(@CL)-1)
SELECT @SL =SUBSTRING(@SL,1,LEN(@SL)-1)


PRINT 'INSERT INTO PS_'+@TBLNM +'('+@CL+')'+'SELECT '+ @SL +' FROM '+'UCP83..PS_'+@TBLNM

CLOSE C1
DEALLOCATE C1

FETCH NEXT FROM C2 INTO @TBLNM
END;

CLOSE C2
DEALLOCATE C2

Congrats Sumit !!

A very unconventional Engagement Ceremony. Uncoventional because no pandits and rituals, that form 99.9999% Brahmin ceremonies especially koknastha Brahmins, were performed. Its been a long long time since I attended such a Maharashtrian function.

Sumit.... going down on your knees and proposing (officially) Ketaki was really wonderful.



I would not be surprised to see a similar type of arrangement for his Wedding. Well that would sum up what Summit Kale is. A very practical and "Swatantra" Character as his uncle said. Swatantra as in "swatachya tantra ne " by what he wants.

But I liked it. Here two families have decided to join to-gether to become one big family. Generally at such functions the girl's relatives and boy's relatives would sit with respective families. Such functions are place for relatives to come together and update each other.

But for a change and that too a good one.. everyone was all ears to the relatives talking on the podium. Sumit had arranged for a couple of close relatives to introduce the couple. Some one close to Ketaki introduced her to Summit's relatives and vice a versa.

I must really say .. Sumit's uncle used all his skills to introduce Summit. He compere's for All India Radio. He did it very naturally and effortlessly. He blended day to day examples so very effectively that probably that everyone who attended the function now knows Summit as they have been knowing him for a long time .

I would be putting some quotes he used... in a days time or so...but I need to rush to earn my bread....

I too wanted to become nostalgic about the days we spent in Sheffield..Which i will in my next blog

CONGRATS SUMIT AND KETAKI .. You Both MAKE A WONDERFUL COUPLE !!!

Record Types , Object Types

DIFFERENT TYPE OF RECORDS IN PEOPLESOFT

0 Table
1 View
2 Derived/Work
3 Subrecord
5 Dynamic View
6 Query View
7 Temp table


______________________________________________________

OBJECT TYPES IN PEOPLESOFT

0 Record
1 Index
2 Field
3 Field Format
4 Translate Values
5 Page
6 Menu
7 Component
8 Record PC
9 Menu PC
10 Query View
11 Tree Structures
12 Trees
13 Access Group
14 Colors
15 Styles
16 Business Pro Map
17 Business Process
18 Activity
19 Roles
20 Process Definitions
21 Server Definitions
22 Process Type Definitions
23 Job Definitions
24 Recurrence Definitions
25 Message Catalog Entry
26 Dimensions
27 Analysis Model
28 Cube Template
29 Business Interlink
30 SQL
31 File Layout Definitions
32 Component Interface
33 App Engine Programs
34 App Engine Sections
35 Message Nodes
36 Message Channels
37 Messages
38 Approval Rule Sets
39 Message PC
40 Subscription PC
41 Not Used
42 Componenten Interface PC
43 Application Engine PC
44 Page PC
45 Page Field PC
46 Component PC
47 Component Record PC
48 Component Record Field PC
49 Image
50 Style Sheet
51 HTML
52 File Reference Objects
53 Permission List
54 Portal Registry Definitions
55 Portal Registry Structure
56 URL Definitions
57 Application Package
58 Application Package PC
59 Portal Registry User Homepages
60 Analytic Types
61 Archive Templates
62 XSLT
63 Portal Reg User Favorites
64 Mobile Page
65 Relationships
66 Component Interface Property PC
67 Optimization Model
68 File Reference
69 File Type Codes
70 Archive Object Definitions
71 Archive Templates
72 Diagnostic Plug-In
73 Analytic Models
74 Not Used
75 Java Portlet User Preferences
76 WSRP Remote Producer
77 WSRP Remote Portlet
78 WSRP Cloned Portlet Handle
79 Services
80 Service Operations
81 Service Operation Handlers
82 Service Operation Versions
83 Routing Defnitions
84 IB Queues
85 XMLP Template Defn
86 XMLP Report Defn
87 XMLP File Defn
88 XMLP Data Src Defn

Authroized Actions (PSAUTHITEM)

AUTHORIZED ACTIONS COLUMN IN THE PSAUTHITEM TABLE

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

Each possible authorized action has a corresponding decimal value. For all the authorized actions for a given menu item for a given operator, these numbers are added together and stored in the authorizedactions column.

For example, if the value for authorizedactions column is 143, the operator is authorized for all the actions. (1 2 4 8 128).

As another example, if the value is 7, the operator is authorized for the actions Add, Update/Display, Update/Display All (1 2 4).

---- Following part is not my writing -----------
An example of a number that seems to be too big to be translated is the following:
The number we will use in our example is 3604

This translates to 111000010100 in binary.
We take the lower 8 bits, which will be 00010100
then we use that to convert and find out what authorized actions the user has:

0 0 0 1 0 1 0 0
| | | | | | | -- Add
| | | | | | ---- Update / Display
| | | | | ------ U/D All
| | | | -------- Correction
| | | ----------
| | ------------ > Unused
| -------------- /
---------------- Data Entry




Also i have modifed the query a bit for SQL server as bitAnd does not work for SQL.


select classid, menuname, barname, baritemname, pnlitemname, displayonly,
case when bitand(authorizedactions,1) > 0 then 'Y' else 'N' END ADDACTION,
case when bitand(authorizedactions,2) > 0 then 'Y' else 'N' END UPDATEACTION,
case when bitand(authorizedactions,4) > 0 then 'Y' else 'N' END UPDATEALLACTION,
case when bitand(authorizedactions,8) > 0 then 'Y' else 'N' END CORRECTIONACTION,
case when authorizedactions > 15 then 'Y' else 'N' END SPECIAL
from psauthitem
where classid = 'ALLPAGES'

SELECT CLASSID, MENUNAME, BARNAME, BARITEMNAME, PNLITEMNAME,CASE WHEN (DISPLAYONLY & 1) > 0 THEN 'Y' ELSE 'N' END DISPLAYONLY,
CASE WHEN (cast(AUTHORIZEDACTIONS as int) & 1) > 0 THEN 'Y' ELSE 'N' END ADDACTION,
case when (cast(AUTHORIZEDACTIONS as int) & 2) > 0 then 'Y' else 'N' END UPDATEACTION,
case when (cast(AUTHORIZEDACTIONS as int) & 4) > 0 then 'Y' else 'N' END UPDATEALLACTION,
case when (cast(AUTHORIZEDACTIONS as int) & 8) > 0 then 'Y' else 'N' END CORRECTIONACTION,
CASE WHEN cast(AUTHORIZEDACTIONS as int) > 15 THEN 'Y' ELSE 'N' END SPECIAL
FROM PSAUTHITEM NOLOCK
WHERE CLASSID =

Find PeopleSoft Navigation

Create the following procedure and execute by passing the portal_objname to it.
This script is for MSS.

I know it is not very efficient but definately works.

USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[ShowNavigation] Script Date: 01/28/2008 01:01:37 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ShowNavigation]( @POBJNAME VARCHAR(30))AS
BEGIN
SET NOCOUNT ON
DECLARE @CNTR INT ,
@PLABEL1 VARCHAR (500),
@PRNTNAME VARCHAR (30),
@PLABEL varchar(90),
@PLABEL2 VARCHAR (50)

SELECT @CNTR = 1
SELECT @PLABEL1 = PORTAL_LABEL FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @POBJNAME;
SELECT @PRNTNAME = PORTAL_PRNTOBJNAME FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @POBJNAME;
SELECT @PLABEL2 = PORTAL_LABEL FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @PRNTNAME;
PRINT (@PLABEL1 + '- '+@PLABEL2)
BEGIN
WHILE @PRNTNAME <> 'PORTAL_ROOT_OBJECT'
BEGIN EXEC ShowNavigation @PRNTNAME
IF @PRNTNAME = 'PORTAL_ROOT_OBJECT'
BREAK;
SELECT @PRNTNAME = PORTAL_PRNTOBJNAME FROM
dbo.PSPRSMDEFN WHERE PORTAL_OBJNAME = @PRNTNAME;
SELECT @PLABEL = PORTAL_LABEL FROM PSPRSMDEFN WHERE
PORTAL_OBJNAME = @PRNTNAME;

END
END
END

Free Left Turns in PUNE ?

No, going by the law ... You cannot take a free left turn if the traffic authorities have provided a Signal specifically for taking a left turn.

Standing at the penultimate signal before Null Stop, Kothrud, Pune, this is what I witnessed couple of days back.

Two Traffic Custodians busy issuing receipts to those taking a left turn inspite of the STOP signal. But who were stopping the law breakers , a youth may be of 20-22 yrs and to his support a person who must have witnessed atleast 55 rainy seasons. Both civilians with whistles blowing and getting people to the side making them pay for the decision.

As I passed them i saw the youth in his eyes ... I felt like he was enjoying the authority the was given , but with the hope that he behaves with atleast traffic responsibility that he learnt that day.
I hope some day People of Pune learn to drive with some responsibility and respect to the three colours on the pole for their and more for their dependant families benefits.

All this was happening on LAW COLLEGE road in Pune.

Peoplesoft Upgrade

UPGRADE
Terms Used in Upgrade
CoP - Copy of Production
CCD - Copy of Current Demo (It is vanilla demo of the version in which the production is.)
NRD - New Release Demo
Before the Upgrade
1. The CoP and CCD should be at the same PeopleSoft Patch Levels.
2. Verify that Upgrade path is given by PeopleSoft
3. Find out and apply if necessary the Required for Upgrade Steps.
Navigation - PeopleSoft Customer Connect -
Implement, Optimize, and Upgrade » Upgrade Guide » Upgrade Documentation and Software » Upgrade Documentation and Scripts » Release
4. Step is extremely important as generally the UPGCONVERT project comes through the above link.
Some issues faced during Upgrade -
1. DBCC CHECKDB went into errors – Running it as part of the CA Upgrade Job
There 14 inconsistencies. This happens if the database restore does not happen smoothly, some block allocation/corruptions occurs.
a. Identified all the tables which went into error – There were 11
b. 8 of them had single errors and 3 of them had 2 errors each listed
c. Total no. of Inconsistencies were 14
d. Ran the below script for each Table and their indexes
e. USE UCP83
GO
DBCC UPDATEUSAGE ('<db name>', '<table 1>', '<table 2>')
2. Compare Fields
In this step in the Upgrade job we find if any PeopleSoft delivered tools field is touch/customized.
You have to merge PPLTOOLS and PPLTLS84CUR projects in to Compare_PPLTools project in the New Release Demo and do a compare
it with CoP. Care needs to be take to do a Project level compare and not a database level compare.
3. If a compare and report is run between two instance with wrong parameter like database is chosen instead of project then the project that was compared needs to be deleted and recreated. Read peoplebooks for understanding what compare and report actually does.
Details of fields in ProjectItem table.
Source Status - 1 - Absent
- 2 - Changed
- 3 - Unchanged
- 4 - *Changed
- 5 - *Unchanged
- 6 - Same
Above applies to TargetStatus
Upgrade Action - 0 - Copy
- 1 - Delete
- 2 - None
- 3 - Copy Prop
The above details can be used to script that change the actions that need to be taken when the project is copied.
For eg. If it is decided that everything that is customised by the client needs to be copied between database then
update PSPROJECTDEFN set UPGRADEACTION = 0 where SOURCESTATUS = 4

In the latest version of PeopleSoft , in the client status we see pool and not the list of users logged in . To see the user list :
modify web.xml and turn joltpooling entry to false.
/webserv//applications/peoplesoft/PORTAL/WEB-INF/web.xml

While altering tables we get error :- in SQL SERVER

String or binary data would be truncated.

The statement has been terminated.

We get the above error when peoplesoft wants to change the column datatype.
First it creates a dummy column , populates data , drops old column , rename dummy column to the original one.
We get the above error while converting text field to varchar(2000).
set ANSI_WARNING OFF will help us complete the scrip successfully.


After upgrade .. many times we get errors while opening pages - error like
first operand null, hmcr servcies .. this is related to campus solutions. To get rid..
open PERMISSION LIST attached to all users, go to web serivices and add services in question give full access.
Run the Refresh Cache process for the HCM Service Framework on Set Up HRMS, System Administration, HCM Registry, Service Registry, HCM Interface Registry page

SQR Flags

A. (-i) This flag tells SQRW where to look for any SQC subroutines that are included in your SQR using the "#INCLUDE" command. It is very important that the path contain a trailing backslash.
Example: -iS:\EP_Master\e752f70\sqr\
B. (-m) This flag tells SQRW where to look for the startup file ALLMAXES.MAX which contains processing limits for various internal parameters.
Example: -mS:\EP_Master\e752f70\sqr\allmaxes.max

C. (-ZIF) For SQR versions 4.x, this flag points to the location of your initialization file, PSSQR.INI.
Example: -ZIFs:\EP_Master\E752f70\sqr\pssqr.ini

D. (-o) This flag is used to define your SQR log file path and name.
Example: -oC:\temp\SQR.LOG

E. (-f) This flag points to the location where your output will be directed to. It is very important that the path contain a trailing backslash.
Example: -fC:\temp\


SQRW C:\psoft\PT855\custom\sqr\fixit.sqr DBNAME/USERNAME/PWD -iC:\psoft\PT855\sqr\ -ZIFC:\psoft\PT855\sqr\pssqr.ini -oC:\temp\fixit.LOG -fC:\temp\ ' ' 'param1' 'param2' param3_number