topic Re: List of all Schema Load Schedules in Administrative Discussions

List of all Schema Load Schedules

srini_ch — Mon, 10 Oct 2022 17:08:20 GMT

Hi All,

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

Version - 5.1.4

Thanks,

Srinivas

Re: List of all Schema Load Schedules

Rasha — Mon, 17 Oct 2022 11:35:27 GMT

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

Re: List of all Schema Load Schedules

srini_ch — Wed, 19 Oct 2022 16:14:35 GMT

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

Re: List of all Schema Load Schedules

srini_ch — Fri, 11 Nov 2022 18:52:44 GMT

Hi,

Is there any update on this request?

Thanks,

Srini

Re: List of all Schema Load Schedules

amit_kothari — Sun, 13 Nov 2022 17:44:13 GMT

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

Re: List of all Schema Load Schedules

srini_ch — Tue, 15 Nov 2022 18:39:01 GMT

Hi Amit,

Thanks for the above solution. After deploying the above, I noticed below,

1. Few schema load schedules are missing their "Recur Every" part in the QRTZ_SIMPROP_TRIGGERS table.

2. We have a few schema loads schedules to run only between business hours, and how do we show the between hours in the report.

Thanks,

Srini

Re: List of all Schema Load Schedules

srini_ch — Thu, 29 Dec 2022 19:40:34 GMT

Hi @amit_kothari ,

Is there any update on how to address the above ask?

Thanks,

Srini