wood burning stoves 2.0*
The moose likes Java in General and the fly likes Error while inserting 200mb blob data in DB2 database. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Error while inserting 200mb blob data in DB2 database." Watch "Error while inserting 200mb blob data in DB2 database." New topic
Author

Error while inserting 200mb blob data in DB2 database.

Bipra De
Greenhorn

Joined: Nov 06, 2011
Posts: 14
Hi,
I am using hibernate and trying to insert a file of size 200mb in the db2 database(column type as Blob). The corresponding java attribute is of type byte[]. Things work fine with small files and with big files it takes too much of time for insert. I have to read the file from a remote server and then insert. Can anyone please help me what will be the good practice to do this. document has to be saved in database as its client requirement.
Thanks.


Warm Regards,
Bipra De.
SCJP 1.6
SCWCD 1.5
Winston Gutkowski
Bartender

Joined: Mar 17, 2011
Posts: 8427
    
  23

Bipra De wrote:I am using hibernate and trying to insert a file of size 200mb in the db2 database(column type as Blob). The corresponding java attribute is of type byte[]. Things work fine with small files and with big files it takes too much of time for insert...

That's incredibly vague. How much time is 'too much time'?

For example: How long does it take to simply copy a 200Mb file from your remote server?
If that takes "too long", then nothing you do in Java or Hibernate is going to help you. You'll also be in a much better position to determine the actual overhead involved in your insert.

First measure; then optimize.

Winston


Isn't it funny how there's always time and money enough to do it WRONG?
Articles by Winston can be found here
Bipra De
Greenhorn

Joined: Nov 06, 2011
Posts: 14
Hi,

Regarding the file transfer i think that can be very well accomplish by using FTP and that will not take much time also. And it's almost taking 20-25 min to insert the file in the database using hibernate when the file is on my local system and database on different server.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2448
    
  28

Generally, with big Blobs. you are better off using java.sql.Blob instead of byte[]. Hibernate provides utility methods that can stream any InputStream into a Blob

However, having said that, I agree with Winston:- first measure then optimize. One thing you need to look at is how GC is behaving. Remember that generally speaking, because of how GC behaves, Java apps become slow before they go out of OOM. Many times, performance issues are really because your app is at the point of OOM. It's important to check GC when you observe that loading lots of data in memory has exponentially reduced the performance of the system. Not saying that is the only issue. Just another data point to look at.
Bipra De
Greenhorn

Joined: Nov 06, 2011
Posts: 14
Hi,

Is there any API or something which insert the data in chunks in db2 ?? I know inserting big size blob can cause performance issue but here i just need to get the value from the web service from remote application and insert in my database.

Thanks...
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2448
    
  28

Hibernate.createBlob will stream the data from an InputStream into the Blob's OutputStream in chunks.

The Blob's OutputStream is provided by the JDBC driver, and Hibernate doesn't have any control over how much bufferring the JDBC driver does on it's own end. We have tested this with extremely large Blobs (4GB.. yeah.. don;t ask why) on Oracle. I am pretty sure that Oracle's JDBC driver pretty much just sends the chunk to the Oracle server. DOn't know what DB2 driver will do.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error while inserting 200mb blob data in DB2 database.