| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Not Commiting Inserts Inmediately - In Compute Node |
« View previous topic :: View next topic » |
| Author |
Message
|
| dprogwmb |
 Posted: Thu Aug 18, 2011 5:46 pm Post subject: Not Commiting Inserts Inmediately - In Compute Node |
 |
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
Hi all
I'm using WMB 7.0.2.
I have one compute node that in the ESQL code, first inserts one register in Table "A", and after inserts one register in Table "B"... but Table "B" has one field that is a Foreign Key to other field in Table A... so after inserting table A, when the node try to insert in table B, It throws an error: "FK violated"... (note: after the error I make a select to see Table A, and the register has been corrected inserted, but obviously In table B hasn't been inserted, and is very logic too)
Note: My Compute node is setted in Transaction = "Automatic"
So I'm thinking if is there any way to configure the Message Broker (not By ESQL code) to commit inmediately after executing the Insert to table A?? In that case I won't have problems to insert in Table B, and I won´t have the FK error...
What should I do? Is there any command or configuration that I shoud set to the Broker to solve that? REGARDS EVERY BODY |
|
| Back to top |
|
 |
| dprogwmb |
| Posted: Thu Aug 18, 2011 7:32 pm Post subject: |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
Oh...  I have been testing again, and I put a PASSTHRU('commit') after inserting in esql, and I'm having the same problem... "FK Violated" .... what can be happening? Do I need to set anything special for the broker to commit inmediate after each insert?
Any ideas? I will give 1000  to the person that can help meeee.... !!!!! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Aug 18, 2011 7:54 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| dprogwmb wrote: |
Oh... I have been testing again, and I put a PASSTHRU('commit') after inserting in esql, and I'm having the same problem... "FK Violated" .... what can be happening? Do I need to set anything special for the broker to commit inmediate after each insert?
Any ideas? I will give 1000 to the person that can help meeee.... !!!!! |
| Quote: |
| Note: My Compute node is setted in Transaction = "Automatic" |
You might also want to consider putting all your SQL statements into a procedure...
And that qualifies for the award! 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| dprogwmb |
| Posted: Thu Aug 18, 2011 9:45 pm Post subject: I have to mix ESQL and SQL code... :( |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
mmm...
This SQL Statements depends on certain ESQL statments, so they can't be executed in a procedure (stored procedure) .... I have to mix ESQL y SQL code in the same compute.... ....
Any other idea... ?
(In other broker server, It works fine, with the same code... but I don't have access to that server, so I can't see that configurations... I have seen how other people deploy the same code to the other server and It works fine)... That's why I was asking what do I need to set, at broker Level or configuration level in my broker server (for example: execute some mqsiXXX...or configure some especial files?,etc... I dont know...) - WITHOUT TOUCHING CODE (esql or SQL)
The awards are still waiting..  REGARDS EVERYBODY!!! |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Aug 18, 2011 11:07 pm Post subject: Re: I have to mix ESQL and SQL code... :( |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| dprogwmb wrote: |
mmm...
This SQL Statements depends on certain ESQL statments, so they can't be executed in a procedure (stored procedure) .... I have to mix ESQL y SQL code in the same compute.... |
would you care to elaborate on this statement?
_________________
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 |
|
|
| dprogwmb |
| Posted: Fri Aug 19, 2011 5:35 am Post subject: CODE |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
| Code: |
SET QUERY = 'INSERT INTO PROCESS_INSTANCE( '
||'PROCESS_INSTANCE_ID, '
||'PROCESS_NAME, '
||'PROCESS_INITIATOR_SYSTEM, '
||'PROCESS_INITIAL_DATETIME, '
||'PROCESS_FINAL_DATETIME, '
||'PROCESS_STATUS, '
||'PROCESS_TYPE_ID, '
||'PROCESS_PARAM_VAL_01, '
||'PROCESS_PARAM_VAL_02, '
||'PROCESS_PARAM_VAL_03) '
||'VALUES(?, (SELECT MIN(PROCESS_TYPE_NAME) FROM PROCESS_TYPE WHERE PROCESS_TYPE_ID = ?), '
||'?, ?, ?, ?, ?, ?, ?, ?)';
IF LENGTH(inRef.ProcessExecution.InitialTimeStamp) = 26 THEN
SET InitialTimeStamp = CAST(inRef.ProcessExecution.InitialTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET InitialTimeStamp = CAST(inRef.ProcessExecution.InitialTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
IF inRef.ProcessExecution.FinalTimeStamp <> '' Then
IF LENGTH(inRef.ProcessExecution.FinalTimeStamp) = 26 THEN
SET FinalTimeStamp = CAST(inRef.ProcessExecution.FinalTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET FinalTimeStamp = CAST(inRef.ProcessExecution.FinalTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
END IF;
SET OutputLocalEnvironment.ProcessExecution[] = PASSTHRU('SELECT PROCESS_TYPE_NAME FROM PROCESS_TYPE WHERE PROCESS_TYPE_ID = ?', CAST(inRef.ProcessExecution.ProcessType AS INTEGER));
INSERT INTO Database.PROCESS_INSTANCE
(PROCESS_INSTANCE_ID,
PROCESS_NAME,
PROCESS_INITIATOR_SYSTEM,
PROCESS_INITIAL_DATETIME,
PROCESS_FINAL_DATETIME,
PROCESS_STATUS,
PROCESS_TYPE_ID,
PROCESS_PARAM_VAL_01,
PROCESS_PARAM_VAL_02,
PROCESS_PARAM_VAL_03)
VALUES
(CAST(inRef.ProcessExecution.InstanceId AS INTEGER),
OutputLocalEnvironment.ProcessExecution.PROCESS_TYPE_NAME,
inRef.ProcessExecution.OriginSystem,
InitialTimeStamp,
FinalTimeStamp,
LEFT(inRef.ProcessExecution.Status,10),
CAST(inRef.ProcessExecution.ProcessType AS INTEGER),
LEFT(inRef.ProcessExecution.ProcessParam01,20),
LEFT(inRef.ProcessExecution.ProcessParam02,20),
LEFT(inRef.ProcessExecution.ProcessParam03,100));
-- Error handling
IF SQLCODE <> 0 THEN
SET Environment.Variables.UserData.ErrCode = '2962';
SET Environment.Variables.UserData.SQLSTATE = cast(SQLSTATE as char);
SET Environment.Variables.UserData.SQLCODE = cast(SQLCODE as char);
SET Environment.variables.UserData.SQLERRORTEXT = SQLERRORTEXT;
SET Environment.Variables.UserData.SQLNATIVEERROR = cast(SQLNATIVEERROR as char);
THROW USER EXCEPTION MESSAGE 2962 VALUES
( Environment.Variables.UserData.SQLSTATE,
Environment.Variables.UserData.SQLCODE,
Environment.Variables.UserData.SQLERRORTEXT,
Environment.Variables.UserData.SQLNATIVEERROR,
'FLOWCONTROL table access failure');
END IF;
SET QUERY = 'INSERT INTO PROCESS_STEP( '
||'PROCESS_STEP_ID, '
||'PROCESS_INSTANCE_ID, '
||'PROCESS_STEP_NAME, '
||'PROCESS_STEP_MESSAGE_ID, '
||'PROCESS_STEP_CORRELATION_ID, '
||'PROCESS_STEP_INITIAL_DATETIME, '
||'PROCESS_STEP_FINAL_DATETIME, '
||'PROCESS_STEP_REQUEST_MESSAGE, '
||'PROCESS_STEP_RESPONSE_MESSAGE, '
||'PROCESS_STEP_STATUS) '
||'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
SET QUERY2 = 'INSERT INTO STEP_EXCEPTION( '
||'STEP_EXCEPTION_ID, '
||'STEP_EXCEPTION_ERROR_NUMBER, '
||'STEP_EXCEPTION_SEVERITY_LEVEL, '
||'STEP_EXCEPTION_EXCEPTION_TYPE, '
||'STEP_EXCEPTION_ERROR_CAUSE, '
||'STEP_EXCEPTION_ERROR_MESSAGE, '
||'STEP_EXCEPTION_ERROR_LOCATION, '
||'STEP_EXCEPTION_FUNCTION_NAME, '
||'STEP_EXCEPTION_COMMAND_LINE, '
||'PROCESS_STEP_ID) '
||'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
--MOVE activRef FIRSTCHILD;
WHILE LASTMOVE(activRef) DO
SET OutputLocalEnvironment.actvSeq[] = PASSTHRU('select PROCESS_STEP_ID_SEQ.NEXTVAL from DUAL');
SET actvSeq = OutputLocalEnvironment.actvSeq.NEXTVAL;
IF LENGTH(activRef.InitialTimeStamp) = 26 THEN
SET InitialTimeStamp = CAST(activRef.InitialTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET InitialTimeStamp = CAST(activRef.InitialTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
IF activRef.FinalTimeStamp <> '' Then
IF LENGTH(activRef.FinalTimeStamp) = 26 THEN
SET FinalTimeStamp = CAST(activRef.FinalTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET FinalTimeStamp = CAST(activRef.FinalTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
END IF;
PASSTHRU(QUERY,
actvSeq,
CAST(inRef.ProcessExecution.InstanceId AS INTEGER),
LEFT(activRef.StepName,50),
LEFT(activRef.MessageId,50),
LEFT(activRef.CorrelationId,50),
InitialTimeStamp,
FinalTimeStamp,
activRef.InputMessage,
activRef.OutputMessage,
LEFT(activRef.Status,20));
|
Very Strange the no autocommit problem, don't you think so? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Aug 19, 2011 5:50 am Post subject: Re: CODE |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| dprogwmb wrote: |
| Very Strange the no autocommit problem, don't you think so? |
Not so strange if it works the way it's documented to work.
Hey, what happens if you set the right Compute Mode of your Compute node, and then go to another Compute node? HMMMM.
Hey, what happens if you use PASSTHRU to call the database's own commit? HMMMM. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Aug 19, 2011 8:12 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
IMHO, any competent Oracle DB Developer could put that ESQL into a Stored Procedure AND Handle the returns when you get an error thus allowing you to throw an exception.
Certainly if I had to do those operations on a DB I'd be writing in Oracle SQL rather than Broker ESQL.
Actually I'd have to as we have a standard that says that all Insert/Update operations MUST be done via a stored procedure.
_________________
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 |
|
|
| dprogwmb |
| Posted: Fri Aug 19, 2011 9:10 am Post subject: Re: CODE |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
| mqjeff wrote: |
| dprogwmb wrote: |
| Very Strange the no autocommit problem, don't you think so? |
Not so strange if it works the way it's documented to work.
Hey, what happens if you set the right Compute Mode of your Compute node, and then go to another Compute node? HMMMM.
Hey, what happens if you use PASSTHRU to call the database's own commit? HMMMM. |
I have tested with Transaction setted to Automatic and to Commit of the compute node... and I have used the PASSTHRU('commit'); ... and It stills not commiting... after the compute, there 's other node ... a FilterNode that only tests if one simple condition exists (without accesing DB)....
Any ideas or commands of Broker or special codification of ESQL? This is the new code:
| Code: |
INSERT INTO Database.PROCESS_INSTANCE
(PROCESS_INSTANCE_ID,
PROCESS_NAME,
PROCESS_INITIATOR_SYSTEM,
PROCESS_INITIAL_DATETIME,
PROCESS_FINAL_DATETIME,
PROCESS_STATUS,
PROCESS_TYPE_ID,
PROCESS_PARAM_VAL_01,
PROCESS_PARAM_VAL_02,
PROCESS_PARAM_VAL_03)
VALUES
(CAST(inRef.ProcessExecution.InstanceId AS INTEGER),
OutputLocalEnvironment.ProcessExecution.PROCESS_TYPE_NAME,
inRef.ProcessExecution.OriginSystem,
InitialTimeStamp,
FinalTimeStamp,
LEFT(inRef.ProcessExecution.Status,10),
CAST(inRef.ProcessExecution.ProcessType AS INTEGER),
LEFT(inRef.ProcessExecution.ProcessParam01,20),
LEFT(inRef.ProcessExecution.ProcessParam02,20),
LEFT(inRef.ProcessExecution.ProcessParam03,100));
PASSTHRU ('Commit') ;
-- Error handling
IF SQLCODE <> 0 THEN
SET Environment.Variables.UserData.ErrCode = '2962';
SET Environment.Variables.UserData.SQLSTATE = cast(SQLSTATE as char);
SET Environment.Variables.UserData.SQLCODE = cast(SQLCODE as char);
SET Environment.variables.UserData.SQLERRORTEXT = SQLERRORTEXT;
SET Environment.Variables.UserData.SQLNATIVEERROR = cast(SQLNATIVEERROR as char);
THROW USER EXCEPTION MESSAGE 2962 VALUES
( Environment.Variables.UserData.SQLSTATE,
Environment.Variables.UserData.SQLCODE,
Environment.Variables.UserData.SQLERRORTEXT,
Environment.Variables.UserData.SQLNATIVEERROR,
'FLOWCONTROL table access failure');
END IF;
SET QUERY = 'INSERT INTO PROCESS_STEP( '
||'PROCESS_STEP_ID, '
||'PROCESS_INSTANCE_ID, '
||'PROCESS_STEP_NAME, '
||'PROCESS_STEP_MESSAGE_ID, '
||'PROCESS_STEP_CORRELATION_ID, '
||'PROCESS_STEP_INITIAL_DATETIME, '
||'PROCESS_STEP_FINAL_DATETIME, '
||'PROCESS_STEP_REQUEST_MESSAGE, '
||'PROCESS_STEP_RESPONSE_MESSAGE, '
||'PROCESS_STEP_STATUS) '
||'VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
SET QUERY2 = 'INSERT INTO STEP_EXCEPTION( '
||'STEP_EXCEPTION_ID, '
||'STEP_EXCEPTION_ERROR_NUMBER, '
||'STEP_EXCEPTION_SEVERITY_LEVEL, '
||'STEP_EXCEPTION_EXCEPTION_TYPE, '
||'STEP_EXCEPTION_ERROR_CAUSE, '
||'STEP_EXCEPTION_ERROR_MESSAGE, '
||'STEP_EXCEPTION_ERROR_LOCATION, '
||'STEP_EXCEPTION_FUNCTION_NAME, '
||'STEP_EXCEPTION_COMMAND_LINE, '
||'PROCESS_STEP_ID) '
||'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
--MOVE activRef FIRSTCHILD;
WHILE LASTMOVE(activRef) DO
SET OutputLocalEnvironment.actvSeq[] = PASSTHRU('select PROCESS_STEP_ID_SEQ.NEXTVAL from DUAL');
SET actvSeq = OutputLocalEnvironment.actvSeq.NEXTVAL;
IF LENGTH(activRef.InitialTimeStamp) = 26 THEN
SET InitialTimeStamp = CAST(activRef.InitialTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET InitialTimeStamp = CAST(activRef.InitialTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
IF activRef.FinalTimeStamp <> '' Then
IF LENGTH(activRef.FinalTimeStamp) = 26 THEN
SET FinalTimeStamp = CAST(activRef.FinalTimeStamp AS TIMESTAMP FORMAT pattern1);
ELSE
SET FinalTimeStamp = CAST(activRef.FinalTimeStamp AS TIMESTAMP FORMAT pattern2);
END IF;
END IF;
PASSTHRU(QUERY,
actvSeq,
CAST(inRef.ProcessExecution.InstanceId AS INTEGER),
LEFT(activRef.StepName,50),
LEFT(activRef.MessageId,50),
LEFT(activRef.CorrelationId,50),
InitialTimeStamp,
FinalTimeStamp,
activRef.InputMessage,
activRef.OutputMessage,
LEFT(activRef.Status,20));
PASSTHRU ('Commit') ;
|
What can I do in order to force all of the commits in ESQL? (appart from the PASSTHRU('Commit');
REGARDS --- I want to give the !!!! |
|
| Back to top |
|
|
| dprogwmb |
| Posted: Fri Aug 19, 2011 2:46 pm Post subject: IT's something from Mellmack I think (alf's planet) |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
| mmm... I've been doing some testing... for example: put in one node the 1st insert and in other node the second insert... but It didn't work... and more extreme: I've made 2 msgflows (1 for each insert)... and deployed them in different execution groups... and still didn't work... I think that is a broker configuration or something like this... Can anybody give me any sugestion ??? The awards are still waiting.... REGARDSSS ... IT's something from Mellmack I think (alf's planet) |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Aug 19, 2011 5:37 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you looked at changing the transactional properties of the node?
The default may not be what you're looking for.
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| dprogwmb |
| Posted: Fri Aug 19, 2011 7:27 pm Post subject: |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
| fjb_saper wrote: |
Have you looked at changing the transactional properties of the node?
The default may not be what you're looking for. |
Hi, Yes I have played with all of the nodes, including Input and output nodes... and nothing works...
But after all... I decided to test basic things... and I tried to insert a register into the table B (the one which gave me problems of FK) and for my surprise, I couldn't insert a register into that table from Broker... I have 2 possible things to do: 1) Re -use the driver of the broker 6.1 for Oracle (to discard problems with Broker 7 and Oracle, managing Foreign Key)-Because in version 6.1 of Broker this flow worked fine 2) Force data type mapping between Oracle and ESQL (from Message Broker) , because in oracle the Foreign Key is Number(22,0), and from esql I'm using Decimal DataType... does anybody know how to force an Explicit mapping between ESQL and Oracle -the implicit is Decimal -broker- <-> Number -oracle- ... Any other idea? (I have all of the weekend to Test this ALF -estrian- problem... ) |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Aug 19, 2011 9:56 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
At the risk of repeating myself,
Do it all in a stored procedure.
Don't you have a decent Oracle developer at your site?
If you do then they should be able to code this properly (with the appropriate transactionality and error controls) in an hour or two using your ESQL as a template.
Otherwise then and as you are saying this worked in 6.1 and fails in 7.0 it is time for a PMR.
_________________
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 |
|
|
| dprogwmb |
| Posted: Sun Aug 21, 2011 9:08 am Post subject: |
|
|
Voyager
Joined: 19 Jul 2011
Posts: 96
|
Hi all...
Good news , I discovered the problem and fixed it ... and it was:
In oracle in the ID Field of the FK problem I'm having a Data type Number... and in ESQL I was inserting an Integer data type... and I changed it to float data type in ESQL and It worked perfectly...
But I still don't understand why did it work in version 6.1 of Broker?
And the same code didn't work in 7.0.2 of Broker?
Is it a problem of Oracle drivers?
The database was always Oracle 11g... (in 6.1 and 7.0.2 of Broker)...
Any ideas why it worked on version 6.1 of broker, and not in 7.0.2 ?
I think I have the award... ... It was a complicated weekend... |
|
| 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
|
|
|
|
