Hi,
I am trying to read data from a huge flat file, row by row and after some manipulations of the data, inserting them into a db2 table. This process has to be scheduled. Can I do this using stored procedure? Will I be able to read a flat file using a stored procedure? Please let me know how to go about this.

thanks a bunch,
prabhu.

The best solution would probably be to use embedded SQL with a C or Java program. Check out the Application Development Guide: Building and Running Applications for details on how to write and compile such a program.

In the past I've used PERL against the file to do a sort of pre-processing to get it into an IMPORT/LOAD-friendly format.

A stored procedure would not be my first choice.

You could also do as Marcus suggests and write an application to do it, but that sounds a lot like work. And you won't get the benefit of the LOAD performance.

marcus,
In fact i have an application in java which reads the flat file and writes it into the database. But this seems to be very slow, takes hours together as the input file is huge around 60mb and contains more than a million records. This is because we read one row from the file using java, do the manipulation in java and then make a db call to insert that into the table. The network slows down the entire process, thats why am looking if stored procedure can be a solution.

Where's the file sitting, does the server have access to it?

Have you looked at Java's batch processing?

PreparedStatement pstmt =
con.prepareStatement(
"INSERT INTO BATCHTEST (COL1, COL2) "
+ " VALUES (?, ?)");
for (int i = 0; i < NUM_ITER; i++) {
pstmt.setString(1, "STRING" + i);
pstmt.setString(
2,
"lkajlskjdflkaasdfasdfasdfajsdlfkjasldfkjalskdjflk asjdf" + i);
pstmt.addBatch();
}
try {
int[] res = pstmt.executeBatch();
con.commit();

Petruk,
Thanks. The requirement we have is that when a record fails, the record alone should be discarded, but in case of a batch update, the entire set would be rolled back, if am not wrong. Again, am not sure what would be the effect on the server memory, if I load the entire 1 million record, in one batch update.

You wouldn't load the entire thing into one batch, break it up into smaller bits (say, 100 INSERT/UPDATES). When the db is remote, it's a big performance boost.

There's more info here:
http://publib.boulder.ibm.com/infoce...d/cjvbtupd.htm

It will continue processing even if some statements fail, but you have to process the resulting BatchUpdateException. It's a little funky, but the docs have that info.

petruk,
thanks a lot for the information that you have provided. i would try to work it out in those lines. But, still, am wondering if a SP can do it all, reading from a file and writing to the db

If the file is local to the DB2 server, there's no reason a SP couldn't do it.