AutoSys Jobs, ZQL and Oracle Database

Since few months, I have been dealing with AutoSys Jobs at workplace and so far, have been running sendevent commands for triggering the AutoSys Jobs to handle commonly used scenarios, covered as follows;

  • CHANGE_STATUS
  • KILLJOB
  • FORCE_STARTJOB
  • STARTJOB
  • JOB_ON_HOLD, JOB_OFF_HOLD
  • JOB_ON_ICE, JOB_OFF_ICE

However, time had come that I started modifying the AutoSys Job definition using JIL, i.e. Job Information Language. So, to gain better understanding I started referring Unicenter AutoSys Job Management Reference Guide and have been exploring ever since.

AutoSys Relational Database

The interesting thing to know, the AutoSys Jobs definition, runtime information, etc is stored in Oracle Database and the guide states the following;

Because Unicenter AutoSys JM uses a relational database, you can query the database to supply custom reports and information.

Note: Using SQL commands to change information in Unicenter AutoSys JM tables may cause your system to fail. The Unicenter AutoSys JM tables and views listed in the following sections are for documentation purposes only. They are not intended to be used as a public interface and are subject to change between releases.

By default, the Unicenter AutoSys JM OS user is “autosys”. However, you may define a different ID for the OS user during installation.

So, the query language used for querying purpose is known as ZQL.

ZQL

If your Unix server has AutoSys installed, chances are you can view man pages as well for ZQL. I will give a brief overview in this blog post, through following ZQL query I wrote recently;

SELECT
A.as_group||','||B.text||','||A.job_name
FROM
<database>.ujo_jobst A,
<database>.ujo_intcodes B
WHERE
A.job_type = '<job_type-attribute>'
AND
A.as_group = '<group-attribute>'
AND
A.status = B.code
ORDER BY
job_name,
text
;

You may click on the hyperlink in the above query to learn more about the table/view definitions.

The column ujo_jobst.as_group (pulled from ujo_job2 table) contains the group information, which is usually specified in defining the AutoSys Job as follows;

group: <group-name>

The column ujo_jobst.job_type (pulled from ujo_job table) is straight-forward, contains either b/c/f, depending on whether its a Box/Command/File Watcher AutoSys Job.

job_type: b

The table ujo_intcodes, contains 3 columns only and code column is equivalent to the ujo_jobst.status column. So, the ujo_intcodes.text column contains values corresponding to each code like RUNNING, ACTIVATED, INACTIVE, FAILURE, etc.

As a result, I have pulled a report by executing ZQL script, having group-specific box AutoSys jobs along with their current status 😉

Advertisements

taT4Sql | Converting Timestamp to Decimal using Teradata SQL

If you have ever worked in Oracle databases, you might have across DUAL table. It’s considered to be system table (I guess) where you can do some operations, get some values like current date, time, etc. I don’t really remember other uses.

Anyways, moving on to Teradata, I thought of doing the same, i.e. pull current timestamp from system table. However, I never came across any DUAL table till now, instead I came to know that just doing the following will do the needful —

SELECT CURRENT_TIMESTAMP;

Now, here comes the hack for the day, my colleague asked me, whether its possible to convert timestamp into decimal format.

Trick 2 Transform..

I remembered doing some casting few months back. If you look at my previous tricks on Teradata SQL, you might remember too. So, this is how I do it —

SELECT CAST( SUBSTR( CAST(CURRENT_TIMESTAMP AS CHAR(19)),1,10) || SUBSTR( CAST(CURRENT_TIMESTAMP AS CHAR(19)),12,8) AS DECIMAL(18));

See ya later..

taT4Sql | Comparing different Date formats using Teradata SQL

In a recent post, I shared code on how to change timestamp format from one style to another. Today, we do similar tweaks for date formats, as I had to do comparison between dates having different formats, on either side of operand in WHERE clause.

Trick 2 Compare Diff Formats..

Suppose, your table has a column having format ‘YYYY-MM-DD’ and you have to do comparison with a column having this format ‘YYYYMMDD’.

SEL *
FROM SOME_TBL
WHERE
CAST( CAST( YYYY_MM_DD_CLMN AS CHAR(10)) AS INTEGER) = YYYYMMDD_CLMN

Hope this helps..

taT4Sql | Changing Timestamp Formats using Teradata SQL

While working on some project at work, I was making use of Teradata SQL for querying databases and loading some table by creating various volatile tables and doing numerous joins in-between them.

Few days back, I came to know  from the client side that, I have to pull some data from a table’s attribute VC having datatype , VARCHAR(N) and insert into another table’s attribute TS, whose datatype would be TIMESTAMP(6). That’s an issue, if data is not in right format and hence, the client stated the following..

So, data present in VC having format ‘YYYYMMDDHHMISS’ and I have to change it to format ‘YYYY-MM-DD HH:MI:SS’, so as to get the data in right format for TS.

Trick 2 Change Format..

INSERT INTO
     SOME_TS_TBL(TS)
SELECT
     SUBSTR(VC,1,4) || '-' || SUBSTR(VC,5,2) || '-' || SUBSTR(VC,7,2) || '  ' ||
     SUBSTR(VC,9,2) || ':' || SUBSTR(VC,11,2) || ':' || SUBSTR(VC,13,2)
FROM
     SOME_VC_TBL

Assuming, SOME_TS_TBL has only one attribute (TS) and SOME_VC_TBL has VC. This computation will do the trick and transformation.

HTH..