Linked SQLserver 2005 gets errors after upgrade to

IDS 11.50.FC6 on HPUX 11.31 itanium server linked to a SqlServer 2005 on w2k3 r2 server (SDK 3.50.TC6). -=-=-=-=-=-=-=-=- Our ERP systems also has a web portal that uses SqlServer, prior to the upgrade of Informix (10.00.FC9 -> 11.50.FC6) this Sunday we could connect from the Sqlserver and get data from Informix database. After the upgrade the link is not work as it did before. We have run the coledbp.sql on the Informix sysmaster to recreate the needed tables after the upgrade. We are the using "IBM Informix OLE DB provider" for the linked server connection. The linked server was working correctly with either direct sql's or using openquery prior to the upgrade. After the upgrade the linked server stopped working. One of the developers provided this simples example of sql that worked before the upgrade. This SQL used to work from the SQL server but now fails: SELECT id FROM cx.cars.informix.id_rec where id =1 giving this error: OLE DB provider "Ifxoledbc" for linked server "cx" returned message "EIX000: (-750) Invalid distribution format found for nrows". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "Ifxoledbc" for linked server "cx". The provider supports the interface, but returns a failure code when it is used. However openquery method continues to work select * from openquery(cx,'SELECT * FROM cars:id_rec where id = 1') Does anyone have a suggestion to get the first sql working again? John Adamski Network Specialist Graceland University

Have you bounced your sqlserver 2005, it needs a restart. Also have you upgraded your Client SDK. Lloyd On Wed, 04 Aug 2010 23:16:07 +0530 wrote >IDS 11.50.FC6 on HPUX 11.31 itanium server linked to a SqlServer 2005 on w2k3 r2 server (SDK 3.50.TC6). -=-=-=-=-=-=-=-=- Our ERP systems also has a web portal that uses SqlServer, prior to the upgrade of Informix (10.00.FC9 -> 11.50.FC6) this Sunday we could connect from the Sqlserver and get data from Informix database. After the upgrade the link is not work as it did before. We have run the coledbp.sql on the Informix sysmaster to recreate the needed tables after the upgrade. We are the using "IBM Informix OLE DB provider" for the linked server connection. The linked server was working correctly with either direct sql's or using openquery prior to the upgrade. After the upgrade the linked server stopped working. One of the developers provided this simples example of sql that worked before the upgrade. This SQL used to work from the SQL server but now fails: SELECT id FROM cx.cars.informix.id_rec where id =1 giving this error: OLE DB provider "Ifxoledbc" for linked server "cx" returned message "EIX000: (-750) Invalid distribution format found for nrows". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "Ifxoledbc" for linked server "cx". The provider supports the interface, but returns a failure code when it is used. However openquery method continues to work select * from openquery(cx,'SELECT * FROM cars:id_rec where id = 1') Does anyone have a suggestion to get the first sql working again? John Adamski Network Specialist Graceland University ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Lloyd, Yes we have rebooted the SqlServer. That did not change the error. By Client are you meaning the windows side or the HPUX side? If you mean the windows side, no we have the same sdk 3.50.TC6. If you mean the HPUX side I believe the upgrade did upgrade that I will have to go check that - after I remember where to look. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Lloyd S Sent: Wednesday, August 04, 2010 1:05 PM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20736] Have you bounced your sqlserver 2005, it needs a restart. Also have you upgraded your Client SDK. Lloyd

John, By Client, i meant the windows side. As for HPUX end when you installed the engine did you also select Client SDK toolkit. Can you do a test connectivity via odbc? To look for software installed on HPUX end run the below command as root, more $INFORMIX/etc/.snfile Lloyd On Thu, 05 Aug 2010 00:03:10 +0530 wrote >Lloyd, Yes we have rebooted the SqlServer. That did not change the error. By Client are you meaning the windows side or the HPUX side? If you mean the windows side, no we have the same sdk 3.50.TC6. If you mean the HPUX side I believe the upgrade did upgrade that I will have to go check that - after I remember where to look. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Lloyd S Sent: Wednesday, August 04, 2010 1:05 PM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20736] Have you bounced your sqlserver 2005, it needs a restart. Also have you upgraded your Client SDK. Lloyd ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Lloyd, Yep .snfile shows the SDK toolkit was installed. (we use a script provided by the ERP company) it installed Version 3.50.UC5. Their application is still 32-bit so this was expected. We had Version 2.90.UC4R1 when we were on IDS 10. Our Cognos Impromptu and a VB programs can use the HPUX sdk and work fine. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Lloyd S Sent: Wednesday, August 04, 2010 1:50 PM To: ids@iiug.org Subject: Re: RE: Linked SQLserver 2005 gets errors afte.... [20738] John, By Client, i meant the windows side. As for HPUX end when you installed the engine did you also select Client SDK toolkit. Can you do a test connectivity via odbc? To look for software installed on HPUX end run the below command as root, more $INFORMIX/etc/.snfile Lloyd On Thu, 05 Aug 2010 00:03:10 +0530 wrote >Lloyd, Yes we have rebooted the SqlServer. That did not change the error. By Client are you meaning the windows side or the HPUX side? If you mean the windows side, no we have the same sdk 3.50.TC6. If you mean the HPUX side I believe the upgrade did upgrade that I will have to go check that - after I remember where to look. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Lloyd S Sent: Wednesday, August 04, 2010 1:05 PM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20736] Have you bounced your sqlserver 2005, it needs a restart. Also have you upgraded your Client SDK. Lloyd ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi John, a long shot, but might be worth a try. Did you run update stats on all you tables after the upgrade? And I mean ALL tables, including system tables (tabid <= 99). Looks like one of the system tables (systables, sysindices, sysfragments) has invalid distributions for their nrows column which could be even expected given you did a major version upgrade. HTH Davorin

Davorin I did run Art's dostat on the 4 non sys* databases. Hmm, let me check which options I used I might not have gotten the sys* tables. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVORIN KREMENJAS Sent: Thursday, August 05, 2010 6:45 AM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20740] Hi John, a long shot, but might be worth a try. Did you run update stats on all you tables after the upgrade? And I mean ALL tables, including system tables (tabid <= 99). Looks like one of the system tables (systables, sysindices, sysfragments) has invalid distributions for their nrows column which could be even expected given you did a major version upgrade. HTH Davorin ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I checked and I used: dostats -d <dbname> -S -v -E. After a quick read of dostats help I'm now not sure that would get the sys* tables. I thought it would do all tables. Maybe Art can clarify if what I did would get the system tables. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John Adamski Sent: Thursday, August 05, 2010 7:31 AM To: ids@iiug.org Subject: RE: Linked SQLserver 2005 gets errors after up.... [20741] Davorin I did run Art's dostat on the 4 non sys* databases. Hmm, let me check which options I used I might not have gotten the sys* tables. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVORIN KREMENJAS Sent: Thursday, August 05, 2010 6:45 AM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20740] Hi John, a long shot, but might be worth a try. Did you run update stats on all you tables after the upgrade? And I mean ALL tables, including system tables (tabid <= 99). Looks like one of the system tables (systables, sysindices, sysfragments) has invalid distributions for their nrows column which could be even expected given you did a major version upgrade. HTH Davorin ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Ok, I manually ran dostats on all system tables (tabid<=99) in the database we are trying to connect to. Then I asked the developer to try again. This did not fix it, got the same results, openquery works the straight select does not. Thanks for the idea, was worth checking. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVORIN KREMENJAS Sent: Thursday, August 05, 2010 6:45 AM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20740] Hi John, a long shot, but might be worth a try. Did you run update stats on all you tables after the upgrade? And I mean ALL tables, including system tables (tabid <= 99). Looks like one of the system tables (systables, sysindices, sysfragments) has invalid distributions for their nrows column which could be even expected given you did a major version upgrade. HTH Davorin

John, the "-m" option includes updating stats on the system catalog tables, they are not done by default. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 5, 2010 at 8:44 AM, John Adamski <adamski@graceland.edu> wrote: > I checked and I used: dostats -d <dbname> -S -v -E. After a quick read of > dostats help I'm now not sure that would get the sys* tables. I thought it > would do all tables. > > Maybe Art can clarify if what I did would get the system tables. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John > Adamski > Sent: Thursday, August 05, 2010 7:31 AM > To: ids@iiug.org > Subject: RE: Linked SQLserver 2005 gets errors after up.... [20741] > > Davorin > > I did run Art's dostat on the 4 non sys* databases. Hmm, let me check which > options I used I might not have gotten the sys* tables. > > John > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > DAVORIN > KREMENJAS > Sent: Thursday, August 05, 2010 6:45 AM > To: ids@iiug.org > Subject: Re: Linked SQLserver 2005 gets errors after up.... [20740] > > Hi John, > > a long shot, but might be worth a try. > > Did you run update stats on all you tables after the upgrade? > And I mean ALL tables, including system tables (tabid <= 99). > Looks like one of the system tables (systables, sysindices, sysfragments) > has > invalid distributions for their nrows column which could be even expected > given you did a major version upgrade. > > HTH > > Davorin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e0cb4e8873dda1859f048d1a50cb

Art, sorry, i am probably one of the very few not familiar with dostats. will he need to do a special drop distributions run first or will dostats automatically do that? Norma Jean

It is a good idea after an upgrade to drop all distributions and then rerun them. Dostats will do this for you with the --drop-distributions option. That is best done as a separate run from the run to build the replacement stats. It can be as part of the same run, but best practice, I think, is a separate run. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 5, 2010 at 11:24 PM, NORMA JEAN SEBASTIAN <njiiug@gmail.com>wrote: > Art, > sorry, i am probably one of the very few not familiar with dostats. > will he need to do a special drop distributions run first or will dostats > automatically do that? > Norma Jean > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e653963298af7b048d2616c1

After manually dropping the distributions and re-running dostats with also the -m option the developer can now connect using either method. It seems I have an older version of dostats that does not have the --drop-distributions option, so off to the iiug site to get the latest. Hopefully the new HPUX compiler will play nice with me. Thanks everyone for your help. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, August 06, 2010 6:37 AM To: ids@iiug.org Subject: Re: Linked SQLserver 2005 gets errors after up.... [20760] It is a good idea after an upgrade to drop all distributions and then rerun them. Dostats will do this for you with the --drop-distributions option. That is best done as a separate run from the run to build the replacement stats. It can be as part of the same run, but best practice, I think, is a separate run. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 5, 2010 at 11:24 PM, NORMA JEAN SEBASTIAN <njiiug@gmail.com>wrote: > Art, > sorry, i am probably one of the very few not familiar with dostats. > will he need to do a special drop distributions run first or will > dostats automatically do that? > Norma Jean > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e653963298af7b048d2616c1 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.