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.
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
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.
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
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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.
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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?
Jerome
Data Integration Consultant at AWS
Connect With Me On LinkedIn
Life is really simple, but we insist on making it complicated.
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>"?