BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aberko01
Calcite | Level 5

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Shmuel
Garnet | Level 18

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

3 REPLIES 3
Shmuel
Garnet | Level 18

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.

aberko01
Calcite | Level 5

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.

Shmuel
Garnet | Level 18

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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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