You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Rick Hillegas <Ri...@Sun.COM> on 2009/04/24 15:39:44 UTC
SYSIBM schema
Can someone shed some light on the purpose of the SYSIBM schema? It
appears to hold a table called "SYSDUMMY1". What is this table used for?
Thanks,
-Rick
Re: SYSIBM schema
Posted by Rick Hillegas <Ri...@Sun.COM>.
Thanks, Tiago. Does anyone from IBM know whether this table is still
being used?
Thanks,
-Rick
Tiago Espinha wrote:
> Hello Rick,
>
> After googling a bit on this, here's what I found from IBM:
> - http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
> "The sysibm.sysdummy1 table is a special in-memory table that can be
> used to discover the value of DB2 registers as illustrated above. You
> can also use the VALUES keyword to evaluate the register or expression."
>
> And also:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0002369.htm
>
> "SYSIBM.SYSDUMMY1 catalog view
>
> Contains one row. This view is available for applications that require
> compatibility with DB2 Universal Database for z/OS and OS/390."
>
> I believe this is the equivalent to Oracle's DUAL table
> (http://en.wikipedia.org/wiki/DUAL_table).
>
> Hope it helps,
> Tiago
>
> On Fri, Apr 24, 2009 at 2:39 PM, Rick Hillegas
> <Richard.Hillegas@sun.com <ma...@sun.com>> wrote:
> >
> > Can someone shed some light on the purpose of the SYSIBM schema? It
> appears to hold a table called "SYSDUMMY1". What is this table used for?
> >
> > Thanks,
> > -Rick
>
Re: SYSIBM schema
Posted by Tiago Espinha <ti...@espinhas.net>.
Hello Rick,
After googling a bit on this, here's what I found from IBM:
-
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
"The sysibm.sysdummy1 table is a special in-memory table that can be used to
discover the value of DB2 registers as illustrated above. You can also use
the VALUES keyword to evaluate the register or expression."
And also:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0002369.htm
"SYSIBM.SYSDUMMY1 catalog view
Contains one row. This view is available for applications that require
compatibility with DB2 Universal Database for z/OS and OS/390."
I believe this is the equivalent to Oracle's DUAL table (
http://en.wikipedia.org/wiki/DUAL_table).
Hope it helps,
Tiago
On Fri, Apr 24, 2009 at 2:39 PM, Rick Hillegas <Ri...@sun.com>
wrote:
>
> Can someone shed some light on the purpose of the SYSIBM schema? It
appears to hold a table called "SYSDUMMY1". What is this table used for?
>
> Thanks,
> -Rick
Re: SYSIBM schema
Posted by Rick Hillegas <Ri...@Sun.COM>.
Tiago Espinha wrote:
> Rick,
>
> To my understanding, this is like a convenience table. It has exactly
> one row, which means that when you do "SELECT current date FROM
> sysibm.sysdummy1" you will get exactly one record with the date.
>
> I am not familiar with the concept of 'vacuous VTI' as you mentioned
> and if the same can be achieved with that, then my bad.
Hi Tiago,
A VTI (or virtual table) is a function which returns a tabular data set.
There are a number of these in the SYSCS_DIAG schema. In this case, we
could write a function which returns an empty data set. This would
create no locks and so would let us also remove the isolation directives
which decorate the metadata queries that mention DUMMY1.
Thanks,
-Rick
>
> Tiago
>
> On Fri, Apr 24, 2009 at 5:21 PM, Rick Hillegas
> <Richard.Hillegas@sun.com <ma...@sun.com>> wrote:
> >
> > Kathey Marsden wrote:
> >>
> >> Rick Hillegas wrote:
> >>>>
> >>> Thanks Kathey. In the metadata queries it seems to be used to
> construct empty results sets of specific shapes. That is, its contents
> are not examined. Those usages could be replaced with a vacuous vti.
> >>>
> >> While we could make this change, we do need to maintain
> compatibility with older clients so the table would have to remain I
> think.
> >> Thanks
> >>
> >> Kathey
> >>
> >>
> > Thanks, Kathey. Other than compatibility with old clients, do you
> see any reason that this table could not be removed eventually?
> >
> > Thanks,
> > -Rick
>
Re: SYSIBM schema
Posted by Tiago Espinha <ti...@espinhas.net>.
Rick,
To my understanding, this is like a convenience table. It has exactly one
row, which means that when you do "SELECT current date FROM
sysibm.sysdummy1" you will get exactly one record with the date.
I am not familiar with the concept of 'vacuous VTI' as you mentioned and if
the same can be achieved with that, then my bad.
Tiago
On Fri, Apr 24, 2009 at 5:21 PM, Rick Hillegas <Ri...@sun.com>
wrote:
>
> Kathey Marsden wrote:
>>
>> Rick Hillegas wrote:
>>>>
>>> Thanks Kathey. In the metadata queries it seems to be used to construct
empty results sets of specific shapes. That is, its contents are not
examined. Those usages could be replaced with a vacuous vti.
>>>
>> While we could make this change, we do need to maintain compatibility
with older clients so the table would have to remain I think.
>> Thanks
>>
>> Kathey
>>
>>
> Thanks, Kathey. Other than compatibility with old clients, do you see any
reason that this table could not be removed eventually?
>
> Thanks,
> -Rick
Re: SYSIBM schema
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
>>
> Thanks, Kathey. Other than compatibility with old clients, do you see
> any reason that this table could not be removed eventually?
>
That's the only reason for keeping it that I know of.
Kathey
Re: SYSIBM schema
Posted by Rick Hillegas <Ri...@Sun.COM>.
Kathey Marsden wrote:
> Rick Hillegas wrote:
>>>
>> Thanks Kathey. In the metadata queries it seems to be used to
>> construct empty results sets of specific shapes. That is, its
>> contents are not examined. Those usages could be replaced with a
>> vacuous vti.
>>
> While we could make this change, we do need to maintain compatibility
> with older clients so the table would have to remain I think.
> Thanks
>
> Kathey
>
>
Thanks, Kathey. Other than compatibility with old clients, do you see
any reason that this table could not be removed eventually?
Thanks,
-Rick
Re: SYSIBM schema
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
>>
> Thanks Kathey. In the metadata queries it seems to be used to
> construct empty results sets of specific shapes. That is, its contents
> are not examined. Those usages could be replaced with a vacuous vti.
>
While we could make this change, we do need to maintain compatibility
with older clients so the table would have to remain I think.
Thanks
Kathey
Re: SYSIBM schema
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Rick Hillegas <Ri...@Sun.COM> writes:
> Kathey Marsden wrote:
>> Rick Hillegas wrote:
>>> Can someone shed some light on the purpose of the SYSIBM schema? It
>>> appears to hold a table called "SYSDUMMY1". What is this table used
>>> for?
>>>
>> The SYSIBM schema is used for the network server metadata queries.
>> I see SYSDUMMY1 referenced in
>> org.apache.derby.client.am.DatabaseMetaData.
>>
> Thanks Kathey. In the metadata queries it seems to be used to
> construct empty results sets of specific shapes. That is, its contents
> are not examined. Those usages could be replaced with a vacuous vti.
Or perhaps simpler: a VALUES statement.
Just replace this
FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR
with this
FROM (VALUES 1) V WHERE 1=0
--
Knut Anders
Re: SYSIBM schema
Posted by Rick Hillegas <Ri...@Sun.COM>.
Kathey Marsden wrote:
> Rick Hillegas wrote:
>> Can someone shed some light on the purpose of the SYSIBM schema? It
>> appears to hold a table called "SYSDUMMY1". What is this table used for?
>>
> The SYSIBM schema is used for the network server metadata queries. I
> see SYSDUMMY1 referenced in org.apache.derby.client.am.DatabaseMetaData.
>
Thanks Kathey. In the metadata queries it seems to be used to construct
empty results sets of specific shapes. That is, its contents are not
examined. Those usages could be replaced with a vacuous vti.
Thanks,
-Rick
Re: SYSIBM schema
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
> Can someone shed some light on the purpose of the SYSIBM schema? It
> appears to hold a table called "SYSDUMMY1". What is this table used for?
>
The SYSIBM schema is used for the network server metadata queries. I
see SYSDUMMY1 referenced in org.apache.derby.client.am.DatabaseMetaData.