Db2 Warehouse (on Cloud): How to create external table?

0
votes

Per the "What's new in Db2 Warehouse on Cloud, ..." it is possible to create external tables. I looked at the linked documentation at the syntax on how to create such an external table to link to my Amazon S3 and my IBM Bluemix / Softlayer Swift Cloud Object Storage. I have a Db2 Warehouse on Cloud with an "Entry Plan" and I don't see any restrictions mentioned.

CREATE EXTERNAL TABLE exttab1(a int,s varchar(50)) using
  (dataobject 'testdata.csv'
   swift('https://fra02.objectstorage.softlayer.net/auth/v1.0/',
    'IBMOS12345:userid',
    '5---MyFullAPIKeyHere---b983',
    'henrik'
   )
  )

The above SQL statement gives me:

SQL0104N: database/sql/driver: [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "EXTERNAL" was found following "CREATE". Expected tokens may include: "". SQLSTATE=42601

What is the correct syntax? Are there restrictions I am not aware of?

1
amazon-s3db2ibm-clouddashdbibm-cloud-storage
No. It won't work. You are lucky that DB2 ignore your request. S3 can never be used as a data store, unless you intend to use it as read once in-memory data store. Otherwise, there is no way to "update" a S3 object. Even it allow you to do the rapid read and replace, AWS is going to charge you $0.01 for every 10k READ/1k write + data transfer cost. - mootmoot
An external table is a simple method for data exchange between apps and to load smaller amounts of data. LOAD already has S3 and Swift support - data_henrik
perhaps, you should look at the code below swift, that mentioned S3,i.e. S3 (endpoint, authKey1, authKey2, bucket). - mootmoot
I am using Cloud Object Storage with Swift API on the IBM Cloud. S3 is possible, not desired by me. - data_henrik
I will check the object storage access first. knowledgelayer.softlayer.com/faq/… - mootmoot

1 Answers

0
votes

The syntax and everything else was correct. I found out that my instance was not on the latest code level. The documentation was ahead of my cloud environment. I am now able to create the above external table and SELECT from it using SQL.