CODEWITHSUNDEEP
csvfile-uploaddb2ibm-event-store
Christian Garcia Arellano
Christian Garcia Arellano

Reputation: 63

How do I load a CSV file into a Db2 Event Store remotely using a Db2 client?

I see in the documentation for Db2 Event Store that a CSV file can be loaded into the system when the file is within the system in this document https://www.ibm.com/support/knowledgecenter/en/SSGNPV_2.0.0/local/loadcsv.html. I also found that you can connect to a Db2 Event Store database using the standard Db2 client in How do I connect to an IBM Db2 Event Store instance from a remote Db2 instance?. What I am trying to do now is load a CSV file using that connection. Is it possible to load it remotely ?

Upvotes: 0

Views: 1354

Answers (2)

Psyduck
Psyduck

Reputation: 667

With other answers mentioned the connection and loading using the traditional db2. I have to add some more details that are required specifically for Db2 Event Store.

Assuming we are using a Db2 Client container, which can be found at docker hub with tag ibmcom/db2. Basically we have to go through following steps:

1/ establish a remote connection from db2 client container to the remote db2 eventstore database

2/ use db2 CLP commands to load the csv file using the db2's external table load feature, which will load csv file from db2 client container to the remote eventstore database.

Step 1: Run the following commands, or run the it in a script. Note that the commands need to be run as the db2 user in the db2 client container. The db2 user name is typically db2inst1

#!/bin/bash -x
NODE_NAME=eventstore
. /database/config/db2inst1/sqllib/db2profile

### create new keydb used for authentication
# remote old keydb files
rm -rf $HOME/mydbclient.kdb  $HOME/mydbclient.sth $HOME/mydbclient.crl $HOME/mydbclient.rdb
$HOME/sqllib/gskit/bin/gsk8capicmd_64 -keydb -create -db $HOME/mydbclient.kdb -pw ${SSL_KEY_DATABASE_PASSWORD} -stash
KEYDB_PATH=/var/lib/eventstore/clientkeystore

# get the target eventstore cluster's SSL public certificate using REST api
bearerToken=`curl --silent -k -X GET "https://$IP/v1/preauth/validateAuth" -u $EVENT_USER:$EVENT_PASSWORD | python -c "import sys, json; print (json.load(sys.stdin)['accessToken']) "`

curl --silent -k -X GET -H "authorization: Bearer $bearerToken"  "https://${IP}:443/com/ibm/event/api/v1/oltp/certificate" -o $HOME/server-certificate.cert 

# insert eventstore cluster's SSL public cert into new gskit keydb
$HOME/sqllib/gskit/bin/gsk8capicmd_64 -cert -add -db $HOME/mydbclient.kdb  -pw ${SSL_KEY_DATABASE_PASSWORD}  -label server -file $HOME/server-certificate.cert -format ascii -fips

# let db2 client use the new keydb 
$HOME/sqllib/bin/db2 update dbm cfg using SSL_CLNT_KEYDB $HOME/mydbclient.kdb SSL_CLNT_STASH $HOME/mydbclient.sth

# configure connection from db2Client to remote EventStore cluster.
$HOME/sqllib/bin/db2 UNCATALOG NODE ${NODE_NAME}
$HOME/sqllib/bin/db2 CATALOG TCPIP NODE ${NODE_NAME} REMOTE ${IP} SERVER ${DB2_CLIENT_PORT_ON_EVENTSTORE_SERVER} SECURITY SSL
$HOME/sqllib/bin/db2 UNCATALOG DATABASE ${EVENTSTORE_DATABASE}
$HOME/sqllib/bin/db2 CATALOG DATABASE ${EVENTSTORE_DATABASE} AT NODE ${NODE_NAME} AUTHENTICATION GSSPLUGIN
$HOME/sqllib/bin/db2 terminate

# Ensure to use correct database name, eventstore user credential in remote 
# eventstore cluster
$HOME/sqllib/bin/db2 CONNECT TO ${EVENTSTORE_DATABASE} USER ${EVENT_USER} USING ${EVENT_PASSWORD}

Some important variables: EVENTSTORE_DATABASE: database name in the remote eventstore cluster

EVENT_USER: EventStore user name remote eventstore cluster

EVENT_PASSWORD: EventStore user password remote eventstore cluster

IP: Public IP of remote eventstore cluster

DB2_CLIENT_PORT_ON_EVENTSTORE_SERVER: JDBC port of remote eventstore cluster, which is typically 18730

SSL_KEY_DATABASE_PASSWORD: keystore's password of the gskit keydb file in the db2 client container, you can set it as you like

After running the commands above, you should have established the connection between local db2 client container and the remote eventstore cluster

2/ Load csv file using external table feature of db2

After the connection between db2 client and remote eventstore cluster is established, we can issue db2 CLP commands like issuing command to any local db2 database.

For example:

    // establish remote connection to eventstore database
    // replace the same variables in ${} with what you used above.
    CONNECT TO ${EVENTSTORE_DATABASE} USER ${EVENT_USER} USING ${EVENT_PASSWORD}
    SET CURRENT ISOLATION UR

    // create table in the remote eventstore database
    CREATE TABLE db2cli_csvload (DEVICEID INTEGER NOT NULL, SENSORID INTEGER NOT NULL, TS BIGINT NOT NULL, AMBIENT_TEMP DOUBLE NOT NULL, POWER DOUBLE NOT NULL, TEMPERATURE DOUBLE NOT NULL, CONSTRAINT "TEST1INDEX" PRIMARY KEY(DEVICEID, SENSORID, TS) INCLUDE (TEMPERATURE)) DISTRIBUTE BY HASH (DEVICEID, SENSORID) ORGANIZE BY COLUMN STORED AS PARQUET

    // external table load to remote eventstore database
    INSERT INTO db2cli_csvload SELECT * FROM EXTERNAL '${DB_HOME_IN_CONTAINER}/${CSV_FILE}' LIKE db2cli_csvload USING (delimiter ',' MAXERRORS 10 SOCKETBUFSIZE 30000 REMOTESOURCE 'YES' LOGDIR '/database/logs' )

    CONNECT RESET
    TERMINATE

For more information, you can check on the Db2 EventStore's public github repo. https://github.com/IBMProjectEventStore/db2eventstore-IoT-Analytics/tree/master/db2client_remote/utils

Upvotes: 1

kkuduk
kkuduk

Reputation: 601

This should be doable with an extra keyword specified REMOTESOURCE YES, e.g:

db2 "INSERT INTO import_test SELECT * FROM EXTERNAL '/home/db2v111/data.del' USING (DELIMITER ',' REMOTESOURCE YES)"

see an example here: IMPORT script on IBM DB2 Cloud using RUN SQL Interface

Upvotes: 2

Related Questions