最近在使用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;
}
}
分享到：
相关推荐
1. 扫描和排序：数据库会根据关联条件对表进行扫描，并可能进行排序，以提高匹配效率。 2. 算子应用：使用相应的关联算法（如Nested Loop Join、Merge Join、Hash Join）找到匹配的行。 3. 结果合并：将匹配的行组合...
索引是对表中一列或多列的值进行排序的一种结构，可以快速访问表中的特定信息。通常情况下，搜索条件、主键、外键、排序字段都应建立索引。特别地，唯一索引可以保证字段值的唯一性，而主键索引为表的主键，它是一种...
综上所述，通过合理使用索引、避免或简化排序以及消除对大型表的顺序存取等方法，可以显著提升SQL查询的性能。这些技术不仅适用于特定的数据库系统（如Informix），也广泛适用于其他主流数据库管理系统。最终目的是...
19. **GROUP BY和HAVING子句**：`GROUP BY`用于根据一个或多个列的值对结果集进行分组，`HAVING`则用于过滤这些分组。 20. **MySQL中的EXPLAIN命令**：用于分析SQL查询的执行计划。 #### 三、性能优化 21. **识别...
MySQL是世界上最流行的关系型数据库管理系统之一，其性能和效率在很大程度上依赖于索引的使用。索引是一种特殊的数据结构，允许数据库快速访问和定位数据，而无需遍历整个表。以下是对MySQL常见面试题中关于索引的...
它通过对表中一列或多列的值进行排序，使得数据查询过程更为高效。索引的作用就像书籍的目录，能快速指引我们找到所需的信息。 ### 索引的优缺点 **优点：** 1. **提升查询速度**：索引能够快速定位到数据，减少全...
- **传统文件系统与数据库系统的区别**：文件系统主要用于简单的数据存储，而数据库系统则更侧重于提供结构化、一致性和安全性的支持。 - **数据库系统的主要组成部分**：包括硬件、软件、用户以及数据库本身。 - **...
在创建表时，经常会创建该表的主键、外键、唯一约束、Check约束等 语法结构 create table 表名( [字段名] [类型] [约束] ……….. CONSTRAINT fk_column FOREIGN KEY(column1,column2,…..column_n) ...
- **4.8.2 更改、删除外部表：** 对外部表进行修改或删除操作。 - **4.8.3 PRESS 示例：** 实际应用案例。 #### 五、子查询 **5.1 子查询的概念和类型** - **概念：** 子查询是在一个查询语句中嵌入另一个查询...
- **排他锁(Exclusive Lock)**：锁定整个表，不允许其他任何用户对表进行读写操作。 #### 9. 更新锁 - **题目解析**：使用FOR UPDATE子句可以在表上放置排他锁。 - **知识点说明**： - **FOR INSERT**: 不是合法...
- **行排序**：讲解如何按特定顺序排列查询结果。 - **LIMIT、OFFSET和TOP**：介绍如何限制查询结果的数量。 - **VALUES列表**：解释如何使用`VALUES`子句生成静态数据集。 - **WITH查询（公共表表达式）**：介绍...