Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    10

    Unanswered: Reading data from a flat file

    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.
    thanks,
    prabhu.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by prabhu22
    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.
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Jan 2004
    Posts
    10
    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.
    thanks,
    prabhu.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by prabhu22
    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();
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Jan 2004
    Posts
    10
    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.
    thanks,
    prabhu.

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by prabhu22
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Jan 2004
    Posts
    10
    Quote Originally Posted by J Petruk
    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
    thanks,
    prabhu.

  9. #9
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by prabhu22
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •