EPY: All About Stored Statements (Primary Solution) (Doc ID 607665.1)

Last updated on APRIL 11, 2024

Applies to:

PeopleSoft Enterprise HCM Payroll for North America - Version 8.8 SP1 to 9.2 [Release 8.8 to 9]
Information in this document applies to any platform.

Purpose

Introduction to Stored Statements and Static SQL

The term "static SQL" refers to predefined SQL statements (e.g. Select, Insert, Update, etc.) which are predefined and thus able to be stored externally from the COBOL programs that use them. This is in contrast to "Dynamic SQL" which is constructed by the COBOL process as part of the logical flow of the program.

The text of the "static SQL" statement is stored in the database, in the PS_SQLSTMT_TBL. Most of the SQL executed by PeopleSoft HCM COBOL programs is static SQL. These SQL statements are delivered in DMS scripts and stored independently from the COBOL programs that execute them. They are referred to as "stored statements" or "stored SQL statements".

The static SQL statements are loaded by DataMover into a PeopleTools table called PS_SQLSTMT_TBL. When you run a COBOL program, the system will retrieve the stored SQL statements from PS_SQLSTMT_TBL as needed by the program.

Files with ".DMS" file type:

There are three types of files with ".DMS" names:

DMS scripts to load a project into the AUDB; the project contains database objects like records, panels, menus, etc. This type of .DMS script file has an accompanying .DAT or .AU file containing the project and objects to be loaded. The objects in the project will be used to update Tools tables.
DMS scripts to load application data (e.g. tax rates or garnishment rules). This type of .DMS script file has an accompanying .DAT file containing the actual data to be loaded. The data will be used to update application tables.
DMS scripts to load SQL stored statements. This type of .DMS script file does not have an accompanying .DAT or .AU file. Stored statement .DMS script files are named for the COBOL program which will use the SQL statements.

Note: This document only addresses the third type of DMS file.