The Pennsylvania State University - Administrative Information Services, a unit of Information Technology Services

Database Developers

This page contains information for Data Warehouse database developers, including Database Developers' Request for Service (RFS), Standards, and Sample JCL.

 

RFS

This form is for individuals directly responsible for information on the Data Warehouse.

Data Warehouse Developers:  Please cut and paste this form either into a Microsoft Word document or your e-mail client, providing all the information below. Please submit to datawarehouse@psu.edu. Upon receipt, you will be sent a confirmation e-mail with a project number.

Requestor's Name:
Requestor's E-mail:
Requestor's Phone Number:
Data Warehouse database affected  (indicate if new database)
Data Warehouse tables affected (indicate if  new table)
Please provide a complete description of the change you are requesting.
 
If you are requesting new fields be added to a table or a new table:

  • List each field by its adabas name followed by the field length. (If this table uses user friendly names, then provide those as well)
  • For each field, indicate which adabas file number the field documentation should be pulled.
  • Indicate if you are the steward of all fields. If you are not the steward, then you'll need to provide an e-mail from the steward approving the fields be added to this table.
  • If any fields are derived, then provide a complete user-friendly description that will be used for the documentation.
  • Are there any timeline concerns?

Developers' Standards

Date Fields
Any date field contained in the data file sent to the Data Warehouse must contain a valid date. If a valid date cannot be determined, then the field must contain null or blanks. Each field must be interrogated to make sure it is a valid field before it is sent to Data Warehouse.

Fields Containing a decimal
Any currency or decimal fields need to have the decimal added to the field before it is sent to the warehouse.

Sample JCL

FTP Steps

Below is sample JCL that will need to be added to your JCL to FTP the information to Data Warehouse. Any information preceded with an asterisk(*) needs to be obtained by sending an e-mail to datawarehouse@psu.edu.

For ISIS Related Jobs:

//JS10     EXEC PFTP

//PS10.INPUT  DD DSN=PERM.MAINTJCL(DW1FTP),

//         DISP=(SHR,KEEP,KEEP)

//         DD *

CD\*yourdirectoryname. 

PUT 'Name of dataset you created(0)'   *yourdatafilename.dat

PUT 'PROC.SOURCE(FTPEND)'   yourjclname.END

 

For IBIS Related Jobs:

//JS10     EXEC PFTP

//PS10.INPUT  DD DSN=PERM.MAINTJCL(DW2FTP),

//         DISP=(SHR,KEEP,KEEP)

//         DD *

CD\*yourdirectoryname. 

PUT 'Name of dataset you created(0)'   *yourdatafilename.dat

PUT 'PROC.SOURCE(FTPEND)'    yourjclname.END

Creating your CA7Agent Update Job

Production Control now requires a second JCL job be developed that will allow for the update process on the warehouse through CA7Agent. 

  • You’ll need to request a new procedure name by using the RPF. The first two characters of the JCL name should be your areas standard procedure letters followed by XP. Take the next available number. A template for this JCL can be found at XL.CA7Agent and an example of one already in production is ZI.FZXP007.
  • You’ll need to request an input parm number. This can be done by sending an e-mail to datawarehouse@psu.edu. This parm number will then be entered into your JCL.

Sample JCL

//JS10   EXEC UCC7TOUN
//SYSIN   DD DSN=UCC7.NODES.AGENT(CA7P01),
//        DISP=(SHR,KEEP,KEEP)
//        DD DSN=UCC7.COMMANDS.AGENT(A1L18),
//        DISP=(SHR,KEEP,KEEP)
//        DD DSN=UCC7.PARMS.AGENT(PARM#xx),
//        DISP=(SHR,KEEP,KEEP)
/*

Content Questions: AIS Support | Support Questions: AIS Support