IDS Forum

Re: Help Please: delete rows in a table and co....

Posted By: Kern Doe
Date: Sunday, 15 June 2008, at 5:37 p.m.

In Response To: Re: Help Please: delete rows in a table and co.... (Art Kagel)

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 =
>> > >>
>> > >> =
>> > >>
>> > >> |
>> > >>
>> >--------------------------------------------------------------------=
>> > >>
>> -----------------------------------------------------------------------=
>> > >> -------|
>> > >> |------------>
>> > >> | 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.
>> >
>> >
>> >
>> >
>>
>>
>>
*******************************************************************************
>> > 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.
>>
>>
>>
>>
*******************************************************************************
>> 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.

Messages In This Thread

• Help Please: delete rows in a table and commit eve
  Kern Doe -- Friday, 13 June 2008, at 11:52 a.m.
  • RE: Help Please: delete rows in a table and commit
    Jack Parker -- Friday, 13 June 2008, at 12:19 p.m.
  • Re: Help Please: delete rows in a table and commit
    Keith Simmons -- Friday, 13 June 2008, at 12:39 p.m.
  • Re: Help Please: delete rows in a table and commit
    Sudhir G Katke -- Friday, 13 June 2008, at 12:47 p.m.
    • Re: Help Please: delete rows in a table and co....
      Art Kagel -- Friday, 13 June 2008, at 12:55 p.m.
    • Re: Help Please: delete rows in a table and co....
      Art Kagel -- Friday, 13 June 2008, at 1:02 p.m.
      • Re: Help Please: delete rows in a table and co....
        Kern Doe -- Friday, 13 June 2008, at 1:19 p.m.
        • Re: Help Please: delete rows in a table and co....
          Art Kagel -- Friday, 13 June 2008, at 1:27 p.m.
          • Re: Help Please: delete rows in a table and co....
            Kern Doe -- Friday, 13 June 2008, at 1:39 p.m.
            • Re: Help Please: delete rows in a table and co....
              Art Kagel -- Saturday, 14 June 2008, at 9:53 p.m.
            • Re: Help Please: delete rows in a table and co....
              Art Kagel -- Saturday, 14 June 2008, at 9:54 p.m.
              • Re: Help Please: delete rows in a table and co....
                Kern Doe -- Sunday, 15 June 2008, at 5:37 p.m.
                • Re: Help Please: delete rows in a table and co....
                  Art Kagel -- Sunday, 15 June 2008, at 9:48 p.m.
                  • Follow up on --- Re: Help Please: delete rows in a
                    Kern Doe -- Wednesday, 18 June 2008, at 2:37 p.m.
        • Re: Help Please: delete rows in a table and co....
          Art Kagel -- Friday, 13 June 2008, at 1:34 p.m.
  • RE: Help Please: delete rows in a table and commit
    dharmendra sharma -- Friday, 13 June 2008, at 2:01 p.m.
    • Re: Help Please: delete rows in a table and co....
      Kern Doe -- Friday, 13 June 2008, at 3:07 p.m.
      • RE: Help Please: delete rows in a table and co....
        dharmendra sharma -- Friday, 13 June 2008, at 3:30 p.m.
      • Re: Help Please: delete rows in a table and co....
        Art Kagel -- Saturday, 14 June 2008, at 10:30 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.