DSXchange

Hi All , i am running a parallel job on LINUX my database is SQL server and target stage i am using is ODBC .I need to truncate the table before every insert so i used OPEN COMMAND as TRUNCATE TABLE tablename it is giving me error as invalid command But when i used DELETE command it worked fine . But i would preffer to go with truncate as the size of the data will be big.

Any Suggestions

Thanks in Advance

You sure you have truncate permissions ?

Can you post the "exact" error message you get.
Also any surrounding error messages if any.

Do I need to have permission for that , because i can truncate the table by going into the data base . What kind of permissions do i need?

I guess you have the permission to truncate the table but still it will be better to confirm with your DBA. re you trying to clear the table and insert or just want to truncate the table and where are you using this command? Also paste the error message here.

Sam

If you can do it from outside datastage then you do have Truncate permissions. Thats not it. Also make sure your using the same id to access the database within datastage.
You said that DELETE FROM TABLE works, you meant the delete command works in the OPEN command option? Please confirm.

Does DS put the open command in a transaction.

i am trying to truncate the table and insert for every new load. i dont have the error message now since i have ran the job with DELETE command and it ran fine . when i read the message it was showing as "invalid command for TRUNCATE TABLE tablename"

Dsguru , DELETE command worked for open command

Perhaps it needs a trailing semi-colon in the OPEN command?

Thanks a lot Ray its working now i used trailing semi-colon in open command
.

So how come the DELETE command was working without a semi-colon

Because DELETE is DML and TRUNCATE TABLE is not.

Makes sense. Thanks for the clarification Ray.

Hi , On the day when we discussed this topic my problem was solved at that time i ran the job with sample file like 1000 recs now i am running the job with 8 million recs , i am surprised to see the same error stating the query in open command " TRUNCATE TABLE table name ;" as invalid command . Can any one help me out here .

Thanks in Advance

Size should not matter, in this situtaion. Check with your DBA's.