MQSeries.net :: View topic - Weird DB2 connectivity/dead lock issue in prod

vasumath · Posted: Mon Mar 21, 2011 7:45 am Post subject:

We are facing one weird issue in Production environment( AIX MB and MQ 6.0)...Looks to me it is DB2 connectivity or dead lock issue

Background

We have few configuration tables. For each message, we hit DB2 database (intention is to cache the values and re-use the same, there is an issue in the coding, cache is not working in production and lower environments as well) and values been retrieved from DB2 and used in the flow.
Failure is not very frequent in the production and happening rarely i.e 15 days in once.
This operation is working completely fine in all the test environments but not in production... In production, we have used additional instance (up to 3) of the flow.
We tried to simulate it in test environment; we posted around 5000 messages in 7 mins, in test environments all the messages processed successfully.
We are not sure how to simulate it in test environment s and the parameters to be checked. Please help me to locate the issue

Code and Exception massage

Please find the code snippet

-- Set ConsumerName from MQMD.ApplIdentityData
SET ConsumerName = TRIM(InputRoot.MQMD.ApplIdentityData);

-- Populate cache if not already done (during flow startup)
IF CACHED_CFG_CONSUMER_P108 IS NULL THEN
READ_DB2 : BEGIN

ATOMIC -- beginning of atomic block. Processing is single threaded until the END; is reached

-- Query to fetch consumer details
SET CACHED_CFG_CONSUMER_P108.TABLE[] = SELECT M.FLOW_ID,
C1.CONSUMER_NAME,
C1.SYSTEM_NAME,
C1.CONSUMER_ROUTING_QMGR,
C1.CONSUMER_ROUTING_QUEUE,
C1.CONSUMER_INPUT_NAME1,
C1.CONSUMER_INPUT_NAME2,
C1.CONSUMER_INPUT_TYPE,
C1.PERF_WRITE_FLAG,
T.TRANSFORM_TYPE,
T.TRANSFORM_FILES,
C2.CME_AUDIT_REQD,
C2.CME_XSD_VALIDATION_REQD,
C2.CME_MSG_COUNT_VALIDATION_REQD,
C2.PATH_BATCH_MSG_COUNT,
C2.PATH_BATCH_REC_COUNT,
C2.PATH_BATCH_MSG_CALC
FROM Database.MS.CFG_CONSUMER AS C1,
Database.MS.CFG_TRANSFORM AS T,
Database.MS.CFG_MSG_FLOW AS M,
Database.MS.CFG_CME_CONTROL AS C2
WHERE C1.CONSUMER_NAME = T.TARGET_CONSUMER
AND
T.SOURCE_PRODUCER = M.SOURCE_NAME
AND
C1.CONSUMER_NAME = C2.SOURCE_NAME
AND
M.FLOW_NAME = Environment.Variables.FlowName;

-- Throw User Exception if the cache is not populated due to SQLException
IF (SQLCODE <> 0) THEN
THROW USER EXCEPTION VALUES
('MS',
'FAILED: SQLEXCEPTION IN SELECT FROM JOIN OF MS.CFG_MSG_FLOW, MS.CFG_CONSUMER, MS.CFG_TRANSFORM & CFG_CME_CONTROL',
'CMS:06001',
'Flow Name = ' || Environment.Variables.FlowName,
'SQLCODE = ' || CAST(SQLCODE AS CHAR), 'SQLERRORTEXT = ' || SQLERRORTEXT,
'SQLNATIVEERROR = ' || CAST(SQLNATIVEERROR AS CHAR), 'SQLSTATE = ' || CAST(SQLSTATE AS CHAR));

END IF;

-- Throw User Exception if no row returned
IF NOT EXISTS(CACHED_CFG_CONSUMER_P108.TABLE[]) THEN
THROW USER EXCEPTION VALUES
('MS',
'FAILED: NO ROWS RETURNED IN SELECT FROM JOIN OF MS.CFG_MSG_FLOW, MS.CFG_CONSUMER, MS.CFG_TRANSFORM & CFG_CME_CONTROL',
'CMS:06007',
'Flow Name = ' || Environment.Variables.FlowName);
END IF;

END READ_DB2;
-- end of the atomic block

END IF;

-- Fetch Details for the consumer from the cache in Environment.Variables
SET Environment.Variables.CONSUMER_RECORD[] = SELECT *
FROM CACHED_CFG_CONSUMER_P108.TABLE[] AS CT
WHERE CT.CONSUMER_NAME = ConsumerName;

-- Throw Exception when duplicate entries found for the consumer
IF CARDINALITY(Environment.Variables.CONSUMER_RECORD[]) > 1 THEN
THROW USER EXCEPTION VALUES
('MS',
'FAILED: MULTIPLE CONSUMER RECEORDS FOUND IN CACHE',
'CMS:06023',
'Flow Name = ' || Environment.Variables.FlowName);
END IF;

-- Throw User Exception when the consumer details not found
IF NOT EXISTS(Environment.Variables.CONSUMER_RECORD[]) THEN
THROW USER EXCEPTION VALUES
('MS',
'FAILED: CONSUMER RECEORD NOT FOUND IN CACHE',
'CMS:06010',
'Flow Name = ' || Environment.Variables.FlowName);
END IF;

Exception message

<?xml version="1.0" encoding="UTF-8"?>
<exceptionReport>
<queuedetails>
<queueName>SVC.CDF_PORD_LGCY_P171.CRQ</queueName>
</queuedetails>
<errorCode>CMS:06010</errorCode>
<exceptionText>
<RecoverableException>
<File>/build/S600_P/src/DataFlowEngine/ImbComputeNode.cpp</File>
<Line>464</Line>
<Function>ImbComputeNode::evaluate</Function>
<Type>ComIbmComputeNode</Type>
<Name>com/mns/ms/consumer/IP1_CFT_1_CDF_PORD_LGCY_P171#FCMComposite_1_2</Name>
<Label>com.mns.ms.consumer.IP1_CFT_1_CDF_PORD_LGCY_P171.ROUTING_CONFIGURATION</Label>
<Catalog>BIPv600</Catalog>
<Severity>3</Severity>
<Number>2230</Number>
<Text>Caught exception and rethrowing</Text>
<RecoverableException>
<File>/build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlStatementGroup.cpp</File>
<Line>602</Line>
<Function>SqlStatementGroup::execute</Function>
<Type>ComIbmComputeNode</Type>
<Name>com/mns/ms/consumer/IP1_CFT_1_CDF_PORD_LGCY_P171#FCMComposite_1_2</Name>
<Label>com.mns.ms.consumer.IP1_CFT_1_CDF_PORD_LGCY_P171.ROUTING_CONFIGURATION</Label>
<Catalog>BIPv600</Catalog>
<Severity>3</Severity>
<Number>2488</Number>
<Text>Error detected, rethrowing</Text>
<Insert>
<Type>5</Type>
<Text>com.mns.ms.consumer.IP1_CFT_1_CDF_PORD_LGCY_P171_ROUTING_CONFIGURATION.Main</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>96.4</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>THROW EXCEPTION VALUES( 'MS', 'FAILED: CONSUMER RECEORD NOT FOUND IN CACHE', 'CMS:06010', 'Flow Name = ' || Environment.Variables.FlowName);</Text>
</Insert>
<UserException>
<File>/build/S600_P/src/DataFlowEngine/ImbRdl/ImbRdlThrowExceptionStatements.cpp</File>
<Line>224</Line>
<Function>SqlThrowExceptionStatement::execute</Function>
<Type>ComIbmComputeNode</Type>
<Name>com/mns/ms/consumer/IP1_CFT_1_CDF_PORD_LGCY_P171#FCMComposite_1_2</Name>
<Label>com.mns.ms.consumer.IP1_CFT_1_CDF_PORD_LGCY_P171.ROUTING_CONFIGURATION</Label>
<Catalog>BIPv600</Catalog>
<Severity>1</Severity>
<Number>2951</Number>
<Text>User generated exception</Text>
<Insert>
<Type>5</Type>
<Text>MS</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>FAILED: CONSUMER RECEORD NOT FOUND IN CACHE</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>CMS:06010</Text>
</Insert>
<Insert>
<Type>5</Type>
<Text>Flow Name = IP1_CFT_1_CDF_PORD_LGCY_P171</Text>
</Insert>
</UserException>
</RecoverableException>
</RecoverableException>
</exceptionText>
</exceptionReport>