ExecuteSQL function within User Variable activity

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

ExecuteSQL function within User Variable activity

Post by brock125 »

I came across a webpage where somebody showed how you could use the ExecuteSQL function within a user variable activity in a job sequence to create a variable. There was very little explanation of how exactly to do this on the webpage though. Has anybody done this before and if so, do you know of any syntax explanations available online? I'm trying to do something like this to get a specific date range (Oracle), midnight of previous day and then 24 hours back from that date (so midnight to midnight). I'd like to create a variable for each timestamp.

This is the example I came across along with a short description that was provided. Can anybody better explain just exactly what this is doing? I understand the SQL part of it. I'm curious about the parameters that are being passed. I don't understand what the "PS_DB" would represent.

Trim(Ereplace(ExecuteSQL(PS_DB.P_DB_NM, PS_DB.P_DB_USER, PS_DB.P_DB_USER_PWD, "D", "SELECT trunc(current_timestamp) - 15 FROM DUAL"), @FM, ""))

Here, one need to provide Database details like database name, Server name, user , password. In above example these details have been passed using parameters and calculated date using simple oracle statement.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Any chance you still have a link to the website? Would be curious to see the discussion.

I don't recall there being an "ExecuteSQL" function or routine available out of the box, so guessing it is one they built in-house. And also guessing what it does is call sqlplus (or whatever passes for their command line interface) and passes in the credentials needed and the SQL string to execute. Must be using DSExecute() to do the actual dirty work, hence the need to strip out any Field Marks from the returned dynamic array.

Looks like PS stands for "Parameter Set" with the "P_" prefix used for the parameters it includes: database name, user, password. No clue what the "D" might mean, however. Return a DATE, perhaps?
-craig

"You can never have too many knives" -- Logan Nine Fingers
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Post by brock125 »

chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Okay, so... basically worthless. Just going to stick with my earlier reply. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Post by brock125 »

Craig,

Maybe you can help me with doing this a different way. I have an existing job sequencer that has an Execute Command Activity that pulls a start and end date from a UNIX file. It then uses those two dates to create two variables in a User Variable Activity (Start Date and End Date). Those two date variables are then used in the Oracle queries within parallel jobs.

I'm trying to do away with the Execute Command Activity to pull the dates from the UNIX file and instead create the same date values from within the User Variable Activity. The current dates are 1 AM from the same day it runs and back 24 hours (i.e. the Oct 20 run would have dates of 19-Oct-16 01:00:00.000000000 AM to 20-Oct-16 01:00:00.000000000 AM). This date range is then used in an Oracle query to pull the specific data. I was thinking I could use the DSJobStartTimestamp DS Macro and then manipulate it accordingly. Unfortunately many of the built in functions that are available in a transformer stage (specifically something like TimeFromMidnightSeconds) aren't available in the User Variable Activity.

Do you know if there is a DataStage equivalent to an Oracle TRUNC(SYSDATE) which sets the time part to 12 AM? I'm looking for something to default the time part on the DSJobStartTimestamp to midnight. I figure I can then add/subtract the appropriate time from that to get to 1 AM on both days. I thought about using timestamp_from_date(DSJobStartDate) but timestamp_from_date isn't available in the User Variable Activity either. Any ideas you (or others) have would be greatly appreciated.
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Post by brock125 »

You can disregard this. I believe I've figured out a way to do this.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Okie dokie... let us know where this goes next.
-craig

"You can never have too many knives" -- Logan Nine Fingers
brock125
Premium Member
Premium Member
Posts: 14
Joined: Wed Dec 14, 2011 9:03 am

Post by brock125 »

My solution for this is below. It was a fairly basic solution in the end.

The job is scheduled to run at 12:01 AM everyday. I created a environment variable (default is 0) in case there are issues with the run and it's delayed more than a day. This was the main reason we're going away from the UNIX script to create the dates. It's much easier to change the value on the environment variable as opposed to getting the UNIX script changed (at least it is in our environment). Just to clarify one thing, the timestamps in our database are GMT. I wanted to point that out before somebody asks why I'm running the job at 12:01 AM (CST) but the EndTime is 1:00 AM.

StartTime: Oconv(Iconv(DSJobStartDate,"DYMD") -1 -$Ev_START_DATE_ADJ,"D-DMY[2,A3,2]") : " 01:00:00.000000000 AM"

EndTime: Oconv(Iconv(DSJobStartDate,"DYMD"),"D-DMY[2,A3,2]") : " 01:00:00.000000000 AM"
dimension
Premium Member
Premium Member
Posts: 1
Joined: Sun Apr 17, 2011 10:32 am

Re: ExecuteSQL function within User Variable activity

Post by dimension »

Hi was anyone able to figure out the sql connection/statement to get a single value from a table into the user variable, to then be used by downstream sequences? I am trying to do this without landing the value in any file due to it's sensitivity...
thanks
dsci
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

Scroll up and read Craig's first reply in the thread. That pretty much sums up what you would need to do.

Mike
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

... and if you end up needing more help with that, please start a new discussion with all the appropriate details of your particular question / issue.

Thanks.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply