MQSeries.net :: View topic - DesignQuestion-Inserting large no of records into oracle DB

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DesignQuestion-Inserting large no of records into oracle DB

DesignQuestion-Inserting large no of records into oracle DB

« View previous topic :: View next topic »

Author

Message

broker_new

Posted: Mon Jul 21, 2008 4:18 am Post subject: DesignQuestion-Inserting large no of records into oracle DB

Yatiri

Joined: 30 Nov 2006
Posts: 614
Location: Washington DC

The message flow gets an XML message which contains 5000 to 10000 job records.All the records has to be inserted into the oracle database.
Iam trying to understand a better way of doing it.

create one job record at a time and using a prepared statement insert into the database .In this case number of inserts will be equal to number of job records(i.e) hitting database from 5000-10000 times.

Is there a way we can pass whole output body to Oracle stored procedure and it take cares of parsing the XML message and inserting it into the database.
Have anybody come across such type of requirement.

friedl.otto

Posted: Mon Jul 21, 2008 6:24 am Post subject:

Centurion

Joined: 06 Jul 2007
Posts: 116

The prepared statement already helps, you could do a batch of prepared statements and only commit at the end ... that also speeds up the process.
_________________
Here's an idea - don't destroy semaphores unless you're certain of what you're doing!

-- Vitor

sridhsri

Posted: Mon Jul 21, 2008 7:20 am Post subject:

Master

Joined: 19 Jun 2008
Posts: 297

What node are you using - JCN or Compute ?

Have you considered what comprises a single unit of work ? Do you want Two-Phase commit enabled - If one of the inserts failed, would you want all of them rolledback ?

If you are using WMB 6.1.X then using JDBC APIs is another option (and XA is now supported with JDBC APIs).

The BAR File has an option of commit count and commit interval - you could play with these options to select something that suits you.

It is also possible for you to invoke a SQL Stored procedure. Remember - the format is no longer XML its a structured object. You will pass a REFERENCE object to the SQL Stored procedure. I am assuming you will have to loop through the object in the SQL to do multiple inserts.

broker_new

Posted: Mon Jul 21, 2008 9:32 am Post subject:

Yatiri

Joined: 30 Nov 2006
Posts: 614
Location: Washington DC

Iam using a compute node to do that.

So if i pass the reference of the output body to the stored procedure then the logic can be put in the SQL statement to loop through the reference and insert the records.Correct me if iam wrong.

If any of the insertion fails everything has to be rolled back and reprocess them again.

broker_new

Posted: Mon Jul 21, 2008 3:14 pm Post subject:

Yatiri

Joined: 30 Nov 2006
Posts: 614
Location: Washington DC

To make more meaningful...Actual Question is
The Company Record has two employee sub records and if i pass the whole XML message to Oracle database whether oracle has to the capability to parse the XML message and insert the records into the table.

<Company>
<Employee>
<Employee_no>1</Employee_no>
<phone>1112223333</phone>
<phone_type>R</phone_type>
</Employee>
<Employee>
<Employee_no>2</Employee_no>
<phone>2223334444</phone>
<phone_type>R</phone_type>
</Employee>
</Company>

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » DesignQuestion-Inserting large no of records into oracle DB

Jump to:

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Protected by Anti-Spam ACP