Help Please: delete rows in a table and commit eve
A user on IDS 7.31 wanted to delete rows from tab1 matching keys in tab2, but in batches with a COMMIT every ~1000 rows to avoid a huge transaction. Suggestions: select batches of keys into a temp table and loop; or add a serial column to the key list and drive a shell/dbaccess loop over serial ranges. An IBM suggestion to use ROWID BETWEEN 1 AND 1000 was corrected by Art Kagel, who explained ROWIDs are page/slot based, not sequential. Art described his dbdelete approach (SELECT FIRST n ROWIDs, delete, commit, repeat) and noted that without SELECT FIRST you'd need a host language (ESQL/C, 4GL, Perl) to manage the cursor, though he believed FIRST does work in 7.31. No confirmation of the final outcome is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi gurus,
How hard is it to turn this simple delete query into something where it will
commit the deletion every 1000 rows or so? I've tried with a stored procedure
but it's slow -- I'm not all a hardcore coder, perhaps, I don't have the right
codes.
delete from tab1
where col_in_tab1 in (select col_in_tab2 from from tab2);
Thanks
(no, Art's utility is not an option for me at this time)
select first 1000 key from table into temp t1 with no log;
delete from table where key in (select key from t1);
drop table t1;
rinse and repeat. It will still be slow.
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
Kern Doe
Sent: Friday, June 13, 2008 11:52 AM
To: ids@iiug.org
Subject: Help Please: delete rows in a table and commit.... [12416]
Hi gurus,
How hard is it to turn this simple delete query into something where it will
commit the deletion every 1000 rows or so? I've tried with a stored
procedure
but it's slow -- I'm not all a hardcore coder, perhaps, I don't have the
right
codes.
delete from tab1
where col_in_tab1 in (select col_in_tab2 from from tab2);
Thanks
(no, Art's utility is not an option for me at this time)
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
On 13/06/2008, Kern Doe <kern_doe@yahoo.com> wrote:
> Hi gurus,
> How hard is it to turn this simple delete query into something where it will
> commit the deletion every 1000 rows or so? I've tried with a stored procedure
> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have the
right
> codes.
> delete from tab1>
> where col_in_tab1 in (select col_in_tab2 from from tab2);
> Thanks
> (no, Art's utility is not an option for me at this time)
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Kern
Not much to go on here, however, here goes. I am assuming you are
using dbaccess or isql for this. Is tab2 a fixed table or generated
for this deletion? If fixed then select the keys you want to delete
out to another table but add a serial column to the beginning (if tab2
is temp for the deletion then regenerate with a serial column). Create
a shell script to run dbaccess and hold an two exported variables. Set
variable1 to 0, and variable2 to 999.
begin work
delete from tab1
where col_in_tab1 in (select col_in_tab2 from tab2
where serial >= variable1
and serial <= variable2);
commit work;
add 1000 to variable1 and variable2
repeat until variable1 is greated than the number of records being deleted.
Keith
Hello Kern,
You can use the following SQL:
delete from tab1
where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=ween
1 and 1000);
Increment the values in between clause by 1000.
=
Sudhir G Katke =
SWG Client Support - Information Management =
Software =
Premium Support Analyst =
Phone: 479 426 4308 Internal: 624 7341 =
sgkatke@us.ibm.com =
=
|------------>
| From: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|"Kern Doe" <kern_doe@yahoo.com> =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| To: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|ids@iiug.org =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| Date: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|06/13/2008 10:55 AM =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|------------>
| Subject: |
|------------>
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
|Help Please: delete rows in a table and commit.... [12416] =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
-------|
Hi gurus,
How hard is it to turn this simple delete query into something where it=
will
commit the deletion every 1000 rows or so? I've tried with a stored
procedure
but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
e
right
codes.
delete from tab1
where col_in_tab1 in (select col_in_tab2 from from tab2);
Thanks
(no, Art's utility is not an option for me at this time)
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
ROWIDs are NOT sequential integers starting from 1! They are normally the
relative page number on which the row resides shifted left 8 bits plus the
slot number on the page containing the pointer to the row's data.
So the first row on the first page of a table is ROWID 0x00000101 or 257 and
the first row on the second page of the table is ROWID 0x00000201 or 513.
You cannot use ROWID like your have consistently, and certainly it will not
delete 1000 rows at a time since only 1-255 rows can fit on a page. If only
two rows fit on a page, your example would delete only six rows at a time.
Art
On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com> wrote:
> Hello Kern,
> You can use the following SQL:
>
> delete from tab1
> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=> ween
> 1 and 1000);
>
> Increment the values in between clause by 1000.
>
> =
>
> Sudhir G Katke =
>
> SWG Client Support - Information Management =
>
> Software =
>
> Premium Support Analyst =
>
> Phone: 479 426 4308 Internal: 624 7341 =
>
> sgkatke@us.ibm.com =
>
> =
>
> |------------>
> | From: |
> |------------>
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |"Kern Doe" <kern_doe@yahoo.com> =
>
> =
>
> |
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |------------>
> | To: |
> |------------>
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |ids@iiug.org =
>
> =
>
> |
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |------------>
> | Date: |
> |------------>
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |06/13/2008 10:55 AM =
>
> =
>
> |
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |------------>
> | Subject: |
> |------------>
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
> |Help Please: delete rows in a table and commit.... [12416] =
>
> =
>
> |
> >--------------------------------------------------------------------=
> -----------------------------------------------------------------------=
> -------|
>
> Hi gurus,
> How hard is it to turn this simple delete query into something where it=
>
> will
> commit the deletion every 1000 rows or so? I've tried with a stored
> procedure
> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
> e
> right
> codes.
> delete from tab1>
> where col_in_tab1 in (select col_in_tab2 from from tab2);
> Thanks
> (no, Art's utility is not an option for me at this time)
>
> ***********************************************************************=
> ********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> =
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
What my dbdelete utility does is the equivalent of:
LOOP:
BEGIN WORK;
SELECT FIRST 8192 ROWID as id
FROM target_table
WHERE id_col IN (SELECT id FROM lookup_table)
INTO TEMP fred;
DELETE FROM target_table
WHERE ROWID IN (SELECT id FROM fred);
COMMIT WORK;
GOTO LOOP;
.....
Except that it doesn't use a temp table, it builds the IN clause on the fly
using the selected ROWIDs.
Art
On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
> ROWIDs are NOT sequential integers starting from 1! They are normally the
> relative page number on which the row resides shifted left 8 bits plus the
> slot number on the page containing the pointer to the row's data.
>
> So the first row on the first page of a table is ROWID 0x00000101 or 257
> and the first row on the second page of the table is ROWID 0x00000201 or
> 513.
>
> You cannot use ROWID like your have consistently, and certainly it will not
> delete 1000 rows at a time since only 1-255 rows can fit on a page. If only
> two rows fit on a page, your example would delete only six rows at a time.
>
> Art
>
>
> On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
> wrote:
>
>> Hello Kern,
>> You can use the following SQL:
>>
>> delete from tab1
>> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=>> ween
>> 1 and 1000);
>>
>> Increment the values in between clause by 1000.
>>
>> =
>>
>> Sudhir G Katke =
>>
>> SWG Client Support - Information Management =
>>
>> Software =
>>
>> Premium Support Analyst =
>>
>> Phone: 479 426 4308 Internal: 624 7341 =
>>
>> sgkatke@us.ibm.com =
>>
>> =
>>
>> |------------>
>> | From: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |"Kern Doe" <kern_doe@yahoo.com> =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | To: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |ids@iiug.org =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | Date: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |06/13/2008 10:55 AM =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | Subject: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |Help Please: delete rows in a table and commit.... [12416] =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>>
>> Hi gurus,
>> How hard is it to turn this simple delete query into something where it=
>>
>> will
>> commit the deletion every 1000 rows or so? I've tried with a stored
>> procedure
>> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
>> e
>> right
>> codes.
>> delete from tab1>>
>> where col_in_tab1 in (select col_in_tab2 from from tab2);
>> Thanks
>> (no, Art's utility is not an option for me at this time)
>>
>> ***********************************************************************=
>> ********
>>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>> =
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated with
> any entity with which I am affiliated nor those of the entities themselves.
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Art,
I can't do "select first" because this is version 7.31 -- that's the problem.
Kern --
----- Original Message ----
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Friday, June 13, 2008 1:02:17 PM
Subject: Re: Help Please: delete rows in a table and co.... [12421]
What my dbdelete utility does is the equivalent of:
LOOP:
BEGIN WORK;
SELECT FIRST 8192 ROWID as id
FROM target_table
WHERE id_col IN (SELECT id FROM lookup_table)
INTO TEMP fred;
DELETE FROM target_table
WHERE ROWID IN (SELECT id FROM fred);
COMMIT WORK;
GOTO LOOP;
......
Except that it doesn't use a temp table, it builds the IN clause on the fly
using the selected ROWIDs.
Art
On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
> ROWIDs are NOT sequential integers starting from 1! They are normally the
> relative page number on which the row resides shifted left 8 bits plus the
> slot number on the page containing the pointer to the row's data.
>
> So the first row on the first page of a table is ROWID 0x00000101 or 257
> and the first row on the second page of the table is ROWID 0x00000201 or
> 513.
>
> You cannot use ROWID like your have consistently, and certainly it will not
> delete 1000 rows at a time since only 1-255 rows can fit on a page. If only
> two rows fit on a page, your example would delete only six rows at a time.
>
> Art
>
>
> On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
> wrote:
>
>> Hello Kern,
>> You can use the following SQL:
>>
>> delete from tab1
>> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=>> ween
>> 1 and 1000);
>>
>> Increment the values in between clause by 1000.
>>
>> =
>>
>> Sudhir G Katke =
>>
>> SWG Client Support - Information Management =
>>
>> Software =
>>
>> Premium Support Analyst =
>>
>> Phone: 479 426 4308 Internal: 624 7341 =
>>
>> sgkatke@us.ibm.com =
>>
>> =
>>
>> |------------>
>> | From: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |"Kern Doe" <kern_doe@yahoo.com> =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | To: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |ids@iiug.org =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | Date: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |06/13/2008 10:55 AM =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |------------>
>> | Subject: |
>> |------------>
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>> |Help Please: delete rows in a table and commit.... [12416] =
>>
>> =
>>
>> |
>> >--------------------------------------------------------------------=
>> -----------------------------------------------------------------------=
>> -------|
>>
>> Hi gurus,
>> How hard is it to turn this simple delete query into something where it=
>>
>> will
>> commit the deletion every 1000 rows or so? I've tried with a stored
>> procedure
>> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
>> e
>> right
>> codes.
>> delete from tab1>>
>> where col_in_tab1 in (select col_in_tab2 from from tab2);
>> Thanks
>> (no, Art's utility is not an option for me at this time)
>>
>> ***********************************************************************=
>> ********
>>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>> =
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Oninit, the IIUG, nor any other
> organization with which I am associated either explicitly or implicitly.
> Neither do those opinions reflect those of other individuals affiliated with
> any entity with which I am affiliated nor those of the entities themselves.
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Then you will have to code it in a host language where you can limit the
number of rows you process at a time by managing the cursor. That means
ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
fetch the entire 8192 maximum number of rowids that it can in a single
FETCH. One big reason that it is as fast as it is.
I take it you are having trouble getting utils2_ak approved for production
use. Let me know if I can help that. Perhaps they would prefer a formal
commercial license with warantees and -limited- liability? Don't know why
anyone would want to pay for something they can get for free, but if that's
what they want, let me know. I've done it before.
Art
On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> Art,
> I can't do "select first" because this is version 7.31 -- that's the
> problem.
> Kern --
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Friday, June 13, 2008 1:02:17 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12421]
>
> What my dbdelete utility does is the equivalent of:
>
> LOOP:
>
> BEGIN WORK;
> SELECT FIRST 8192 ROWID as id
> FROM target_table
> WHERE id_col IN (SELECT id FROM lookup_table)
> INTO TEMP fred;>
> DELETE FROM target_table
> WHERE ROWID IN (SELECT id FROM fred);>
> COMMIT WORK;
>
> GOTO LOOP;
> .......
>
> Except that it doesn't use a temp table, it builds the IN clause on the fly
> using the selected ROWIDs.
>
> Art
>
> On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > ROWIDs are NOT sequential integers starting from 1! They are normally the
> > relative page number on which the row resides shifted left 8 bits plus
> the
> > slot number on the page containing the pointer to the row's data.
> >
> > So the first row on the first page of a table is ROWID 0x00000101 or 257
> > and the first row on the second page of the table is ROWID 0x00000201 or
> > 513.
> >
> > You cannot use ROWID like your have consistently, and certainly it will
> not
> > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
> only
> > two rows fit on a page, your example would delete only six rows at a
> time.
> >
> > Art
> >
> >
> > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
> > wrote:
> >
> >> Hello Kern,
> >> You can use the following SQL:
> >>
> >> delete from tab1
> >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=> >> ween
> >> 1 and 1000);
> >>
> >> Increment the values in between clause by 1000.
> >>
> >> =
> >>
> >> Sudhir G Katke =
> >>
> >> SWG Client Support - Information Management =
> >>
> >> Software =
> >>
> >> Premium Support Analyst =
> >>
> >> Phone: 479 426 4308 Internal: 624 7341 =
> >>
> >> sgkatke@us.ibm.com =
> >>
> >> =
> >>
> >> |------------>
> >> | From: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |"Kern Doe" <kern_doe@yahoo.com> =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | To: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |ids@iiug.org =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | Date: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |06/13/2008 10:55 AM =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | Subject: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |Help Please: delete rows in a table and commit.... [12416] =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >>
> >> Hi gurus,
> >> How hard is it to turn this simple delete query into something where it=
> >>
> >> will
> >> commit the deletion every 1000 rows or so? I've tried with a stored
> >> procedure
> >> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
> >> e
> >> right
> >> codes.
> >> delete from tab1> >>
> >> where col_in_tab1 in (select col_in_tab2 from from tab2);
> >> Thanks
> >> (no, Art's utility is not an option for me at this time)
> >>
> >> ***********************************************************************=
> >> ********
> >>
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >> =
> >>
> >>
> >>
> >>
>
>
>
*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
> >
> > --
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization with which I am associated either explicitly or implicitly.
> > Neither do those opinions reflect those of other individuals affiliated
> with
> > any entity with which I am affiliated nor those of the entities
> themselves.
> >
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those
> opinions reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************@@NL@
But wait, I'm fairly certain that while SKIP does not work in 7.31, FIRST
does indeed work.
Art
On Fri, Jun 13, 2008 at 1:27 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Then you will have to code it in a host language where you can limit the
> number of rows you process at a time by managing the cursor. That means
> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
> fetch the entire 8192 maximum number of rowids that it can in a single
> FETCH. One big reason that it is as fast as it is.
>
> I take it you are having trouble getting utils2_ak approved for production
> use. Let me know if I can help that. Perhaps they would prefer a formal
> commercial license with warantees and -limited- liability? Don't know why
> anyone would want to pay for something they can get for free, but if that's
> what they want, let me know. I've done it before.
>
> Art
>
>
> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>
>> Art,
>> I can't do "select first" because this is version 7.31 -- that's the
>> problem.
>> Kern --
>>
>> ----- Original Message ----
>> From: Art Kagel <art.kagel@gmail.com>
>> To: ids@iiug.org
>> Sent: Friday, June 13, 2008 1:02:17 PM
>> Subject: Re: Help Please: delete rows in a table and co.... [12421]
>>
>> What my dbdelete utility does is the equivalent of:
>>
>> LOOP:
>>
>> BEGIN WORK;
>> SELECT FIRST 8192 ROWID as id
>> FROM target_table
>> WHERE id_col IN (SELECT id FROM lookup_table)
>> INTO TEMP fred;>>
>> DELETE FROM target_table
>> WHERE ROWID IN (SELECT id FROM fred);>>
>> COMMIT WORK;
>>
>> GOTO LOOP;
>> .......
>>
>> Except that it doesn't use a temp table, it builds the IN clause on the
>> fly
>> using the selected ROWIDs.
>>
>> Art
>>
>> On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
>>
>> > ROWIDs are NOT sequential integers starting from 1! They are normally
>> the
>> > relative page number on which the row resides shifted left 8 bits plus
>> the
>> > slot number on the page containing the pointer to the row's data.
>> >
>> > So the first row on the first page of a table is ROWID 0x00000101 or 257
>> > and the first row on the second page of the table is ROWID 0x00000201 or
>> > 513.
>> >
>> > You cannot use ROWID like your have consistently, and certainly it will
>> not
>> > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
>> only
>> > two rows fit on a page, your example would delete only six rows at a
>> time.
>> >
>> > Art
>> >
>> >
>> > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
>> > wrote:
>> >
>> >> Hello Kern,
>> >> You can use the following SQL:
>> >>
>> >> delete from tab1
>> >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid>> bet=
>> >> ween
>> >> 1 and 1000);
>> >>
>> >> Increment the values in between clause by 1000.
>> >>
>> >> =
>> >>
>> >> Sudhir G Katke =
>> >>
>> >> SWG Client Support - Information Management =
>> >>
>> >> Software =
>> >>
>> >> Premium Support Analyst =
>> >>
>> >> Phone: 479 426 4308 Internal: 624 7341 =
>> >>
>> >> sgkatke@us.ibm.com =
>> >>
>> >> =
>> >>
>> >> |------------>
>> >> | From: |
>> >> |------------>
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |"Kern Doe" <kern_doe@yahoo.com> =
>> >>
>> >> =
>> >>
>> >> |
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |------------>
>> >> | To: |
>> >> |------------>
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |ids@iiug.org =
>> >>
>> >> =
>> >>
>> >> |
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |------------>
>> >> | Date: |
>> >> |------------>
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |06/13/2008 10:55 AM =
>> >>
>> >> =
>> >>
>> >> |
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |------------>
>> >> | Subject: |
>> >> |------------>
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >> |Help Please: delete rows in a table and commit.... [12416] =
>> >>
>> >> =
>> >>
>> >> |
>> >> >--------------------------------------------------------------------=
>> >>
>> -----------------------------------------------------------------------=
>> >> -------|
>> >>
>> >> Hi gurus,
>> >> How hard is it to turn this simple delete query into something where
>> it=
>> >>
>> >> will
>> >> commit the deletion every 1000 rows or so? I've tried with a stored
>> >> procedure
>> >> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have
>> th=
>> >> e
>> >> right
>> >> codes.
>> >> delete from tab1>> >>
>> >> where col_in_tab1 in (select col_in_tab2 from from tab2);
>> >> Thanks
>> >> (no, Art's utility is not an option for me at this time)
>> >>
>> >>
>> ***********************************************************************=
>> >> ********
>> >>
>> >> Forum Note: Use "Reply" to post a response in the discussion forum.
>> >>
>> >> =
>> >>
>> >>
>> >>
>> >>
>>
>>
>>
*******************************************************************************
>> >> Forum Note: Use "Reply" to post a response in the discussion forum.
>> >>
>> >>
>> >
>> >
>> > --
>> > Art S. Kagel
>> > Oninit (www.oninit.com)
>> > IIUG Board of Directors (art@iiug.org)
>> >
>> > Disclaimer: Please keep in mind that my own opinions are my own opinions
>> > and do not reflect on my employer, Oninit, the IIUG, nor any other
>> > organization with which I am associated either explicitly or implicitly.
>> > Neither do those opinions reflect those of other individuals affiliated
>> with
>> > any entity with which I am affiliated nor those of the entities
>> themselves.
>> >
>>
>> --
>> Art S. Kagel
>> Oninit (www.oninit.com)
>> IIUG Board of Directors (art@iiug.org)
>>
>> Disclaimer: Please keep in mind that my own opinions are my own opinions
>> and
>> do not reflect on my employer, Oninit, the IIUG, nor any other
>> organizatio
Hi Art,
The problem is not about $$$ -- it is a change to the system that I don't want
to do yet (even it is a dev box) unless I exhaust all my options then I will
push for it convincing that the dbdelete is the way to go no need to reinvent
the wheel. Upon getting the suggestion from Obnoxio a couple of days ago, I
actually down loaded both: your utilities and the free SDK (for Solaris 7) but
have not done anything yet
Perhaps you are the creator then may I ask you directly, how difficult is it
to compile the dbdelete? (I don't need other stuff)
ok, first I need to install ESQL (root id must involve)
2nd, I need to compile the dbdelete.ec I guess?
seems like some modifications will be neede to the dbdelete before compiling?
Thanks.
kern --
----- Original Message ----
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Friday, June 13, 2008 1:27:32 PM
Subject: Re: Help Please: delete rows in a table and co.... [12423]
Then you will have to code it in a host language where you can limit the
number of rows you process at a time by managing the cursor. That means
ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
fetch the entire 8192 maximum number of rowids that it can in a single
FETCH. One big reason that it is as fast as it is.
I take it you are having trouble getting utils2_ak approved for production
use. Let me know if I can help that. Perhaps they would prefer a formal
commercial license with warantees and -limited- liability? Don't know why
anyone would want to pay for something they can get for free, but if that's
what they want, let me know. I've done it before.
Art
On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> Art,
> I can't do "select first" because this is version 7.31 -- that's the
> problem.
> Kern --
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Friday, June 13, 2008 1:02:17 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12421]
>
> What my dbdelete utility does is the equivalent of:
>
> LOOP:
>
> BEGIN WORK;
> SELECT FIRST 8192 ROWID as id
> FROM target_table
> WHERE id_col IN (SELECT id FROM lookup_table)
> INTO TEMP fred;>
> DELETE FROM target_table
> WHERE ROWID IN (SELECT id FROM fred);>
> COMMIT WORK;
>
> GOTO LOOP;
> .......
>
> Except that it doesn't use a temp table, it builds the IN clause on the fly
> using the selected ROWIDs.
>
> Art
>
> On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > ROWIDs are NOT sequential integers starting from 1! They are normally the
> > relative page number on which the row resides shifted left 8 bits plus
> the
> > slot number on the page containing the pointer to the row's data.
> >
> > So the first row on the first page of a table is ROWID 0x00000101 or 257
> > and the first row on the second page of the table is ROWID 0x00000201 or
> > 513.
> >
> > You cannot use ROWID like your have consistently, and certainly it will
> not
> > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
> only
> > two rows fit on a page, your example would delete only six rows at a
> time.
> >
> > Art
> >
> >
> > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
> > wrote:
> >
> >> Hello Kern,
> >> You can use the following SQL:
> >>
> >> delete from tab1
> >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid bet=> >> ween
> >> 1 and 1000);
> >>
> >> Increment the values in between clause by 1000.
> >>
> >> =
> >>
> >> Sudhir G Katke =
> >>
> >> SWG Client Support - Information Management =
> >>
> >> Software =
> >>
> >> Premium Support Analyst =
> >>
> >> Phone: 479 426 4308 Internal: 624 7341 =
> >>
> >> sgkatke@us.ibm.com =
> >>
> >> =
> >>
> >> |------------>
> >> | From: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |"Kern Doe" <kern_doe@yahoo.com> =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | To: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |ids@iiug.org =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | Date: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |06/13/2008 10:55 AM =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |------------>
> >> | Subject: |
> >> |------------>
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >> |Help Please: delete rows in a table and commit.... [12416] =
> >>
> >> =
> >>
> >> |
> >> >--------------------------------------------------------------------=
> >> -----------------------------------------------------------------------=
> >> -------|
> >>
> >> Hi gurus,
> >> How hard is it to turn this simple delete query into something where it=
> >>
> >> will
> >> commit the deletion every 1000 rows or so? I've tried with a stored
> >> procedure
> >> but it's slow -- I'm not all a hardcore coder, perhaps, I don't have th=
> >> e
> >> right
> >> codes.
> >> delete from tab1> >>
> >> where col_in_tab1 in (select col_in_tab2 from from tab2);
> >> Thanks
> >> (no, Art's utility is not an option for me at this time)
> >>
> >> ***********************************************************************=
> >> ********
> >>
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >> =
> >>
> >>
> >>
> >>
>
>
>
*******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
> >
> > --
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization with which I am associated either explicitly or implicitl
Please try following code in SP (Since I don't have access to DBACCESS now, I
couldn't check validity/error of following code.
please correct any syntax error or any mistakes in following code)..
Define lv_cnt smallint;
define lv_col1 ?????; --please replace ???? with actual data type
let lv_cnt = 0;
begin work;
let lv_col1 = null;
foreach cur1 with hold for select col_in_tab1 into lv_col1
from tab1 where col_in_tab1 in (select col_in_tab2 from from tab2 where
col_in_tab2 = col_in_tab1)
delete from tab1 where col_in_tab1 = lv_col1;
let lv_cnt = lv_cnt + 1;
if lv_cnt >= 1000 then
let lv_cnt = 0;
commit work;
begin work;
end if;
let lv_col1 = null
end foreach;
commit work;
> To: ids@iiug.org> From: kern_doe@yahoo.com> Subject: Help Please: delete
rows in a table and commit.... [12416]> Date: Fri, 13 Jun 2008 11:52:29 -0400>
> Hi gurus, > How hard is it to turn this simple delete query into something
where it will > commit the deletion every 1000 rows or so? I've tried with a
stored procedure > but it's slow -- I'm not all a hardcore coder, perhaps, I
don't have the right > codes. > delete from tab1 > > where col_in_tab1 in
(select col_in_tab2 from from tab2); > Thanks > (no, Art's utility is not an
option for me at this time) > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Now you can invite friends from Facebook and other groups to join you on
Windows Live Messenger. Add now.
https://www.invite2messenger.net/im/?source=TXT_EML_WLH_AddNow_Now
Dharmendra,
With few fixes, your codes actually work! it will do the job but it's real
slow -- to get a different (better) result we have to do it differently
perhaps change the query around or use Art's esql code -- and unfortunately
his is not yet available for our old version of IDS....
Thanks.
----- Original Message ----
From: dharmendra sharma <dharmendrasharma@hotmail.com>
To: ids@iiug.org
Sent: Friday, June 13, 2008 2:01:05 PM
Subject: RE: Help Please: delete rows in a table and co.... [12426]
Please try following code in SP (Since I don't have access to DBACCESS now, I
couldn't check validity/error of following code.
please correct any syntax error or any mistakes in following code)..
Define lv_cnt smallint;
define lv_col1 ?????; --please replace ???? with actual data type
let lv_cnt = 0;
begin work;
let lv_col1 = null;
foreach cur1 with hold for select col_in_tab1 into lv_col1
from tab1 where col_in_tab1 in (select col_in_tab2 from from tab2 where
col_in_tab2 = col_in_tab1)
delete from tab1 where col_in_tab1 = lv_col1;
let lv_cnt = lv_cnt + 1;
if lv_cnt >= 1000 then
let lv_cnt = 0;
commit work;
begin work;
end if;
let lv_col1 = null
end foreach;
commit work;
> To: ids@iiug.org> From: kern_doe@yahoo.com> Subject: Help Please: delete
rows in a table and commit.... [12416]> Date: Fri, 13 Jun 2008 11:52:29 -0400>
> Hi gurus, > How hard is it to turn this simple delete query into something
where it will > commit the deletion every 1000 rows or so? I've tried with a
stored procedure > but it's slow -- I'm not all a hardcore coder, perhaps, I
don't have the right > codes. > delete from tab1 > > where col_in_tab1 in
(select col_in_tab2 from from tab2); > Thanks > (no, Art's utility is not an
option for me at this time) > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Now you can invite friends from Facebook and other groups to join you on
Windows Live Messenger. Add now.
https://www.invite2messenger.net/im/?source=TXT_EML_WLH_AddNow_Now
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Kern,
I think it's slow while selecting records using two tables and/or not using
proper index. How long it takes if you just run the select statement alone?
The code I have provided will solve your "commit on every 1000 records"
problem. It will have no impact on slowness of query unless
the select statement itself takes long time. Once you use the proper index and
the improved query statement to fetch
records faster, you should have better result.. How many records are in tab2
that do not exist in tab1?
Since I don't know the relationship of tab1 and tab2, I'm not sure whether
following suggestion will work or not..
Please try to change your query as;
foreach cur1 with hold for select col_in_tab2 into lv_col1 from tab2 --
removed tab1 from the query
delete from tab1 where col_in_tab1 = lv_col1; -- please check SQLCA.SQLERRDhere to see whether record has been deleted or not
-- as I mentioned before, I don't have access to dbaccess and also don't
remember the actual syntax to get this value..
-- please replace the following IF condition with actual syntax...
IF "record has been deleted" then
let lv_cnt = lv_cnt + 1; if lv_cnt >= 1000 then let lv_cnt = 0; commit work;
begin work; end if; end if;let lv_col1 = null end foreach; commit work;
> To: ids@iiug.org> From: kern_doe@yahoo.com> Subject: Re: Help Please: delete
rows in a table and co.... [12427]> Date: Fri, 13 Jun 2008 15:07:47 -0400> >
Dharmendra, > With few fixes, your codes actually work! it will do the job but
it's real > slow -- to get a different (better) result we have to do it
differently > perhaps change the query around or use Art's esql code -- and
unfortunately > his is not yet available for our old version of IDS.... >
Thanks. > > ----- Original Message ---- > From: dharmendra sharma
<dharmendrasharma@hotmail.com> > To: ids@iiug.org > Sent: Friday, June 13,
2008 2:01:05 PM > Subject: RE: Help Please: delete rows in a table and co....
[12426] > > Please try following code in SP (Since I don't have access to
DBACCESS now, I > couldn't check validity/error of following code. > > please
correct any syntax error or any mistakes in following code).. > > Define
lv_cnt smallint; > define lv_col1 ?????; --please replace ???? with actual
data type > > let lv_cnt = 0; > begin work; > let lv_col1 = null; > foreach
cur1 with hold for select col_in_tab1 into lv_col1 > > from tab1 where
col_in_tab1 in (select col_in_tab2 from from tab2 where > col_in_tab2 =
col_in_tab1) > > delete from tab1 where col_in_tab1 = lv_col1; > > let lv_cnt
= lv_cnt + 1; > > if lv_cnt >= 1000 then > > let lv_cnt = 0; > > commit work;
> > begin work; > > end if; > > let lv_col1 = null > end foreach; > commit
work; > > > To: ids@iiug.org> From: kern_doe@yahoo.com> Subject: Help Please:
delete > rows in a table and commit.... [12416]> Date: Fri, 13 Jun 2008
11:52:29 -0400> > > Hi gurus, > How hard is it to turn this simple delete
query into something > where it will > commit the deletion every 1000 rows or
so? I've tried with a > stored procedure > but it's slow -- I'm not all a
hardcore coder, perhaps, I > don't have the right > codes. > delete from tab1
> > where col_in_tab1 in > (select col_in_tab2 from from tab2); > Thanks >
(no, Art's utility is not an > option for me at this time) > > > > >
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > >
_________________________________________________________________ > Now you
can invite friends from Facebook and other groups to join you on > Windows
Live Messenger. Add now. >
https://www.invite2messenger.net/im/?source=TXT_EML_WLH_AddNow_Now > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Enjoy 5 GB of free, password-protected online storage.
http://www.windowslive.com/skydrive/overview.html?ocid=TXT_TAGLM_WL_Refresh_skyd
rive_062008
Kern,
Unless you are using non-standard datatypes that dbdelete might not
recognize (and that would only matter if those types were used in the filter
to select rows), dbdelete will work out of the box. Just compiling
dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
package 'make's out of the box as long as you have GNU Make (you have to
make a minor change to the main make file (Makefile) and the makefile for
myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
documented in the BUILD file). To build the entire package if you have GNU
make just unpack the shell archive by executing it and type 'make'.
Art
On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> Hi Art,
> The problem is not about $$$ -- it is a change to the system that I don't
> want
> to do yet (even it is a dev box) unless I exhaust all my options then I
> will
> push for it convincing that the dbdelete is the way to go no need to
> reinvent
> the wheel. Upon getting the suggestion from Obnoxio a couple of days ago, I
> actually down loaded both: your utilities and the free SDK (for Solaris 7)
> but
> have not done anything yet
> Perhaps you are the creator then may I ask you directly, how difficult is
> it
> to compile the dbdelete? (I don't need other stuff)
> ok, first I need to install ESQL (root id must involve)
> 2nd, I need to compile the dbdelete.ec I guess?
> seems like some modifications will be neede to the dbdelete before
> compiling?
> Thanks.
> kern --
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Friday, June 13, 2008 1:27:32 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12423]
>
> Then you will have to code it in a host language where you can limit the
> number of rows you process at a time by managing the cursor. That means
> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
> fetch the entire 8192 maximum number of rowids that it can in a single
> FETCH. One big reason that it is as fast as it is.
>
> I take it you are having trouble getting utils2_ak approved for production
> use. Let me know if I can help that. Perhaps they would prefer a formal
> commercial license with warantees and -limited- liability? Don't know why
> anyone would want to pay for something they can get for free, but if that's
> what they want, let me know. I've done it before.
>
> Art
>
> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>
> > Art,
> > I can't do "select first" because this is version 7.31 -- that's the
> > problem.
> > Kern --
> >
> > ----- Original Message ----
> > From: Art Kagel <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Sent: Friday, June 13, 2008 1:02:17 PM
> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
> >
> > What my dbdelete utility does is the equivalent of:
> >
> > LOOP:
> >
> > BEGIN WORK;
> > SELECT FIRST 8192 ROWID as id
> > FROM target_table
> > WHERE id_col IN (SELECT id FROM lookup_table)
> > INTO TEMP fred;> >
> > DELETE FROM target_table
> > WHERE ROWID IN (SELECT id FROM fred);> >
> > COMMIT WORK;
> >
> > GOTO LOOP;
> > .......
> >
> > Except that it doesn't use a temp table, it builds the IN clause on the
> fly
> > using the selected ROWIDs.
> >
> > Art
> >
> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com> wrote:
> >
> > > ROWIDs are NOT sequential integers starting from 1! They are normally
> the
> > > relative page number on which the row resides shifted left 8 bits plus
> > the
> > > slot number on the page containing the pointer to the row's data.
> > >
> > > So the first row on the first page of a table is ROWID 0x00000101 or
> 257
> > > and the first row on the second page of the table is ROWID 0x00000201
> or
> > > 513.
> > >
> > > You cannot use ROWID like your have consistently, and certainly it will
> > not
> > > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
> > only
> > > two rows fit on a page, your example would delete only six rows at a
> > time.
> > >
> > > Art
> > >
> > >
> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
> > > wrote:
> > >
> > >> Hello Kern,
> > >> You can use the following SQL:
> > >>
> > >> delete from tab1
> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid> bet=
> > >> ween
> > >> 1 and 1000);
> > >>
> > >> Increment the values in between clause by 1000.
> > >>
> > >> =
> > >>
> > >> Sudhir G Katke =
> > >>
> > >> SWG Client Support - Information Management =
> > >>
> > >> Software =
> > >>
> > >> Premium Support Analyst =
> > >>
> > >> Phone: 479 426 4308 Internal: 624 7341 =
> > >>
> > >> sgkatke@us.ibm.com =
> > >>
> > >> =
> > >>
> > >> |------------>
> > >> | From: |
> > >> |------------>
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |"Kern Doe" <kern_doe@yahoo.com> =
> > >>
> > >> =
> > >>
> > >> |
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |------------>
> > >> | To: |
> > >> |------------>
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |ids@iiug.org =
> > >>
> > >> =
> > >>
> > >> |
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |------------>
> > >> | Date: |
> > >> |------------>
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |06/13/2008 10:55 AM =
> > >>
> > >> =
> > >>
> > >> |
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |------------>
> > >> | Subject: |
> > >> |------------>
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >> |Help Please: delete rows in a table and commit.... [12416] =
> > >>
> > >> =
> > >>
> > >> |
> > >> >--------------------------------------------------------------------=
> > >>
> -----------------------------------------------------------------------=
> > >> -------|
> > >>
> > >> Hi gurus,
> > >> How hard is it to turn this simple delete query into something where
> it=@@
Oh, forgot, I uploaded an updated edition of the utils2_ak package on
Friday. Though it only has updates to dostats and myschema, the dbdelete
you already have is up-to-date.
Art
On Sat, Jun 14, 2008 at 9:53 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Kern,
>
> Unless you are using non-standard datatypes that dbdelete might not
> recognize (and that would only matter if those types were used in the filter
> to select rows), dbdelete will work out of the box. Just compiling
> dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
> package 'make's out of the box as long as you have GNU Make (you have to
> make a minor change to the main make file (Makefile) and the makefile for
> myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
> documented in the BUILD file). To build the entire package if you have GNU
> make just unpack the shell archive by executing it and type 'make'.
>
> Art
>
>
> On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>
>> Hi Art,
>> The problem is not about $$$ -- it is a change to the system that I don't
>> want
>> to do yet (even it is a dev box) unless I exhaust all my options then I
>> will
>> push for it convincing that the dbdelete is the way to go no need to
>> reinvent
>> the wheel. Upon getting the suggestion from Obnoxio a couple of days ago,
>> I
>> actually down loaded both: your utilities and the free SDK (for Solaris 7)
>> but
>> have not done anything yet
>> Perhaps you are the creator then may I ask you directly, how difficult is
>> it
>> to compile the dbdelete? (I don't need other stuff)
>> ok, first I need to install ESQL (root id must involve)
>> 2nd, I need to compile the dbdelete.ec I guess?
>> seems like some modifications will be neede to the dbdelete before
>> compiling?
>> Thanks.
>> kern --
>>
>> ----- Original Message ----
>> From: Art Kagel <art.kagel@gmail.com>
>> To: ids@iiug.org
>> Sent: Friday, June 13, 2008 1:27:32 PM
>> Subject: Re: Help Please: delete rows in a table and co.... [12423]
>>
>> Then you will have to code it in a host language where you can limit the
>> number of rows you process at a time by managing the cursor. That means
>> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
>> fetch the entire 8192 maximum number of rowids that it can in a single
>> FETCH. One big reason that it is as fast as it is.
>>
>> I take it you are having trouble getting utils2_ak approved for production
>> use. Let me know if I can help that. Perhaps they would prefer a formal
>> commercial license with warantees and -limited- liability? Don't know why
>> anyone would want to pay for something they can get for free, but if
>> that's
>> what they want, let me know. I've done it before.
>>
>> Art
>>
>> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>>
>> > Art,
>> > I can't do "select first" because this is version 7.31 -- that's the
>> > problem.
>> > Kern --
>> >
>> > ----- Original Message ----
>> > From: Art Kagel <art.kagel@gmail.com>
>> > To: ids@iiug.org
>> > Sent: Friday, June 13, 2008 1:02:17 PM
>> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
>> >
>> > What my dbdelete utility does is the equivalent of:
>> >
>> > LOOP:
>> >
>> > BEGIN WORK;
>> > SELECT FIRST 8192 ROWID as id
>> > FROM target_table
>> > WHERE id_col IN (SELECT id FROM lookup_table)
>> > INTO TEMP fred;>> >
>> > DELETE FROM target_table
>> > WHERE ROWID IN (SELECT id FROM fred);>> >
>> > COMMIT WORK;
>> >
>> > GOTO LOOP;
>> > .......
>> >
>> > Except that it doesn't use a temp table, it builds the IN clause on the
>> fly
>> > using the selected ROWIDs.
>> >
>> > Art
>> >
>> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com>
>> wrote:
>> >
>> > > ROWIDs are NOT sequential integers starting from 1! They are normally
>> the
>> > > relative page number on which the row resides shifted left 8 bits plus
>> > the
>> > > slot number on the page containing the pointer to the row's data.
>> > >
>> > > So the first row on the first page of a table is ROWID 0x00000101 or
>> 257
>> > > and the first row on the second page of the table is ROWID 0x00000201
>> or
>> > > 513.
>> > >
>> > > You cannot use ROWID like your have consistently, and certainly it
>> will
>> > not
>> > > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
>> > only
>> > > two rows fit on a page, your example would delete only six rows at a
>> > time.
>> > >
>> > > Art
>> > >
>> > >
>> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
>> > > wrote:
>> > >
>> > >> Hello Kern,
>> > >> You can use the following SQL:
>> > >>
>> > >> delete from tab1
>> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid>> bet=
>> > >> ween
>> > >> 1 and 1000);
>> > >>
>> > >> Increment the values in between clause by 1000.
>> > >>
>> > >> =
>> > >>
>> > >> Sudhir G Katke =
>> > >>
>> > >> SWG Client Support - Information Management =
>> > >>
>> > >> Software =
>> > >>
>> > >> Premium Support Analyst =
>> > >>
>> > >> Phone: 479 426 4308 Internal: 624 7341 =
>> > >>
>> > >> sgkatke@us.ibm.com =
>> > >>
>> > >> =
>> > >>
>> > >> |------------>
>> > >> | From: |
>> > >> |------------>
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |"Kern Doe" <kern_doe@yahoo.com> =
>> > >>
>> > >> =
>> > >>
>> > >> |
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |------------>
>> > >> | To: |
>> > >> |------------>
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |ids@iiug.org =
>> > >>
>> > >> =
>> > >>
>> > >> |
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |------------>
>> > >> | Date: |
>> > >> |------------>
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |06/13/2008 10:55 AM =
>> > >>
>> > >> =
>> > >>
>> > >> |
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |------------>
>> > >> | Subject: |
>> > >> |------------>
>> > >>
>> >-------------------------------
AFAIK dbdelete will work with OL4.01, I wrote it for 5.05 originally and
while it's been updated to support later features, IB it will work with any
Informix database including SE 7.xx. You MAY need to install a recent CSDK
if you don't want to compile it without removing a few new code features,
but I'm no even certain that it would not still compile with ESQL v5.
Art
On Fri, Jun 13, 2008 at 3:07 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> Dharmendra,
> With few fixes, your codes actually work! it will do the job but it's real
> slow -- to get a different (better) result we have to do it differently
> perhaps change the query around or use Art's esql code -- and unfortunately
> his is not yet available for our old version of IDS....
> Thanks.
>
> ----- Original Message ----
> From: dharmendra sharma <dharmendrasharma@hotmail.com>
> To: ids@iiug.org
> Sent: Friday, June 13, 2008 2:01:05 PM
> Subject: RE: Help Please: delete rows in a table and co.... [12426]
>
> Please try following code in SP (Since I don't have access to DBACCESS now,
> I
> couldn't check validity/error of following code.
>
> please correct any syntax error or any mistakes in following code)..
>
> Define lv_cnt smallint;
> define lv_col1 ?????; --please replace ???? with actual data type
>
> let lv_cnt = 0;
> begin work;
> let lv_col1 = null;
> foreach cur1 with hold for select col_in_tab1 into lv_col1
>
> from tab1 where col_in_tab1 in (select col_in_tab2 from from tab2 where
> col_in_tab2 = col_in_tab1)
>
> delete from tab1 where col_in_tab1 = lv_col1;>
> let lv_cnt = lv_cnt + 1;
>
> if lv_cnt >= 1000 then
>
> let lv_cnt = 0;
>
> commit work;
>
> begin work;
>
> end if;
>
> let lv_col1 = null
> end foreach;
> commit work;
>
> > To: ids@iiug.org> From: kern_doe@yahoo.com> Subject: Help Please: delete
> rows in a table and commit.... [12416]> Date: Fri, 13 Jun 2008 11:52:29
> -0400>
> > Hi gurus, > How hard is it to turn this simple delete query into
> something
> where it will > commit the deletion every 1000 rows or so? I've tried with
> a
> stored procedure > but it's slow -- I'm not all a hardcore coder, perhaps,
> I
> don't have the right > codes. > delete from tab1 > > where col_in_tab1 in
> (select col_in_tab2 from from tab2); > Thanks > (no, Art's utility is not
> an
> option for me at this time) > > >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. >
> _________________________________________________________________
> Now you can invite friends from Facebook and other groups to join you on
> Windows Live Messenger. Add now.
> https://www.invite2messenger.net/im/?source=TXT_EML_WLH_AddNow_Now
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Thanks Art.
No, the data is just standard, just character field columns; I'm talking about
those joining fields.
In addition, I need to mention that the table to purge is fragmented;
therefore, there will be no rowid, is it a problem to your dbdelete?
Yesterday, I randomly selected out about 10 rows from tableB (look up table)
and hardcoded in a simple query via dbaccess to test the delete. Only half of
these were matching in tableA and it took about 3 seconds to finish. Through
my SP with similar structure to Dharmendra's suggestion, it averages out 5,6
rows/sec. So, I'm confused, perhaps no matter how we try, 20 or more rows
deleted per second may not be achievable for this table?
Regards
----- Original Message ----
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Saturday, June 14, 2008 9:54:29 PM
Subject: Re: Help Please: delete rows in a table and co.... [12431]
Oh, forgot, I uploaded an updated edition of the utils2_ak package on
Friday. Though it only has updates to dostats and myschema, the dbdelete
you already have is up-to-date.
Art
On Sat, Jun 14, 2008 at 9:53 PM, Art Kagel <art.kagel@gmail.com> wrote:
> Kern,
>
> Unless you are using non-standard datatypes that dbdelete might not
> recognize (and that would only matter if those types were used in the filter
> to select rows), dbdelete will work out of the box. Just compiling
> dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
> package 'make's out of the box as long as you have GNU Make (you have to
> make a minor change to the main make file (Makefile) and the makefile for
> myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
> documented in the BUILD file). To build the entire package if you have GNU
> make just unpack the shell archive by executing it and type 'make'.
>
> Art
>
>
> On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>
>> Hi Art,
>> The problem is not about $$$ -- it is a change to the system that I don't
>> want
>> to do yet (even it is a dev box) unless I exhaust all my options then I
>> will
>> push for it convincing that the dbdelete is the way to go no need to
>> reinvent
>> the wheel. Upon getting the suggestion from Obnoxio a couple of days ago,
>> I
>> actually down loaded both: your utilities and the free SDK (for Solaris 7)
>> but
>> have not done anything yet
>> Perhaps you are the creator then may I ask you directly, how difficult is
>> it
>> to compile the dbdelete? (I don't need other stuff)
>> ok, first I need to install ESQL (root id must involve)
>> 2nd, I need to compile the dbdelete.ec I guess?
>> seems like some modifications will be neede to the dbdelete before
>> compiling?
>> Thanks.
>> kern --
>>
>> ----- Original Message ----
>> From: Art Kagel <art.kagel@gmail.com>
>> To: ids@iiug.org
>> Sent: Friday, June 13, 2008 1:27:32 PM
>> Subject: Re: Help Please: delete rows in a table and co.... [12423]
>>
>> Then you will have to code it in a host language where you can limit the
>> number of rows you process at a time by managing the cursor. That means
>> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
>> fetch the entire 8192 maximum number of rowids that it can in a single
>> FETCH. One big reason that it is as fast as it is.
>>
>> I take it you are having trouble getting utils2_ak approved for production
>> use. Let me know if I can help that. Perhaps they would prefer a formal
>> commercial license with warantees and -limited- liability? Don't know why
>> anyone would want to pay for something they can get for free, but if
>> that's
>> what they want, let me know. I've done it before.
>>
>> Art
>>
>> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
>>
>> > Art,
>> > I can't do "select first" because this is version 7.31 -- that's the
>> > problem.
>> > Kern --
>> >
>> > ----- Original Message ----
>> > From: Art Kagel <art.kagel@gmail.com>
>> > To: ids@iiug.org
>> > Sent: Friday, June 13, 2008 1:02:17 PM
>> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
>> >
>> > What my dbdelete utility does is the equivalent of:
>> >
>> > LOOP:
>> >
>> > BEGIN WORK;
>> > SELECT FIRST 8192 ROWID as id
>> > FROM target_table
>> > WHERE id_col IN (SELECT id FROM lookup_table)
>> > INTO TEMP fred;>> >
>> > DELETE FROM target_table
>> > WHERE ROWID IN (SELECT id FROM fred);>> >
>> > COMMIT WORK;
>> >
>> > GOTO LOOP;
>> > .......
>> >
>> > Except that it doesn't use a temp table, it builds the IN clause on the
>> fly
>> > using the selected ROWIDs.
>> >
>> > Art
>> >
>> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com>
>> wrote:
>> >
>> > > ROWIDs are NOT sequential integers starting from 1! They are normally
>> the
>> > > relative page number on which the row resides shifted left 8 bits plus
>> > the
>> > > slot number on the page containing the pointer to the row's data.
>> > >
>> > > So the first row on the first page of a table is ROWID 0x00000101 or
>> 257
>> > > and the first row on the second page of the table is ROWID 0x00000201
>> or
>> > > 513.
>> > >
>> > > You cannot use ROWID like your have consistently, and certainly it
>> will
>> > not
>> > > delete 1000 rows at a time since only 1-255 rows can fit on a page. If
>> > only
>> > > two rows fit on a page, your example would delete only six rows at a
>> > time.
>> > >
>> > > Art
>> > >
>> > >
>> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <sgkatke@us.ibm.com>
>> > > wrote:
>> > >
>> > >> Hello Kern,
>> > >> You can use the following SQL:
>> > >>
>> > >> delete from tab1
>> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid>> bet=
>> > >> ween
>> > >> 1 and 1000);
>> > >>
>> > >> Increment the values in between clause by 1000.
>> > >>
>> > >> =
>> > >>
>> > >> Sudhir G Katke =
>> > >>
>> > >> SWG Client Support - Information Management =
>> > >>
>> > >> Software =
>> > >>
>> > >> Premium Support Analyst =
>> > >>
>> > >> Phone: 479 426 4308 Internal: 624 7341 =
>> > >>
>> > >> sgkatke@us.ibm.com =
>> > >>
>> > >> =
>> > >>
>> > >> |------------>
>> > >> | From: |
>> > >> |------------>
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |"Kern Doe" <kern_doe@yahoo.com> =
>> > >>
>> > >> =
>> > >>
>> > >> |
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |------------>
>> > >> | To: |
>> > >> |------------>
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |ids@iiug.org
If the table was not created WITH ROWID dbdelete will still work, as long as
there is a unique key you can supply to the -u option, but it will not run
quite as fast. Don't know about your particular server, but my runs have
typically taken about 10-12 seconds to delete 8192 rows or about 800 a
second.
Art
On Sun, Jun 15, 2008 at 5:37 PM, Kern Doe <kern_doe@yahoo.com> wrote:
Thanks Art.
> No, the data is just standard, just character field columns; I'm talking
> about
> those joining fields.
> In addition, I need to mention that the table to purge is fragmented;
> therefore, there will be no rowid, is it a problem to your dbdelete?
> Yesterday, I randomly selected out about 10 rows from tableB (look up
> table)
> and hardcoded in a simple query via dbaccess to test the delete. Only half
> of
> these were matching in tableA and it took about 3 seconds to finish.
> Through
> my SP with similar structure to Dharmendra's suggestion, it averages out
> 5,6
> rows/sec. So, I'm confused, perhaps no matter how we try, 20 or more rows
> deleted per second may not be achievable for this table?
> Regards
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Saturday, June 14, 2008 9:54:29 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12431]
>
> Oh, forgot, I uploaded an updated edition of the utils2_ak package on
> Friday. Though it only has updates to dostats and myschema, the dbdelete
> you already have is up-to-date.
>
> Art
>
> On Sat, Jun 14, 2008 at 9:53 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Kern,
> >
> > Unless you are using non-standard datatypes that dbdelete might not
> > recognize (and that would only matter if those types were used in the
> filter
> > to select rows), dbdelete will work out of the box. Just compiling
> > dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
> > package 'make's out of the box as long as you have GNU Make (you have to
> > make a minor change to the main make file (Makefile) and the makefile for
> > myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
> > documented in the BUILD file). To build the entire package if you have
> GNU
> > make just unpack the shell archive by executing it and type 'make'.
> >
> > Art
> >
> >
> > On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >
> >> Hi Art,
> >> The problem is not about $$$ -- it is a change to the system that I
> don't
> >> want
> >> to do yet (even it is a dev box) unless I exhaust all my options then I
> >> will
> >> push for it convincing that the dbdelete is the way to go no need to
> >> reinvent
> >> the wheel. Upon getting the suggestion from Obnoxio a couple of days
> ago,
> >> I
> >> actually down loaded both: your utilities and the free SDK (for Solaris
> 7)
> >> but
> >> have not done anything yet
> >> Perhaps you are the creator then may I ask you directly, how difficult
> is
> >> it
> >> to compile the dbdelete? (I don't need other stuff)
> >> ok, first I need to install ESQL (root id must involve)
> >> 2nd, I need to compile the dbdelete.ec I guess?
> >> seems like some modifications will be neede to the dbdelete before
> >> compiling?
> >> Thanks.
> >> kern --
> >>
> >> ----- Original Message ----
> >> From: Art Kagel <art.kagel@gmail.com>
> >> To: ids@iiug.org
> >> Sent: Friday, June 13, 2008 1:27:32 PM
> >> Subject: Re: Help Please: delete rows in a table and co.... [12423]
> >>
> >> Then you will have to code it in a host language where you can limit the
> >> number of rows you process at a time by managing the cursor. That means
> >> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
> >> fetch the entire 8192 maximum number of rowids that it can in a single
> >> FETCH. One big reason that it is as fast as it is.
> >>
> >> I take it you are having trouble getting utils2_ak approved for
> production
> >> use. Let me know if I can help that. Perhaps they would prefer a formal
> >> commercial license with warantees and -limited- liability? Don't know
> why
> >> anyone would want to pay for something they can get for free, but if
> >> that's
> >> what they want, let me know. I've done it before.
> >>
> >> Art
> >>
> >> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >>
> >> > Art,
> >> > I can't do "select first" because this is version 7.31 -- that's the
> >> > problem.
> >> > Kern --
> >> >
> >> > ----- Original Message ----
> >> > From: Art Kagel <art.kagel@gmail.com>
> >> > To: ids@iiug.org
> >> > Sent: Friday, June 13, 2008 1:02:17 PM
> >> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
> >> >
> >> > What my dbdelete utility does is the equivalent of:
> >> >
> >> > LOOP:
> >> >
> >> > BEGIN WORK;
> >> > SELECT FIRST 8192 ROWID as id
> >> > FROM target_table
> >> > WHERE id_col IN (SELECT id FROM lookup_table)
> >> > INTO TEMP fred;> >> >
> >> > DELETE FROM target_table
> >> > WHERE ROWID IN (SELECT id FROM fred);> >> >
> >> > COMMIT WORK;
> >> >
> >> > GOTO LOOP;
> >> > .......
> >> >
> >> > Except that it doesn't use a temp table, it builds the IN clause on
> the
> >> fly
> >> > using the selected ROWIDs.
> >> >
> >> > Art
> >> >
> >> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com>
> >> wrote:
> >> >
> >> > > ROWIDs are NOT sequential integers starting from 1! They are
> normally
> >> the
> >> > > relative page number on which the row resides shifted left 8 bits
> plus
> >> > the
> >> > > slot number on the page containing the pointer to the row's data.
> >> > >
> >> > > So the first row on the first page of a table is ROWID 0x00000101 or
> >> 257
> >> > > and the first row on the second page of the table is ROWID
> 0x00000201
> >> or
> >> > > 513.
> >> > >
> >> > > You cannot use ROWID like your have consistently, and certainly it
> >> will
> >> > not
> >> > > delete 1000 rows at a time since only 1-255 rows can fit on a page.
> If
> >> > only
> >> > > two rows fit on a page, your example would delete only six rows at a
> >> > time.
> >> > >
> >> > > Art
> >> > >
> >> > >
> >> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <
> sgkatke@us.ibm.com>
> >> > > wrote:
> >> > >
> >> > >> Hello Kern,
> >> > >> You can use the following SQL:
> >> > >>
> >> > >> delete from tab1
> >> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid> >> bet=
> >> > >> ween
> >> > >> 1 and 1000);
> >> > >>
> >> > >> Increment the values in between clause by 1000.
> >> > >>
> >> > >> =
> >> > >>
> >> > >> Sudhir G Katke =
> >> > >>
> >> > >> SWG Client Support - Information Management =
> >> > >>
> >> > >> Software =
> >> > >>
> >> > >> Premium Support Analyst =
> >> > >>
> >> > >> Phone: 479 426 4308 Internal: 624 7341 =
> >> > >>
> >> > >> sgkatke@us.ibm.com =
> >> > >>
> >> > >> =
> >> > >>@@N
I guess, once again I did try but was not successful in using Art's dbdelete
due to not having esql available (not for a technical reason). Well until next
time.
Thank you Art et al for your inputs.
Kern --
----- Original Message ----
From: Art Kagel <art.kagel@gmail.com>
To: ids@iiug.org
Sent: Sunday, June 15, 2008 9:48:46 PM
Subject: Re: Help Please: delete rows in a table and co.... [12434]
If the table was not created WITH ROWID dbdelete will still work, as long as
there is a unique key you can supply to the -u option, but it will not run
quite as fast. Don't know about your particular server, but my runs have
typically taken about 10-12 seconds to delete 8192 rows or about 800 a
second.
Art
On Sun, Jun 15, 2008 at 5:37 PM, Kern Doe <kern_doe@yahoo.com> wrote:
Thanks Art.
> No, the data is just standard, just character field columns; I'm talking
> about
> those joining fields.
> In addition, I need to mention that the table to purge is fragmented;
> therefore, there will be no rowid, is it a problem to your dbdelete?
> Yesterday, I randomly selected out about 10 rows from tableB (look up
> table)
> and hardcoded in a simple query via dbaccess to test the delete. Only half
> of
> these were matching in tableA and it took about 3 seconds to finish.
> Through
> my SP with similar structure to Dharmendra's suggestion, it averages out
> 5,6
> rows/sec. So, I'm confused, perhaps no matter how we try, 20 or more rows
> deleted per second may not be achievable for this table?
> Regards
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Saturday, June 14, 2008 9:54:29 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12431]
>
> Oh, forgot, I uploaded an updated edition of the utils2_ak package on
> Friday. Though it only has updates to dostats and myschema, the dbdelete
> you already have is up-to-date.
>
> Art
>
> On Sat, Jun 14, 2008 at 9:53 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Kern,
> >
> > Unless you are using non-standard datatypes that dbdelete might not
> > recognize (and that would only matter if those types were used in the
> filter
> > to select rows), dbdelete will work out of the box. Just compiling
> > dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
> > package 'make's out of the box as long as you have GNU Make (you have to
> > make a minor change to the main make file (Makefile) and the makefile for
> > myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
> > documented in the BUILD file). To build the entire package if you have
> GNU
> > make just unpack the shell archive by executing it and type 'make'.
> >
> > Art
> >
> >
> > On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >
> >> Hi Art,
> >> The problem is not about $$$ -- it is a change to the system that I
> don't
> >> want
> >> to do yet (even it is a dev box) unless I exhaust all my options then I
> >> will
> >> push for it convincing that the dbdelete is the way to go no need to
> >> reinvent
> >> the wheel. Upon getting the suggestion from Obnoxio a couple of days
> ago,
> >> I
> >> actually down loaded both: your utilities and the free SDK (for Solaris
> 7)
> >> but
> >> have not done anything yet
> >> Perhaps you are the creator then may I ask you directly, how difficult
> is
> >> it
> >> to compile the dbdelete? (I don't need other stuff)
> >> ok, first I need to install ESQL (root id must involve)
> >> 2nd, I need to compile the dbdelete.ec I guess?
> >> seems like some modifications will be neede to the dbdelete before
> >> compiling?
> >> Thanks.
> >> kern --
> >>
> >> ----- Original Message ----
> >> From: Art Kagel <art.kagel@gmail.com>
> >> To: ids@iiug.org
> >> Sent: Friday, June 13, 2008 1:27:32 PM
> >> Subject: Re: Help Please: delete rows in a table and co.... [12423]
> >>
> >> Then you will have to code it in a host language where you can limit the
> >> number of rows you process at a time by managing the cursor. That means
> >> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
> >> fetch the entire 8192 maximum number of rowids that it can in a single
> >> FETCH. One big reason that it is as fast as it is.
> >>
> >> I take it you are having trouble getting utils2_ak approved for
> production
> >> use. Let me know if I can help that. Perhaps they would prefer a formal
> >> commercial license with warantees and -limited- liability? Don't know
> why
> >> anyone would want to pay for something they can get for free, but if
> >> that's
> >> what they want, let me know. I've done it before.
> >>
> >> Art
> >>
> >> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >>
> >> > Art,
> >> > I can't do "select first" because this is version 7.31 -- that's the
> >> > problem.
> >> > Kern --
> >> >
> >> > ----- Original Message ----
> >> > From: Art Kagel <art.kagel@gmail.com>
> >> > To: ids@iiug.org
> >> > Sent: Friday, June 13, 2008 1:02:17 PM
> >> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
> >> >
> >> > What my dbdelete utility does is the equivalent of:
> >> >
> >> > LOOP:
> >> >
> >> > BEGIN WORK;
> >> > SELECT FIRST 8192 ROWID as id
> >> > FROM target_table
> >> > WHERE id_col IN (SELECT id FROM lookup_table)
> >> > INTO TEMP fred;> >> >
> >> > DELETE FROM target_table
> >> > WHERE ROWID IN (SELECT id FROM fred);> >> >
> >> > COMMIT WORK;
> >> >
> >> > GOTO LOOP;
> >> > .......
> >> >
> >> > Except that it doesn't use a temp table, it builds the IN clause on
> the
> >> fly
> >> > using the selected ROWIDs.
> >> >
> >> > Art
> >> >
> >> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com>
> >> wrote:
> >> >
> >> > > ROWIDs are NOT sequential integers starting from 1! They are
> normally
> >> the
> >> > > relative page number on which the row resides shifted left 8 bits
> plus
> >> > the
> >> > > slot number on the page containing the pointer to the row's data.
> >> > >
> >> > > So the first row on the first page of a table is ROWID 0x00000101 or
> >> 257
> >> > > and the first row on the second page of the table is ROWID
> 0x00000201
> >> or
> >> > > 513.
> >> > >
> >> > > You cannot use ROWID like your have consistently, and certainly it
> >> will
> >> > not
> >> > > delete 1000 rows at a time since only 1-255 rows can fit on a page.
> If
> >> > only
> >> > > two rows fit on a page, your example would delete only six rows at a
> >> > time.
> >> > >
> >> > > Art
> >> > >
> >> > >
> >> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <
> sgkatke@us.ibm.com>
> >> > > wrote:
> >> > >
> >> > >> Hello Kern,
> >> > >> You can use the following SQL:
> >> > >>
> >> > >> delete from tab1
> >> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid> >> bet=
> >> > >> ween
> >> > >> 1 and 1000);
> >> > >>
> >> > >> Increment th