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 » Validating CSV File

Post new topic  Reply to topic Goto page Previous  1, 2
 Validating CSV File « View previous topic :: View next topic » 
Author Message
fjb_saper
PostPosted: Thu Feb 09, 2012 8:24 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

whydieanut wrote:

Coming to the exact requirements, my input CSV file is in the following format:
Code:

DOCNO_001,DR,10000,...,ABC
DOCNO_001,CR,5000,...,DEF
DOCNO_001,CR,5000,...,GHI
DOCNO_002,DR,2000,...,ABC
DOCNO_002,CR,2000,...,XYZ
DOCNO_003,DR,15000,...,LMN
DOCNO_003,CR,4000,...,XYZ
DOCNO_003,CR,2000,...,ABC
DOCNO_003,CR,9000,...,UVW

(The SAP IDoc has a similar structure with each line of CSV translating to a segment in the Data Record of the IDoc.)

This example consists of 3 separate transactions, identified by the value of the first field (DOCNO_001, DOCNO_002, DOCNO_003)
Each transaction consists of variable number of line items.

Requirement is to send a complete transaction to SAP; so if 1 line item of a transaction has an error, the entire transaction needs to be rejected (all line items for it)

In addition, since it's a batch, the sending application wants us to reject the whole file in case of error in even a single transaction; they don't want the file to be sent partially (i.e. sending all valid transactions), since reprocessing only failed transactions would be difficult for them.

Also, SAP wants us to send in the whole batch as a single IDoc.

To add to this, the overall design consists of 2 flows - One reading in the input CSV files and converting it into an IDoc and putting into a Queue, and another common flow reading from this Queue and sending the IDoc into the SAP Adapter (without any further processing).


Given all this, managing transactions is becoming tricky.


So we are really talking about multiple atomic transactions. The file represents x atomic transactions. Unless the SAP team developed a highly customized IDoc you will have multiple IDocs in your batch which ever way you look at it...

What I could understand is SAP requiring you to commit the batch as a whole... but send each IDoc on it's own...

Yes SAP deals with the uploads and does not need WMB for that, but then how are you validating the data and rejecting it?

So deal with each batch as infile, validation, outfile... and then have a compute node that renames / moves a successfully validated file... and finally have SAP upload the file. (better transactional control...)

Not quite what you wanted to hear, but IMHO a trade for better transactional control...

Have fun
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
whydieanut
PostPosted: Fri Feb 10, 2012 1:23 am    Post subject: Reply with quote

Disciple

Joined: 02 Apr 2010
Posts: 186

fjb_saper wrote:

So we are really talking about multiple atomic transactions. The file represents x atomic transactions. Unless the SAP team developed a highly customized IDoc you will have multiple IDocs in your batch which ever way you look at it...

What I could understand is SAP requiring you to commit the batch as a whole... but send each IDoc on it's own...


It's a custom IDoc with repeating segments having the same structure as a single line of the input CSV file. Hence a single IDoc can handle the entire contents of the input file.

SAP wants each transaction to be atomic (all lines items or none)
The sending application wants the entire File to be atomic (process whole file or nothing)

fjb_saper wrote:

Yes SAP deals with the uploads and does not need WMB for that, but then how are you validating the data and rejecting it?

So deal with each batch as infile, validation, outfile... and then have a compute node that renames / moves a successfully validated file... and finally have SAP upload the file. (better transactional control...)

Not quite what you wanted to hear, but IMHO a trade for better transactional control...

Have fun

That would mean doing away with the SAP Adapter altogether!
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Fri Feb 10, 2012 10:24 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY

whydieanut wrote:

fjb_saper wrote:

Yes SAP deals with the uploads and does not need WMB for that, but then how are you validating the data and rejecting it?

So deal with each batch as infile, validation, outfile... and then have a compute node that renames / moves a successfully validated file... and finally have SAP upload the file. (better transactional control...)

Not quite what you wanted to hear, but IMHO a trade for better transactional control...

Have fun

That would mean doing away with the SAP Adapter altogether!


Not quite. It just shows / demonstrates that the online process is suited to handle the atomic transaction (standard IDoc) and not suited to handle the batch the way your customer wants / needs it handled. What are you going to do once the custom IDoc exceeds 100 MB?

Have fun
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
mqsiuser
PostPosted: Fri Feb 10, 2012 11:34 am    Post subject: Reply with quote

Yatiri

Joined: 15 Apr 2008
Posts: 637
Location: Germany

whydieanut wrote:
I am expecting a peak load of about 10,000 lines of CSV data in the file...
The SAP guys want the entire contents of the file in a single IDoc.


@Vitor: He says he is expecting a peak load of 10.000 lines. In another thread he talks about 0.3 - 0.4 KB per line and so the maximum size of the file is 3-4 MB per (flat-)file. That is reasonably below the bounds of what broker can process (in a single flow-execution!).

I would process the whole message in one flow(-execution). You can then handle all your transaction issues within the flow.

SAP (especially SAP-PI) usually can connect to a queue manager. Connect (the SAP-System) to one that sits "near" the SAP-System, do not go across you (company-)network (for reliability-reasons). You do this with setting up a server connection channel on MQ and giving the SAP-Team the 3 MQ-Client-jars and a SVRCONN-String.

I guess with the SAP-Adapter you can instead (of making SAP read and write to queues on a queue manager) call BAPI/RFC ?!
_________________
Just use REFERENCEs
Back to top
View user's profile Send private message
Vitor
PostPosted: Fri Feb 10, 2012 11:50 am    Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

mqsiuser wrote:
@Vitor: He says he is expecting a peak load of 10.000 lines. In another thread he talks about 0.3 - 0.4 KB per line and so the maximum size of the file is 3-4 MB per (flat-)file. That is reasonably below the bounds of what broker can process (in a single flow-execution!).


I did say he wasn't asking too much of WMB. It's not a lot of data.

It's just a lot for an IDoc. I echo the comment of my most worthy associate on this manner.

whydieanut wrote:
What I meant was, just because something can be done in the Middleware Layer, doesn't mean that it has to be done here.
Can I just go ahead and tell the functional guys that this is not the responsibility of Middleware (considering that the only option feasible seems to be impacting performance)?


To answer your question (eventually) no it doesn't have to be done in the Middleware layer. It can be done in the Middleware layer, perhaps not in the way you've envisaged, but the final decision is a design one and site specific.

You certainly can tell the functional guys it's not a Middleware responsibility and there's justification for that view. They can equally say it's a delivery issue and nothing to do with them. It depends on many things, including where your site places responsibilties and how rigidly it's enforced.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
mqsiuser
PostPosted: Fri Feb 10, 2012 12:19 pm    Post subject: Reply with quote

Yatiri

Joined: 15 Apr 2008
Posts: 637
Location: Germany

Vitor wrote:
It's just a lot for an IDoc.


It would be a lot for a single order, but the IDOC for order(s) allows to contain a list of ORDERS. Then 3 to 4 MB (for e.g. the orders of a day) is reasonable.

@Vitor: Good that you come back to the original topic of the post, I like your view.

@whydieanut: Somewhere you waste yor resources! Use proper ESQL-Reference-handling-code !

Honestly... if your flow's performance significantly drops when you are processing about 1000 lines, then you likely walk into the tree to often: Do not use "SET OutputRoot.XMLNSC.IDOC.... = InputRoot.MRM.line[x]..." on your assign-statements! Use references (on the input- and ouput-tree: rIn, rOut, rInOrder, rOutOrder, rInLine, rOutLine, ...) with "move(/create) firstchild/next sibling" and while(-lastmove)-loops! Do not use CARDINALITY and do not access elements in a list with brackets [], like "list.element[7]".
_________________
Just use REFERENCEs
Back to top
View user's profile Send private message
whydieanut
PostPosted: Sun Feb 12, 2012 9:08 pm    Post subject: Reply with quote

Disciple

Joined: 02 Apr 2010
Posts: 186

Thanks a lot for all the input.

So in short, the only way to validate such a batch of CSV data is using ESQL code, only.

Will have another set of discussions with the parties involved and update this tread with the progress...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page Previous  1, 2 Page 2 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Validating CSV File
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.