Re: SYSIBM schema
Rick Hillegas 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
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
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 mailto:[email protected]>> 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
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
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 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
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
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
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.
Re: SYSIBM schema
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 mailto:[email protected]>> 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
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 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
