DS Routine to delete data

[kaushik.sr]

Hi all,

We are using one DS routine to DELETE the data from a table (DB2) befor e load. The routine, if run individually, works fine. But when integrated in a sequencer, doesn't work.
We have String comparison in the WHERE clause of the DELETE SQL.

If we remove WHERE clause the routine works fine even in the sequencer. Is there any other way to get thru this?

Stage used to load : ODBC stage

Your valuable suggestions are welcome.

Thanks
Kaushik

[swapnilverma]

what is the error message you are getting ?

[Katie09]

If we remove WHERE clause the routine works fine even in the sequencer. Is there any other way to get thru this?

Stage used to load : ODBC stage

If you are removing the where clause, it is working fine..it means there is issue with the value which you are passing..debug and find out what value is passed..as it is string check for the spaces or any special characters are amended to the original string which may be cause for aborting the sequence..

hope this helps...

[chulett]

So, your "DS routine" is actually a Server job, yes?

[ray.wurlod]

The clause "doesn't work" conveys no useful information. What do you mean? Please share the routine code (encased in Code tags), that we might assess why the problem you are getting is occurring.

[kaushik.sr]

The following is routine that had given as input:

Cmd='. ~/.profile;db2 "connect to ':DatabaseName:' user ':UserId:' using ':Password:'"; db2 "delete from ':SchemaNameDM:'.':TableName:' where CALENDAR_MONTH_KEY IN (SELECT CALENDAR_MONTH_KEY FROM ':SchemaNameDM:'.D_CALENDAR_MONTH WHERE CAL_YR_NBR = ':RUNYEAR:' AND INTEGER(RIGHT(TRIM(CHAR(CALENDAR_MONTH_KEY)),2))=':RUNMONTH:')AND MGMT_ENTITY_KEY IN (SELECT MGMT_ENTITY_KEY FROM ':SchemaNameDM:'.D_MGMT_ENTITY WHERE LEGAL_ENTITY_GRP_CODE=':LEGforDELETE:') AND SCENARIO_KEY IN (SELECT SCENARIO_KEY FROM ':SchemaNameDM:'.D_SCENARIO WHERE SCENARIO_CODE=':SCENARIOforDELETE:' )"'

And we dont get any error msg. Just that the Where condition is not working.

Thanks
Kaushik

[ray.wurlod]

Try building the Cmd variable in a more piecemeal fashion, so that it's easier to read and therefore easier to maintain.
Also please show us the rest, particularly how you are executing the command.