Wednesday, November 30, 2016

database link between Vertica/non-Vertica database

Summarize some research about database link  between Vertica/non-Vertica database


As of now, DB links feature in not present in Vertica. We can not create Vertica database links like those that exist in Oracle and SQL Server. Moreover, we cannot run two databases simultaneously at one time in Vertica cluster.

But we can easily connect to another Vertica database if we need to to copy data between two Vertica databases. See the SQL Reference Manual:
CONNECT TO VERTICA database USER username PASSWORD 'password' ON 'host',port

For copy ORACLE data to Vertica, once can uses a unix shell script. Use COPY command in Vertica. In a shell script, pipe the output from oracle to Vertica in COPY command ( using input as STDIN) and then run the script. This shell script can also be used as STORED PROC in Vertica and running this Stored Proc from Vertica will pull the data from Oracle to Vertica.

For table join, if it requires join two tables in two Vertica databases, why not use two different schemas in same cluster/database  which can act as two different database.

Create Linkserver from SQL Server to Vertica via ODBC is possible, however there can have  performance concern.

Thursday, November 24, 2016

install 32bit instantclient ODBC for 64bit windows 2008 R2

One of my developer encountered similar issue in this nice article .

His creates Visual Basic form in MS Office Excel to pull data from Oracle database, while the Excel is 32 bits installed in his 64 bits windows 7 , hence requires 32 bits ODBC for this to work.















Launch 32bit ODBC configuration program (ODBC Data Source ) C:\Windows\SysWOW64, we can see there is "Microsoft ODBC for Oracle".  This is the same finding for his target depLoying server (windows 2008R2).  I was thinking no more 32bits  "Microsoft ODBC for Oracle" in windows 2008, which is wrong.


The server has 64 bits oracle client already installed for other application,

Luckily  I notice there is 32bit ODBC for 11g instantclient.  This is help to minimize the impact.


Follow the instantclient ODBC installation guide :
1. run odbc_install.exe as administrator in command prompt.
2. Append path of instantclient (e:\instantclient11203) to system environment variable PATH
3. create tnsnames.ora in e:\instantclient11203\netwoork\admin\

Missing part of the guide is : Launch 32bit ODBC configuration program (ODBC Data Source ) C:\Windows\SysWOW64\odbcad32.exe,

After this, my developer told me that his program can run suddenly.






Reference:

MS ODBC for Oracle

Oracle ODBC Centre