Re: Loading ASCII data
>
> 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 ).
Syntax is
Create a File 'dbsload.scs' and the enter the following
( Assuming '|' as the delimiter in ASCII File )
FILE "<ASCII File>" DELIMITER "|" <Number of Columns in Table>;
INSERT INTO <Table>;
( Assuming Byte Position in ASCII File )
FILE "<ASCII File>" (
Field1 StartPos - EndPos,
Field2 StartPos - EndPos,
Field3 StartPos - EndPos,
.......
);
INSERT INTO <Table> (
Column1, Column2, Column3 ... );
After this Execute the following from Command Line
$ dbload -d <Database> -c dbsload.scs -l <Log File> -n 500
[ # Rows to Commit
Before next 500 ]Dbload Will read dbsload.scs and Insert into Table
Note : Make Sure [ # of Rows Commit synchronize your Number of Locks
Stuff has been tested and proven for loading more than 1 Million at one go.
I Hope this serve the purpose.
Rgds,
Chetan Anand
DHL INTERNATIONAL (Singapore) Pte Ltd
Singapore
email : anand@sin-co.sin-ro.dhl.com
[ Patience is Bitter but its fruit is Sweet ]