Monday, January 07, 2008

On The Peoplesoft Road : Peoplesoft Database creation

The Database creation will be the main step on the Peopelsoft road installation.

First of all and before to go on an unknown road, some basics have to be known : difference between PeopleTools and PeopleSoft Application, the PeopleTools based version of the PeopleSoft Application, what are DEMO/SYS/PeopleTools System databases, what are DataMover and Application Designer, what is the bootstrap mode, what does the PS and people users.
I'll try to explain as simple as I can, and don't hesitate to add comments if you see bugs.

1. Difference between PeopleTools and PeopleSoft Application
The PeopleTools are a set of tools (external of the database) like DataMover and Application Designer. But they are also a Application Server and Process Scheduler.
The PeopleTools are a set of database objects (embedded onto the database). These objects hosts all the environement to allow the Application to run as well, like all the page definition, all the processes, a metamodel (tables definition, views definition, index definition, tablespace definition...). This list is not limited but at least (I hope) give an idea. The external tools and internal (embedded on the database) are linked and work together.

PeopleSoft Application host only the database objects link to the business module for which it was created.

I hope you see the difference, one is behind (PeopleTools), one is the real business (Application).

2. The PeopleTools based version of the PeopleSoft Application
When Peoplesoft dev team develop a new Application, the developers work generally on the last PeopleTools release. When the Application is ready to be sold to the customer, they "export" all the objects (including PeopleTools and Application objects) with the DataMover (a specific SQL language definition) to have a dat file. Once this export has been done, they didn't recreate it each time a new PeopleTools release is out later. This is the Application database version.
When you install a PeopleSoft Application, you may want to work on the last available PeopleTools release which can or can be not be the same as the developers when they created the Application itself.
That's why, we have often to upgrade the PeopleSoft Application to the latest PeopleTools release. That means you have to install the latest PeopleTools release, then the PeopleSoft Application then upgrade the Application Database as well regarding the Database version of your Application and your exected PeopleTools version.
Basically the Application Database version is the PeopleTools version when the Application has been created.

3. What are DEMO/SYS/PeopleTools System databases ?
Basically, there are three type of databases we can create.
DEMO : if you load an Application in DEMO mode, that means a set of data sample will be load, for training
SYS : if you load an Application in SYS mode, that means no data (or only what required), for production
PeopleTools System : if you choose this mode, you'll load only the PeopleTools objects, take care, if you want to load an Application, the Application Database version must be the same as the current PeopleTools. It's very rare to use it, unless you want a Performance database or for some other reason.

4. What are DataMover and Application Designer ?
DataMover is a query tool (update/delete/insert) which allow to manipulate the data, import, and export from/to file.
Application Designer is a development tool, to create/delete/update objects (record, Peoplecode, page definition...) in the Peoplesoft metamodel. Application Designer doesn't work directly against the Oracle object, there is a translator which check the meatamodel before querying the database.
You create a projet, a set of objects/definitions (...), and build it to create a SQL script file which you can run immediateley or later within your lovely query tool (SQL*Plus of course).

5. What is the bootstrap mode ?
You can connect in bootstrap mode only within DataMover.
Bootstrap mode means you use the owner of the objects, and you connect directly to the database in 2tier mode (and not in 3tier mode through the Application Server). This is used for administration task, and shouldn't be used for the development. This user doesn't know the Peoplesoft metamodel, it's not an Application user.

6. What does the PS and people users
On the Peoplesoft database, there are three specials users : the owner of database (see above) used for the administration task in bootstrap mode, PS and people.
PS : an Oracle user and an Application user.
The PS Oracle user host three tables with all the Application users and authorizations. Once the tables are created at the beginning of the install process, this user is not allowed to create a session against the database.
The PS Application user is the super admin user, basically PS has all the rights.Note that PS is the main user available for HRMS, it's VP1 for FSCM, PSCR for CRM application.
PEOPLE is an Oracle user account, no objects at all, and only SELECT rights on the PS'tables.
When you connect (except in bootstrap mode) to DataMover or Application Designer, Peoplesoft will create a connection to the database with the PEOPLE user, read if the user has the right has well to connect and to use DMS and/or AppDesigner. Once the check are ok, Peoplesoft reconnect you to the database with the owner of objects.

Then, and only when you have understood those concepts, you can go in the Peoplesoft database creation which can be split in three mains steps (I'll insert the links when the articles will be ready) :
1. Database creation (db creation and Application loading)
2. Upgrade your application to your PeopleTools version (only if required)
3. Post creation tasks

Have fun,


Anonymous said...

How can I run datamover script from unix box. I dont have access to windows env.


Nicolas Gasparotto said...

Go to $PS_HOME/bin
And run the following command to get help of the DataMover in command line :
psdmtx /help

Raj said...

hi -

Am new to PS hrms. i was secessfully installes PSHRMS 9.0 with Oracle 10g.

The problem is i can login as PSADMIN/PSADMIN IN PIA LOGIN.I couldn't login as PS/PS in PIA. What could be the problem.

Am using XP OS


Prashant said...

Hi Raj
Please Check if PS id is working fine. Try resetting the password for PS userid and then login again. Please make sure that after resetting the password update it in application server, process scheduler and webserver configuration file if u have used it those files.

সুব্রত সাহা said...

What does it mean by symbolic id ?

Nicolas Gasparotto said...

Basically, on the connection, the connectid (people) is taken the symbolic id (typically SYSADM1) from the PSOPRDEFN table within the user you are trying to connect to. Then it goes to PSACCESSPRFL to take the accessid and accessid's password to be able to reconnect as the Peoplesoft objects owner.
That architecture allows a single database to host multiple instances of Peoplesoft application, even though it is strongly not recommanded.

Hope that helps to understand,

You can also find out more in an other article I wrote sometime ago :


TucsonGirl said...

Hi Nicolas,

I am doing a fresh install of the HRMS 9.1 and PeopleTools 8.51, with HRMS 9.1 MP2 and Tax Updates.

Now I have installed the following:

PeopleTools 8.51
HRMS 9.1

I am on RHEL 5.2

Now, for example, I'll call the new database DMHCM.

I am having trouble booting the application server and process scheduler and so I decided to apply the MP manually instead, since I am on a tight deadline.

I logged on to the Windows server (where there is initially one environment already set up using PT 8.51)
I copied the folder and configured everything and tried to boot the Windows batch server (DMHCM)

Since I am applying the MPs manually, I log on to App Designer. (from my understanding, the actual update of the MPs are applied to the database)

Now I am trying to copy the XML project but when , I get the following message:

The database is at release 8.50. The PeopleTools being run require databases at release 8.51.

Gosh, do I have to create a new database? I think I've messed everything up.

Anonymous said...


Can you say what the estimated size is of a 9.1 HCM Demo DB?

Toni said...

great job with this site! congratulations.
And now as usual a question :), where I can see the difference between a system and a demo database?
What I mean is that I have created a database, SYSTEM, for a HRMS 9.0 instalation and I have a lot of users in the system, something like:
HAM [PS]Absence Mgmt Core - User
HAM_K0W001 AM2PNA Manager
HAM_K0W002 AM Employee
HAM_K0W003 AM Employee
HAM_K0W006 AM Manager
HAM_K0W007 AM Employee
HAM_K0W010 AM2PI Mgr & Administrator
HAM_K0W013 HAM_K0W013
HAM_K0W023 HAM_K0W023
HAM_K0W101 AM Manager
HAM_K0W102 AM Employee
HAM_K0W103 AM Employee
HAM_K0W106 AM Manager
HAM_K0W107 AM Employee
HAM_K0W201 AM2TL Manager
HAM_K0W202 AM Employee
HAM_K0W203 AM Employee
HAM_K0W206 AM Manager
HAM_K0W207 AM Employee
HAM_K0W301 AM2GP Manager
HAM_K0W302 AM Employee
HAM_K0W303 AM Employee
HAM_K0W306 AM2GP Manager
HAM_K0W307 AM Employee
HCRARG [PS] HRMS Argentina - User
HCRAUS [PS] HRMS Australia - User
HCRAUS_KA0001 [PS] Jane Eliz. Taylor - EE
HCRAUS_KA0003 [PS] Maria Cortes - EE
HCRBEL [PS] HRMS Belgium - User
HCRBRA [PS] HRMS Brazil - User
HCRCAN [PS] HRMS Canada - User
HCRCAN_KC0001 [PS] Martina R Griffiths - EE
HCRCAN_KC0002 [PS] Kirby Dunbar - MGR
HCRCAN_KC0003 [PS] Cynthia A Rogers - EE
HCRCAN_KC0004 [PS] Charles M Reid - EE
HCRCAN_KC0031 [PS] Marcel Saint-Amand - MGR
HCRCHE [PS] HRMS Switzerland - User
HCRCHE_KWG019 [PS] Hans Ott - EE
HCRCHE_KWG030 [PS] Anna Widmer - EE
HCRCHN_K6001 HRMS China Manager - K6001
HCRCHN_K6002 HRMS China Employee - K6002
HCRDEU [PS] HRMS Germany - User
HCRESP [PS] HRMS Spain - User
HCRESP_KEG001 [PS] Antonio Diaz Ruiz - EE
HCRFRA [PS] HRMS France - User
HCRFRA_KF0001 [PS] Maurice Berger - EE/MGR
HCRFRA_KF0030 [PS] Laurent Lacombe - EE
HCRGBR [PS] HRMS United Kingdom -User
HCRHKG [PS] HRMS Hong Kong - User
HCRHKG_KH0001 [PS] William Lee - EE
HCRHKG_KH0018 [PS] Flora Thieu - Mgr
HCRIND [PS] HRMS India - User
HCRIND_KP0002 [PS] Naveen Patil - EE
HCRIND_KP0006 [PS] Laric Richards - Mgr
HCRITA [PS] HRMS Italy - User
HCRITA_KIG001 [PS] M.Buonarroti - EE
HCRJPN [PS] HRMS Japan - User
HCRJPN_KJ1003 [PS] Fusaka Endo - EE
HCRJPN_KJ1004 [PS] Hiromi Goto - MGR
HCRMEX [PS] HRMS Mexico - User
HCRMYS [PS] HRMS Malaysia - User
HCRMYS_KM0001 [PS] Rahim bin Awang - EE
HCRMYS_KM0008 [PS] Lee Chin - Mgr
HCRNLD [PS] HRMS Netherlands - User
HCRNLD_KNG001 [PS] Guido Maarschalk - MGR
HCRNLD_KNG003 [PS] Gerrit Mastenbroek - EE
HCRNZL [PS] HRMS New Zealand - User
HCRSGP [PS] HRMS Singapore - User
HCRSGP_KS0001 [PS] Chu Kang Phua - EE
HCRSGP_KS0017 [PS] Janet Yang -Mgr
HCRTHA (blank)
HCRUSA [PS] HRMS United States - User
HCRUSA_KU0001 Douglas Lewis
HCRUSA_KU0002 [PS] Charles Baran - EE
HCRUSA_KU0003 [PS] Jean Parsons - MGR
HCRUSA_KU0007 [PS] Betty Locherty - MGR
HCRUSA_KU0010 [PS] Antonio Santos - EE
HCRUSA_KU0011 [PS] Patrick Seto - MGR
HCRUSA_KU0012 [PS] Allan Martin - EE
HCRUSA_KU0015 [PS] Carmichael Espinosa - EE
HCRUSA_KU0020 [PS] Christelle Stevenson - EE
HCRUSA_KU0035 [PS] James Fung - EE
HCRUSA_KU0042 [PS] Danny Johnson - EE
HCRUSA_KU0046 [PS] Rosanna Channing - EE
HCRUSA_KU0054 [PS] Stanley Lowe - EE
HCRUSA_KU0056 [PS] Edward Ng - EE
HCRUSA_KU0065 [PS] Adland Chu - EE
HCRUSA_KU0068 [PS] Marc Kessler - MGR
HCRUSA_KU0069 [PS] Angela McKay - EE
HCRUSA_KU0077 [PS] Annie Mirzoyan - EE
HCRUSA_KU0101 [PS] Cynthia Adams - EE
HCRUSA_KU0105 [PS] Vicky Adler - EE
HCRUSA_KU0106 [PS] Kevin Chae - EE
HCRUSA_KU0108 [PS] Joyce Hayden - EE
HCRUSA_KU0113 [PS] Cassandra Jacobson - EE
HCRUSA_KU0118 [PS] Shirley Hafferty - EE

How can I know what else is in the database :), can you please give me some pointers?

Snehasish said...

What are the difference between PeopleTools 8.49 and PeopleTools 8.51??

Anonymous said...

Hi, Gasporro. I want to install FINSCM9.1 on a virtual box
Task Completed;
1. Install Virtual Box
2. Install Oracle VM Server 2.2 (choose Linux on Oracle OS option )
3. Install Oracle VM Manager on XE database
Do I need to install the template now? I have 2 template one for Apps with 3img files after extraction and PIA with 2 img files after extraction do I need to combine both to one image file or Apps first then PIA second? How can I load/Import it thru VM Manager. And once loaded how do I go about going into the application? Do I need to access it thru the web browser outside the virtual box? Appreciate your assistance as I'm not familiar with VM. Thank you very much - Nicole

Peoplesoft Community Network said...

Well article list appreciated it very helpful and informative.Keep it updates.