Re: Massive Delete
PERL ROCKS!!!!!!!!!!
----- Original Message ----
From: Quman <yquman@gmail.com>
To: bozon <curtis@crowson1.com>
Cc: informix-list@iiug.org
Sent: Tuesday, September 26, 2006 3:31:52 PM
Subject: Re: Massive Delete
I attached an esql.ec file. That can do our job.
If your table does not have unique ID column, you need modfy it.
I prefer using PERL to do it now. this is our early version.
Thanks,
Quman
/*
* File: CleanupRepTab.ec
*
* CleanupRepTab.ec is an ESQL/C program to cleanup database tables that
* are replicated by Informix Enterprise Replication server between multiple
* sites. For each table to be cleaned, there is a cleanup SQL WHERE clause
* stored in the db_cleanup_rep table. CleanupRepTab.ec will lookup the
* db_cleanup_rep table and delete the rows which satisfy the condition.
* db_cleanup_rep has the following schema,
* create table db_cleanup_rep (
* id serial not null ,
* tabname char(32) not null ,
* colname char(32) not null ,
* delcond varchar(255),
* status char(8),
* primary key (id) constraint db_cleanup_rep_pk );
*
* id: a serial number generated for each row.
* tabname: the name of the table to be cleaned, e.g., arch_recall_reqs,
* file_change_notice, etc.
* colname: a column name of the table, its type must be Integer and Unique.
* delcond: the condition used to delete rows of the table.
* status: "active" means the clean activity is on, "inactive" means off.
* The final results of cleanup are stored in two files: db_cleanup_sum.msg and
* db_cleanup_all.msg. db_cleanup_sum.msg contains the summary about
the deletion
* for each table. db_cleanup_all.msg contains all the ids of cleaned rows for
* each table.
*/
#include <stdio.h>
#include <time.h>
#include <sys/types.h>
#include <stdlib.h>
#include <sqlhdr.h>
#include <sqlca.h>
#define TABLIMIT 100 /*number of tables can be handled */
#define ROWLIMIT 320000 /*max number of rows in a table handled at one
time run*/
void deleteRows(char* tabname, char* colname,
char* delcond, FILE *fp_sum, FILE *fp_all){
int ids[ROWLIMIT];
int thecount=0;
EXEC SQL BEGIN DECLARE SECTION;
int theid;
char buff[255]; char buff2[255];
EXEC SQL END DECLARE SECTION;
/* select the row ids into a array*/
sprintf(buff, " SELECT %s FROM %s WHERE %s INTO TEMP toDelRow
WITH NO LOG \\n",
colname, tabname, delcond);
fputs(buff, fp_sum);
/*printf("stmt1 : %s\\n \\n", buff);*/
EXEC SQL EXECUTE IMMEDIATE :buff;
EXEC SQL PREPARE sele FROM 'select * from toDelRow';
EXEC SQL DECLARE C2 CURSOR FOR sele;
EXEC SQL OPEN C2;
for (; ; ) {
EXEC SQL FETCH C2 into :theid;
if ( sqlca.sqlcode != 0 || thecount > ROWLIMIT) { break; }
ids[thecount]=theid;
/*printf(" i= %d id= %d \\n", thecount,theid);*/
thecount++;
}
EXEC SQL CLOSE C2;
EXEC SQL DROP TABLE toDelRow;
/* delete from target table row by row */
for ( int i=0;i<thecount; i++) {
sprintf(buff2,"DELETE FROM %s WHERE %s = %d \\n",
tabname, colname, ids[i]);
/*printf(" stmt2 = %s \\n", buff2);*/
EXEC SQL begin work without replication;
EXEC SQL EXECUTE IMMEDIATE :buff2;
EXEC SQL commit work;
/* keep a log in the file*/
fputs(buff2, fp_all);
}
/* record summary deletion info*/
sprintf(buff,"===> %s rows deleted: %d \\n \\n", tabname, thecount);
fputs(buff, fp_sum);
}
int main(int argc, char *argv[]) {
static int mycount=0;
char* tname[TABLIMIT];
char* cname[TABLIMIT];
char* cond[TABLIMIT];
FILE *fp_sum, *fp_all;
fp_sum= fopen("db_cleanup_rep.sum","w+");
fp_all= fopen("db_cleanup_rep.all","w+");
time_t current_time; time(¤t_time);
fputs(ctime(¤t_time), fp_sum);
fputs(ctime(¤t_time), fp_all);
EXEC SQL BEGIN DECLARE SECTION;
int id;
char tabname[32];
char colname[32];
char delcond[255];
EXEC SQL END DECLARE SECTION;
/* allocate memory for arraies */
for (int i=0; i<TABLIMIT ; i++){
tname[i]=malloc(32);
cname[i]=malloc(32);
cond[i]=malloc(255);
}
/*select table clean info into arraies*/
EXEC SQL DATABASE 'noaa';
EXEC SQL PREPARE sel FROM
"SELECT * FROM db_cleanup_rep where status='active'";
EXEC SQL DECLARE C1 CURSOR FOR sel;
EXEC SQL OPEN C1;
for ( ;; ) {
EXEC SQL FETCH C1 into :id, :tabname, :colname, :delcond;
if ( sqlca.sqlcode != 0 ){ break; }
sprintf(tname[mycount],"%s",tabname);
sprintf(cname[mycount],"%s",colname);
sprintf(cond[mycount],"%s",delcond);
mycount++;
}
EXEC SQL CLOSE C1;
/*printf(" count= %d \\n", mycount);*/
/*handle it table by table*/
for ( int i=0;i<mycount; i++) {
/*printf("mycount= %d, i=%d, %s, %s\\n \\n", mycount, i,
cname[i], cond[i]);*/
deleteRows(tname[i],cname[i],cond[i], fp_sum, fp_all);
}
fclose(fp_sum); fclose(fp_all);
}
On 26 Sep 2006 12:04:25 -0700, bozon <curtis@crowson1.com> wrote:
> Quman,
> How does the script limit the number of records deleted? There isn't a
> command to do "delete first 50 from <table> " is there ?
> I did something similar to fix in place alters ( I of course updated a
> column that wasn't in an index or trigger, instead of deleting the
> record) but to get the records to work on I unloaded the primary key of
> the records (because you can't even "select first 50 <pk> from <table>
> into temp nibble_<table> with no log;)
>
> If you are going to do this during down time you can do the following:
>
> begin work;
>
> rename <table> to old_<table> ;
>
> create table <table> (...);
>
> alter table <table> type(raw);
> insert into <table> select * from old_<table> where <records_to_keep>;
> alter table <table> type(standard);
> drop table old_<table>;>
> <create indexes>
> <create constraints>
> <recreate fk constraints from other tables>
>
> commit work;
>
> Since the inserts are being done to a raw table it won't be logged (of
> course it won't be replicated either).
> Quman wrote:
> > Normally, we have a script(perl,...) to do this,
> >
> > Inside the loop, we can