Connecting with third-party tools

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. Download and install Aginity Workbench.
2. Determine your ODBC DSN from the connection information that you noted earlier.
3. Launch Aginity Workbench. If the database connection dialog box does not open automatically, open it by clicking Connect on the toolbar.
4. Establish a database connection. Use the host name, user ID, and password from the connection information that you noted earlier.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. In Tableau Desktop, open the window or page in your tool that is used to define a database connection.
2. From the start page, click Connect to data.
3. From the Data Sources list, select the data source or driver to use for your database connection. In the On a server section of the list, select IBM Db2.
4. From the IBM DB2 Connection window, enter the connection information by using the definitions in Table 1.
5. Click Connect to establish the connection. Tableau offers several options for connecting to your data. To make full use of your Db2 Warehouse on Cloud database, choose the Connect Live option.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

You must have the Db2 driver package or the IBM® Data Server Driver Package installed on your local computer.

Restriction: Connections between Excel and Db2 Warehouse on Cloud are supported on only the Windows operating system.

Procedure

1. In the web console, go to the Run SQL page.
2. Type one or more SELECT statements in the editor text box.
3. Click one of the Run options.
4. Click Excel ODC File.
5. Download and open the BLUExcel.odc file in Excel. If a security notice is displayed, click Enable.
6. Click Open to connect to the Db2 Warehouse on Cloud database. The Connect To DB2 Database dialog box opens.
7. Type the user ID and password that you use to log in to Db2 Warehouse on Cloud. To obtain the user ID and password, click Connect in the web console or Connect > Connection Information in the web console.
8. Ensure that the connection mode is Share, and then click OK.

Results

The query results are displayed in an Excel spreadsheet. These are the same results that are displayed in the Results viewer. Now you can generate charts and reports and analyze your data by using Excel. For more information about how to do this and how to run SQL queries on your data from the web console, see:

• Tutorial: Generating charts and reports by using Excel
• Analyzing with Excel

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

You must have the Db2 driver package or the IBM® Data Server Driver Package installed on your computer.

Procedure

1. Determine your ODBC DSN data from the connection information that you collected beforehand.

2. Create a new connection:

   a. In the ArcCatalog Catalog tree, open the Database Connections node and click Add Database Connection.

   b. In the Database Connections wizard:

   • Select DB2 from the Database Platform drop-down list.

   • Enter the following string in the Data source field:

     HostName=<hostname>;Port=<port>;Database=<database>;
     CLIENTBUFFERSUNBOUNDLOBS=1;LOBCACHESIZE=50000000;
     FET_BUF_SIZE=256K  
     

     where <hostname>, <port>, and <database> are placeholders that represent the host name, port number, and database name that you noted earlier.

   • Select Database authentication as the authentication type.

   • Specify your user name and password (the user ID and password that you noted earlier) in the corresponding fields.

   • Press OK.

     

Results

The ArcCatalog component of Esri ArcGIS for Desktop is now connected to your Db2 Warehouse on Cloud database.

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

For steps on how to connect from SAS to a Db2 Warehouse on Cloud database, see the SAS documentation:

• SAS/ACCESS Interface to DB2 under UNIX and PC Hosts

Prerequisites

Before attempting to connect to your Db2 Warehouse on Cloud database, verify that you have the prerequisites.

Procedure

1. In your local R environment, install the ibmdbR package by entering the following command:

   install.packages("ibmdbR")

   Your local R environment accesses the Comprehensive R Archive Network (CRAN) and automatically downloads and installs the ibmdbR package and any prerequisite packages that are not already installed.

2. Create an ODBC driver connection between your R development environment and your Db2 Warehouse on Cloud database:

   a. Set up your database as an ODBC data source.

   b. Open your locally installed R development environment.

   c. At the R prompt, enter the following statements to create the connection. Replace the placeholders with the connection information that you collected beforehand.

   • If your locally installed R development environment runs in the Db2 Warehouse on Cloud database:

     library(ibmdbR)
     host.name <- "placeholderForYourHostName"
     port <-"placeholderForPortNumber" # 50000 if not using SSL or 50001 if using SSL
     user.name <-"placeholderForYourUserName"
     pwd <- "placeholderForYourPassword"
     con.text <- paste("placeholderForYourDSNName;DRIVER=BLUDB",
                       ";Database=BLUDB",
                       ";Hostname=",host.name,
                       ";Port=",port,
                       ";PROTOCOL=TCPIP",
                       ";UID=", user.name,
                       ";PWD=",pwd,sep="")
     # Connect to using a odbc Driver Connection string to a remote database
     con <- idaConnect(con.text)
     

   • If your locally installed R development environment does not run in the Db2 Warehouse on Cloud database:

     library(ibmdbR)
     driver.name <- "{placeholderForYourDriverName}"
     db.name <- "placeholderForYourDatabaseName"
     host.name <- "placeholderForYourHostName"
     port <-"placeholderForYourPort"
     user.name <-"placeholderForYourUserName"
     pwd <- "placeholderForYourPassword"
     con.text <- paste("placeholderForYourDSNName;DRIVER=",driver.name,
                       ";Database=",db.name,
                       ";Hostname=",host.name,
                       ";Port=",port,
                       ";PROTOCOL=TCPIP",
                       ";UID=", user.name,
                       ";PWD=",pwd,sep="")
     # Connect to using a odbc Driver Connection string to a remote database
     con <- idaConnect(con.text)
     

     The statement that is used to create the connection object uses the idaConnect() method, not the odbcConnect() or odbcDriverConnect() methods.

   d. Initialize the analytics package by issuing the following R command:

   idaInit(con)

   e. To test whether the connection is working, issue the following R command:

   idaShowTables()

   The console displays a list of all of the tables and views in the current schema.

Watch this video to see how to create a connection in RStudio and add connected data to a project: