Truncate in DB2

[kris007]

Hi All,

I am trying to truncate a table in DB2 using the following LOAD REPLACE statement from the Open Command option

Code: Select all

load from /dev/null of del replace into ownername.tablename NONRECOVERABLE

but the statement doesn't execute successfully giving an error

Code: Select all

update_dim: ERROR during execution of DB2-CLI requests:  SQL_ERROR;  SQLSTATE = 42601;  NativeErrorCode = -104;  Message = [IBM][CLI Driver][DB2/LINUX] SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "_dim NONRECOVERABLE"".  Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601. 

Failed to prepare statement:  load from /dev/null of del replace into ownername.tablename NONRECOVERABLE

It would be great if anyone could point me in the right direction on how to truncate a table in DB2

Thanks

[DSguru2B]

Well, your load replace syntax seems to be correct but db2 is reading something more than that. Thats why its giving you SQL0104N which just means its a syntax error. Even the SQL0104N help is not very helpful as it just says, hey, your syntax is messed up, fix it. Close that window, open a new one and try the command again. Even try going in from command line and try it there. Also make sure you are running this command for UDB and not DB2. If its DB2, all the tables in that table space will be wiped out. But for UDB it will only truncate the table that you specify.

[kris007]

Well, your load replace syntax seems to be correct but db2 is reading something more than that. Thats why its giving you SQL0104N which just means its a syntax error. Even the SQL0104N help is not very helpful as it just says, hey, your syntax is messed up, fix it. Close that window, open a new one and try the command again. Even try going in from command line and try it there. Also make sure you are running this command for UDB and not DB2. If its DB2, all the tables in that table space will be wiped out. But for UDB it will only truncate the table that you specify.

Yeah DSGuru it is syntax error, I wanted to know if there is any special syntax to issue command line commands.It works well when I try it in command line.

[ghila]

Hello,

You can also use the following SQL in order to do a truncate in DB2 :
ALTER TABLE <tablename> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

Hope this will help.

[ArndW]

I know it might sound silly, but what if you explicitly add a semicolon to your statement?

[kris007]

You can also use the following SQL in order to do a truncate in DB2 :
ALTER TABLE <tablename> ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

ghila, Thanks for the response when I tried to execute the above query I got sql code =SQL1596N

SQL1596N WITH EMPTY TABLE cannot be specified for <table-name>SQLSTATE=42928

I did a search on sql code =SQL1596N I got some link saying its not allowed for REFRESH IMMEDIATE table. I donot know what that means.

I know it might sound silly, but what if you explicitly add a semicolon to your statement?

ArndW- Thanks for the suggestion, I have already tried that, it doesnt matter I still get the same error.I even tried removing quotes and all other various combination. Is there anything like PL/SQL equivalent syntax for DB2 .

[DSguru2B]

Did you try it without the keyword 'NONRECOVERABLE' ???

[cmg]

did you connect to DB2?

if so use
db2 "load from /dev/null of del replace into schama.tablename nonrecoverable"

[kris007]

Did you try it without the keyword 'NONRECOVERABLE' ???

Yes I did but in vain.

[ghila]

Kris,

Seems your table is either an MQT or part of an MQT, or there are constraints references. So this do not allow you to use WITH EMPTY TABLE clause.

[thurmy34]

Hi,

I use this syntaxe:

Code: Select all

ECHO CONNECT TO database USER user using pwd >file.SQL
ECHO import from nul: of del replace into schema.table; >>file.SQL
ECHO DISCONNECT CURRENT; >>file.SQL
ECHO TERMINATE; >>file.SQL

DB2CMD.EXE /w /c DB2.EXE -tvf file.SQL -l file.LOG

and it's work fine.

I call this from a before.after routine.

Hope this help.

Sorry guys i didn't see it was on unix.
Shoud i delete my post ?

[kris007]

Dont worry about it, . I was thinking of a way to invoke it from database stage rather writing script and calling it.
Thanks.