RE: deleting 300,000 records with 50000 locks
Posted in 1998
Topics: Stored Procedures & SPL, Connectivity: ESQL/C, 4GL & Embedded SQL, Server Administration, Migration, Import/Export & Data Conversion
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--