delay commit when using built-in mode tMysqlOutput
Hi there!
I am wondering if there is anyway to delay the committing of data written using the built-in mode in tMysqlOutput or other db output components? Or does it have to be auto-commit, since a separate commit can't know about the component to be associate with?
I'm wondering because the job I'm creating has a large number of connections (10ish), and more will be added in the future. I'd like to be able to loop through the connections as I'm performing an identical operation on each. I only want to commit all these changes once I'm sure there were no errors with any of the operations.
I'd never come across Federated storage engines, they sound interesting, I think this would add unnecessary complexity to our dbs, in order to make this job a bit cleaner! For now though, I've unfortunately had to stick to having a connection component for each db in the parent job, along with the commits at that same level, and a new connection/commit component will have to be added for each new client.
I was, however, able to simplify the child job by having a single 'shared' connection component being looped on, and programmatically setting the 'shared db connection name' field at each loop. This mean this subjob now doesn't need to be updated per client, only the parent job.
There exists component tMysqlCommit with is linked with tMysqlConnection. Can you please post your job here? I am not sure, how are you managing multiple connections.
Have you tried tMySqlCommit? You can uncheck auto-commit in your connection component, and use one or more of these on component ok or on subjob ok. This will ensure no commits are made in the event of an error. If there is an error you can use tMySqlRollback to rollback any non committed transactions.
Hey thanks for your quick reply. Perhaps I should be provide a bit more info.
In order to use the Commit component, there needs to be an associated tMysqlConnection component for that db. However, in trying to loop through the db connections, I would not be using a tMysqlConnection component since I would be providing the connection details inside the tMysqlOutput component.
So currently it's:
MysqlConnection1 -> process -> MysqlOutput1
MysqlConnection2 -> process -> MysqlOutput2
...
MysqlConnectionN -> process -> MysqlOutputN
Commit1 -> Commit2 -> ... -> CommitN
Whereas I'd like to be able to do:
JavaFlex (for i in dbs [1, 2, ..., N]) {
get connection props for db i
process -> MysqlOutput_i (connect within this component)
}
Commit all
I am not sure why you work like this (Are the connections to one database or to several databases?), but this may lead to inconsistencies when several commits will be performed and several not.
If you want to keep the consistency you need to do everything in one transaction.
Can you please describe why are you working like this and why is it not possible to do it in one transaction, when (as you mentioned), you are repeating the same operation several times?
I should specify, I'm performing the same operation on a different db each time. And there are a lot of them/the list of db grows regularly, hence why I'd like to be able to programmatically connect, load and at the end of all processing, commit all.
Create one database where with FEDERATED storage engine will be made links to all databases and then create storage procedure which will update all databases (tables with engine FEDERATED) and manage all the commits in one procedure. If the operation is simple, it can be a way.
I'd never come across Federated storage engines, they sound interesting, I think this would add unnecessary complexity to our dbs, in order to make this job a bit cleaner! For now though, I've unfortunately had to stick to having a connection component for each db in the parent job, along with the commits at that same level, and a new connection/commit component will have to be added for each new client.
I was, however, able to simplify the child job by having a single 'shared' connection component being looped on, and programmatically setting the 'shared db connection name' field at each loop. This mean this subjob now doesn't need to be updated per client, only the parent job.
