Highlighted
Six Stars

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.

 

Thanks for your help!

1 ACCEPTED SOLUTION

Accepted Solutions
Six Stars

Re: delay commit when using built-in mode tMysqlOutput

Hi again lojdr,

 

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.

 

Thanks for your suggestions!

7 REPLIES
Eight Stars

Re: delay commit when using built-in mode tMysqlOutput

Hello,

 

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.

 

Regards

Lojdr

Seven Stars

Re: delay commit when using built-in mode tMysqlOutput

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.

Six Stars

Re: delay commit when using built-in mode tMysqlOutput

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

Thanks

 

Eight Stars

Re: delay commit when using built-in mode tMysqlOutput

Hello,

 

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?

 

Regards

Lojdr

Six Stars

Re: delay commit when using built-in mode tMysqlOutput

Hi Lojdr,

 

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.

 

Thanks,

 

jonasdb

Eight Stars

Re: delay commit when using built-in mode tMysqlOutput

Hello,

 

Hmmmm... In my mind came one idea.

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.

Not sure if is it acceptable solution for you. 

 

Regards

Lojdr

Six Stars

Re: delay commit when using built-in mode tMysqlOutput

Hi again lojdr,

 

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.

 

Thanks for your suggestions!