Direct Data Update(DDU) Information
Direct Data Updates (DDUs) are used to directly update the data in a Peoplesoft table. Common reasons to perform DDUs include, but are not limited to, correcting errors that otherwise cannot be corrected, to perform mass updates that may take too long to perform manually, or to purge data when no purge process exists. A DDU may take the form of a SQL script, an SQR, or a Data Mover Script (DMS). In the development non-production instances, developers have the ability to perform these updates themselves. Once testing and validation is complete, the developer submits a DDU request in order to have the DDU run in the production environment.
DDUs are not subject to a submission deadline. DDUs are executed after all approvals are received. How soon they are executed after all approvals are received depends on the execution time specified in the request. If no execution time is specified, the DDU will be executed within one business hour. Please note one business hour could carry over to a different day. For instance, if approvals are received at 4:30pm, one business hour would carry until 8:30am the next business day.
DDU Submission Process Flow these instructions as Word doc
To initiate a request for a Direct Data Update to a production environment, the requestor should supply the information listed below in an email. The email should be sent to firstname.lastname@example.org, email@example.com, and the functional and technical approvers.
- E-mail Subject – The subject line for the e-mail will use the following format:
Subject: DDU Request for ‹database› by ‹requestor› on ‹date› - ‹##›, where
- ‹database› is the database the script needs to be run in, i.e. "HPOMPRD" or "FPOMPRD"
- ‹requestor› is the name of the person submitting the request
- ‹date› is the submission date in either YYYY-MM-DD or YYYY/MM/DD format
- ‹##› is the number of the request from that person on that day (occassionally people submit more than one request on a given day)
- Execution – The time that the DDU is requested to be executed by.
- Description – A description of what the script or program will be doing to the database.
- Justification – The reason this process must be run as a Direct Data Update and cannot be performed using the PeopleSoft web interface.
- Instance - The instance in which the script was tested.
- Expected row count(s) – The expected number of rows inserted, updated, or deleted for each table in the script or program. Typically this will be determined by executing a select statement in the Production database. This can be in the body of the e-mail message or provided as an attachment. If it is expected that the row counts may be different, this should be described in the request.
- For SQL Scripts - Include the exact SQL statement(s) to be executed as an attachment to the email. The script file name should end with the .sql suffix, and all sql statements contained within it should be terminated with a semi-colon (;). No commit statements should be included in the script. The DBA executing the script will commit the changes after verifying the row counts match the expected values.
- For Data Mover Scripts - Include the datamover script to be executed as an attachment to the email. If the Data Mover script is an import, then the appropriate “.dat” file should also be attached with instructions indicated where the script is expecting to find the “.dat” file, or the “.dat” file should reside on a network directory and the script should be coded as such. Information on the location of the “.dat” file should be included in the email to assure that the DBA running the process has the same network mapping as the Data Mover script is expecting.
- For SQR Programs -Include the exact SQR program(s) to be executed as an attachment to the email, or list the path to where the SQR is locate on a network share. Alternatively you may configure a SQRW shortcut on a network share that already contains the sqr file and if desired sqr.log file path(s). No commit statements should be included in the script. The DBA executing the script will commit the changes after verifying the row counts match the expected values. It is required that the program code in Appendix A be included in the SQR program.
Sample Email Submission
Subject: DDU Request for HPOMPRD by DEVELOPER 2013/02/01 #01
NAME, please provide functional approval.
NAME, please provide technical approval.
Execution: Please run no later than 4:00pm on 02/01/2013
Description: Delete all rows from the email address table with a “HOME” address type that contains a “csupomona.edu” address.
Justification: csupomona.edu addresses are stored and maintained in the “CAMP” type. When they are placed into ”HOME” they become inaccurate after a student leaves and their address is recycled leading to the same address being stored for different emplids. The number of records is too great to perform the action online.
The script was tested in HAPOMTST.
Expected row counts:
Review and Approval
A DDU request requires the following levels of review and approval: the functional manager, and a technical reviewer. The role and process for these review and approval steps are as follows:
- Functional Manager - The PeopleSoft Manager for the Functional Area, as defined in the PeopleSoft Responsibility Matrix, must approve the DDU. The purpose for the functional manager approval is to ensure their acknowledgement for the update being requested as well as that appropriate testing occurred.
- Technical Reviewer - The purpose of the technical review is to perform the following:
- Validate that the Direct Data Update performs the actions described in the request.
- Check that the update is syntactically correct
- Confirm that no commit statements are embedded in the SQL script or that the SQR program is coded as noted above
The authorized Technical Reviewers are the following staff (or their designated proxies):
SA Vacant, Saraswathi Kumathi HR Patricio Beltran, Glendy Yeh Backup Tim Raymond, Glendy Yeh
The Technical Reviewer must be someone other than the request initiator and the functional reviewer.