| Author |
Message
|
| flequi |
 Posted: Mon Nov 05, 2007 4:38 am Post subject: How to insert in DB with the same size of fields?? |
 |
|
Newbie
Joined: 05 Jul 2007
Posts: 8
Location: Spain
|
Hi, I have a problem with my WMB 6.0 aplication, I have a lot of datainsert nodes that inserts in a DB from the fields of an XML. The problem it's that sometimes these fields of the XML are larger than the fields of the database.
How method it's the better to controll the length of the database fields and adjust the values (with substrings or similar)?
It's posible to read the max.length of the database's fields?
I need a method that let me modify anytime the data model without to deploy all the flows with the datainserts.
Thanks a lot. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Mon Nov 05, 2007 4:46 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
you may have to use a JCN and check the metadata of the prepared statement....
Take as well into consideration CCSID changes. (multiple byte char to single byte char etc...)
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Nov 05, 2007 4:52 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
How can you be sure that the "extra" stuff in the XML data is stuff you can throw away? How do you know if you can throw away the stuff at the END of the field or at the FRONT of the field?
You need to define an interface to your flow (even if it's not officially a WSDL), and then enforce that in your code.
If your interface is defined such that these fields can be any length, then your flow needs to responsibly handle receiving fields that are longer than the database... or the database needs to be set up to handle any length fields!
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| rajmq |
| Posted: Mon Nov 05, 2007 5:29 am Post subject: |
|
|
Partisan
Joined: 29 Sep 2002
Posts: 331
Location: USA
|
I think increasing the database column length is an ideal approach,
As you said "inserts lots of message" if you add more validation in the esql then you will get performance issue.
We had a similar kind of requirement then we are decided to insert correct length xml values in the good tables and remaining all data to bad tables (bad tables are created with maxi. character value).
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0 |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Mon Nov 05, 2007 5:45 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
Unless you are sure from a business/data perspective that it is valid to trim fields then I recommend that you use a standard backout & exception reporting process, so that you have the original message to 'replay' after modifying the message to remove e.g. extra spaces or to truncate addresses appropriately or whatever is appropriate to your business needs. Alternatively if there is a valid requirement for increasing the column length then the messages can be 'replayed' after that is done.
Of course, getting the interface to meet the requirements in the first place would be best but that is not always possible. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Nov 05, 2007 6:35 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| rajmq wrote: |
| As you said "inserts lots of message" if you add more validation in the esql then you will get performance issue. |
It's only a performance "issue" if the performance does not match the requirements... and if the flow logic does not match the business requirements, then it doesn't matter how much it does or does not match the performance requirements...
That is, if the flow is required to perform additional validation, then it needs to perform that validation! And then it might need to be optimized to meet performance requirements.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| flequi |
| Posted: Mon Nov 05, 2007 7:21 am Post subject: Thanks to all |
|
|
Newbie
Joined: 05 Jul 2007
Posts: 8
Location: Spain
|
Thanks to all for your quick answers.
I think that my solution is nearest to the answers of jefflowrey and fjb_saper than to increase the DB field's length because I CAN'T (and my DB administrator isn't going to do)
I can´t use many JavaComputeNodes, because the people of IBM that went to teach us about MB, said that it's worst for the performance. We should to include a lot of JCNodes in our flows.
Is there any property to controll the datainsert mode, if the fields are truncated or not??
I don't understand how can I to solve the problem with WSDL, Can you helpme about this, jefflowrey??
Thanks again
Daniel Portilla |
|
| Back to top |
|
|
| rajmq |
| Posted: Mon Nov 05, 2007 7:55 am Post subject: |
|
|
Partisan
Joined: 29 Sep 2002
Posts: 331
Location: USA
|
| Quote: |
Jeff wrote:
That is, if the flow is required to perform additional validation, then it needs to perform that validation! And then it might need to be optimized to meet performance requirements.
|
Agreed..... but it is depends upon the company project schedule/cost etc... 
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0 |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Mon Nov 05, 2007 10:43 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
I am not very sure but can't a DB2 stored procedure be called from ESQL where the stored procedure returns the result of a describe table statement.
Thus you will be able to get the column types and lengths in your ESQL where you can compare the actual data length with the one returned and decide what you want to do.
This is assuming the underlying DB is DB2. This I hope can be extended to other DB's.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Nov 05, 2007 1:12 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I'm not saying that you can solve this using a WSDL, or that you should solve this using a WSDL.
I'm saying that you need to decide, based on business requirements, how big the field in the XML can be. And then you need to decide, based on business requirements, how to enforce that. And how to handle cases when the field is too big.
One way to do this is to tell the people that build the XML that the field can only be so long - this can be done by providing them an XML Schema that defines the XML data interface. If it happens to be an XML data interface to a webservice, THEN that interface can be described using a WSDL.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| flequi |
| Posted: Tue Nov 06, 2007 3:55 am Post subject: Thanks |
|
|
Newbie
Joined: 05 Jul 2007
Posts: 8
Location: Spain
|
Hi, thanks for your answers. Sorry I didn´t understand you before jefflowrey. I think so, but my business requirements don´t let me to specify the length of the xml's fields, then I have to trim/substring the value of this xml field.
I would to know if is there any automatic method into Broker like shalabh1976 says, that let me to verify the column length (of the DB) with the xml field's length.
I thought to use a mapping with substrings functions before the datainsert, but we don´t want to compile/deploy with every DB change, that I don't know until the application crash.
I need some transformation that read the data model of the DB and compare to the XML, and it trimms the field if it's necesary.
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Nov 06, 2007 4:06 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You need to find out what the business requirements say about truncating the fields.
I'm not aware of a good way to do what you want to do in Broker.
If your DB provides mechanisms for you to query table definitions, then you can write a bunch of ESQL code to do that, and populate some variables with column sizes, and then use those in your substrings.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Nov 06, 2007 4:26 am Post subject: Re: Thanks |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| flequi wrote: |
I need some transformation that read the data model of the DB and compare to the XML, and it trimms the field if it's necesary.
|
But you still need to agree with the business what part of the data you're trimming, or at least warn them that only the first x characters (where x is the width of the database column) is going to be stored.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
