BookmarkSubscribeRSS Feed
reginado
Fluorite | Level 6

 

Hi,

Do anybody checked if you use SAS/ACCESS® Interface to Teradata with Teradata 16.10?

Thanks,

RD

16 REPLIES 16
kiranv_
Rhodochrosite | Level 12

I have not. I use Teradata 15 with SAS. Did you face any issues

reginado
Fluorite | Level 6

I was interested if anyone tested it, if I should expect issues/changes..

Will test it on Monday, will let you know if any changes.

kiranv_
Rhodochrosite | Level 12

we faced one issue when moving from 14 to 15. Please check the topic

Impact of ROW_NUMBER in Teradata 15 versus Teradata 14 on SAS tables in below article

 

http://www.mwsug.org/proceedings/2016/SA/MWSUG-2016-SA11.pdf

 

kiranv_
Rhodochrosite | Level 12

Hi @reginado, did you find any thing exciting/unusual in your tests.

reginado
Fluorite | Level 6
Hi, I tested the connectivity form SAS to TD and all went fine. For now no additional test were done.
Thanks, RD
nbonda
Obsidian | Level 7

We are in the process of testing loading SAS dataset into Teradata using the proc append procedure with fastload and multistmt option and both arefailing.

the code that works fine with TTU 15.0 is not working on TTU 15.10 and 16.10. 

kiranv_
Rhodochrosite | Level 12

what is the error.

nbonda
Obsidian | Level 7

I am using below script.

LIBNAME UTIL TERADATA USER="XXX" PASSWORD="XXY" SERVER=SERVER_NAME SCHEMA=DB_NAME 

                                                UTILCONN_TRANSIENT=YES;

proc append data=work.sample

                     base=util.td_tablename (MULTISTMT=YES) FORCE;

 

 

I am getting below error:

 

ERROR: teradata connection: the column SEQ is not found in 'td_tablename_RS' table. correct error and restart with option TPT_RESTART=YES.

 

I got above error on a test server that has ttu 16.10. but same script run perfectly fine on ttu 15.0.

 

Anybody have similar issue with ttu 15.10 or above. I don't understand what changing in new versions after ttu 15.0.

kiranv_
Rhodochrosite | Level 12

 

 

I have never used multistmt  and have no idea on this. @JBailey  who is an expert and has written quite a few papers on this kind of topic might be able to help us.

 

 i would do following too. 

First I would check the DDL of your base table and data table and check whether they are same or not in both the versions.

I would also try fastload and multiload whether they are working or not

 

proc append data=work.sample

                     base=util.td_tablename_new (fastload=YES) FORCE;

 

 

 

JBailey
Barite | Level 11

Hi @kiranv_,

 

You are way too kind.

 

Best wishes,

Jeff

JBailey
Barite | Level 11

Hi @nbonda,

 

Are you loading into an existing Teradata table or are you creating the table during the load?

 

Best wishes,

Jeff

JBailey
Barite | Level 11

Hi @nbonda

 

Is there any chance that there is an old restart table hanging around? It could be that  'td_tablename_RS' is referring to a different version of the table - one without the column SEQ in it. If there is a spurious restart table you may want to DROP it. Or you can point it to a different table name by specifying the TPT_LOG_TABLE= option.

 

There is more information here: https://support.sas.com/resources/papers/proceedings11/142-2011.pdf

 

Best wishes,
Jeff

nbonda
Obsidian | Level 7

We tested with TPT=NO option in Fastload (bulkload) and multi statement(multistmt). It's working fine . SAS admins also suggested same.

 

JBailey
Barite | Level 11

Hi @nbonda

 

This type of error is typically caused by resubmitting a TPT job when a previous job has failed (failed jobs create the *_RS tables). The initial job leaves a Restart Table (_RS) that has a different layout (columns are different) than the one being currently run. For example: Job 2 includes a SEQ column that was not present in Job 1. This is why SAS (really TPT) is complaining that there is no SEQ column. This could be because TPT has added columns to the restart table (_RS). 

 

The solution is to DROP the existing Restart Table (_RS) and rerun the job.

 

TPT=NO FASTLOAD uses a SAS created loading mechanism. TPT=NO MULTISTMT does not create a Restart Table. That is why you aren't seeing the message. The real solution is to use TPT=YES and clean up the Restart Table processing. If you drop the Restart Table your code will work.

 

The best practice is to use TPT=YES for interacting with Teradata because Teradata views TPT as the future of load processing for their database. 

 

Best wishes,

Jeff 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 16 replies
  • 2720 views
  • 3 likes
  • 5 in conversation