Search code examples
sqldb2db2-luwdb2-woc

How to create external table in db2 with basic DML operation

I created external table with following command

db2 "

CREATE EXTERNAL TABLE TEST(a int) using
  (dataobject '/home/db2inst2/test.tbl' )
  )
"
db2 "insert into TEST values(1)"
db2 "insert into TEST values(2)"

But looks like it is replacing value. Is there any option to append files & do basic DML operation on external table. Please let me know if any other option available in db2 V11.5

Solution

  • It's not possible.

    CREATE EXTERNAL TABLE statement

    Restrictions

    • External tables cannot be used by a Db2 instance running on a Windows system.
    • Data being loaded must be properly formatted.
    • You cannot delete, truncate, or update an external table.
    • For remote external tables (that is, for external tables are not located in a Swift or S3 object store and for which the REMOTESOURCE option is set to a value other than LOCAL):
      • A single query or subquery cannot select from more than one external table at a time, and cannot reference the same external table more than once. If necessary, combine data from several external tables into a single table and use that table in the query.
      • A union operation cannot involve more than one external table.

    In addition:

    For an unload operation, the following conditions apply:

    • If the file exists, it is overwritten.