ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » TDS and Data loading dilemna with WBI and MQ

Post new topic  Reply to topic
 TDS and Data loading dilemna with WBI and MQ « View previous topic :: View next topic » 
Author Message
dhincali
PostPosted: Tue Dec 28, 2004 1:07 pm    Post subject: TDS and Data loading dilemna with WBI and MQ Reply with quote

Novice

Joined: 29 Dec 2003
Posts: 22

My company is new to middleware and we are starting on our requirements analysis for using MQ and WBI. We are actually doing a proof of concept to determine if they can fit into our environment. I have been assigned a few tasks and hope to receive some suggestions on how to resolve them. We will receive a TDS(tagged-delimited strcuture) file into the MQSeries queues that can be processed through WBIMB 5.0 into DB2 database(data loading).

The situation is that these TDS files are in batches and their frequency is one to many in a given day. However, the first requirement is that we need to process these TDS files in the same sequence WBI gets them. There is this possibility that some files maybe sent that are not in correct order. My company wants to load some data into the database in the same order it sends to MQSeries so that there are no referential integrity constraints in the target database.Is there a way that MQSeries will not let these files from getting processed in WBI if they are not in sequence.

Also, these TDS files should not be processed until all the previous files are completely processed in WBI and loaded into DB2 database. Parsing and message delivery and processing scenarios are not the issue of concern here. We have resolved that we can take care of them later.
Are there any support pacs that can address these issues?

Finally, these TDS files are sent in batches with correlation ids having unique sequence numbers. Is there a way to check in MQ itself to see if the sequence numbers are in order before WBIMB picks these messages and processes them?

I am trying to wrack up brains to resolve this issue and come up with a good proof of concept. Help would be greatly appreciated. I would be happy to provide more information as required.


Regards,
Dhincali
_________________
learning traveller
Back to top
View user's profile Send private message
dhincali
PostPosted: Wed Dec 29, 2004 7:50 am    Post subject: Reply with quote

Novice

Joined: 29 Dec 2003
Posts: 22

I was just wondering if there is a way in WBI to check for the batches of messages by correlation id and see if the message can be processed. The reason i was asking this question is that as per our requirement is considered, it looks like a typical batch processing job. But there is a small glitch where the batches that are sent by the vendor to MQ and later thru WBI are actually in order. If WBI processes these batches without checking for the sequential order then the data goes all the way into DB2 database causing referential integrity problems.

Can this solution be implemented in MQ itself or do we need a java program that checks if the batch is complete or not?

Any ideas would be appreciated?

Thanks,
dhincali
_________________
learning traveller
Back to top
View user's profile Send private message
jefflowrey
PostPosted: Wed Dec 29, 2004 7:58 am    Post subject: Reply with quote

Grand Poobah

Joined: 16 Oct 2002
Posts: 19981

I don't think you can "sort" by correlation ID.

You could use regular WMQ grouping, instead.

Or you could stage your messages into a secondary table, and then extract them and replay them into the real database when the last message in a group arrives.
_________________
I am *not* the model of the modern major general.
Back to top
View user's profile Send private message
kingsley
PostPosted: Wed Dec 29, 2004 8:28 am    Post subject: Reply with quote

Disciple

Joined: 30 Sep 2001
Posts: 175
Location: Hursley

Two things, i would like to discuss.

If ordering of messages is more important, first write a c program or Java program or what ever language you prefer. If the messages are coming or generated in your organisation (other than coming from External Vendors) , then use Message Sequence number where ever possible.

The program reads the messages and will place them into a second queue in the order exactly you prefer. If the messages are out of Order, then this program has to take care of all those things.

Secondly my suggestion is "Dont use WBI for database udpates as it will slow down the processing . If the Database or WBI fails during a Database Update, then you will spend more time than required to debug it" as experience taught us.

Use WBI for Data conversion and write to a output Queue. Database Updates using WBI is preferred only when a small number of messages are needed to update database.

Also, error tracking for Database Updates in WBI means, counting on Performance.

Write a second program, to update the Database , track & Report Errors
Back to top
View user's profile Send private message Visit poster's website
dhincali
PostPosted: Wed Dec 29, 2004 8:35 am    Post subject: Reply with quote

Novice

Joined: 29 Dec 2003
Posts: 22

Hi Jeff,

I have searched for some clear pointers on WMQ grouping. Are there any pointers to information on WMQ grouping(latest).

By the way, do you mean using a Database table to store a binary representation of the message bitstream, and when the last message in the group is retrieved, we can then retrieve the other message bitstreams from the database? (regarsing second solution u specified)

Somehow i feel WMQ grouping looks like a more viable solution. I will try to poke my nose more into it and see how i can use WBI with this solution.

Regards,
Dhincali
_________________
learning traveller
Back to top
View user's profile Send private message
dhincali
PostPosted: Wed Dec 29, 2004 8:49 am    Post subject: Reply with quote

Novice

Joined: 29 Dec 2003
Posts: 22

Hi Kingsley,

The messages are coming or getting generated in our organisation but in some cases the external vendors send us the data but they will always use a particular correlation id format to send the data. So this way we will always have the message sequence numbers. Also, the data from the sources is always according to a particulat format.

But my company wants to accomplish this ordering of messages without using any Java programs or C programs. Is it possible someway in WMQ itself with grouping? I have used Java programs before with respect to MQ batches, but we are just trying to get a different solution.

Secondly we get different Tagged delimited structured data and we call a lot of DB2 stored procedures, so we thought about using WBI for data loading into DB2. The data though is not huge and will not be more than 2 or 3 MB at a time. I however understand what you were suggesting as i had some similar experiences with WBI performances in data loading scenarios in batch processing environments.

Your suggestions will be very much appreciated.

Thanks and Regards,
Dhincali
_________________
learning traveller
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » TDS and Data loading dilemna with WBI and MQ
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
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.