topic No multiload access in teradata in SAS Procedures

No multiload access in teradata

Hercules — Wed, 31 Dec 2014 07:32:37 GMT

Hello,

I have been trying to load my data in teradata selecting load technique as append multiload.

But it gives me error as

Insufficient authorization to access /sasconf/Lev1/SASApp/BL_2090353243819822617.dat

I have all the authorization but still getting this error only in multiload, but not in fast load.

Also when I go to the path, no such file exist.

Re: No multiload access in teradata

LinusH — Wed, 31 Dec 2014 11:26:24 GMT

It sounds like that your OS account that executes the SAS process doesn't have write permissions in the /Lev1 /SASApp / directory.TThere are several DS options the help you specify name/location for the files use during load.

Changing the current working directory might also work.

Re: No multiload access in teradata

jakarman — Wed, 31 Dec 2014 11:46:50 GMT

Your error is a normal issue of a failing design by SAS (current working dir).
There is a SAS-note on that, see: 50345 - Changing the current working directory for the SAS® Workspace Server

As you are using Unix the net issue is the "/home" filesystem. Commonly Unix admins are having problems to support users using Unix.

With SAS you are using Unix as normally needing access to handle files that is common to Windows. Unless that "/home/<dept>/<key>" is configured to do that like happy users with Windows you can expect that intermediate BL_? .dat file giving issues on space limitations.

Review the mulitload settings SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition the temp tables can be set dedicate SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Re: No multiload access in teradata

Harry — Wed, 31 Dec 2014 22:49:28 GMT

I've gotten this error when I've attempted to fastload into a Teradata table that is NOT empty. In the Teradata Loader pre-code, make sure you're deleting all rows from the Teradata table.

Re: No multiload access in teradata

jakarman — Wed, 31 Dec 2014 23:46:56 GMT

Harry you are correct in that the fastload is requiring an empty table in teradata.

The multiload however does not have that requirement.

Another different behavior can be that of errors and duplicate records.
Teradata is using error tables not the usual returncode. That makes sense when loading many records and a small amount are in error,

Re: No multiload access in teradata

Harry — Thu, 01 Jan 2015 00:04:00 GMT

Correct, and your comment makes things much more clear. Looking at the code snippet below, if the Teradata Loader detects that the destination table is empty, it uses the FASTLOAD. If data does exist, the MULTILOAD is used. In my case, the MULTILOAD generated duplicate primary index entries. Because I didn't have an error table defined in the ML_ERROR2 parameter, the loader apparently tries to generate the BL....dat file in the default home directory for the Workspace server, and of course I don't have write permission there, hence the authorization error. Makes complete sense now.

Thanks and happy new year.

732 %if &etls_recnt=0

733 %then %do;

734 %put %str(NOTE: Appending data ...);

735

736 proc append base = MITSTAGE.ODS_T_PHYSICAL_DEVICE_TrueUp(DBCREATE_TABLE_OPTS = 'unique primary index

736 ! PHYSICAL_DEVICE_PK ("CUSTOMER_ID","ESN_SEQ_NO","PRODUCT_TYPE","SUBSCRIBER_NO","UNIT_ESN") '

737 &TDFLOADOPTS sessions = 4

738 TPT=NO FASTLOAD = YES

739 BL_LOG = &_errTable )

740 data = &etls_lastTable (&etls_tableOptions) force;

741 run;

742

743 %rcSet(&syserr);

744

745 %end;

746 %else %do;

747 proc append base = MITSTAGE.ODS_T_PHYSICAL_DEVICE_TrueUp(DBCREATE_TABLE_OPTS = 'unique primary index

747 ! PHYSICAL_DEVICE_PK ("CUSTOMER_ID","ESN_SEQ_NO","PRODUCT_TYPE","SUBSCRIBER_NO","UNIT_ESN") '

748 &TDFLOADOPTS sessions = 4

749 TPT=NO MULTILOAD = YES )

750 data = &etls_lastTable (&etls_tableOptions) force;

751 run;

Re: No multiload access in teradata

Hercules — Thu, 01 Jan 2015 03:28:37 GMT

That worked big time Jaap, thanks a lot and happy new year.