Page 1 of 1
Delete with no log in DB2
Posted: Mon Jan 15, 2007 2:23 am
by 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).
Posted: Mon Jan 15, 2007 2:26 am
by kumar_s
Where are you calling this command? And what is the command about? What is the full log that shows you this info?
Posted: Mon Jan 15, 2007 2:57 am
by 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;
Re: Delete with no log in DB2
Posted: Mon Jan 15, 2007 4:42 am
by I_Server_Whale
thurmy34 wrote:ex Date_1='01/01/2007' in datastage
How about enclosing '01/01/2007' in single quotes like below:
. Four single quotes.
Posted: Mon Jan 15, 2007 6:45 am
by 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.
Posted: Mon Jan 15, 2007 11:51 am
by 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.