Using DB2 on the IBM Cloud from macOS

This is a relatively new area to me, as I typically work with DB2 on a local ( to me ) server, be it an AS/400, an AIX LPAR or a Linux or Windows VM.

However, IBM does offer DB2 on Cloud, formerly known as IBM dashDB, which is available from the IBM Cloud ( nee Bluemix ) console: -

Having spun up an instance ( using the Lite plan ) I then get a nice little dashboard: -

which includes documentation on the many different tools/methods I can use to connect to the database.

In essence, I now have a database - BLUDB - sitting on an internet-accessible host ( with a nice long complex host/service name ) on port 50000 ( as I've not yet chosen to add TLS encryption ) with a set of bind credentials.

Having downloaded and installed the macOS driver, I get the CLPPlus tool: -

/Applications/CLPPlus.app/Contents/MacOS/clpplus

plus a whole set of DB2 driver tools: -

ls -al /Applications/dsdriver/

total 160
drwxr-xr-x  26 davidhay  admin    832 22 May 09:55 .
drwxrwxr-x+ 76 root      admin   2432 21 May 16:55 ..
-rw-r--r--   1 davidhay  admin      4 22 May 09:55 .ftok
-r-xr-xr-x@  1 davidhay  admin   2165 21 May 16:55 Readme.txt
drwxr-xr-x   4 davidhay  admin    128  3 Apr  2017 adm
drwxr-xr-x@ 12 davidhay  admin    384 21 May 16:55 bin
drwxr-xr-x  12 davidhay  admin    384  3 Apr  2017 bnd
drwxr-xr-x   7 davidhay  admin    224 22 May 09:55 cfg
drwxrwsr-t   3 davidhay  admin     96  3 Apr  2017 cfgcache
drwxr-xr-x   4 davidhay  admin    128  3 Apr  2017 conv
-r--r--r--   1 davidhay  admin   2365 21 May 16:55 db2cshrc
drwxr-xr-x   3 davidhay  admin     96 22 May 09:55 db2dump
-r--r--r--   1 davidhay  admin   2241 21 May 16:55 db2profile
drwxr-xr-x  14 davidhay  admin    448  3 Apr  2017 include
-r-xr-xr-x@  1 davidhay  admin  58056 21 May 16:55 installDSDriver
-rw-r--r--   1 davidhay  admin   2816 21 May 16:55 installDSDriver.log
drwxr-xr-x@ 60 davidhay  admin   1920 21 May 16:55 java
drwxr-xr-x@  4 davidhay  admin    128 21 May 16:55 json
drwxr-xr-x   9 davidhay  admin    288  3 Apr  2017 lib
drwxr-xr-x@ 27 davidhay  admin    864 21 May 16:55 license
drwxr-xr-x   3 davidhay  admin     96  3 Apr  2017 msg
drwxr-xr-x@  3 davidhay  admin     96 21 May 16:55 php
drwxr-xr-x   3 davidhay  admin     96 21 May 16:55 python
drwxr-xr-x@  4 davidhay  admin    128 21 May 16:55 rdf
drwxr-xr-x@  3 davidhay  admin     96 21 May 16:55 ruby
drwxr-xr-x@ 10 davidhay  admin    320 21 May 16:55 tools

The trick was to execute the db2profile script: -

source /Applications/dsdriver/db2profile

which gives me this: -

db2level 

DB21085I  This instance or install (instance name, where applicable: "*") uses 
"64" bits and DB2 code release "SQL11011" with level identifier "0202010F".
Informational tokens are "DB2 v11.1.1.1", "s1703232000", "DARWIN64111", and Fix 
Pack "1a".
Product is installed at "/Applications/dsdriver".

db2cli -help

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.

===============================================================================
DB2 interactive Call Level Interface (CLI) environment
===============================================================================

The DB2 interactive CLI environment is a multi-purpose design and prototyping 
tool.

Command syntax

  db2cli [-help] [<mode>]

Command parameters (<mode> values)

  validate

      Validate db2cli.ini and db2dsdriver.cfg configuration files.

  bind

      Bind dynamic packages used by CLI, .NET and JCC applications against the 
      target database.

  refreshldap

      Functionality to refresh dsn entries from LDAP to IBM Data Server Driver 
      Configuration File.

  execsql

      Execute or prepare the given SQL statements. Can also capture the SQLs in 
      the PDQXML file when capturemode is enabled in the configuration files.

  writecfg

      Functionality to add/remove dsn or database entries, and to add, modify 
      and remove the parameters in the db2dsdriver.cfg file.

  -help

      Display db2cli tool help and usage information.

NOTE: For further details of each <mode>, append "-help" option after
specifying <mode>.

For example:

  1. db2cli validate -help
  2. db2cli writecfg -help

This then gave me what I needed to create the DB2 CLI connection: -

db2cli writecfg add -database BLUDB -host foobar.snafu.bluemix.net -port 50000

db2cli writecfg add -dsn dashdb -database BLUDB -host foobar.snafu.bluemix.net -port 50000

 db2cli validate -dsn dashdb -connect -user srb12321 -passwd p455w0rd! 

the latter of which returned: -

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver Package
Client Version (level/bit): DB2 v11.1.1.1 (s1703232000/64-bit)
Client Platform           : Darwin
Install/Instance Path     : /Applications/dsdriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /Applications/dsdriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /Applications/dsdriver/cfg/db2cli.ini
db2diag.log Path          : /Applications/dsdriver/db2dump/db2diag.log

===============================================================================
db2dsdriver.cfg schema validation for the entire file:
===============================================================================

Success: The schema validation completed successfully without any errors.

===============================================================================
db2cli.ini validation for data source name "dashdb":
===============================================================================

Note: The validation utility could not find the configuration file db2cli.ini. 
The file is searched at "/Applications/dsdriver/cfg/db2cli.ini".

===============================================================================
db2dsdriver.cfg validation for data source name "dashdb":
===============================================================================

[ Parameters used for the connection ]

Keywords                  Valid For     Value
---------------------------------------------------------------------------
DATABASE                  CLI,.NET,ESQL BLUDB
HOSTNAME                  CLI,.NET,ESQL foobar.snafu.bluemix.net
PORT                      CLI,.NET,ESQL 50000

===============================================================================
Connection attempt for data source name "dashdb":
===============================================================================

[SUCCESS]

===============================================================================
The validation is completed.
===============================================================================

Having created a table ( as per another post here ), I've also connected the ACE Toolkit to DB2: -

I also found the documentation for the CLI to be of use: -

Db2 driver package

Connecting CLPPlus to a Db2 database

For reference, here's a useful insight into DB2-on-Cloud: -

6 ways IBM dashDB moves DB2 customers to the cloud faster