Re: Loading ASCII data
In article h9t@emory.mathcs.emory.edu, anand@sin-co.sin-ro.DHL.COM (Chetan anand) writes:
]>
]> Anybody know how many locks are used per row when one is loading data
]> into a table? I'm trying to load 110,000 rows from a pipe-delimited
]> ASCII file into a table.
]>
]> load from "/tmp/ASCIIfile"
]> insert into so_and_so_rec
]> # ^
]> # 271: Could not insert new row into the table.
]> # 847: Error in load file line 42836. <<<------ This number varies
]> slightly each time
]> I try the load.
]>
]> Though you might not think so from the error messages, I'm quite sure
]> I'm running out of locks. When I start the process, tbstat -k shows
]> about 3000 locks. Near the time the load fails the tbstat shows over
]> 240,000 locks (260,000 max). After it fails the number of locks drops
]> immediately back to ~3000.
]>
]> But the process only loaded ~42,000 rows! Am I to conclude that each
]> row loaded takes six locks? For what? I've heard of "locking twins",
]> but ...
]>
]
]> [ Stuff Deleted ... ]
]
]Hi,
]
]Since I had the same problem sometime back. I shifted my decision to use
]DBLOAD in which you can load ASCII File with or without FIELD delimiters
]and U can also specify after how many rows U want to commit ( which again
]depends of Number of Locks ).
[stuff deleted ...]
]I Hope this serve the purpose.
]
]Rgds,
]
]Chetan Anand
Another solution is just to get an exclusive lock on the table. This way
you don't run out of locks - you only hold one lock.
Try...
lock table so_and_so_rec in exclusive mode;
load from "/tmp/ASCIIfile"
insert into so_and_so_rec;
- Ben Kelley.
---
This e-correspondance is PostcardWare (tm). You are hereby granted the licence
to view, read, and otherwise inwardly digest this message for 14 days, after
which, for continued use, you should register by sending a post card to the
address below. Registration entitles you to free email support in understanding
future signatures.
Ben Kelley - SBC Australia, PO Box N100, Grosvenor Pl, Sydney, 2000, Australia
(e-postcards to ben@syd.au.swissbank.com) Ph +61 (0)2 258 2382