4 Replies Latest reply on Dec 18, 2012 9:32 AM by Steven Hawkins

    Cannot find materialized table when importing a VDB

    Samier Nadji Newbie

      Hi,

       

      I have the following scenario:

      vdb-a contains a view that has a materialized table. The physical datasource is defined in vdb-a.

      vdb-b imports vdb-a

       

       

      vdb-a deploys successfully. I can see that it gets set to ACTIVE.

      However, when I try to deploy vdb-b, I get the following errors:

       

      09:39:06,895 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4) TEIID31090 Materialization table dbo.MV_MY_MAT_VIEW not found in Schema MyCacheDb for view MY_MAT_VIEW

      09:39:06,897 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4) TEIID31073 Invalid functions; Translator metadata load SdmDataSource []

      09:39:06,899 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40073 The metadata for the VDB vdb-b.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

      09:39:06,900 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 4) TEIID40003 VDB vdb-b.1 is set to FAILED

      09:39:06,961 INFO  [org.jboss.as.server] (HttpManagementService-threads - 5) JBAS018559: Deployed "vdb-b-vdb.xml"

       

      I don't think I need to add anything to vdb-b to resolve the materialized views in vdb-a, do I?

       

      Thanks

        • 1. Re: Cannot find materialized table when importing a VDB
          Steven Hawkins Master

          > I don't think I need to add anything to vdb-b to resolve the materialized views in vdb-a, do I?

           

          No, you should not.  I don't see a good reason why vdb-a would work and vdb-b would not either.  What version are you using?  What schemas are in vdb-a and vdb-b?

           

          Steve

          • 2. Re: Cannot find materialized table when importing a VDB
            Samier Nadji Newbie

            We are using Teiid 8.2.

             

            I was able reproduce the problem with very basic VDBs/schemas.

             

            The details:

            • Datasources (source and cache) are on MSSQL 2008 R2
            • The source DB (DB1)  has a simple table (dbo.Table1) that has 2 columns
            • The cache table is on the same system but a different database (TeiidCacheDb).

             

            These are my VDBs:

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

            <vdb name="vdb-a" version="1">

                <property name="UseConnectorMetadata" value="cached" />  

                <model visible="false" type="PHYSICAL" name="DS1">

                    <property name="importer.useFullSchemaName" value="true"/>

                    <source name="DS1" translator-name="sqlserver" connection-jndi-name="java:/DS1"/>

                </model>

                <model visible="false" type="PHYSICAL" name="TeiidCacheDb">

                    <property name="importer.useFullSchemaName" value="true"/>

                    <source name="TeiidCacheDb" translator-name="sqlserver" connection-jndi-name="java:/TeiidCacheDb"/>

                </model>

                <model visible="true" type="VIRTUAL" name="view1">

                     <metadata type="DDL"><![CDATA[               

                        CREATE VIEW v1 (

                            col1    string,

                            col2    integer 

                        )

                        OPTIONS (MATERIALIZED true, MATERIALIZED_TABLE 'TeiidCacheDb.TeiidCacheDb.dbo.MV_v1')  

                        AS  select

                            t1.Column1  AS col1,

                            t1.Column2  AS col2               

                            FROM

                                DS1.DB1.dbo.Table1 t1

                    ]]> </metadata>   

                </model>

            </vdb>

             

             

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

            <vdb name="vdb-b" version="1">   

                <property name="UseConnectorMetadata" value="cached" />  

                <import-vdb name="vdb-a" version="1" import-data-policies="false"/>

                <model visible="false" type="VIRTUAL" name="hidden">

                     <metadata type="DDL"><![CDATA[               

                        CREATE VIEW v2 (

                            col1    string

                        )

                        AS  select

                            t1.Column1  AS col1

                            FROM

                                DS1.DB1.dbo.Table1 t1

                    ]]> </metadata>   

                </model>

            </vdb>

             

            One interesting observation: when I create the "cache" datasource with a name different than the DB (i.e. called datasouce CacheDS instead of TeiidCacheDb), I got a different error:

            15:22:52,426 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2) TEIID31089 Invalid Schema qualifier TeiidCacheDb is specified on materialization table TeiidCacheDb.dbo.MV_v1 for view v1

             

            When the cache datasouce name matches the DB, i get this error:

            15:43:42,788 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 4) TEIID31090 Materialization table dbo.MV_v1 not found in Schema TeiidCacheDb for view v1

            • 3. Re: Cannot find materialized table when importing a VDB
              Ramesh Reddy Master

              Samier,

               

              I replicated the issue, looks like Teiid is not geting fully qualifying name for materialized table validation. You can log JIRA for this bug.

               

              Thanks

               

              Ramesh..