|
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 |
|