topic Re: Compatibility with Teradata 16.10 in SAS Data Management

Compatibility with Teradata 16.10

reginado — Fri, 21 Jul 2017 14:12:10 GMT

Hi,

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

Thanks,

Re: Compatibility with Teradata 16.10

kiranv_ — Fri, 21 Jul 2017 14:15:34 GMT

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

Re: Compatibility with Teradata 16.10

reginado — Fri, 21 Jul 2017 14:59:20 GMT

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.

Re: Compatibility with Teradata 16.10

kiranv_ — Fri, 21 Jul 2017 15:33:48 GMT

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

Re: Compatibility with Teradata 16.10

kiranv_ — Sat, 05 Aug 2017 16:03:48 GMT

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

Re: Compatibility with Teradata 16.10

reginado — Mon, 07 Aug 2017 13:39:21 GMT

Hi, I tested the connectivity form SAS to TD and all went fine. For now no additional test were done.
Thanks, RD

Re: Compatibility with Teradata 16.10

nbonda — Sat, 23 Jun 2018 11:51:20 GMT

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.

Re: Compatibility with Teradata 16.10

kiranv_ — Sat, 23 Jun 2018 14:10:57 GMT

what is the error.

Re: Compatibility with Teradata 16.10

nbonda — Wed, 27 Jun 2018 04:19:52 GMT

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.

Re: Compatibility with Teradata 16.10

kiranv_ — Wed, 27 Jun 2018 12:48:38 GMT

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;

Re: Compatibility with Teradata 16.10

JBailey — Thu, 28 Jun 2018 18:34:35 GMT

Hi @kiranv_,

You are way too kind.

Best wishes,

Jeff

Re: Compatibility with Teradata 16.10

JBailey — Thu, 28 Jun 2018 18:35:51 GMT

Hi @nbonda,

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

Best wishes,

Jeff

Re: Compatibility with Teradata 16.10

JBailey — Thu, 28 Jun 2018 19:59:24 GMT

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

Re: Compatibility with Teradata 16.10

nbonda — Fri, 23 Nov 2018 20:33:21 GMT

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

Re: Compatibility with Teradata 16.10

JBailey — Fri, 22 Mar 2019 19:57:09 GMT

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

Re: Compatibility with Teradata 16.10

satish504 — Tue, 19 Mar 2019 12:57:02 GMT

Hi All,

Can anyone please let me know the steps from SAS side, when Teradata DBA upgrading database version from 15.10 to 16.20.

Thanks in advance.

Regards,

Satish

Re: Compatibility with Teradata 16.10

JBailey — Fri, 22 Mar 2019 17:44:53 GMT

Hi @satish504

Are you using SAS/ACCESS Interface to Teradata only? Or, are you using the SAS In-Database (SAS Embedded Process) products?

Best wishes,

Jeff