Bulk Update Trigger RUN_PROGRAM via Database - Which Tables to Update

[Puneet Kalra]

I need to bulk update the RUN_PROGRAM field for multiple job triggers (changing python3 to uv run --no-sync across ~hundreds of jobs), and doing this manually through the GUI is not feasible.

Current Approach:
I've been updating the SCHEDULING_ENTITY / SCI_C_ SCHEDULING_ENTITY   table directly:

-- ID to ensure one job is updated for testing.

UPDATE SCHEDULING_ENTITY

SET RUN_PROGRAM = REPLACE(RUN_PROGRAM, 'python3 ', 'uv run python ')

WHERE LOWER(RUN_PROGRAM) LIKE '%send_datadog_metric.py%' and ID =1234;

COMMIT; The Problem:
Database queries show the updated values, but the schedulix GUI still displays the old RUN_PROGRAM values. Let me know the solution.

[Ronald Jeninga]

Hi,

NEVER EVER update the database manually as long as you don't EXACTLY know whet you are doing!

And if, shut down the scheduling system first.

Believe me, as one of the main architects, database designers and programmers of the system, I've found out very early that manually manipulating the database is the fastest was to destroy the system.

The server reads all information it is going to need at startup.

During processing it will modify values in memory, in a safe way such that it can roll back any transaction as long as it isn't committed yet.

Then at the end of each transaction it will flush the changes to the database.

The purpose of this approach is twofold. 

First of all it minimizes the communication with that database system (due to an optimized write through strategy) which leads to a high performance system.

Secondly, taking advantage of database system properties, if something happens no data will be lost.

(The consequence is that you can "kill -9" the server without causing damage, the system would also survive a power failure or alike). 

This logic explains your observation (your "Problem").

But what you have created is a discrepancy between the internal state of the running server and the state of the database.

You are now on very, very thin ice!

From my perspective, the best thing to do now is to try to repair this as soon as possible.

This means, pray that the server didn't touch any of the rows you've updated and then try to find a query that'll revert your change, AFTER you've shut down the server.

Then you can restart the server and it should be fine.

Next you can use the official way of doing bulk updates.

Now if you are extremely lucky, you might get away with your change, because it is a relatively simple change, probably without too many side effects.

(One of the side effects will be that the server will be convinced that the run program has never ever been something else that the new value, which is obviously wrong).

If you'd like to take the risk, restart the server. If it works, you're lucky, if it breaks, you're screwed.

Don't blame me in case of the latter.

HOW TO PERFORM SUCH A BULK OPERATION CORRECTLY?

 You weren't that wrong about using the database. The point is: DON'T EVER write to to it yourself.

So

Step 1: Find the job definitions you'd like to change

SELECT ID

FROM SCI_C_SCHEDULING_ENTITY

WHERE LOWER(RUN_PROGRAM) LIKE '%send_datadog_metric.py%'

AND ID =1234;

(I assume you've limited your query to a single job definition for testing your change. That was a good idea as it limited the damage you've done).

Now, if you use the "shell" from the GUI, you can make this even a little fancier due to an extension of the select statement I've implemented:

SELECT ID

FROM SCI_C_SCHEDULING_ENTITY

WHERE LOWER(RUN_PROGRAM) LIKE '%send_datadog_metric.py%'

AND ID =1234

WITH ID JOB QUOTED;

Instead of the ID this query will now show the full qualified path with quoted identifiers.

Step 2: Modify the query such that it outputs a schedulix command instead of just an ID

That would look like

SELECT 'ALTER JOB DEFINITION ', ID, 'WITH RUN PROGRAM = ''', REPLACE(RUN_PROGRAM, 'python3 ', 'uv run python '), ''';'

FROM SCI_C_SCHEDULING_ENTITY

WHERE LOWER(RUN_PROGRAM) LIKE '%send_datadog_metric.py%'

AND ID =1234

WITH ID JOB QUOTED;

Note, I didn't test this and you'll have to pay attention to the correct quoting and maybe some other details of the resulting output.

Step 3: Copy and paste the output into the GUI shell and execute it

First of all, you can easily test the logical and syntactical correctness of the statements you've generated.

Just start with an extra line

BEGIN MULTICOMMAND

/* Here insert your statements */

END MULTICOMMAND ROLLBACK;

When executing this, the server will execute your statements but perform a rollback instead of a commit in the end.

Hence if the feedback tells you that the statement was aborted because a rollback was requested, the statements are both syntactically and logically correct (logically here means that the server is able to process the statements).

If you get some other reply, something is wrong with your statement.

As soon as you are happy with the intermediate results, you remove the ROLLBACK option from the MULTICOMMAND, execute the statement and you're fine.

Exactly this is the official way of performing bulk updates.

In fact if you'd do the changes through the GUI one by one, the set of commands performed by the GUI would effectively match the commands you've generated .

The language specification can be found in the Command Reference.

Hope that helps!

Good luck and best regards,

Ronald