How PeopleSoft constructs SQL

PeopleSoft constructs SQL for creating tables, views, indexes and tablespaces. The constructed SQL is used by Application Designer when building objects and stored in DataMover export files for bootstrapping databases.

The examples given are from PeopleSoft 7.5, but much of this remains the same in PS8 and PS9.

It can be useful to understand how these items are created to provide insight into the PeopleSoft processes of creating items, and to be able to improve some of the processes for particular platforms.

PeopleSoft stores the SQL building blocks on the database (isnít that just like them?). At its heart are the following tables:

Table Description

PSDDLDEFPARMS

Contains the definition of the parameters, their names and default values.

PSDDLMODEL

Contains the "model" SQL statement with placeholders for variable parts of the statements.

PSIDXDDLPARM

Specific values for parameters for specific indexes.

PSRECDDLPARM

Specific values for parameters for specific records.

PSSPCDDLPARM

Specific values for parameters for specific tablespaces.

 

The Construction

We will use an example to illustrate the process of construction. We are going to be building the PS_JOB table from Application Designer on the Oracle platform.

When constructing a statement, PeopleSoft finds the right type of statement in the PSDDLMODEL table, depending on which object is being created, which platform is being used and the sizing set in use. For instance, the create table function on the Oracle platform as delivered (sizing set 0) is:

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**);

Note that there are two types of placeholder:

  • **INIT** filled in from DDL parameter tables.
  • [TBNAME] filled in by the build process from the definition of the object being created. Note that the TBSPCNAME comes from the field PSRECDEFN.DDLSPACENAME.

The specific object being built (PS_JOB) is checked for in its specific object parameters table (PSRECDDLPARM).

This is checked and one value is found - for INIT - which has a value of 957,440.

This can be viewed in Application Designer:

Open the record "JOB", Tools->Data Admin->Record DDL

For index DDL for JOB, Tools->Data Admin->Indexes and press the "Edit DDL" button.

So we have NEXT, MAXEXT, and PCT outstanding. These are gained from the default values (PSDDLPARM) - our statement type is "Table" - see appendix for list of values:

Parameter Value
INIT 10000 overridden to 957,440
MAXEXT 110
NEXT 100000
PCT 0

Both the model and the default parameters can be seen in Go->PeopleTools->Utilities->Use->DDL Model Defaults.

So, we can now put all this together and see that the create table statement for PS_JOB would look like:

CREATE TABLE PS_JOB (EMPLID...) TABLESPACE HRLARGE STORAGE (INITIAL 957440 NEXT 100000 MAXEXTENTS 110 PCTINCREASE 0);

 

Appendix: Column Translations

These translations are available in the XLATTABLE.

Statement_Type Value
1 Table
2 Index
3 Unique Index
4 Tablespace

 

PlatformID Value
0 SQLBase
1 DB2
2 Oracle
3 Informix
4 DB2/Unix
5 ALLBASE
6 Sybase
7 Microsoft
8 DB2/400

Computing Articles