Loading SAS To Netezza Communication Link Failure

[jim_toby]

I am trying to load a SAS table with 50 columns and over 34 million rows into netezza.

Here is my code:

LIBNAME ECLIB000 "\\path\to\SASTables";
libname sdf odbc noprompt="server=xxxx; DRIVER={NetezzaSQL}; port=xxxx; database=xxxxx; username=xxx; password=xxxx;";
proc sql;
create table sdf.myNewTable
(bulkload = YES)
AS SELECT * FROM ECLIB000.MySASTable;
quit;

When doing this, I get the error: Error terminating Netezza load: Communication link failure.

In this post: https://communities.sas.com/t5/SAS-Data-Management/Communication-link-failure-when-upload-table-to-N... I saw that someone else had the same issue, but I don't quite understand the answer or where to place the FORMAT statement in my code.

Can anyone help?

Thanks1

Thanks

[jim_toby]

I am trying to load a SAS table with 50 columns and over 34 million rows into netezza.

Here is my code:

LIBNAME ECLIB000 "\\path\to\SASTables";
libname sdf odbc noprompt="server=xxxx; DRIVER={NetezzaSQL}; port=xxxx; database=xxxxx; username=xxx; password=xxxx;";
proc sql;
create table sdf.myNewTable
(bulkload = YES)
AS SELECT * FROM ECLIB000.MySASTable;
quit;

When doing this, I get the error: Error terminating Netezza load: Communication link failure.

Can anyone help?

Thanks

[kiranv_]

Can you try distribute_on dataset option. Please pick a unique column for distribute_on. An example picked from sas communities is shown below. or use atleast distrubute_on = random. if you do not specify any column  for distrubute_on then datanase will pick a column and can lead to data skewing and probably making everything inefficient and leading to disconnect

proc sql;
create table netlib.customtab(DISTRIBUTE_ON='partno') 
   as select partno, customer, orderdat from saslib.orders;
quit;

/* for multiple columns*/

data netlib.mytab(DISTRIBUTE_ON='col1,col2');
col1=1;col2=12345;col4='mytest';col5=98.45;
run;

data netlib.foo(distribute_on=random);
mycol1=1;mycol2='test';
run;

[jim_toby]

@kiranv_ When I use distribute_on I get the error: Invalid option name DISTRIBUTE_ON

This is my new code:

LIBNAME ECLIB000 "\\path\to\SASTables";
libname sdf odbc noprompt="server=xxxx; DRIVER={NetezzaSQL}; port=xxxx; database=xxxxx; username=xxx; password=xxxx;";
proc sql;
create table sdf.myNewTable
(bulkload = YES distribute_on= 'firstColumn')
AS SELECT * FROM ECLIB000.MySASTable;
quit;

I even tried it without the bulkload and I get the same error. 

[kiranv_]

please remove quotes and then try

distribute_on= firstColumn

Please check this link it has various varitions  and above does not work please try the below. I work mostly with teradata that is why I am not well versed with netezza options but one of them should work

https://www.sas.com/partners/directory/ibm/NetezzaDWAppliances-withSAS.pdf

proc sql;
create table nzData.test (DBCREATE_TABLE_OPTS='DISTRIBUTE on (ID)'
BULKLOAD=YES)
 as (select * from SASDATA.SASFoo);
quit;

[jim_toby]

@kiranv_ I add the distribution and now I'm back to the original error: Error terminating Netezza load: Communication link failure

This is my code:

LIBNAME ECLIB000 "\\path\to\SasTable";
libname sdf odbc noprompt="xxx; DRIVER={NetezzaSQL}; port=xxx; database=xxx; username=xxx; password=xxxx;";
proc sql;
create table sdf.NewTable
(DBCREATE_TABLE_OPTS= 'distribute on (col1)' bulkload = YES)
AS SELECT * FROM ECLIB000.SASTable;
quit;

When I try to load a smaller table, it works fine. Is there something else I can do to help solve this issue?

[kiranv_ · Accepted solution]

Try one last thing. distrubute_on =random.

if does not work out this is not a preferable way.

check the total number of records in your SAS table. Say you have 1000 records in SAS records create several SAS views using data step

first view--if  _n_ ge 1 and le 250;

second view if _n_ ge 251 and le 500;

and so on and load one by 1 by 1 into your netezza table by using insert statement.

please contact your Netezza team admins, looks like you may time limit for your connectivity and discuss your problem. I have seen this happening when we used  load into oracle tables  from SAS. 

[jim_toby]

@kiranv_ so just to clarify, instead of splitting my current table into multiple tables and than loading these tables into  Netezza, it would be better to create multiple SAS views to load to Netezza? I'm asking because it seems like SAS Views has a slower processing time and I'm wondering if it will be much slower to load data from SAS views into netezza rather than form an actual data file.

[kiranv_]

it should not differ much. the reason I told to make views is that you may need more space in SAS with table. you have lot of space you can very well make tables and then drop them.