DATA Step, Macro, Functions and more

SAS implicit Pass trhough

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

SAS implicit Pass trhough

HI All, i wanted to move a table from oracle to teradata.The tables are vey huge in size more than 200 GB

i tried the below code

proc append base=tlib.LOADTEST2 (TPT=YES FASTLOAD=YES
TPT_MIN_SESSIONS=2
TPT_MAX_SESSIONS=6
TENACITY=1
SLEEP=5
TPT_ERROR_TABLE_1 = LOADTEST_ET
TPT_ERROR_TABLE_2 = LOADTEST_UV
TPT_LOG_TABLE = LOADTEST_RS
TPT_TRACE_LEVEL=2
TPT_TRACE_LEVEL_INF=12
TPT_TRACE_OUTPUT='C:\SAS_TPT_fastload.txt')
data=OLIB.Master_Oracle_Table(orhints='/*+ parallel(16)*/');
run;

 

tlib--> Teradata library (pre assigned it)

olib -->oracle library (pre assigned it)

 

Though its working,i couldnt fetch the table which has more than 250+ columns

when i run the code ,its says -No sufficient memory in SAS

 

Can any one let me know the reason for the same? any thing i have change in the code to avoid it


Accepted Solutions
Solution
‎01-11-2017 09:00 AM
Super User
Posts: 5,260

Re: SAS implicit Pass trhough

What SAS version are you using?

There have been reported memory problems for multi threaded procedures in 9.2.

 

Also, there is a reported memory problem for bulk-loading Greenplum, this might be similar...?

Adding

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

might give some information on what is going on in the SAS/ACCESS interface (try also with a smaller table so you can audit a non-error copy operation).

Data never sleeps

View solution in original post


All Replies
Contributor
Posts: 24

Re: SAS implicit Pass trhough

Hi.

 

Are you just using SAS for bridging between databases?

That wouldn't be efficient.

 

SAS would be downloading the data to a file (and formating it) and then uploading it to the destination.

 

If it's the case, why just not unload the table from ORACLE and load it from there to TERADATA?

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Occasional Contributor
Posts: 6

Re: SAS implicit Pass trhough

Yes we are just using SAS as bridge between two database.certain functionality of Teradata (TPT API) is not working in our server
Contributor
Posts: 24

Re: SAS implicit Pass trhough

 

Then it will be better to unload the data from Oracle and load it into Teradata with the database native utilities.

 

I wouldn't recomend bridging data as you might get problems with numeric precision error.

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Trusted Advisor
Posts: 2,113

Re: SAS implicit Pass trhough

It is very unusual for SAS to run out of memory in PROC APPEND; it reads a record and writes a record.  SAS is much more likely to run out of disk space. 

 

I'd suggest copying the exact error message (rather than the paraphrase you provided) and searching the knowledgebase at SAS.COM.  A Google search with

site:sas.com

at the end will sort through it pretty quickly.

Occasional Contributor
Posts: 6

Re: SAS implicit Pass trhough

i want to replace certain character(Example | to space) in the teradata table.is it possible to do that in the above append statement itself?
Solution
‎01-11-2017 09:00 AM
Super User
Posts: 5,260

Re: SAS implicit Pass trhough

What SAS version are you using?

There have been reported memory problems for multi threaded procedures in 9.2.

 

Also, there is a reported memory problem for bulk-loading Greenplum, this might be similar...?

Adding

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

might give some information on what is going on in the SAS/ACCESS interface (try also with a smaller table so you can audit a non-error copy operation).

Data never sleeps
☑ This topic is solved.

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

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