Hi all,

I would like to perform multiple updates within a single database open and close.

Box: solaris
dB: informix

This is how i do perform for single update

dbaccess <dbname> -<<EOF
update table1 set col1=1 where col2=1
EOF

i have the col1 values in a file (some 1000 values)
now how do i proceed with an update of 100 values at a time and give a commit. The above sample is time consuming as it has to open and close the database for each and every update done.


And between the dbaccess and EOF i cannot read from the file as within them only database operations are allowed and no file manipulation, storing in variables are allowed.

Any help would be greatly appreciated.

Thanks.

Hi All,

got the solution myself but in a different way and it is efficient compared to the one i had thought of doing that.

dbaccess <dbname> -<<EOF
update table1 set col1=1 where col2=1
EOF

Instead of
getting the values
opening the database
performing single update
closing the database

Generate the .sql file which generates all the database statements within one begin and commit statement

and that sql file could be fed to the database as

dbaccess <dbname> <.sql filename>

When the i tried the previous method it took some 25 minutes for 12000 records
and in the later method only 3 minutes.

Any ideas welcome.