Kal, According to the Java 5 docs, the getIndexInfo ResultSet should contain all of the required information (INDEX_NAME, COLUMN_NAME, NON_UNIQUE) - if the index consists of multiple columns, then multiple ResultSet entries will be returned for a given INDEX_NAME, and you'll have to use ORDINAL_POSITION to recreate the sequencing of columns within the index.
If not, you might try looking at the thread/link that Rick Hillegas sent (http://www.nabble.com/How-can-I-fetch-constraint-attribute-on-Column-Level- from-SYS-Tables---td19554573.html#a19554573) Thomas INFOTECH Soft, Inc. See DatabaseMetaData#getIndexInfo(): http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getIn dexInfo(java.lang.String, <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html#getI ndexInfo(java.lang.String, java.lang.String, java.lang.String, boolean, boolean)> java.lang.String, java.lang.String, boolean, boolean) From: Kalyan Inuganti [mailto:kinuga...@gmail.com] Sent: Thursday, March 05, 2009 1:44 PM To: tho...@infotechsoft.com; derby-user@db.apache.org Subject: Re: Derby 10.1 -> 10.2 upgrade issue 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 <thomas.tay...@infotechsoft.com> 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#getIn dexInfo(java.lang.String, <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html%23ge tIndexInfo%28java.lang.String,%20java.lang.String,%20java.lang.String,%20boo lean,%20boolean%29> java.lang.String, java.lang.String, boolean, boolean) 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:kinuga...@gmail.com] Sent: Thursday, March 05, 2009 11:10 AM To: derby-user@db.apache.org 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/%3C462D5D f8.80...@gmail.com%3e Thanks, Kal
