RE: deleting 300,000 records with 50000 locks
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------ =_NextPart_000_01BD7395.92F1ABF8
Content-Type: text/plain
Sukumar,
I don't know why you don't just write a small program (4GL, ESQL/C, etc.) to
delete the 300,000 rows, doing a COMMIT after every n rows have been
processed, but...
To re-load the 700,000 rows, I'd suggest that you use the DBLOAD utility.
It will allow you to do a COMMIT after every n rows have been loaded (in
fact, the DBLOAD option IS "-n"). You didn't say which version you are
using, but for 7.2x, DBLOAD is described in the Informix Migration Guide, pp
9-22 thru 9-37. For earlier versions, it's probably in the Admin. Manual,
or maybe even in the Guide to SQL. Regardless, I do know that DBLOAD has
been around for quite a while, so it should be available to you.
HTH
================================
Paul A. Mosser, Open Systems DBA
Wells Fargo Bank
Tempe, Arizona, U.S.A.
mosserp@wellsfargo.com
================================
> -----Original Message-----
> From: Sukumar Konduru [SMTP:sukumar@gte.net]
> Sent: Wednesday, April 29, 1998 6:58 AM
> To: informix-list@iiug.org
> Subject: deleting 300,000 records with 50000 locks
>
> Hi
>
> I need to delete 300,000 transactions for every 3 months. We have more
> than 1 million transactions in this table.
>
> What I do is as follows.
>
> 1. Prepare dbschema for the table
> 2. unload to a file required transactions. i.e recent 3 months
> transactions.
> 3. Drop the table
> 4. create table with dbschema
> 5. load from file.
> i.e load from "file.dat" insert into tablename
>
> We have only 50,000 locks. Only way I am able to load records in to
> database is by splitting
> into lesss than 10,000 records and inserting records from these small
> files one by one .
>
> Why I am not able to even insert 40,000 records at a time , inspite of
> having 50,000 locks.
> When I try to insert even 10,000 records, onstat shows that 40000
> locks are in use.
>
> Is every inserting transactions needs 4 locks?. It is surprising me.
>
> I can not use "BEGIN WORK", because we do not have more log files.
> All these problems are due to lack of RAM
>
> Could you please mail me
>
> sukumar@gte.net
>
> Thank you
> sukumar konduru
------ =_NextPart_000_01BD7395.92F1ABF8
Content-Type: application/ms-tnef
Content-Transfer-Encoding: base64
eJ8+IgwRAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy
b3NvZnQgTWFpbC5Ob3RlADEIAQSAAQAvAAAAUkU6IGRlbGV0aW5nIDMwMCwwMDAgIHJlY29yZHMg
d2l0aCA1MDAwMCBsb2NrcwALDgEJgAEAIQAAADE4MzgyNkU0N0NERkQxMTFCMUYzMDAwMDgzNzY4
RkY0AAwHASCAAwAOAAAAzgcEAB0ACgAmAAgAAwAxAQEFgAMADgAAAM4HBAAdAAoAIgAOAAMAMwEB
DYAEAAIAAAACAAIAAQOQBgCMCwAAKwAAAAsAAgABAAAAAwAuAAAAAABAADkAQC32BpVzvQEeAHAA
AQAAACsAAABkZWxldGluZyAzMDAsMDAwICByZWNvcmRzIHdpdGggNTAwMDAgbG9ja3MAAAIBcQAB
AAAAGwAAAAG9c49dkxFWKDjfexHRoh4IACu51WkAAOqDsAACAQkQAQAAAIsGAACHBgAAtwoAAExa
RnVLn9YnAwAKAHJjcGcxMjXSMgD7MzYB6CACpAPjCQIAY2gKwHNldDA+IAcTAoMAUALyELlUYR5o
A3ECgw5QEFZwcnHaMhFZMwPGEWV9CoAIyOwgOwlvDjA1Fz8KYAKA2QqBdWMAUAsDYwBBC2Bybg4Q
MDMVUQvEBgB15Gt1AMByLAqiCoQKgChJIGQCICcFQGtugm8H4HdoeSB5CGBRHTVqdXMFQHcFEHSg
ZSBhIHMAwGwDIAUUYG8JwGFtICg0AEdMLCBFU1FMDC9DIMARIGMuKSBkdG8dMGVsESAfYHTCaB9g
MzAwLCKwEUC5A2B3cyDAHUALgGcfccBDT01NSVQfcAGAewSQIVB2BJAeEAOgIyIgsxDgJOAgYgnh
H/JjB5CFERBkIMBidXQuJyAtHGpUIdAYoC0JAGFkJSJTNyK7SScooHN13GdnB5AFQCJgYQVAHjID
HuAiRERCTE9BRI0rIHQDEB9AeS4gKdD/HwEDEAMgH8EdwR4yIcIh0H8j7yT/JgIocgmAIHALgCBa
ZgDQdCDAK3lvBTBpBwIgKdAF8CItbiIpJSyRWR5CaWQdYnNh5x4QHfAN4GggL0EAkDKh/x4yCsAf
YB7gI6Em0zFABbHwNy4yeCDAK7UEACHhnwTyJfAooDEhImJJbjaBaG1peAXQaSAxMoNHCnUz0GUg
wHBwIDlsLTIUkCJgch5QOqAz6zbALKBGBbFlCsAsUC8BtzTVI1EfQCcEICABYgGgpmweEDhlQWQ5
IG4skHpNAHB1B0AgwAWxAMB5/yXwLyIDoDhlOgMhsiDxLJH4UmVnCxEiEAQQKcEuAv8doyqjK7UQ
4AQgJfMKwAhg9m4ooDaCcToQH1M0cSIQPSDAcyHQH0AfkBMwdWz/KKA/sSWwC3ALYAJgQOMeMfEn
S0hUSBxoGnIC0RIB4HMxNyA9Sm9LXBxk3FBhRnARUD6xbyaRHEA4IE9wJhEGsB7wZW2rBCArsEEc
ZFciAGwEIKpGCsBnIdBCAHBrJ7WvTlBN0CDABxF6AiBhIMDwVS5TLk0QHGQEYE1iuHBAd08yMVBP
oS4FoD5tHGRLX0t/SQgTszE2f0jqLFAPMQqgA2AfUDFwIOotWNJPBRBnC4AHQAXQ/SaBYSpQWNMc
ZlhkWDELE4NYZAIAaS0xNDQBQLksUDE4IrAMwVxzYjuglQNhOgyDYhvHIEsCIAZkCHAeUFtTTVRQ
JjoqIBwDQGcfUC5u/REgXRxlXaAGYAIwXgdPIHsz4AeQZDRAUNEUYAMRMgI5IMAxOTk4IDbkOjVj
0EFNYPcoEF4HDwuAOPQoYAQAdEBpaRUqMC4FsGdg+HVian9YkV4HIfMjoiKmKDEFoWTnBCAD8CJg
IDUi4RFACQD8Y2sQoFqPW5lX9AzQAcH/GoETwRVSDAFIZACgHHtgsPcw4SHIIqZ0IEAAgDFhPLKb
NnMvNDM/cAIhaHMskH9PICWUBGAYoBxkKqEDoDH/P3AtATKScUs4YzeRAZEiEP0nS1cqskJTN5FD
wQIQLVLrcyAcajEskFAYoAqxH2D8ZGIE8CJwAMA2cyJidrP9HGQyLJBEcCh0LiJcQEdRnxigRPEY
oXE7LJBpLn2ifyZwAjAsoHK2dBV+ahxkM90skEQDYDqAe500LJEFAL878CIyRzNqM3rGHGQ1LJH/
KHMxQANhfWMnRSygfzIoc82GUyKGo2KwdCI4UU1x/wVAC4AhwXazUUAHgHcbc2W/AiA9wWqAItNq
4yyQT4ui/nc0QR0gIFFHNihzabY4U35vHGSIsQGgQ8AfYDeRYv0eEHMLUB9AaNIcZIlzQfL/dpF0
gyLFacUAcDhCiSIjov9pp4ZTIlIrQR+jHGR9cgQg/wIgJdEeEJbCdw2NNR2wBUD/RzY/1okjXJCS
vJjxIbAHcZYgPPEAgHAfQm9mHGT/JaEjoovrd3YmER0gcUAeEP8hwYkFP+OSlWmmIMCLgR7w/yrB
RkElcSqjXJAi4RxkauPvNYMxISsxJ0tJBCAvNJPI13FLb9IEIDRq1D8skCzB/zeRKiBSkAUQNeI/
cKQ9g6AHA5GY0isyIkJFR0nCTmJAT1JLIibRBZD/TNArQR3geqEh0JjSc4cwkfcjwJZjJ0VBH9GV
VD1iIhDfTmF6gwpQjeMA0GucgQfw/2RHHHMIUUaBHjILUDvwK0H/AMADEYo8X/0nW3SBsLAeMV+z
2x2QXyQcZBZRALhgAAMA/T9SAwAAHgAxQAEAAAAQAAAATU9TU0VSUDQzOTkxREE3AAMAGkAAAAAA
HgAwQAEAAAAQAAAATU9TU0VSUDQzOTkxREE3AAMAGUAAAAAAHgBCEAEAAAAcAAAAPDZpN2JqZyQx
Y2wkMUBndGUxLmd0ZS5uZXQ+AAsAA4AIIAYAAAAAAMAAAAAAAABGAAAAAAOFAAAAAAAAAwAFgAgg
BgAAAAAAwAAAAAAAAEYAAAAAEIUAAAAAAAADAACACCAGAAAAAADAAAAAAAAARgAAAABShQAAdBAA
AB4AAYAIIAYAAAAAAMAAAAAAAABGAAAAAFSFAAABAAAABQAAADguMDIAAAAAAwACgAggBgAAAAAA
wAAAAAAAAEYAAAAAAYUAAAAAAAALAASACCAGAAAAAADAAAAAAAAARgAAAAAOhQAAAAAAAAMABoAI
IAYAAAAAAMAAAAAAAABGAAAAABGFAAAAAAAAAwAHgAggBgAAAAAAwAAAAAAAAEYAAAAAGIUAAAAA
AAAeAAiACCAGAAAAAADAAAAAAAAARgAAAAA2hQAAAQAAAAEAAAAAAAAAHgAJgAggBgAAAAAAwAAA
AAAAAEYAAAAAN4UAAAEAAAABAAAAAAAAAB4ACoAIIAYAAAAAAMAAAAAAAABGAAAAADiFAAABAAAA
AQAAAAAAAAADACYAAAAAAAMANgAAAAAAAwCAEP////8CAfk/AQAAAGcAAAAAAAAA3KdAyMBCEBq0
uQgAKy/hggEAAAAGAAAAL089V0VMTFMgRkFSR08gJiBDTy4vT1U9V0ZCMS9DTj1OT04tRVhDSEFO
R0UgUkVDSVBJRU5UUy9DTj1NT1NTRVJQNDM5OTFEQTcAAB4A+D8BAAAADQAAAE1vc3NlciwgUGF1
bAAAAAAeADhAAQAAABAAAABNT1NTRVJQNDM5OTFEQTcAAgH7PwEAAABnAAAAAAAAANynQMjAQhAa
tLkIACsv4YIBAAAABgAAAC9PPVdFTExTIEZBUkdPICYgQ08uL09VPVdGQjEvQ049Tk9OLUVYQ0hB
TkdFIFJFQ0lQSUVOVFMvQ049TU9TU0VSUDQzOTkxREE3AAAeAPo/AQAAAA0AAABNb3NzZXIsIFBh
dWwAAAAAHgA5QAEAAAAQAAAATU9TU0VSUDQzOTkxREE3AEAABzCgeYIHk3O9AUAACDD4q/GSlXO9
AR4APQABAAAABQAAAFJFOiAAAAAAHgAdDgEAAAArAAAAZGVsZXRpbmcgMzAwLDAwMCAgcmVjb3Jk
cyB3aXRoIDUwMDAwIGxvY2tzAAALACkAAAAAAAsAIwAAAAAAAwAGEKuM0YADAAcQUAYAAAMAEBAA
AAAAAwAREAAAAAAeAAgQAQAAAGUAAABTVUtVTUFSLElET05US05PV1dIWVlPVURPTlRKVVNUV1JJ
VEVBU01BTExQUk9HUkFNKDRHTCxFU1FML0MsRVRDKVRPREVMRVRFVEhFMzAwLDAwMFJPV1MsRE9J
TkdBQ09NTUlUAAAAAAMU
------ =_NextPart_000_01BD7395.92F1ABF8--