Run UV SQL via DataStage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 35
- Joined: Thu Apr 19, 2007 12:42 pm
Run UV SQL via DataStage
I would like to execute UV SQL via DataStage and not an AIX script. Can this be done?
-
- Participant
- Posts: 152
- Joined: Tue Jan 13, 2009 8:59 am
Re: Run UV SQL via DataStage
[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???
did you try with "UNIVERSE" STAGE in a server job???
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 35
- Joined: Thu Apr 19, 2007 12:42 pm
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
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
Try...
Not sure why there was a second 'where' in there.
Code: Select all
SELECT INSTANCE, DTM, MODIFIER, REASON FROM UNNEST DS_AUDIT ON MODS WHERE CLASS = '2' AND INTERFACE = 'MYJOB'
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 35
- Joined: Thu Apr 19, 2007 12:42 pm
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 35
- Joined: Thu Apr 19, 2007 12:42 pm
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!!
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!!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.)
Also you might try using EVAL (though the SQL expressions seem to be working OK).
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
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
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.