Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    16

    Unanswered: How to find out count of rows affected in a update statement

    Hi,

    I am on db2 v8.2 udb/aix. Is there any way to find out the number of rows that were affected by running an update sql ? Do i need to create a update trigger to capture this ?

    Thanks,
    Ravi
    Last edited by Ravi_R; 06-10-09 at 16:02.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    GET DIAGNOSTICS SQL-variable-name = ROW_COUNT

    or

    sqlerrd(3) in SQLCA

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are running the SQL in a script or from the command line, use the -a option:

    db2 -a "update table-name set ........."

    This will show the SQLCA and the number of rows affected in insert, update, or delete is in sqlerrd(3). Does not show rows selected.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    select count(*) from final table (update ...)
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    b.t.w. as far as I know there is no way to determine the number of rows affected if a DELETE also throws rows away because of a "DELETE CASCADE" rule. Am I correct?

  6. #6
    Join Date
    Mar 2006
    Posts
    16
    Tks for the suggestions - it works fine for what i am trying to do here .

Posting Permissions

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