| Author |
Message
|
| smdavies99 |
 Posted: Wed Dec 12, 2012 1:58 am Post subject: String data, right truncation on Select |
 |
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
This one has got me puzzled. I've seen plent of instances of this error on unsert or update operations but never on a select.
(Broker 7.0.0.4 windows Server 2008 R2)
I have a simple table in an SQLServer 2008 DB that is keyed by a UUID. The other column of interest is called message.
It contains some serialised XML.
My flow gets the UUID from an MQ Message and does a select on the table.
| Code: |
select MESSAGE from P_IB_PAS_MSGLOG where UUID = '94716e7e-0d02-4b9b-9fcd-2053e732dba3'
|
This select works in the SQLServer management Studio.
however in broker I get the following runtime error.
| Code: |
DECLARE msgBlob BLOB ASBITSTREAM (InputBody
ENCODING 546
CCSID 1208);
DECLARE msgChar CHARACTER CAST (msgBlob AS CHARACTER CCSID 1208);
2012-12-12 11:47:17.881540 4860 UserTrace BIP2231E: Error detected whilst processing a message in node 'AMM_PAS_COLLECTOR.SF_PAS_COLLECT_MQ_INPUT2.PAS.COLLECT.STAGE.2'.
The message broker detected an error whilst processing a message in node 'AMM_PAS_COLLECTOR.SF_PAS_COLLECT_MQ_INPUT2.PAS.COLLECT.STAGE.2'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2012-12-12 11:47:17.881600 4860 RecoverableException BIP2230E: Error detected whilst processing a message in node 'AMM_PAS_COLLECTOR.SF_PAS_COLLECT_MQ_INPUT2.SF_FLOW_Wrapper_Input.Initialise'.
The message broker detected an error whilst processing a message in node 'AMM_PAS_COLLECTOR.SF_PAS_COLLECT_MQ_INPUT2.SF_FLOW_Wrapper_Input.Initialise'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2012-12-12 11:47:17.881630 4860 RecoverableException BIP2488E: ('.SF_Flow_Wrapper_Input_Initialise.Main', '8.3') Error detected whilst executing the SQL statement ''PROPAGATE TO TERMINAL 'out' FINALIZE DEFAULT DELETE DEFAULT;''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2012-12-12 11:47:17.881650 4860 RecoverableException BIP2230E: Error detected whilst processing a message in node 'AMM_PAS_COLLECTOR.Build_Collection'.
The message broker detected an error whilst processing a message in node 'AMM_PAS_COLLECTOR.Build_Collection'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2012-12-12 11:47:17.881668 4860 RecoverableException BIP2488E: ('.AMM_PAS_COLLECTOR_Build_Collection.Main', '4.3') Error detected whilst executing the SQL statement ''Build_MergeMessage();''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2012-12-12 11:47:17.881688 4860 RecoverableException BIP2934E: Error detected whilst executing the function or procedure ''Build_MergeMessage''.
The message broker detected an error whilst executing the function or procedure ''Build_MergeMessage''. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2012-12-12 11:47:17.881706 4860 RecoverableException BIP2488E: ('.AMM_PAS_COLLECTOR_Build_Collection.Build_MergeMessage', '9.2') Error detected whilst executing the SQL statement ''SET Environment.DATA.Collection[ ] = DEFAULTPASSTHRU(cSQL);''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2012-12-12 11:47:17.881730 4860 DatabaseException BIP2321E: Database error: ODBC return code '1' using ODBC driver manager ''odbc32.dll''.
The message broker encountered an error when processing a database operation. The ODBC return code was '1'. See the following messages for information obtained from the database concerning this error.
Use the following messages to determine the cause of the error. Typical problems are an incorrect datasource or table names. Correct either the database or message broker configuration.
2012-12-12 11:47:17.881750 4860 DatabaseException BIP2322E: Database error: SQL State ''01004''; Native Error Code '0'; Error Text ''[Microsoft][SQL Server Native Client 10.0]String data, right truncation''.
The error has the following diagnostic information: SQL State ''01004'' SQL Native Error Code '0' SQL Error Text ''[Microsoft][SQL Server Native Client 10.0]String data, right truncation''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the reason identified in this message with the accompanying messages to determine the cause of the error.
|
Is the reason for this error the fact that the data is some serialised XML or is there some other reason? Can I actually read the serialised XML from the Table with broker?
Here are the statements that are used in another flow to serialise the XML
| Code: |
DECLARE msgBlob BLOB ASBITSTREAM (InputBody
ENCODING 546
CCSID 1208);
DECLARE msgChar CHARACTER CAST (msgBlob AS CHARACTER CCSID 1208); |
any thoughts/brickbats most welcome.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Dec 12, 2012 4:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Presumably you're using PASSTHRU?
Or you've modified the SELECT statement you posted to be valid and legal ESQL rather than valid and legal SQL?
Does an ODBC trace show you a better idea of what the parameters passed to the database look like? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 12, 2012 8:20 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I was using PASSTHRU but I (for some reason????) couldn't get it to work.
I changed the code to look like this
| Code: |
SET OutputLocalEnvironment.Variables.MESSAGE[] =SELECT P.MESSAGE AS "MESSAGE"
FROM Database.P_IB_PAS_MSGLOG AS P
where P.UUID = cUUID;
|
That now works fine.
As an aside,
I'm doing this to avoid using the Collector Node. I've had no end of issues with transactionality and locking of DB tables with it so for the time being we are giving up on it. These issues seem to appear after a 7-10 days and 300K+ collections so they are not going to be easy to find.
Yes a PMR should be the next step however making it repeatable is not easy.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Dec 12, 2012 8:52 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| smdavies99 wrote: |
That now works fine.
As an aside,
I'm doing this to avoid using the Collector Node. I've had no end of issues with transactionality and locking of DB tables with it so for the time being we are giving up on it. These issues seem to appear after a 7-10 days and 300K+ collections so they are not going to be easy to find.
Yes a PMR should be the next step however making it repeatable is not easy. |
So, you are storing messages in a database to avoid using the Collector node?
But even if "That now works fine, you occasionally have issues with the database?
You want to avoid using the Collector node because the collections are large and you get performance problems?
What about just storing the messages in a queue and collecting them with the MQGet node? That would give the best performance. If you implement it correctly and the queue does not grow very deep, of course. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 12, 2012 9:08 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| All inputs to a Collector node are in separate transactions from the output. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Dec 12, 2012 9:17 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| Yes, but they are all served on the same plate. You may get problems if you don't have a big enough plate in your cupboard, that's what I meant. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 12, 2012 9:22 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Esa wrote: |
| Yes, but they are all served on the same plate. You may get problems if you don't have a big enough plate in your cupboard, that's what I meant. |
Yes, you need to make sure that there is sufficient space in the various holders for the transaction that holds the completed message.
It may be easier to modify the database logs to hold the large transaction than the MQ logs to handle the large transaction. But if the output of the collection is being put to an MQ queue - you still need enough space in both logs.... |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Dec 12, 2012 9:33 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| mqjeff wrote: |
It may be easier to modify the database logs to hold the large transaction than the MQ logs to handle the large transaction. But if the output of the collection is being put to an MQ queue - you still need enough space in both logs.... |
Good point. smdavies99 has not mentioned the size of the collections or how many collections are being built simultaneously. Or how long it takes to build one collection, days, hours or minutes? These facts are really important for selecting the correct implementation. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 12, 2012 9:40 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
To elaborate a bit.
I was using the collector node to store up the first (up to ) 8 messages that match cetain criteria. If there aren't 8 messages in a collection then it times out and what is in the collection gets processed.
There can be 300+ collections in process at any one time.
The majority of collections actually timeout.
The flow with the collector in works for 7-10 days at a time and then everything locks up. I need to make sure that it works 365 days a year hence I'm putting things into a DB table. Because of the time period it works perfectly making the problem repeatable for a PMR is going to be very difficult.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 12, 2012 9:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Is your database undergoing maintenance at the time of the lockup?
Is the queue manager experiencing an otherwise large volume of persistent put/gets at the time of the lockup?
Is the server hosting the broker otherwise experiencing file system maintenance (perhaps the queue manager is hosted on shared storage that is undergoing replication)?
You have a systemic problem that will be merely relocated by avoiding the use of collector. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 12, 2012 10:04 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Is your database undergoing maintenance at the time of the lockup?
No IT happens at random times of the working day.
Is the queue manager experiencing an otherwise large volume of persistent put/gets at the time of the lockup?
No Well no more than usual. Overall, the system isn't heavily loaded.
Is the server hosting the broker otherwise experiencing file system maintenance (perhaps the queue manager is hosted on shared storage that is undergoing replication)?
no
There is probably some deadlocking in the tables associated with the flow but actually finding it is not going to be easy.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Dec 12, 2012 10:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| smdavies99 wrote: |
Is your database undergoing maintenance at the time of the lockup?
No IT happens at random times of the working day.
Is the queue manager experiencing an otherwise large volume of persistent put/gets at the time of the lockup?
No Well no more than usual. Overall, the system isn't heavily loaded.
Is the server hosting the broker otherwise experiencing file system maintenance (perhaps the queue manager is hosted on shared storage that is undergoing replication)?
no
There is probably some deadlocking in the tables associated with the flow but actually finding it is not going to be easy. |
I am pessimistically 75% certain that it is related to the volume of traffic being handled by the qmgr and the EG at the time of the lockup.
You should easily be able to ramp up something from IH03 or any of the other performance related spacs and execute a large volume of traffic against the queue manager while you run something through the collector.
It could simply be that the qmgr is reorganizing it's own log files at the time of the lockup, too. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Dec 12, 2012 10:40 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
I am pessimistically 75% certain that it is related to the volume of traffic being handled by the qmgr and the EG at the time of the lockup.
I wish I had your confidence  The flow is running in its own EG.
You should easily be able to ramp up something from IH03 or any of the other performance related spacs and execute a large volume of traffic against the queue manager while you run something through the collector.
I could if I had the time and were not 2000+ miles and 3 RDP hops away from the server.
It could simply be that the qmgr is reorganizing it's own log files at the time of the lockup, too.
There are very few uncomitted messages in this system. There are never more than three per flow at any point in time. However in the last lockup, the input messages to the collector flow was getting Queue Full errors yet the Qdepth for that Queue was 100K. Yet the visible Qdepth was less than 100 when I first noticed it. Stopping the flow didn't suddenly reveal thousands of newly comitted messages. Certainly some ideas for further investigation if possible
Thanks again for the ideas mqjeff.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Dec 12, 2012 12:07 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| smdavies99 wrote: |
| However in the last lockup, the input messages to the collector flow was getting Queue Full errors yet the Qdepth for that Queue was 100K. Yet the visible Qdepth was less than 100 when I first noticed it. Stopping the flow didn't suddenly reveal thousands of newly comitted messages. |
Are you collecting persistent or nonpersistent messages? Just wondering if you could get a queue full error when there is not enough memory for allocating the full size of in-memory buffer for nonpersistent messages? Probably not.
The problem could also be related to something that a certain operating system does periodically?
Have you run flow statistics or collected event monitoring data?
Do you run mqsireload regularly against the execution group?
You could try distributing the workload by refining the criteria that you use for grouping messages to collections and run a preprocessor flow that would spray the messages to several instances of the collector flow, each running in their own execution group and using their own collector queues and/or database tables. |
|
| Back to top |
|
|
|
|
