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).
Delete with no log in DB2
Moderators: chulett, rschirm, roy
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
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;
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: Delete with no log in DB2
How about enclosing '01/01/2007' in single quotes like below:thurmy34 wrote:ex Date_1='01/01/2007' in datastage
Code: Select all
''01/01/2007''
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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
So now it okay for me.
Thanks for Helping.
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# )
Thanks for Helping.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.