Commit within Update

[Astrocloud]

I believe that Informix 9.4 is ANSI

I am updating a table from a temp table. The problem is that I am trying to update too many records and I believe that I am running out of room.

My temp table looks like this:

06116047933201|X2007:247:1188|01|
06116047933301|X2007:247:1189|01|
06116047933401|X2007:247:1191|01|
06116047954701|X2007:250:1121|01|
06116047996101|X2007:259:0136|01|
06116047996201|X2007:259:0136|02|
06116047996301|X2007:259:0136|03|
06116024013111|X2007:260:0456|01|
06116024013101|X2007:260:0456|02|
06116024013201|X2007:260:0456|03|

only it's big. (16000+ rows)


I load it like thus:

create temp table t_table (markcode CHAR(14), X_code CHAR (15), lines INT);
load from "/home/astrocloud/large.mark.list.txt"
insert into t_table;

Here is my update:

update rin1 set rin1.markcrn = (select t_table.markcode from
t_table, rin0 where
t_table.X_code = rin0.code and
rin0.skey = rin1.tkey
and t_table.lines = rin1.lineno)


Note that there is no "Where" statement after the embedded select. I have tested this code on a short version of the temp table and it works fine. Sometimes to avoid the query of "do you really want to update ..." -I insert a "where rin1.tkey > 0" but it's not necessary.

Here's the problem, when updating 16000 rows -it runs out of memory. Works fine on 100 rows. Works terrible on lots more.

I was told by a coworker that it is possible to insert a variable and increment it for every update -then after a certain amount of updates run a "Commit"

Does anyone know how to do this?

Thanks,

Astro

 

[rac2]

Commit is used with transactions; transactions are specific to the database product, they are not ANSI SQL.

Managing resource issues is also specific to the product.

I would think that a process involving 16000 rows would be a one-time only process, not something that would be used in a transaction situation. If so then you might try creating a table, doing the work, then drop it. Instead of using a temporary table.

Here are links to Informix forums

http://www.tek-tips.com/threadminder.cfm?pid=179

http://www.tek-tips.com/threadminder.cfm?pid=404

http://www.tek-tips.com/threadminder.cfm?pid=876

 

[johnherman]

Alot also depends on your server size. 16k transactions at my company is small, less than a day's worth. So you either need more memory, if this is a regular process, or, as rac2 stated, this is a one-time process. In that case, perhaps you can split the data into several tables and process each table separately. Give us some more details and we can help better.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[r937]

rac2, just an FYI, you can simply write forumNNN and the system will append the name of the forum

so if you write ...

forum[red][/red]179
forum[red][/red]404
forum[red][/red]876

then the links will show up like this --

forum179
forum404
forum876



r937.com | rudy.ca

 

[rac2]

thnx for that. Of course it leads to the question of how you wrote

[ignore]forum179[/ignore]

without creating a link.

 

[r937]

like this -- forum[red][/red]179

;-)

r937.com | rudy.ca

 

[PHV]

A more classical way:
to get [ignore]forum179[/ignore]
type [ignore][ignore]forum179[/ignore][/ignore]