Tag Archives: SQL Server

PeopleSoft HR 8.9 Installation Steps

Posted by on August 28, 2009 No comments

Hardware/Software Specifications

Server Hostname

Server Description IP Address
GLWEBAPP GL Web & App Server 10.x.x.x
GLDBASE GL Database Server 10.x.x.x

The machines that were used in the installation of the PeopleSoft test environments were Dell PowerEdge 2950.

The operating system for both servers was Microsoft Windows Server 2003 R2 Enterprise Edition Service Pack 2.

The GLDBASE machine shall house only the database server software (Microsoft SQL Server 2005).

The GLWEBAPP machine shall house all the other server software:

  • File Server (PeopleTools)
  • Application Server (Tuxedo)
  • Web Server (IBM WebSphere)
  • Batch Server

These two servers were accessed remotely using Remote Desktop Connection. Local administrator accounts were set up on these two machines. The username for these accounts were ProjAdmin1, ProjAdmin2 and ProjAdmin3.

Installation Sequence

The following steps were performed in this sequence to install the PeopleSoft Demo instance:

 PeopleSoft Demo Installation (FSDMO)

  1. Install SQL Server [ok 10-Nov-08]
  2. Install PeopleTools 8.49 on Database Server [17-Nov-08]
  3. Install PeopleTools 8.49 on Application Server [ok 11-Nov-08]
  4. Install Application (Financials) on Database Server [ok 17-Nov-08]
  5. Install Application (Financials) on Application Server [ok 11-Nov-08]
  6. Install Tuxedo (Application Server) [ok 11-Nov-08]
  7. Install PeopleTools 8.49.15 Product Patch on Database Server [ok 17-Nov-08]
  8. Install PeopleTools 8.49.15 Product Patch on Application Server [ok 12-Nov-08]
  9. Install IBM WebSphere (Web Server) [ok 11-Nov-08]
  10. Create Database Manually [ok 18-Nov-08]
  11. Shutdown, Backup Database After Creating
  12. Install PIA for WebSphere (fsdmo) [ok 18-Nov-08] [reinstall WebLogic 28-Nov-08]
  13. Create Application Domain [ok 18-Nov-08]
  14. Install PeopleTools PeopleBooks [ok 18-Nov-08] [reinstall 1-Dec-08]
  15. Install Application PeopleBooks [ok 18-Nov-08] [reinstall 1-Dec-08]
  16. Create Process Scheduler [ok 18-Nov-08]
  17. Apply Maintenance Pack 5 (Financials)

PeopleSoft Demo Installation (FSDEMO)

This FSDEMO installation shall be a plain-vanilla installation (without MP5).

  1. Install SQL Server [already installed]
  2. Install PeopleTools 8.49 on Application Server [ok 15-Dec-08] [E:PSOFTFSDEMO]
  3. Install PeopleTools 8.49 on Database Server [ok 15-Dec-08] [D:PSOFTFSDEMO]
  4. Install Application CDs (Financials) on Database Server [ok 15-Dec-08]
  5. Install Application CDs (Financials) on Application Server [ok 15-Dec-08]
  6. Install PeopleTools 8.49.15 Product Patch on Database Server [ok 15-Dec-08]
  7. Install PeopleTools 8.49.15 Product Patch on Application Server [ok 15-Dec-08]
  8. Install Tuxedo CD (Application Server) [already installed in E:bea]
  9. Install IBM WebSphere CD (Web Server) [already installed in E:IBMWebSphere]
  10. Create Database Manually [ok 18-Nov-08]
  11. Shutdown, Backup Database After Creating
  12. Install PIA for WebSphere (fsdmo) [ok 18-Nov-08] [reinstall WebLogic 28-Nov-08]
  13. Create Application Domain [ok 18-Nov-08]
  14. Install PeopleTools PeopleBooks [ok 18-Nov-08] [reinstall 1-Dec-08]
  15. Install Application PeopleBooks [ok 18-Nov-08] [reinstall 1-Dec-08]
  16. Create Process Scheduler [ok 18-Nov-08]
  17. Apply Maintenance Pack 5 (Financials)

PeopleSoft Sys Installation

  1. Install SQL Server [ok 10-Nov-08]
  2. Install PeopleTools 8.49 on Database Server [17-Nov-08]
  3. Install PeopleTools 8.49 on Application Server [ok 11-Nov-08]
  4. Install Application (Financials) on Database Server [ok 17-Nov-08]
  5. Install Application (Financials) on Application Server [ok 11-Nov-08]
  6. Install Tuxedo (Application Server) [ok 11-Nov-08]
  7. Install PeopleTools 8.49.15 Product Patch on Database Server [ok 17-Nov-08]
  8. Install PeopleTools 8.49.15 Product Patch on Application Server [ok 12-Nov-08]
  9. Install IBM WebSphere (Web Server) [ok 11-Nov-08]
  10. Create Database Manually [ok 18-Nov-08]
  11. Install PIA for WebSphere (fsdmo) [ok 18-Nov-08]
  12. Create Application Domain [ok 18-Nov-08]
  13. Install PeopleTools PeopleBooks [ok 18-Nov-08]
  14. Install Application PeopleBooks [ok 18-Nov-08]
  15. Create Process Scheduler
  16. Install Crystal Reports (on Workstation)
  17. Install Change Assistant
  18. Apply Maintenance Pack 5 (Financials)

Precedence

The SQL Server software must be installed before the PeopleTools.

PeopleTools Product Patch must be installed before PIA.

Step 1: SQL Server Installation

The Microsoft SQL Server 2005 DVD was staged (that is, copied) to D:stagingSQLServer2005 on the GLDBASE machine. After staging, setup.exe from D:stagingSQLServer2005Servers was run.

By default, the following required components were installed prior to installing SQL Server:

  • .NET Framework 2.0
  • Microsoft SQL Native Client
  • Microsoft SQL Server 2005 Setup Support Files

These components were installed to C:Program FilesMicrosoft SQL Server by default.

The other SQL Server components were installed to D:Program FilesMicrosoft SQL Server. These components are:

  • Database Services
    • Data Files
    • Replication
    • Full-Text Search
    • Shared Tools
  • Client Components
    • Connectivity Components
    • Management Tools

The Microsoft SQL Native Client (SNaC) must also be installed on the GLWEBAPP machine in which PeopleTools is installed. To do so, share the D:staging directory on GLDBASE (10.18.14.31). From GLWEBAPP, navigate to \10.x.x.xstagingSQLServer2005Servers. Then run setup.exe.

Allow the required components to install (.NET Framework 2.0, Microsoft SQL Native Client and Microsoft SQL Server 2005 Setup Support Files). Continue the installation and install the Connectivity Components on GLWEBAPP.

Step 2: File Server (PeopleTools) Installation

It was necessary to install up the SQL Server Connectivity Components on the GLWEBAPP because the SQL Server Connectivity Program Directory must be specified as part of the installation.

The seven PeopleTools CDs were staged to the E:stagingCDImages

Note that you MUST install PeopleTools on both nodes.

The following values/options were set as part of the PeopleTools installation on the peoplesoft4hr node:

Variable Value
PeopleSoft (PeopleTools) License code z66smq-22c7mc-3w444e1-2is234-81p1f7
Type of Database Non-Unicode Database
Peoplesoft Application Server Checked
Peoplesoft Batch Server Checked
Peoplesoft Database Server Checked
Peoplesoft File Server Checked
Peoplesoft Web Server Checked
PS_HOME Drectory Name E:PSOFTHR900
ERP Connectors? No
Connectivity Program Directory C:Program FilesMicrosoft SQL Server80Toolsbinn
Install PeopleTools Icons? Yes
PeopleTools Program Group Folder PeopleTools 8.4
Environment Hub machine name Peoplesoft4hr
Environment Hub Port Number 8000
PeopleTools (Product Installation) Checked
PeopleTools System Database Checked
PeopleTools Language Pack Not checked
PeopleTools Language Development Kit Not checked

Step 3: Application Installation

To install the PeopleSoft Enterprise Human Resources Management System and Campus Solutions 9.0 (HR) application, the disk was obtained from the CD. It was unzipped (“exploded”) and staged to the F:stagingcd849-HR directory, in the disk1 directory.

To install the Financials application, navigate to F:stagingcd849-HRdisk1 directory and run setup.exe

The following values/options were entered

Variable Value
PeopleSoft (HRMS) License code
Type of Database Non-Unicode Database
PeopleSoft Application Server Checked
PeopleSoft Batch Server Checked
PeopleSoft Database Server Checked
PeopleSoft File Server Checked
PeopleSoft Web Server Checked
PS_HOME Directory Name E:PSOFTFS900

Step 4: Application Server (Tuxedo) Installation

The Tuxedo 9.1 CD (Windows version) was staged to E:stagingCDImagescd849-Tuxdisk1 on the GLWEBAPP server. To install Tuxedo, run pstuxinstall.exe. This brings up a console interface (as opposed to a GUI wizard).

The following prompts appeared when psstuxinstall.exe is run:

1 file(s) copied.

1 file(s) copied.

BEA Home being defaulted to C:\bea  (y/n)? [y]: n

Please provide a BEA Home: E:\bea

Tuxedo 9.1 will be installed to E:\beaTuxedo9.1  (y/n)? [y]: <Enter>

TListen port being defaulted to 3050  (y/n)? [y]: <Enter>

Accept default TListen password ‘password’  (y/n)? [y]: <Enter>

Are you happy with your selections (y/n)? [y]: <Enter>

Tuxedo 9.1 is being installed on your system….

Tuxedo 9.1 base install completed.

Stopping Tuxedo 9.1 Services…

The TListen 9.1 (Port: 3050) service is stopping..

The TListen 9.1 (Port: 3050) service was stopped successfully.

The BEA ProcMGR V9.1 service is stopping.

The BEA ProcMGR V9.1 service was stopped successfully.

Patch installation completed.

Please note that you must reboot your system before using any Tuxedo 9.1 or PeopleSoft applications.

Press any key to continue…

After installing Tuxedo, the GLWEBAPP server was rebooted remotely.

Step 5: Web Server (WebSphere) Installation

The two IBM WebSphere CDs (Windows version) were staged to the E:stagingCDImagescd849-WebSphere directories, in the disk1 and disk2 directories.

To install IBM WebSphere, navigate to disk1 and run installWAS.bat.

Install to E:IBMWebSphereAppServer

For Environments, choose None, then click Yes to accept the choice.

In the Summary screen, click Next.

Uncheck the Create a new WebSphere Application Server profile, and then click Finish.

Step 6: PeopleTools 8.49.15 Product Patch Installation

Check Customer Connection (now Metalink3 at http://metalink3.oracle.com) for the latest PeopleTools 8.49 Product Patch. As at this time, the latest PeopleTools Product Patch for version 8.49 is 8.49.15.

Download this patch 84915.exe from:

ftp://ftp.peoplesoft.com/outgoing/ptools/84915/84915.exe

(The password for 84915.exe is ‘MisterEd’)

Run 84915.exe and unzip the file to E:stagingCDImages. It automatically creates the cd84915 directory and unzips the file.

From E:stagingCDImagescd84915disk1, run setup.exe. It updates PS_HOME (E:PSOFTFS900) with the updated files.

Step 7: Pure Internet Architecture (PIA) Installation

To create the PIA, change directory to E:PSOFTFS900PsMpPIAInstall and run setup.exe

Install PIA to the IBM WebSphere web server, located in E:IBMWEBSPHEREAppServer.

Application name and web site name should be set to the name of the environment e.g. fsdmo. Create a Multi Server installation, not a Single Server installation.

Note that the port numbers for http and https must not be consecutive.

Web Profile Name, User ID and Password should remain the default.

The Report Repository should be set to E:psreports.

The installation process takes about ten minutes to complete.

PeopleSoft HR 8.9 Installation Checklist

Posted by on August 28, 2009 No comments

1. Install SQL Server

2. Install SQL Server Native Client

3. Stage PeopleTools 8.49 CDs on F:stagingpt-849

4. Install PeopleTools 8.49 on peoplesoft4hr (E:PSOFTHR900)

5. Install Application (Human Resources) on peoplesoft4hr

6. Install Database Manually

7. Task B-1: Running Server Configuration Scripts

8. Task B-2: Creating a Database

9. Task B-3: Configuring ODBC Data Source

10. Task B-4: Running ADDOBJ.SQL

11. Task B-5: Setting Up Connect ID

12. Task B-6: Creating Data Mover Import Scripts

13. Task B-7: Running Data Mover Import Scripts

14. Task B-8: Updating Database to Latest PeopleTools Release

15. Task B-8-2: Updating PeopleTools System Tables

16. Task B-8-3: Updating PeopleTools Database Objects

17. Task B-8-5: Deleting Obsolete PeopleTools Database Objects

18. Task B-8-6: Altering PeopleTools Tables

19. Task B-8-7: Updating PeopleTools System Data

20. Task B-8-8: Running PeopleTools Conversions

21. Task B-9: Running Additional Data Mover Scripts

22. Task B-11: Running VERSION Application Engine Program

23. Task B-12: Changing the Base Language

24. Task B-13: Running SQR Reports

25. Task B-14: Checking the Database

26. Task B-15: Cleaning and Backing Up the Database

27. Shutdown, Backup Database After Creating

28. Install Tuxedo (Application Server)

29. Install BEA WebLogic 9.2 Web Server

30. Install PIA

31. Create Application Domain

32. Install PeopleTools PeopleBooks

33. Install Application PeopleBooks

34. Create Process Scheduler

35. Install COBOL compiler (and license)

36. Execute PTPATCH.DMS using Data Mover (8.49.15)

37. Apply project PATCH849 using Application Designer

38. Run DDDAUDIT, SYSAUDIT to verify system ok, troubleshoot if necessary

39. Shutdown all servers and backup PS_HOME

(IBRK-10): IB Routing referencing invalid handlers

Posted by on January 31, 2009 Comments Off on (IBRK-10): IB Routing referencing invalid handlers

Problem:

After running the SYSAUDIT report, the following errors appear under Integration Broker Audits:

(IBRK-10): IB Routing referencing invalid handlers:
Routing On Send Handler On Receive Handler
 - ------ --------------- ------------------
 ~GENERATED~17288 ACK
 1 Total Rows

Solution:

Log on to SQL Server Management Studio and run the following queries against fsdev:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 6 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-30 11:38:38.000 VP1 PPT GENERATED NULL
(1 row(s) affected)
ONRCVHDLRNAME COUNT(*)
 ------------------------------ -----------
 1500
 ACK 1
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
(1 row(s) affected)

On fsdmo, ran the following queries:

select * from PSIBRTNGDEFN where ROUTINGDEFNNAME = '~GENERATED~17288'
select * from PSIBRTNGDEFN where ONRCVHDLRNAME = 'ACK'
select ONRCVHDLRNAME, count(*) from PSIBRTNGDEFN group by ONRCVHDLRNAME
select * from PSSRVHDLRRCV_VW

The outputs were:

ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(1 row(s) affected)
ROUTINGDEFNNAME EFFDT VERSION EFF_STATUS SENDERNODENAME RECEIVERNODENAME RTNGTYPE IB_SYNCHNONBLOCK IB_OPERATIONNAME VERSIONNAME CONNOVERRIDE CONNGATEWAYID CONNID LOGMSGDTLFLG ONSNDHDLRNAME ONRCVHDLRNAME GENERATED IB_LOCALIDFLG LASTUPDDTTM LASTUPDOPRID OBJECTOWNERID DESCR DESCRLONG
 ------------------------------ ----------------------- ----------- ---------- ---------------------------------------------------------
 CREATE_WORKLIST_ITEM 2005-10-21 00:00:00.000 1 I ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-23 11:11:56.000 PPLSOFT Create worklist entry NULL
 ~GENERATED~17288 2005-10-21 00:00:00.000 1 A ~~ANY~~ PSFT_EP R N CREATE_WORKLIST_ITEM V1 N 2 ACK Y 2 2008-12-18 16:04:16.000 PPLSOFT PPT GENERATED NULL
(2 row(s) affected)
ONRCVHDLRNAME
 ------------------------------ -----------
 1513
 ACK 2
(2 row(s) affected)
IB_OPERATIONNAME ONRCVHDLRNAME DESCR
 ------------------------------ ------------------------------ ---------------------------
 CREATE_WORKLIST_ITEM ACK
 CREATE_WORKLIST_ITEM OnAckForMarkedWorkedResp Create worklist entry
 (2 row(s) affected)

Note that in PSSRVHDLRRCV_VW, there is a new row for ACK.

1. The simplest solution, however, is to delete the offending routing, ~GENERATED~17288.

2. In PIA, navigate to Integration Broker > Service Utilities > Service Administration. Select the Routings tab, and open the Delete section. Enter ~GENERATED~17288 into the Routing Name field and click Search. Check Select and then click Delete.

Under Results, the following message appears: Unable to delete. Routing belongs to a restricted service.

3. One way to resolve this is to change the restricted service to unrestricted. Navigate to Integration Broker > Configuration > Service Configuration. Click the Restricted Services tab, and search for PT_WORKLIST. Uncheck Restricted, and then Save.

4. Determine the field that is used as the lookup. Navigate to Integration Broker > Integration Setup > Routings. In the Find an Existing Value tab, in Routing Name, enter ~GENERATED~17288 and click Search.

5. Type Ctrl + J and note the Page name: IB_ROUTINGDEFN

6. On GL-WEB-APP, launch Application Designer and click Insert Definitions into Project. Choose Page and enter IB_ROUTINGDEFN. Choose IB_ROUTINGDEFN and click Close.

7. Double-click IB_ROUTINGDEFN. Right-click on the OnReceive Handler field and choose View Definition. On the ONRCVHDLRNAME field, right click and choose Record Field Properties. Click the Edits tab and note the Prompt Table: PSSRVHDLRRCV_VW.

8. In Management Studio, for the fsdev database, expand the Views navigator and locate PSSRVHDLRRCV_VW. Right click on PSSRVHDLRRCV_VW, choose Script View as, choose CREATE To, and click New Query. Note the command to create the view:

CREATE VIEW [dbo].[PSSRVHDLRRCV_VW] (IB_OPERATIONNAME, ONRCVHDLRNAME, DESCR) AS SELECT IB_OPERATIONNAME , HANDLERNAME , DESCR FROM PSOPRHDLR WHERE HANDLERID = 'RECV'

9. In PIA, navigate to Integration Broker > Integration Setup > Service Operations. In the Find Service Operation tab, in the Service Operation field, enter CREATE_WORKLIST_ITEM. Then click Search. Click CREATE_WORKLIST_ITEM to view it.

10. If the record is restricted, you will not be able to enter new Handlers. See step 3 above to resolve.

11. Click the Handler tab. Add a new Handler with the following information:
Name: ACK
Type: OnReceive
Implementation: Application Class
Status: Active

Click Details and add the following information:

Package Name: PT_WF_WORKLIST
Path: : [that is, only a colon must be entered]
Class ID: OnAckForMarkedWorkedResp
Method: OnAckReceive

12. Click Save.

ZFSCM900_DROP5_PRE_DMS Errors

Posted by on January 28, 2009 No comments

Problem:

When running the ZFSCM900_DROP5_PRE_DMS step to drop unneeded views, many steps complete in error:

SQL Error. Error Position: 0 Return: 601 - [Microsoft][SQL Native Client][SQL Server]
Cannot drop the view 'PS_XXXX_XX_XXXX_VW', because it does not exist or you do not have permission.

The end of the log file states ‘Successful Completion’.

Solution:

This is expected behaviour. The MP5 documentation states:

Dropping Views
In this step, if you encounter an error, due to some tables and views previously dropped or already do not exist in the database, please ignore the error, mark this step as Complete and continue with the Maintenance Pack installation. Note that the script log would still mark as Successful completion.

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist in PSXLATITEM Table

Posted by on January 21, 2009 No comments

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist in PSXLATITEM Table

Problem:

After running SYSAUDIT on FSDEV, the following error appears:

(QUERY-27) Following Queries Use XLAT Fields Whose Values Do not Exist In PSXLATITEM Table:
Query Name Field Name
 ---------- ----------------------
 ARDISH01 DRAFT_TYPE
 1 Total Rows

Solution:

1. Determine if scriptsPTPATCH.DMS was run. Log onto SQL Server Management Studio and running the following command:

select * from PS_MAINTENANCE_LOG where UPDATE_ID = '768890';

2. Next, load Application Designer and copy the PATCH849 project from file.

It was already copied.

3. Check PS_QRYFIELD_VW to see the fields used in the ARDISH01 query:

select * from dbo.PS_QRYFIELD_VW where QRYNAME = 'ARDISH01'

4. Get list of ‘DRAFT_’ field names from the PSXLATITEM table:

select distinct FIELDNAME from PSXLATITEM where FIELDNAME like 'DRAFT_%'

There is no DRAFT_TYPE field in this table.

5. Get list of ‘DRAFT_’ field names from the PSXLATITEM table:

Checked SYSAUDIT for FSDMO. This error is not present. Apply MP5 to FSDEV to resolve issue.

Audit contents of PS_AELOCKMGR

Posted by on January 18, 2009 No comments

Problem:

Application Engine Audits Result: Exception(s) Found
 (AE-16) Audit contents of PS_AELOCKMGR:
Message
 ----------------------------------------------------
 PS_AELOCKMGR contains 2row(s). There should be 1.
 1 Total Rows

Solution:

In SQL Server Management Console, checked the contents of the S_AELOCKMGR table.

It returns:

AE_LOCK
 -------
 N
 N

Delete the extra row using:

SET ROWCOUNT 1
 DELETE FROM [FSDMO].[dbo].[PS_AELOCKMGR]

Build and Alter Step While Applying MP5

Posted by on January 16, 2009 No comments

The Build and Alter step (or ‘chapter’) consists of two tasks:

1. Build and Alter Task_1
2. Build and Alter Task

Under the first task (Build and Alter Task_1), there are two steps:
a. Build Step
b. Run Recreate Tables Script

No action is required for the Build and Alter Task_1 steps. Read the documentation in the right pane, and then mark the steps as Complete.

Under the second task (Build and Alter Task), there are seven steps:
a. Build and Alter Step
b. Run Create Tables Script
c. Run Alter Tables Script
d. Run Create Triggers Script
e. Run Create Views Script
f. Additional Step 02
g. Run Create Indexes Script

The first step (Build and Alter Step) can be run automatically. The step completes with warnings, as there are some records have over 16 keys or some record definitions do not exist. The log file is:
E:/temp/softwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5}/BuildAndAlter.log

The output scripts generated by the Build and Alter Step are located in E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5}

The second step, Run Create Tables Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateTables.sql script in SQL Server Management Studio. The CreateTables.sql script executed successfully in 59 seconds.

The third step, Run Alter Tables Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} AlterTables.sql script in SQL Server Management Studio. The AlterTables.sql script executed successfully in 1 minute 30 seconds.

The fourth step, Run Create Triggers Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateTriggers.sql script in SQL Server Management Studio. The CreateTriggers.sql script executed successfully in less than 1 second.

The fifth step, Run Create Views Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateViews.sql script in SQL Server Management Studio. The CreateViews.sql script executed successfully in 26 seconds.

The sixth step, Additional Step 02 is done by logging into PIA and populating the KEYLIST field.

The seventh step, Run Create Indexes Script is done by running the E:tempsoftwareupdateFSCM900_MP5{3d899576-cc86-11dd-930f-9c077f4a98a5} CreateIndexes.sql script in SQL Server Management Studio. The CreateIndexes.sql script executed successfully in 10 seconds.

Note: When these steps were first run against the FSDMO instance, the Create Views step failed because the PTPATCH.DMS script and PATCH849 project were not run against the database.

Subquery returned more than 1 value

Posted by on January 13, 2009 No comments

Problem:

File: e:pt84915b-retailpeopletoolssrcpsappengaedebug.hSQL error. Stmt #: 16
 03 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]
 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or 
when the subquery is used as an expression.
 [Microsoft][SQL Native Client][SQL Server]The statement has
 Failed SQL stmt:UPDATE PS_PTPP_OPTIONS SET PTPP_REG_PREFIX = 
( SELECT A1.EOPP_REG_PREFIX FROM PS_EOPP_OPTIONS A1)
 Message Set Number: 200
 Message Number: 0
 Message Reason: File: e:pt84915b-retailpeopletoolssrcpsappengaedebug.hSQL
 error. Stmt #: 1603 Error Position: 0 Return: 8601 - [Microsoft][SQL Native Client][SQL Server]Subquery 
returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or 
when the subquery is used as an expression.
 [Microsoft][SQL Native Client][SQL Server]The statement has
 Failed SQL stmt:UPDATE PS_PTPP_OPTIONS SET PTPP_REG_PREFIX = 
( SELECT A1.EOPP_REG_PREFIX FROM PS_EOPP_OPTIONS A1) (200,0)
Process 7113 ABENDED at Step UPGPT846PP.EO_TO_PT.Step51a (SQL) -- RC = 8601 (108,524)
 Message Set Number: 108
 Message Number: 524
 Message Reason: Process 7113 ABENDED at Step UPGPT846PP.EO_TO_PT.Step51a (SQL)
 -- RC = 8601 (108,524)
Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

Solution:

Ran:

SELECT * FROM PS_EOPP_OPTIONS A1
Two rows returned. Ran:

SELECT DISTINCT * FROM PS_EOPP_OPTIONS A1
One row returned.

Checked PS_EOPP_OPTIONS table in FSDEMO. It has one row.

Solution: Delete duplicate row:

SET ROWCOUNT 1
DELETE FROM [FSDMO].[dbo].[PS_EOPP_OPTIONS]

All Processing Suspended: Restart OPRID=VP1

Posted by on January 11, 2009 No comments

Problem:

When running the UPGPT846PP Application Engine program the following error appears:

C:>E:PSOFTFS900binclientwinx86psae -CD FSDMO -CT MICROSFT -CO VP1 -CP VP1 -R INSTALL -AI UPGPT846PP
PeopleTools 8.49.15 – Application Engine
Copyright (c) 1988-2008 PeopleSoft, Inc.
All Rights Reserved

All Processing Suspended: Restart OPRID=VP1, RUNID=INSTALL, PI=7078 (108,503)
Message Set Number: 108
Message Number: 503
Message Reason: All Processing Suspended: Restart OPRID=VP1, RUNID=INSTALL, PI=7078 (108,503)

C:>

Solution:

When an application engine program is restarted, it returns:

All Processing Suspended: Restart OPRID=%1 RUNID=%2, PI=%3

Delete the offending application engine run control record using the following SQL statement:

delete from PS_AERUNCONTROL where OPRID = %OPRID% and PROCESS_INSTANCE = %PI%

That is, in SQL Server Query Editor, choose FSDMO and run:

select * from PS_AERUNCONTROL

Process Instance OPRID RUN_CTRL_ID AE_APPLID RUN_DTTM
7078 VP1 INSTALL UPGPT846PP 40:39.0

delete from PS_AERUNCONTROL where OPRID = ‘VP1’ and PROCESS_INSTANCE = 7078

Reference:
http://www.zutshigroup.com/site/tech/app_engine_processing_suspended

DDDAUDIT Report Generates 136 Pages

Posted by on January 9, 2009 No comments

Problem:

After running DDDAUDIT on fsdmo, the report generates the following errors:

(TABLE-1) SQL Table Names defined in the Application Designer that are not blank and not the same as the Record Name: No rows found.
(TABLE-2) SQL Table defined in the Application Designer and not found in the Database: (continued) 3,739 Total Rows
(TABLE-3) SQL Table defined in the Database and not found in the Application Designer: No rows found.
(TABLE-4)INFORMATIONAL ONLY - Tablespace not defined for SQL Table in the Application Designer: No rows found.
(TABLE-5) Table Contains More Than 500 Fields : No rows found.
(VIEWS-1) Views defined in the Application Designer and not found in the Database: No rows found.
(VIEWS-2) Views defined in the Database and not found in the Application Designer: No rows found.
(INDEX-1) Index defined in the Application Designer and not found in the Database: 4,028 Total Rows
(TRIGGER-1) Trigger defined in the Application Designer and not found in the Database: No rows found.

Solution:

Script to populate the database was not run. Logged on to Data Mover as user ‘sa’ (in bootstrap mode) and ran fsdmomss_fsdmo.dms script. Ensure that the fsdmomss script from the correct PS_HOME is used.

Did not complete the database installation. In the “Enterprise PeopleTools 8.49 Installation for Microsoft SQL Server”, start from Task B-8 and import the projects. Run Application Designer and import PS