Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Red face Unanswered: How do i create an empty result set?

    I know very little of DB2 but one of my clients has a problem (which makes it my problem...). In some of our software an empty result set is needed from any queried table/view, which is created by 'select * from table_name where 1=2 '.
    In Oracle this condition is evaluated to false before any table scan begins, which returns an empty set instantly. In DB2 this seems to take forever, because the query does a full table scan! This is a Known Problem according to IBM.
    We tried 'select * from table_name where RNN(table_name)=0' but this was as slow as 'where 1=2'.
    The query should not reference any specific column name since it must work with any table/view name.
    Has anyone out there an idea how to accomplish this with the same performance as in Oracle?

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Couldn't you do something like...
    Code:
    select a.* 
    from   tabname a
    ,         sysibm.sysdummy1 b
    where ibmreqd != ibmreqd
    Just a suggestion... and not tested!

    Damian

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    Hmm...is 'ibmreqd' some kind of pseudocolumn? Remember i'm new to this...

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    or VALUES(CAST(NULL AS CHAR(10)))

    Cheers
    Sathyaram



    [

    QUOTE]Originally posted by Damian Ibbotson
    Couldn't you do something like...
    Code:
    select a.* 
    from   tabname a
    ,         sysibm.sysdummy1 b
    where ibmreqd != ibmreqd
    Just a suggestion... and not tested!

    Damian
    [/QUOTE]
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How do i create an empty result set?

    Which version and platform of DB2 are you on ?

    DB2 7.2 AIX , on a table, say table1, with roughly 100 million records and indexed col1 gives the following execution result :


    Running in Embedded Dynamic mode.

    ---------------------------------------------

    Statement number: 1

    select count(*) from table1 where coll1='00001000'

    1
    --------------
    1


    Number of rows retrieved is: 1
    Number of rows sent to output is: 1

    Elapsed Time is: 0.002 seconds

    ---------------------------------------------

    Statement number: 2


    select count(*) from table1 where 1=2

    1
    --------------
    0


    Number of rows retrieved is: 1
    Number of rows sent to output is: 1

    Elapsed Time is: 0.001 seconds

    ---------------------------------------------

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Mar 2004
    Posts
    4
    I know they recently upgraded but i dont know to what version. (personally, i don't even have a DB2 around here..).
    Could you please post the complete query, not just the VALUES clause?
    By the way - select count(*) won't do it. We need to do select * to get all the empty columns.

  7. #7
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Hmm...is 'ibmreqd' some kind of pseudocolumn? Remember i'm new to this...
    The 'SYSDUMMY1' table is just like Oracle's 'DUAL' table. It hold a single value in a field, 'IBMREQD'.

    The best use I have seen of this table is for existence checks where SYSDUMMY1 is referenced in an outer query and correlated to a subquery containing your existence check.
    Code:
    select coalesce(1,0) from sysibm.sysdummy1 a
    where exists (select 1 from yourTable b where b.col = someValue and a.ibmreqd = a.ibmreqd)
    As to why you couldn't use just any old table with 1 row, I don't know? ...you probably could but any example I have seen seems to use the SYSDUMMY table, so in a sheep-like fashion, I've assumed that there must be something special about _that_ table.

    If anyone can explain if this table has 'magic' properties I'd like to know, otherwise, why is it there?

    Damian
    Last edited by Damian Ibbotson; 03-23-04 at 17:14.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Well, in DB2 for LUW, we have the VALUES Statement for selecting one dummy row , like, you write inside a stored proc ,
    values(SQLCODE) or from a command line
    values(func1(1))

    But DB2 on mainframe does not support this statemenet ... Even V8 does not have a full support as you have in Unix platforms ... And so, SYSIBM.SYSDUMMY1 is used on the mainframe ...

    SQL Reference says,
    SYSIBM.SYSDUMMY1
    Contains one row. This view is available for applications that require compatibility with DB2 Universal Database for z/OS and OS/390.

    Cheers
    Sathyaram



    Originally posted by Damian Ibbotson
    The 'SYSDUMMY1' table is just like Oracle's 'DUAL' table. It hold a single value in a field, 'IBMREQD'.

    The best use I have seen of this table is for existence checks where SYSDUMMY1 is referenced in an outer query and correlated to a subquery containing your existence check.
    Code:
    select coalesce(1,0) from sysibm.sysdummy1 a
    where exists (select 1 from yourTable b where b.col = someValue and a.ibmreqd = a.ibmreqd)
    As to why you couldn't use just any old table with 1 row, I don't know? ...you probably could but any example I have seen seems to use the SYSDUMMY table, so in a sheep-like fashion, I've assumed that there must be something special about _that_ table.

    If anyone can explain if this table has 'magic' properties I'd like to know, otherwise, why is it there?

    Damian
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Well, in DB2 for LUW, we have the VALUES Statement for selecting one dummy row , like, you write inside a stored proc ,
    values(SQLCODE) or from a command line
    values(func1(1))

    But DB2 on mainframe does not support this statemenet ... Even V8 does not have a full support as you have in Unix platforms ... And so, SYSIBM.SYSDUMMY1 is used on the mainframe ...

    SQL Reference says,
    SYSIBM.SYSDUMMY1
    Contains one row. This view is available for applications that require compatibility with DB2 Universal Database for z/OS and OS/390.

    Cheers
    Sathyaram
    There had to be a reason. Thanks for clearing that up Sathy.

  10. #10
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Hi

    My reply to the first question. Did you try: SELECT ... FROM Table WHERE 0=1 FETCH FIRST ROW ONLY?

  11. #11
    Join Date
    Mar 2004
    Posts
    4
    Nope, but i will try it the next time i talk to my client (maybe today). Thanks.

Posting Permissions

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