CODEWITHSUNDEEP
dashdblobclpplus
jpwsutton
jpwsutton

Reputation: 170

How to export IXF and LOB files from dashdb

I need to copy some tables in one dashdb database over to separate dashdb database. Normally I would export the CSV file from one and load it into the other using the Web console, however one table in particular has a CLOB column and so we will need to export to an ixf + lob files and then import it. Unfortunately I can't see any easy way to do this as it looks like clpplus can only export to the server that the database is on (which I don't have access to) and I can't see any way to get it to export the lob files. Does anyone know how best to accomplish this?

Upvotes: 0

Views: 2102

Answers (2)

Matthias Nicola
Matthias Nicola

Reputation: 1

Another option is to export the table with the LOBs to a local machine and then import into another dashDB.

One way to export a dashDB table to a local client is to run the EXPORT command in a DB2 Command Line Processor (CLP) on your client machine. To do so, you need to install the IBM Data Server Runtime Client and then catalog your dashDB databases in the client, like this: 

CATALOG TCPIP NODE mydash REMOTE  dashdb-txn-small-yp-lon02-99.services.eu-gb.bluemix.net SERVER 50000;
CATALOG DATABASE bludb AS dash1 AT NODE mydash;
CONNECT TO dash1 USER <username> USING <password>;

Now, let's export the table called "mytable" so that the LOB column is written to a separate file:

export to mytable.del of del 
      lobfile mylobs
      modified by lobsinfile
      select * from mytable;

This export commands produces the files mytable.del and mylobs.001.lob. The file mytable.del contains pointers into the file mylobs.001.lob that specify the offset and length of each value.

If the LOB data is too large to fit into a single file, then additional files mylobs.002.lob, mylobs.003.lob, etc. will be created.

Note the exported data will be sent from dashDB to your local client in uncompressed form, which may take some time depending on the data volume.

If the .DEL and .LOB files reside on a client machine, such as your laptop or a local server, you can use the IMPORT command to ingest these files into a table with a LOB column. In the CLP you would first connect to the dashDB database that you want to load into.

Let's assume the original table has been exported to the files mytable.del and mylobs.001.lob, and that these files are now located on your client machine in the directory /mydata. Then this command will load the data and LOBs into the target table:

IMPORT FROM /mydata/mytable.del OF DEL
   LOBS FROM /mydata 
   MODIFIED BY LOBSINFILE
   INSERT INTO mytable2;

This IMPORT command can be run in a DB2 Command Line Processor on your client machine.

Upvotes: 0

Torsten Steinbach
Torsten Steinbach

Reputation: 391

If the CLOB values are in reality smaller than 32K you can try to transform them into a VARCHAR value as part of the SELECT statement that you provide to EXPORT.

If you really need to export LOB files you can write them to your users home dir inside the dashDB instance and then use the /home REST API to download the files e.g. with curl: https://developer.ibm.com/static/site-id/85/api/dashdb-analytics/

Upvotes: 0

Related Questions