| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9.0.0.2: SQLServer Procedure Call, Execution Group Restar |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Mon Mar 09, 2015 7:14 am Post subject: IIB9.0.0.2: SQLServer Procedure Call, Execution Group Restar |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
There are times when the execution group restarts, automatically when a procedure call to MS SQLServer is being executed...
Is there a way to figure out what's wrong ? The trace file & the error log just contain that the fact that the EG restarted...
_________________
Regards |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Mar 09, 2015 7:25 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Did you take a service level trace?
Did you look for any FDC files?
Did you look at the memory usage of the EG before and after the restart?
Did you look at the odbc trace? |
|
| Back to top |
|
|
| akil |
| Posted: Mon Mar 09, 2015 10:36 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
I've taken the service trace, and I see a restart in that as well, not sure what else to look for.
If the procedure just does a 'return 0' , then the flow works.. only if it does some real work does the execution group restarts..So i suppose it's got to do something with the procedure's behavior.
The odbc trace returns the last line as follows :
| Code: |
[ODBC][7350][542136064][2015-03-09 23:52:51.632][SQLExecute.c][344]
Exit:[SQL_SUCCESS_WITH_INFO]
DIAG [01000] [IBM][ODBC SQL Server Legacy Driver][SQL Server]update [HO-CUSTOMERHANDOFF-STAGING] set [ERROR_DESCRIPTION]='COLUMN '+'UDF30 '+' NOT MAPPPED ', [IS-ERROR]=1^M
from [HO-CUSTOMERHANDOFF-STAGING] StgTab ^M
where (StgTab.udf30 is not null or StgTab.udf30 <> '') and [IS-ERROR]=0 and batchid=0
|
_________________
Regards |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Mar 09, 2015 10:51 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Time for a PMR perhaps?
_________________
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 |
|
|
| akil |
| Posted: Mon Mar 09, 2015 11:38 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
I suppose so, I wrote a simple flow HTTPInput --> DatabaseNode --> HTTPReply , and the restart of the EG is reproducible, basis the parameters passed...
The EG starts if I call procedure-A, but not if I call procedure-B.. the parameters are identical, I suppose I should try doing something with the body?
Any clues
_________________
Regards |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Mar 09, 2015 11:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| akil wrote: |
I suppose so, I wrote a simple flow HTTPInput --> DatabaseNode --> HTTPReply , and the restart of the EG is reproducible, basis the parameters passed...
The EG starts if I call procedure-A, but not if I call procedure-B.. the parameters are identical, I suppose I should try doing something with the body?
Any clues |
I would verify, outside of broker, that procedure-b doesn't return 10,000 1 mb rows, or 1 row with a 1GB clob...
But, yes, if you can't follow what's going on in the service trace, you *should* open a PMR. |
|
| Back to top |
|
|
| akil |
| Posted: Mon Mar 09, 2015 12:38 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Will raise a PMR,
The procedure's do not return any result set, the code is as follows,
The one marked with 'EG-ERROR' is the one that restarts.
This procedure takes around 50 seconds to return, (when executed outside the broker), not sure if that's a reason..
| Code: |
CREATE PROCEDURE [dbo].[INITIATE_BATCH_PROCESS] @procedureName VARCHAR(100), @batchid INT, @partnerid INT, @filename VARCHAR(100)
AS
SET NOCOUNT ON
BEGIN TRY
--This is an if-else block instead of a dynamic call to avoid sql-injection challenges
--Since the procedure names include unsupported characters,
IF @procedureName = 'HO-PROCESS-02' -- center creation
BEGIN
EXEC [HO-PROCESS-02] @batchid, @partnerid, @filename
END
ELSE IF @procedureName = 'HO-PROCESS-04' -- group creation
BEGIN
EXEC [HO-PROCESS-04] @batchid, @partnerid, @filename
END
ELSE IF @procedureName = 'HO-PROCESS-CU' -- customer creation, EG-ERROR
BEGIN
EXEC [HO-PROCESS-CU] @batchid, @partnerid, @filename
END
ELSE IF @procedureName = 'HO-PROCESS-A0' -- saving account creation
BEGIN
EXEC [HO-PROCESS-A0] @batchid, @partnerid, @filename
END
ELSE IF @procedureName = 'HO-PROCESS-01' -- loan creation
BEGIN
EXEC [HO-PROCESS-01] @batchid, @partnerid, @filename
END
ELSE
RAISERROR ('An invalid procedure name was passed: The procedure was %s, with parameters %d, %d, %s',15,1,@procedureName,@batchid,@partnerid,@filename)
END TRY
BEGIN CATCH
RAISERROR ('An error occurred in the procedure that was called: The procedure was %s, with parameters %d, %d, %s',15,1,@procedureName,@batchid,@partnerid,@filename)
END CATCH
|
_________________
Regards |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
