What REST API load_source and params do I use to load CSV data from IBM COS S3 to IBM Db2 on Cloud

Question

I have been unable to use the Db2 on Cloud REST API to load data from a file in IBM Cloud Object Storage (COS). This is preventing a hybrid integration POC.

Another user has reported similar REST API issue using the SERVER configuration, see the IBM Developer thread at https://developer.ibm.com/answers/questions/526660/how-to-use-db2-on-cloud-rest-api-to-load-data-from.html

I cannot seem to get the parameters correct, and I think the docs have errors in them for current Cloud Object Storage with HMAC keys ... such as for the endpoint to use, and whether auth_id should be the access_key_id.

I've tried a variety of data load commands, like the following, but none work. Can someone provide an example of a command that works (with any considerations/explanations for values)?

curl -H "x-amz-date: 20200112T120000Z" -H "Content-Type: application/json" 
  -H "Authorization: Bearer <auth_token>" 
  -X POST "https://dashdb-xxxx.services.eu-gb.bluemix.net:8443/dbapi/v3/load_jobs"
 -d '{"load_source": "SOFTLAYER", "schema": "MDW84075",
     "table": "SALES", "file_options":
      {"code_page": "1208", "column_delimiter": ",",
       "string_delimiter": "", "date_format": "YYYY-MM-DD", "time_format": 
       "HH:MM:SS", "timestamp_format": "YYYY-MM-DD HH:MM:SS", 
       "cde_analyze_frequency": 0 }, "cloud_source":
 {"endpoint": "https://s3-api.us-geo.objectstorage.softlayer.net/auth/v2.0", 
"path": "<bucket>/sales_data_test.csv", "auth_id": "<access_key_id>", 
"auth_secret": "<secret_access_key>"} }'

Different attempts with the API call fail with a variety of messages, which usually do not have enough information to debug (and searches in doc/web do not find the messages); eg:

{"trace":"","errors":[{"code":"not_found", "message":"HWCBAS0030E: The requested resource is not found in service admin.", "target":{"type":"","name":""},"more_info":""}]}

P.S. I was able to use the DB2 on Cloud UI to load data from the file in COS S3, with the same access key values.

P.P.S. Perhaps "load_source": "SOFTLAYER" is an issue, but it is the only option that might map to an IBM cloud object storage. The API docs do not give any other option that might work with IBM COS S3.

data_henrik data_henrik · Accepted Answer · 2020-01-20T16:43:23

If you use Db2 on Cloud with Cloud Object Storage with the REST API, then for LOAD the type should be S3. Both Amazon and IBM COS use the S3 protocol. Softlayer had its own SWIFT protocol before, but it is not available (anymore) for IBM COS.

Also see here for some docs on loading data using LOAD. The examples use Amazon and IBM COS, both with S3 protocol.

What REST API load_source and params do I use to load CSV data from IBM COS S3 to IBM Db2 on Cloud

1 Answers