libname problem when connecting to netezza

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

libname problem when connecting to netezza

The following code does not work, giving me the message ERROR: DWH.TEMP2.DATA does not exist. This is the only error message.

 

 

OPTIONS REMOTE=unix_name COMAMID=TCP;
FILENAME RLINK 'H:\mysas\unix_log_in.txt';
signon;
rsubmit;
filename mysas "~/mysas" ; 

libname DW NETEZZA uid=userID
                   pwd=my_password
                   server=netezza_name

                   database=database1
                   PRESERVE_TAB_NAMES=YES
                   CONNECTION = GLOBAL
                   sqlgeneration=DBMS
                   BULKUNLOAD = YES
                   AUTOCOMMIT = YES  ;
libname DWH NETEZZA uid=userID
                    pwd=my_password
                    server=netezza_name

                    database=database2
                    PRESERVE_TAB_NAMES=YES
                    CONNECTION = GLOBAL
                    sqlgeneration=DBMS
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES  ;

proc sql;

connect to netezza (user     = userID
                    pwd      = my_password
                    server   = netezza_name
                    database = database1
                    CONNECTION = GLOBAL
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES ) ;
execute (
	create temporary table TEMP1 as
	select distinct Year
	from TABLE_NAME;
) by netezza;

create table temp1 as 
select * from DW.TEMP1;
disconnect from netezza;

connect to netezza (user     = userID
                    pwd      = my_password
                    server   = netezza_name
                    database = database2
                    CONNECTION = GLOBAL
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES ) ;

execute (
	create temporary table TEMP2 as
	select distinct Year)
	from TABLE_NAME;
) by netezza;

create table temp2 as 
select * from DWH.TEMP2;

quit;

endrsubmit;
signoff;

 

The following code does work:

OPTIONS REMOTE=unix_name COMAMID=TCP;
FILENAME RLINK 'H:\mysas\unix_log_in.txt';
signon;
rsubmit;
filename mysas "~/mysas" ; 

libname DW NETEZZA uid=userID
                   pwd=my_password
                   server=netezza_name

                   database=database1
                   PRESERVE_TAB_NAMES=YES
                   CONNECTION = GLOBAL
                   sqlgeneration=DBMS
                   BULKUNLOAD = YES
                   AUTOCOMMIT = YES  ;
proc sql;

connect to netezza (user     = userID
                    pwd      = my_password
                    server   = netezza_name
                    database = database1
                    CONNECTION = GLOBAL
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES ) ;

execute (
	create temporary table TEMP1 as
	select distinct Year
	from TABLE_NAME;
) by netezza;

create table temp1 as 
select * from DW.TEMP1;
disconnect from netezza;
libname DW clear;

libname DWH NETEZZA uid=userID
                    pwd=my_password
                    server=netezza_name

                    database=database2
                    PRESERVE_TAB_NAMES=YES
                    CONNECTION = GLOBAL
                    sqlgeneration=DBMS
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES  ;

connect to netezza (user     = userID
                    pwd      = my_password
                    server   = netezza_name
                    database = database2
                    CONNECTION = GLOBAL
                    BULKUNLOAD = YES
                    AUTOCOMMIT = YES ) ;

execute (
	create temporary table TEMP2 as
	select distinct Year)
	from TABLE_NAME;
) by netezza;

create table temp2 as 
select * from DWH.TEMP2;
quit;

endrsubmit;
signoff;

The second is just moving around the code from the first, and the only new line is the green libname statement after the disconnect statement. So some kind of name conflict is happening, but I have no idea what it is or why. The libraries DW and DWH should be pointing to different databases, so why is only the first connection working?

 


Accepted Solutions
Solution
‎03-17-2017 09:56 AM
Trusted Advisor
Posts: 1,459

Re: libname problem when connecting to netezza

It seems as SAS can connect to NETEZA, only one database at a time.

 

Did your second code, with the 

libname DW clear;

work? If yes - it may fit my theory.

View solution in original post


All Replies
Solution
‎03-17-2017 09:56 AM
Trusted Advisor
Posts: 1,459

Re: libname problem when connecting to netezza

It seems as SAS can connect to NETEZA, only one database at a time.

 

Did your second code, with the 

libname DW clear;

work? If yes - it may fit my theory.

New Contributor
Posts: 2

Re: libname problem when connecting to netezza

Yes, the second block of code works. But does the the libname statement actually establish a connection to the database, or is the connection only established when the connect to statement is invoked?

 

To complicate matters, in the second block of code, if I remove the "libname DW clear" statement, then it fails to work unless the second libname also uses "DW" instead of "DWH". I'm not certain whether that supports your theory or not.

Trusted Advisor
Posts: 1,459

Re: libname problem when connecting to netezza

It is interesting and chalenging.

 

LIBNAME syntax is documented as:

LIBNAME libref <engine> (library-specification-1 <. . . library-specification-n>) 

where libref is a symbolic reference name.

 

Would you try run first block with libref as:  DWH or LIB1 or any other valid name  ? Does it work ?

Is it working only for first sas session connection to NETEZZA ?

Is it working if you define first conncetion to database2 and second to database1 ? (swap order of databases)

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 195 views
  • 0 likes
  • 2 in conversation