Question

Doubts regarding speed in data access

Database, Oracle, DB2, Windows, Java/J2EE, SQL

y name is Yahya. I am working as a Project Leader in an IT Industry in India

I am working on a new project with J2EE technology.

I am using Weblogic Application Server and Iplanet Web server for this project. It is for a bank. I will have to write a program in the server which will read a text files each containing records ranging from 700 to 2000.

These text files are submited by the users online. These records should be updated to their appropriate databases (there are totally 6 databases). I have a common table which will help me in locating the appropriate database to which I should append these records.

Since there will be a lot of such text files uploaded by the user, I need to find the most efficient way to update the database.

Some of the suggestions I received were to use
1. c/c++ routines to update the database.
2. PL/SQL routines to update the database.

It would be great if you can suggest me a best approach this problem.

Thank you.

The fastest possible way is to use a compiled language, with this logic flow:

Have a large, static array of record buffers
Open the input as a streamed (Unix or OS/400) or double-buffered (zOS) file.
Loop
Read a record
Leave the loop if EOF
Validate the record
If passed
Store its values into the array and increment the count
If the count is the same as the number of records the array can hold
Do a block insert of the array into the table
Set the count back to zero
End-if
Else
Generate an appropriate error message
End-if
End-loop
Close the input file
If there were errors
Roll back
Else
If there are records in the array
Do a block insert of the used portion of the array into the table
End-if
Commit
End-if

---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)