Understanding Record Definitions
Fields that are grouped together as a unit are record definitions. A record definition represents what the underlying SQL database tables look like and how they process data. Two tabs exist in record definitions.
Use the Record Fields tab to access the fields that compose the record definition.
This example illustrates the fields and controls on the Record Fields tab. You can find definitions for the fields and controls later on this page.
Use the Record Type tab to specify way that the record definition is to be used, for example a SQL table or view, or perhaps a temporary table.
This example illustrates the fields and controls on the Record Type tab. You can find definitions for the fields and controls later on this page.
Select the Record Type tab to view the record definition types. Select from these types:
Term |
Definition |
|---|---|
SQL Table |
Select to define a record definition that has a corresponding physical SQL table in the database. Create this table when you run the Build Operation from the Build menu. This value is the default setting. |
SQL View |
Select to define a record definition that corresponds to a SQL view, which is not a physical SQL table in the database but fields from one or more SQL tables that are reorganized into a different sequence. SQL view provides an alternate view of information that is stored in tables. To create the SQL view, click the Click to open SQL Editor button, enter a SQL Select statement, and then run the Build process. |
Dynamic View |
Select to define a record definition that can be used like a view in pages and PeopleCode, but is not actually stored as a SQL view in the database. Instead, the system uses the view text as a base for the SQL Select that is performed at runtime. Dynamic views can provide superior performance in some situations, such as search records and in PeopleCode Selects, because they are optimized more efficiently than normal SQL views. Note: Keys for dynamic views should not be effective date fields. The dynamic view should contain only fields whose names exactly match the fields comprising the SQL query. |
Derived/Work |
Select to define the record definition as a temporary workspace to use during online page processing. A derived or work record is not stored in the database, so you do not build it. |
SubRecord |
Select to define the record definition as a subrecord: a group of fields that is commonly used in multiple record definitions and that you can add to other record definitions. This way, you can change a group of fields in one place, as opposed to changing each record definition in which the group of fields is used. |
Query View |
Select to define the record definition as a view that is constructed using the PeopleSoft Query tool. Before you can create the view, PeopleSoft Application Designer prompts you to save the definition. |
Temporary Table |
Select to define the record definition as a temporary table. You can specify temporary images of the table on the PeopleTools Options page. Use temporary tables for running PeopleSoft Application Engine batch processes. Temporary tables can store specific data to update without risking the main application table. When creating a temporary table on an Oracle RDBMS, you have the option of creating a Global Temporary Table (GTT). Using GTTs can enhance Application Engine performance. |
Non Standard SQL Table Name |
Specify the SQL table name that you are defining to override the standard convention of prefixing PS_ to the record name. |
Build Sequence No. |
This field is available when the record type is a SQL or Query view. Set the order in which the view is to be created. The default value is 1 when the record or view is initially created. Views that must be created first can be set to 0, while views that you want created last can be set to any number from 1 to 99. The build sequence number is stored with the other details of the record or view in the database. Note: The maximum valid entry for the sequence number is 99. |
When building temporary tables on an Oracle database, you have the option of implementing Global Temporary Tables (GTT) for enhanced performance.
This example shows the Record Type tab when creating a temporary table on the Oracle platform with the Global Temporary Table option displayed.
When building a GTT, using the Application Designer Build feature, the SQL generated to build the table appears similar to the following example:
CREATE GLOBAL TEMPORARY TABLE PS_QE_AETEST (PROCESS_INSTANCE
DECIMAL(10) NOT null,
QE_AE_INT_1 SMALLINT NOT null,
QE_AE_APPLID VARCHAR2(12) NOT null,
QE_AE_SECTION VARCHAR2(8) NOT null,
QE_AE_STEP VARCHAR2(8) NOT null,
JOB_INSTANCE DECIMAL(10) NOT null,
PROCESSINSTANCE INTEGER NOT null,
QE_RETURN_CD SMALLINT NOT null,
RUN_CNTL_ID VARCHAR2(30) NOT null,
AS_OF_DATE DATE NOT null,
QE_AE_SQLROWS INTEGER NOT null,
CURRENCY_CD VARCHAR2(3) NOT null,
ANNUAL_RT DECIMAL(18, 3) NOT null) ON COMMIT PRESERVE ROWS
TABLESPACE PTAPP
/
CREATE UNIQUE iNDEX PS_QE_AETEST ON PS_QE_AETEST (PROCESS_INSTANCE)
/
Note: If you select the Global Temporary Table option, the temporary table name cannot exceed 11 characters.
Note: A Global Temporary Table must be assigned to the PSGTTnn tablespace, where nn is the tablespace number (01, 02, and so on).
Global Temporary Tables are discussed in more detail in the product documentation for:PeopleTools: Data Management. See Improving Process Performance with Global Temporary Tables. They are also discussed in the product documentation for PeopleTools: Application Engine. See Understanding Global Temporary Tables.
When building SQL views or query views on an Oracle database, you have the option of implementing a materialized view.
A materialized view acts as a database table that contains the results of a query. Once created, the data in a materialized view can be synchronized (automatically or manually) with its source information as needed with little or no programming effort.
A view has a logical existence, but a materialized view has a physical existence. All the characteristics and tasks that can be performed with a table can also be done with a materialized view. For example, a materialized view can be indexed, analyzed, and so on.
Materialized views are discussed in more detail in the product documentation forPeopleTools: Data Management. See Using Materialized Views.
This example shows the Record Type tab when creating a temporary table on the Oracle platform with the Global Temporary Table option displayed.
Term |
Definition |
|---|---|
Materialized View |
Select to create a materialized view for Oracle database systems when SQL View or Query View is also selected. This option is available only for Oracle systems. When selected, the Materialized View Options group box appears to the right. |
Materialized View Options
Term |
Definition |
|---|---|
Refresh Method |
Complete: The materialized view table will be refreshed completely. (Default) Fast: The materialized view table will be refreshed incrementally when running the Application Designer Build option. |
Refresh Mode |
On Demand: Administrators refresh the view through Materialized View Maintenance page. (Default) On Commit: During a commit, the Oracle system executes triggers and updates the materialized view log tables. |
Build Options |
Immediate: The system creates the view and populates the view from the base tables. This may be time consuming depending upon the complexity of the view and data. (Default) Deferred: The system creates the view but does not populate the view during the build process. An administrator must run a refresh from the Materialized View Maintenance page. |
Materialized views are discussed in more detail in PeopleTools: Data Management. See Using Materialized Views.
Similar options exist for IBM Db2 for z/OS (Materialized Query Tables) and Microsoft SQL Server (Indexed Views).