Testing connectivity to Greenplum

Here's a sample way to test:

$ ./odbctest
Registered Data Sources:
Greenplum Wire Protocol (DataDirect 6.0 Greenplum Wire Protocol)
Server Name? greenplum
User ID? gpadmin
Password? password
Detected Data Direct compatibility
Server greenplum conforms to LEVEL 1.
Server's cursor commit behavior: PRESERVE
Transactions supported: ALL

Maximum number of concurrent statements: 0

For a list of tables, use PRINT.

> PRINT
TABLE_SCHEM TABLE_NAME
--------------------------------------------------------------------------------
gp_toolkit __gp_localid
gp_toolkit __gp_log_master_ext
gp_toolkit __gp_log_segment_ext
gp_toolkit __gp_masterid
gp_toolkit gp_disk_free
information_schema sql_features
information_schema sql_implementation_info
information_schema sql_languages
information_schema sql_packages
information_schema sql_parts
information_schema sql_sizing
information_schema sql_sizing_profiles
public customer
public lineitem
public nation
public orders
public part
public partsupp
public region
public supplier
public uac_1_0
public uac_1_1
public uac_1_2
public uac_1_3
public uac_1_4
public unica_snapshot_test
public unica_snapshot_test2
gp_toolkit __gp_fullname
gp_toolkit __gp_is_append_only
gp_toolkit __gp_number_of_segments
gp_toolkit __gp_user_data_tables
gp_toolkit __gp_user_data_tables_readable
gp_toolkit __gp_user_namespaces
gp_toolkit __gp_user_tables
gp_toolkit gp_bloat_diag
gp_toolkit gp_bloat_expected_pages
gp_toolkit gp_locks_on_relation
gp_toolkit gp_locks_on_resqueue
gp_toolkit gp_log_command_timings
gp_toolkit gp_log_database
gp_toolkit gp_log_master_concise
gp_toolkit gp_log_system
gp_toolkit gp_param_settings_seg_value_diffs
gp_toolkit gp_pgdatabase_invalid
gp_toolkit gp_resq_activity
gp_toolkit gp_resq_activity_by_queue
gp_toolkit gp_resq_priority_backend
gp_toolkit gp_resq_priority_statement
gp_toolkit gp_resq_role
gp_toolkit gp_resqueue_status
gp_toolkit gp_roles_assigned
gp_toolkit gp_size_of_all_table_indexes
gp_toolkit gp_size_of_database
gp_toolkit gp_size_of_index
gp_toolkit gp_size_of_partition_and_indexes_disk
gp_toolkit gp_size_of_schema_disk
gp_toolkit gp_size_of_table_and_indexes_disk
gp_toolkit gp_size_of_table_and_indexes_licensing
gp_toolkit gp_size_of_table_disk
gp_toolkit gp_size_of_table_uncompressed
gp_toolkit gp_skew_coefficients
gp_toolkit gp_skew_idle_fractions
gp_toolkit gp_stats_missing
gp_toolkit gp_table_indexes
information_schema _pg_foreign_data_wrappers
information_schema _pg_foreign_servers
information_schema _pg_user_mappings
information_schema administrable_role_authorizations
information_schema applicable_roles
information_schema attributes
information_schema check_constraint_routine_usage
information_schema check_constraints
information_schema column_domain_usage
information_schema column_privileges
information_schema column_udt_usage
information_schema columns
information_schema constraint_column_usage
information_schema constraint_table_usage
information_schema data_type_privileges
information_schema domain_constraints
information_schema domain_udt_usage
information_schema domains
information_schema element_types
information_schema enabled_roles
information_schema foreign_data_wrapper_options
information_schema foreign_data_wrappers
information_schema foreign_server_options
information_schema foreign_servers
information_schema information_schema_catalog_name
information_schema key_column_usage
information_schema parameters
information_schema referential_constraints
information_schema role_column_grants
information_schema role_routine_grants
information_schema role_table_grants
information_schema role_usage_grants
information_schema routine_privileges
information_schema routines
information_schema schemata
information_schema sequences
information_schema table_constraints
information_schema table_privileges
information_schema tables
information_schema triggered_update_columns
information_schema triggers
information_schema usage_privileges
information_schema user_mapping_options
information_schema user_mappings
information_schema view_column_usage
information_schema view_routine_usage
information_schema view_table_usage
information_schema views

112 tables found.

> exit
$

Sample Greenplum ODBC.ini

Here's my working Greenplum ODBC.ini (modified from the sample one that shipped with DataDirect) - note that I only needed to change 3 parameters:
- Database
- HostName
- PortNumber


[Greenplum]
Driver=/app/Progress/DataDirect/Connect64_for_ODBC_61/lib/ddgplm25.so
Description=DataDirect 6.1 Greenplum Wire Protocol
AlternateServers=
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=gpdemo
DefaultLongDataBuffLen=2048
EnableDescribeParam=0
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=192.168.1.65
InitializationString=
KeyPassword=
KeyStore=
KeyStorePassword=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=
MaxPoolSize=100
MinPoolSize=0
Password=
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
XMLDescribeType=-10

Logging ODBC Trace

You turn on the trace by specifying the parameter Trace=1 (0 to turn off):

[ODBC]
IANAAppCodePage=4
InstallDir=/app/Progress/DataDirect/Connect64_for_ODBC_61
Trace=0
TraceFile=odbctrace.out
TraceDll=/app/Progress/DataDirect/Connect64_for_ODBC_61/lib/ddtrc25.so

Greenplum ODBC Drivers

I'm testing Greenplum ODBC drivers from 2 different vendors:

1. Greenplum
2. DataDirect

Here are the results of my tests:
- The Greenplum drivers does not work with my application, only DataDirect works.
- The Greenplum driver comes with 2 types of Driver Manager
- DataDirect
- datadirect-52_64
- datadirect-51sp2_64
- datadirect-53sp2_64
- Unix ODBC v2.2.12

When I used the UnixODBC v2.2.12 I get the following error:
Error code -1: Native code 0
State IM005
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

When I used the Greenplum DataDirect drivers, I got the following error (depending on the version):
./odbctest: error while loading shared libraries: libddicu21.so: cannot open shared object file: No such file or directory
./odbctest: error while loading shared libraries: libddicu22.so: cannot open shared object file: No such file or directory

The above led me to believe that the DataDirect drivers bundled with my application is incompatible with the Greenplum ones.

Here's how to configure the Greenplum drivers:
1. Create a ocbc.ini
[Greenplum]
Description = PostgreSQL driver for Greenplum
Driver = /app/gp/greenplum-connectivity-4.1.1.0-build-4/drivers/odbc/psqlodbc-08.03.0400/unixodbc-2.2.12/psqlodbcw.so
Trace = 1
TraceFile=/app/gp/greenplum-connectivity-4.1.1.0-build-4/odbctrace.out
Debug=1
Database = gpdemo
Servername = 192.168.1.65
UserName = gpadmin
Password = password
Port = 5432
ReadOnly = No
RowVersioning = No
DisallowPremature = No
ShowSystemTables = Yes
ShowOidColumn = No
FakeOidIndex = No
useDeclareFetch = 1
Fetch = 4096
UpdatableCursors = No
Protocol = 7.4-1

2. Amend the parameters in the script greenplum_connectivity_path.sh (commented values are for reference)
GP_ODBC_DRIVER=psqlodbc-08.02.0500
GP_ODBC_DRIVER_MANAGER=datadirect-52_64
#GP_ODBC_DRIVER_MANAGER=datadirect-51sp2_64
#GP_ODBC_DRIVER_MANAGER=unixodbc-2.2.12
#GP_ODBC_DRIVER_MANAGER=datadirect-53sp2_64

#GP_ODBC_DRIVER=psqlodbc-08.04.0200
#GP_ODBC_DRIVER_MANAGER=unixodbc-2.2.12

#GP_ODBC_DRIVER=psqlodbc-08.03.0400
#GP_ODBC_DRIVER_MANAGER=unixodbc-2.2.12

#GP_ODBC_DRIVER=psqlodbc-08.02.0400
#GP_ODBC_DRIVER_MANAGER=datadirect-52_64

ODBCINI=/app/gp/greenplum-connectivity-4.1.1.0-build-4/odbc.ini
export ODBCINI

For DataDirect, it is quite simple:
1. Configure the sample odbc.ini in /app/Progress/DataDirect/Connect64_for_ODBC_61
2. Source the file odbc.sh

And then restart my application. (use odbctest to verify odbc.ini is working properly).

Remote connection to Greenplum

I've been testing out connectivity with Greenplum from my application via ODBC.

When connecting to Greenplum remotely, you will need to update the file pg_hba.conf found in the DB data directory.

So where is the data directory? If you are using the Demo Image by Greenplum, you can find its location in /home/gpadmin/gpsetup/gp_init_config_4.1

In this file, it says it is in /dbfast1 and /dbfast2:
ARRAY_NAME="Greenplum Database for Hadoop"
MACHINE_LIST_FILE=../hosts
SEG_PREFIX=gph
PORT_BASE=50000
declare -a DATA_DIRECTORY=(/dbfast1 /dbfast2)
MASTER_HOSTNAME=gp-single-host
MASTER_DIRECTORY=/dbfast1
MASTER_PORT=5432
TRUSTED_SHELL=ssh
CHECK_POINT_SEGMENTS=8
ENCODING=UNICODE

There may be multiple files:
[root@gp-single-host dbfast1]# find . -depth -name *hba* -print
./gp-1/pg_hba.conf
./gp0/pg_hba.conf
./gph-1/pg_hba.conf
./gph0/pg_hba.conf

Add the following entries in the file:
host all all 192.168.1.65/32 trust
host all all 192.168.1.85/32 trust

Remember to stop and start your Greenplum database and you should be able to connect now.