Batch Queue The Possibilities Presented by Warren Lord
Batch Queue: The Possibilities Presented by: Warren Lord, Northeastern University 1 | CBORD 2018 User Group Conference | Presentation
Automation with Batch Queue Topics Covered • Overview of Northeastern University • Overview of Batch Queue • Northeastern use of batch queue • Details of the student locker integration with batch queue • Conclusion 2 | CBORD 2018 User Group Conference | Presentation
Northeastern University Quick Facts • Founded in 1898 • Campuses in Boston, Charlotte, Seattle, San Jose, Toronto • 38, 000 total enrollment • 13, 500 undergraduates • 8, 300 students in resident halls • 37 Residence Halls 3 | CBORD 2018 User Group Conference | Presentation
Overview of Batch Queue • Used for nightly processing • Used to schedule running of scripts • Can use it to spool files, such as creating mp files • Can use it to insert, update, and delete records • Can use it to automate sending of emails • Anything you can do with SQL 4 | CBORD 2018 User Group Conference | Presentation
Batch Queue via CS Gold GUI 5 | CBORD 2018 User Group Conference | Presentation
Batch Queue via CS Gold GUI 6 | CBORD 2018 User Group Conference | Presentation
Batch Queue via Command Line • Run on the TPS • bq /? – for help screen • bq show – shows queues/jobs/status • bq start /manager – start queue manager • bq stop /manager – stop queue manager 7 | CBORD 2018 User Group Conference | Presentation
Batch Queue via Command Line bq show 8 | CBORD 2018 User Group Conference | Presentation
Batch Queue via Command Line bq /? 9 | CBORD 2018 User Group Conference | Presentation
Northeastern Batch Queue Jobs 3 TPSs run batch queue jobs – share load of Fullmerge and Inc. Merge 10 | CBORD 2018 User Group Conference | Presentation
Northeastern Batch Queue Jobs • RMS Import (every 2 mins) • Housing Sync Procedure (as needed) • Updating Pins (once a day) • Deactivating access (once every week day) • Integrating student lockers with CS Gold (multiple times a day) • Integrating EMS with unlocking AD 400 locks (once a day) • Donating meal swipes (every 5 mins) 11 | CBORD 2018 User Group Conference | Presentation
Integrating Student Lockers • Student flag/patron group to allow access to a web page • Students use a web page to pick a locker • Locker reservations stored in local_locker table in the Diebold schema • Gave iqtps select, update privileges on the local_locker table • Batch Queue job used to extract reservations • Student Locker system consumes the reservations 12 | CBORD 2018 User Group Conference | Presentation
Student Lockers – lockerupdate. sql file (1) set newpage 0 set serveroutput off set termout off set linesize 500 set trimspool on set space 0 set echo off set heading off set feedback off set verify off set pagesize 0 13 | CBORD 2018 User Group Conference | Presentation
Student Lockers – lockerupdate. sql file (2) update local_locker set exportdate=sysdate where patronid is not null and leffective is not null and lexpiry is not null and exportdate is null and rentstatus='R'; commit; 14 | CBORD 2018 User Group Conference | Presentation
Student Lockers – lockerupdate. sql file (3) spool \nunet. neu. eduSHARESgriffinRelaxxlockerinfo. csv select suid || ', ' || val_univ_id|| ', ' ||val_nam_last || ', ' || val_nam_first || ', ' || to_char(leffective, 'DD/MM/YYYY') || ', ' || to_char(lexpiry, 'DD/MM/YYYY') || ', ' || smagid || ', ' || lockernumber || ', ' || lockergroupname || ', ' || to_char(exportdate, 'DD/MON/YYYY HH 24: MI') from local_locker, pers where patronid=id_pers and exportdate > (sysdate - 1/192) and patronid is not null; spool off; / EXIT; 15 | CBORD 2018 User Group Conference | Presentation
Student Lockers – lockerupdate. cmd file SQLPLUS %RDBMS_USER%@oraclegold/%RDBMS_PASSWORD% @%csg_server%customlockerupdate. sql 16 | CBORD 2018 User Group Conference | Presentation
Batch Queue via CS Gold GUI 17 | CBORD 2018 User Group Conference | Presentation
Student Lockers – sample output record 1252933143056000, 001447878, Doe, John, 10/09/2018, 15/12/2018, 6015247566129178, 202 00, 20 -RI, 11/SEP/2018 14: 32 18 | CBORD 2018 User Group Conference | Presentation
Conclusion • Batch queue is a very useful utility • You can use batch queue to schedule jobs or run jobs on demand • You can use SQL data manipulation commands • You spool mp files, insert records, update tables, and more • Batch queue helps to distribute load of Fullmerge, and Inc. Merge • If you create tables that need to used, give permissions to iqtps • Batch Queue in CS Gold GUI vs Batch Queue on the TPS command line 19 | CBORD 2018 User Group Conference | Presentation
w. lord@northeastern. edu Senior Director, Student Affairs Technology Thank You! 20 | CBORD 2018 User Group Conference | Presentation
We want your feedback! Please evaluate this session in the ERMobile app. Your feedback helps us improve future sessions. Find this session in the app, and touch COMPLETE SESSION SURVEY. Thank you! 21 | CBORD 2018 User Group Conference | Presentation
- Slides: 21