| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Database error: SQL State '08007' |
« View previous topic :: View next topic » |
| Author |
Message
|
| narendra |
 Posted: Tue Feb 04, 2003 9:11 am Post subject: Database error: SQL State '08007' |
 |
|
Apprentice
Joined: 04 Jun 2002
Posts: 26
|
I have a message flow to query data from db2 database and breakup each row into XML then propagate.
This works fine for rows approximately less than 100000.
Any thing over that it gives the following DB2 error and rolls back the whole transaction.
( PWD.MODEL_1 ) Database error: SQL State '08007'; Native Error Code '-30081'; Error Text '[IBM][CLI Driver][DB2/6000] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "9.111.17.32". Communication function detecting the error: "recv". Protocol specific error code(s): "10054", "*", "0". SQLSTATE=08001
'.
The error has the following diagnostic information: SQL State '08007' SQL Native Error Code '-30081' SQL Error Text '[IBM][CLI Driver][DB2/6000] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "9.111.17.32". Communication function detecting the error: "recv". Protocol specific error code(s): "10054", "*", "0". SQLSTATE=08001
'
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.
Please let know, if any one has come across this problem |
|
| Back to top |
|
 |
| philip.baker |
| Posted: Tue Feb 04, 2003 9:48 am Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
narendra,
Could you please supply the version of MQSI/WMQI including which CSD being used, the DB/2 Database Version and fix pack loaded, and the DB/2 database driver you are using. Also, which operating system is your broker running on. (I will assume Windows.)
I suspect you may be having issues with the driver being used to access the database. There may be a solution with either changing the driver or perhaps changing some settings under the Advanced CLI/ODBC settings of the driver.
_________________
Regards,
Phil |
|
| Back to top |
|
|
| narendra |
| Posted: Tue Feb 04, 2003 10:53 am Post subject: |
|
|
Apprentice
Joined: 04 Jun 2002
Posts: 26
|
Hi Philip
Thanks for your reponse. Please find beolw the details.
Env: Win 2k, MQSeries 5.2.1 with CSD5, WMQI 2.1 CSD4, hardware 800 Mhz, 512 M Ram and plenty of hardisk
DB2 ver 7 with fixpack WR21254
we have a pub msg flow (on WIN2K server) which uses the passthru to pass the entire SQL statement and gets the
reuslt back from an AIX DB2 database server. The result set is then assigned to
InputDestinationList.XML.MessageData[]. Note the resultset is still one single huge msg which consists of many rows.
The PROPAGATE function is then used to break this msg into many single msgs. Each single msg corresponds to
one row.
The pub msg runs ok if the result set contains less than 130,000 msgs . Each msg is about 715 bytes
If the resultset contains more than 130,000 msgs, then the WMQI msg run for 12 hrs then fail with this error msg captured in the eventlog listed below.
.
Note that, either all 130,000 msgs is written out or none because the PROPAGATE function treats the
result set as one unit of work so it must use some sync point . My suspicion is that there is a
hard code value or some formula to calculate what the max val for sync point is!. I cannot find this in any doc yet.
logtype linear
logfilepages: 4055 (max)
logprimary file: 30 (max)
logsecondary file: 30 (max)
msg type : non persistent
set queue to recv max 640,000 msgs
set qmr to have 1,000,000 uncommited msg, 100,000 open handles
which means each logfile S00000xx.log is 16 Meg with a total of 60 (primary + secondary) logs to accomodate a total of 960 Meg max log file
This is the error in the eventlog
( PWD.MODEL_1 ) Database error: SQL State '08007'; Native Error Code '-30081'; Error Text '[IBM][CLI Driver][DB2/6000] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "9.111.17.32". Communication function detecting the error: "recv". Protocol specific error code(s): "10054", "*", "0". SQLSTATE=08001
'.
The error has the following diagnostic information: SQL State '08007' SQL Native Error Code '-30081' SQL Error Text '[IBM][CLI Driver][DB2/6000] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "9.111.17.32". Communication function detecting the error: "recv". Protocol specific error code(s): "10054", "*", "0". SQLSTATE=08001
Please note: Our AIX DB2 DBA has looked at the errrpt on the AIX machine and there is nothing
wrong at the time it fails.
we have run this test on 3 different Win 2k boxes and all gave the same results |
|
| Back to top |
|
|
| philip.baker |
| Posted: Tue Feb 04, 2003 12:06 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
narendra,
Thanks for the further info. However, I am a bit confused by some of the information you provided. The error indicates some problem with the DB/2 ODBC CLI connection when the message flow is trying to retrieve the large amount of data. (The sizes you supplied indicate an excess of 90 meg. of data coming back from the query.) But, you indicate that the result is received and then assigned to the InputDestinationList. If that is the case, then all the data should have been retrieved successfully. Are you setting the InputDestinationList directly to the PassThru SQL?
I may need to know more info about how your MQSI flow is setup. I do believe the problem is the ODBC connection not liking all the data it has to send back to MQSI, but it may be easier to change the logic of the message flow to retrieve the data in smaller chunks, if possible. (Maybe using a more restrictive WHERE clause on the query, or at least select the data into a temporary structure and then using a CARDINALITY DO loop to PROPOGATE the messages. This way you would at least get most of the data processed instead of failing for all messages because the query did not get the complete result set.) I will investigate any ODBC recommendations by I probably don't have the same DB/2 ODBC driver you are using.
Are you using the DB2 driver IBM DB2 ODBC Driver Version 7.01.00.65 on the Broker Server?
FYI - there is another DB/2 FixPack that may be loaded. If it is the same for AIX as Windows, the FixPack is WR21306 which is FP7 for DB/2V7.1. (Which I guess relates to FP4 for DB/2V7.2) As your WMQI Broker is not running on AIX, not sure if loading this FP will make any difference at all.
_________________
Regards,
Phil |
|
| Back to top |
|
|
| philip.baker |
| Posted: Tue Feb 04, 2003 1:32 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
narendra,
The only candidates I came up with for changing on the ODBC connection used to access the AIX DB/2 database was the OPTIMIZEFORNROWS and EARLYCLOSEOFF parameters. To change the settings, from the Data Sources(ODBC) selection, select the System DSN and click on the defined name of the data source used to access the AIX DB/2 database. and click the Configure tab. Key in the connection information, and select the Advanced tab. Select the Optimization tab and change the value for the Optimize for N rows parameter. You may want to set this to 100,000. Then go to the Compatibility tab and turn the Early Cursor Close parameter off.
Not sure if setting these parameters on the ODBC side will help at all. There may be network issues to further consider.
_________________
Regards,
Phil |
|
| Back to top |
|
|
| narendra |
| Posted: Tue Feb 04, 2003 1:44 pm Post subject: |
|
|
Apprentice
Joined: 04 Jun 2002
Posts: 26
|
philip
Response to your first para :
If i do not break the message everything goes fine. No problem.
Here is the code in the extract node.
DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.
--DECLARE SQLState1 CHARACTER;
--DECLARE SQLErrorText1 CHARACTER;
--DECLARE SQLCode1 INTEGER;
--DECLARE SQLNativeError1 INTEGER;
DECLARE GOTDATA BOOLEAN;
DECLARE CTR INTEGER;
IF (InputBody.Message.application.topic_name = 'vgcamptactic') THEN
SET InputDestinationList.XML.MessageData[] = PASSTHRU('
SELECT DISTINCT M.TACTIC_CODE AS CAMPAIGN_ID, M.LINKURL, C.BOBDB__OBJID AS BOBDB_OBJID, C.NAME AS CNAME, MT.ID, MT.NAME AS MTNAME
FROM VGNSYS.VGN_RULES R
JOIN VGNUSR.ECMA_EMAIL_LAUNCH L ON R.ID=L.RULEID
JOIN VGNSYS.VGN_CP C ON C.BOBDB__OBJID=R.CAMPAIGNID
JOIN VGNUSR.ECMA_EM_CAM_MET M ON R.ID=M.RULEID
JOIN VGNSYS.VGN_METRICS MT ON CHAR(MT.ID)=M.METRICID
WHERE L.CREATEDATE > ?', InputBody.Message.application.runtime);
ELSEIF (InputBody.Message.application.topic_name = 'vgclickthrough') THEN
SET InputDestinationList.XML.MessageData[] = PASSTHRU('
SELECT CAMPAIGNID AS CAMPAIGN_OBJID, R.SEGMENTID, M.RULEID, M.METRICID,
M.METRIC_COUNT, M.ANALYSISDATE
FROM VGNSYS.VGN_RULES R
JOIN VGNUSR.ECMA_REPORT_METRIC M ON R.ID=M.RULEID
WHERE TIMESTAMP(M.ANALYSISDATE , ''23.59.59'') > ?', InputBody.Message.application.runtime);
ELSEIF (InputBody.Message.application.topic_name ='vgemailsent') THEN
SET InputDestinationList.XML.MessageData[] = PASSTHRU('
SELECT CAMPAIGNID AS CAMPAIGN_OBJID, R.SEGMENTID, cast(L.CREATEDATE as char(26)) as LCREATEDATE, I.IMPRESSION_ID, I.USERID, I.EMAIL_ADDRESS, I.LAUNCHID, cast(I.CREATEDATE as char(26)) as ICREATEDATE, I.OPT_OUT, I.UNDELIVERABLE
FROM VGNSYS.VGN_RULES R
JOIN VGNUSR.ECMA_EMAIL_LAUNCH L ON R.ID=L.RULEID
JOIN VGNUSR.ECMA_EMAIL_CAMPAIGN_IMPRESSION I ON L.LAUNCHID= I.LAUNCHID
WHERE I.CREATEDATE >= ? AND I.CREATEDATE < ? ', InputBody.Message.application.runtime,InputBody.Message.application.endtime);
ELSEIF (InputBody.Message.application.topic_name = 'vgsegment') THEN
SET InputDestinationList.XML.MessageData[] = PASSTHRU('
SELECT DISTINCT S.SEGMENTID, S.NAME, DESCRIPTION, STATE, TYPE
FROM VGNSYS.VGN_SG S
JOIN VGNSYS.VGN_RULES R ON S.SEGMENTID=R.SEGMENTID
JOIN VGNUSR.ECMA_EMAIL_LAUNCH L ON R.ID=L.RULEID
WHERE L.CREATEDATE> ?', InputBody.Message.application.runtime);
END IF;
SET CTR = CARDINALITY( InputDestinationList.XML.MessageData[]);
IF (CTR = 0 ) THEN
SET GOTDATA = FALSE;
ELSE
SET I = 1;
WHILE I <= CTR
DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
EVAL('SET OutputRoot.XML.'|| InputBody.Message.application.topic_name || ' = ROW(CURRENT_TIMESTAMP as "dih_timestamp",InputBody.Message.application.topic_name as "dih_topic",InputBody.Message.application.system_name as "dih_source_data", ''CDT'' as "dih_timezone", InputBody.Message.application.topic_version as "dih_topic_version",InputDestinationList.XML.MessageData[I])');
PROPAGATE;
SET I = I+1;
END WHILE;
RETURN FALSE;
END IF;
IF (GOTDATA = FALSE) THEN
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.NoMessage."dih_topic_version" = InputBody.Message.application.topic_version;
SET OutputRoot.XML.NoMessage."dih_timestamp" = CURRENT_TIMESTAMP;
SET OutputRoot.XML.NoMessage."dih_topic"= 'dummy';
SET OutputRoot.XML.NoMessage."dih_source_data"= InputBody.Message.application.system_name;
-- SET OutputRoot.MQRFH2.psc.Topic='application/dummy';
END IF;
--SET SQLState1 = SQLSTATE;
--SET SQLErrorText1 = SQLERRORTEXT;
--SET SQLCode1 = SQLCODE ;
--SET SQLNativeError1 = SQLNATIVEERROR;
--THROW USER EXCEPTION MESSAGE 2950 VALUES ('The SQL State :
--',SQLState1,SQLCode1,SQLNativeError1,SQLNativeError1,SQLErrorText1);
Response to your Second para :
We tried getting smaller blocks of data by changing the where clause and we could get everything, again no problem.
Response to your third para :
DB2 ODBC driver version we have is 7.01.55
Please let me know, if you need more inof.
I reallt appreciate your help.
Regards
Narendra |
|
| 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
|
|
|
|
