Parameter in WHERE clause

[jerome_rajan]

Hi All,

I am trying to create a generic job that can, given the right table name and key column, delete the required records. Since the job uses RCP, i had to write a custom SQL since i could find no way of specifying the key column in a schema file. My delete query looks like this.

Code: Select all

DELETE FROM #SCHEMA#.#TABLE# WHERE #KEYCOL#=ORCHESTRATE.#KEYCOL#

But datastage aborts with the following message

Code: Select all

DB2_Delete_Update,0: Statement DELETE FROM <tablename> WHERE KEY_1=? failed to run.

It has resolved all parameters but the last one which it has replaced with a '?'

Can someone throw some light on how to find a work-around to fulfill this requirement?
Thanks in Advance

[bhasds]

Hi Jerome,

If I have understood the requirement, you need pass the schema name,table name and key col name as a parameter in the delete statement.

You can store these 3 parameters in a file and pass it from the job sequence as a parameter one by one.

Please let me know if this is helpful.

[ArndW]

I would recommend using a Modify stage to create a new column (call it TempKeyCol) from the real column name as given in the parameter #KEYCOL#, then have your SQL do

Code: Select all

DELETE FROM #SCHEMA#.#TABLE# WHERE #KEYCOL#=ORCHESTRATE.TempKeyCol

[chulett]

Hmmm... wouldn't that preclude the use of RCP?

[ray.wurlod]

Not if you generate TempKeyCol in a Modify stage.

[ArndW]

I use this method all the time with RCP and generic jobs. Often I want to process tables whose key column have different names so I pass the key in as a parameter, use a modify stage to define a "known" key name, and can then process that constructed column through whatever manipulations and output database stages that I want. It works quite well and is sometimes the only way to perform some actions, as seen by the problems that the OP has had.

[jerome_rajan]

I see a lot of seemingly very useful solutions that have been tried and tested. But till I get my premium membership(which I've purchased and has not been activated yet ), I won't be able to see any of them.
Once I get around my membership issues with the admin, I'll report back

[jerome_rajan]

Still not working. It continues to say

Code: Select all

DB2_Delete_Update,0: Statement DELETE FROM <tablename> WHERE KEY_1=? failed to run.

Am I missing something here?

I peeked the output of the modify stage and everything is as expected except that KEY_1 is no longer present in the output. Instead TempKeyCol has taken its place. This, I believe, is how it should be working. But my job still aborts.

[ray.wurlod]

I have to ask: have you marked TempKeyCol as Key ? That's what gets DataStage to include in in the WHERE clause of generated SQL.

[jerome_rajan]

I have marked it as 'Key'. In any case, I'm writing a custom SQL.

[jerome_rajan]

Assuming that I've gone wrong implementing the above solution and that it does work, I have one more connected query. all my tables have keys that have different datatypes and different lengths. When I create the column in the MODIFY stage, do I not have to be specific about the datatype?

[ArndW]

To the last question - you do need to specify a datatype for the temporary column. I usually end up using a Varchar type with no specified length for this, as the conversions to/from string from most datatypes is implicit.

Does the error message contain the real table name or "<tablename>"?

[jerome_rajan]

It contains the real table name. I wasn't too sure if revealing the name of the table was right.

[ArndW]

Ray's comment made me think - if you have the key column defined, then you don't need custom SQL for this, just let DataStage do that for you.

[jerome_rajan]

Tried doing that too. The job aborts saying:

Code: Select all

Failed to generate where clause for delete statement. No key fields defined in the design schema

And yes, I've defined TempKeyCol as key.