db2 export data to remote location

Question

I am trying to connect to db2 database (server2) to export data from table to a csv file, for this I am using clpplus from the local server (server1) (which I want to export the csv to), the reason I want the CSV file to be on server1 is that I want to import it to a VERTICA database using COPY command

clpplus db2admin/Password@server2-ip:50000/SAMPLE

then I run

EXPORT TO C:\Data\alarms.csv OF DEL MODIFIED BY NOCHARDEL SELECT * FROM v_alarms;

The thing is that alarms.csv file is created on server2, but I want it to be created on server1. I tried something like

EXPORT TO server1-ip\C:\Data\alarms.csv OF DEL MODIFIED BY NOCHARDEL SELECT * FROM v_alarms;

but doesn't seem to work as it returns an I/O error

SQL3001C An I/O error (reason = "sqlofopn -2029060079") occurred while opening the output file

db2level command returns: on server1:

DB21085I This instance or install (instance name, where applicable: "DB2") uses "64" bits and DB2 code release "SQL11050" with level identifier "0601010F". Informational tokens are "DB2 v11.5.0.1077", "s1906101300", "DYN1906101300WIN64", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

on server2:

DB21085I This instance or install (instance name, where applicable: "DB2") uses "64" bits and DB2 code release "SQL11050" with level identifier "0601010F". Informational tokens are "DB2 v11.5.0.1077", "s1906101300", "DYN1906101300WIN64", and Fix Pack "0". Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".

Answer 1

With Db2 11.5/Db2 Warehouse you can use EXTERNAL TABLES to export the data with CLPPlus, e.g:

/opt/ibm/db2/v11.5/bin/clpplus <myuser>/<myspass>@<host>:50000/BLUDB

Then:

SQL> Export external to '/tmp/data.del' options(REMOTESOURCE 'JDBC') select * from db2inst1.mytab ;
DB250000I: The command completed successfully.

REMOTESOURCE is the keyword to store the data in different location than the server (you can e.g. use LZ4 to compress the data before sending, which can help if the dataset is large).

For the possible options values, see the documentation for CREATE EXTERNAL TABLE. Of course you can also use full Db2 client, as mao explained.

Answer 2

You are using Db2-LUW v11.5 (currently the latest version), which documents the following CLPPlus restructuib at this link:

IMPORT, EXPORT and LOAD commands have a restriction that processed files must be on the server (note: only for CLPPlus).

You have different options with v11.5 (as compared to earlier versions), depending on why you want to create CSV files.

One option with v11.5 is to use external tables, that is to copy the contents of the real table into a external-table and then use the clpplus command export (external table). Use the EXPORT CLPPlus command to export an external table file to a local server location, a remote client, a IBM® Cloud Object Storage, or an AWS S3 object store. This may suit your use case, only you can decide. You need to study the documentation to get the details. With this option, you continue to use CLPPlus, and you don't need any of the following.

One other older option is to ignore CLPplus and instead use the legacy db2 command line processor ,which has an EXPORT command that will write the output file locally to where it is running. This option is useful is you already have a set of scripts that depend on the CLP as many legacy sites already have. This option has prerequisites, specifically that the db2 CLP is available , its packages are bound into the target databases, and that the nodes and databases are catalogued. If either or both of your databases are on cloud then this option can be frustrating if you lack relevant permissions to bind the required packages. None of these activities (binding, cataloging, export) are programming, they are all configuration, and typically done by a DBA or a devops script. Each of the relevant commands are exhaustively documented in the Knowledge Centre:

Binding the CLI utilities may need to be done if your db2 client differs from the db2level of the target database. So you only need to do this if you get errors that mention CLI packages not being bound or missing.

Catalog actions. On server1 you would use db2 catalog tcpip node s2node... to define the details of server12. See documentation catalog tcpip node . Next you run db2 catalog database ... at node s2node to point-to the remote database on the server2 node, see documentation catalog database. Then db2 terminate.

To use the CLP on server1 , db2 connect to ... user ... using ... to make the connection to the server2 database, and finally db2 EXPORT TO\] C:\Data\alarms.csv OF DEL MODIFIED BY NOCHARDEL SELECT * FROM schemaname.v_alarms