DARTS : Daily Extract Maintenance

Information for Maintaining the Daily Extracts in PeopleSoft

The Jobsets

There are two jobsets UM_CS4 and UM_CS5 that run in CSRPT and CSPRD daily (Monday- Saturday). They are similar in both CSPRD and CSRPT, but the important ones for reporting run in CSRPT.

UM_CS4 starts at 2:15 AM and UM_CS5 starts at 5:15 AM in CSRPT. The examples given here are for CSRPT, but much of this applies to CSPRD as well. The jobsets can be modified or inspected by navigating to PeopleTools > Process Scheduler > Schedule JobSet Definitions in MaineStreet. From the search page, search by Job Name for "UM_CS4" and select the entry with the Active Schedule Status. You will see something like this:

The items of most interest on this page are the Status which should be Active and the Recurrence Name which is an object that determines when the JobSet is run. Click on the second tab labeled Schedule JobSet Items to see the jobs that are to be scheduled.

Expand the Process List by clicking the - signs to the left of UM_CS4. You now can see the jobs that will be run at 2:15 am. (The Run Control ID was not needed for UMCLSSCH.)

 In a similar manner, you can examine UM_CS5 and see the following:

As with UM_CS4 you should verify that the Status is Active. Go to "Process Monitor" to check the status of each job.

Moreover, there are two more job sets for Financial aid data. UM_FINA runs at 9 am on Tuesday, Thursday, and Saturday. UM_FINA2 runs at 9 am on Saturday.

More information is available on Jobsets (CSRPT) : Type and Sources and Jobsets (CSPRD) : Type and Sources.

When the information changes, modify this page: CSRPT Daily Extract Overview for end users.

The Email

An email is generated every day by the Application Engine UM_TABLE_CHK. This email is probably the highlight of your daily existence, so you probably see something like the following every morning:

If you are not getting this email, go to University of Maine System > Student Admissions > Batch Processing > Check Appl Extract . There is a list of IDs at the bottom of the page where you can add additional IDs. See Maintaining email address for automated batch process for more information.

If you see "Extracts have all run successfully in CSRPT" as your email header, you know all is right with the world and the critical jobs have run to success. If, however, you see "Extract Statistics", go and get an extra cup of coffee while whimpering softly because there is work to be done.

The first paragraph of the email summarizes the checks that have been done on the major extracts. All the extracts and archives should have reported a run status of "Success". If you see a run status of "Processing" or "Error", there may be a problem. If there are no changes at all in the number of records, I would be suspicious and investigate the reason.  The run status in this section comes from the job control table (PS_UM_EXTRACT_CNTL). This table is updated by the extracts themselves at various points in their processing, therefore many failures are shown as "Processing". To see the final status, refer to the next paragraph "The Last Jobs in the Queue" where you will see the final run status.

The Jobs

Here is a list of the jobs and the navigation to the components where they may be individually run. You need to log in as BATCH in order to change information under BATCH ID.

Jobs in UM_CS4:

(All jobs in CS4 start at the same time.)

UMAPPEXT

This is the applicant extract SQR. It Builds the PS_UM_APPL_EXTRACT table. Find it at University of Maine System > Student Admissions > Batch Processing > Admissions Extract.

Run the Applicant Extract as shown. Check the "Use System Date" box and no other check box. Do not check the "First Truncate Table" check box.  You may change the beginning term to a term no more recent than a year prior to the current term if the process is becoming slow.

UMPROEXT

This is the prospect extract SQR. It builds PS_UM_PROS_EXTRACT and is useless. If you must you can find it at University of Maine System > Student Recruiting > Batch Processing > Prospect Extract.

UMSTUEXT
This is the student extract SQR. If builds the extract table PS_UM_STUD_EXTRACT which is the most heavily used of the extract tables and upon which many other views are based. If this fails it will cause UMSTUARC to fail.
In CSPRD, navigate to University of Maine System > Records and Enrollment > Student Flat File Extract.
In CSRPT, navigate to University of Maine System > Records and Enrollment > Stdnt Rpt File Creation Page > Student Flat File Extract.

As before, keep the "Use System Date" check box checked and the other check boxes unchecked. The beginning term must be at least a year prior to the current term and don't even think about checking the first truncate table box.

UM_STUCRSTAB

This is an application engine that builds a student course extract table called PS_UM_D_STD_ENRL. It is run from the same page as UMSTUEXT.
In CSPRD, navigate to University of Maine System > Records and Enrollment > Student Flat File Extract
In CSRPT, navigate to University of Maine System > Records and Enrollment > Stdnt Rpt File Creation Page > Student Flat File Extract 

UMCLSSSCH
Here is an SQR to build the PS_UM_CLSS_SCH_FCL table.
In CSPRD, navigate to University of Maine System > Records and Enrollment > Build UM_CLSS_SCH_FCL. 
In CSRPT, navigate to University of Maine System > Records and Enrollment > Stdnt Rpt File Creation Page > Build UM_CLSS_SCH_FCL. 

The run control is much like the extract run controls. As before only check the Use System Date checkbox. Don't check the box "First Delete All Rows". The result will not be pleasing. Don't change the terms in the screenshot below. The SQR has a logic that changes terms automatically.

Jobs in UM_CS5:

(Each job in CS5 will not run until the previous job finishes due to the resource.)

UMSTUARC
This is an SQR run from the same page as UMSTUEXT. It builds the massive archive table named PS_UM_STUD_EXT_ARC and PS_UM_STD_ENR_ARC. It is found at University of Maine System > Records and Enrollment > Stdnt Rpt File Creation Page > Student Flat File Extract. UMSTUARC will wait up to 3 hours for UMSTUEXT to finish.

UM_APPACAD_P
This is an app engine without a run control page. It builds the PS_UM_APPACAD_PROG table. It is run from PeopleTools > Application Engine > Request AE.  The admit_term >= 0910 is hardcoded into the App Engine Program.

UM_PSQRYSQL
This is the same deal as UM_APPACAD_P above and has no run control page. This builds the PS_UM_PSQRYDFN_SQL table.

UM_TABLE_CHK
This is an app engine that checks the extracts and then sends an email. Masochists can do this all manually by running a bunch of queries or you can go to University of Maine System > Student Admissions > Batch Processing > Check Appl Extract. If you have had to rerun one of the extracts, run this again afterward. This is also the place to add people to the email list.  It runs immediately after UMSTUARC runs.

The term on the screen above controls this part of the email message:

UM_BLDITYPND
This is an app engine that builds the PS_UM_ITYP_TREENDS table. There is no run control page so run this as UM_APPACAD_P above.

UMEMPARC
This is a SQR that builds PS_UM_F_EMPLOY_ARC which archives the UM_F_EMPLOYEE table. Navigation is University of Maine System > Utilities > Archive UM_F_EMPLOYEE table.

Job in UM_FINA:

UMFAIDBF

This updates PS_UM_FIN_AID. It will take all day to complete the job.  

The navigation is University of Maine System > Financial Aid > Fin Aid View Processes > Backfill Financial Aid VW. (You need to log in as BATCH to change information under BATCH ID.)

Job in UM_FINA2:

UMFAIDDT

This updates PS_UM_FIN_AIDDT. It will take all day to complete the job.

The navigation is University of Maine System > Financial Aid > Fin Aid View Processes > Populate Fin Aid Dated Table. (You need to log in as BATCH to change information under BATCH ID.)

The solutions for issues

• CSPRD: When UM_CS4 in CSPRD fails or has issues, please notify Mike Higgins (michael.d.higgins@maine.edu). He will restart the job or fix the issues in CSPRD. 
• CSRPT: Please go to Common Problems and Solutions - CSRPT Daily Extract page for more information.