Rows updated inserted

Database
Enthusiast

Rows updated inserted

Hi,

I have written one BTEQ script to insert, update and delete the table.
When ever a table gets updated, inserted or deleted, I want the number of rows gets affected.

I know, Activity_count variable is used to capture but, i want the count in unix variable or insert the activity count value in one dummy table.

Do any one have any suggestion on this.

If question is not clear do let me know.

Thanks in advance.
9 REPLIES
Junior Contributor

Re: Rows updated inserted

Hi Monika,
there are several possible solutions:

1. Parse the output for " *** Query completet." using grep and use awk to extract the count
2. Write a NOTIFY exit and use it before each DML statement
3. Rewrite it to a Stored Procdure and use it's ACTIVITY_COUNT

Dieter
Enthusiast

Re: Rows updated inserted

Hi Dieter,
Thank you very much for your reply. Notify exit suits in my condition.
But, still not clear on how to insert the activity count into a table.

.set NOTIFY

Could you please assist me some more here.
Thanks a lot.
Junior Contributor

Re: Rows updated inserted

A NOTIFY exit is written in C, there are examples in the BTEQ manual and the directory where BTEQ is installed.
Within that C programm you can do whatever you want, including writing to a flat file or inserting into a table.

Before every command you set the NOTIFY and BTEQ passes information to it.

Dieter
Enthusiast

Re: Rows updated inserted

Hi Dieter,

I tried as below:

insert into db1.employee(4,'ddd',40000,current_timestamp(0));

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.if ACTIVITYCOUNT <> 0 then insert into db1.employee values (10,'sss',ACTIVITYCOUNT,current_timestamp(0));
insert into db1.employee values (10,'sss',ACTIVITYCOUNT,current_timestamp(0));
*** Failure 5628 Column ACTIVITYCOUNT not found in db1.employee.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

But still not clear, why am I not able to insert into the table.
Could you tell me, where I am going wrong. Why ACTIVITYCOUNT value is not available after then ?
Teradata Employee

Re: Rows updated inserted

Hello,

It is perhaps due to fact that you need to use "ACTIVITY_COUNT" not "ACTIVITYCOUNT"! :)

HTH!

Regards,

Adeel

Enthusiast

Re: Rows updated inserted

HI Adeel,

Tried with activity_count too. Same error.
I think, in BTEQ activitycount works. activity_count is used in SP.
See the error below :

insert into db1.employee(4,'ddd',40000,current_timestamp(0));

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

.if ACTIVITY_COUNT <> 0 then insert into db1.employee values (10,'sss',ACTIVITY_COUNT,current_timestamp(0));
*** Error: Did not recognize the variable 'ACTIVITY_COUNT'.

Junior Contributor

Re: Rows updated inserted

Hi Monika,
ACTIVITYCOUNT (like ERRORCODE & ERRORLEVEL) in BTEQ is only available with .IF, but you can't use it for any SQL statement.

Whereas the ACTIVITY_COUNT varaible in a SP is set after every SQL statement.

Dieter
Enthusiast

Re: Rows updated inserted

Hi Dieter,

One clarification needed.
In 'notify exit' we will write a C program. how can we use sql to update one table ?
Is it possible to use insert statement in the C program (as we use Pro *C for oracle).

Junior Contributor

Re: Rows updated inserted

Hi Monika,
there's a Preprocessor/Embedded SQL available for Teradata, but i never worked with it.

I'd prefer to write it as a SP, this is the easiest way.

Dieter