Question & Answer
Question
How do I connect to NPS using R and RODBC?
Answer
For Release 4.6.2, you should have the following component versions:
| Component | Version |
| R | 2.91 |
| RODBC | 1.3-0 |
| OS | Ubuntu 9.04 32 bit |
| NPS | 4.6.2 (P-7) |
| ODBC driver | 4.6.2 (P-7) |
1. Install R and RODBC from binaries. No custom compiling with additional flags is necessary.
2. Install the NPS ODBC driver (32-bit) and configure it as follows:
From /etc/odbc.ini:
- [ODBC Data Sources]
NZSQL = NetezzaSQL
[NZSQL]
Driver = /usr/local/nz/lib/libnzodbc.so
Description = NetezzaSQL ODBC
Servername = my_nps_server
Port = 5480
Database = system
Username = admin
Password = password
ReadOnly = false
ShowSystemTables = false
LegacySQLTables = false
LoginTimeout = 0
QueryTimeout = 0
DateFormat = 1
NumericAsChar = false
SQLBitOneZero = false
StripCRLF = false
[ODBC]
IANAAppCodePage=4
InstallDir=/opt/odbc32v51
Trace=0
TraceDll=/opt/odbc32v51/lib/odbctrac.so
TraceFile=odbctrace.out
UseCursorLib=0
From /etc/odbcinst.ini:
- [ODBC Drivers]
NetezzaSQL = Installed
[NetezzaSQL]
Driver = /usr/local/nz/lib/libnzodbc.so
Setup = /usr/local/nz/lib/libnzodbc.so
APILevel = 1
ConnectFunctions = YYN
Description = Netezza ODBC driver
DriverODBCVer = 03.51
DebugLogging = false
LogPath = /tmp
UnicodeTranslationOption = utf8
CharacterTranslationOption = all
PreFetch = 256
Socket = 16384
3. Launch R and use the following syntax to retrieve a list of users on the system:
- > library(RODBC)
> channel <- odbcConnect("NZSQL",uid="admin",pwd="password")
> channel <- odbcConnect("NZSQL")
> sqlQuery(channel, paste("select usename, usesysid from _t_user",
+ "where usesysid > 540 order by usename limit 10"))
usename usesysid
1 NSCBLO 561
2 akalayag 570
3 ageno 543
4 aguur 571
5 ahiller 541
6 aranthal 581
7 bright 550
8 bmacfurlane 572
9 bpine 569
10 btyzska 546
> close(channel)
[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":null,"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Historical Number
NZ342555
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21575298