Hi Thomas, I have been doing some research on how i can get the column name(s) that the index corresponds to (see the 2nd *bold* section of the SQL) and haven't found anything yet. Any ideas? I would also like to know if a given index is a unique index or not? The reason I say this is because I looked at "Create Index..." statements and there are 2 flavors - ones with the *Unique *qualifier and the others without.
statement.executeUpdate(“CREATE *UNIQUE* INDEX “+indexNameString+” ON DeviceInfo *(DeviceID)*”); Thanks a lot for your help! Kal On Thu, Mar 5, 2009 at 11:15 AM, Thomas J. Taylor < [email protected]> wrote: > Hi Kal, > > > > I’ll check to see if I can find the code/process that I used back then to > solve the issue. Since I only had one (remote) Derby installation causing > problems, once I figured out the way to resolve the problem (drop & recreate > index), I probably (1) used DBLook to identify the corrupt (missing) > indexes, then used SQurilleL to (2) identify the names of the keys through > the GUI, (3) write the DDL to drop and re-create the indexes. > > > > You should be able to use JDBC to get the same index information and > drop/create the index that way; however, the challenge is identifying the > corrupt indices. Perhaps this might work? > > > > Connection connection; // existing db connection > > Statement statement = connection.createStatement(); > > try { > > // test table to confirm corrupt index: SQLException is thrown if > corrupt > > statement.executeQuery(“SELECT DeviceID, DeviceName, DeviceType FROM > DeviceInfo WHERE DeviceID=1”); > > } catch (SQLException ex) { > > // retrieve index information for the corrupt table > > // > http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java.lang.String, > java.lang.String, java.lang.String, boolean, > boolean)<http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html%23getIndexInfo%28java.lang.String,%20java.lang.String,%20java.lang.String,%20boolean,%20boolean%29> > > DatabaseMetaData databaseMetaData = conn.getMetaData(); > > ResultSet resultSet = databaseMetaData.getIndexInfo(null, null, > “DeviceInfo”, false, false); > > // for each index, drop & recreate the index > > while (resultSet.hasNext()) { > > // get the name of the > > String indexNameString = resultSet.getString(“INDEX_NAME”); > > statement.executeUpdate(“DROP INDEX ”+indexNameString+” ON > DeviceInfo); > > // recreate index: > http://db.apache.org/derby/docs/10.2/ref/rrefsqlj20937.html > > statement.executeUpdate(“CREATE UNIQUE INDEX “+indexNameString+” > ON DeviceInfo (DeviceID)”); > > } > > } > > > > Thomas Taylor > > INFOTECH Soft, Inc. > > > > *From:* Kalyan Inuganti [mailto:[email protected]] > *Sent:* Thursday, March 05, 2009 11:10 AM > *To:* [email protected] > *Subject:* Derby 10.1 -> 10.2 upgrade issue > > > > Hi, > > I am reaching out to you guys for some help with a Derby indexing issue > that we have run into at Monsanto, St. Louis. The issue is pretty much the > same issue that was reported by Thomas J. Taylor in 2007 (The link is > provided below). > > *Brief Description:* > > I have a database that was originally created with Derby 10.1.1.0 and was > > recently upgraded to Derby 10.2.2.0. I've performed this upgrade on several > > copies of the same database schema (each created on different computers, > > but with the same version of Java (1.5.0_07) and Derby (10.1)). > > > For all but one of the database upgrades, it worked correctly. However, in > > one case, it appears that the PRIMARY KEY and FOREIGN KEY constraints have > > been lost/corrupted. When I use DBLook to check a 'working' database, I see > > the appropriate constraints for keys. However, on the 'defective' database, > > these constraints are missing. > > > We have over 80 tables in the DB and over 1000 users. Even though we have > only 2 reported occurrences of this issue so far, it might be more > widespread. It is a nightmare to manually identify the corrupted indexes for > each occurrence. Any thoughts on how we can tackle this through a > programmatic approach? > > Here is the link to the old report: > > > http://mail-archives.apache.org/mod_mbox/db-derby-user/200704.mbox/%[email protected]%3e > > Thanks, > Kal >
