SAS implicit Pass trhough

[Saranya_Sub]

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

[Daniel-Santos]

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

[Saranya_Sub]

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

[Daniel-Santos]

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

[Doc_Duke]

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.

[Saranya_Sub]

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?

[LinusH · Accepted solution]

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).