topic Datetime() update and insert error in teradata in SAS Procedures

Datetime() update and insert error in teradata

Hercules — Thu, 27 Nov 2014 17:55:12 GMT

I have to update a field value with current date and time in teradata table.

I am using

Update library.table_name set column = datetime() ;

Inside proc sql.

It is showing me error, cursor pointer in teradata mode is not allowed.

When I'm trying to update current time by pass through statements in teradata using,

Connect to teradata ( credentials) ;

Select * from Conn to teradata ;

(Update schema.table_name set column = current_timestamp(0)) ;

Then it's working fine but showing in logs that no rows were selected.

I only can select the tables I. E. View the tables by passing through but neither can update nor can insert or create table by passing through

I simply cannot understand.

Kindly let me know a solution and an explanation why this is happening if anyone else has encountered the same problem ever.

Thank you.

Re: Datetime() update and insert error in teradata

SASKiwi — Thu, 27 Nov 2014 19:37:56 GMT

With problems like these a posting your SAS log showing your complete PROC SQL step, the errors reported, plus any Teradata options used would be helpful. Providing code snippets does not give the full picture.

As a first step does your userid have update permission on the table you are trying to update?

Re: Datetime() update and insert error in teradata

Patrick — Thu, 27 Nov 2014 20:33:14 GMT

With implicit SQL you want to use "OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX; " before the SQL so that you can see in the log what data base SQL has actually been sent to the data base. This should then help to identify what's going wrong.

Re: Datetime() update and insert error in teradata

jakarman — Thu, 27 Nov 2014 21:52:42 GMT

At first sight (waiting for the real code) you are using implicit sql to Teradata. That is a bad idea for several reasons.

The tables are to be expected into big sizing type. Getting the data to SAS and back is not a smart approach when you would get it to work. (the select *)

The cursor message is telling you have done something that would be acceptable in a OLTP approach with small data sizing in a transaction.

The explit pass through using execute statement with Teradata SQL should do the job without any problem in the case you would update the table (why select * ?)

May be a Teradata view creation (replace view) storing that at Teradata could do the job you are needing.

Not sure what you are trying to achieve.

What do you want to do?

Re: Datetime() update and insert error in teradata

Hercules — Fri, 28 Nov 2014 01:47:38 GMT

Thanks for the kind feedback.

Suppose I have a table xyz in teradata with column

Job_id, status and end_time.

At first when a job starts, the precode sends the job_id, Start_time I. E. "&etls_startTime"dt and status as inprogress

After the job executed I want to update the end time of that job using current datetime as" & etls_endTime" is working because it is captured after the precode has run.

This is what I want to achieve, but I don't want use status Handeling due to some reason.

Re: Datetime() update and insert error in teradata

Hercules — Fri, 28 Nov 2014 01:52:21 GMT

But I can update or insert or create the table if I do not pass through into teradata

Re: Datetime() update and insert error in teradata

Tom — Fri, 28 Nov 2014 02:04:54 GMT

When you just want to run code in remote data base use EXECUTE statement instead of trying to use a SELECT statement to execute a statement. Also depending on the connection mode to teradata you might need to also execute a COMMIT statement.

execute ( update database.table_name set column = current_timestamp(0) where id=xxxx ) by teradata ;

execute ( commit ) by teradata ;

Re: Datetime() update and insert error in teradata

Hercules — Fri, 28 Nov 2014 05:03:34 GMT

That worked!!!

Thank you so much Tom.

But I am nort able to insert current timestamp using sas function datetime() if I am not passing through.

by using,

proc sql;

insert into tera.table_name set column = datetime();

quit;

This is throwing me error:

CURSOR PROCESSING IN TERADATA MODE IS NOT ALLOWED.

Re: Datetime() update and insert error in teradata

Hercules — Fri, 28 Nov 2014 05:05:37 GMT

If I simply want to insert current datetime in teradata table by simply using,

proc sql;

update tera.table_name set column = datetime();

quit;

This is throwing me error:

CURSOR PROCESSING IN TERADATA MODE IS NOT ALLOWED.

Can you suggest how to do it?

Re: Datetime() update and insert error in teradata

jakarman — Fri, 28 Nov 2014 10:58:51 GMT

Did you have followed the Teradata skill sets training by SAS?

Chapter 6 Creating, Updating and Loading Teradata Tables from SAS 6-1

6.1 Creating and loading Teradata tables from SAS 6-3

6.2 Loading data into Teradata leveraging Teradata Load Utilities from SAS 6-24

6.3 Updating Teradata tables from SAS (optional) 6-44

You need the set several options for Teradata access and do that in some structured way.

Set bulkload=yes and define you primary-index sensible or set no-primary index. Teradata is parallel processing on a lot of node and does the spread by some key derived from that primary index.

When you are getting a note on cursor usage you are torturing yourself and Teradata as that is OLPT access not the method where Teradata is designed for.

Re: Datetime() update and insert error in teradata

Tom — Fri, 28 Nov 2014 15:49:02 GMT

What does that statement even mean?

Make a table in SAS with the record you want to insert and then insert it. That way SAS is just moving data and not a function call.

data temp;

column=datetime();

run;

proc append data=temp base=tera.table ;

run;

Re: Datetime() update and insert error in teradata

jakarman — Fri, 28 Nov 2014 17:56:20 GMT

Review the sas/access docs when not having the SAS Teradata skills, SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition (Maximizing Teradata Load Performance)

The sample with using append is there and showing the dedicated needed options for that step. The one for avoiding skewness with primary index will also be somewhere.

Teradata is behaving different as of his massive parallel design optimized for teradata sizes.

Re: Datetime() update and insert error in teradata

Hercules — Sat, 29 Nov 2014 04:55:59 GMT

I did that earlier Tom what you are suggesting now.

Data test;

A=datetime() ;

Run;

Proc sql;

Update. Tera.table set column=(select a from work.Test) ;

And still not working. Same error CURSOR PROCESSING IS NOT ALLOWED IN TERADATA MODE.

It does work on an insert statement but not in update statement.

Re: Datetime() update and insert error in teradata

Hercules — Sat, 29 Nov 2014 04:57:23 GMT

No I haven't Jaap, I'm just 3 weeks new to SAS and I'm learning on my own. That's why I haven't read those chapters which you are referring. But I'll surely gona read them now.

Re: Datetime() update and insert error in teradata

Tom — Sat, 29 Nov 2014 06:31:06 GMT

Syntax like :

Update Tera.table set column=(select a from work.Test) ;

Is probably not going to work very well since WORK.TEST is a SAS table and TERA.TABLE is a database table.

But If you really just have a single value then there is no need to reference another table.

Did you try just setting the value?

%let jobid=101;

proc sql noprint;

update tera.table set stop_dt = "%sysfunc(datetime(),datetime20)"dt

where jobid = &jobid

;

quit;

If you want to write pass-through SQL to run inside the database you can generate a datetime literal in Teradata using this format:

TIMESTAMP 'YYYY-MM-DD HH:MM:SS'

Re: Datetime() update and insert error in teradata

Hercules — Sat, 29 Nov 2014 07:04:30 GMT

I tried that also but that didn't worked either. Through pass through its working fine, again thanks to you, so for now I would go with it as an alternative method.

But I will look into it and share it with you also if it happens.

Also I would like you to have a look into my another query as well which is

May be you could help me with it as well. Would be really grateful.

Re: Datetime() update and insert error in teradata

jakarman — Sat, 29 Nov 2014 08:33:38 GMT

Teradata is different as to other databases. Amps PE bynet with nodes are dedicated words

Teradata Architecture » TeraData Tech Looks to me a nice start for that.

The better one is looking at Teradata.com http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1141_111A/ch04.106.05.html

That site is reacting weird sometimes.

You see the nodes and architecture behind. Distributed shared nothing.
That is why you cannot use the same naïve coding approaches like the basic OLTP ones. Using Teradata like an OLTP is a bad idea. Trying to do the inserts like an OLTP as bad.

You see something of that back in the SAS/Access documentation. Being aware what is different behind that at Teradata is more important.