Results 1 to 10 of 10

Thread: Setting SQLCODE

  1. #1
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7

    Post Unanswered: Setting SQLCODE

    Hi,

    Anyone know how to set the SQLCODE with a query without actually
    returning any records ? This means no host variables are required.

    I know i could count the records, but i've starting looking now...
    Any ideas ?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you say "set the SQLCODE" do you mean execute a valid SQL statement that returns SQLCODE of 0?

    Are you looking for the most efficient statement that can be executed, and without host variables?

  3. #3
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7
    Yes...

    I want to execute a query from a cobol app that checks for a record.
    I don't need to get the data...just need to know if it exists.

    Cheers...

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    In my COBOL/DB2 days, we just selected count(*). Assuming your WHERE clause predicate is the same as a unique index (or primary key) it will be "index only access" and it should be pretty quick. I would not worry too much about the performance hit of returning the count(*).

    If you want to do it by SQLCODE rather than checking count(*), then just select one column which is in the WHERE clause (and also part of the unique index). Same index only access.

    I don't know if there is something in new DB2 versions that you can try, but it would likely be in the SELECT section of the SQL manual.

  5. #5
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    Try:
    SELECT '1' INTO :<char-working-storage>
    FROM <YOUR TABLE>
    WHERE <criteria is met>

    More efficent than COUNT

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Why is select "1" in to host-:variable more efficeint than count(*)?

  7. #7
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    More efficent because no calculation will be performed...
    I assumed your WHERE criteria would use a unique index and limit the selection to one row since you said you are checking for a single record. My understanding is, Function calls would require more resources than SELECT '1' - which requires nothing more than index existance check.

    I certainly could be wrong - let me know if you find something better.
    Thanks,
    Rick

  8. #8
    Join Date
    Sep 2002
    Posts
    456
    You are right Rick. Selecting 1 is definitely more effecient than count(*).
    I don't have the article in hand, when I do I will post it in case somebody is interested.

    dollar

    Originally posted by Rick-dba
    More efficent because no calculation will be performed...
    I assumed your WHERE criteria would use a unique index and limit the selection to one row since you said you are checking for a single record. My understanding is, Function calls would require more resources than SELECT '1' - which requires nothing more than index existance check.

    I certainly could be wrong - let me know if you find something better.
    Thanks,
    Rick

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    OK, but I would like to see the article. I assume that we are talking about S/390 since he is using COBOL.

    I thought (maybe mistakenly) that DB2 already has the count calculated, regardless of whether you ask for it. Since DB2 is using a unique index to retrieve the row (I am assuming), DB2 should know the count is 1.

    Both count(*) and select constant (or any column in the unique index that was also used in the select predicate) would be "index only access," so the access path is the same and I would guess that any differences would be very minor.

  10. #10
    Join Date
    May 2003
    Location
    Belfast, N.I.
    Posts
    7
    thanks guys !!

    I'd also be interested in reading the article.

Posting Permissions

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