DB2 400下降柱 [英] DB2 400 drop column
问题描述
我想删除一个称为 id 的列，这是一个自动递增的PK。
I want to drop a column called id which is an auto incrementing PK.
SQL：
alter table "CO88GT"."XGLCTL" drop column id cascade;
我得到：
Error: [SQL0952] Processing of the SQL statement ended. Reason code 10.
SQLState: 57014
ErrorCode: -952
我可能是错的，但我认为这与防止表丢失数据有关。要解决此问题，我需要创建一个没有该列的新表，并将数据从旧表复制到新表中，然后用新表替换旧表。
I could be wrong but I think it has something to do with preventing the table from losing data. To get around this issue I need to create a new table without the column and copy the data from the old table into the new table and then replace the old table with the new table.
推荐答案
信息
由于可能丢失数据，AS400正在向您发出警告（询问消息），请您取消或忽略请求的操作。因此，由于这是一个交互式请求，因此无法通过JDBC / ODBC键入 I来忽略，并且AS会向您抛出一个错误代码：-952，SQLState：57014，原因码10。
Info
AS400 is giving you a warning (inquiry message) because of possible data loss, asking you to Cancel or Ignore the requested operation. So, beacuse of this being a interactive request, over JDBC/ODBC you cannot type 'I' to ignore, and AS throws you an ErrorCode: -952 with SQLState: 57014 and Reason code 10.
在说：
In the documentation of SQL0952 says:
Message Text: Processing of the SQL statement ended. Reason code &1.
Cause Text: The SQL operation was ended before normal completion. The reason code is &1. Reason codes and their meanings are:
* 1 - An SQLCancel API request has been processed, for example from ODBC.
* 2 - SQL processing was ended by sending an exception.
* 3 - Abnormal termination.
* 4 - Activation group termination.
* 5 - Reclaim activation group or reclaim resources.
* 6 - Process termination.
* 7 - An EXIT function was called.
* 8 - Unhandled exception.
* 9 - A Long Jump was processed.
* 10 - A cancel reply to an inquiry message was received.
* 11 - Open Database File Exit Program (QIBM_QDB_OPEN).
* 0 - Unknown cause.
如果您使用的是JDBC并且SQL错误不是不言自明的，则可以制作一个JDBC与参数 errors = full 连接，它将提供有关该错误的更多信息。有关其他连接参数，请参见此。
If you are using JDBC and the SQL error isn't self-explanatory, you can make a JDBC connection with parameter 'errors=full', which will give much more info on the error. For other connection parameters see this.
示例连接字符串：
jdbc：as400：// serverName; libraries = * libl; naming = system; 错误=完全;
与该连接所导致的错误将是像这样：
With that connection the resulting error would be like this:
Error: [SQL0952] Processing of the SQL statement ended. Reason code 10.
Cause . . . . . : The SQL operation was ended before normal completion.
The reason code is 10.
Reason codes and their meanings are:
1 -- An SQLCancel API request has been processed, for example from ODBC.
2 -- SQL processing was ended by sending an exception.
3 -- Abnormal termination.
4 -- Activation group termination.
5 -- Reclaim activation group or reclaim resources.
6 -- Process termination.
7 -- An EXIT function was called.
8 -- Unhandled exception.
9 -- A Long Jump was processed.
10 -- A cancel reply to an inquiry message was received.
11 -- Open Database File Exit Program (QIBM_QDB_OPEN).
0 -- Unknown cause.
Recovery . . . : If the reason code is 1, a client request was made to cancel SQL processing. For all other reason codes, see previous messages to determine why SQL processing was ended.
SQLState: 57014
ErrorCode: -952
解决方案
最后，如果您不能使用STRSQL，另一种解决方案是使用iSeries Navigator，确切地说，它是运行SQL脚本（通常在这里->％Program Files％\IBM\Client Access\Shared\cwbundbs.exe）。
The solution
So finally, if you cannot use STRSQL, another solution is to use iSeries Navigator, to be exact its "Run SQL scripts" (it is usually here --> "%Program Files%\IBM\Client Access\Shared\cwbundbs.exe").
但首先您必须添加的所有系统回复参数（每台计算机仅一次）
But first of all you have to add a system reply parameter (only once per machine)
ADDRPYLE SEQNBR（1500） MSGID（CPA32B2）RPY（'I'）
ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY('I')
此操作在绿屏中完成。这会在CPA32B2查询消息中设置默认答案（'I'）。 CPA32B2是一个内部按摩ID，与下拉列操作绑定。
This is done in "green screen". This sets a deafult answer ('I') on CPA32B2 inquiry message. The CPA32B2 is an internal massage id, which is tied to an drop column operation.
（实际上不必在绿屏中完成操作，使用它例如：
(It actually doesn't have to be done in "green screen", use it like CHGJOB command. Example :
cl：ADDRPYLE SEQNBR（1500）MSGID（CPA32B2）RPY（'I'）;
cl: ADDRPYLE SEQNBR(1500) MSGID(CPA32B2) RPY('I');
）
现在您可以启动运行SQL脚本，这是第一个要运行的命令是：
Now you can start "Run SQL scripts", the first command to run is:
cl：CHGJOB INQMSGRPY（* SYSRPYL）;
cl: CHGJOB INQMSGRPY(*SYSRPYL);
这将更改当前作业参数 INQMSGRPY ，改为* SYSRPYL。 *当显示查询消息时，SYSRPYL会查看是否存在系统回复参数。
this changes the current job parameter INQMSGRPY, to *SYSRPYL. *SYSRPYL causes to look if exists a system reply parameter when an inquiry message should be displayed.
现在，您可以运行您的alter来删除该列。
Now you can run your alter which drops the column.
不幸的是，我不知道如何仅使用JDBC删除列。如果有人知道，请让我知道。
Unfortunately, I don't know how to drop a column, just using JDBC. If someone knows please let me know.
参考文献：
- Understanding What Controls the Automatic Reply Function
- Replying to Run-Time Inquiry Messages
- Error in dropping column (forum post)
- How can I avoid SQL0952 on ALTER TABLE?
这篇关于DB2 400下降柱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
