Is it possible to take the output of a select into variable
Moderators: chulett, rschirm, roy
Is it possible to take the output of a select into variable
Hi,
I have an issue with Incremental Load where i do not have a date or timestamp. We decided to go with a numeric field which will resolve the issue theoritically but i cannot store the same in an job control record.
Is it possible for me to select value from database into a parameter so that i can pass the same to use in where clause of another select query.
Thanks in advance.
Sree
I have an issue with Incremental Load where i do not have a date or timestamp. We decided to go with a numeric field which will resolve the issue theoritically but i cannot store the same in an job control record.
Is it possible for me to select value from database into a parameter so that i can pass the same to use in where clause of another select query.
Thanks in advance.
Sree
Sure, write a job to save it to a hashed file. Make the hashed file have a key and this value. Hard code the key to "Something" or whatever you like. Use UtilityHashRead to read this value into a stage variable in a job sequence. You can now use this value as a parameter. All the Utility routines are supplied by IBM in the SDK.
Mamu Kim
Thanks for your response, i understand till writing into a hash file. My requirement is to use that value in where clause of DRS stage. I know that we can use Job parameters inside DRS stage. Please let me know how can i use UtilityHashRead to read value from Hash file to pass as a Sequence Job parameter.kduke wrote:Sure, write a job to save it to a hashed file. Make the hashed file have a key and this value. Hard code the key to "Something" or whatever you like. Use UtilityHashRead to read this value into a stage variable in a job sequence. You can now use this value as a parameter. All the Utility routines are supplied by IBM in the SDK.
What you will need to do(after writing the value to the hashed file in a seperate job) is, in your job with DRS stage, you will create a parameter which you will be using in the sql within the DRS stage. In the Job properties, leave the default value of this parameter as blank. Now, in your job sequence, place this job on the canvas. While inserting parameters within the Job activity stage, for the parameter you are using within the SQL, right click within the expression box of that parameter and then call the UtilityHashLookUp Routine in there and enter the necessary values. Now, this value will pass on to the sql within your DRS stage and you will achieve what you need. I have done it in the past successfully. Come back if you have any questions.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Kris, i have a couple of questions
- How do i get the reference of the hash file when i call the UtilityHashLookUp Routine
in the expression box of the Job Activity?
- It asks me for 3 parameters even though the third one is non- mandatory.
- Do we need a pointer entry to the VOC, before calling it in the UtilityHashLookUp Routine?
If no, do we have to give the path of the pathed hashed file.
- How do i get the reference of the hash file when i call the UtilityHashLookUp Routine
in the expression box of the Job Activity?
- It asks me for 3 parameters even though the third one is non- mandatory.
- Do we need a pointer entry to the VOC, before calling it in the UtilityHashLookUp Routine?
If no, do we have to give the path of the pathed hashed file.
Do we need a pointer entry to the VOC, before calling it in the UtilityHashLookUp Routine?
Yes, you will have to create a pointer entry to the VOC if you are not creating the hashed file within the project.
It asks me for 3 parameters even though the third one is non- mandatory
UtilityHashLookup(%TableName%, %KeyValue%, %PositionToReturn%)
You will have to pass the hashfile Name, the key value(like Kim suggested, you will need to hardcode it to some value say X), and then the position of the value to return which in your case would be 1.
How do i get the reference of the hash file when i call the UtilityHashLookUp Routine in the expression box of the Job Activity?
I am not sure what exactly you are asking here. All you will need to do is call the Routine and enter the appropriate values or pass appropriate parameters.
Note: While creating a hashed file, create it with a dummy key column whose value should be hardcoded to somevalue within the Transformer stage and the second column is the column of your interest which holds the value.
Yes, you will have to create a pointer entry to the VOC if you are not creating the hashed file within the project.
It asks me for 3 parameters even though the third one is non- mandatory
UtilityHashLookup(%TableName%, %KeyValue%, %PositionToReturn%)
You will have to pass the hashfile Name, the key value(like Kim suggested, you will need to hardcode it to some value say X), and then the position of the value to return which in your case would be 1.
How do i get the reference of the hash file when i call the UtilityHashLookUp Routine in the expression box of the Job Activity?
I am not sure what exactly you are asking here. All you will need to do is call the Routine and enter the appropriate values or pass appropriate parameters.
Code: Select all
UtilityHashLookup(HashFileName, "X", "1") where X is the hardcoded key value
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Hi Kris,kris007 wrote:You have to execute the SETFILE command from the Administrator Client.
Select the Project and then click command and then execute the statement. You don't need any routine.
I am sorry to disturb you. I was trying to do the following and was not able to run it successfully.
a) I created a sequence with two Job Activities and one Routine Activity
b) The are in this sequence Job Activity > Routine Activity > Job Activity
c) First Job Activity is writing to a Hash File which is having two columns with column names as DUMM_NAME and PROCESS_INSTANCE with values as 'X' and 3450. This ran to Success.
d) Second is Routine Activity(Routine_Activity_7) in which i select UtilityHashLookup routine with Arguments as "MY_FILE","X","1" as i am interested in 3450 value. This ran to Success.
e) My last Job Activty has four parameters and first three are coming from environmental variables and last one is expected to get value from Routine, but i do not get any thing if i right click on Expression it only gives copy, paste options. I was able to select "Routine_Activity_7.$ReturnValue" (there are no quotes in original value) from the previous routine.
f) When i run the final sequence it passes through the first Job Activity and Routine Activity but third Job Activity is not at all initiated. The message is showing as the routine returned a value and then after sequence ends.
Hope i could explain the problem, please let me know if you are looking for additional information.
Thanks in Advance
Sree
You won't need a RoutineActivityStage in between your Jobs.
In your JobActivityStage2, for the last parameter, rightclick and choose DSTransform>>>sdk>>>Utility>>>UtilityHashLookUp and then pass the values in here.
Code: Select all
JobActivity1->>JobActivity2
One of the reasons you could face this is when you have created the parameter in the Job with type as FileName or List. If so, change it to a string or Integer. Other than that, I don't see a reason why you won't be able to choose the Routines or Transforms from the expression editor within the JobActivity Stage unless you are on a version earlier than 7.5. Which version of DataStage are you using?but i do not get any thing if i right click on Expression it only gives copy, paste options
It depends on how you have defined triggers coming out from your RoutineActivityStage. If you can give that information things could be explained from that point of view.When i run the final sequence it passes through the first Job Activity and Routine Activity but third Job Activity is not at all initiated. The message is showing as the routine returned a value and then after sequence ends.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
when i use the UtilityHashLookUp Routine in my JobActivity's expression box i get this error
My hash file being XXX
I am using
Is it wrong? Is it something with quotes ?
Code: Select all
Variable 'XXX' not defined.
I am using
Code: Select all
UtilityHashLookup(XXX, Key, 1)