Hi All,
We have a scenario where we update the same audit table and column across multiple processes for different values in where clause, once when a Sequence job starts and finally as the last step when the Seq job ends.
The only change in the update query across different seq jobs is the value in the where clause changes based on the process
e.g.
Update TableName Set ColName1 = sysdate where Colname2 = 'XYZ'
Update TableName Set ColName1 = sysdate where Colname2 = 'ABC'
We are trying to pass the value 'XYZ' as the InvocationID and use it this way in the SQL statement
Update TableName Set ColName1 = sysdate where Colname2 = #DsJobInvocationId#
But this does not work..Can we or Can we not use the InvocationID this way in the where clause
(I believe it works if we use the InvocationId for the tablename in this query)
Multi Instance Job - DsJobInvocationId
Moderators: chulett, rschirm, roy
"Does not work" meaning what? It should work just fine as far as I know but you might want to try this instead of what you posted:
But then again it depends on what "does not work" means.
Code: Select all
Update TableName Set ColName1 = sysdate where Colname2 = '#DsJobInvocationId#'
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
By Not working I mean it does not update the record when we pass the Invocationid to be used as part of the Where Clause (We verified it is a valid value and should update 1 record)
I already tried using the single quotes around it, as you suggested, but then it treats that as a string value and does not evaluate the InvocationId. So does not update the record in this case as well
Any other way this can be achieved
I already tried using the single quotes around it, as you suggested, but then it treats that as a string value and does not evaluate the InvocationId. So does not update the record in this case as well
Any other way this can be achieved
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In the past I've always doubled up - the Invocation ID governs the instance name, and I also pass the same value as an explicit job parameter. This approach works.
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.
Then something is not right about how you are specifying the macro as I recall doing that exact same thing all the time back in the day and it will certainly evaluate the invocation id and then use it within the single quotes. Make sure you match case exactly with what the name of it is and if you are typing it in by hand use the built-in helper instead. I don't have any docs with me during the day, perhaps it is "DSJobInvocationId"... case matters. Double-check.synsog wrote:I already tried using the single quotes around it, as you suggested, but then it treats that as a string value and does not evaluate the InvocationId.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers