Commands to test COBOL server express

$PS_HOME/bin> ./ptpdbtst
$PS_HOME/bin> cobrun
$PS_HOME/bin> cob -v
$PS_HOME/bin> cob -V
$PS_HOME/bin> ls -l PSRUN*

Oracle cold backup database refresh

1.  Prepare controlfile creation script.  This can be done by

alter database backup controlfile to trace as '/export/home/oracle/ctlfile.sql';

2.  View the edit ctlfile.sql, so that it should contain the following

CREATE CONTROLFILE SET DATABASE "UATDB" RESETLOGS NOARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 1
  MAXLOGHISTORY 453
LOGFILE
  GROUP 1 '/u01/oradata/UATDB/redo01.log' SIZE 200M,
  GROUP 2 '/u02/oradata/UATDB/redo02.log' SIZE 200M,
  GROUP 3 '/u03/oradata/UATDB/redo03.log' SIZE 200M
-- STANDBY LOGFILE
DATAFILE
  '/u01/oradata/UATDB/system01.dbf',
  '/u01/oradata/UATDB/undotbs01.dbf',
  '/u01/oradata/UATDB/users01.dbf',
  '/u01/oradata/UATDB/data01.dbf',
  '/u01/oradata/UATDB/tools01.dbf',
  '/u01/oradata/UATDB/perfstat01.dbf',
  '/u01/oradata/UATDB/sysaux01.dbf'
CHARACTER SET WE8ISO8859P1
;

3. Gernerate script to create TEMP tablespace

set long 2000
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

4. Shutdown UATDB cleanly

shutdown immediate

5. Copy the cold backup files from production to UAT box.

6. Start the database in nomount state

startup nomount

7. Run the create control file script

@/export/home/oracle/ctlfile.sql

8. Open the database with resetlogs

alter database open resetlogs;

9. Create temp tablespace from the script generated from step 3:

CREATE TEMPORARY TABLESPACE "TEMP1" TEMPFILE
'/u01/oradata/UATDB/TEMP01.dbf' SIZE 5242880000
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

PeopleSoft database refresh steps

Below are steps can we followed for pre and post refresh of PeopleSoft application environment.Please also  copy the SQR,COBOL,Crystal for file object refresh.

1. Export any security that you need to preserve from the target database to a flat file. Data Mover is a nice tool to use for this since you can qualify each table with a list of operator ID's to export. The tables you should consider exporting for specific users are PSOPRDEFN, PSOPRALIAS, PSROLEUSER, PSUSERATTR, PSUSEREMAIL, PSUSERPRSNLOPTN, PS_ROLEXLATOPR and PS_RTE_CNTL_RUSER.
2. Stop the target application environment, including application servers and process schedulers. Stopping the web server is optional since it doesn't connect directly to the database. Be sure to clear cache.
3. Overlay the target database with a recent backup of the Production database. The exact process will differ for your database platform, but your DBA should be able to do this with minimal direction.
4. After the database comes back on-line, set DBNAME in PSDBOWNER back to the target database name.
5. Set GUID to ' ' in the PSOPTIONS table. This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.
6. Delete the data from the reporting tables, process scheduler tables and application messaging tables since this data isn't relevant in the target database. Run prcsclr.dms, rptclr.dms and appmsgpurgeall.dms. In addition, I also delete from the report manager tables PSRF_RATTR_TBL; PSRF_RSCRTY_TBL; PSRF_RINFO_TBL; and delete from PSRF_FINFO_TBL where PSRF_PRNT_FLDR_ID <> 0;
7. If you have a script to reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users, run it now. It should update the PSUSEREMAIL and PS_ROLEXLATOPR tables.
8. Log on to the target database with data mover and run the following command to set the SYSADM password and any other back to it's pre-refreshed value:
   -- Author: Brent Martin, ERP Associates Inc.
   -- Date: February 10, 2006
   -- Purpose: Reset application passwords and purge PeopleTools tables after a database refresh so that
   -- the application can be started with minimal reconfiguration.
   -- Compatibility: This works with PeopleTools 8.4x releases
   -- Notice: This script modifies data in your PeopleSoft database. It changes account passwords, and
   -- it deletes data from process scheduler, report repository and integration broker tables.
   -- It as provided for reference purposes only. Use it at your own risk.
   
   set log c:\temp\dbrefresh.log;
   
   -- Change Access Password (SYSADM)
   CHANGE_ACCESS_PASSWORD SYSADM1 CHANGEME1;
   
   -- Change Application Password (PSAPPS)
   update psoprdefn set OPERPSWD = 'changeme1', encrypted = 0 where oprid = 'PSAPPS';
   encrypt_password PSAPPS;
   
   -- Set GUID to blank so PSEMAgent and Change Assistant doesn't get confused
   update psoptions set guid = ' ';
   
   -- Purge Process Scheduler
   RUN h:\fdmo881\scripts\PRCSCLR.DMS;
   
   -- Purge Report Repository Tables
   RUN h:\fdmo881\scripts\RPTCLR.DMS;
   
   -- Purge Application Messaging RUN h:\fdmo881\scripts\APPMSGPURGEALL.DMS;
9. Import the security that you exported in step 1.
10. While you're in data mover, change the user passwords that are configured in your application server, process scheduler, and integration broker configurations back to the pre-refresh values:
    update psoprdefn set OPERPSWD = 'devpswd', encrypted = 0 where oprid = ('PSAPPS');
    encrypt_password PSAPPS;
    update psoprdefn set OPERPSWD = 'devpswd', encrypted = 0 where oprid = ('PTWEBSERVER');
    encrypt_password PTWEBSERVER;
11. Clear application server, web server and process scheduler cache if you haven't already done it. Start the target application environment.
12. Log on through the web front end.
13. Update the Report Node configuration and verify your Process Scheduler Servers are using the correct configuration. Make any Web Profile changes if needed.
14. Navigate to PeopleTools > Utilities > Options and update the database name and description.
15. Change password rules as appropriate for a development environment.
16. Change your default local node if it should be named differently than the default local node from the source database. If it's not named differently, you may be at risk for a single sign-on vulnerability.
17. Reconfigure attachment servers (if used)
18. Reconfigure REN servers and clusters if the application server didn't configure it correctly at startup.
19. Make sure you copy all of the batch objects (SQR's, Crystal Reports, COBOL programs, etc.) from your source to your target environment to keep the environment in synch. Don't forget to do this on both UNIX and Windows servers.
20. Perform some sanity checks to make sure the environment is behaving itself before you let users back into it. At a minimum you should verify you can log on and run a report. Verify the report runs to completion, posts, and allows you to view the report without having to sign in a second time.

Windows COBOL compilation(Net Express)

Below are the commands to compile cobol on windows.The .gnt files - executable version of cobol will be generated after compilation and copied to CBLBINU for unicode version and CBLBIN for non unicode . Adjust the paths as necessary.


set PS_HOME=c:\pshome\hcm90

set PATH=%ps_home%\src\cbl\win32;%path%

set COBROOT="c:\program files\micro focus\net express\base"

cd %PS_HOME%\setup

cblbld c: \temp\compile

(make sure you have a space between the c: and the \temp\compile)

Here is the output:

ASCII Cobol Compilations Proceeding

Creating Directory c:\temp\compile

Logging progress to file c:\temp\compile\CBLBLD.LOG

COBOL compiler found in “c:\program files\micro focus\net express\base”  -- note the " and space

The system cannot find the path specified.

Target directory (c:\pshome\hcm90\CBLBINA) exists and is writable



Copying source files …

useful EXCEL formula to generate SQL DDL/grant scripts

TO generate update script:

---------------------------
Enter the formula as below to generate the multiple statement and drag colums to generate DDL statements
=CONCATENATE($E$3,A2,"';")
Where
$E$3 Excel cell -->update psoprdefn set operpswd = ' ' where oprid='  (or similar insert statement)
A2 Excel  cell --> list table names or user name
“’;” --> is a string


To Generate role and permision and grants
----------------------------------------------
="grant select, update, delete, insert on " & A1 & " to user_role;"
="create public synonym " & A1 & " for sysadm." & A1 & ";"


Where
& A1 & Excel column  contains the table list

Purging Web Server Cache with out shutdown

We'll be using the "Purge" servlet directive to make it happen.
To set up your system for servlet directives you need to specify a password in your web profile. So open your web profile (PeopleTools > Web Profile > Web Profile Configuration), navigate to the Custom Properties tab, and look for a property called auditPWD. It should be set to a string, and the property value should be whatever password you want to set. The default is "dayoff". If you make any changes, you'll have to bounce your web server for it to take effect.
Once the auditPWD property is in place, you can simply call the psp servlet with the purge command, and your web server cache will be purged. The syntax of the URL is: http://[server]/psp/[site]/?cmd=purge&pwd=[password]
For example: http://psoft.rajpandian.com:6500/psp/HDEV/?cmd=purge&pwd=dayoff.
Anyway, update the URL with your specific info, paste it into your browser, and the web server cache will be cleared without a web server bounce.
As always, make sure you test these techniques in a non-production database, and use at your own risk! I hope you find them helpful.

PeopleSoft troubleshooting and tracing

Application Engine
Tracing
Use the -TRACE option to trace the SQL executed by an application engine program. You can do this either through the command line debugger or in the override parameters of the process definition.

To trace the PeopleCode executed by an application engine program use the -TOOLSTRACEPC option.

These are the various tracing options and what they do. Like all other PeopleSoft trace flags these are bit fields so if you want trace values 1, 2 and 4, you would set the trace to 1 + 2 + 4 = 7 (-TRACE 7).

Value Trace Setting
0 Disables tracing.
1 Initiates the Application Engine step trace.
2 Initiates the Application Engine SQL trace.
4 Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128 Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256 Initiates the PeopleCode detail to the file for the timings trace.
1024 Initiates the statement timings trace, but stores the results in the PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL tables.
2048 Requests a database optimizer trace file.
4096 Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192 Sets a trace for PeopleSoft Integration Broker transform programs.

Note that output from the -TRACE flag goes to the application engine trace (.AET) file. Output from the -TOOLSTRACEPC output goes to the PeopleTools trace file (.TRC).

Command Line Debugging
To run application engine in debug mode, run the following from the command line of %PS_HOME%/bin/client/winx86. You may need to install peopletools locally first.

psae.exe -CT DatabaseType -CD DatabaseInstance -CO OperatorID -CP OperatorPassword
-R RunControl -I ProcessInstance -AI ApplicationEngineProgramName -DEBUG Y

The app engine will log into the database and present you with the following prompt:

PeopleTools 8.48.12 - Application Engine
Copyright (c) 1988-2007 PeopleSoft, Inc.
All Rights Reserved

Application Engine Debugger - enter command or type ? for help.

{APPENGINENAME}.{SECTION}.{FIRST_STEP}>

Note if you get an error about the environment variable PS_SERVER_CFG not being set, you will need to set up the following environment variables:

PS_SERVDIR pointing to your process scheduler location (e.g. %PS_HOME%\appserv\prcs\CS90)
PS_SERVER_CFG pointing to your process scheduler configuration file (e.g. %PS_SERVDIR%\psprcs.cfg)
You can view the current values in the state record, step into code - which will take you into app designer if it is a PeopleCode step. But the best feature is that it allows you to modify the contents of a state record.

Note that the following commands are available in the debugger:

Debug Commands:

(Q)uit Rollback work and end program
E(X)it Commit work and end program (valid between steps)
(C)ommit Commit work (valid between steps)
(B)reak Set or remove a break point
(L)ook Examine state record fields
(M)odify Change a state record field
(W)atch Set or remove a watch field
(S)tep over Execute current step or action and stop
Step (I)nto Go inside current step or called section and stop
Step (O)ut of Execute rest of step or called section and stop
(G)o Resume execution
(R)un to commit Resume execution and stop after next commit

COBOL
This article on COBOL SQL Tracing explains how to enable tracing on a COBOL process definition.

PeopleCode
Tracing PeopleCode
In the set trace flags on the signon page use Each Statement or through PeopleTools -> Utilities -> Debug -> Trace PeopleCode use Show Each to log all the PeopleCode that fires during your trace. This will tell you what PeopleCode and events are firing (and what is not firing) and is usually enough to give you an idea of what is going on. The best thing is that this trace produces a much smaller log file than selecting most of the other PeopleCode trace settings, making the PeopleCode trace manageable!

Writing to a log file
Here's a simple example of how to log to a file. Obivously its not a good idea to use hardcoded absolute paths so adjust this code accordingly.

&fileLog = GetFile("C:\temp\LOGFILE.log", "w", "a", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("End");
&fileLog.Close();

PeopleTools security records

A summary of the main PeopleTools security records.

Operators
Record Description
PSOPRDEFN
Stores all operators (users) in the PeopleSoft system. Also stores their employee ID (EMPLID), encrypted password, primary permission list, default navigator home page, process profile permission list and row security permission list.

PSROLEUSER
The highest level of security access is defined by roles (think of them as groups). This table stores the roles the user belongs to.
PSOPRCLS Roles link together permission lists which are the security objects that define access to components, pages, and other areas of the system. This view returns the permission lists that a user has access to via their roles. Note that prior to PeopleTools 8, permission lists were synonymous with classes and most of the security tables still use this convention.

PSOPRALIAS
Aliases can be mapped to a particular operator ID (user). The obvious alias is employee ID (EMPLID) but others include external organisation ID (EXT_ORG_ID) and customer ID (CUST_ID). All ways of referring to the same entity.

PSOPRALIASTYPE
This is the setup table for operator aliases

PSOPRALIASFIELD
This is the setup table that maps operator aliases to records & fields

PSUSERATTR
User attributes store the a hint password question & response for a user (if this is enabled)

PSUSEREMAIL Email addresses for users.

Roles
======
Record Description
PSROLEDEFN
Stores roles and their properties. Roles can be assigned dynamically through Query, PeopleCode or LDAP. Roles are also used in conjunction with Workflow and routing.
PSROLECLASS
Roles are made of up of one or permission lists, and this table links the two together. Very handy.

Permission Lists
=================
Record Description
PSCLASSDEFN
Permission lists are where the security really happens. They provide access to menus, components and pages and a host of other security including PeopleTools, Process security, Component Interfaces, Web Libraries, Web Services, Personalisations, Query and Mass Change.
PSAUTHITEM
The link between permission lists and menus
PSAUTHBUSCOMP The link between permission lists and component interfaces and their methods
PSAUTHOPTN The link between permission lists and personalisations
PSAUTHPRCS The link between permission lists and process groups
PSAUTHSIGNON The link between permission lists and signon times
PSAUTHWEBLIBVW A view linking permission lists and access to web libraries (really just Menus in PSAUTHITEM that begin with WEBLIB_).
PSAUTHWS The link between permission lists and web services (service operations)
PS_SCRTY_ACC_GRP The link between permission lists, trees and query access groups
PS_MC_OPR_SECURITY The link between permission lists and mass change templates. This is an odd table, it uses the field OPRID but really it links permission lists

Check out this forum post for details about how to check which users have access to PeopleTools.

Portal
The PeopleSoft Enterprise Portal basically refers to the navigation system that links menus and components to the PeopleSoft online interface, navigation and search functionality. Access to anything in the online application is handled through portal security. The records associated with this include:

Record Description
PSPRSMDEFN Stores the structure of the portal registry. This data is stored in a hierarchical (tree) structure within the table. The field PORTAL_URI_SEG1 is the menu, PORTAL_URI_SEG2 is the component, and PORTAL_URI_SEG3 is the market.
PSPRSMPERM Stores permission lists associated with access to everything within the portal registry

which users have access to a particular page

Find out which users (and from what permission lists) have access to a particular page:

select distinct OPRID, OPRCLASS
from PSOPRCLS
where OPRCLASS in (
select distinct CLASSID
from sysadm.PSAUTHITEM
where PNLITEMNAME = ''
);

Query to find processes by process status

The following query will give you a summary of the requested processes by process status.

select
RQST.RUNSTATUS,
RQST.PRCSTYPE,
(
select XLAT.XLATLONGNAME
from PSXLATITEM XLAT
where XLAT.EFFDT = (
select max(XLAT_ED.EFFDT)
from PSXLATITEM XLAT_ED
where XLAT_ED.FIELDNAME = XLAT.FIELDNAME
and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE
) and XLAT.FIELDNAME = 'RUNSTATUS'
and XLAT.FIELDVALUE = RQST.RUNSTATUS
) as RUNSTATUS_XLAT,
count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,
min(RUNDTTM) as FIRST_OCCURRED,
max(RUNDTTM) as LAST_OCCURRED
from PSPRCSRQST RQST
group by RQST.RUNSTATUS, RQST.PRCSTYPE
order by RUNSTATUS_XLAT, RQST.PRCSTYPE

Application Engine programs only stuck at queued

You may find that only application engine programs are stuck at queued while other processes (SQRs, crystals etc) run to success. This typically happens due to processes blocking the process scheduler queue. Check the process scheduler/master process scheduler logs. You might see something like this:

Checking Process cancels...
(NET.113): Client ChkAeStatus3 service request succeeded
Process 2319373 is still running as Session ID 19955
(NET.113): Client ChkAeStatus1 service request succeeded
Process 2319395 is still running as Session ID 19946
(NET.113): Client ChkAeStatus2 service request succeeded
Process 2319404 is still running as Session ID 19950
Application Engine : 3:3
Requests found in Process Request table 3

This indicates that the three process instances, 2319373, 2319395 and 2319404 are all running. As there is a maximum of 3 application engine programs that can run at any one time and there are currently 3 running, all other application engine programs requested will remained at queued. However, the three process instances may not actually be running. If this is the case, these will need to be manually stopped, for example with a process scheduler restart and perhaps by manually killing the processes on the process scheduler server if required.

Process run status

In the PeopleTools 8.4x look at the translates on the field RUNSTATUS or use the query.

select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'

Here's a summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.

Value Status
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart

The following query will give you a summary of the process run statuses in your process request table:

select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;

Processes stuck at Queued

There are a number of reasons why a process might be stuck at queued. The most obvious is that the process scheduler is down (check the Servers tab in the process monitor). Beyond this, there are some useful troubleshooting that apply:

Check the following tables:

PSPRCSRQST
PSPRCSQUE
PSPRCSPARMS

The row count should be the same in both tables. If one is out of sync with the other, then it can help to remove orphaned instances in of the tables. Restarting the process scheduler and clearing the process scheduler cache will also fix a number of issues.

EMI calculation using Excel

I mentioned the formula used to calculate an EMI, but putting values in the formula and calculating it is a cumbersome task. So I did some research in Microsoft Excel and found out that it can done quite easily in excel.
EMI calculation in Excel
Step 1: Open the excel sheet and locate the fx button

Step 2: In the pop-up menu, click on Financial Catergory

Step 3: In the Function Name click 'PMT'

Step 4: A box will appear as shown, fill in the values mentioned and you get the EMI.

Good Amazon deals site

I found this site has good one to buy from Amazon

www.deal4low.com