db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "DatabaseConsistencyCheck" by KatheyMarsden
Date Tue, 17 May 2011 23:22:20 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The "DatabaseConsistencyCheck" page has been changed by KatheyMarsden.
http://wiki.apache.org/db-derby/DatabaseConsistencyCheck?action=diff&rev1=5&rev2=6

--------------------------------------------------

  
  If you found corrupt indexes in the consistency check, you may be able to drop and recreate
them to get them back into a consistent state.
  
-   1. First exit ij or the jvm accessing the database, and make a tar zip copy of the database,
preserving date stamps if possible. This can be used to try to understand the root cause of
the corruption and to start over if you make a mistake or make things worse. A frequent cause
is corruption after restore if proper online database procedures were not used. Ask derby-user@apache.org
for help.
+   1. First exit ij or the jvm accessing the database, and make a tar zip copy of the database,
preserving date stamps if possible. This can be used to try to understand the root cause of
the corruption and to start over if you make a mistake or make things worse. A frequent cause
is corruption after restore if proper online database backup procedures were not used. Ask
derby-user@apache.org for help understanding how the corruption occurred.
  
    1. Run dblook to get the ddl for the database, e.g.
      {{{ java org.apache.derby.tools.dblook -d jdbc:derby:mydb > mydb_ddl_sql.out }}}
@@ -109, +109 @@

      Then look in the dblook output for the index name for the exact syntax to recreate it.
       
    1. Do an offline compress of all the tables.  Below is some sample code for doing this:
-   [[attachment:CompressAll.java]]
+ {{{
+ import java.sql.*;
+ 
+ public class CompressAll {
+ 
+     public static void main(String[] args) throws Exception {
+ 	if (args.length == 0) {
+ 	    System.err.println("Usage: java CompressAll <url>");
+ 	    System.exit(1);
+ 	}
+ 	String url = args[0];
+ 	Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
+ 	Connection conn = DriverManager.getConnection(url);
+ 	Statement s = conn.createStatement();
+ 	ResultSet rs = s.executeQuery("SELECT SCHEMANAME, TABLENAME FROM sys.sysschemas s, sys.systables
t WHERE s.schemaid = t.schemaid   and t.tabletype = 'T'");
+ 
+ 	CallableStatement cs = conn.prepareCall ("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");

+ 	while (rs.next() ) {
+ 	    String schema = rs.getString(1);
+ 	    String table = rs.getString(2);
+ 	    System.out.println("Now compressing " + schema + " " + table);
+ 	    cs.setString(1,schema);
+ 	    cs.setString(2,table);
+ 	    cs.setShort(3, (short) 1);
+ 	    cs.execute();	    
+ 	}
+ 	// finally shutdown the database
+ 	try {
+ 	    DriverManager.getConnection(url +";shutdown=true");
+ 	}
+ 	catch (SQLException se) {
+ 	    if (! se.getSQLState().equals("08006")) {
+ 		System.out.println("Compress and shutdown complete");
+ 	    }
+ 	}
+     }
+ }
+ 
+ 
+ }}}
+  
    
    1. Without knowing the exact cause of the corruption, it is quite possible there is other
corruption unseen.  The only way to be 100% certain the database is ok is to manually check
the data, export it, create a new database using the dblook output and import the data.
  

Mime
View raw message