RE: SQL Delete question -- MIDRANGE-L

Dennis,

When I need to run SQL in batch, you've got a few choices.  For very long
runs (more than a few hours), I'd put the statement in a source member and
submit a RUNSQLSTM command to execute it.  For relatively quick queries
(hour or two) I'd use iSeries Navigator SQL tool.  QM queries also have a
batch mode, as I recall.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Dennis Munro [mailto:DMunro@badgerminingcorp.com]
Sent: Tuesday, January 21, 2003 11:24 AM
To: Midrange List (E-mail)
Subject: SQL Delete question


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

<SNIP>
DELETE FROM BPCSF/ESN WHERE SNTYPE = 'P' and SNDESC =   
'Billing date: approx. 4/15/02 March 2002 usage'        
Z<SNIP>

Sure is a simple "program" to accomplish the job but what about performance?
We run 24x7 so letting the SQL statement run some weekend would impact the
least amount of users if that is the only option & I am not against doing
that???

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

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.