Feedback on insert/delete/update/merge

shahnazurs · #1 (**permalink**) 06-25-09, 06:36

Hi,

Can someone say how do I know that how many records got updated?

Code:

update 
set abc.emp a
where a.deptno=20;

In oracle we have implicit cursor SQL which tells if I say SQL%ROWCOUNT, do we have any kind of cursor or any functionality in DB2?

This would help me great if you could provide valuable information.

Thanks,
Shahnaz.

n_i · #2 (**permalink**) 06-25-09, 07:28

Search this forum - I remember this question was answered a couple of weeks ago.

Stealth_DBA · #3 (**permalink**) 06-25-09, 07:40

Shahnaz, Look up GET DIAGNOSTICS. I believe the syntax is:

DECLARE variable INTEGER

GET DIAGNOSTICS variable = ROW_COUNT

shahnazurs · #4 (**permalink**) 06-25-09, 08:01

thanks for your reply.

GET DIAGNOSTICS works only if we go for creating procedure or function,...

I am in CLP

db2=> update abc.emp set ename='shahnaz';

db2=>

In this level, how can I use GET DIAGNOSTICS ? I think we need to declare variable here, since I am new bee to this DB2 arena it would be great help for me if you resolve this issue.

Thanks,
Shahnaz.

stolze · #5 (**permalink**) 06-25-09, 19:17

You can simply query the results of the UPDATE statement:

Code:

SELECT COUNT(*)
FROM NEW TABLE ( UPDATE ... )

shahnazurs · #6 (**permalink**) 06-26-09, 01:42

I tried by giving

Code:

select count(*) from sysibm.sysdumm1
(update abc.emp set ename='shahnaz')

but it throws me error!

Quote:

SQL0104N An unexpected token "sysibm.sysdummy1" was found following "select
count(*) from". Expected tokens may include: "<new_or_old_or_final_table>".
SQLSTATE=42601

Can anybody suggest about this issue?

Thanks,
Shahnaz.

sathyaram_s · #7 (**permalink**) 06-26-09, 02:01

select count(*) from new table
(update abc.emp set ename='shahnaz') as x

shahnazurs · #8 (**permalink**) 06-26-09, 02:34

thanks Satyaram .

It is doing fine for insert,update and delete operations...

I tried using for merge statement to find out how many records update and even how many records inserted but it gives me error!

Code:

select count(*) from new table
(
merge into abc.emp_temp a
using abc.emp b
on(a.empno=b.empno)
when matched then
update set
ename=b.ename
)

Can you please suggest, what would be the work around?

thanks,
Shahnaz.

Marcus_A · #9 (**permalink**) 06-26-09, 03:19

If you use the -a option, the entire SQLCA will be output. After INSERT, UPDATE, DELETE, or MERGE, SQLERRD(3) contains the actual number of rows that qualified for the operation.

for example:

db2 -tvaf filename

or

db2 -a "update set abc.emp a where a.deptno=20"

shahnazurs · #10 (**permalink**) 06-26-09, 05:51

Fantastic Marcus.

It is really works. I got a sql file where I have select statements and insert statements; if I use db2 -tvaf then it is going to work on all the statement but I need to restrict only to insert statement.

Thanks,
Shahnaz.

sathyaram_s · #11 (**permalink**) 06-26-09, 07:09

You can also turn the 'a' switch on/off using a stmt

update command options using a on
update command options using a off

if you put the ON stmt before the inserts and then after you have done the insert, turn it OFF .. If you are using these commands, you do not need the 'a' in -tvaf

HTH

shahnazurs · #12 (**permalink**) 06-29-09, 03:05

But I tried the following statement

Code:

merge into abc.emp_temp a
using abc.emp b
on(a.empno=b.empno)
when matched then
update set
ename=b.ename
using a on;

but it is giving error! Can you please correct me if I missed anything. Hopefully DB2 engine might have confusing by looking at two "using"!

Thanks,
Shahnaz.

Marcus_A · #13 (**permalink**) 06-29-09, 12:39

update command options using a on;

merge into abc.emp_temp a
using abc.emp b
on(a.empno=b.empno)
when matched then
update set
ename=b.ename;

update command options using a off;

Internet Services

Web Development

Digital Marketing

Consumer Tech