Rss Categories

Report of Graduates

Views: 567 Created: 2019-06-25 01:15 Last Updated: 2019-07-29 16:44

Filename:    ReportOfGraduates.doc

Author:        L. Sweeney

Created:      7/17/2017

Updated:     6/24/2019


Detail Procedures for Downloading and Uploading SQL Scripts

1. Check for email with sql script attachments from TBR Contact person (currently Melissa Hunter).

2. Click on file attachments and download sql files from email attachment.

3. Open Filezilla for test (mdeltat) or Prod (mdeltap) with username banner/pw. Change to directory /home/banner (cd /home/banner).

If older versions of the output files already exist, rename them for backup purposes (rename arfile.dat to arfile.YYYYMMDD).

4. Verify current sql scripts are already on directory. If not, upload sql scripts downloaded from TBR email attachments to /home/banner directory.

5. Access Linux using Putty

6. Logon to mdeltat or mdeltap.

7. Change directory to /home/banner (cd /home/banner) and list the script files

(ls -lrth grads2019*).

If the sql script files are not found, repeat steps 4 - 7.


How to Run SQL Scripts

1. While logged on using Linux with Putty, verify script files exist in the correct directory. If not, repeat download and upload steps.

2. Type .  oraenv and type environment name (PROD or TSA8) and press Enter. Next, type sqlplus to start a SQL session (Can use sqlplus / as sysdba)

3. Enter username saturn, press Enter, and then the password.

4. At SQL prompt, type @ in front of the script name.

Run each of the 3 files as shown (order is NOT important)

1) grads2017_Updated.sql

Input: 2019, 2, 70 – Output: arfile.dat


2) grads2017_Updated_summary.sql

Input: 2019, 2, 70 – Output: arfile_summary.fle


3) grads2017_EDIT_Updated.sql

Input: 2, 70, 2019 – Output: rogedit.csv


SQL> @grads2017_Updated.sql


Sample input values:

Enter Spring Grad Year (Like 2008 for the reporting period 2007-2008): 2019

Enter System Code (It should be one digit): 2

Enter Institution Code (It should be two digits): 70


5. Output files from sql scripts:





6. When sql scripts are complete, exit sql session and verify with ls -lrt command

7. Type exit to leave sqlplus session and return to Linux prompt

8. Type ls -lrt to view the latest files.

9. Verify output files exist with non-zero file size

If output files not found (i.e. arfile.dat) rerun the sql!


How to Send Output Files

1. Email Institutional Research staff when files are ready (in Test or Prod).

2. On Test or PROD, open FileZilla, copy output files to folder under Easdoc TBR Historical Files\ReportGrads (i.e. June_2019) for IR staff to view.

3. After IR staff confirms arfile.dat is ready to send to TBR:

          Rename output file on Prod server mdeltap dir /home/banner

          To rename in Linux:

          mv arfile.dat  AR2019G70

          (This example uses year 2019. Update year when appropriate).


To rename using FileZilla:

Login to mdeltap (or mdeltat), access dir /home/banner, click on filename, press F2 (or right click and select Rename)


Rename arfile.dat to AR2019G70.dat


YYYY – Year (2019)

CCC - TBR institution code (G70)

4) Upload file to TBR server.

          To upload in FileZilla:

          Log in as TBR and upload file

From – Easdoc folder TBR Historical Files\ReportGrads\ MonthYear

To – TBR server directory /campus_data/stcc/from_campus.


To upload in UC4:

          Run Job STCC_FILE_TRANSFER (Enter filename and directory path)



Special notes!

1) Only the renamed version of arfile.dat is uploaded to TBR. The other files do NOT need to be uploaded (summary and edit files are for IR staff).

2) IR staff may request the file to be named ARYYYYCCC_PRELIM.

Example: AR2019G70_PRELIM

3) Verify jobsubs are active and working on Test Environment (check with DBA)

4) Look for output files in Filezilla by sorting on Last Modified column and on Linux using Putty with the list command: ls -lrth

5) Type . oraenv to view which Test SQL environment you are on. Example: ORACLE_SID = [TSA8] ?

Press Enter key to accept default environment

Type . oraenv to select another Test SQL environment name

6) If some scripts take a while to run, start them at the end of the day and review the output the next day.