CODEWITHSUNDEEP
db2flat-filedb2-luw
AngocA
AngocA

Reputation: 7693

DB2 external tables?

I just heard that Oracle has a feature called External Table that allows to access a flat file (for example a CSV file in the file system) from the database.

I just want to know if there is something similar in DB2 for LUW.

The closest thing I could see is to implement a Table function (written in Java, for example) that will read the file, and return a table with the data from the file. However, this procedure takes a long time (create the Java code, compile the Java and create the function in DB2 associating the Java class) and the implementation is not dynamic for different files with different quantity of columns (table function returns a predefined set of columns).

Here the documentation of Oracle External Tables: http://docs.oracle.com/cd/B28359_01/server.111/b28319/et_concepts.htm

Upvotes: 2

Views: 2989

Answers (4)

Doug Dailey
Doug Dailey

Reputation: 11

IBM Db2 11.5 has support for external tables that will allow you to do this.

This was formerly provided only by Netezza and this functionality has made its way to Db2.

See the manual page for CREATE EXTERNAL TABLE here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r_create_ext_table.html

Upvotes: 1

SilentSteel
SilentSteel

Reputation: 2434

There is also: (a) external table support in the warehousing engine products (Db2 Warehouse, Db2 Warehouse on Cloud) (b) Data virtualization (aka federation/fluid query) in all Db2 products which may achieve the same thing.

Upvotes: 0

bart
bart

Reputation: 121

As mentioned, InfoSphere Federation Server is a good choice. There are two alternatives for DB2 UDB (Universal Database), which may be helpful in specific use cases:

  • DataLinks: it is basically another data type that keeps a reference to your external file. It also provides several levels of control over external data such as referential integrity, access control, coordinated backup and recovery, and transaction consistency.
  • DB2 Extenders: they extend functionality of the DB2 to operate on specific file formats, e.g. XML Extender provide set of features to operate on XML files inside DB2

Upvotes: 0

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

Yes, IBM offers this as part of their InfoSphere Federation Server, which basically allows you to define nicknames inside a database to various data sources. Supported data sources

Upvotes: 1

Related Questions