RE: SQL Delete question - Finished

[rob@xxxxxxxxx]

If you're truly a 24x7x52 shop there are packages out there to reorg a 
file while active.

Ideally you'd avoid the sticky situation in the first place by:
a)  CHGPF REUSEDLT(*YES)
b)  Some sort of process to avoid huge invalid data loads without shooting 
your users in the foot like maximum records would.

Rob Berendt
-- 
"They that can give up essential liberty to obtain a little temporary 
safety deserve neither liberty nor safety." 
Benjamin Franklin 




Dennis Munro <DMunro@badgerminingcorp.com>
Sent by: midrange-l-bounces@midrange.com
01/22/2003 08:56 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     "'Midrange Systems Technical Discussion'" 
<midrange-l@midrange.com>
        cc: 
        Fax to: 
        Subject:        RE: SQL Delete question - Finished


Thanks to all that answered.

Martin, your thought about the records all being contiguous was correct. I
used Linoma Software Surveyor File Edit process which shows the rrn &
filtered each p/o group & deleted the records by the rrn range.  I did it 
on
the production box after 5 PM & the time to delete the 6.4 million+ 
records
was about 25 minutes which included the time to get the logicals back in
synch.  I had done a CPYF to a work file & run the SQL delete by rrn 
against
the work file & it took less than 8 minutes BUT there were no logicals 
over
it & I was the only one using the file.

Richard & Tom mentioned the CPYF process & that would have been my next
attempt if the SQL would not have worked out.  I do have to run the CPYF 
to
get rid of the deleted records but I will wait until month-end & get rid 
of
the deleted records.

Again, thanks to all for their suggestions.

Thanks - Dennis.

Dennis Munro

"I love deadlines.  I especially like the whooshing sound they make as 
they
go flying by."  Dilbert's Words Of Wisdom:

Badger Mining Corporation
www.badgerminingcorp.com
dmunro@badgerminingcorp.com
(920) 361-2388

-----Original Message-----
From: Chris Bipes [mailto:chris.bipes@cross-check.com]
Sent: Tuesday, January 21, 2003 4:22 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Delete question

Run your SQL statement from Ops Nav or MS SQL.  It then runs as
Client/Server.  I am moving away from STRSQL to PC based packages.  In Ops
Nav highlight databases,  The lower plane shows "Run an SQL Script".

-----Original Message-----
From: Dennis Munro [mailto:DMunro@badgerminingcorp.com]

V5R1M0 fairly current on all group/cum ptf's

The PO processing in BPCS must have gone haywire one day because I had 2,
085,055 notes written for one given PO - possibly a P/C locked up but who
will remember what happened 10+ months ago.

When I go to delete them using SQL, I aborted the job after 35 minutes
because I just had no clue how long it would take plus I had to get home.
Looking at it today, I did get about 1,350,000 records deleted in that 
time
frame.  This was run on our test box which is a 720 & I was the only 
person
signed on at the time

 My production machine is an 820 with 35(???) interactive and I guess I am
making the assumption that STRSQL is a green screen/interactive job & what
would be a way to better delete this number of records?  The real problem 
is
the above scenario happened three times so I have 6,444,070 ESN records to
be deleted.  I would get killed if I ran that during the day with that 
kind
of performance hit.

Below is the SQL statement used & I am wondering is there something that
would "speed" up the process.  .
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
The information contained in this e-mail message is confidential 
information
intended only for the use of the individual or entity named above. If the
reader of this message is not the intended recipient, or the employee or
agent responsible to deliver it to the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this
communication is strictly prohibited. If you have received this
communication in error, please immediately notify us by telephone, and
return the original message to us at the below address via the U.S. Postal
Service, and delete it from your computer.  Badger Mining Corporation P.O.
Box 328  Berlin, WI 54923  920-361-2388
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo.cgi/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.