Help using Base SAS procedures

No multiload access in teradata

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

No multiload access in teradata

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.


Accepted Solutions
Solution
‎12-31-2014 06:46 AM
Valued Guide
Posts: 3,208

Re: No multiload access in teradata

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 
  

---->-- ja karman --<-----

View solution in original post


All Replies
Super User
Posts: 5,256

Re: No multiload access in teradata

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.

Data never sleeps
Solution
‎12-31-2014 06:46 AM
Valued Guide
Posts: 3,208

Re: No multiload access in teradata

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 
  

---->-- ja karman --<-----
Contributor
Posts: 72

Re: No multiload access in teradata

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

Occasional Contributor
Posts: 9

Re: No multiload access in teradata

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.

Valued Guide
Posts: 3,208

Re: No multiload access in teradata

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,  

---->-- ja karman --<-----
Occasional Contributor
Posts: 9

Re: No multiload access in teradata

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 744 views
  • 3 likes
  • 4 in conversation