Start a new topic

Error Using Truncate Table Command in DB2 v9.5

[This topic is migrated from our old forums. The original author name has been removed]

When attempting to use the TRUNCATE option on the "Empty Table" right-click menu option on a DB2 v9.5 LUW database, the following error message appears: An error occurred while executing the database request for: DB2/AIX64 SQL09055 IBM DB2 JDBC Universal Driver Architecture 2.5.36 Short message: An error occurred while performing the operation: An unexpected token ""DB2REPT"" was found following "truncate table ". Expected tokens may include: "JOIN". The command that caused the problem: truncate table "DB2REPT"."TEST_TIMESTAMP" Long Message: An unexpected token ""DB2REPT"" was found following "truncate table ". Expected tokens may include: "JOIN". Details:    Type: com.ibm.db2.jcc.a.SqlException    Error Code: -104    SQL State: 42601 System Information: Product: DbVisualizer Personal 7.1.1 Build: #1578 (2010/06/23 11:20) Java VM: Java HotSpot(TM) Client VM Java Version: 1.6.0_18 Java Vendor: Sun Microsystems Inc. OS Name: Windows XP OS Arch: x86 OS Version: 5.1 Using the Delete option instead works of course, but I know that Truncate has less overhead and would rather use it. Does anyone know why it won't work?

[This reply is migrated from our old forums.]

Re: Error Using Truncate Table Command in DB2 v9.5
Hi, This is now fixed in the DbVisualizer 7.1.2 version. Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Error Using Truncate Table Command in DB2 v9.5
Thank you, Roger. At least I can quit banging my head!
[This reply is migrated from our old forums.]

Re: Error Using Truncate Table Command in DB2 v9.5
Jeff, Sorry I didn't read the DB2 release docs correctly. Truncate was in introduced in DB2 LUW beginning from 9.7. The "Empty Table" with Truncate being select have never worked with DB2 until 9.7 was released. I suspect it is a copy/paste from the DB2 z/OS profile. Sorry for the inconvenience. This is fixed in the next maintenance version . The "Truncate" option will show only if 9.7 or later. Best Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: Error Using Truncate Table Command in DB2 v9.5
Hi Roger, This is version 9.5, but I tried both statements below and it still doesn't work. Is something different required for 9.5? I've searched IBM's documentation, but the TRUNCATE command shows only the scalar number truncation, nothing for table truncation. truncate table DB2REPT.TEST_TIMESTAMP immediate truncate table immediate DB2REPT.TEST_TIMESTAMP
[This reply is migrated from our old forums.]

Re: Error Using Truncate Table Command in DB2 v9.5
Hi Jeff, Thanks for your post. The "truncate" command starting in DB2 9.7 require the "immediate" keyword last in the command. We will fix this in the next maintenance version planned for release within a week. A workaround in the "Empty Table" action is that you use the "Copy to SQL Editor" button and then in the SQL Editor add "immediate" last in the command. Then execute it manually in the SQL Commander. Thanks again for reporting this. Best Regards Roger