SAS Data Load To Netezza Fails

bhavani — Thu, 27 Feb 2014 10:26:35 GMT

Hi,

I have this issue where in the data load to a Netezza table is failing. This is a new implementation that we are doing.

My Environment:

SAS 9.3

AIX

Netezza.

I have a table with 149 columns with mostly numeric values (132 to be precise). I have a script that uses NZLOAD lo load the netezza table.

Command:

nzload -host $enzeesrv -db $enzeedb -u $enzeeusr -pw $enzeepwd -df $path/$pipename -t $tablename -delim '|' -lf $path/$pipename.log -bf $path/$pipename.bad -MaxErrors 1 -crInString

I call this script from a SAS job. The Longest record length = 1500 and I am using LRECL=2500 (just to accomodate).

The load is failing citing the following reason in the log file. I am using PIPE '|' as delimited to load the UNIX pipe.

It is failing on the very 1st for the 103rd column whose value is a 0. As a matter of fact columns 33 toll 149 are all numeric (most values for these columns is 0).

bad #: input row #(byte offset to last char examined) [field #, declaration] diagnostic, "text consumed"[last char examined]

----------------------------------------------------------------------------------------------------------------------------

1: 1(255) [103, INT4] missing field - fillRecord not enabled, "0"[0x0a<LF>]

I tried to see if there were any Carriage Returns (CR) in the clumn, but could not.

I tried using the -fillRecord option in the nzload commad but to no success. May be I did not know how ot use it.

Also, what is more puzziling is how come nzload is writing only 103 column values to the .bad file and not the entire record?

Any suggestions on how to circumvent this problem????

Message was edited by: Bhavani kumar Metla

Re: SAS Data Load To Netezza Fails

DBailey — Thu, 27 Feb 2014 13:10:08 GMT

So a bit out of my area w/ Netezza...but I seem to remember something about SAS not writing consecutive delimiters for null values?

Re: SAS Data Load To Netezza Fails

bhavani — Thu, 27 Feb 2014 16:09:38 GMT

I tested this with only 10 rows. All of them have values, yet it is failing.

Re: SAS Data Load To Netezza Fails

AndrewZ — Fri, 02 Oct 2020 15:30:36 GMT

I had the same error about "missing field" when bulk loading from SAS 9.4 (Windows 10) to Netezza. In my case, there was a line break within a value, causing Netezza to interpret it as two short rows instead of one full row.

topic SAS Data Load To Netezza Fails in SAS Programming

SAS Data Load To Netezza Fails

Re: SAS Data Load To Netezza Fails

Re: SAS Data Load To Netezza Fails

Re: SAS Data Load To Netezza Fails