How to connect to Netezza database

[mike456]

I am trying to use DIAdem instead of Aginity to connect to Netezza database.  I have tried the following code, but cannot get a successful connection. Any help is appreciated.

ConnectionStr = "Driver={NetezzaSQL};server=______;UserName=______;Password=______;Database=______;LoginTimeout=120"

Set ADO = CreateObject("ADODB.Connection")     'I have tired a few options here including "NZSQL.Connection"

ADO.Open ConnectionStr

[AndreasK]

The following code can be used to use the MS ADO dialog to determine the connection string.

Option Explicit 

Call ADOConStrGet()
dim conStr : constr = ADOConString
if "" <> conStr then
  dim conFile : conFile = TmpDrv & "constr.txt"
  FileWriteLn conFile, 0, conStr
  MsgBox "Result in: '" &  conFile & "'" & VBCRLF & conStr
end if

The dialog contains a test button where you can play till it works.

Hope this helps.

[mike456]

This was helpful, but it does not seem to see the Netezza connection. 

[AndreasK]

I have no idea about Netezza but if it has an odsbc driver and is installed on your machine as an system dsn

DSN=NZSQL;Uid=myUsername;Pwd=;

should be sufficient. Maybe you need to add the driver.

Driver=NetzzaSQL;DSN=NZSQL;Uid=myUsername;Pwd=;

The name of the driver should be pickable on the first Tab Provider.

Maybe it will show up afterards. Aktually you are only seeing those of the ODBC provider.

Please visit the Provider Tab and check if you can switch the driver to Netezza.

[mike456]

This is still an open question.  I can connect using Tableau Desktop software, but cannot get DIAdem to connect to IBM PDA (Netezza)

[AndreasK]

How does the Provider Tab look like?

[mike456]

[AndreasK]

I have no idea how this works out. There seems to be no special driver.

"Provider=IBMDADB2;DSN=NZSQL;User Id=;Password=;"

is someting that can be found on the IBM pages for DB2.

All of this is just guessing. The connection string is indivual to the target database.

The dialog showing up with

ADOConStrGet()

is a native MS ADO dialog. So it should be possible to configure the connection there and validate it by using the test button, but I have no clou what to fill in for your database.

Andreas

[mike456]

Under my ODBC Data Source Administrator --> System DSN tab.  Name = "NZSQL", Driver = "Netezza SQL" shows separately from the IBM DB2 ODBC Drivers.  I believe that Netezza is different from DB2.  I can connect with Tableau Desktop and R Studio.  It looks like DIAdem is not recognizing the driver....

[AndreasK]

What error do you get if you use

DSN=NZSQL;Uid=myUsername;Pwd=;

without giving the driver at all?

P.S.: There is no implementation at this point. VBS just uses Microsoft ADO (Com Component) to connect to the databases. Like you see in the dialog the Driver is nt registered in ADO. Could you ask the person setting up the database for the connection string.