Datetime() update and insert error in teradata

[Hercules]

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.

[SASKiwi]

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?

[Hercules]

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

[Patrick]

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.

[jakarman]

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?

[Hercules]

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.

[Hercules]

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?

[Tom · Accepted solution]

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 ;

[Hercules]

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.

[Tom]

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;

[Hercules]

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.

[Tom]

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'

[Hercules]

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.

[jakarman]

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.

[jakarman]

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.