Custom Hooks for IW Extensions

The AIF DATA POM schedule contains 20 generic jobs that support execution of custom PL/SQL procedures in IW. Use these jobs in POM to hook your extensions directly to the AIF DATA batch schedule for automated nightly execution without needing to rely on DBMS_SCHEDULER jobs. 10 of the jobs are placed just after the staging table loads, but before any internal tables are populated, allowing you to edit or create data in staging tables directly. The other 10 jobs are placed at the end of the AIF DATA batch for custom post-load activities.

Table 9-1 AIF DATA Jobs for IW

Job Name	Description
RI_IW_CUSTOM_1_JOB	Execute Custom IW Procedure 1
RI_IW_CUSTOM_2_JOB	Execute Custom IW Procedure 2
RI_IW_CUSTOM_3_JOB	Execute Custom IW Procedure 3
RI_IW_CUSTOM_4_JOB	Execute Custom IW Procedure 4
RI_IW_CUSTOM_5_JOB	Execute Custom IW Procedure 5
RI_IW_CUSTOM_6_JOB	Execute Custom IW Procedure 6
RI_IW_CUSTOM_7_JOB	Execute Custom IW Procedure 7
RI_IW_CUSTOM_8_JOB	Execute Custom IW Procedure 8
RI_IW_CUSTOM_9_JOB	Execute Custom IW Procedure 9
RI_IW_CUSTOM_10_JOB	Execute Custom IW Procedure 10
RI_IW_PRELOAD_CUSTOM_1_JOB	Execute Custom IW Preload Procedure 1
RI_IW_PRELOAD_CUSTOM_2_JOB	Execute Custom IW Preload Procedure 2
RI_IW_PRELOAD_CUSTOM_3_JOB	Execute Custom IW Preload Procedure 3
RI_IW_PRELOAD_CUSTOM_4_JOB	Execute Custom IW Preload Procedure 4
RI_IW_PRELOAD_CUSTOM_5_JOB	Execute Custom IW Preload Procedure 5
RI_IW_PRELOAD_CUSTOM_6_JOB	Execute Custom IW Preload Procedure 6
RI_IW_PRELOAD_CUSTOM_7_JOB	Execute Custom IW Preload Procedure 7
RI_IW_PRELOAD_CUSTOM_8_JOB	Execute Custom IW Preload Procedure 8
RI_IW_PRELOAD_CUSTOM_9_JOB	Execute Custom IW Preload Procedure 9
RI_IW_PRELOAD_CUSTOM_10_JOB	Execute Custom IW Preload Procedure 10

Here are the steps to enable the functionality:

1. Create a package and package body for the process that needs to be run as a custom job. After logging into AI Foundation, from the task menu go to Innovation Workbench -> Manage Workbench and then in APEX go to SQL Workshop. On the right-hand side click Package under Create Object and proceed with creating the package specification, package body, and create the procedure inside the package body.

2. Back in AI Foundation, use the Manage System Configuration screen in the Control Center to modify the table RI_CUSTOM_JOB_CFG and edit values for the following columns:

   1. PACKAGE_NAME: Enter the name of the package that was created in IW.

   2. PROCEDURE_NAME: Enter the name of the procedure in your package that was created in IW.

   3. PROCEDURE_DESCR: Enter a description, if desired.

   4. RUN_TIME_LIMIT: The run time limit is 900 seconds by default. It can be changed to a different value if needed. If the custom process runs for longer than the value indicated in RUN_TIME_LIMIT when running as a part of the batch process, the custom process will stop and move on to the next job/process.

   5. CONNECTION_TYPE: Valid values are LOW and MEDIUM. This value should almost always be LOW unless the job is supposed to run a process that would need multiple threads. HIGH is not a valid value. If HIGH is entered, it will switch to LOW by default when the job runs.

   6. ENABLE_FLG: Set this value to Y to indicate that this job should be executed as part of the batch process.

3. The POM jobs should be enabled in the Nightly batch once configured. Alternatively, you may use the ad hoc process RI_IW_CUSTOM_ADHOC to run the jobs outside of the batch.

Because these jobs are added as part of the nightly batch, they do not allow extended execution times (>900 seconds) by default. If you are building an extension that requires long-running jobs, those should be scheduled using the DBMS_SCHEDULER package from within IW itself so that you don’t cause batch delays.