db2 drop table（db2的库，drop table不释放空间，怎么办前几天是做了数据库的归档，现在已经去掉了d）

本文目录

• db2的库，drop table不释放空间，怎么办前几天是做了数据库的归档，现在已经去掉了d
• db2表分区删除
• db2 表数据分区 是否可以修改
• db2 建立一张临时表，并设置几个字段

db2的库，drop table不释放空间，怎么办前几天是做了数据库的归档，现在已经去掉了d

。。。一般数据所在表空间都是DMS的，是事先就分配好大小的容器。你得调整下容器的大小。

db2表分区删除

1.常规方法 delete 查询条件 1.1.删除效率较慢 1.2.经常确认归档日志是否占满 1.3.大的事务长期没有提交容易导致活动日志压力过大 2.detach效率较高 2.1.detach到临时表 db2  "alter table T_PERFORM detach partition PART221 into table perform_PART221"; 2.2.删除临时表 db2 drop  tableperform_PART221;

db2 表数据分区 是否可以修改

1.创建分区表，INCLUSIVE（包含),exclusive（不包含) CREATE TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" ( "REPORT_DATE" DATE, "MA_ACCT_NO" VARCHAR(100), "TRANS_DATE" DATE, "ORG_UNIT_ID" VARCHAR(15), "ORG_PROD_ID" VARCHAR(15), "CURR_CD" VARCHAR(15), "ACCT_NO" VARCHAR(15), "TRANS_NUM" VARCHAR(15), "TRANS_NO" VARCHAR(15), "TRANS_DIF" VARCHAR(15), "DEPOSIT_CHAR" VARCHAR(15), "DEPOSIT_BAL" DECIMAL(18,2), "TRANS_AMT" DECIMAL(18,2), "TRANS_TYPE" CHARACTER(1), "FLG" CHARACTER(1), "RATE" DECIMAL(18,6), "TRXMEM" DECIMAL(4,0)) IN "MA_DATA" INDEX IN "MA_INDEX" PARTITION BY RANGE ( "REPORT_DATE" NULLS LAST ) ( PARTITION PART0 STARTING ’2010-12-10’ INCLUSIVE ENDING ’2010-12-20’ INCLUSIVE ) ;ALTER TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" DATA CAPTURE NONE LOCKSIZE ROW APPEND OFF NOT VOLATILE;COMMENT ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" IS ’活期交易明细表’;COMMENT ON "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" ( "REPORT_DATE" IS ’数据日期’, "MA_ACCT_NO" IS ’管会账号’, "TRANS_DATE" IS ’营业日期’, "ORG_UNIT_ID" IS ’行所号’, "ORG_PROD_ID" IS ’业务品种 ’, "CURR_CD" IS ’币别’, "ACCT_NO" IS ’帐号’, "TRANS_NUM" IS ’交易序号’, "TRANS_NO" IS ’交易代号’, "TRANS_DIF" IS ’交易区别’, "DEPOSIT_CHAR" IS ’存款性质 ’, "DEPOSIT_BAL" IS ’存款余额’, "TRANS_AMT" IS ’交易金额’, "TRANS_TYPE" IS ’交易别’, "FLG" IS ’连动标志’, "RATE" IS ’汇率’ );GRANT CONTROL ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2";GRANT SELECT, INSERT, UPDATE, ALTER, DELETE, INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2" WITH GRANT OPTION;GRANT SELECT, INSERT, UPDATE, ALTER, DELETE, INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2" WITH GRANT OPTION;GRANT SELECT, INSERT, UPDATE, ALTER, DELETE, INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "MAPUB" WITH GRANT OPTION;2.增加分区,注意这里的INCLUSIVE，exclusive，这时只有2011-01-31的数据可以进行insertalter table MABAS.BAS_MID_TRANS_LIST_CK_BAK add partition LIST_CK_bak0131 STARTING ’2011-01-31’ INCLUSIVE ENDING ’2011-02-01’ exclusive3.insert 数据insert into MABAS.BAS_MID_TRANS_LIST_CK_BAK select * from MABAS.BAS_MID_TRANS_LIST_CK where report_date=date(’2011-01-31’);4.转移分区到临里表alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK detach partition LIST_CK_bak0131 into MABAS.BAS_MID_TRANS_LIST_CK_BAK15.删除生成的分区迁移表,注意这时分区表的分区（LIST_CK_bak0131）己经不存在了，如果要insert必须新增该分区drop table MABAS.BAS_MID_TRANS_LIST_CK_BAK1以下为应用写的shell脚本，并参照syscat.datapartitions 进行判断，如果有则删除分区，否则进行新建，以下为具体的脚本。. /home/odSUSEr1/.profile#配置文件SYSNAME=GDBMAMADS_HOME=/home/odsuser1/gdbma/etl#DS ConfigDSConfigFile=$MADS_HOME/dsconfig_gdbma#MARPT ETL2数据库#DB信息DBNAME=`awk ’FS="=" {if ($0~/^MABASDBName/) print $2}’ $DSConfigFile`DBUSR=`awk ’FS="=" {if ($0~/^MABASDBUser/) print $2}’ $DSConfigFile`DBPWD=`awk ’FS="=" {if ($0~/^MABASDBPassword/) print $2}’ $DSConfigFile`DBSCHEMA=`awk ’FS="=" {if ($0~/^MABASDBSchema/) print $2}’ $DSConfigFile`DBPWD=`$MADS_HOME/Encrypt/discrypt.sh $DBPWD`dbname=$DBNAMEuser=$DBUSRpasswd=$DBPWD#连接数据库db2 connect to $DBNAME user $DBUSR using $DBPWD 》/dev/nulldb2 set schema=$DBSCHEMA;#传递参数JOB_NAME=$1DELETE_DATE=$2#DELETE_DBSCHEMA=$1#DELETE_TAB=$2#DELETE_COL=$3#DELETE_TYPE=$4#DELETE_DATE=$5#DELETE_VALUE=$6delete_tab="select SCH_NAME,TAB_NAME,IF_PARTITION,TAB_DATE,DEL_VALUE from mabas.s_job_info_m t where JOB_NAME = ’"$JOB_NAME"’ "DEL_DATA=`db2 -t "$delete_tab"`if then delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE(’"$DELETE_DATE"’) "` else delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE(’"$DELETE_DATE"’) and $DEL_VALUE "` fifidone

db2 建立一张临时表，并设置几个字段

如果你要使用临时表可以用 adodb.connection.execute "create table 表名 ....." 来建立表，用完以后用 adodb.connection.execute "drop table 表名" 来删除该表。 ’比如我现在要使用一个名为 datatype 的表 ’请先引用 ADO (Microsoft ActiveX Data Objects 2.X) dim conn as new adodb.connection conn.connectiongstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\databaseName.mdb;User ID=userName;Password=userPassword;" conn.open conn.execute "create table datatype(aa binary,bb bit,cc smallint,dd AUTOINCREMENT(2,4),ee money,ff datetime,gg guid,hh single,ii double,jj smallint,kk long,ll image,mm memo,nn text(100),oo yesno)" ’当然，你也完全可以用 select into ... 语句来根据某个表生成临时表。 ’用完以后我可以 conn.execute "drop table datatype"