IDS Forum

RE: usage of oledb

Posted By: Philippe Fornaciari
Date: Monday, 28 August 2017, at 5:37 a.m.

In Response To: usage of oledb (Habichtsberg, Reinhard)

Hello,

We got this kind of issue in the same context. This problem was only on tables including Bigint and Bigserial data type.

The problem was identified as IC82827

idsdb00239667 - ERROR: THE OLE DB PROVIDER "IFXOLEDBC" FOR LINKED SERVER <XXX> SUPPLIED INCONSISTENT METADATA FOR A COLUMN. - FOR COLUMNS OF
VARCHAR(M,R) (CSDK-4.10.xC3 - Closed)

Informix support found a way to fix this with the following with the following SQL command to run on the sysmaster database.

Would you mind to run this SQL against sysmaster to update the OLEDB support
table and try again from SQLServer?

update sysmaster:informix.oledbtypes set xstid=52 where typename='bigint';

update sysmaster:informix.oledbtypes set xstid=53 where
typename='bigserial';

'52' and '53' are the correct extended id for bigint and bigserial

KR,

Philippe FORNACIARI

Tel +33 (0)5 46 44 75 76
Email p.fornaciari@irium-software.com
Web www.irium-software.com

� Please consider the environment before printing this e-mail

Les informations contenues dans ce courrier, y compris les éventuels documents joints, peuvent être confidentielles. Ce courrier est destiné exclusivement au(x) destinataire(s) et aux personnes autorisées par le(s) destinataire(s). Si vous n’êtes pas le destinataire visé, nous vous informons par la présente que la loi interdit strictement toute diffusion, copie ou divulgation de cette communication. Si vous avez reçu ce message par erreur, nous vous saurions gré d’en informer immédiatement l’expéditeur par courrier et de supprimer ce message de vos systèmes. IRIUM n’est responsable - ni ne souscrit à – aucune opinion, recommandation, conclusion, sollicitation, offre ou accord ou encore information contenu(e)s dans cette communication. IRIUM décline toute responsabilité en cas de préjudice résultant de l’utilisation de l’Internet et/ou du courrier.

-----Message d'origine-----
De : ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] De la part de Habichtsberg, Reinhard
Envoyé : vendredi 25 août 2017 12:05
À : ids@iiug.org
Objet : usage of oledb [39756]

Hi all,

we are working on a data warehouse project which will be implemented with MS SQLServer. Now the guys from software development urge me to run the coledbp.sql script on the sysmaster database. They want to use a OLEDB Linked Server in Microsoft® SQL Server to allow retrieval of data in an IBM Informix database server. They want to extract the data directly from the Informix databases to put them into their SQL Server DWH.

A first test showed up with an error: Der OLE DB-Anbieter 'Ifxoledbc' für den Verbindungsserver 'TESTINFORMIX1' hat inkonsistente Metadaten für eine Spalte bereitgestellt. Für die ik-Spalte (Kompilierzeit-Ordnungszahl 1) des "adressen":"informix"."ik_stamm"-Objekts wurde für 'DBCOLUMNFLAGS_ISNULLABLE'
der Wert 0 zur Kompilierzeit und 32 zur Laufzeit gemeldet.
Try to translate: The OLE DB-provider 'Ifxoledbc' for the connectionserver 'TESTINFORMIX1' has inconsistent metadata for a row provided. For the ik-column (compiletime-ordinal number 1) of the "adressen":"informix"."ik_stamm"-object is reported the value 0 during compilation and 32 while running for 'DBCOLUMNFLAGS_ISNULLABLE'.
The query was:
SELECT * FROM
[adressen@sds_db1_tli].adressen.informix.ik_stamm

Some queries on other tables ran without error, other with conversation errors.

For the error above we found a workaround:
SELECT *
FROM OPENQUERY([adressen@sds_db1_tli],
'SELECT *
FROM informix.ik_stamm')

I wonder if it is really best practice to use OLEDB esp. with openquery. The amount of data is about 10 TB initial and has to be updated daily. I suspect we run into performance issues - it's only gut feeling, I have no clue really.

I would be glad if some guys here would share their experiences and/or show some alternatives.

TIA,
Mit freundlichem Gruß
Reinhard Habichtsberg
Dienstleistungsverantwortlicher IT-Rechenzentrum
Solaris- und Datenbanksysteme
Abrechnungszentrum Emmendingen
Tel.: +497641/9201-203

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• usage of oledb
  Habichtsberg, Reinhard -- Friday, 25 August 2017, at 6:05 a.m.
  • RE: usage of oledb
    Philippe Fornaciari -- Monday, 28 August 2017, at 5:37 a.m.
    • Re: RE: usage of oledb
      REINHARD HABICHTSBERG -- Tuesday, 29 August 2017, at 6:24 a.m.
  • Re: usage of oledb
    REINHARD HABICHTSBERG -- Tuesday, 29 August 2017, at 6:32 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.