SQL DB2 需要有关更改表更改列集数据类型的帮助
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7905160/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 need help for alter table alter column set data type
提问by Manoj
I need to change the data type of column from VARCHARto DECIMAL
我需要将列的数据类型从 更改VARCHAR为DECIMAL
I have data in the column, but they are all numeric values like 10.00, 25.50etc,
我在列中有数据，但它们都是数值，例如10.00, 25.50等，
Now when I do alter table MY_TABLE alter column COL1 set data type to decimal(11,2)its failing.
现在当我做alter table MY_TABLE alter column COL1 set data type to decimal(11,2)它的失败。
Whats the process i can follow here.
我可以在这里遵循什么过程。
I'm sure this is not a new question but i couldn't find the solution, so instead of raking my brains i'm asking out here.
我确定这不是一个新问题，但我找不到解决方案，所以我没有绞尽脑汁，而是在这里问。
采纳答案by apokryfos
The syntax is
ALTER TABLE {tableName} MODIFY {Column-Name} {New-Column-Definition}I.E. ALTER TABLE MY_TABLE MODIFY COL1 DECIMAL(11,2)
语法是
ALTER TABLE {tableName} MODIFY {Column-Name} {New-Column-Definition}IEALTER TABLE MY_TABLE MODIFY COL1 DECIMAL(11,2)
Generally this (altering the column data type) won't convert the existing data to the new type and will probably set the incompatible values as NULL (or it may make a smart conversion it's pretty much DBMS server specific)
通常，这（更改列数据类型）不会将现有数据转换为新类型，并且可能会将不兼容的值设置为 NULL（或者它可能会进行智能转换，它几乎是特定于 DBMS 服务器的）
You can do something like this if the data volume is not too high
如果数据量不是太大，你可以这样做
ALTER TABLE MY_TABLE ADD COLUMN Temporary decimal(11,2);
UPDATE MY_TABLE SET Temporary=CAST(COL1 AS DECIMAL(11,2));
ALTER TABLE MY_TABLE DROP COLUMN COL1;
ALTER TABLE MY_TABLE CHANGE Temporary COL1 decimal(11,2);
Edit: The first part is probably incorrect
编辑：第一部分可能不正确
回答by zawhtut
Try dropping the system tables under "SYSTOOLS" schema. Then try changing it again.
尝试删除“SYSTOOLS”模式下的系统表。然后再次尝试更改它。
回答by Abdelrahman Aly
I tried @apokryfos solution, it works fine! It's not complete though, some SQL commands fails with error SQLCODE: -668, SQLSTATE: 57016
我试过@apokryfos 解决方案，它工作正常！虽然它不完整，但某些 SQL 命令失败并显示错误 SQLCODE: -668, SQLSTATE: 57016
Example:
例子：
SELECT count(COLUMN)
FROM SCHEMA.TABLE_NAME
WHERE id = ID_NUMBER
FETCH FIRST 1 ROWS ONLY
SELECT count(COLUMN)
FROM SCHEMA.TABLE_NAME
WHERE id = ID_NUMBER
FETCH FIRST 1 ROWS ONLY
This will fail!
这将失败！
To solve this, you need to REORG the table. Open the DB2 Shell, and type the following commands:
要解决此问题，您需要对表进行重组。打开 DB2 Shell，并输入以下命令：
db2 connect to <your database name here> db2 REORG TABLE SCHEMA.TABLE_NAME
db2 connect to <your database name here> db2 REORG TABLE SCHEMA.TABLE_NAME
There is also an SQL command to REORG, I didn't try it though:
还有一个用于 REORG 的 SQL 命令，但我没有尝试：
CALL SYSPROC.ADMIN_CMD('REORG TABLE SCHEMA.TABLENAME');
CALL SYSPROC.ADMIN_CMD('REORG TABLE SCHEMA.TABLENAME');
Reference:Failing update table in db2 with SQLCODE: -668, SQLSTATE: 57016, SQLERRMC: 7;
回答by billinkc
I'm assuming DB2 won't allow you to provide a guide for the data conversion but I pray to the SQL Server gods.
我假设 DB2 不允许您提供数据转换指南，但我向 SQL Server 大神祈祷。
Given that assumption, add a new column, migrate the data over, verify your work, drop the original column, rename the new.
鉴于这个假设，添加一个新列，迁移数据，验证您的工作，删除原始列，重命名新列。
ALTER TABLE MY_TABLE ADD col1_new decimal(11,2) NULLUPDATE MY_TABLE SET col1_new = CAST(col1 AS decimal(11,2)Check counts of distinct values, nulls etc between
col1andcol1_newbearing in mind10.00,10.0and10would be different values invarcharand one value indecimal. It does not sound like that is an issue in your data but something to look for if the counts differALTER TABLE MY_TABLE DROP COLUMN col1I have no idea what the db2 equivalent of
sp_renameis
ALTER TABLE MY_TABLE ADD col1_new decimal(11,2) NULLUPDATE MY_TABLE SET col1_new = CAST(col1 AS decimal(11,2)检查之间的不同值、空值等的计数
col1并col1_new记住10.00，10.0并且10将是 中的不同值varchar和 中的一个值decimal。听起来这不是您的数据中的问题，而是如果计数不同则需要查找的内容ALTER TABLE MY_TABLE DROP COLUMN col1我不知道 db2 的等价物
sp_rename是什么
