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.
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.
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".
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.