我试图创建/选择一个具有FIXED格式的Db2外部表,但没有成功。
以下是我尝试过的:
来自IBM pdf(https://www.tridex.org/wp-content/uploads/Db2ExternalTables_Tridex.pdf第21页(:
db2 "CREATE EXTERNAL TABLE TABLE1_FIXED (col1 int, col2 varchar(20)) USING (DATAOBJECT '/home/myuser/myfile.fixed' FORMAT FIXED LAYOUT(REF BYTES 1,col1 BYTES @1, col2 varchar(20) BYTES 4) RECORDLENGTH @1+6)"
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement. Invalid character: "". Text preceding the invalid character: "F BYTES 1,col1 BYTES".
SQLSTATE=42601
关于这个SQL查询,我尝试了很多方法,但都没有成功。
我还尝试从一个简单的表创建一个外部表:
[i1156@pc-l-0037(el040701_dev:) ~]$ db2 "create table TABLE (col1 char(1) not null)"
DB20000I The SQL command completed successfully.
db2 "CREATE EXTERNAL TABLE TABLE_EXT (col1 char(1) not null) USING (DATAOBJECT '/home/myuser/TABLE.fixed' FORMAT FIXED LAYOUT(col1 char(1)) RECORDLENGTH 1)"
DB20000I The SQL command completed successfully.
cat TABLE.fixed
abc
db2 "select * from TABLE_EXT"
SQL1476N The current transaction was rolled back because of error "-5199".
SQLSTATE=40506
外部表的创建似乎还可以,但当我在这个外部表上运行"select*"时,我得到了SQL1476N错误。
我希望有人能帮助我;(感谢
最后,我取得了进展,解决了原来的问题。
在我的简单测试sql查询中,我忘记在LAYOUT选项中的列定义之后指定"BYTES"选项。
而且,我猜我的输入文件";TABLE.fixed";需要具有行末字符,而最初的情况并非如此。在编辑它并用LF分隔每一行之后,我可以运行";选择"*";在我的外桌上。也许这是对外部表解析的限制。
以下是步骤:
cat TABLE.fixed
a
b
c
db2 "CREATE EXTERNAL TABLE TABLE_EXT (col1 char(1) not null) USING (DATAOBJECT '/home/myuser/TABLE.fixed' FORMAT FIXED LAYOUT(col1 char(1) BYTES 1))"
db2 "select * from TABLE_EXT"
COL1
----
a
b
c
3 record(s) selected.
关于另一个包含引用的SQL查询,我也解决了这个问题,将'\@'序列替换为'&'(以下查询适用于我的需求,具有空值(:
cat table1_ASC.txt
N1 Nrow1
N2 Nrow2
N45 Y
db2 "CREATE EXTERNAL TABLE TABLE1_FIXED (col1 int, col2 varchar(20)) USING (DATAOBJECT '/home/myuser/table1_ASC.txt' FORMAT FIXED LAYOUT(ref BYTES 1, col1 int BYTES 11 nullif &1='Y', ref BYTES 1, col2 varchar(20) BYTES 20 nullif &3='Y'))"
db2 "select * from TABLE1_FIXED"
COL1 COL2
----------- --------------------
1 row1
2 row2
3 -
3 record(s) selected.