Header Ads

  • Recent Posts

    All About Stored Statements

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

    1. Project Import Script:
    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.
    This applies only to 7.x only and does not apply to 8.x.

    This is the script that you run to import the tax update project into the AU database.  The Appendix A documentation that you receive with each tax update will tell you what to do with this script.  Project import script files are named AUxxxrel.dms, where "xxx" = tax update ID and "rel" = version -- for example, the project import script for tax update 99-A for version 7.50 was named AU99A750.DMS.

    Occasionally a tax update may be delivered with no project, and therefore no AUxxxrel. DMS script; this happens when there is no record, field, menu, panel, PeopleCode, etc. changes in the tax update, and therefore no project is needed.  For example, tax update 99-B did not include a project, so there was no AU99B750.DMS script delivered with tax update 99-B -- this is correct.
     
    2. Application Data Upgrade Script:
    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. These are the DataMover scripts that update the application data tables -- like tax rates and garnishment rules.  These scripts always come in pairs -- two files which have the same name, but one has a .dms suffix and the other has a .dat suffix.  These script files are named T99B(rel)(y).DMS and T99B(rel)(y).DAT where 'rel' is your release (e.g., 750) and 'y' is either:
    'U'  for U.S. tax table updates,
    'C' for Canadian tax table updates,
    'G' for garnishment table updates, or
    'M' for message table and/or strings table updates.
    • Only customers who have U.S. employees and do U.S. processing need to apply scripts with "U" file name extensions.
    • Only customers who have Canadian employees and do Canadian processing need to apply scripts with "C" file extensions.
    • Both U.S. and Canadian customers should apply scripts with 'G' or 'M' file extensions (if delivered) to update the garnishment and/or message tables respectively.
    3. Stored SQL Statement Scripts:
    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.

    Creating the Static SQL Statement *.DMS Files:
    The SQL statements reside in a text file with the same name as the calling COBOL program, and an extension of .DMS (e.g. file name PSPPYRUN.dms for the SQL statements used by COBOL program PSPPYRUN).
    The first line of the SQL statement is made up of the STORE command followed by an 18 character name of the statement. The name must conform to the following pattern:

    Character Naming convention:
    • Character 1 - 8 Program name
    • Character 9 Constant (underscore)
    • Character 10 Constant specifying SQL statement type:
      •  S for select
      • D for delete
      • I for insert
      • U for update
    • Character 11 Constant (underscore)
    • Character 12 - 17 Unique statement name within a program, usually a reference to the table being accessed
    Examples of Stored Statements Names:
    • PSPPYRUN_S_OFFPAGE For program PSPPYRUN, does a SELECT against PS_PAY_PAGE for Off-cycle rows
    • PSPPYBLD_U_CAL For program PSPPYBLD, makes an UPDATE to the PS_PAY_CALENDAR table
    • PSPCUPDT_I_CHK For program PSPCUPDT, does an INSERT to the PS_PAY_CHECK table
    After the STORE [statement name], the actual SQL statement follows. Bind variables are used to pass values to the statement at the time of execution. Each SQL statement must end with a semicolon in a line of its own.
    Where to find the Stored Statement Script Files:
    The individual stored statement .dms scripts (one per calling program) reside in the SRC\CBL\BASE folder along with the .cbl files (COBOL programs and copy members). STORE*.dms scripts which can be used to run all of the stored statements for a product also reside in the %PS_HOME%\src\cbl\base folder.

    When you receive a fix, tax update, or application upgrade which includes any stored statement .dms script files, you need to put them into this directory as well (e.g. %PS_HOME%\src\cbl\base).

    Individual DMS Scripts Vs STORE *.DMS Scripts:
    For each PeopleSoft product, there is a delivered STORE*.dms script file (e.g. STOREPAY.dms, STOREGL.dms, etc.) that loads all of the static SQL statements for all of the COBOL programs within that product.

    HRMS PRODUCT SUITE STORE*.DMS SCRIPTS ARE:
    1. PeopleTools %PS_HOME%\src\cbl\base\STOREPT.DMS. This must be always run FIRST.
    2. HR Common %PS_HOME%\src\cbl\base\STOREHRM.DMS
    3. Benefits Admin %PS_HOME%\src\cbl\base\STOREBAS.DMS. If this is present, must always be run LAST.
    4. Pension %PS_HOME%\src\cbl\base\STOREPEN.DMS
    5. Payroll %PS_HOME%\src\cbl\base\STOREPAY.DMS
    6. Time & Labor %PS_HOME%\src\cbl\base\STORETL.DMS only for 7.5 or earlier releases as of 8.x the stored procedures needed for the TL Load process are contained in STOREPAY.DMS.
    7. Payroll Interface %PS_HOME%\src\cbl\base\STOREPYI.DMS
    8. GL Interface %PS_HOME%\src\cbl\base\STOREJG.DMS
    Each of the STORE*.dms scripts runs all of the individual.dms stored statement scripts for a given product. When you run a STORE*.dms script, DataMover in turn runs the individual SQL scripts for that product. DataMover pulls the individual scripts from this same folder (%PS_HOME%\src\cbl\base), and updates the PS_SQLSTMT_TBL with the actual SQL statements.

    NOTE:
    The STORE*.dms scripts are actually files that contain DataMover instructions to retrieve and execute many other stored statement scripts. So, rather than running each of the individual stored statement .dms scripts separately (one for each program), the STORE*.dms files allow the user just to run this one script, which in turn will run all the scripts for that product.

    How to RUN(LOAD) Stored Statement Scripts:
    1. Log out of the application database
    2. Start DataMover (using PSDMT.EXE in the PeopleTools directory, or the "semi truck" icon)
    3. Log into DataMover
    4. Log into the appropriate database
    5. File > Open
    6. Select the *.dms script that you need to run (e.g. storepay.dms, psppyrun.dms, etc.)
    7. Click on the traffic light to run the script
    When you are reloading all of the stored statements, the STORE*.dms scripts may be run in any order -- EXCEPT that We have found at various releases that PeopleTools (STOREPT.dms) must be run first and if you have Benefits Administration, then STOREBAS.dms must be run last.

    Selective Loads Vs ALL Stored Statements for a Product:
    You may choose to run only individual, selected stored statement .dms scripts. For example, if you apply a fix which delivers a single stored statement .dms script, you have the option to run only that one .dms script through DataMover to load just the SQL statements defined in that script file. However, it's safer to run the full-product scripts (such as STOREPAY or STOREBAS) to ensure that all stored statements remain in synch.

    NOTE:
    PeopleSoft strongly recommends that you always reload ALL of the stored statements when applying any tax update or application upgrade.
    The STORE*.dms scripts can be run anytime you think the link may be broken between the COBOL programs and the static SQL (or the SQL statement table). PeopleSoft recommends that you reload all of the stored statements whenever you get any COBOL execution errors involving SQL statements (e.g. errors in bind/setup data, invalid column, etc.).

    Where to apply Stored Statement Scripts:
    DO NOT use an AUD to apply stored statement .dms scripts.
    DO run the STORE*.dms scripts directly against each of your other databases (Demo, Test, Production, etc.).

    The stored statements must be loaded separately for each database, since each database has its own PS_SQLSTMT_TBL from which the SQL will be retrieved at runtime. (This is unlike the COBOL executable, which may be shared between databases.)

    Editing Stored Statement Scripts:
    If you should need to change the text of Stored Statements and if you choose to edit these scripts outside of DataMover, DO NOT USE NON-TEXT EDITORS such as Write or Word. Editing with these editors will cause the scripts to fail. You should use a TEXT editor, such as Notepad or KEDIT, to make modifications to the DMS script that loads the statement, then rerun the DMS script using the Data Mover.

    1 comment:

    1. Very detailed article, very helpful. Thanks Shyam.

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad