Results 1 to 13 of 13
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: Feedback on insert/delete/update/merge

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Search this forum - I remember this question was answered a couple of weeks ago.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Shahnaz, Look up GET DIAGNOSTICS. I believe the syntax is:

    DECLARE variable INTEGER

    GET DIAGNOSTICS variable = ROW_COUNT

  4. #4
    Join Date
    May 2005
    Posts
    25
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can simply query the results of the UPDATE statement:
    Code:
    SELECT COUNT(*)
    FROM NEW TABLE ( UPDATE ... )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2005
    Posts
    25
    I tried by giving

    Code:
    select count(*) from sysibm.sysdumm1
    (update abc.emp set ename='shahnaz')
    but it throws me error!

    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.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    select count(*) from new table
    (update abc.emp set ename='shahnaz') as x
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    May 2005
    Posts
    25
    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.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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"
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    May 2005
    Posts
    25
    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.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    May 2005
    Posts
    25
    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.

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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;
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •