数据库分年度统计数据量

最新推荐文章于 2024-08-21 23:31:18 发布

原创

最新推荐文章于 2024-08-21 23:31:18 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

不得不说还有半年就要毕业了，还有写审计数据分析报告。

正题，分年度统计数据量，思路：养老保险领域数据库有14个每个有几百张表，有的表有时间属性，有的没有时间属性，

第一步，根据数据库自带函数统计每张表数据量

第二步，人工挑选每张表时间属性字段并配置表格HELP1

第三步，计算当前年份记录数在整张表的百分比，统计即可。

数据库 DB2

语言 Java

贴代码

package com.Audit.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

public class test1 {

/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub

String domain = "606";
String object[] = {"shengzhi","haerbin","qiqihaer","jixi","hegang","shuangyashan","daqing","yichun","jiamusi","qitaihe","mudanjiang","hehei","suihua","daxinganling"};
int pubdata = 16640;
String SchemaName = "administrator";
String objectDBName[] = {"GB_DB1","GB_DB5","yl2302","yl2303","yl2304","yl2305","yl2306","yl2307","yl2308","yl2309","yl2310","yl2311","yl2312","yl2327"};
test1 t1 = new test1();
for(int i = 0;i<object.length;i++){
t1.execute(domain, object[i], pubdata, objectDBName[i],SchemaName);
}

}

public void execute (String domain,String object,int pubdata,String objectDBName,String SchemaName){
List<List<String>> queryResultList1 = new ArrayList<List<String>>();

//conn1
Connection conn1 = test1.getConn1();
Statement stmt1 = test1.getStatement(conn1);
// String sql = "Select c.c1, c.cc ,c.c2*d.d2"
// +"From ( Select a.a1 c1, a.aa cc,cast(a.a2 as float)/cast(b.b2 as float) c2"
// +"From ( SELECT 'AA02' as a1 , AA02. AAE001 aa,count(*) a2 FROM AA02 Group by AA02. AAE001"
// +") a inner join ("
// +"SELECT 'AA02' as b1,count(*) b2 FROM AA02 ) b on a.a1=b.b1 ) c inner join "
// +"(select TABNAME d1,SUM(DATA_OBJECT_P_SIZE+INDEX_OBJECT_P_SIZE+LOB_OBJECT_P_SIZE+LONG_OBJECT_P_SIZE+XML_OBJECT_P_SIZE) d2 "
// +"FROM SYSIBMADM.ADMINTABINFO T WHERE TABSCHEMA='ADMINISTRATOR' group by TABNAME) d on c.c1 = d.d1;";

// String sql =""+
// "Select c.c1, c.cc ,c.c2*d.d2 "+
// "From ( Select a.a1 c1, a.aa cc,cast(a.a2 as float)/cast(b.b2 as float) c2 "+
// "From ( SELECT '"+tableName+"' as a1 , "+tableName+"."+timeName+" aa,co