FPSINPUT.STARTUP_CREATE_DBMS_JOBS

>

> FPSINPUT.WIP_WAFER_HIST_LOOP

View FPSADMIN.STARTUP_CREATE_DBMS_JOBS

When we startup a new facility using database links for ETL we must create the DBMS_SCHEDULER jobs to call ADM_LOAD_DWH to load the data. These jobs should be reasonably standard at most link-based sites so this view stores a template to use for startup at any site. Please review the script particularly the hour to run the StWk job to ensure that you schedule them at the correct time for your site. It is important to note that the repeat_interval is based on the start time of the previous run. Therefore the next scheduled execution time might arrive while the job is still running. If so, the new instance of the job will wait to start until the current one completes. There is no easy way to schedule based on the end time of the previous run. We use the FREQ syntax rather than the SYSTIMESTAMP syntax for repeat_interval because we have observed some strange behavior with SYSTIMESTAMP syntax where it shows the next_run_time in the past. The FREQ syntax gives the same behavior and seems a bit safer. For some good examples on using the repeat_interval field creatively see this link: https://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN10040 BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.ADM_LOCKED_HIST_ONE_MIN' ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=1' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOCKED_HIST_INSERT;' ,enabled => true ,comments => 'This records any rows from ADM_LOCKED_STATUS into ADM_LOCKED_HIST every minute.' ); END; (slash) BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.DWH_ETL_REALTIME' ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=59' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOAD_DWH(''RealTime'');' ,enabled => false ,comments => 'This is the real time job which just runs repeatedly. The default here is to run every 59 seconds (and this means to start every 59 seconds) but we might want to set more frequent in production.' ); END; (slash) BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.DWH_ETL_5MIN' ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOAD_DWH(''5min'');' ,enabled => false ,comments => 'This is the 5 minute job which usually contains WIP_LOTS_STATIC and VERIFY which we want to refresh frequently but not in parallel with the RealTime job.' ); END; (slash) BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.DWH_ETL_10MI' ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=11' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOAD_DWH(''10mi'');' ,enabled => false ,comments => 'This is the 10 minute job which usually contains DASH tables like GANTT and LINE_HOLDS which take a bit longer to refresh.' ); END; (slash) BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.DWH_ETL_30MI' ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=31' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOAD_DWH(''30mi'');' ,enabled => false ,comments => 'This is the 30 minute job which usually contains RTG and EQP updates.' ); END; (slash) BEGIN SYS.DBMS_SCHEDULER.CREATE_JOB ( job_name => 'FPSADMIN.DWH_ETL_EASH' ,repeat_interval => 'FREQ=DAILY; BYHOUR=1' ,end_date => NULL ,job_class => 'DEFAULT_JOB_CLASS' ,job_type => 'PLSQL_BLOCK' ,job_action => 'ADM_LOAD_DWH(''EaSh'');' ,enabled => false ,comments => 'This script is called EaSh for each shift but we generally run daily starting at 01:00 since it is really slow due mainly to CTM_SUMMARY.' ); END; (slash)