How to change "check_amount" field from a trigger in all jobs?

[Alonso Gómez]

Hi,

I need to change the value of filed check_amount of a trigger defined in all jobs. Are there any posibility using sdmsh ?

Thank you.

[Ronald Jeninga]

Hi Alonso,

using sdmsh would be my first choice as well.

Anything else would potentially mean hours of stupid clicking; we didn't go to school for that ;-)

Basically we need to generate statements like

alter trigger triggername on job definition jobdefname

with check = period

;

As you already discovered, there is a table called TRIGGER_DEFINITION which can serve as a source to generate the statements of the above form.

Since triggers are part of the definition layer, it is a versioned object. And because we only need the triggers that exist now, we'll use the SCI_C_TRIGGER_DEFINITION to select from.

The period is something you already know. You probably would like to have a value like "5 minutes" or something alike.

For the rest I'll assume those "5 minutes", you can replace it yourself with the desired value.

The check option only makes sense in case of asynchronous triggers. Thus we'll select only those.

The first step is

SELECT name /* triggername */, fire_id /* the triggering job definition */

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL');

If you execute this in sdmsh, there is an additional feature you can use.

The second step is

SELECT name /* triggername */, fire_id /* the triggering job definition */

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL')

WITH fire_id job;

As you can see, we're almost there. So let's complete the statement.

Third step

SELECT 'alter trigger ', name /* triggername */, ' on job definition ', fire_id /* the triggering job definition */,

       ' with check = 5 minutes;'

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL')

WITH fire_id job;

The result looks pretty good already, but it is possible that some of the names have to be quoted.

Fourth step

SELECT 'alter trigger ', '''' || name || '''' /* triggername */, ' on job definition ', fire_id /* the triggering job definition */,

       ' with check = 5 minutes;'

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL')

WITH fire_id job quoted;

While testing this myself, I found that the alter trigger statement for async triggers demands a condition and the type.

Although I regard this a flaw (I'm going to fix that), there's nothing we can do about it now.

Hence, fifth step

SELECT 'alter trigger ', '''' || name || '''', ' on job definition ', fire_id,

       ' with check = 5 minutes, condition = ', '''' || condition || ''', type = ', type, ';'

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL')

WITH fire_id job quoted;

The result of this statement is a list of commands that can be executed with sdmsh.

It makes sense to test the procedure first, but in the end you'll do something like

-bash-4.2$ echo "SELECT 'alter trigger ', '''' || name || '''', ' on job definition ', fire_id,

       ' with check = 5 minutes, condition = ', '''' || condition || ''', type = ', type, ';'

FROM sci_c_trigger_definition

WHERE type in ('UNTIL_FINISHED', 'UNTIL_FINAL')

WITH fire_id job quoted;

" | sdmsh | grep '^alter trigger' | sdmsh

Connect

CONNECT_TIME : 15 Mar 2019 10:52:45 GMT

Connected

[SYSTEM@localhost:2506] SDMS> alter trigger  'CHECK_RUNTIME'  on job definition  SYSTEM.'EXAMPLE_JOBS'.'ASYNC_TRIGGER_JOB'  with check = 5 minutes, condition =  '(int("0" + $STARTTIME) > 0) and int($SYSDATE) - int("0" + $STARTTIME) > 300', type =  UNTIL_FINISHED ;

Trigger altered

Hope that helps.

Best regards,

Ronald

[Alonso Gómez]

Thank you Ronald,

It is a great help.