Run UV SQL via DataStage

[dav_mcnair]

I would like to execute UV SQL via DataStage and not an AIX script. Can this be done?

[betterthanever]

[quote="dav_mcnair"]I would like to execute UV SQL via DataStage and not an AIX script. Can this be done?[/quote]

did you try with "UNIVERSE" STAGE in a server job???

[chulett]

In a Server or Sequence job, sure.

[ray.wurlod]

In a parallel job you can use a before/after job subroutine.

It may also be possible to use an ODBC Enterprise stage, but no promises here. Try it. You may need to be careful to make sure that the stage only executes on the machine where DataStage server engine is installed.

[dav_mcnair]

I have the UNIVERSE stage working returning rows but looks like some of the columns are multi valued. I am retrieving row from the DS_AUDIT table. I need an individual row per entry and not just one row. Is their a UV SQL keyword to output as multiple rows?

QUERY:
SELECT INSTANCE, DTM, MODIFIER, REASON FROM DS_AUDIT WHERE CLASS = '2' WHERE INTERFACE = 'MYJOB"

OUTPUT:
MYJOB,2008-01-14 00:00:002008-01-14 00:01:00,myuidmyuid,Designer editDesigner edit.

EXPECTED OUTPUT:
MYJOB,2008-01-14 00:00:00,myuid,Designer edit
MYJOB,2008-01-14 00:01:00,myuid,Designer edit

[chulett]

Try...

Code: Select all

SELECT INSTANCE, DTM, MODIFIER, REASON FROM UNNEST DS_AUDIT ON MODS WHERE CLASS = '2' AND INTERFACE = 'MYJOB'

Not sure why there was a second 'where' in there.

[dav_mcnair]

Tried the above SQL and received the error below. The UNNEST qualifier looks to be a field modifier based on the SQL documentation.

UV..uv_ds_audit.DSLink2: DSD.BCIOpenR call to SQLExecDirect failed.
Statement was:SELECT INSTANCE, DTM, MODIFIER, REASON FROM UNNEST DS_AUDIT WHERE CLASS = '2' AND INSTANCE = MYJOB'
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT". Scanned command was FROM UNNEST DS_AUDIT SELECT

[chulett]

You removed (or did not add) the "ON MODS" part of my posted sql, hence the error.

[ray.wurlod]

I also think you need INSTANCE rather than INTERFACE in the WHERE clause, unless you've modified the file dictionary of DS_AUDIT. The table clause is correctly UNNEST DS_AUDIT ON MODS because MODS is the name of an association of multivalued columns - if you like, the sub-name of the nested table.

[chulett]

Right, missed that. INSTANCE not INTERFACE.

[dav_mcnair]

Yeahh.. The inteface was a typo...

Also trying to run the SQL below to give me all the jobs that have changed since current date -1 and does not seem to be picking up the DTM in the where clause.. Any ideas?

-------------SQL----------
SELECT INSTANCE, DTM, MODIFIER, REASON,CAST(CAST(CURRENT_DATE -1 AS VARCHAR(10)) || ' ' || CAST(CURRENT_TIME AS VARCHAR(10)) AS VARCHAR(20)) AS DT FROM UNNEST DS_AUDIT ON MODS WHERE CLASS = '2' AND DTM >= CAST(CAST(CURRENT_DATE -1 AS VARCHAR(10)) || ' ' || CAST(CURRENT_TIME AS VARCHAR(10)) AS VARCHAR(20))

Thanks to all for you posts and help!!

[ray.wurlod]

Is it being picked up in the SELECT clause? Then maybe you can use the column alias name in the WHERE clause. (I haven't tried this.)

Code: Select all

SELECT INSTANCE, DTM, MODIFIER, REASON,CAST(CAST(CURRENT_DATE -1 AS VARCHAR(10)) || ' ' || CAST(CURRENT_TIME AS VARCHAR(10)) AS VARCHAR(20)) AS DT FROM UNNEST DS_AUDIT ON MODS WHERE CLASS = '2' AND DTM >= DT

Also you might try using EVAL (though the SQL expressions seem to be working OK).

Code: Select all

SELECT INSTANCE, DTM, MODIFIER, REASON, EVAL "OCONV(DATE() - 1, 'D-YMD[4,2,2]') : ' ' : OCONV(TIME(), 'MTS:')" FMT '20T' AS DT FROM UNNEST DS_AUDIT ON MODS WHERE CLASS = '2' AND DTM >= DT