List of all Schema Load Schedules

[srini_ch]

Hi All,

Is there a way to retrieve all the schema load schedules with details in a tenant?

Version - 5.1.4

Thanks,

Srinivas

[Rasha]

Hi Srini,

Please Try the following query using Incorta metadata DB:

SELECT
    A.NAME AS "Schema_Name",
    B."NAME" AS "Job_Name",
    B.DESCRIPTION AS "Description"
FROM
    _IncortaMetadata.SCHEMA A
    JOIN _IncortaMetadata.SCHEDULER_JOB B ON A.ID = B.ENTITYID
WHERE
    (B.JOBTYPE = 1)
    And B.ENTITYTYPE = 5

[srini_ch]

Hi Rasha,

The above query will only help us retrieve the Job Names by schema. But we would like to know the details (similar to Schedule Screen - Like timings, Load Type, Days etc) of the schedule of each job.

Thanks,

Srini

[srini_ch]

Hi,

Is there any update on this request?

Thanks,

Srini

[amit_kothari]

Hi @srini_ch  - 

I created a dashboard that which shows the dashboard and schema schedule information (attached) . For this I had to import couple of new tables into Incorta metadata schema to make it work. If these tables and joins already exist then dont have to do these changes. Below are the changes to be done in Physical schema (_IncortaMetadata)

• Import following table

QRTZ_TRIGGERS
QRTZ_SIMPROP_TRIGGERS

• Create a formula column in the SCHEDULER_JOB table. Defined the column name as ID_String. Below is the formula logic

string(
  _IncortaMetadata.SCHEDULER_JOB.ID
)

•  Define the join between SCHEDULER_JOB to QRTZ_TRIGGERS, USERS table (Refer to the screenshot)

SCHEDULER_JOB.ID_String ------> QRTZ_TRIGGERS.TRIGGER_NAME
SCHEDULER_JOB.OWNERID -----> USER.ID

•  Define the join between QRTZ_TRIGGERS to QRTZ_SIMPROP_TRIGGERS table