| Author |
Message
|
| javaforvivek |
 Posted: Thu Jul 08, 2004 7:05 am Post subject: Writing processed message to file system.. |
 |
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Hi,
I have an XML Message, which I need to process, use some mapping rules to produce new values and then convert it to a CSV File, and then call a stored procedure which will upload the data from CSV file to SQLServer2000 database.
AS I must use this stored procedure to upload data, and because this stored procedure reads data from file and not from an MQMessage, I have to write the MQ Message first to the File System of the Windows2000.
I am planning of using a custom Java-Node to write the MQMessage to CSV File and then a Compute Node will call the stored procedure using passthru function.
My message flow will look like:
Series of compute nodes processing an XML Message, a Java Node converting this XML Message to CSV Message and write this CSV Message to a File, and then a Compute node calling the stored procedure.
Will this combination work in WBIMB5+CSD03 on Windows2000 Professional??
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
 |
| wooda |
| Posted: Thu Jul 08, 2004 7:55 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
Why don't you try it and find out ?
Does this need to be transactional ? Because as you are writing and reading the same file in one flow this may be a problem. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 08, 2004 8:14 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I wouldn't think that file operations would be affected by transactionality.
Which, in and of itself, could end up being a problem.
But to answer the very poorly asked original question...
There's no information you have provided that clearly indicates that this will NOT work.
So it should work. But it depends.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 08, 2004 11:41 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I'd say re-design this. Use WBIMB to load data from XML message into SQL Server db.
It will save you lot of work.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 08, 2004 11:46 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Kirani
I agree with that, but there may be a couple of reasons why Javaforvivek doesn't want to do this.
One, the broker may not be running on Windows. If it's not running on Windows, then there's no access to SQLServer from the broker.
Two, the logic inside the SP may be very complicated and it would take more work to reimplement in ESQL.
Either of these are fairly good reasons to choose the design in question.
But I still wouldn't necessarily do it this way. I'd write a standalone MQ application that took over after the message XML was transformed - This seems much more like an application operation than a "routing or transformation" operation. 
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 08, 2004 12:07 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| jefflowrey wrote: |
But I still wouldn't necessarily do it this way. I'd write a standalone MQ application that took over after the message XML was transformed - This seems much more like an application operation than a "routing or transformation" operation.
|
Well, it depends. Broker is also used to do data warehousing (uploading/updating db). So you are not doing anything unusual by putting updates in the DB. I'm not saying that use Broker as Application Server. Writing/introducing multiple layers mean high maintenance.
I'd let Vivek explain objective behind this design.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jul 08, 2004 1:00 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
More layers can indeed mean higher maintenance.
It can also mean "better abstraction" and "better modularity", as well as "increased fault isolation" (keeping one thing from breaking other things).
It Depends! 
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| javaforvivek |
| Posted: Mon Jul 12, 2004 7:40 am Post subject: Interesting Discussion |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Well.
I guess I need to explain it in details..
First of all, The design that I suggested is not the design that must be implemented.
I have an XML Message. The requirement is that, Theis XML Message should be converted in another XML Message, using the mapping rules. For e.g., In my XML Message the Data looks like this:
<Employee EmployeeId="10000">
<Name> Vivek</Name>
<LastName>Puranik</LastName>
<SalaryAccountNo>B-1234</SalaryAccountNo>
This data is originally in an XML File generated by (Let's Say) Application1.
There is a SQL Server Database which contains mappings for EmployeeId and SalaryAccountNo as Follows:
EmployeeIdMapping Table has data as follows:
App1EmployeeId | App2EmployeeId
10000 | A10000
20000 | A20000
Similarly,
SalaryAccountMapping Table has data as follows:
App1SalAcc | App2SalAcc2
B-1234 | B-A-1234
B-4567 | B-A-4567
There is a stored procedure in SQL Server database which calls an import DLL which accepts CSV file and uploads data to App2 SQL Server Database.
Now the Message Flow should function in such a way, that it will convert the XML data mentioned above, to a CSV data as
A-10000,Vivek,Puranik,B-A-1234
and write it to a CSV File.
Now as we don't want to lose the trnsaction handling ability of WBIMB, we are thinking of calling this import stored procedure through a Compute Node. But as the import DLL accepts only a CSV file, we have to convert the input XML message to CSV file. This CSV file is also used by the APP2 users to verify whether all the required data is imported or not..
That is why I had an idea of writing a Java Node, which will write this CSV data to a File and then a Compute Noe will call this stored procedure..
Hufff!! That's long story.. And,at the first glance, even I am not happy with this design of calling a DLL from WBIMB..!! But that's what the Users want... We can't avoid calling DLL!!!!
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Jul 12, 2004 4:02 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Vivek,
WBIMB will not coordiante your UOW for File-write operations (via plug-in node). Now that you have very limited options for re-desig, you can do it this way,
1. Message flow will generate transform incoming XML message in CWF format.
2. Another program
a) reads these CWF messages and writes them to a file.
b) Calls stored procedure to upload the data into SQL Server DB.
You will have to maintain the offset positions somewhere so that you can coordinate the File write operations within your program.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| javaforvivek |
| Posted: Tue Jul 13, 2004 12:25 am Post subject: Which program can I use? |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Hi Kirani,
Thanks for the advice.
I wonder whether a Java Program that reads the CWF Message and writes it to a file and then calls the stored procedure, can be converted to a Java Plug-in Node...
Can't I do that??
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Jul 13, 2004 12:31 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Yes, you can do that. But it'd be better to keep these two processing seperate.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| javaforvivek |
| Posted: Wed Jul 14, 2004 2:37 am Post subject: Converting and transforming XML message to CSV Message |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Kiran,
Continuing the discussion, Can I use Mapping Node to convert the input XML message to output CSV Message?
If yes, then do I have to create another message def file within the message set project or I have to create separate Message Set Project and Message Set and Def file?
Another way that comes into my mind is to use CREATE FIELD and REMOVE FIELD functions of ESQL, transform the same input XML message to an XML Message with different elements as :
<Employee EmployeeId="10000" SalaryAccount="A-1234">
<Name> Vivek</Name>
<LastName>Puranik</LastName>
to
<Employee>
<ID> A-10000</ID>
<SalaryAccount>A-B-1234</SalaryAccount>
---Some new elements giving more details about this employee--
</Employee>
and then convert this new XML Message to a CSV file.
And the news is that, I have to continue with the same bad design as the client thinks that DLL is the safest way to post the data to the database, bacause this DLL is given by the App2 Vendors...
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jul 15, 2004 10:44 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Vivek,
| javaforvivek wrote: |
Continuing the discussion, Can I use Mapping Node to convert the input XML message to output CSV Message?
|
Yes.
| javaforvivek wrote: |
If yes, then do I have to create another message def file within the message set project or I have to create separate Message Set Project and Message Set and Def file?
|
Either will work fine.
| javaforvivek wrote: |
...and then convert this new XML Message to a CSV file
|
Why do you want to convert XML to a different format and then to CSV format? You don'
t need to do XML->XML->CSV. You will not gain anything but slow down the processing.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| javaforvivek |
| Posted: Fri Jul 16, 2004 2:38 am Post subject: |
|
|
Master
Joined: 14 Jun 2002
Posts: 282
Location: Pune,India
|
Hi Kiran,
Long discussions with my Solution Architects, we have at last agreed upon staying for an XML output message rather than a CSV file. SO now the sceario is that I have to write a conversion and transformation of one XML Message to other XML Message via a message flow, and then a client application will get this message from OUTPUT queue on WebsphereMQ and do whatever it wants to do it.
Fortunately, the Application2 vendor has supplied with a DLL which handles XML data instead of CSV data... So my worries about XML-CSV confusion are over.
Now what I have to worry about is the XML to XML data mapping using some business rules maintained in database.
So now I have to ask is :
How to do this:
| Code: |
<Employee EmployeeId="10000" SalaryAccount="A-1234">
<Name> Vivek</Name>
<LastName>Puranik</LastName>
to
<Employee>
<ID> A-10000</ID>
<SalaryAccount>A-B-1234</SalaryAccount>
---Some new elements giving more details about this employee--
</Employee>
|
I am using MRM Domain.
_________________
Vivek
------------------------------------------------------
...when you have eliminated the impossible, whatever remains, however improbable, must be the truth. |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Jul 16, 2004 10:59 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Vivek,
Its good to hear that your application is becoming simple!
You can do this transformation using simple ESQL statements. If you have DTD (or XML schema) for these elements, then you can import them into MRM.
Sample Code
| Code: |
SET OutputRoot.XML.Employee.ID = InputRoot.XML.Employee.EmployeeId;
SET OutputRoot.XML.Employee.SalaryAccount = InputRoot.XML.Employee.SalaryAccount;
|
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
|
|
