Is it possible to take the output of a select into variable

[Sreenivas]

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

[kduke]

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.

[Sreenivas]

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.

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.

[meena]

Hi,
Use routine activity in your sequencer and call the routine which is in Routine folder "Routine/SDK/Utility/UtilityHashRead" and pass the value in the argument.

Please let me know how can i use UtilityHashRead to read value from Hash file to pass as a Sequence Job parameter.

[kris007]

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.

[Dsnew]

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.

[kris007]

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.

Code: Select all

UtilityHashLookup(HashFileName, "X", "1") where X is the hardcoded key value 

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.

[Dsnew]

Thanks for the explaination.

I am familiar with using SETFILE to set pointer inside VOC.
Do we have to write a routine to do it, or is ther another way to automate this.

[kris007]

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.

[kduke]

Put your hashed file in the current project then you do not need SETFILE.

[Sreenivas]

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.

Hi Kris,

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

[Dsnew]

Put your hashed file in the current project then you do not need SETFILE.

Will this not clutter up the project space?

What is the best way to decide on the destination of a hashed file?

[kris007]

You won't need a RoutineActivityStage in between your Jobs.

Code: Select all

JobActivity1->>JobActivity2

In your JobActivityStage2, for the last parameter, rightclick and choose DSTransform>>>sdk>>>Utility>>>UtilityHashLookUp and then pass the values in here.

but i do not get any thing if i right click on Expression it only gives copy, paste options

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?

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.

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.

[Dsnew]

when i use the UtilityHashLookUp Routine in my JobActivity's expression box i get this error

Code: Select all

Variable 'XXX' not defined.

My hash file being XXX

I am using

Code: Select all

UtilityHashLookup(XXX, Key, 1)

Is it wrong? Is it something with quotes ?

[chulett]

Lack of quotes, actually. Of course, you could just try it and see... worst case you'll get a different error.