loading data

[penzk001]

People,

I am tring to load a table that contains 500000 rows, using the load statement.

The syntax I am using is:
load from 'movements.unl' insert into movements;


The load starts OK, after a while the loading stops saying

271: Could not insert new row into the table. 134: ISAM error: no more locks
847: Error in load file line 304000.


The system currently has 120000 locks.

I am doing something wrong ??

 

[cdlvj]

Try to lock the table with exclusive mode.

Also the load is being treated as one transaction. There should be a set statement to process commits at certain intervals.

But the lock should take care of it.

 

[penzk001]

Can you give me an example please ?

 

[cdlvj]

BEGIN WORK
LOCK TABLE movements IN EXCLUSIVE MODE
load from 'movements.unl' insert into movements;
COMMIT WORK

http://www-306.ibm.com/software/data/informix/pubs/library/ids_92.html

 

[vpshriyan]

Hi penzk,

Loading massive data in a single transaction has it's own pitfalls. You can control the escalation of the locks by locking the table in exclusive mode. However it may result in Long Transaction, should it find the log space insufficient to record the changes.

You may read a FAQ on how to avoid such situation at:
faq179-2537

You may use command line utility "dbload" to load bulk or mass loads.

Help:
dbload --

Example:
dbload -d dbname -c cmdfile -l error.log -n rows_to_commit -k

cmdfile
FILE 'datafile' DELIMITER '|' no_of_columns ;
INSERT INTO table_name ;

Regards,
Shriyan

 

[penzk001]

Thanks, for your help, i have managed to load the table, using the dbaccess method. However I now moving to load using the dbload, as I have some problems using the LTX.


Thanks
Kenneth