SQL statement or table to get job-global script link for "Call script in case of error"

[Steffi_Warnecke]

Hello there,

I got another question for the IDM folks here.

We're on IDM 7.2 and I'm doing some more documentation of my IDM system and now I'm looking for an good way to find out which global script is used in which job as the "Call script in case of error" option on the "Logging" tab of jobs.

Or for the first step: For which jobs this option is filled.

I'm not really looking forward to check each of our jobs manually, so I'm hoping some of you have already found the table or SQL statement to get this link.

.

The table "mc_job_script_link" sounds perfect for this, but it is empty for me (OPER-user), so no luck there.

.

Does anybody have any other idea or do I really have to go about this manually?

.

Regards,

Steffi.

[lambert-giese · Accepted solution]

You should have said you're on MSS right away 😉

Try this.

Kind regards, L

[Steffi_Warnecke · Accepted solution]

Weeeeell, I put it as a user tag. 😉

Thanks for that! I'll check it out when I'm back at my servers. Looking forward to use the extracted job infos for even more information after checking for the scripts. *g*

.

Regards,

Steffi.

[Steffi_Warnecke · Accepted solution]

Changed the accepted answer to this one, because of the script to find what I'm looking for. Works great! 🙂

I just have a question: In my testsystem I get some results where the column for "scriptname" is empty. Is the job description corrupt for those jobs? Meaning that there once was a script attached, but isn't anymore and the job definition wasn't updated properly?

[lambert-giese · Accepted solution]

From the query's structure, you should only get non-empty values for error_script, because the CROSS APPLY operator acts like an INNER join. You would only get a line in the result set if an XML element /mx:EMS/mx:DSEJobs/mx:Job/mx:Common/ERRFUNCTION exists in the job definition.

However, I have never tested what happens if such an element exists, but has empty content. Could be that this would produce lines with an empty error_script column.

To check what's the root cause, choose "Save as template" from the job's context menu in MMC. The .dst file will be the plain XML representation as stored in the database, just without BASE64 encoding. You could then have a look at the above XML element in the .dst file, and check whether an empty element is the cause of what you're seeing. If that is so, you could add a WHERE clause to the last SELECT to eliminate such lines.

And yes: maybe MMC behaves in a way that the element initally doesn't exist, but if an error script is added and later deleted, the XML ends up with an empty (instead of a non-existing) element. Try it out.

[Steffi_Warnecke · Accepted solution]

maybe MMC behaves in a way that the element initally doesn't exist, but if an error script is added and later deleted, the XML ends up with an empty (instead of a non-existing) element. Try it out.

.

Yep, that happens, I checked.

[lambert-giese]

Hi Steffi,

the error script is referenced inside the job definition from XML element /EMS/DSEJobs/Job/Common/ERRFUNCTION, which means you could report on it if you first BASE64-decode the job definition's XML (MC_JOBS.JOBDEFINITION), and then query the plain XML using your database's XQuery/XPath support in SQL. It's comparatively easy on MSSQL, but to do it in Oracle, you'll need custom PL/SQL code because the job definition is stored as data type LONG.

Kind regards, Lambert

[Steffi_Warnecke]

Hi Lambert,

I'm on MSSQL, but I have to admit I never ventured into the job definitions (decode them etc).

Thank you for the pointer, though. At least now I know it's in the definition (I feared so much). I'll check this mysterious decoding out. 🙂

.

Regards,

Steffi.