This is what i do in sqlserver to find the number of affteced rows.

UPDATE authors SET city = 'Oakland' WHERE city = 'Salt Lake City'
SELECT @@rowcount AS 'RowsChanged'


What's the alternate option for the same process in db2 .
with out using select count(*) from statements .
can we find the same using any handlers in stored procedures .

in the sqlca, sqlerrcd(3) contains a count of

• rows read
• rows inserted
• rows updated
• rows deleted

for an execution of a particular sql.

I have gone through some IBM articles and it says it works if i keep the include option for geeting the sqlca .

I am unable to get the same in stored procedures .

Can you provide me a sample piece of code using the sqlca option .

may be...

GET DIAGNOSTICS SQL-variable-name = ROW_COUNT;

Or you do a "SELECT COUNT(*) FROM NEW TABLE ( UPDATE ... )"

GET DIAGNOSTICS SQL-variable-name = ROW_COUNT;

this works fine for me and i am done .

but I would like to check how to use sqlca in db2 procedures .

the sqlca is used in a stored procedure as in any other program:



/* this is a stored procedure for test purpose */
dbsp03: proc (parm1) options(main noexecops);

dcl h bin fixed(31) init (123);

exec sql include sqlca;

exec sql delete from my.table where col > :h ;

if sqlca.sqlcode ^= 0 & sqlca.sqlcode ^= 100 then do;
call your_error_routine();
end;

put skip edit ('----------- DBSP03 (REPORT) ----------') (A);
put skip edit (' Number of records deleted: ', SQLCA.SQLERRD(3) ) (A,A);
put skip edit ('----------- DBSP03 (REPORT END) -------') (A);

end dbsp03 ;


Note: The number of records affected by the statement is stored in the variable SQLERRD(3), not -as dbzTHEdinosaur mentioned- in SQLERRCD(3)


if your program language is C, the number is stored in sqlerrd[2]

umayer,

your are correct and I apologize for not referring to the manual for proper spelling before posting.