11 Replies Latest reply on Sep 7, 2009 8:12 PM by rkishore999

    problem using vdblessdef file

      We have written a vdbless.def file to connect to one of our Oracle instances. The file looks like this.

      <?xml version="1.0" encoding="UTF-8"?>

























      db url
      extensionjar:classes12.jar
      schema
      passwd





      We have placed the above file in /deploy directory of TEIID server 6.2 home. We started the server and is running on port 31000.

      We have run this command.
      run.bat vdblessmyvdb "select * from <table_name>"
      We are getting the following error.
      2009-08-24 14:41:14,193 WARN [main] org.jboss.cache.transaction.GenericTransactionManagerLookup - Falling back to JBoss Cache's internal DummyTransactionManager
      2009-08-24 14:42:43,040 ERROR [Worker1_QueryProcessorQueue0] org.teiid.DQP - Unexpected exception for request 0.0
      [MetaMatrixComponentException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      1 [ConnectorException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:149)
      at org.teiid.metadata.QueryMetadataCache.loadMetadata(QueryMetadataCache.java:195)
      at org.teiid.metadata.QueryMetadataCache.lookupMetadata(QueryMetadataCache.java:154)
      at com.metamatrix.dqp.embedded.services.EmbeddedMetadataService.lookupMetadata(EmbeddedMetadataService.java:97)
      at org.teiid.dqp.internal.process.Request.initMetadata(Request.java:215)
      at org.teiid.dqp.internal.process.Request.processRequest(Request.java:581)
      at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
      at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
      Caused by: org.teiid.connector.api.ConnectorException: "Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      at org.teiid.connector.metadata.runtime.MetadataFactory.setValuesUsingParent(MetadataFactory.java:105)
      at org.teiid.connector.metadata.runtime.MetadataFactory.addTable(MetadataFactory.java:117)
      at org.teiid.connector.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:171)
      at org.teiid.connector.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:89)
      at org.teiid.connector.jdbc.JDBCConnector.getConnectorMetadata(JDBCConnector.java:334)
      at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.getConnectorMetadata(ConnectorWrapper.java:167)
      at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.getMetadata(ConnectorManager.java:161)
      at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:147)
      ... 12 more
      2009-08-24 14:44:10,592 ERROR [Worker1_QueryProcessorQueue1] org.teiid.DQP - Unexpected exception for request 1.0
      [MetaMatrixComponentException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      1 [ConnectorException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:149)
      at org.teiid.metadata.QueryMetadataCache.loadMetadata(QueryMetadataCache.java:195)
      at org.teiid.metadata.QueryMetadataCache.lookupMetadata(QueryMetadataCache.java:154)
      at com.metamatrix.dqp.embedded.services.EmbeddedMetadataService.lookupMetadata(EmbeddedMetadataService.java:97)
      at org.teiid.dqp.internal.process.Request.initMetadata(Request.java:215)
      at org.teiid.dqp.internal.process.Request.processRequest(Request.java:581)
      at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
      at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
      at java.lang.Thread.run(Unknown Source)
      Caused by: org.teiid.connector.api.ConnectorException: "Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
      at org.teiid.connector.metadata.runtime.MetadataFactory.setValuesUsingParent(MetadataFactory.java:105)
      at org.teiid.connector.metadata.runtime.MetadataFactory.addTable(MetadataFactory.java:117)
      at org.teiid.connector.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:171)
      at org.teiid.connector.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:89)
      at org.teiid.connector.jdbc.JDBCConnector.getConnectorMetadata(JDBCConnector.java:334)
      at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.getConnectorMetadata(ConnectorWrapper.java:167)
      at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.getMetadata(ConnectorManager.java:161)
      at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:147)
      ... 12 more

      Regards

      Ram

        • 1. Re: problem using vdblessdef file

          We have written a vdbless.def file to connect to one of our Oracle instances. The file looks like this.

          
          <?xml version="1.0" encoding="UTF-8"?>
          <VDB>
           <VDBInfo>
          
           <Property Name="Name" Value="VDBLessMyVdb" />
           <Property Name="Version" Value="1" />
          
           <Property Name="UseConnectorMetadata" Value="cached" />
           </VDBInfo>
          
          
           <Model>
           <Property Name="Name" Value="testdev" />
          
           <ConnectorBindings>
           <Connector Name="Test Oracle Connector" />
           </ConnectorBindings>
          
          
           <Property Name="importer.useFullSchemaName" Value="false"/>
           </Model>
          
          
           <ConnectorBindings>
           <Connector Name="Test Oracle Connector" ComponentType="Oracle Connector">
           <Properties>
           <Property Name="URL">db url</Property>
           <Property Name="ConnectorClassPath">extensionjar:classes12.jar</Property>
           <Property Name="User">schema</Property>
           <Property Name="Password">password</Property>
           </Properties>
           </Connector>
           </ConnectorBindings>
          </VDB>
          
          


          We have placed the above file in /deploy directory of TEIID server 6.2 home. We started the server and is running on port 31000.

          We have run this command.
          run.bat vdblessmyvdb "select * from <table_name>"
          We are getting the following error.
          2009-08-24 14:41:14,193 WARN [main] org.jboss.cache.transaction.GenericTransactionManagerLookup - Falling back to JBoss Cache's internal DummyTransactionManager
          2009-08-24 14:42:43,040 ERROR [Worker1_QueryProcessorQueue0] org.teiid.DQP - Unexpected exception for request 0.0
          [MetaMatrixComponentException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          1 [ConnectorException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:149)
          at org.teiid.metadata.QueryMetadataCache.loadMetadata(QueryMetadataCache.java:195)
          at org.teiid.metadata.QueryMetadataCache.lookupMetadata(QueryMetadataCache.java:154)
          at com.metamatrix.dqp.embedded.services.EmbeddedMetadataService.lookupMetadata(EmbeddedMetadataService.java:97)
          at org.teiid.dqp.internal.process.Request.initMetadata(Request.java:215)
          at org.teiid.dqp.internal.process.Request.processRequest(Request.java:581)
          at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
          at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
          at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
          at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
          at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
          at java.lang.Thread.run(Unknown Source)
          Caused by: org.teiid.connector.api.ConnectorException: "Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          at org.teiid.connector.metadata.runtime.MetadataFactory.setValuesUsingParent(MetadataFactory.java:105)
          at org.teiid.connector.metadata.runtime.MetadataFactory.addTable(MetadataFactory.java:117)
          at org.teiid.connector.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:171)
          at org.teiid.connector.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:89)
          at org.teiid.connector.jdbc.JDBCConnector.getConnectorMetadata(JDBCConnector.java:334)
          at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.getConnectorMetadata(ConnectorWrapper.java:167)
          at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.getMetadata(ConnectorManager.java:161)
          at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:147)
          ... 12 more
          2009-08-24 14:44:10,592 ERROR [Worker1_QueryProcessorQueue1] org.teiid.DQP - Unexpected exception for request 1.0
          [MetaMatrixComponentException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          1 [ConnectorException]"Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:149)
          at org.teiid.metadata.QueryMetadataCache.loadMetadata(QueryMetadataCache.java:195)
          at org.teiid.metadata.QueryMetadataCache.lookupMetadata(QueryMetadataCache.java:154)
          at com.metamatrix.dqp.embedded.services.EmbeddedMetadataService.lookupMetadata(EmbeddedMetadataService.java:97)
          at org.teiid.dqp.internal.process.Request.initMetadata(Request.java:215)
          at org.teiid.dqp.internal.process.Request.processRequest(Request.java:581)
          at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
          at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
          at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
          at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
          at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
          at java.lang.Thread.run(Unknown Source)
          Caused by: org.teiid.connector.api.ConnectorException: "Non-uniquely named record detected 'TableRecordImpl name=OGIS_SPATIAL_REFERENCE_SYSTEMS, nameInSource=null, uuid=null, pathInModel=testdev/OGIS_SPATIAL_REFERENCE_SYSTEMS'
          at org.teiid.connector.metadata.runtime.MetadataFactory.setValuesUsingParent(MetadataFactory.java:105)
          at org.teiid.connector.metadata.runtime.MetadataFactory.addTable(MetadataFactory.java:117)
          at org.teiid.connector.jdbc.JDBCMetdataProcessor.getTables(JDBCMetdataProcessor.java:171)
          at org.teiid.connector.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:89)
          at org.teiid.connector.jdbc.JDBCConnector.getConnectorMetadata(JDBCConnector.java:334)
          at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.getConnectorMetadata(ConnectorWrapper.java:167)
          at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.getMetadata(ConnectorManager.java:161)
          at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:147)
          ... 12 more

          Regards

          Ram

          • 2. Re: problem using vdblessdef file
            rareddy

            Ram,

            Thanks for re-posting. Congratulations and Thanks for trying out the new feature "vdb-less" data integration in 6.2.

            Did you try?

            select * from testdev.OGIS_SPATIAL_REFERENCE_SYSTEMS
            


            also, looks like the import of the metadata is fetching more than single schema, where the above table name exists in multiple schemas. Since, you turned off the "importer.useFullSchemaName=false" they being merged in the single name space and conflicting. You can define the single schema that need to be imported using another property in the "model" element

            <Property Name="importer.schemaPattern" Value="my-schema"/>
            


            this will eliminate conflicts in metadata and also speeds up the metadata importing process significantly. For complete list of properties that you can use to fine tune your import look at

            http://www.jboss.org/community/wiki/TeiidConnectorSuppliedMetadata

            importer settings section for a JDBC connection.

            • 3. Re: problem using vdblessdef file

              Ramesh,

              Thanks for your inputs on this. I got it working after some difficulties.

              There is one issue we were stuck with initially. It is trying download all the procedure metadata and giving the following error. I think if 2 procedures are having a parameter with same name it is getting confused. So we used "" to ignore downloading procedure metadata upon which it went fine.

              [MetaMatrixComponentException]"Non-uniquely named record detected 'testdev.EXECUTE_JOB.P_RUN_DATE'
              1 [ConnectorException]"Non-uniquely named record detected 'testdev.EXECUTE_JOB.P_RUN_DATE'
              at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:149)
              at org.teiid.metadata.QueryMetadataCache.loadMetadata(QueryMetadataCache.java:195)
              at org.teiid.metadata.QueryMetadataCache.lookupMetadata(QueryMetadataCache.java:154)
              at com.metamatrix.dqp.embedded.services.EmbeddedMetadataService.lookupMetadata(EmbeddedMetadataService.java:97)
              at org.teiid.dqp.internal.process.Request.initMetadata(Request.java:215)
              at org.teiid.dqp.internal.process.Request.processRequest(Request.java:581)
              at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:365)
              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:200)
              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
              at com.metamatrix.common.queue.WorkerPoolFactory$StatsCapturingSharedThreadPoolExecutor$1.run(WorkerPoolFactory.java:211)
              at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
              at java.lang.Thread.run(Unknown Source)
              Caused by: org.teiid.connector.api.ConnectorException: "Non-uniquely named record detected 'testdev.EXECUTE_JOB.P_RUN_DATE'
              at org.teiid.connector.metadata.runtime.MetadataFactory.setValuesUsingParent(MetadataFactory.java:105)
              at org.teiid.connector.metadata.runtime.MetadataFactory.addProcedureParameter(MetadataFactory.java:303)
              at org.teiid.connector.jdbc.JDBCMetdataProcessor.getProcedures(JDBCMetdataProcessor.java:131)
              at org.teiid.connector.jdbc.JDBCMetdataProcessor.getConnectorMetadata(JDBCMetdataProcessor.java:102)
              at org.teiid.connector.jdbc.JDBCConnector.getConnectorMetadata(JDBCConnector.java:334)
              at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.getConnectorMetadata(ConnectorWrapper.java:167)
              at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.getMetadata(ConnectorManager.java:161)
              at com.metamatrix.dqp.embedded.services.EmbeddedDataService.getConnectorMetadata(EmbeddedDataService.java:147)

              
              select * from testdev.OGIS_SPATIAL_REFERENCE_SYSTEMS
              
              
              


              I ran this query. I got the following error. What is this query meant for?

              2009-08-24 19:47:44,031 WARN [Worker1_QueryProcessorQueue0] org.teiid.DQP - Processing exception 'Group does not exist: testdev.OGIS_SPATIAL_REFERENCE_SYSTEMS' for request 0.0. Exception type com.metamatrix.api.exception.query.QueryResolverException throw from com.metamatrix.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:868). Enable detail logging to see the entire stacktrace.
              2009-08-24 19:47:44,062 WARN [Worker1_QueryProcessorQueue1] org.teiid.DQP - Processing exception 'Group does not exist: testdev.OGIS_SPATIAL_REFERENCE_SYSTEMS' for request 1.0. Exception type com.metamatrix.api.exception.query.QueryResolverException throw from com.metamatrix.query.resolver.util.ResolverUtil.handleUnresolvedGroup(ResolverUtil.java:868). Enable detail logging to see the entire stacktrace.

              Regards

              Ram



              • 4. Re: problem using vdblessdef file
                rareddy

                Ram,

                Ignore the "select" command from above, that was the table which was conflicting before, just try to access whatever tables in your schema and make sure you are able to query them.

                Ramesh..

                • 5. Re: problem using vdblessdef file
                  shawkins

                  To see all of your tables you can use the system tables:

                  select * from system.groups

                  the fullname column will contain the fully qualified table name. The table names can also be seen using any tool that reads JDBC DatabaseMetaData.

                  I'll add a JIRA/KI about importing overloaded procedures. Currently our metadata doesn't support defining overloaded procedures, but some sources do provide a unique name for each procedure signature that could be used as our import name.

                  • 6. Re: problem using vdblessdef file

                    One more thing I noticed is password in ConfigurationInfo.def is not being encrypted in 6.2. If a vdb is generated with encrypted password and is being deployed in 6.2 server, oracle login is failing while accessing the deployed vdb.

                    Regards

                    Ram

                    • 7. Re: problem using vdblessdef file
                      rareddy

                      Ram,

                      The default encryption mechanism prior to 6.2 is more of obfuscation than encryption as the certificate used for encrypting password was common for every one. So, we decided to rip it completely in 6.2, just allow clear text passwords as default. By doing this user is no longer in the false assumption that the credentials are secure, which is wrong.

                      However, Teiid still supports encryption, but user must take additional actions before that can be enabled. See the document on encryption at

                      https://www.jboss.org/community/wiki/EncryptingpasswordsinTeiid

                      to add the encrypted passwords to your configuration. All passwords in Teiid system can be encrypted using the technique specified in the document.

                      If you are using Designer version prior to 6.2, which by default encrypts passwords, however Teiid 6.2 runtime no longer has the certificate to decrypt thus it will fail. You would manually need to update your "ConfigurationInfo.def" file either with clear text password or follow the document.

                      Hope this helps.

                      • 8. Re: problem using vdblessdef file

                        Very well explained Ramesh. Thank you so much.

                        Regards

                        Ram

                        • 9. Re: problem using vdblessdef file

                           

                          "shawkins" wrote:
                          To see all of your tables you can use the system tables:

                          select * from system.groups

                          the fullname column will contain the fully qualified table name. The table names can also be seen using any tool that reads JDBC DatabaseMetaData.

                          I'll add a JIRA/KI about importing overloaded procedures. Currently our metadata doesn't support defining overloaded procedures, but some sources do provide a unique name for each procedure signature that could be used as our import name.



                          I don't understand why the procedures are a problem. We have a procedure executeJob(Date x) in two different packages. Procedures are not being imported qualified with the package name. I think this is a problem. They are not really overloaded procedures.

                          Regards

                          Ram

                          • 10. Re: problem using vdblessdef file
                            rareddy

                            Ram,

                            This has been logged as

                            https://jira.jboss.org/jira/browse/TEIID-796

                            as Steve mentioned in his post, Teiid importer only looking at the base procedure name and not the fully qualified name with package name. Even if it did Teiid metadata does specify the storage format currently.

                            Looks like there is temporary solution looking for the unique names from the source, and next Teiid medata needs to be expanded to allow this.

                            Ramesh..

                            • 11. Re: problem using vdblessdef file

                              Ramesh/Steve,

                              I'm sorry. I didn't understand the temp solution. Is it that we just need to import only those procedures from the source which are unique from all the packages and ignore those procedures with same names??

                              Regards

                              Ram