| Author |
Message
|
| wbintegrator |
 Posted: Sun Mar 08, 2015 1:25 am Post subject: SQLServer table created by flow not visible until flow end |
 |
|
Voyager
Joined: 08 Feb 2006
Posts: 83
|
Hi everybody,
Before I try the way that works for sure, I want to share the problem here as it is a good oppurunity for learning something new. Here is the overview of the case and the problem:
1. Flow accesses SQLServer 3 times (through stored procedures) - first time to create table, second to insert data into the table, 3rd to remove the table if something went wrong in the process.
2. after creating the table and inserting data, a webservice is invoked that further processes the inserted rows in the previous step
The problem is that, even though the table and insertion finish OK, the invoked WS does not "see" the table and returns error that object does not exist. Just to remind it all happens in one single flow.
I tried decoupling the 2 steps by creating the table beforehand through manually calling the procedures and only then calling the WS through flow that processes the existing table.
My conjecture - SQL Server does not actually "commit" the DDL/DML operations while still in the flow session, thus not making the objects visible to external processes that open a new session to operate on those objects.
I've tried using Flow Order Node and making the subflow that accesses the DB not part of the transaction unit (just in case) by setting the flag:
| Code: |
| SET OutputLocalEnvironment.Destination.MQ.Defaults.transactionMode = 'no'; |
and setting the flow Transaction Mode to 'No';
didn't help.
The obvious solution seems to be deviding the process into 2 flows, when the WS will be called only after the oblects are created and commited in SQLServer.
Any ideas how the case can be solved without breaking up the flow (& process) into 2 flows?
We are using WMB 7.0.0.7
SQLServer 2008 R2 (transactions managements is automatic) |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Sun Mar 08, 2015 8:21 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You could always consider using a stored procedure that will transactionally create the table and COMMIT it. Then you just have to call the stored proc.
Once you know that the table is there then the rest of the inserts should work fine under normal transactional control
However.... IMHO I regard creating tables on the fly like this to be very bad practice. Many DBA's I've worked with over the years would have hissy fits if an application developer suggested that he gave CREATE_TABLE access to a user for this sort of access. (I was as a former Oracle DBA)
_________________
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 6:25 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Yes, in SQL Server, table creation is part of a transaction (unlike Oracle where this executing a DDL will force a commit). Also, creating temporary tables is a common practice in SQL Server (unlike Oracle where global temporary tables are created as part of the database setup).
I would think that if you put the transaction mode = 'commit' instead of the default 'transaction' on the compute node that calls the procedures will fire a commit on the database, irrespective of the flow. We do this for audits in our flows..
_________________
Regards |
|
| Back to top |
|
|
| wbintegrator |
| Posted: Sun Mar 15, 2015 6:51 am Post subject: |
|
|
Voyager
Joined: 08 Feb 2006
Posts: 83
|
Thanks for your answers guys.
smdavies99 I understand your point and tend to agree, although the design part is closed for discussion right now and we have to work with what we have.
Same for changing the SQL Server part.
akil you might absolutely be right, the thing I forgot to mention is that the DB access part is written in Java node which does not have any of the configurations settings you mentioned (not as far as I know anyway)
Last edited by wbintegrator on Mon Mar 16, 2015 12:29 am; edited 1 time in total |
|
| Back to top |
|
|
| akil |
| Posted: Sun Mar 15, 2015 12:33 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Yes, in the JCN, you need to do connection.commit() in the code, there is no configuration.
If the brokers pool gives a problem, you can open your own JDBC connections..
_________________
Regards |
|
| Back to top |
|
|
| wbintegrator |
| Posted: Tue Mar 31, 2015 11:52 pm Post subject: |
|
|
Voyager
Joined: 08 Feb 2006
Posts: 83
|
| akil wrote: |
Yes, in the JCN, you need to do connection.commit() in the code, there is no configuration.
If the brokers pool gives a problem, you can open your own JDBC connections.. |
Switched to using conn.commit() in code instead of the many little flows we had to write.
Works great so far and easier to maintain and move from env to env.
Thanks guys! |
|
| Back to top |
|
|
| nelson |
| Posted: Wed Apr 01, 2015 6:30 am Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| wbintegrator wrote: |
| akil wrote: |
Yes, in the JCN, you need to do connection.commit() in the code, there is no configuration.
If the brokers pool gives a problem, you can open your own JDBC connections.. |
Switched to using conn.commit() in code instead of the many little flows we had to write.
Works great so far and easier to maintain and move from env to env.
Thanks guys! |
Hi wbintegrator, the Knowledge Center explicitly says:
| Quote: |
When using the getJDBCType4Connection call, your code must comply with the following restrictions:
Do not include code that makes explicit transaction calls such as COMMIT or ROLLBACK. This restriction includes explicit transaction calls in a database stored procedure.
Do not close a connection, or cache a connection in the JavaCompute node. |
So... you should be aware that you have to handle the transaction within the JCN, because the broker will not deal with commits/rollbacks calls. |
|
| Back to top |
|
|
| akil |
| Posted: Fri Apr 03, 2015 8:29 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
My understanding of that note is that once you commit in the JCN, all actions done on that connection are committed ( including from earlier nodes ). Now, if some error happens downstream, then you are left with a partly committed database.
This may or may not be a problem basis the flow. The easy thing to do is use 2 data sources, use one for committing in the JCN , and use the other where you want the broker to commit.
_________________
Regards |
|
| Back to top |
|
|
| nelson |
| Posted: Fri Apr 03, 2015 9:10 pm Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
| akil wrote: |
| My understanding of that note is that once you commit in the JCN, all actions done on that connection are committed ( including from earlier nodes ). Now, if some error happens downstream, then you are left with a partly committed database. |
| Quote: |
JDBC API in an unmanaged environment
You can access standard Java APIs in the code that you write for your JavaCompute nodes, including JDBC calls. You can therefore use JDBC APIs to connect to a database, write to or read from the database, and disconnect from the database. On operating systems other than z/OS, the broker supports your JDBC connection code calling both type 2 and type 4 JDBC drivers in this environment, but does not supply them. You must obtain these drivers from your database vendor. On z/OS, type 2 drivers are not supported.
If you choose this method to access databases, the broker does not support managing the transactions; your code must manage the local commit and rollback of database changes. Your code must also manage the connection lifecycle, connection thread affinity, and connection pooling. You must also monitor the access to databases when you use this technique to ensure that these connections do not cause interference with connections made by the broker. In particular, be aware that type 2 drivers bridge to an ODBC connection that might be in use in message flows that access databases from ESQL. |
|
|
| Back to top |
|
|
| akil |
| Posted: Sat Apr 04, 2015 12:00 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Indeed, there's always the way to open a fresh connection, but that isn't served from the pool, and comes with a performance penalty, unless connection pools are implemented (which aren't trivial)
Using the brokers pool, carefully creating separate dataSources (JDBCProviders) , one to commit in the JCN (not bothering about the brokers commit) and another to not commit in the JCN (and letting the broker commit at the end of the flow) seems to be work pretty well.
_________________
Regards |
|
| Back to top |
|
|
| wbintegrator |
| Posted: Wed May 06, 2015 12:26 am Post subject: |
|
|
Voyager
Joined: 08 Feb 2006
Posts: 83
|
In our case, the requirement was to have the immediate commits even if there are partly commited transactions if something goes wrong.
For such cases we have a special exception handling subflow that deletes the partly commited data. |
|
| Back to top |
|
|
|
|
