I am not very familiar with iseries/DB2. However, I work on a website that uses it as its primary database.
我对iseries / DB2不太熟悉。但是,我在一个使用它作为主数据库的网站上工作。
A new column was recently added to an existing table. When I view it via AS400, I see the following data type:
最近在现有表中添加了一个新列。当我通过AS400查看它时,我看到以下数据类型:
Type: S
Length: 9
Dec: 2
This tells me it's a numeric field with 6 digits before the decimal point, and 2 digits after the decimal point.
这告诉我这是一个数字字段,小数点前有6位数字,小数点后2位数字。
When I query the data with a simple SELECT (SELECT MYCOL FROM MYTABLE), I get back all the records without a problem. However, when I try using a DISTINCT, GROUP BY, or ORDER BY on that same column I get the following exception:
当我使用简单的SELECT(SELECT MYCOL FROM MYTABLE)查询数据时,我会毫无问题地收回所有记录。但是,当我尝试在同一列上使用DISTINCT,GROUP BY或ORDER BY时,我得到以下异常:
[SQL0802] Data conversion of data mapping error
I've deduced that at least one record has invalid data - what my DBA calls "blanks" or "4 O". How is this possible though? Shouldn't the database throw an exception when invalid data is attempted to be added to that column?
我推断至少有一条记录有无效数据 - 我的DBA称之为“空白”或“4 O”。这怎么可能呢?当尝试将无效数据添加到该列时,数据库是否应该抛出异常?
Is there any way I can get around this, such as filtering out those bad records in my query?
有什么方法可以解决这个问题,例如在我的查询中过滤掉那些不良记录吗?
4 个解决方案
#1
4
"4 O" means 0x40 which is the EBCDIC code for a space or blank character and is the default value placed into any new space in a record.
“4 O”表示0x40,它是空格或空白字符的EBCDIC代码,是放入记录中任何新空间的默认值。
Legacy programs / operations can introduce the decimal data error. For example if the new file was created and filled using the CPYF command with the FMTOPT(*NOCHK) option.
传统程序/操作可能会引入十进制数据错误。例如,如果使用带有FMTOPT(* NOCHK)选项的CPYF命令创建并填充新文件。
The easiest way to fix it is to write an HLL program (RPG) to read the file and correct the records.
解决它的最简单方法是编写HLL程序(RPG)来读取文件并更正记录。
#2
2
If the file has record format level checking turned off [ie. LVLCHK(*NO)] or is overridden to that, then an HLL program. (ex. RPG, COBOL, etc) that was not recompiled with the new record might write out records with invalid data in this column, especially if the new column is not at the end of the record.
如果文件已关闭记录格式级别检查[即。 LVLCHK(* NO)]或被覆盖,然后是HLL程序。未使用新记录重新编译的(例如RPG,COBOL等)可能会在此列中写出包含无效数据的记录,尤其是在新列不在记录末尾的情况下。
Make sure that all programs that use native I/O to write or update records on this file are recompiled.
确保重新编译使用本机I / O写入或更新此文件上的记录的所有程序。
#3
2
The only solution I could find was to write a script that checks for blank values in the column and then updates them to zero when they are found.
我能找到的唯一解决方案是编写一个脚本来检查列中的空白值,然后在找到它们时将它们更新为零。
#4
0
I was able to solve this error by force-casting the key columns to integer. I changed the join from this...
我能够通过强制将键列强制转换为整数来解决此错误。我改变了这个联接......
FROM DAILYV INNER JOIN BXV ON DAILYV.DAITEM=BXV.BXPACK
...to this...
FROM DAILYV INNER JOIN BXV ON CAST(DAILYV.DAITEM AS INT)=CAST(BXV.BXPACK AS INT)
...and I didn't have to make any corrections to the tables. This is a very old, very messy database with lots of junk in it. I've made many corrections, but it's a work in progress.
......而且我没有必要对表格进行任何更正。这是一个非常古老,非常混乱的数据库,里面有很多垃圾。我做了很多修正,但这是一项正在进行中的工作。
#1
4
"4 O" means 0x40 which is the EBCDIC code for a space or blank character and is the default value placed into any new space in a record.
“4 O”表示0x40,它是空格或空白字符的EBCDIC代码,是放入记录中任何新空间的默认值。
Legacy programs / operations can introduce the decimal data error. For example if the new file was created and filled using the CPYF command with the FMTOPT(*NOCHK) option.
传统程序/操作可能会引入十进制数据错误。例如,如果使用带有FMTOPT(* NOCHK)选项的CPYF命令创建并填充新文件。
The easiest way to fix it is to write an HLL program (RPG) to read the file and correct the records.
解决它的最简单方法是编写HLL程序(RPG)来读取文件并更正记录。
#2
2
If the file has record format level checking turned off [ie. LVLCHK(*NO)] or is overridden to that, then an HLL program. (ex. RPG, COBOL, etc) that was not recompiled with the new record might write out records with invalid data in this column, especially if the new column is not at the end of the record.
如果文件已关闭记录格式级别检查[即。 LVLCHK(* NO)]或被覆盖,然后是HLL程序。未使用新记录重新编译的(例如RPG,COBOL等)可能会在此列中写出包含无效数据的记录,尤其是在新列不在记录末尾的情况下。
Make sure that all programs that use native I/O to write or update records on this file are recompiled.
确保重新编译使用本机I / O写入或更新此文件上的记录的所有程序。
#3
2
The only solution I could find was to write a script that checks for blank values in the column and then updates them to zero when they are found.
我能找到的唯一解决方案是编写一个脚本来检查列中的空白值,然后在找到它们时将它们更新为零。
#4
0
I was able to solve this error by force-casting the key columns to integer. I changed the join from this...
我能够通过强制将键列强制转换为整数来解决此错误。我改变了这个联接......
FROM DAILYV INNER JOIN BXV ON DAILYV.DAITEM=BXV.BXPACK
...to this...
FROM DAILYV INNER JOIN BXV ON CAST(DAILYV.DAITEM AS INT)=CAST(BXV.BXPACK AS INT)
...and I didn't have to make any corrections to the tables. This is a very old, very messy database with lots of junk in it. I've made many corrections, but it's a work in progress.
......而且我没有必要对表格进行任何更正。这是一个非常古老,非常混乱的数据库,里面有很多垃圾。我做了很多修正,但这是一项正在进行中的工作。