1

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".

Elias Ghali
  • 823
  • 1
  • 13
  • 29
  • What is your Db2-server *version* for both server1 and server2 ? EDIT your question to append the output of the `db2level` command when tun on server1 and server2 . The reason is that IBM documents for some versions: "IMPORT, EXPORT and LOAD commands have a restriction that processed files must be on the server" – mao Oct 11 '19 at 09:25
  • To workaround that restriction you can use the plain `db2 export` command but that requires that the nodes/databases are catalogued first. The plain `db2` command will always write the output file to the workstation on which it runs. – mao Oct 11 '19 at 09:27
  • I would appreciate it if you can provide a full working script, to catalog the SAMPLE database, and export data from server2 to server1 – Elias Ghali Oct 11 '19 at 09:31

2 Answers2

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.

kkuduk
  • 591
  • 2
  • 6
  • Thank you for the tips, what I'm trying now is to use the EXTERNAL TABLES approach, but I'm still facing issues, from a CLPPlus cmd connected to SAMPLE db on server2, I'm running the following: `EXPORT EXTERNAL TO 'C:\Data\ftp_data\alarms.csv' SELECT * FROM v_alarms;` but it's returning an error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=*;_data\alarms.csv' AS; – Elias Ghali Oct 15 '19 at 07:33
  • @kkuduk Running `EXPORT EXTERNAL` gives on standby db `DB2 SQL Error: SQLCODE=-1773, SQLSTATE= , SQLERRMC=5,` – curious_nustian Sep 02 '21 at 08:09
1

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

mao
  • 11,321
  • 2
  • 13
  • 29
  • The reason I want the CSV file to be on server1 is that I want to import it to a VERTICA database using vsql with COPY command, therefore it's not actually expected for server1 to have db2 installed. also regarding the CLP `db2 connect to`, it doens't allow to specify the remote server whenever I try to run `db2 connect to server2-ip` it returns an error `SQL0104N An unexpected token "x.x.x.x" was found following "TO". Expected tokens may include: "". SQLSTATE=42601 – Elias Ghali Oct 11 '19 at 10:45
  • server1 needs to have *at least* a Db2-client installed (a client that includes the `db2` clp ). server1 does not need a Db2-server to be installed (although a server automatically includes a Db2 client). Your new symptom suggests that you have not correctly catalogued the remote node+remote-database on server1. You cannot connect via CLP unless the catalog actions completed without errors. – mao Oct 11 '19 at 11:11
  • Thank you for the tips, what I'm trying now is to use the EXTERNAL TABLES approach, but I'm still facing issues, from a CLPPlus cmd connected to SAMPLE db on server2, I'm running the following: `EXPORT EXTERNAL TO 'C:\Data\ftp_data\alarms.csv' SELECT * FROM v_alarms;` but it's returning an error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=*;_data\alarms.csv' AS; – Elias Ghali Oct 15 '19 at 07:30