Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: To find the no. of rows affected by a select statement

    Hi all,

    I would like to know the number of rows affected by a select statement.. I tried with
    'GET DIAGNOSTICS', but it does not work..

    any other way to do this..

    cheers
    Shefu..

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    Did u received any error ? if yes post here. if not, how did u used get diagnostics ...

    The GET DIAGNOSTICS statement returns information about the most recently run SQL
    statement. One can either get the number of rows processed (i.e. inserted, updated, or deleted), or the return status (for an external procedure call).

    Syntax:
    GET DIAGNOSTICS <<SQL Var Name>> = ROW_COUNT or RETURN_COUNT

    Example:
    BEGIN ATOMIC
    DECLARE numrows INT DEFAULT 0;
    UPDATE staff
    SET salary = 12345
    WHERE ID < 100;
    GET DIAGNOSTICS numrows = ROW_COUNT;
    UPDATE staff
    SET salary = numrows
    WHERE ID = 10;
    END

  3. #3
    Join Date
    Apr 2004
    Posts
    54
    GET DIAGNOSTIC returns num rows for "inserted, updated, or deleted"
    There is nothing about "select".

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    Hi Shefu,

    I suppose you would like to return something like '10 rows updated' or '1 row inserted' - like many other DBMS have it.

    Well, this information is included in the sqlca.
    so, instead of executing
    db2 insert into ... values ...

    do a
    db2 - a insert into ... values ...

    in (3) it will give you the number of affected rows.

    If you like, with some quick C programming you would be able to write a Command Line returning that value for you. see the C samples, they quickly introduce you to that.

    Actually this is something really nice to have and should not be a problem for IBM - lets hope that one day there will be a command option to get it
    Juliane

  5. #5
    Join Date
    Oct 2005
    Posts
    109
    oh sorry, I also did not read properly. you just reference the SELECT:

    well, what programming language are u using ?

    In command line this is number is returned, in other programming it is rather difficult: there are functions to return in which rownumber you are right now.
    So that could be selected when finishing.

    What are you trying to do ?
    Juliane

  6. #6
    Join Date
    Apr 2005
    Posts
    127
    Hi all,

    Quote Originally Posted by gardenman
    GET DIAGNOSTIC returns num rows for "inserted, updated, or deleted"
    There is nothing about "select".
    I would like to include this logic in my DB2 Scripts., Is there anyother alternatives or work arounds to do it..

    Thanks
    Shefu..

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't know in what programming environment you are in. But normally after each SQL command, your code will check that the command executed properly without errors.

    Your code will contain something like
    Code:
    UPDATE table1 
    SET column1 = 23
    WHERE column2 = 'set column1 to 23'
    USING SQLCA;
    if SQLCA.SQLcode = -1 then
    	Message("Error", "DB returns error message: " + SQLCA.SQLErrtext)
    	return (-1)
    end if
    SQLCA is a transcation object. At the beginning you will open a transaction object (database, userID, pasword, ...). From that moment on DB2 uses that transaction object to pass you information about the executed SQL statements.

    Apart from the SQL error code and errormessage, it also contains a property that tells you how much rows were affected by the DELETE, UPDATE, INSERT, SELECT.
    Code:
    nmbr_of_records = SQLCA.SQLNRows
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  8. #8
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the help wim,

    assume that its a DB2 procedure executed in a DB2 command line processor. I want the number of rows affected by a SELECT statement in the script. Get Diagnostics does not helps me..

    shefu..

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    You may find the number of rows affected by "SELECT" by counting the rows returned by this command.
    So if you run the DB2 command line
    DB2 "Select * From table Where ..." > result.txt
    then the number of rows returned by this select is the output of the unix command
    wc -l result.txt
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Apr 2005
    Posts
    127
    Thanks Peter,

    its not a command line SELECT query, its a SELECT query used inside a DB2 PROCEDURE and i want to know the number of rows returned by the SELECT query while executing the DB2 procedure..
    hope i am clear now..

    regards,
    Shefu.

  11. #11
    Join Date
    Jan 2005
    Posts
    36
    Hi,

    As mentioned earlier the GET DIAGNOSTICS does not give you the no. of rows for the SELECT statement.
    A possible workaround would be to do a SELECT COUNT(*) INTO VARIABLE.
    You could then use this variable to verify the no. of rows and use it wherever you need in the SQL Procedure.

    HTH
    RiTz

  12. #12
    Join Date
    Apr 2005
    Posts
    127
    Thanks for the answer ritz.. lemme try this.

Posting Permissions

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