最近在使用DB2的时候,老是出现57016的错误(由于表不活动,不能对表进行访问),特别是在数据导入和导出的时候,必定会出现,每次都要执行reorg一下。在reorg的过程中,如果该表有外键依赖,且外键表也不活动,就需要先对外键表进行reorg。
一次数据的导入,接近30%的表不能访问了,而且还有部分表的外键检查也被disable了,在reorg前,还要先恢复外键检查,非常麻烦。一个简单的办法是先删除所有外键,在reorg所有表之后再加上外键,但是这样还是可能在最后加外键的过程中表又不活动了。
所有我想对数据库的所有表按照外键依赖顺序进行排序,这样在reorg一张表之前,保证其依赖的外键表已经被reorg了。但是还是有一个问题,就是有些表有循环的外键依赖:对于这样的问题,只能通过先删除外键,reorg之后再加上。
下面是我用JAVA写的排序算法,包含了循环依赖检查(TableFK是一个非常简单的类,就不列出了):
package test;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class ReorgAllTable {
private static final String SCHEMA = "TEST";
private final static String DB_URL = "jdbc:db2://localhost:50000/SIMPLE";
private final static String DB_DRIVER = "com.ibm.db2.jcc.DB2Driver";
private final static String DB_USERNAME = "TEST";
private final static String DB_PASSWORD = "TEST";
private Map<String, List<TableFK>> tableFKListMap = new HashMap<String, List<TableFK>>();
private Map<String, TableFK> tableFKMap = new HashMap<String, TableFK>();
private List<String> sortedTableList = new ArrayList<String>();
private Set<String> cycleFKSet = new HashSet<String>();
static {
try {
Class.forName(DB_DRIVER);
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
ReorgAllTable reorgAllTable=new ReorgAllTable();
reorgAllTable.sortTableSet();
String sql = reorgAllTable.createSortedSql();
System.out.println(sql);
}
public void sortTableSet() throws Exception {
fetchTableFkListMap();
Set<String> tableSet=tableFKListMap.keySet();
Iterator<String> tableSetIter = tableSet.iterator();
while (tableSetIter.hasNext()) {
String table = tableSetIter.next();
sortTable(table, null);
}
}
private void sortTable(String table, Set<String> pCycleCheckSet) {
List<TableFK> tableFKList = getTableFKList(table);
if (tableFKList == null || tableFKList.isEmpty()) {
addTable(table);
return;
}
for (TableFK tableFK : tableFKList) {
String pTable = tableFK.PKTABLE_NAME;
if (!sortedTableList.contains(pTable)) {
// 循环依赖检查
Set<String> cycleCheckSet = new HashSet<String>();
if (pCycleCheckSet != null) {
cycleCheckSet.addAll(pCycleCheckSet);
}
String fkName = tableFK.FK_NAME;
if (!cycleCheckSet.contains(fkName)) {
cycleCheckSet.add(fkName);
} else {
cycleFKSet.add(fkName);
continue;
}
// 递归查找
sortTable(pTable, cycleCheckSet);
//
cycleCheckSet.clear();
cycleCheckSet = null;
}
}
addTable(table);
}
private void addTable(String table) {
if (!sortedTableList.contains(table)) {
sortedTableList.add(table);
}
}
private Map<String, List<TableFK>> fetchTableFkListMap() throws Exception {
Connection connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
DatabaseMetaData metaData = connection.getMetaData();
List<String> tableList = fetchTableList(connection,metaData);
for (String table : tableList) {
List<TableFK> tableFKList = new ArrayList<TableFK>();
ResultSet importedKeys = metaData.getImportedKeys(null, SCHEMA, table);
while (importedKeys.next()) {
TableFK tableFK = new TableFK();
tableFK.table = table;
tableFK.PKTABLE_SCHEM = importedKeys.getString("PKTABLE_SCHEM");// 引用的外键表模式
tableFK.PKTABLE_NAME = importedKeys.getString("PKTABLE_NAME");// 引用的外键表名
tableFK.PKCOLUMN_NAME = importedKeys.getString("PKCOLUMN_NAME");// 引用的外键表主键列名
tableFK.FKTABLE_SCHEM = importedKeys.getString("FKTABLE_SCHEM");// 主表模式
tableFK.FKTABLE_NAME = importedKeys.getString("FKTABLE_NAME");// 主表名
tableFK.FKCOLUMN_NAME = importedKeys.getString("FKCOLUMN_NAME");// 主表外键列名
tableFK.FK_NAME = importedKeys.getString("FK_NAME");// 主表外键名
tableFK.PK_NAME = importedKeys.getString("PK_NAME");// 引用的外键表主键名
tableFKList.add(tableFK);
tableFKMap.put(tableFK.FK_NAME, tableFK);
}
importedKeys.close();
// System.out.println(tableFKList.toString());
tableFKListMap.put(table, tableFKList);
}
connection.close();
return tableFKListMap;
}
private List<String> fetchTableList(Connection connection, DatabaseMetaData metaData) throws SQLException {
List<String> tableList = new ArrayList<String>();
//也可以使用DatabaseMetaData.getTables(null,SCHEMA,"%",new String[]{"TABLE"})来获取所有表
PreparedStatement prepareStatement = connection.prepareStatement("select name from sysibm.systables where type='T' and creator=?");
prepareStatement.setString(1, SCHEMA);
ResultSet rs = prepareStatement.executeQuery();
while (rs.next()) {
String table = rs.getString(1);
tableList.add(table);
}
rs.close();
prepareStatement.close();
return tableList;
}
private TableFK getTableFK(String tableFKName) {
return tableFKMap.get(tableFKName);
}
private List<TableFK> getTableFKList(String table) {
return tableFKListMap.get(table);
}
public String createSortedSql() {
StringBuilder sb = new StringBuilder();
for (String fkName : cycleFKSet) {
TableFK tableFK = getTableFK(fkName);
String sqlDropFK = sqlDropFK(tableFK);
sb.append(sqlDropFK);
}
for (String table : sortedTableList) {
String sqlCheckFk = sqlCheckFk(table);
sb.append(sqlCheckFk);
String sqlReorg = sqlReorg(table);
sb.append(sqlReorg);
}
for (String fkName : cycleFKSet) {
TableFK tableFK = getTableFK(fkName);
String sqlCreateFK = sqlCreateFK(tableFK);
sb.append(sqlCreateFK);
}
return sb.toString();
}
private String sqlCheckFk(String table) {
return "SET INTEGRITY FOR " + tableName(table) + " IMMEDIATE CHECKED;\n";
}
private String sqlReorg(String table) {
return "REORG TABLE " + tableName(table) + ";\n";
}
private String sqlDropFK(TableFK tableFK) {
return "ALTER TABLE " + tableName(tableFK) + " DROP FOREIGN KEY " + tableFK.FK_NAME + ";\n";
}
private String sqlCreateFK(TableFK tableFK) {
return "ALTER TABLE " + tableName(tableFK) + " ADD CONSTRAINT " + tableFK.FK_NAME + " FOREIGN KEY(\"" + tableFK.FKCOLUMN_NAME
+ "\") REFERENCES " + tableName(tableFK.PKTABLE_NAME) + "(\"" + tableFK.PKCOLUMN_NAME
+ "\") ON DELETE RESTRICT ON UPDATE RESTRICT ENFORCED ENABLE QUERY OPTIMIZATION;\n";
}
private String tableName(String table) {
return SCHEMA + "." + table;
}
private String tableName(TableFK tableFK) {
return SCHEMA + "." + tableFK.table;
}
}
分享到:
相关推荐
使用eclipse获取mysql数据库中的表结构以及主外键
数据库主外键详解,带代码片段。让你直观了解数据库的主外键设置
项目中需要把一个数据库的部分表插入到另外一个数据库,设计到外键约束,写了一个程序对主表子表进行排序,插入的时候再也不用去考虑外键约束问题了
内包含外键约束模式,数据库的视图基本操作
本文档是数据库的外键约束方式指南 需要的朋友请下载 保证可用
数据库主键和外键设计的原则
达梦数据库
Mysql数据库如何删除某一个数据库的所有外键并不删除相应的索引,一个sql脚本解决,无需知道外键的名称。
本文介绍在在MySQL数据库中定义数据表、设置索引、定义外键、级联等操作。
在比较复杂的数据库中,经常要处理表中的外键关系,本人写了一些处理外键的方法,还忘诸位多多指点……
删除数据库的所有表、存储过程、主键、外键、索引等
就是为程序员提供了一条可以查询表的主键被其他表引用为外键情况的查询
用于查询某个数据库下所有表的外键约束情况,以便统一分析外键约束是否合理;主要查询出外键的,是否级联删除,级联更新,强制外键约束,强制复制约束始终状态。如想使用别的状态,请自行添加。下载的童鞋别下载错了...
NULL 博文链接:https://yanzhenwei.iteye.com/blog/850724
NULL 博文链接:https://zhouhaitao.iteye.com/blog/2113955
省表两个属性 id和名称 市与县都是三个属性 外键已建立好 非常全
本文介绍了在MySQL数据库中定义外键的方法。
删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心。但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情;另外在测试的时候,也有需要重新创建数据库...
根据主外键关系递归生成drop语句,请在plsql developer中执行
禁用、启用外键约束和触发器