SQL查询以截断IBM DB2中的表 [英] SQL Query to truncate table in IBM DB2

问题描述

任何人都可以给我截断IBM DB2中的表的语法。

我运行以下命令： truncate table tableName immediate ;

错误是DB2

SQLCODE = -104，SQLSTATE = 42601，SQLERRMC = table; truncate; JOIN，DRIVER = 3.50.152
消息：在truncate之后发现意外的令牌table。预期令牌可能包括：JOIN.. SQLCODE = -104，SQLSTATE = 42601，DRIVER = 3.50.152

语法匹配在IBM的参考文档中指定的一个： http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_truncate.htm

解决方案

有一篇关于截断，这里是DB2的Gist的东西

几乎遵循标准（自9.7以来）
DB2要求将IMMEDIATE关键字添加到普通的TRUNCATE TABLE语句中，例如：

  TRUNCATE表someschema.sometable IMMEDIATE 
  

TRUNCATE TABLE必须是第一个状态在交易中。以TRUNCATE TABLE开头的事务可能包括其他语句，但是如果事务被回滚，则TRUNCATE TABLE操作不会被撤销。
DB2 TRUNCATE TABLE操作有多个可选参数，有关更多信息，请参阅文档;特别是REUSE STORAGE参数对于特殊的DBA任务可能很重要。
在DB2版本< 9.7，您可能会滥用IMPORT声明。不幸的是，您需要知道执行该命令的操作系统，以使其正常工作：

在类Unix系统上：
IMPORT FROM / dev / null OF DEL REPLACE INTO tablename
在Windows上：
从NUR OF DEL REPLACE IN进入到tablename
IMPORT不能在所有上下文中被滥用。例如，当使用动态SQL（来自Java / .NET / PHP /...-不使用db2命令行处理器）时，需要将IMPORT命令包装在对ADMIN_CMD的调用中，例如：

  CALL ADMIN_CMD（'IMPORT FROM / dev / null OF DEL REPLACE INTO tablename'）
  

IMPORT似乎在涉及其他操作的交易中被允许，但这意味着立即进行COMMIT操作。

ALTER TABLE命令也可能被滥用以快速清空表，但是它需要更多的权限，并且可能会导致前端恢复的麻烦。

这是从网站取得的：
http://troels.arvin.dk/db/rdbms/# bulk-truncate_table-db2

Can any one give me the syntax to truncate a table in IBM DB2.

I m running the following command: truncate table tableName immediate;

The eror is DB2

SQLCODE=-104, SQLSTATE=42601, SQLERRMC=table;truncate ;JOIN , DRIVER=3.50.152 Message: An unexpected token "table" was found following "truncate ". Expected tokens may include: "JOIN ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.50.152

The syntax matches the one specified in the reference docs of IBM : http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_sql_truncate.htm

解决方案

There is a great article about truncating, here is the Gist of DB2 stuff

Almost follows the standard.(since version 9.7) DB2 requires that the IMMEDIATE keyword be added the the ordinary TRUNCATE TABLE statement, e.g.:

TRUNCATE TABLE someschema.sometable IMMEDIATE 

TRUNCATE TABLE must be the first statement in a transaction. A transaction starting with TRUNCATE TABLE may include other statements, but if the transaction is rolled back, the TRUNCATE TABLE operation is not undone. DB2s TRUNCATE TABLE operation has a number of optional arguments, see the documentation for more on this; especially, the REUSE STORAGE argument may be important for ad-hoc DBA tasks. In DB2 versions < 9.7, you may abuse the IMPORT statement. Unfortunately, you need to know which operating system the command is executed from for this to work:

On unix-like systems: IMPORT FROM /dev/null OF DEL REPLACE INTO tablename On Windows: IMPORT FROM NUL OF DEL REPLACE INTO tablename IMPORT cannot be abused in all contexts. E.g., when working with dynamic SQL (from Java/.NET/PHP/...—not using the db2 command line processor), you need to wrap the IMPORT command in a call to ADMIN_CMD, e.g.:

CALL ADMIN_CMD('IMPORT FROM /dev/null OF DEL REPLACE INTO tablename')

IMPORT seems to be allowed in a transaction involving other operations, however it implies an immediate COMMIT operation.

The ALTER TABLE command may also be abused to quickly empty a table, but it requires more privileges, and may cause trouble with rollforward recovery.

This was taken from the website: http://troels.arvin.dk/db/rdbms/#bulk-truncate_table-db2

这篇关于SQL查询以截断IBM DB2中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！