CODEWITHSUNDEEP
sqldatabasedb2truncatedb2-luw
tony
tony

Reputation: 277

How to Truncate table in DB2? Error: Not a valid Command Line Processor command

Running following statement in DB2 CLP (Command Window)

db2 "truncate table MYSCHEMA.TABLEA immediate"

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0969N  There is no message text corresponding to SQL error "-20356" in the
message file on this workstation.  The error was returned from module
"SQLNQBE2" with original tokens "MYSCHEMA.TABLEA".

Can some please tell me what I'm doing wrong or what I'm missing? I'm trying to simply truncate from a single table and I'm getting the following error message. Not sure what I'm doing wrong. I've tried it with/without quotes, with/without schema, with/without immediate. I've also tried in Command Editor (remove db2 and quotes) and still not working. I'm using: DB2/AIX64 9.7.9 Also, I have delete privilege as I am able to delete records but I want to truncate.

Thanks in advance!

Upvotes: 0

Views: 8587

Answers (3)

mustaccio
mustaccio

Reputation: 18945

The version of the DB2 client you're using doesn't seem to match that of the server, this is why you cannot see the actual error message for SQLCODE -20356. If you could, you'd see this:

The table MYSCHEMA.TABLEA cannot be truncated because DELETE triggers exist for the table, or the table is the parent in a referential constraint.

Further explanation and suggested actions can be found, as usual, in the fine manual.

Upvotes: 1

Seb.B.
Seb.B.

Reputation: 151

I had this problem recently too. In my case I had to do a COMMIT WORK right before TRUNCATE. This solved my problem. Please try and tell us if this helped.

Upvotes: 0

esky2000
esky2000

Reputation: 1

ALTER TABLE MYSCHEMA.TABLEA ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

or

import from /dev/null of del replace into MYSCHEMA.TABLEA

Upvotes: 0

Related Questions