usage of oledb

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

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.

Philippe, thank you very much. We have no Bigint nor Bigserial in the table in question. dbschema: create table "informix".ik_stamm ( ik integer, typ char(1), name1 char(28), name2 char(28), name3 char(28), str_postf char(46), plz char(5), ort char(24), blz integer, kontonummer decimal(10,0), kontoinhaber char(26), anhang char(1), lkz1 char(3), name4 char(28), strasse2 char(46), lkz2 char(3), plz2 char(5), ort2 char(24), grp_ik integer, vorwahl char(6), telefon char(9), fax char(9), klassifikation char(2), regional_kz char(2), bundesland char(2), reg_bezirk char(2), regional_kbv char(2), regional_kzbv char(2), bank_lkz char(3), bank char(27), art char(1), aend_datum date, loesch_datum date, loesch_user char(10), belege_vorhanden char(1), sap_datum date, vst_abtr_kz char(1), abrcode smallint, erf_user char(10), erf_datum datetime year to second, iban char(34), bic char(11), vorwahl_fax char(6), mobilfunknummer char(20) ) extent size 300000 next size 100000 lock mode row; revoke all on "informix".ik_stamm from "public" as "informix"; alter table "informix".ik_stamm add constraint primary key (ik) constraint "informix".pk_ik_stamm ; But I will try what you suggested. Thanks again, Reinhard.

No one here who may share one's experience with oledb as tool for ETL-processing?