Delete with no log in DB2

[thurmy34]

Hi,
I would like to perform a huge delete inside Datastage.
I have to handle the log of the database.
I know the db2 command and it's works with a call to a .cmd file.
I have some issue with the ' caractere who disappear between datastage and my cmd.(ex Date_1='01/01/2007' in datastage become Date_1= 01/01/2007 in my cmd and the sql failed).

[kumar_s]

Where are you calling this command? And what is the command about? What is the full log that shows you this info?

[thurmy34]

Kumar,
I call the delete.cmd in a Before routine.
The delete.cmd create a sql file and execute it like that
DB2CMD.EXE /w /c DB2.EXE -tvf FicSql -l FicLog
The sql file cotains

Code: Select all

UPDATE COMMAND OPTIONS USING C OFF;
CONNECT TO DB1 USER USR1 USING xxxx;
ALTER TABLE DBM02.IFCE_IFC_CD_ECRITURES_COMPTABLE ACTIVATE NOT LOGGED INITIALLY;
DELETE FROM DBM02.IFCE_IFC_CD_ECRITURES_COMPTABLE 
WHERE (month(D_TRAITEMENT)!=12 and day(D_TRAITEMENT)!=31)
 or (D_TRAITEMENT= 01/01/2007 ); <-- here i lost the '
COMMIT;
DISCONNECT CURRENT;
TERMINATE;

[I_Server_Whale]

ex Date_1='01/01/2007' in datastage

How about enclosing '01/01/2007' in single quotes like below:

Code: Select all

''01/01/2007''

. Four single quotes.

[thurmy34]

If i do that (''01/01/2007'') db2 looks for a column named 01/01/2007 and it's doesn't work.

Anyway, for some reasons the other guys in my team thought that i can't do that in the DB2 API.
I test it and it works.
In the before tab I have

Code: Select all

ALTER TABLE DBM02.IFCE_IFC_CD_ECRITURES_COMPTABLE ACTIVATE NOT LOGGED INITIALLY; 
DELETE FROM DBM02.IFCE_IFC_CD_ECRITURES_COMPTABLE 
WHERE (month(D_TRAITEMENT)!=12 and day(D_TRAITEMENT)!=31) 
 or (D_TRAITEMENT= #MyDate# )

So now it okay for me.
Thanks for Helping.

[DSguru2B]

And who in the world told you that it will not work in the before sql tab. Never 'assume'. Test it out yourself first before confirming any one else's notion. Anyways, thanks for the info, you can now mark your post as resolved.