473,289 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

SQL Stored Procedures X Java Stored Procedures in DB2 8.2

Hi All,

We are starting a large data warehousing project using DB2 8.2 on AIX.
There is a direction to move any new internal development to Java and a
question was raised: Would it be a good idea to have all stored
procedures that we might need (ETL, additional transformers, etc) for
this project and any other project written in Java as opposed to SQL/P?
Does anyone have any number or experience in terms of performance
differences?

Regards,

Rafael Faria

Nov 12 '05 #1
2 1881
Rafael Faria wrote:
Hi All,

We are starting a large data warehousing project using DB2 8.2 on AIX.
There is a direction to move any new internal development to Java and a
question was raised: Would it be a good idea to have all stored
procedures that we might need (ETL, additional transformers, etc) for
this project and any other project written in Java as opposed to SQL/P?
Does anyone have any number or experience in terms of performance
differences?

It depends.
I would _not_ use Java in any environment with high throughput.
That is Java UDF in queries with many rows or Java Procs for high
through in e.g. batch processing.
Using Java to drive your complex queries (Read: it's not the Java that
takes time it's the SQL) is no problem.
SQL Procedures are faster than Java today and they will only get faster
from release to release.
So if your SQL in the procs takes minutes to run, the choice of language
for logic is irrelevant.
If you drive a lot of logic in your ETL and the SQL is trivial the
choice of language is everything.

Just my 2 cents canadian
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2
I tend to summarize procedures this way: use the strengths of the language.

LANGUAGE SQL should always be the first choice; if you can use the
mechanism inside that language to do what you want to do, then it will
not only give you the best performance, but it is also the easiest to
maintain and port from system to system (no external files to worry
about, just the DDL). It's a fairly straightforward language to learn,
as it's not that much more complex than the SQL statements it will be
executing.

However, there are operations that that language can't do, such as
accessing files, opening TCPIP ports, etc. In that case, it's really a
matter of choice what EXTERNAL (ie, accessing an external library)
language you use. JAVA would probably be fine in this case, as it's
object structure tends to encapsulate complex functionality quite well,
and it's probably the second most portable language behind SQL, as you
can use the "SQLJ"-schema'ed functions (I won't describe them here --
they're documented quite well online) to manage your JAR files and
ensure changes are picked up on the server. However, most SQL inside of
JAVA procedures is dynamically executed, which can hinder performance,
and can require some configuration (picking a JDK, ensuring the files
required can be access, setting the JAVA_HEAP_SZ) in order to get the
best results.

Really, though, if you can't use LANGUAGE SQL I'd use whatever language
you're comfortable coding in.

Rafael Faria wrote:
Serge Rielau wrote:
Rafael Faria wrote:
Hi All,

We are starting a large data warehousing project using DB2 8.2 on
AIX.
There is a direction to move any new internal development to Java
and a
question was raised: Would it be a good idea to have all stored
procedures that we might need (ETL, additional transformers, etc)
for
this project and any other project written in Java as opposed to
SQL/P?
Does anyone have any number or experience in terms of performance
differences?


It depends.
I would _not_ use Java in any environment with high throughput.
That is Java UDF in queries with many rows or Java Procs for high
through in e.g. batch processing.
Using Java to drive your complex queries (Read: it's not the Java


that
takes time it's the SQL) is no problem.
SQL Procedures are faster than Java today and they will only get


faster
from release to release.
So if your SQL in the procs takes minutes to run, the choice of


language
for logic is irrelevant.
If you drive a lot of logic in your ETL and the SQL is trivial the
choice of language is everything.

Just my 2 cents canadian
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Hi Serge / All,

Thanks very much for your comments. They seem to support what I
expected.
The old DW is running on Oracle on an old Unix server. The new DW is
going to be running on brand new IBM servers and on DB2 8.2 and we will
have additional feeds from new operational systems that the old DW
didn't have to handle. Therefore it's a bit hard to draw conclusions
based on the existing numbers.
At this stage, we don't anticipate any extremely sophisticated logic or
transformations on the new ETL processes. There will be a lot of data
to be moved from operational systems to the warehouse on a daily basis
though, so performance will be a very important factor.
For these reasons I'm inclined to vote against java stored procedures
at this point.
We might put together a prototype to compare both solutions just to
substantiate with numbers our decision.
Given the scenario above would you implement the Java SP using JDBC or
SQLJ?

Thanks again for any feedback.

Regards,

Rafael Faria

Nov 12 '05 #3

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

Similar topics

1
by: Suresh Tri | last post by:
Hi all, I am in search of any Enterprise level Opensource Project that uses Java Stored Procedures supported by Oracle. I could not find any by googling. Can any one plese point me to any such...
11
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures...
7
by: Alex | last post by:
Hi all, I am trying to install a java stored procedure via the windows development centre. The linux box is running 8.1 FP4 as is the windoze platform. If I am on the linux box i can install...
3
by: Rhino | last post by:
I've spent the last couple of hours trying to figure out how to debug a Java stored procedure and am just going in circles. The last straw came when I got "Cannot open input stream for default"...
4
by: Rhino | last post by:
Is it possible for a Java Stored Procedure in DB2 V7.2 (Windows) to pass a Throwable back to the calling program as an OUT parameter? If yes, what datatype should I use when registering the...
4
by: Mike L. Bell | last post by:
As the DBA for a development project a couple of years ago, I was in charge of migrating/promoting stored procedures from the development environment to the QA and production environments once they...
1
by: Alex | last post by:
Hi all, Just been dabbling with java stored procedures and I'm having problems replacing System: db2 8.1.4 on RH 7.1 linux system 1). Look at java jdbc samples as supplied with db2 and run...
2
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered...
2
by: Michael | last post by:
Running DB2 v7 UDB ("DB2 v7.1.0.93", "n031208" and "WR21333") on Windows XP, I am unable to find out why the "Build for Debug" option within Stored Procedure Builder is not enabled on Java stored...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.