0

I am trying to create/select an Db2 external table with the FIXED format, but whitout any success.

Here is what I have tried:

From an IBM pdf (https://www.tridex.org/wp-content/uploads/Db2ExternalTables_Tridex.pdf page 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

I have tried many things concerning this SQL query, without any success.

I have also tried to create an external table from a simple table:

[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

The external table creation seems to be okay, but when I run a 'select *' on this external table, I got the SQL1476N error.

I hope someone could help me ;) Thanks

Gas
  • 17,601
  • 4
  • 46
  • 93
Steph
  • 47
  • 1
  • 8
  • Do you intentionally create this external table with the `CHAR(1)` column `col1` making db2 read longer string (3 characters) from the file? Is this some "stress test"? – Mark Barinstein Sep 22 '22 at 08:41
  • In fact, the "TABLE.fixed" file containing 'abc' correspond to 3 rows inserted into the "TABLE" table: 'a', 'b', and 'c'. To be sure that this data file is correct, I have just tried to load this file into a new table having the same DDL as the "TABLE" origin table with the Db2 Load command, and the Load works file, I can execute a "Select *" on the table and obtain 3 rows. – Steph Sep 22 '22 at 12:42

1 Answers1

0

Finally, I have been able to make progress and resolve the original question.

Into my simple test sql query, I forgot to specify the 'BYTES' option after the column definition into the LAYOUT option.

And also, I guess that my input file "TABLE.fixed" needs to have end of rows characters, which is not the case originally. After edit it and separate each rows with LF, I can run "select *" on my external table. Maybe this is a limitation of the external tables parsing.

Here are the steps :

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.

Concerning the other SQL query, containing references, I have also been able to resolve the issue, by replacing the '\@' sequence by '&' (the following query is an adapted one for my needs, with null values):

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.
Steph
  • 47
  • 1
  • 8