10 Replies Latest reply on May 15, 2017 2:53 PM by Steven Hawkins

    Using DDL importer in teiid 9.2 with Sybase

    gadeyne.bram Master

      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".

        • 1. Re: Using DDL importer in teiid 9.2 with Sybase
          Ramesh Reddy Master

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

          • 2. Re: Using DDL importer in teiid 9.2 with Sybase
            gadeyne.bram Master

            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
            
            • 3. Re: Using DDL importer in teiid 9.2 with Sybase
              Ramesh Reddy Master

              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?

              • 4. Re: Using DDL importer in teiid 9.2 with Sybase
                Steven Hawkins Master

                > 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.

                • 5. Re: Using DDL importer in teiid 9.2 with Sybase
                  gadeyne.bram Master

                  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>
                  
                  • 6. Re: Using DDL importer in teiid 9.2 with Sybase
                    gadeyne.bram Master

                    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.

                    • 7. Re: Using DDL importer in teiid 9.2 with Sybase
                      Ramesh Reddy Master

                      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

                      • 8. Re: Using DDL importer in teiid 9.2 with Sybase
                        gadeyne.bram Master

                        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...

                        • 9. Re: Using DDL importer in teiid 9.2 with Sybase
                          Steven Hawkins Master

                          > 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.