• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

transaction problem

 
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Ranch Hand
Posts: 859
IBM DB2 Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
so it isn't mission critical used constantly by hundreds of people, well that is ok (ish).
 
J. Kevin Robbins
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 859
IBM DB2 Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@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 L Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
*lots of swear words* did they ask a single techy, OF COURSE NOT.....
 
J. Kevin Robbins
Bartender
Posts: 1810
28
jQuery Netbeans IDE Eclipse IDE Firefox Browser MySQL Database Chrome Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic