cancel
Showing results for 
Search instead for 
Did you mean: 

List of all Schema Load Schedules

srini_ch
Cosmonaut

Hi All,

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

Version - 5.1.4

Thanks,

Srinivas

6 REPLIES 6

Rasha
Employee
Employee

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

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
Cosmonaut

Hi,

Is there any update on this request?

Thanks,

Srini

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