Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Mamta Satoor <ms...@gmail.com>]

Hi,
 The patch for optimizer overrides includes changes in metadata.properties.
I think this change in metadata.properties is going to require some form of
upgrade code so that the sql text stored in the systems tables can be
replaced with new sql text which uses this new form of optimizer overrides.
 I wondered if anyone can give me some pointers on how to get started with
upgrade code to achieve this. In the mean time, I will start looking into it
too.
 thanks,
Mamta

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Daniel John Debrunner <dj...@debrunners.com>]

Mike Matrigali wrote:

> I am not sure if there is a place - maybe dan has written something up.
> 
> The way I think about it is, if your 10.2 feature can not work without
> db upgrades to a 10.1 db it should be disabled in soft upgrade.  If
> your feature would somehow change the 10.1 db such that 10.1 server
> will no longer work in some way then it must be disabled in soft
> upgrade.
> 
> The one I am never sure about is I believe it ok to add a feature
> available under soft upgrade, even it an application which uses it will
> no longer work under the previous version, since in that case the
> application decided to make itself not backward compatible.

I believe these should be allowed. It is only anything written in to the
on-disk database that would not be understood by previous releases that
should be disabled.

If an application uses a new feature then that's their choice. It also
allows experimentation with the new feature, say seeing in using the new
feature allowed performance increases, while still allowing reversion to
10.1/10.0.

Dan.

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Mike Matrigali <mi...@sbcglobal.net>]

I am not sure if there is a place - maybe dan has written something up.

The way I think about it is, if your 10.2 feature can not work without
db upgrades to a 10.1 db it should be disabled in soft upgrade.  If
your feature would somehow change the 10.1 db such that 10.1 server
will no longer work in some way then it must be disabled in soft
upgrade.

The one I am never sure about is I believe it ok to add a feature
available under soft upgrade, even it an application which uses it will
no longer work under the previous version, since in that case the
application decided to make itself not backward compatible.

Mamta Satoor wrote:
> I will have to admit my ignorance on soft upgrade. Can you point me 
> somewhere to understand what is involved in a soft upgrade and then I 
> will be able to answer your question?
>  
> Mamta
>  
> On 11/11/05, *Mike Matrigali* <mikem_app@sbcglobal.net 
> <ma...@sbcglobal.net>> wrote:
> 
>     In the case of soft upgrade (ie. the case where there are no changes
>     to the metadata.properties) will you be disabling the new optimizer
>     overrides?
> 
>     Mamta Satoor wrote:
>      > Hi,
>      >
>      > The patch for optimizer overrides includes changes in
>      > metadata.properties. I think this change in metadata.properties
>     is going
>      > to require some form of upgrade code so that the sql text stored
>     in the
>      > systems tables can be replaced with new sql text which uses this new
>      > form of optimizer overrides.
>      >
>      > I wondered if anyone can give me some pointers on how to get started
>      > with upgrade code to achieve this. In the mean time, I will start
>      > looking into it too.
>      >
>      > thanks,
>      > Mamta
> 
> 

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Mamta Satoor <ms...@gmail.com>]

I will have to admit my ignorance on soft upgrade. Can you point me
somewhere to understand what is involved in a soft upgrade and then I will
be able to answer your question?
 Mamta
 On 11/11/05, Mike Matrigali <mi...@sbcglobal.net> wrote:
>
> In the case of soft upgrade (ie. the case where there are no changes
> to the metadata.properties) will you be disabling the new optimizer
> overrides?
>
> Mamta Satoor wrote:
> > Hi,
> >
> > The patch for optimizer overrides includes changes in
> > metadata.properties. I think this change in metadata.properties is going
> > to require some form of upgrade code so that the sql text stored in the
> > systems tables can be replaced with new sql text which uses this new
> > form of optimizer overrides.
> >
> > I wondered if anyone can give me some pointers on how to get started
> > with upgrade code to achieve this. In the mean time, I will start
> > looking into it too.
> >
> > thanks,
> > Mamta
>
>

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Mike Matrigali <mi...@sbcglobal.net>]

In the case of soft upgrade (ie. the case where there are no changes
to the metadata.properties) will you be disabling the new optimizer
overrides?

Mamta Satoor wrote:
> Hi,
>  
> The patch for optimizer overrides includes changes in 
> metadata.properties. I think this change in metadata.properties is going 
> to require some form of upgrade code so that the sql text stored in the 
> systems tables can be replaced with new sql text which uses this new 
> form of optimizer overrides.
>  
> I wondered if anyone can give me some pointers on how to get started 
> with upgrade code to achieve this. In the mean time, I will start 
> looking into it too.
>  
> thanks,
> Mamta 

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Army <qo...@sbcglobal.net>]

Satheesh Bandaram wrote:
> Thanks Army for the good description... But I do think there are SOME metadata 
> calls that are stored in the database...

Yes, you're absolutely right.  Just after I sent that last email, I realized one 
other check that I should have done before clicking "send".

I tried deleting the metadata.properties file and I tried deleting the 
"getProcedures" method from the file, and both failed as expected.  But when I 
went an _changed_ the "getProcedures" method, the changes that I made didn't 
propagage through--so yes, something is in fact being stored in the tables.

Sorry for the misinfo.

Yikes,
Army

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Mamta Satoor <ms...@gmail.com>]

Yes, Satheesh, you are right. I have been primarily focusing on
getPrimaryKeys as an example and I see that it is being saved in
sys.sysstatements and that's how I started to think about upgrade.
 Army, thanks for your time on describing the codepath. I was under the
impression that all the metadata queries get saved in the database, but
looks like not.
 Now, that we are back to the upgrade issue, I guess I need to figure out
what should be done for soft upgrade. With my optimizer override patch,
there is no support for old overrides syntax (in 10.2) which is what the
metadata queries would have during soft upgrade. I am not sure at this
point, how would I handle those old format metadata queries because w/o the
new syntax, 10.2 would raise errors for metadata calls because the
10.2parser doesn't recognize the old format anymore.
 thanks,
Mamta

 On 11/11/05, Satheesh Bandaram <sa...@sourcery.org> wrote:
>
> Thanks Army for the good description... But I do think there are SOME
> metadata calls that are stored in the database...
>
> Try this:
>
> ij> *select stmtname, text from sys.sysstatements where text like
> '%PROPERTIES%';
> *
> STMTNAME
> |TEXT
>
>
>
>
>
>
>
>
>
>
>
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
>
> --------------------------------------------------------------------------------
> ----------------------------------------------------------------
> getPrimaryKeys
> |SELECT CAST ('' AS VARCHAR(128)
> ) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME,
> COLS.COL
> UMNNAME AS COLUMN_NAME, CONGLOMS.DESCRIPTOR.getKeyColumnPosition(
> COLS.COLUMNNUMB
> ER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME FROM PROPERTIES
> joinOrder=FIXED S
> YS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1', SYS.SYSSCHEMAS S
> PROPERTIES
> joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1', SYS.SYSCONSTRAINTSCONS PR
> OPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3',
> SYS.SYSKEYS K
> EYS PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1',
> SYS.SYSCONGLOME
> RATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP, index =
> 'SYSCONGLOMERATES_IND
> EX1', SYS.SYSCOLUMNS COLS PROPERTIES joinStrategy=NESTEDLOOP, index
> ='SYSCOLUMNS
> _INDEX1' WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND
> T.TABLENAME
> LIKE ? AND T.SCHEMAID = S.SCHEMAID AND T.TABLEID = COLS.REFERENCEID AND
> T.TABLE
> ID = CONGLOMS.TABLEID AND CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND
> CONS.
> CONSTRAIN&
> getCrossReference
> |SELECT CAST ('' AS VARCHAR(128)
> ) AS PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, CAST ('' AS
> VARCHA
> R(128)) AS FKTABLE_CAT, S2.SCHEMANAME AS FKTABLE_SCHEM, T2.TABLENAME AS
> FKTABLE_
> NAME, COLS2.COLUMNNAME AS FKCOLUMN_NAME, CAST (
> CONGLOMS2.DESCRIPTOR.getKeyColumn
> Position( COLS2.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN
> F2.UPDAT
> ERULE='S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE (CASE
> WHEN F
> 2.UPDATERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE
> java.s
> ql.DatabaseMetaData::importedKeyNoAction END) END) AS SMALLINT) AS
> UPDATE_RULE,
> CAST ((CASE WHEN F2.DELETERULE='S' THEN
> java.sql.DatabaseMetaData::importedKeyR
> estrict ELSE (CASE WHEN F2.DELETERULE='R' THEN
> java.sql.DatabaseMetaData::impor
> tedKeyNoAction ELSE (CASE WHEN F2.DELETERULE='C' THEN
> java.sql.DatabaseMetaData:
> :importedKeyCascade ELSE (CASE WHEN F2.DELETERULE='U' THEN
> java.sql.DatabaseMeta
> Data::importedKeySetNull ELSE
> java.sql.DatabaseMetaData::importedKeyNoAction END
> )END)ENd&
> odbc_getPrimaryKeys
> |SELECT JDBC_SUBQUERY.TABLE_CAT
> AS TABLE_CAT, JDBC_SUBQUERY.TABLE_SCHEM AS TABLE_SCHEM,
> JDBC_SUBQUERY.TABLE_NAME
> AS TABLE_NAME, JDBC_SUBQUERY.COLUMN_NAME AS COLUMN_NAME, CAST
> (JDBC_SUBQUERY.KE
> Y_SEQ AS SMALLINT) AS KEY_SEQ, JDBC_SUBQUERY.PK_NAME AS PK_NAME FROM (
> SELECT CA
> ST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM,
> T.TABLENAME A
> S TABLE_NAME, COLS.COLUMNNAME AS COLUMN_NAME,
> CONGLOMS.DESCRIPTOR.getKeyColumnPo
> sition(COLS.COLUMNNUMBER) AS KEY_SEQ, CONS.CONSTRAINTNAME AS PK_NAME FROM
> PROPER
> TIES joinOrder=FIXED SYS.SYSTABLES T PROPERTIES index='SYSTABLES_INDEX1',
> SYS.SY <http://sys.sy/>
> SSCHEMAS S PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1',
> SYS.
> SYSCONSTRAINTS CONS PROPERTIES joinStrategy=NESTEDLOOP, index
> ='SYSCONSTRAINTS_I
> NDEX3', SYS.SYSKEYS KEYS PROPERTIES joinStrategy=NESTEDLOOP, index
> ='SYSKEYS_IN
> DEX1', SYS.SYSCONGLOMERATES CONGLOMS PROPERTIES joinStrategy=NESTEDLOOP,
> index =
> 'SYSCONGLOMERATES_INDEX1', SYS.SYSCOLUMNS COLS PROPERTIES
> joinStrategy=NESTEDLO
> OP, inde&
> getImportedKeys
> |SELECT CAST ('' AS VARCHAR(128)
> ) AS PKTABLE_CAT, S.SCHEMANAME AS PKTABLE_SCHEM, TABLENAME AS
> PKTABLE_NAME, COLS
> .COLUMNNAME AS PKCOLUMN_NAME, CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT,
> FKTABLE_
> SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, CAST (
> CONGLOMS.DESCRIPTOR.getKeyColumnPositi
> on( COLS.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, CAST ((CASE WHEN
> FK_UPDATERULE='
> S' THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE (CASE WHEN
> FK_UPDAT
> ERULE='R' THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE
> java.sql.Data
> baseMetaData::importedKeyNoAction END) END) AS SMALLINT) AS UPDATE_RULE,
> CAST (
> (CASE WHEN FK_DELETERULE='S' THEN
> java.sql.DatabaseMetaData::importedKeyRestrict
> ELSE (CASE WHEN FK_DELETERULE='R' THEN
> java.sql.DatabaseMetaData::importedKeyN
> oAction ELSE (CASE WHEN FK_DELETERULE='C' THEN
> java.sql.DatabaseMetaData::import
> edKeyCascade ELSE (CASE WHEN FK_DELETERULE='U' THEN
> java.sql.DatabaseMetaData::i
> mportedKeySetNull ELSE java.sql.DatabaseMetaData::importedKeyNoAction END)
> END)
> END) END&
>
> 4 rows selected
> ij>
>
> Army wrote:
>
> Mamta Satoor wrote:
>
> Hi,
> The patch for optimizer overrides includes changes in metadata.properties.
>
> I think this change in metadata.properties is going to require some form
> of
> upgrade code so that the sql text stored in the systems tables can be
> replaced with new sql text which uses this new form of optimizer
> overrides.
>
>
> If I'm following the metadata codepath correctly, I don't _think_ the SQL
> text for metadata.properties is actually stored in the system tables.
> Rather, Derby reads the "metadata.properties" file (which is part of the
> classes directory and is included in the build jars) directly at run time.
>
> To explain why I think this is the case, take "getProcedures()" (which is
> part of the Java DatabaseMetaData class) as an example. In the
> EmbedDatabaseMetaData class, the getProcedures() call ends up being:
>
> PreparedStatement s = getPreparedQuery(queryName);
> s.setString(1, swapNull(catalog));
> s.setString(2, swapNull(schemaPattern));
> s.setString(3, swapNull(procedureNamePattern));
> return s.executeQuery();
>
> "getPreparedQuery()" includes the following line:
>
> String queryText = getQueryDescriptions().getProperty(nameKey);
>
> Then "getQueryDescriptions()" calls "loadQueryDescriptions()", which does
> some security checking and ultimately calls "PBloadQueryDescriptions()",
> which (finally) calls:
>
> InputStream is = getClass().getResourceAsStream("metadata.properties");
> p.load(is);
>
> As a quick (but not by any means exhaustive) check of this, I went and
> renamed the "metadata.properties" file in my classes directory to
> something else, then tried to call "getProcedures" from a Derby
> DatabaseMetaData instance after connecting to (an already created) database.
> The call failed with an NPE because the InputStream was not able to find
> metadata.properties. I then put the metadata.properties file back and
> deleted the "getProcedures" statement, to see what would happen if the file
> was there but didn't have the query in it. That failed, too, with an error
> saying:
>
> Feature not implemented: getProcedures.
>
> So that leads me to believe that the queries are not actually stored in
> the database.
>
> As for the stored procedures that Kathey mentioned, those are used for
> client-server communication. The client calls these stored procedures, which
> are then mapped to the appropriate methods in EmbedDatabaseMetaData (the
> mapping occurs in SystemProcedures.java, which is where the stored
> procedures are implemented), and thus the same codepath shown above is used.
>
>
>  I wondered if anyone can give me some pointers on how to get started with
>
> upgrade code to achieve this. In the mean time, I will start looking into
> it
> too.
>
>
> All of that said, I'm not entirely sure what that means for your upgrade
> question. But here's my theory:
>
> Even if metadata.properties is different from one version to another, the
> engine will only read the metadata.properties file that it has in its
> build. So as long as your changes don't modify the stored procedure
> signatures in any way (they only change the metadata.properties file), I
> don't _think_ upgrade is an issue because nothing in the system table has
> actually changed.
>
> As for soft upgrade...umm, I tend to lean toward this being a non-issue,
> as well, because nothing in the database is actually _changed_ when a
> metadata procedure is called. If you soft upgrade to a later version,
> execute a bunch of metadata queries, then revert back to an older version
> and execute a bunch more metadata queries, everything should still be
> fine...I think.
>
> But all of that is based on my examination of the metadata codepath; if
> I've overlooked or oversimplified, people should feel free to correct me.
>
> Hopefully that's more helpful than confusing,
> Army
>
>
>
>

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Army <qo...@sbcglobal.net>]

> Mamta Satoor wrote:
> 
>>Hi,
>>The patch for optimizer overrides includes changes in metadata.properties.
>>I think this change in metadata.properties is going to require some form of
>>upgrade code so that the sql text stored in the systems tables can be
>>replaced with new sql text which uses this new form of optimizer overrides.

If I'm following the metadata codepath correctly, I don't _think_ the SQL text 
for metadata.properties is actually stored in the system tables.  Rather, Derby 
reads the "metadata.properties" file (which is part of the classes directory and 
is included in the build jars) directly at run time.

To explain why I think this is the case, take "getProcedures()" (which is part 
of the Java DatabaseMetaData class) as an example.  In the EmbedDatabaseMetaData 
class, the getProcedures() call ends up being:

	PreparedStatement s = getPreparedQuery(queryName);
	s.setString(1, swapNull(catalog));
	s.setString(2, swapNull(schemaPattern));
	s.setString(3, swapNull(procedureNamePattern));
	return s.executeQuery();

"getPreparedQuery()" includes the following line:

	String queryText = getQueryDescriptions().getProperty(nameKey);

Then "getQueryDescriptions()" calls "loadQueryDescriptions()", which does some 
security checking and ultimately calls "PBloadQueryDescriptions()", which 
(finally) calls:

	InputStream is = getClass().getResourceAsStream("metadata.properties");		
	p.load(is);

As a quick (but not by any means exhaustive) check of this, I went and renamed 
the "metadata.properties" file in my classes directory to something else, then 
tried to call "getProcedures" from a Derby DatabaseMetaData instance after 
connecting to (an already created) database.  The call failed with an NPE 
because the InputStream was not able to find metadata.properties.  I then put 
the metadata.properties file back and deleted the "getProcedures" statement, to 
see what would happen if the file was there but didn't have the query in it. 
That failed, too, with an error saying:

	Feature not implemented: getProcedures.

So that leads me to believe that the queries are not actually stored in the 
database.

As for the stored procedures that Kathey mentioned, those are used for 
client-server communication.  The client calls these stored procedures, which 
are then mapped to the appropriate methods in EmbedDatabaseMetaData (the mapping 
occurs in SystemProcedures.java, which is where the stored procedures are 
implemented), and thus the same codepath shown above is used.

>>I wondered if anyone can give me some pointers on how to get started with
>>upgrade code to achieve this. In the mean time, I will start looking into it
>>too.

All of that said, I'm not entirely sure what that means for your upgrade 
question.  But here's my theory:

Even if metadata.properties is different from one version to another, the engine 
will only read the metadata.properties file that it has in its build.  So as 
long as your changes don't modify the stored procedure signatures in any way 
(they only change the metadata.properties file), I don't _think_ upgrade is an 
issue because nothing in the system table has actually changed.

As for soft upgrade...umm, I tend to lean toward this being a non-issue, as 
well, because nothing in the database is actually _changed_ when a metadata 
procedure is called.  If you soft upgrade to a later version, execute a bunch of 
metadata queries, then revert back to an older version and execute a bunch more 
metadata queries, everything should still be fine...I think.

But all of that is based on my examination of the metadata codepath; if I've 
overlooked or oversimplified, people should feel free to correct me.

Hopefully that's more helpful than confusing,
Army

Re: Derby-573 - optimizer overrides changes to metadata.properties and upgrade

[Kathey Marsden <km...@sbcglobal.net>]

Mamta Satoor wrote:

>Hi,
> The patch for optimizer overrides includes changes in metadata.properties.
>I think this change in metadata.properties is going to require some form of
>upgrade code so that the sql text stored in the systems tables can be
>replaced with new sql text which uses this new form of optimizer overrides.
> I wondered if anyone can give me some pointers on how to get started with
>upgrade code to achieve this. In the mean time, I will start looking into it
>too.
>  
>


I don't know if this helps or confuses the issue more, but I seem to
recall  a long time ago (before contribution)  after a very unfortunate
incident with metadata changes, the product was changed to drop these
stored prepared statements whenever the version changed.  The method to
do this is still in DD_Version,  dropJDBCMetadataSPSes() but doesn't
actually seem to be called from anywhere now , so I don't really know
the current state.

Kathey