aspose file tools*
The moose likes JDBC and the fly likes transaction problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "transaction problem" Watch "transaction problem" New topic
Author

transaction problem

J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 917
    
  13

I have a DAO that needs to update 3 tables (iSeries, DB2), so I'm running it as an atomic transaction. However, I'm getting "[SQL7008] (table) in (file) not valid for the operation"

It's explained here.

I am not a DBA so I don't understand the fixes explained on that web page. I don't want auto-commit on because that defeats the whole purposed of using a transaction.

What to do? I can write my own rollback mechanism if needed, but that's a PITA. I'd prefer to let DB2 handle that side of things.


"The good news about computers is that they do what you tell them to do. The bad news is that they do what you tell them to do." -- Ted Nelson
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

That looks like an iSeries DB2/400 issue.

We've been working with iSeries for a while now and sometimes get these errors when the underlying FILE(s)
are not properly configured for permissions.

WP
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 917
    
  13

It's not permissions because I can run the same SQL statement from WinSQL and it works fine. Only when the DAO turns off auto-commit and tries to run the same statement does it blow up.

Our iSeries programmer tells me that we are not doing journaling on the test library and we don't want to add it because of the overhead. It appears that I won't be able to use a transaction but will have to leave auto-commit on. I guess I'll just have to check the return code from the execute methods and do my own rollback routine. Crap. This DAO just got a lot more complicated and more fragile.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I hope you come back, what sort of application are you running? your DBAs not allowing you to run transactions is crazy
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 917
    
  13

It's poorly designed application. Two of the tables should have been combined into one, but I inherited this and can't change it now because of other dependencies. We run transactions on the production libraries, but they don't want to add it to the test library and we don't have a test iSeries; just the production box. It makes things challenging. Our iSeries programmer is constantly compiling stuff directly into production and it gives me the willies every time he does that, but he's been doing it for 30+ years, so I don't question it.

On the upside, this is a minor application that will only be used by one person, once a month, so if it screws up I'll just go in and fix the records manually. I don't like it, but that's the way it is.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

so it isn't mission critical used constantly by hundreds of people, well that is ok (ish).
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 917
    
  13

Yeah, if it was critical I'd put up more of a fight about doing it the right way. But we all know that we don't always get to do things the way we want. Sometimes it's just "get it into production and move onto the next project".
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

@JK

I know exactly what you mean.

We have a mix of DB2/Windows and DB2/iSeries here and depending on how the wind blows, they move instances around.
(Something to do with licensing).

What we did was design logical rollbacks, which is not the "correct" way to do it, and put the most volatile unit-of-work first!
so if that fails, we don't continue with the others.

Well now, the powers that be have inked a deal with Micro$oft to move everything (except iSeries) to SQL Server!!

Job security I guess, but annoying as hell.

At least I didn't get scared, and just quit like another poster here in a different thread ;)
I think of it as a challenge, but would really have preferred MySQL on Linux servers.

Oh well, that's what golf course meetings between vendors and clients result in.

WP
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

*lots of swear words* did they ask a single techy, OF COURSE NOT.....
J. Kevin Robbins
Bartender

Joined: Dec 16, 2010
Posts: 917
    
  13

Many years ago someone said that most technical decisions in corporate IT are based on articles from in-flight magazines. Unfortunately, there seems to be a lot of truth to that. They don't bother to ask the people who actually understand the technology. No, just believe the vendor sales rep. We've all seen how that usually works out.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: transaction problem