Forums | Admin Discussion Forums: open-discussion Start New Thread Nested Flat Threaded Ultimate Show 25 Show 50 Show 75 Show 100   By: Philip Nelson RE: Schema Dumper Fails because of XML index [ reply ]   2008-02-25 22:28 Alex, I found, by digging into and calling things directly, that one of the four XML indexes created when you define one XPath based index is actually reported as a Type 3. What I've found is a plugin which allows me to generate my test databases using migrations rather than SchemaDumper. It's called migrate_test_db - http://www.elctech.com/2007/7/12/migrate_test_db and is saving me a lot of problems (since I also do other things with execute("sql statement") By: Alex Pitigoi RE: Schema Dumper Fails because of XML index [ reply ]   2008-02-24 20:56 A quick glance at DB2 Info Center reveals: http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0024071.htm "There are two internal and system generated indexes associated with XML columns. Whenever you create an XML column, an XML path index is automatically created by DB2(R) on the XML column. DB2 also creates a single XML regions index for all XML columns within a table." But the most relevant bit is: "Both the XML path and the XML regions indexes are recorded in SYSCAT.INDEXES. Note that these indexes are not recognized by any application programming interface that returns index metadata." By: Alex Pitigoi RE: Schema Dumper Fails because of XML index [ reply ]   2008-02-24 20:47 Hi Phil, First, you should probably open a bug to track this issue more effectively, but let me provide also some details that might give a bit more insight: 1. The 4 possible types of data retrieved by the driver through IBM_DB::statistics are the following (see driver API), and I am yet to confirm which would help with XML index: http://rubyibm.rubyforge.org/docs/driver/0.9.0/rdoc/classes/IBM_DB.html#M000019 * Return value:: Parameter type * 0 (SQL_TABLE_STAT):: The row contains statistics about the table itself. * 1 (SQL_INDEX_CLUSTERED):: The row contains information about a clustered index. * 2 (SQL_INDEX_HASH):: The row contains information about a hashed index. * 3 (SQL_INDEX_OTHER):: The row contains information about a type of index that is neither clustered nor hashed. 2. We might be able to override what's needed in the IndexDefinition if we can find enough details of the spec (as we had done with TableDefinition in order to support XML) We'll keep you posted with our investigation in this regards. Thanks for those details provided, Alex By: Philip Nelson Schema Dumper Fails because of XML index [ reply ]   2008-02-23 22:40 Alex / Antonio, I know this is pushing the boundaries a bit. However, my project is using pureXML within a RoR framework (accessing XML columns through stored procedures). I've applied your patches (and some of my own) to schema_dumper.rb to allow me to prepare my test database. However this is failing because it fails to differentiate between an XML index and an ordinary index (and fails because you can't create an ordinary index on an XML column). I've dug into this a bit. The indexes method in schema_dumper.rb calls the ActiveRecord indexes method. This in turn uses the IBM_DB statistics method to construct an IndexDefinition object. Obviously we can't extend the IndexDefinition object. But I also had a look at the statistics method to see if there is anything at that level which differentiates between the different index types. It seems that there are four definitions of "type" but virtually everything gets put into type 3 (INDEX_OTHER). So I'm a bit stuck on exactly how to approach this. Probably having to check and eliminate XML indexes using SQL first ? Any help appreciated. Phil