0

In our Batch project we run a shell script,which in turn invokes a web service.Our requirement is to run a select query on a DB 2 Database and fetch say 1000 A_TYPE accounts,then we will have to use these accounts to get a list of other accounts(A_TYPE,B_TYPE collumns) by using A_TYPE accounts in a prepared statement from a ORACLE DB.

The problem is that for every 1000 A_TYPE accounts i have to prepare the statement 1000 times and execute it 1000 times to get the B_TYPE accounts.It is time taking and not so efficient.

So if someone could suggest me a way to pass a list or array of A_TYPE accounts and then get a list or hashmap of A_TYPE,B_TYPE accounts so that i can use them further,it would be very helpful. Also i have to update my Db2 DB with the B_TYPE accounts in the place of A_TYPE accounts. All my logic is written in web service operation(JAVA) itself.

UPDATE:

Yes i am directly accessing the DBs through JNDI names.In webservice handler logic i ll make a call to both the DB's.I donot know the DB2 version used since i access it using JNDI name,Oracle is 10 g.i have queries like 

SELECT A_TYPE account from ADB2 TABLE based on  conditions (?,?) FETCH 1000 records  only"` and `SELECT A_TYPE_Account,B_TYPE_Account from ORACLE_TABLE where A_TYPE_Account='?(A_TYPE)'

No i dont have to worry about transactions.i dont have idea of host variables and DB2 Dynamic query.

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
Sumateja Konge
  • 43
  • 1
  • 10
  • 1
    I sincerely doubt you _need_ to prepare the statement each time - usually prepared statements have placeholders for host variables (essentially), meaning you only have to prepare an "outline". If you just feed DB2 a dynamic query (no-prep), it'll actually do that automatically (pull out host variables and cache a single statement), although you'd be better off doing a single prepare. What kind of statements do you have, and do you have to worry about transactions? Are you directly accessing the dbs, or only through the webservice? What versions/platforms for the dbs (especially DB2)? – Clockwork-Muse Jan 28 '14 at 09:33
  • yes i am directly accessing the DBs through JNDI names.I donot know the DB2 version used since i access it using JNDI name. – Sumateja Konge Jan 29 '14 at 04:34

1 Answers1

0

Assuming you do this Oracle <--> DB2 stuff a lot, consider getting
- Oracle Transparent Gateway for DRDA http://www.oracle.com/technetwork/database/gateways/index.html
- IBM Infosphere Federation Server
http://www-03.ibm.com/software/products/en/ibminfofedeserv/

Note if you have DB2 Advanced Enterprise Server Edition (AESE), Infosphere Federation Server is included.

Both products would allow you to use a single join query sent to one DB that returns data from both DBs. The Oracle product is really nice in that it allows Oracle to see the DB2 database as another Oracle DB and for DB2 to see the Oracle database as another DB2 database. (Thanks to IBM publishing the specs for both the client and server side of the DRDA protocol DB2 uses. Too bad no other vendor is willing to do so, though they have no trouble taking advantage of the fact IBM did so.)

Neither product is what I would call cheap.
For cheap, you could take advantage of Oracle Database Gateway for ODBC
http://docs.oracle.com/cd/E16655_01/gateways.121/e17936/toc.htm

If you really want to continue down the path you've started to...I see two options
1) have your java app batch insert the 1000 DB2 records into a temporary table in Oracle then use it in a join for the final results.
bulk insert from Java into Oracle
2) pass the 1000 TYPEA records as a single string parameter into a stored procedure on Oracle that parses them out and makes use of them (assuming they aren't too big)

Community
  • 1
  • 1
Charles
  • 21,637
  • 1
  • 20
  • 44
  • Charles thanks for your answer...but i dont have the option of using DB2 AESE.And the i dont have the access to create table in Oracle Database.Creating a stored procedure is also not possible. – Sumateja Konge Jan 29 '14 at 04:31