Sybase清理海量历史数据

Sybase清理海量历史数据

Sybase清理海量历史数据(单张表几亿数据真实环境使用中)

之前在试验了bcp,传统的正向delete方法皆不可取的情况下，考虑使用select into 反向做法，把要保留的数据通过select into 法重新创建对象，

达到了数据快速进入新表，并对新表进行索引默认值重建，即达到了数据清理，也达到了数据清理后索引不会失效的目的，还腾出了碎片空间。 前期采用了静态的处理方式，在由于不同地方部署版本不同导致数据库结构也不完全相同情况下，需要ddlgen方式拿回的ddl脚本然后对静态清理程序进行对比修改，非常繁琐，头晕眼花对的，后来考虑到sybase的对象定义信息都保留的系统数据字典中，通过分析数据字典的方式进行对象的动态重构，从而省去了程序对照修改的累事。 以下看我实际处理的步骤思路以及具体脚本：

1.清理程序基本保证在深夜时间可以清理完毕，但是为了保证第二天海量数据运营级的数据库能够正常运行，所以做到了清理程序支持断点续清，清理未完也不影响第二天应用正常使用的目的。所以额外增加了清理日志表：

IF EXISTS (SELECT 1 FROM sysobjects o WHERE o.name = 'dba_delete_history_data' AND o.type = 'U') drop table dba_delete_history_data go

create table dba_delete_history_data (

id char(32) not null ,--流水id

table_name varchar(100) not null ,--表名

start_time datetime null ,--清理开始时间

end_time datetime null ,--清理结束时间

status int not null, --清理结果状态

default_fail_sql varchar(7000) null --默认值加了注释，导致语句过长产生问题 )

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

lock datarows go

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

2.单个对象表的清理封装过程up_delete_history_table

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

if exists (select 1

from sysobjects

where id = object_id('up_delete_history_table') and type = 'P')

drop procedure up_delete_history_table go

create procedure up_delete_history_table @objname varchar(100),--对象表名

@condition_name varchar(1000),--清理条件 @is_bak varchar(1),--清理后是否备份原表

@server_lock_scheme varchar(50) --服务的锁级别配置 as

declare @start_time datetime declare @end_time datetime declare @indid int

declare @keys varchar(1024) declare @msg varchar(1024)

declare @create_tmp_str varchar(1024) declare @drop_temp varchar(1024)

declare @lock_datarows_str varchar(100) declare @pk_str varchar(1024) declare @start_str varchar(100) declare @default_str varchar(8000) declare @index_str varchar(1024) declare @grant_str varchar(1024) declare @ddl_str varchar(1024) declare @rename_str varchar(1024) declare @end_str varchar(100)

declare @column_name varchar(1024) declare @pre_column_name varchar(1024) declare @default_name varchar(255) declare @new_id char(32)

declare @data_str varchar(10) declare @sysstat2 int

declare @tab_lock_scheme varchar(50)

begin

set nocount on

set @start_time=getdate()

set @data_str=right(convert(varchar(10),getdate(),112),4) select @new_id =newid()

insert into dba_delete_history_data

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

select @new_id,@objname,@start_time,null,0,''

set @start_str =@objname+' data cleaning start......' print @start_str

set @drop_temp=\"if exists(select 1 from sysobjects where type='U' and name='\"+@objname+\"_tmp') begin truncate table \"+@objname+\"_tmp drop table \"+@objname+\"_tmp end\" exec (@drop_temp) set @create_tmp_str='select * into '+@objname+'_tmp from '+@objname+' '+@condition_name exec (@create_tmp_str) if @@error != 0 return -1

create table #temp (

id int identity,

column_name varchar(200), default_name varchar(7500) )

declare @n int declare @rows int select @n=1

insert #temp(column_name,default_name) select c.name,d.text

from syscomments d,sysprocedures p ,syscolumns c,sysobjects o

where d.id = c.cdefault and p.id=d.id and c.id=o.id and o.type='U' and o.name=@objname

and p.sequence=0 and p.status & 4096 =4096 order by d.id,d.colid select @rows = @@rowcount select @pre_column_name='' while @n <= @rows begin

select @column_name=column_name,@default_name=default_name from #temp where id=@n if @n=1

select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name

if @n>1 and @column_name!= @pre_column_name begin

exec (@default_str) if @@error != 0

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

begin

update dba_delete_history_data set default_fail_sql=default_fail_sql+left(@default_str,400)+' ' where table_name=@objname end

select @default_str ='alter table '+@objname+'_tmp replace '+@column_name+' '+@default_name end

if @n>1 and @column_name = @pre_column_name

select @default_str=@default_str+@default_name

select @pre_column_name=@column_name select @n = @n + 1 end

if @rows >0

exec (@default_str) if @@error != 0 begin

update dba_delete_history_data

set default_fail_sql=default_fail_sql+left(@default_str,400)+' ' where table_name=@objname end

drop table #temp

if @@trancount = 0

begin set chained off end

set transaction isolation level 1

if @objname like \"%.%.%\" and substring(@objname, 1, charindex(\".\1) != db_name()

begin raiserror 17460 end

if not exists (select id from sysobjects where id = object_id(@objname)) begin raiserror 17461 end select @indid = min(indid)

from sysindexes where id = object_id(@objname) and indid > 0 and indid < 255 if @indid is null begin

exec sp_getmessage 170, @msg output print @msg end

while @indid is not null begin

declare @i int

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

declare @thiskey varchar(30)

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

declare @sorder char(4) declare @lastindid int select @keys = \"\

while @i <= 31 begin

select @thiskey = index_col(@objname, @indid, @i) if (@thiskey is null) begin

goto keysdone end

if @i > 1 begin

select @keys = @keys + \ end

select @keys = @keys + @thiskey

select @sorder = index_colorder(@objname, @indid, @i) if (@sorder = \"desc\")

select @keys = @keys + \" \" + @sorder select @i = @i + 1 end

keysdone:

if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048) begin

select @pk_str='alter table '+@objname+'_tmp add constraint '+name+' primary key nonclustered ('+@keys+')'

from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 = 2048 exec (@pk_str) end if exists(select 1 from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048) begin

select @index_str='create index '+name+' on '+@objname+'_tmp('+@keys+')'

from sysindexes where id = object_id(@objname) and indid = @indid and status & 2048 <> 2048

exec (@index_str) end

select @lastindid = @indid select @indid = null

select @indid = min(indid) from sysindexes where id = object_id(@objname) and indid > @lastindid and indid < 255 end

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

create table #temp_protect (

id int identity,

table_name varchar(200), user_name varchar(200), action int, protecttype int )

declare @protect_n int declare @protect_rows int

declare @protect_str varchar(200) select @protect_n=1

insert #temp_protect(table_name,user_name,action,protecttype) select o.name,u.name,p.action,p.protecttype

from sysprotects p,sysobjects o,sysusers u where p.id=o.id and u.uid=p.uid and o.name=@objname

select @protect_rows = @@rowcount while @protect_n <= @protect_rows begin

select @protect_str=(case when protecttype=0 then 'grant with grant ' when protecttype=1 then 'grant '

when protecttype=2 then 'revoke ' end) + (case when action=151 then 'references' when action=167 then 'set proxy'

when action=187 then 'set statistics on' when action=188 then 'set statistics off' when action=193 then 'select' when action=195 then 'insert' when action=196 then 'delete' when action=197 then 'update'

when action=198 then 'create table' when action=203 then 'create database' when action=205 then 'grant' when action=206 then 'revoke'

when action=207 then 'create view' when action=221 then 'create trigger' when action=222 then 'create procedure' when action=224 then 'execute'

when action=228 then 'dump database' when action=233 then 'create default' when action=235 then 'dump transaction' when action=236 then 'create rule'

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

when action=253 then 'connect'

when action=282 then 'delete statistics' when action=317 then 'dbcc'

when action=320 then 'truncate table' when action=326 then 'update statistics' when action=347 then 'set tracing' end) + ' on '+ table_name+ '_tmp to '+ user_name

from #temp_protect where id = @protect_n exec (@protect_str)

select @protect_n = @protect_n + 1 end

drop table #temp_protect

if(lower(@is_bak)='y') begin

set @drop_temp=\"if exists(select 1 from sysobjects where type='U' and name='\"+@objname+'_'+@data_str+\"') begin truncate table \"+@objname+'_'+@data_str+\" drop table \"+@objname+'_'+@data_str+\" end\" exec (@drop_temp)

set @rename_str='exec sp_rename '+@objname+','+@objname+'_'+@data_str exec (@rename_str) set @rename_str=''

----更改备份表的lock schema以达到重新编译表对象的目的

select @sysstat2 = sysstat2 from sysobjects where name = @objname+'_'+@data_str and type = 'U'

if @sysstat2 & 8192 = 8192 --原为allpages begin

set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock datarows'

exec (@lock_datarows_str) end

else if @sysstat2 & 16384 = 16384 --原为datapages begin

set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock datarows'

exec (@lock_datarows_str) end

else if @sysstat2 & 32768 = 32768 --原为datarows begin

set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock datapages'

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

exec (@lock_datarows_str)

set @lock_datarows_str='alter table '+@objname+'_'+@data_str+' lock datarows'

exec (@lock_datarows_str) end end

if(lower(@is_bak)='n') begin

set @ddl_str='truncate table '+@objname exec (@ddl_str)

set @ddl_str='drop table '+@objname exec (@ddl_str) end

set @rename_str='exec sp_rename '+@objname+'_tmp,'+@objname exec (@rename_str)

--更改新的正式表的lock schema以达到重新编译表对象的目的

select @sysstat2 = sysstat2 from sysobjects where name = @objname and type = 'U' if @sysstat2 & 8192 = 8192 --原为allpages begin

set @lock_datarows_str='alter table '+@objname+' lock datarows' exec (@lock_datarows_str) end

else if @sysstat2 & 16384 = 16384 --原为datapages begin

set @lock_datarows_str='alter table '+@objname+' lock datarows' exec (@lock_datarows_str) end

else if @sysstat2 & 32768 = 32768 --原为datarows begin

set @lock_datarows_str='alter table '+@objname+' lock datapages' exec (@lock_datarows_str)

set @lock_datarows_str='alter table '+@objname+' lock datarows' exec (@lock_datarows_str) end

set @end_str=@objname+' data cleaning complete!' print @end_str set @end_time=getdate()

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

update dba_delete_history_data set status=1,end_time=@end_time

where id=@new_id and default_fail_sql ='' set nocount off end go

sp_procxmode up_delete_history_table, anymode go

grant execute on up_delete_history_table to sms go

grant execute on up_delete_history_table to web go

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

3.多表传入人机交互时间作为整体任务，

进行同一分割时间点的批任务清理过程up_delete_history_data

if exists (select 1

from sysobjects

where id = object_id('up_delete_history_data') and type = 'P')

drop procedure up_delete_history_data go

create procedure up_delete_history_data

@objnames varchar(8000), --多表字符串，表名间用 逗号隔开 @history_time varchar(20), --清理时间点 @is_bak varchar(1) --是否备份原表标记 as

declare @condition_name varchar(1000) declare @objname varchar(50) declare @lock_scheme varchar(50) select @objnames=','+@objnames+',' begin

if(lower(@is_bak) not in ('n','y')) begin

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

print \"the third parameter means is bakup all source tables, must be 'n' or 'y'\" return -1 end

if @objnames is null or char_length(@objnames)=0 begin

print \"please input source tables\" return -1 end

if @history_time is null or @history_time>getdate() begin

print \"please input history_time and histroy_time must less than current date\"

return -1 end

select @lock_scheme=value2 from master..sysconfigures where config=327 and name='lock scheme'

if charindex(',ew_sending_queue,',@objnames) > 0 begin

set @objname='ew_sending_queue'

set @condition_name='where send_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',sms_sending,',@objnames) > 0 begin

set @objname='sms_sending'

set @condition_name='where send_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',sms_receiving,',@objnames) > 0 begin

set @objname='sms_receiving'

set @condition_name='where receive_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',sms_timing,',@objnames) > 0 begin

set @objname='sms_timing'

set @condition_name='where creation_time > convert(char(10),dateadd (dd ,-5,getdate()),111)'

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

end

if charindex(',si_record,',@objnames) > 0 begin

set @objname='si_record'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',msg_comment,',@objnames) > 0 begin

set @objname='msg_comment'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',msg_homework,',@objnames) > 0 begin

set @objname='msg_homework'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',msg_notice,',@objnames) > 0 begin

set @objname='msg_notice'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',msg_sending,',@objnames) > 0 begin

set @objname='msg_sending'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',msg_receiving,',@objnames) > 0 begin

set @objname='msg_receiving'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',ex_fjabt_student_record,',@objnames) > 0 begin

set @objname='ex_fjabt_student_record'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',ex_fjabt_teacher_record,',@objnames) > 0

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

begin

set @objname='ex_fjabt_teacher_record'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',adc_interface_log,',@objnames) > 0 begin

set @objname='adc_interface_log'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',adc_userbind_log,',@objnames) > 0 begin

set @objname='adc_userbind_log'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',fee_pay_detail,',@objnames) > 0 begin

set @objname='fee_pay_detail'

set @condition_name='where pay_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',log_card_login,',@objnames) > 0 begin

set @objname='log_card_login'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',sys_operation_log,',@objnames) > 0 begin

set @objname='sys_operation_log'

set @condition_name='where operated_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_student_signin,',@objnames) > 0 begin

set @objname='st_student_signin'

set @condition_name='where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_teacher_signin,',@objnames) > 0 begin

set @objname='st_teacher_signin'

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

set @condition_name='where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_device,',@objnames) > 0 begin

set @objname='st_device'

set @condition_name='where stat_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_fjabt_student_record,',@objnames) > 0 begin

set @objname='st_fjabt_student_record' set @condition_name='(index pk_st_fjabt_student_record) where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_fjabt_teacher_record,',@objnames) > 0 begin

set @objname='st_fjabt_teacher_record' set @condition_name='(index pk_st_fjabt_teacher_record) where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_school,',@objnames) > 0 begin

set @objname='st_school'

set @condition_name='where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_user,',@objnames) > 0 begin

set @objname='st_user'

set @condition_name='where statistic_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',log_module_access,',@objnames) > 0 begin

set @objname='log_module_access'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',log_user_operation,',@objnames) > 0 begin

set @objname='log_user_operation'

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',eb_login_log,',@objnames) > 0 begin

set @objname='eb_login_log'

set @condition_name='where login_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',fp_call_bill,',@objnames) > 0 begin

set @objname='fp_call_bill'

set @condition_name='where creation_time > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_card_class,',@objnames) > 0 begin

set @objname='st_card_class'

set @condition_name='(index pk_st_card_class) where stat_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_student_class,',@objnames) > 0 begin

set @objname='st_student_class'

set @condition_name='(index pk_st_student_class) where stat_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_sms_analysis_class,',@objnames) > 0 begin

set @objname='st_sms_analysis_class'

set @condition_name='(index pk_st_sms_analysis_class) where stat_date > '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

if charindex(',st_student_record,',@objnames) > 0 begin

set @objname='st_student_record' set @condition_name='(index idx_school_id) where month >= convert(int,datepart(year,+'''+@history_time+'''))*100+convert(int,datepart(month,+'''+@history_time+'''))'

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end

授课：XXX

如果您需要使用本文档，请点击下载按钮下载!

if charindex(',ew_score_detail,',@objnames) > 0 begin

set @objname='ew_score_detail'

set @condition_name='where process_time> '''+@history_time+''''

exec up_delete_history_table @objname,@condition_name,@is_bak,@lock_scheme end end go

sp_procxmode up_delete_history_data, anymode go

（注：可编辑下载，若有不当之处，请指正，谢谢!）

授课：XXX