Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2010
    Posts
    32

    Unanswered: fiind rowcount in db2

    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 .

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    in the sqlca, sqlerrcd(3) contains a count of
    • rows read
    • rows inserted
    • rows updated
    • rows deleted
    for an execution of a particular sql.
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Mar 2010
    Posts
    32
    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 .

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    may be...

    GET DIAGNOSTICS SQL-variable-name = ROW_COUNT;

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Or you do a "SELECT COUNT(*) FROM NEW TABLE ( UPDATE ... )"
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2010
    Posts
    32
    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 .

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    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]
    Last edited by umayer; 05-12-10 at 07:19.

  8. #8
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    umayer,

    your are correct and I apologize for not referring to the manual for proper spelling before posting.
    Dick Brenholtz, Ami in Deutschland

Posting Permissions

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