Define Location to Connect to Greenplum Database

Question

How can I create a Greenplum database location and configure it in order for HVR GUI to be able to connect to a Greenplum database?

Environment

HVR 5

Answer

For information on requirements for Greenplum, see our documentation.

Prerequisites:

1. The hvrremotelistener is running on the remote Greenplum machine
2. The database that we are connecting to is testdb
3. Driver Manager Library is already present on the remote machine where Greenplum resides
4. The odbcinst.ini file is already present on the remote machine where Greenplum resides Greenplum database should be running
5. The DataDirect Manager Library must exist on the machine that HVR connects to Greenplum
6. The odbcinst.ini file must be present on the Linux machine that HVR connects to Greenplum
7. The gpfdist server needs to be used in order to perform bulk loading in Greenplum database
8. For Greenplum it is necessary that you have a staging directory created on the target machine. In this case it is in the directory structure /home/gpadmin/stage1
9. A channel number_demo is already created with source Oracle database named as orcl

Steps

Step 1. Create a new location

Create location under Location Configuration:

1. In the New location dialog, specify the name of your location and select Greenplum under the Class section. In this example, we name the location gplum

   

2. Since a Greenplum database in this example is on a remote server, we click the checkbox for Connect to HVR on remote machine. This action will unlock the fields in the Database Connection group box:

   • Node: IP address of the machine where hvrremotelistener is running
   • Port: Port number on which hvrremotelistener is running. By default it is 4343
   • Login: Username used to connect to your Greenplum database
   • Password: Password used to connect to your Greenplum database

   

3. Under the section Database Connection, fill in the following parameters:

   • Node: IP address of the server where hvrremotelistener is running
   • Port: Greenplum Database clients connect with TCP to the Greenplum master instance at the client connection port, 5432. So by default we use that port
   • Database: Name of the Greenplum database that we are connecting to. In our case it is testdb
   • User: Username used to connect to your Greenplum database
   • Password: Password used to connect to your Greenplum database
   • Driver Manager Library: Required only if you are running Greenplum on linux/unix. It should point to the DataDirect library installation
   • ODBCINST: This field is required only if you are running Greenplum on linux/unix. It should point to a properly configured odbcinst.ini file

   

4. Click on Test connection to see if it is working, then click on OK to add this location to the list.

Step 2. Configure gpfdist

Configure gpfdist. To do it, running the command below from the directory where your staging directory resides:

gpfdist -p 33333 -d stage1 -m 1048576

Step 3. Add Greenplum as target

Add gplum as a TARGET to your channel. For correct work, the following is essential:

• While defining Integrate, make sure to check /Burst

• While defining LocationProperties, define /StagingDirectoryHvr and /StagingDirectoryDb

  

Step 4

Save all the changes you've made. Greenplum database is now ready to be used as a Target machine.