473,888 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

multiple insertions through jdbc

Hi everybody!

I'm using DB2 PE v8.2.3 for linux. I've defined a database with the
following schema:

ANNOTATION(ID,A UTHOR,TEXT)
ANNOTATION_BOOK (ANNOTATION_ID, OBJECT_ID)
BOOK(ID,AUTHOR, TITLE).

Between the book and annotation entities there is a many-to-many
relation: one annotation can involve many books and a book can be
annotated by many annotations. So the attributes ANNOTATION_ID and
OBJECT_ID of the ANNOTATION_BOOK table are foreign keys referring to the
ANNOTATION and BOOK tables ids, respectively. I'm using two sequences
for the attributes ID of tables ANNOTATION and BOOK: annotation_id_s eq
and book_id_seq.

I've to make batch insertions in this database that are the result of an
algorithm. The algorithm output is a set made up of thousands of
annotations involving a small number of books. These books might
already been stored in the database and obviously I don't want to have
multiple tuples that represent the same book, i.e., have the same values
for the attributes AUTHOR and TITLE.

The result of the algorithm has to be stored in the database through
JDBC. I'm using the driver in the packages: db2jcc.jar and db2java.jar.
My current solution is:

1. Query the table BOOK to determine the ids of the books that are
already persistent.

2. Inserting the book annotations in sequence in a batch. More
specifically:
2.1 Creating an instance of object java.sql.Statem ent, let's call it
statement.
2.2 Adding an sql insert command for table ANNOTATION to the statement:

insert into ANNOTATION(ID,A UTHOR,TEXT)
values(next value for annotation_id_s eq,...).

2.3 For each book involved in the book annotation:
2.3.1(a) If the book is not already persistent add two sql insert
command for table BOOK and table ANNOTATION_BOOK to the statement:

insert into BOOK(ID,AUTHOR, TITLE)
values(next value for public.book_id_ seq,...)

insert into ANNOTATION_BOOK (ANNOTATION_ID, BOOK_ID)
values(previous value for annotation_id_s eq,
previous value for book_id_seq);

2.3.1(b) If the book annotation involves a book that is already
persistent add one sql insert command for table ANNOTATION_BOOK with the
value that you retrieved at point 1 for the attribute BOOK_ID:

insert into ANNOTATION_BOOK (ANNOTATION_ID, BOOK_ID)
values(previous value for annotation_id_s eq,
<value_retrieve d_at_point_1>);
2.4 Execute the batch:

statement.execu teBatch();
This solution is not very efficient. I would like to improve the
insertion performance. Maybe after retrieving the ids of the persistent
books (point 1), I could ask the dbms to increase the book and
annotation sequence by how much I need and then insert the tuples one
table at the time, maybe using a prepared statement instead of a statement.

Do you have any suggestion?

Thank you very much,

Diego
Feb 15 '06 #1
2 2945
The nature of this question and the use of PE indicates this could be a
class problem.

The key to improving performance here is to minimize the overhead.
1. Use parameter markers and multiple statements.
2. Figure out a way to make the database manager do some of the work for
you.
3. Consider inserting multiple ANNOTATION_BOOK rows with each statement.

You'll have problems with your 1. because you've made no allowance for
the books you'll be adding to the table. You've also said nothing about
how you will be determining the id for the persistant book associated
with the current annotation. You've also said nothing about determining
that an annotation you are processing already exists in the annotation
table. (It's possible that all annotations are considered new and get a
new row in their table.)

Is a sequence appropriate? Would identity columns for BOOK and
ANNOTATION be an alternative?
Phil Sherman

Diego wrote:
Hi everybody!

I'm using DB2 PE v8.2.3 for linux. I've defined a database with the
following schema:

ANNOTATION(ID,A UTHOR,TEXT)
ANNOTATION_BOOK (ANNOTATION_ID, OBJECT_ID)
BOOK(ID,AUTHOR, TITLE).

Between the book and annotation entities there is a many-to-many
relation: one annotation can involve many books and a book can be
annotated by many annotations. So the attributes ANNOTATION_ID and
OBJECT_ID of the ANNOTATION_BOOK table are foreign keys referring to the
ANNOTATION and BOOK tables ids, respectively. I'm using two sequences
for the attributes ID of tables ANNOTATION and BOOK: annotation_id_s eq
and book_id_seq.

I've to make batch insertions in this database that are the result of an
algorithm. The algorithm output is a set made up of thousands of
annotations involving a small number of books. These books might
already been stored in the database and obviously I don't want to have
multiple tuples that represent the same book, i.e., have the same values
for the attributes AUTHOR and TITLE.

The result of the algorithm has to be stored in the database through
JDBC. I'm using the driver in the packages: db2jcc.jar and db2java.jar.
My current solution is:

1. Query the table BOOK to determine the ids of the books that are
already persistent.

2. Inserting the book annotations in sequence in a batch. More
specifically:
2.1 Creating an instance of object java.sql.Statem ent, let's call it
statement.
2.2 Adding an sql insert command for table ANNOTATION to the
statement:

insert into ANNOTATION(ID,A UTHOR,TEXT)
values(next value for annotation_id_s eq,...).

2.3 For each book involved in the book annotation:
2.3.1(a) If the book is not already persistent add two sql
insert command for table BOOK and table ANNOTATION_BOOK to the statement:

insert into BOOK(ID,AUTHOR, TITLE)
values(next value for public.book_id_ seq,...)

insert into ANNOTATION_BOOK (ANNOTATION_ID, BOOK_ID)
values(previous value for annotation_id_s eq,
previous value for book_id_seq);

2.3.1(b) If the book annotation involves a book that is already
persistent add one sql insert command for table ANNOTATION_BOOK with the
value that you retrieved at point 1 for the attribute BOOK_ID:

insert into ANNOTATION_BOOK (ANNOTATION_ID, BOOK_ID)
values(previous value for annotation_id_s eq,
<value_retrieve d_at_point_1>);
2.4 Execute the batch:

statement.execu teBatch();
This solution is not very efficient. I would like to improve the
insertion performance. Maybe after retrieving the ids of the persistent
books (point 1), I could ask the dbms to increase the book and
annotation sequence by how much I need and then insert the tuples one
table at the time, maybe using a prepared statement instead of a statement.

Do you have any suggestion?

Thank you very much,

Diego

Feb 17 '06 #2
Hi Phil,

I've made some modifications to this procedure that go in the direction
you were suggesting in your points 1,2 and 3 and I had a good improvement.
You've also said nothing about how you will be determining the id for the persistant book associated with the current annotation.
I assume that the pair of attributes (AUTHOR, TITLE) of the relation
BOOK is unique (actually the db is more complex than that but this was a
significant simplification) . So I query BOOK with two values for this
pair of attributes, e.g.:

select *
from BOOK " +
where AUTHOR = ? and TITLE = ?
You've also said nothing about determining that an annotation you are processing already exists in the annotation table. (It's possible that all annotations are considered new and get a new row in their table.)
Yes, that's exactly the case. All annotation are considered new and get
a new row.
Is a sequence appropriate? Would identity columns for BOOK and ANNOTATION be an alternative?


In the new procedure I cannot use identity columns.
The new procedure works as follows:

1. Query the table BOOK to determine the ids of the books that are
already persistent. To do that I execute a PreparedStateme nt for each
book:

String query = "select * " +
"from public.BOOK " +
"where AUTHOR = ? and TITLE = ?";
preparedStateme nt = connection.prep areStatement(qu ery);
for each book
preparedStateme nt.setXXX(1,... );
preparedStateme nt.setXXX(2,... );
resultSet = preparedStateme nt.executeQuery ();

As you suggested in point 1, I tried to execute a multiple statement
(using addBatch) with a preparedStateme nt but it is not supported with a
SELECT.

2. Request as many ids as needed for the annotations from the sequences
that manages the ANNOTATION table id:

preparedStateme nt = connection.prep areStatement("v alues nextval for
public.annotati on_id_seq");
for each annotation
ResultSet resultSet = preparedStateme nt.getResultSet ();

3. Request as many ids as needed for the books.

4. Inserts tuple in ANNOTATION table.
5. Inserts tuple in BOOK_ANNOTATION table.
6. Insert tuples in BOOK table.

Points 4,5 and 6 are implemented through a prepared statement batch
(what you called in your point "multiple statement", if I got it right).
As an example, consider point 4:

String sql = "insert into public.annotati on(id,author,te xt) " +
"values(?,?,?); ";
preparedStateme nt = connection.prep areStatement(sq l);
for each annotation
preparedStateme nt.setXXX(1,... );
preparedStateme nt.setXXX(2,... );
preparedStateme nt.setXXX(3,... );
preparedStateme nt.addBatch();

This is analogous to what I intended to do for point 1. But apparently
multiple sql commands with prepared statements don't work with SELECTs.

Thanks a lot!

Diego
Feb 20 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2273
by: Nils Valentin | last post by:
Hi MySQL Fans ;-), Is it possible that the 3.08 series allows to connect to 4.0.14 versions but not to the 4.1 alpha-versions ? I get belows error when tryig to connect from DbVisualizer which was working fine until a while ago. I haven't watched it recently, and haven't changed anything I am aware off right now. After I realized the problem I tried many 3.08 versions with the same symptom. Watch were it says "Unknown system variable...
1
2046
by: Sergio | last post by:
Hi everybody, I have the following scenario. Several computers with shared disk in a LAN. Each of these computer has a MySQL server that serves several databases. I have several clients that communicate with a Java process that I have in each computer to answer queries for a given database (it connects to the MySQL server). I also have a Java process that updates continuously the databases; this process executes only in the local...
4
10701
by: randy.p.ho | last post by:
Using JDBC, is there a way to call a stored procedure with multiple return values? Thanks.
0
1266
by: Matthew Lunnon | last post by:
Hi all, We're running multiple PostgresQL database installations, each on a separate port. This works fine and psql -p <port number> template1 works and connects to the correct instance of the database. We have a problem with JDBC connections though. We have an application thatuses Resin and connects to the database via a jdbc driver. When we use, for example:
1
9042
by: Praveen | last post by:
Hi, I have installed WebSphere Portal on AIX and connected to DB2 on a remote machine, Getting the followin errors when trying to get the values from database thru applications installed on Portal. Any Help Appreciated. Thanks in advance. Praveen Singh
2
4571
by: bevis | last post by:
I'm new to sql server and mysql but this seems like it should be a pretty straight forward jdbc connection. But I have spent almost 2 days just trying to get a jdbc connection. Please help if you can. I'm using Eclipse 3.2.2 and I have installed mysql-connector-java-5.0.6-bin.jar. I am trying to connect to SQL Enterprise Manager version 8.0 innstall on a Windows 2003 Server Enterprise Edition. I have been able to successfully test...
1
1561
by: Keita | last post by:
Hi everyone, I have a txt file where my sql insertions are (Insert into XXX values XXX; Insert into YYY values YYY and so on), but when inserting them in SQL view from Access, it sends an error saying that only one query can be made (characters found after the sql statement)...is there any way to make multiple sql inserts without making any macro just as if copy-pasting multiple queries as in SQL Server? any comment will be appreciated
3
22847
by: Anoop | last post by:
Is it true that there are no type 4 jdbc drivers to connect to a DB2 server v7.1? The DB2 server is hosted on ACF2 (OS/390). We would be connecting from windows and solaris boxes. If it is true, what are my options? Will I have to install db2 connect on every client machine that needs to connect to the DB2 servers? Is the DB2 Connect freely available or will we have to purchase it? I have also read on this group that we can install DB2...
9
2329
by: mak1084 | last post by:
Hi Friends, I'm facing a serius problem...in 2 days i have to submit my project and i'm stuck here..please help me... I'm getting some values from the database from one table say roll_no on one page....and then on that page i'm putting some values in front of that roll_no. Now I want to store the all data in a another table.. P.S. roll no. are not fixed and that is it can any thing from 1 - 60 here is my code.
0
9800
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11176
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10880
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10434
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7144
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3246
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.