Compatibility with Teradata 16.10

[reginado]

Hi,

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

Thanks,

RD

[kiranv_]

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

[reginado]

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_]

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_]

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

[reginado]

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

[nbonda]

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_]

what is the error.

[nbonda]

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_]

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]

Hi @kiranv_,

You are way too kind.

Best wishes,

Jeff

[JBailey]

Hi @nbonda,

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

Best wishes,

Jeff

[JBailey]

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]

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

[JBailey]

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