I have a patch for implementing update statistics as a stored procedure. The main logic is to convert the stored procedure request to an ALTER TABLE... sql(bear in mind that this generated sql is not available for the user to issue directly. This alter table syntax will be accepted only when sql is not coming directly from the user. This is similar in concept to what we do for compress table) and go through the existing ALTER TABLE code to do table/schema/index validation, ensuring proper permissions exist for user to call update statistics etc. The actual code to update the statistics has been (literally) copied from the existing disabled code for update statistics in Derby (impl.sql.execute.UpdateStatisticsConstantAction.java) The user can invoke update statistics for just one index on a table or for all indexes on a table. The syntax of the new stored procedure is as follows
call syscs_util.SYSCS_UPDATE_STATISTICS(schemaname, tablename, indexname)
If the user specifies null for the indexname, then all the indexes on the tablename will have their statistics updated. If the user does specify an indexname, then statistics will be updated only for that index.
The patch here include engine changes and upgrade changes. It also fixes existing tests that were impacted by the engine changes. TODO : I need to add more tests for the actual functionality. The existing disabled test selectivity.sql appears to have some tests for old disabled (non-compliant) update statistics syntax. I will use these existing tests as the basis for the new junit tests for the new stored procedure.
The files impacted by this patch are as follows. I will briefly go over the actual code changes in the following paragraph
$ svn stat -q
Parser, compile and execute time changes for the new stored procedure For the stored procedure, we generate one of the following 2 ALTER TABLE ... sqls This sql is generated if user wants us to update the statistics of all the indexes on a table
ALTER TABLE (schemaname.)tablename ALL UPDATE STATISTICS
This sql is generated if user wants us to update the statistics of only one index on a table
ALTER TABLE (schemaname.)tablename UPDATE STATISTICS indexname
We will first make sure that we are at data dictionary version 10.5 or higher in order to accept the new ALTER TABLE sql syntax. Next, we will make sure that this ALTER TABLE was generated through internal code and not from the user code. One these 2 criterias are made, we will generated the compile time node for ALTER TABLE sql.
The changes in this class are simple and simply make sure that we are ready to accept the new ALTER TABLE... syntax
Since we have more parameters to pass from compile phase to execute phase for ALTER TABLE now, I needed to make changes into GenericConstantActionFactory.java from those additional params.
The changes in this file are copied from the existing disabled code for update statistics in impl.sql.execute.UpdateStatisticsConstantAction.java. This existing code relies on a method in IndexRowGenerator.java which I added back again (this method was removed by Dan a long time back because it was not being used by any active code in Derby. The method was removed as part of revision 565966).
This is where we intercept the call to SYSCS_UPDATE_STATISTICS and convert it into internal syntax of ALTER TABLE... sql
The following changes in engine code are related to upgrade code
It marks the database to be at version 10.5 and it adds the new system stored procedure SYSCS_UPDATE_STATISTICS to the data dictionary for 10.5 release.
This provides a way to add the stored procedure SYSCS_UPDATE_STATISTICS to the data dictionary during hard upgrade to 10.5 release of an existing db.
Following existing tests needed some modifications
1)RolesTest.java, GrantRevokeDDLTest.java, db2Compatibility.out
The number of stored procedures have increased by one and hence these tests have to take that into account.
Following tests changes are for upgrade testing(added a new test for 10.5 release and the new test is called Changes10_5.java
1)upgradeTests/_Suite.java, Changes10_2.java, UpgradeRun.java
I will appreciate any feedback on the patch.