Using DDL importer in teiid 9.2 with Sybase

[gadeyne.bram]

Hi,

I'm trying to import some Sybase tables and views.

I create a override translator because we use the JtdsDriver.

CREATE FOREIGN DATA WRAPPER sybaseoverride TYPE sybase OPTIONS ( JtdsDriver true);

I create the server.

CREATE SERVER izisprod FOREIGN DATA WRAPPER sybaseoverride OPTIONS ("jndi-name" 'izisprod');

And the schema

CREATE SCHEMA izisprod SERVER izisprod;
SET SCHEMA izisprod;

But when I try to import

IMPORT FOREIGN SCHEMA "Patient.dbo" FROM SERVER izisprod INTO izisprod
    OPTIONS(
        importer.useFullSchemaName false,
        importer.tableTypes 'TABLE,VIEW'
);

I always get a message that the izisprod model does not contain any objects and that the deployment of the vdb has failed.

For "Patient.dbo" I've tried several formats: Patient, "Patient", "Patient"."dbo".

I know that in the importer in teiid designer I always get the schemas (like Patient) and then below these schemas subschemas like dbo and schemas for every username in the database. In Teiiddesigner I always checked the checkbox next to the subschema named dbo and unchecked the others.

If I look to a table name in my original .vdb file then I see something like name in source Patient.dbo.P_GeneralData or for other tables the dotted parts are sometimes encapsulated in double quotes like "Patient"."dbo"."P_GeneralData".

[rareddy]

Can you try just "Patient" instead of "Patient.dbo"? Basically I send that as the schema element in the getTables call on DatabaseMetadata api.

[gadeyne.bram]

Hi Ramesh,

I've did some further investigation. I've created a small Java program that reads out the DatabaseMetaData interface. Could it be a difference that in Teiid I'm using the XADataSource type in stead of the normal Driver?

try(Connection connection = DriverManager.getConnection("jdbc:jtds:sybase://izisdata2:5000/Patient","username","password");){
  DatabaseMetaData m = connection.getMetaData();
  System.out.println("Catalog: \""+connection.getCatalog()+"\"");
  System.out.println("\nCatalogs");
  System.out.println("--------");
  try(ResultSet rs=m.getCatalogs()){
       while(rs.next()){
            System.out.println(rs.getString(1));
       }
  }
  System.out.println("\nSchemas");
  System.out.println("-------");
  try(ResultSet rs=m.getSchemas()){
       while(rs.next()){
            System.out.println(rs.getString(1)+" "+rs.getString(2));
       }
  }
  System.out.println("\nTables");
  System.out.println("------");
  try(ResultSet rs = m.getTables(connection.getCatalog(), null, null, null)){
       while (rs.next()) {
            System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
       }
  }

  System.out.println("\nCatalog Patient, Schema dbo");
  System.out.println("------");
  try(ResultSet rs = m.getTables("Patient", "dbo", null, null)){
       while (rs.next()) {
            System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));
       }
  }
} catch (SQLException e) {
     e.printStackTrace();
}

This outputs (reduced)

Catalog: "Patient"


Catalogs
--------
...
Department
Patient
System
....

Schemas
-------
...
dbo null
...

Tables
------
...
Patient dbo P_GeneralData TABLE
...

Catalog Patient, Schema dbo
------
....
Patient dbo P_GeneralData TABLE
...

So I think the schema name should be dbo?

So I've changed the import statement to this (also tried Patient and "Patient" as the schema name:

IMPORT FOREIGN SCHEMA dbo FROM SERVER izisprod INTO izisprod
    OPTIONS(
  importer.catalog 'Patient',
        importer.useFullSchemaName false,
        importer.tableTypes 'TABLE,VIEW'
);

This always results in this stack trace:

08:30:56,584 WARN  [org.teiid.RUNTIME] (Worker8_async-teiid-threads18)  TEIID50036 VDB vdb.4.0.0 model "izisprod" metadata failed to load. Reason:TEIID11010 java.sql.SQLException: Object does not exist in this database.: org.teiid.translator.TranslatorException: TEIID11010 java.sql.SQLException: Object does not exist in this database.
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:308)
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:69)
        at org.teiid.query.metadata.NativeMetadataRepository.getMetadata(NativeMetadataRepository.java:96)
        at org.teiid.query.metadata.NativeMetadataRepository.loadMetadata(NativeMetadataRepository.java:62)
        at org.teiid.metadata.MetadataRepository.loadMetadata(MetadataRepository.java:46)
        at org.teiid.runtime.AbstractVDBDeployer$MetadataRepositoryWrapper.loadMetadata(AbstractVDBDeployer.java:84)
        at org.teiid.query.metadata.ChainingMetadataRepository.loadMetadata(ChainingMetadataRepository.java:55)
        at org.teiid.jboss.VDBService$6.run(VDBService.java:342)
        at org.teiid.jboss.VDBService$7.run(VDBService.java:393)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:282)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Object does not exist in this database.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:677)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1032)
        at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getCrossReference(JtdsDatabaseMetaData.java:614)
        at net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData.getImportedKeys(JtdsDatabaseMetaData.java:887)
        at org.teiid.translator.jdbc.JDBCMetdataProcessor.getForeignKeys(JDBCMetdataProcessor.java:572)
        at org.teiid.translator.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:172)
        at org.teiid.translator.jdbc.JDBCExecutionFactory.getMetadata(JDBCExecutionFactory.java:306)
        ... 14 more

[rareddy]

Right now there is no semantics in the statement for catalog information, IMO we should come up with some semantics like in "Patient.dbo", Patient is catalog and "dbo" is schema. Which has not been done before. Comments?

[shawkins]

> IMO we should come up with some semantics like in "Patient.dbo", Patient is catalog and "dbo" is schema. Which has not been done before. Comments?

It would be best to keep the name components separate, so that we can avoid a lot potential ambiguities.

For now you can just specify the catalog as an importer property in the options clause.

[gadeyne.bram]

Hi,

I've tried this again against the 9.3 beta release but I can still not get the importer to import the Patient schema.

I've again tried different versions but if I'm correct this one should work (given the schema and catalog names from my previous post).

IMPORT FOREIGN SCHEMA dbo FROM SERVER izisprod INTO izisprod
    OPTIONS(
    importer.catalog 'Patient',
    importer.useFullSchemaName false, 
      importer.tableTypes 'TABLE,VIEW'
);

All different versions still resulted in "java.sql.SQLException: Object does not exist in this database."

This is a snippet from the xmi document that was generated by teiiddesigner. Do you see anything else I can try in DDL?

<jdbc:JdbcSource xmi:uuid="mmuuid:f4ea2b50-9408-4610-b10d-6bc14b964111" name="IZISprod" driverName="Sybase" driverClass="net.sourceforge.jtds.jdbc.Driver" username="xxx" 
          url="jdbc:jtds:sybase://izisdata2:5000/Patient;JtdsDriver=true">
    <importSettings xmi:uuid="mmuuid:e1c51c5a-8502-4cf5-bc79-a33dfdfc8efa" createSchemasInModel="false" generateSourceNamesInModel="FULLY_QUALIFIED" includeIndexes="false" 
          includeProcedures="true" includeApproximateIndexes="false">
      <includedCatalogPaths>/Patient</includedCatalogPaths>
      <includedSchemaPaths>/Patient/dbo</includedSchemaPaths>
      <excludedObjectPaths>multiple e.g. /Patient/dbo/TABLE/A_P_DailyOrderSchedule</excludedObjectPaths>
      <includedTableTypes>TABLE</includedTableTypes>
      <includedTableTypes>VIEW</includedTableTypes>
    </importSettings>
  </jdbc:JdbcSource>

[gadeyne.bram]

I've got it working! The problem occurs when teiid tries to import the foreign key information.

IMPORT FOREIGN SCHEMA dbo FROM SERVER izisprod INTO izisprod
    OPTIONS(
    importer.catalog 'Patient',
    importer.schemaPattern 'dbo',
    importer.importKeys false,
    importer.useFullSchemaName false, 
      importer.tableTypes 'TABLE,VIEW'
);

The problem is now that the primary key information is also missing.

With importKeys enabled I see this in the logs:

16:18:03,382 DEBUG [org.teiid.CONNECTOR] (Worker10_async-teiid-threads74)  JDBCMetadataProcessor - Importing tables

16:18:03,742 DEBUG [org.teiid.CONNECTOR] (Worker10_async-teiid-threads74)  JDBCMetadataProcessor - Importing columns

16:18:04,226 DEBUG [org.teiid.CONNECTOR] (Worker10_async-teiid-threads74)  JDBCMetadataProcessor - Importing primary keys

16:18:06,757 DEBUG [org.teiid.CONNECTOR] (Worker10_async-teiid-threads74)  JDBCMetadataProcessor - Importing index info

16:18:07,742 DEBUG [org.teiid.CONNECTOR] (Worker10_async-teiid-threads74)  JDBCMetadataProcessor - Importing foreign keys

and then the exception occurs. I can not see what database object it is trying to access.

[rareddy]

Looking at [1] it looks like for the FK it is cross checking the reference columns in the primary table and finds some anomaly.  You need to check into that.

[1] jtds/JtdsDatabaseMetaData.java at master · rjnichols/jtds · GitHub

[gadeyne.bram]

Hi Ramesh,

I've used wireshark to see what is send to the database.

I see that a command is send sp_fkeys U_PV_Comorbv2 which is a view. Indeed when I execute this command myself I get the same error. Are foreign keys possible in views in Sybase? Maybe it does not really make sense to issue this command then.

I'm also wondering if this should be captured and logged as a warning message instead of a hard exception that interrupts the further import process?

I've also found that when I do "importer.importKeys false" then the tables and views are read correctly but the views are listed as tables and not as views...

[shawkins]

> I'm also wondering if this should be captured and logged as a warning message instead of a hard exception that interrupts the further import process?

Yes this should be logged in general, and avoided specifically for sybase.

> I've also found that when I do "importer.importKeys false" then the tables and views are read correctly but the views are listed as tables and not as views...

There has not been consensus on how/if Teiid should report source table types.  From the perspective of the runtime they are all effectively treated as a table or specifically a foreign table - the differentiation of whether it's a source system table, source view etc. typically doesn't even matter to the translator.  If you have a scenario where you need to retain the source table types for Teiid JDBC database metadata we can start a new discussion/issue.

[shawkins]

The import issue will be worked under [TEIID-4907] Sybase and other import should tolerate exception retrieving foreign keys - JBoss Issue Tracker