IBM Linked server will not do inserts

15080 pts.
Tags:
AS/400
AS/400 database
Microsoft SQL Server 2000
SQL 2000
SQL Server
I've spent close to 2 days looking for an answer and only got a headache. We have our IBM DB2 linked server defined as IBM DB2 UDB for iSeries OLE DB Provider. We can run select, update and deletes we just cannot do an insert. Here are our sample statements. SELECT * FROM OPENQUERY(OUR_DB2,'SELECT * FROM MYLIB.AUMP') UPDATE OPENQUERY(OUR_DB2, 'SELECT * FROM MYLIB.AUMP WHERE AUPGM = ''MYPGM'' ') SET AUUSR = 'AUUSR_3' DELETE OPENQUERY(OUR_DB2,'SELECT * FROM MYLIB.AUMP') WHERE AUPGM = 'MYPGM' insert into OPENQUERY(OUR_DB2, 'select AUPGM,AUUSR from MYLIB.AUMP ') values('MYPGM' , 'MYUSER') We have the provider "IBMDA400" set to allow in process. This was the only IBM provider on the drop down list to choose from. It's a SQL Server 2000. When we try and run the insert this is the error data we get. OLE DB error trace [OLE/DB Provider 'IBMDA400' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.]. Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'IBMDA400' reported an error. The provider does not support the necessary method. I've Googled and tried a lot of suggestions out there but some see so involved or did not work. I don't see why everything but the insert works. Any ideas ???

Software/Hardware used:
sql server 2000, i-series

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    I've Googled and tried a lot of suggestions out there but some see so involved or did not work.
    .
    But what suggested fixes did you actually try? We should know so we don't waste time (ours and yours) suggesting things you have actually eliminated. At least one IBM document seems extremely close.
    .
    Also, what OS is running on both ends of the connection? And what iSeries Access version and service level are being used?
    .
    Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    HI Tom, Our server SQL 2000 and our i-Series is currently on V7R1M0. We even tried setting up the linked server on our local machine just to do a test.Our local machine is running SQL 2008 R2 and the machine has more provider options like IBMDA400, IBMDARLA and IBMDASQL. We were unable to get any of these to do an insert. We also tried suggestions we found like "SET IMPLICIT_TRANSACTIONS ON" and "COMMIT TRANSACTION". We even tried using the 4-part linked server name without the openqry and still no luck. We did not try the things listed below because we figured if 3 out of the 4 SQL functions work, then to fix the insert should not involve that much work. A lot of the post we read seem to contradict each other some say use IBMDA400 and others say don't, use IBMDASQL. Some went as far as to say you need to configure the data source connection string, and cut and paste that into the Linked server definition. Another suggestion was to configure the Microsoft Distributed Transaction Coordinator but that involves changing the registry. Hope that info helps a little more.
    15,080 pointsBadges:
    report
  • ToddN2000
    One more thing I forgot. To complicate things a bit our companies two IT departments are always butting heads. We did see some suggestions that we should turn journaling on but the AS/400 side does not want journaling turned on. So that put a road bock up on a few other suggestions we saw listed.
    15,080 pointsBadges:
    report
  • CharlieBrowne
    Since you cannot turn on journaling, is there a place to specify COMMIT(*NO)? If not, I would try to get journaling turn on, just to see if it works. You could even create a testing library with those file and journal those objects. Then delete everything when you are done testing. This allows you to do a step at a time.
    41,380 pointsBadges:
    report
  • TomLiotta
    So, the AS/400 group is not concerned with database integrity? Does the top level of company management know? IMO, their jobs are at risk (both those who won't journal and any high-level management who approve of not doing it). It's one thing to avoid journals when it's fully controlled within the system. But when remote systems become part of the transaction processes, it becomes risky indeed. (And even in-house transactions should be journaled.)
    .
    Journaling should be enabled at least long enough to see if it works. A process should be verified using correct configuration first. Once you know you have everything right, then you can start looking at changes such as trying to run without journaling.
    .
    Did you read to the bottom of the Configuring an OLE DB Provider on an SQL Server page? It'd be easiest to search the page for [ Msg 7399 ].
    .
    Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    Thanks for the link Tom. Here's the deal on journaling, I have had to go back and re-build corrupt files and files that records accidentally deleted through a utility I found called UNDEL2. I told them if we had journaling it would have not been an issue. They still do not want it on and I was not given a good reason other than the overhead it takes.. As a side note as to why we are so aggravated, is we can do inserts to the I-Series from our web service using an IBM DB2 connection string using the same credentials. The problem happens when we are trying to set up an agent job to run a stored procedure to do the final inserts into the I-Series database every 5 minutes.
    15,080 pointsBadges:
    report
  • CharlieBrowne
    What additional overhead is the AS400 group concerned about? Performance wise, it is minimal. The information is already in storage, It will just do 2 writes (assuming you journal before and after images and omitting OPNCLO) If disk space is a concern, disk is cheap. And you have the receivers changed automatically and copied to a backed and removed from the system. "Overhead" as a reason for not journaling has never been an acceptable excuse.
    41,380 pointsBadges:
    report
  • ToddN2000
    I hear you loud and clear CharlieBrown. Wish I had a say in the matter. I know disk gets cheaper all the time and as for performance, it's not that big of a hit. I'd do it just for the safety of the database issues alone. In the past few years we had quite a few damaged objects due to power failures. We did not have a UPS at the time and I was fortunate I could recover the data. They finally got a UPS. We do run several large divisions of our company on this machine but not enough to warrant their fears. I feel it's more of an issues as to will be responsible for the process. This becomes more of an concern to them I think when the current project I am involved with get rolled out to all of our I-Series machines (5). Only 2 are local, 2 remote and 1 overseas. Sorry for getting off topic but it's alw3ays been an uphill battle.
    15,080 pointsBadges:
    report
  • TomLiotta
    Space shouldn't be a big concern. Journaling might only be introduced for files modified remotely, and receiver size could be set reasonably small. . Also, at i 6.1 and later, an exit program can be assigned to the QIBM_QJO_CHG_JRNRCV exit point. This program could delete receivers as they are detached so that no significant space is ever consumed. . I'd probably modify that to track the last couple detached receivers and only delete the oldest one. That would let you keep a little history, but the amount of history could be adjusted by varying receiver size and number of receivers. Management could be automated. . It's not actually required to save receivers if you never intend to restore them. But it sure can be nice having a couple on hand. . Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following