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
DS Routine to delete data
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
Re: DS Routine to delete data
what is the error message you are getting ?
Thanks
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
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...
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...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
-
- Participant
- Posts: 2
- Joined: Fri Nov 02, 2007 1:49 pm
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Also please show us the rest, particularly how you are executing the command.
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.