truncate command in ODBC

samsuf2002 · Post by **samsuf2002** » Thu Dec 21, 2006 10:16 am

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

DSguru2B · Post by **DSguru2B** » Thu Dec 21, 2006 10:18 am

You sure you have truncate permissions ?

narasimha · Post by **narasimha** » Thu Dec 21, 2006 10:21 am

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

samsuf2002 · Post by **samsuf2002** » Thu Dec 21, 2006 11:08 am

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?

us1aslam1us · Post by **us1aslam1us** » Thu Dec 21, 2006 11:17 am

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

DSguru2B · Post by **DSguru2B** » Thu Dec 21, 2006 11:22 am

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.

Ultramundane · Post by **Ultramundane** » Thu Dec 21, 2006 1:04 pm

Does DS put the open command in a transaction.

samsuf2002 · Post by **samsuf2002** » Thu Dec 21, 2006 1:29 pm

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

ray.wurlod · Post by **ray.wurlod** » Thu Dec 21, 2006 2:36 pm

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

samsuf2002 · Post by **samsuf2002** » Thu Dec 21, 2006 3:23 pm

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

DSguru2B · Post by **DSguru2B** » Thu Dec 21, 2006 9:42 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Dec 22, 2006 12:38 am

Because DELETE is DML and TRUNCATE TABLE is not.

DSguru2B · Post by **DSguru2B** » Fri Dec 22, 2006 7:17 am

Makes sense. Thanks for the clarification Ray.

samsuf2002 · Post by **samsuf2002** » Wed Dec 27, 2006 1:45 pm

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

narasimha · Post by **narasimha** » Wed Dec 27, 2006 1:51 pm

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