来自定界文件的DB2加载 - 转义“在字段不起作用 [英] DB2 Load from delimitited Files - escape " in Fields doesn't work
问题描述
我在DB2命令行中执行以下命令处理器：
DB2 LOAD FROMDB_ACC_PASS_REGEXP.delOF DEL METHOD P（1，2，3，4，5）MESSAGESDB_ACC_PASS_REGEXP .del.msgINSERT INTO DB_ACC_PASS_REGEXP（APP_ID，APREGEXP，EXPLAIN_TEXT，ID，OPT_KZ）NONRECOVERABLE INDEXING MODE REBUILD
哪些加载数据在以下文件中指定到数据库中。
1，[az],, 1,0
1，[AZ],, 2,0
1，[0-9],, 3,0
1，[！| \|§| $ |％|& | / |（|）| = |？|`|'| * | + | 〜|'|＃| - | _ |。|：|，|; |μ|< |> | |°| ^],, 4,0
^
这是问题
问题是，只有3这四个插入将被接受，最后一个将被拒绝，因为DB2 Load在双引号之前没有注意到转义字符。
如果我更改最后一个一行到：
1，[！| x |§| $ |％|& | / |（|）| = | ？|`|'| * | + |〜|'|＃| - | _ |。|：|，|; |μ| |°| ^],, 4,0
^
这是更改的字符
没有问题..
为什么转义字符\不起作用？
编辑
好吧，我刚刚尝试了这个oracle的方式，这个工作...我逃避了另一个，所以我的行看起来像
1，[！||§| $ |％|& | / |（|）| = |？|`|'| * | + |〜|'|＃| - | _ |。|：|，|; |μ| <|> | |°| ^],, 4,0
但这只是一种方法..这不解释为什么IBM提供反斜杠作为转义字符（ http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin。 cmd.doc％2Fdoc％2Fr0008305.html ）
使用LOAD与ascii / delimited文件需要调整文件类型修饰符（在表6和表8中链接的文档页面）我不太清楚，但我不记得在DB2中使用反斜杠作为转义字符。
您可以使用另一个字符分隔符作为双引号与 chardel 选项，或强制不使用 nochardel 选项的字符分隔符。
但是...
在你的情况下，你需要特殊字符作为常规表达所以你永远需要逃脱与和与。我认为没有其他方法可以使这项工作。
I bet it's totaly simple and i just don't see it, but i don't get it ..
I execute the following command in DB2 command line processor:
DB2 LOAD FROM "DB_ACC_PASS_REGEXP.del" OF DEL METHOD P (1, 2, 3, 4, 5) MESSAGES "DB_ACC_PASS_REGEXP.del.msg" INSERT INTO DB_ACC_PASS_REGEXP (APP_ID,APREGEXP,EXPLAIN_TEXT,ID,OPT_KZ) NONRECOVERABLE INDEXING MODE REBUILD
Which loads the Data specified in following File into the database.
1,"[a-z]",,1,0
1,"[A-Z]",,2,0
1,"[0-9]",,3,0
1,"[!|\"|§|$|%|&|/|(|)|=|?|`|´|*|+|~|'|#|-|_|.|:|,|;|µ|<|>| |°|^]",,4,0
^Here is the Problem
The Problem is, that only 3 of these 4 inserts will be accepted. The last one will be rejected, because DB2 Load doesn't notice the escape character before the double quotation mark.
if I change the last line to:
1,"[!|x|§|$|%|&|/|(|)|=|?|`|´|*|+|~|'|#|-|_|.|:|,|;|µ|<|>| |°|^]",,4,0
^Here is the changed character
there is no problem ..
WHY doesn't the escape character "\" work??
edit Okay.. I just tryed it the oracle way now and that works ... I escape " with another " so my Line looks like
1,"[!|""|§|$|%|&|/|(|)|=|?|`|´|*|+|~|'|#|-|_|.|:|,|;|µ|<|>| |°|^]",,4,0
But that's only a way to do it .. That doesn't explain why IBM offers the Backslash as an escape character (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0008305.html)
Using LOAD with ascii / delimited files requires to tune the file type modifiers (look on Table 6 and Table 8 of the docu page you linked). I am not quite sure, but I can't remember using backslash as escape character in DB2.
You can either use another character delimiter as double quotes with chardel option or force no character delimiter with nochardel option.
BUT ...
In your case you need special characters as regular expressions, so you will always need to escape " with "" and ' with ''. I think there is no other way to get this working.
这篇关于来自定界文件的DB2加载 - 转义“在字段不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
