topic Re: Uploading data to a Permanent table by means of Fast Load. in SAS Enterprise Guide

Uploading data to a Permanent table by means of Fast Load.

cklau — Fri, 28 Sep 2012 18:00:51 GMT

I recently requested a Permanent Table as a location to upload Member_Keys to quickly pull Member information from other databases.

The Permanent Table was given to me as "DMGMT".

However, after being provided the requested table, I'm not sure how to upload the MBR_KEY's to the empty permanent table.

For now, my rough code is listed below:

%let userid=XXXXXXX;

%let passtera=XXXXXX;

%let TDPID=tdpid;

OPTIONS NOSYMBOLGEN;

LIBNAME TERALIB TERADATA USER=&userid. PASSWORD:&passtera. DATABASE=USERDATA_ENT_REPL MODE=teradata

LOGDB=USER_UTLTY_ENT TDPID=&tdpid. CONNECTION=global DBMSTEMP=yes SCHEMA=EDW_NOHAPHI TPT=NO;

proc sql exec feedback stimer;

create table class_2 as select * from ccare.sample;

quit;

proc append data=class_2 base=Teralib.DMGMT (FASTLOAD=yes);

run;

Thanks!

Re: Uploading data to a Permanent table by means of Fast Load.

Patrick — Sat, 29 Sep 2012 03:27:28 GMT

What exactly is your question? Is your code throwing an error?

You sure know this link with all the sample code http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001405937.htm

Why do you need the SQL statement where you're doing nothing than copying a table? Can't you just use the source table directly in your Proc Append statement?

proc append data=ccare.sample base=Teralib.DMGMT (FASTLOAD=yes);

run;

Also: Make sure that your source table only contains the variables which you actually need to load into the target teradata table. So may be your statement needs to look like

proc append data=ccare.sample(keep=MBR_KEY) base=Teralib.DMGMT (FASTLOAD=yes);

run;

And then: If you need to re-fresh your table make sure you always first truncate it using some SQL pass through statement like "delete from USERDATA_ENT_REPL.DMGMT all;"

Re: Uploading data to a Permanent table by means of Fast Load.

cklau — Mon, 01 Oct 2012 22:17:59 GMT

Thank you for the link. I'm not sure I'm able to utilize the codes on it just yet. I feel confused in general about what parts I need to change in the current code. I'm not even sure where the Permanent table they gave me is located....

I have just changed my code to include your suggested code:

proc append data=ccare.sample(keep=MBR_KEY) base=Teralib.DMGMT (FASTLOAD=yes);

run;

I do get errors when I run my code.

ERROR: Teradata connection: The user does not have CREATE TABLE access to database EDW_NOHAPHI. Correct error and restart as an

APPEND process with option TPT_RESTART=YES. Since no checkpoints were taken, if the previous run used FIRSTOBS=n, use the

same value in the restart.

I have just changed my password. Yet, I should have access to that database. What do you recommend?

Re: Uploading data to a Permanent table by means of Fast Load.

Patrick — Tue, 02 Oct 2012 01:04:21 GMT

Didn't you write that the Teradata table has been created for you? If so then you would NOT need "create" access - you only need read, update and delete.

Proc Append will try and create the target table for you if it doesn't exist (but only then).

Best practice is to create Teradata tables using native Teradata SQL code. Also best practice is to load tables indirectly via a view (but that's may be not required in your case).

Do you need to create the table - or is this something which will be done for you?

Message was edited by: Patrick Matter added the NOT to my post

Re: Uploading data to a Permanent table by means of Fast Load.

cklau — Tue, 02 Oct 2012 17:11:09 GMT

The Teradata Team at my company created the Permanent table for me, supposedly. I actually don't know where that table is located on the server either... which I feel is a problem as well. I'm still quite a beginner at SAS programing and I don't feel adequate to alter the code... I'm lost at times on what to replace, and where...

When I asked the same Teradata Team for a base code to use, they sent me the code below... I took it and tweaked it, but no matter what add ons they suggested, my code still has errors. So I'm at a point where I feel quite lost. I almost want to abandon my attempt at this... yet I'll soon have 60,000+ Member_Key's that I'll need to pull claims data for. This is the purpose that I want the permanent table up and running. I have no clue another work around to this... if there is another solution besides using a permanent table, please let me know.

Base Code from the Teradata Team:

This is what we did for another project.

%let userid=yourid;

%let passtera=yourpass;

%let edlsrvr=DWTEST2; ** wlpedl for production **;

%let edldb= dbname;

libname edluat teradata user=&userid. password=&passtera. TDPID=&edlsrvr. SCHEMA=&edldb.;

proc sql exec feedback stimer;

create table edluat.ANY_NAME_YOU_WANT(FASTLOAD=yes) as select * from Your_SAS_table;

quit;

Re: Uploading data to a Permanent table by means of Fast Load.

Tom — Tue, 02 Oct 2012 17:37:48 GMT

That SAS code will create a new table, specifically the "CREATE TABLE" command will try to make a new table. So you will need to have proper access to create the new table. I have found it more natural for me to use PROC APPEND to add data to an existing table, but I am a SAS programmer and most SQL still seems strange to me.

proc append base= edluat.ANY_NAME_YOU_WANT(FASTLOAD=yes) data=Your_SAS_table;

run;

If you want to use FASTLOAD then the table either has to be empty or non-existent.

You can get more information in your SAS log by setting these options:

option nostsuffix sastrace=',,,ds' sastraceloc=saslog ;

The d in the sastrace option says to show the database commands that SAS generates. The s says to show timing information. The other two say to put the messages into the SAS log and not to append the lines with extra tagging that makes them harder for humans to read.

Re: Uploading data to a Permanent table by means of Fast Load.

TomKari — Tue, 02 Oct 2012 20:30:44 GMT

The SQL equivalent to what Tom is suggesting is to truncate the table, and then load it. This would probably have to be done using pass-through code.

I agree with Tom that the code you were given creates the table, and therefore you problably can't do it.

Re: Uploading data to a Permanent table by means of Fast Load.

cklau — Tue, 02 Oct 2012 20:39:48 GMT

My recent code is as follows:

OPTIONS NOSYMBOLGEN;

LIBNAME TERALIB TERADATA USER=&userid. PASSWORD=&passtera. mode=teradata

TDPID=&TDPID. CONNECTION=GLOBAL;

OPTIONS SYMBOLGEN;

proc append data=ccare.sample(keep=MBR_KEY) base=Teralib.DMGMT (FASTLOAD=yes);

run;

Yet I get the following error:

ERROR: Error attempting to CREATE a DBMS table. ERROR: Teradata execute: The user does not have CREATE TABLE access to database EDL_NOHAPHI.

However, the Teradata Team told me that they created a Permanent table for me to upload data to... DMGMT.

The reason why I need them to create the empty-space table is b/c I do not have write access to the Teradata environment normally. Or, that's at least what I'm assuming happens.

If I dont have the access to create a table... are there any other ways?

Basically, my project requires the pulling of data for 60,000 records. I imagine that this will take a long time to do if I dont have a method that can join the Member_Key's directly to the data. I'm sorry for all this trouble, and I appreciate all of your willingness to help w/ suggestions...

Re: Uploading data to a Permanent table by means of Fast Load.

TomKari — Tue, 02 Oct 2012 22:48:31 GMT

Your code looks correct to me. Here's a link to the SAS documentation for optimizing Teradata loads:

http://support.sas.com/documentation/cdl/en/acreldb/65247/HTML/default/viewer.htm#n0ht8i7t92tocpn18vn0krftm85m.htm

It's Greek to me, but one thing is says is that the target dataset must be empty.

Can I suggest that you try the same thing, but without the FASTLOAD=yes dataset option. Trim your source file to around 5,000 records, that should run quite quickly. At least then you'll be able to see if it relates to FASTLOAD, or something else. I have done this successfully using other DBMS products, and 60,000 records shouldn't be terrible even without optimized loading.

Tom

Re: Uploading data to a Permanent table by means of Fast Load.

Patrick — Wed, 03 Oct 2012 09:37:47 GMT

If proc append tries to create a table then the table obviously does not exist. Else Proc Append would not try to create the table for you. Are you 100% sure that you're connecting to the right database?

Tom's suggestion to add

option nostsuffix sastrace=',,,ds' sastraceloc=saslog ;

is a very good one as it will show you in the log what SQL code SAS actually generates and sends to Teradata for execution. Copying this code and send it to the Teradata DBA would be a good start to get the issue resolved.

But again: If using Proc Append creates a SQL Create statement then SAS thinks at this moment the table doesn't exist at all. May be the table has been created for you but there are some grants (permissions) missing for the user you're using for connecting to Teradata.

Re: Uploading data to a Permanent table by means of Fast Load.

cklau — Wed, 03 Oct 2012 20:11:16 GMT

Hi Everyone! Thanks for your help! It actually works now! The issue that I had to resolved dealt with my company swtiching over to using a different user_ID connection along with our outlook passwords to connect to the system. The final code that worked is as follows:

option obs=max;

option compress=yes;

option nostsuffix sastrace=',,,ds' sastraceloc=saslog ;

%let TDPID=DWPROD2; **wlpedl for production**;

LIBNAME TERALIB TERADATA USER='MY_USER_ID@LDAP' PASSWORD='OUTLOOK PASSWORD'

/*database=USERDATA_ENT_REPL */

mode=teradata logdb=USER_UTLTY_ENT TDPID=&TDPID.

CONNECTION=GLOBAL DBMSTEMP=YES TPT=NO;

proc append data=ccare.sample(keep=MBR_KEY) base=Teralib.DMGMT (FASTLOAD=yes);

run;

***Note: DMGMT was the name of the Permanent table that the Teradata Team had created for me in the Teradata environment.

The next question would be, if I wanted to change the data that I've uploaded, must I delete ALL values in the Permanent table in order to upload new values into the table.

Re: Uploading data to a Permanent table by means of Fast Load.

Patrick — Thu, 04 Oct 2012 03:21:32 GMT

If you want to use fastload then yes, the table must be empty.

Re: Uploading data to a Permanent table by means of Fast Load.

cklau — Thu, 04 Oct 2012 21:31:23 GMT

A coworker also helped me discover how to delete the permanent table. Yay! It works!

PROC SQL EXEC FEEDBACK STIMER;

CONNECT TO TERADATA AS TD1 (USER='MY_USER_ID@LDAP' PASSWORD='OUTLOOK PASSWORD'

TDPID=DWPROD2 MODE=TERADATA CONNECTION=GLOBAL);

EXECUTE(

delete from DMGMT

) BY TD1

;

quit;