6 Replies Latest reply on Nov 16, 2016 8:18 AM by Steven Hawkins

    How to exclude loading of SQLserver sys* tables?

    Marco Ardito Master

      I've read about the "excludeTables" importer property in JDBC Translators · Teiid Documentation

       

      But using something like this doesn't work, all sys* tables are imported as well

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <vdb name="vendite" version="1">
          <description>vendite</description>
          <property name="UseConnectorMetadata" value="true" />        
          <model name="vendite">
              <property name="importer.useFullSchemaName" value="true"/>
                      <property name="importer.excludeTables" value="*sys*"/>  
              <source name="sqlserver_vendite" translator-name="sqlserver" connection-jndi-name="java:/vendite"/>
          </model>
      </vdb>
      

       

      instead of "*sys*" I also tried something like  "vendite.sqldbname.dbo.sys*" but it doesn't exclude sys* tables, too.

       

      jdbc DS is a sql server through jtds 1.3.0 on teiid 9.02, atm.

       

      AM I missing sometyhing? What should I do?

      Marco

        • 1. Re: How to exclude loading of SQLserver sys* tables?
          Ramesh Reddy Master

          excludeTables is a regular expression against fully qualified name of your table. I found the following code

           

          String fullName = getFullyQualifiedName(tableCatalog, tableSchema, tableName);
          if (shouldExclude(fullName)) {
            excludedTables++;
            continue;
          }
          

           

          What you supplied looks fine to me, may be need to write small test against your tale names to see why it is not matching?

          • 2. Re: How to exclude loading of SQLserver sys* tables?
            Steven Hawkins Master

            That's actually not a valid regular expression.  You probably mean something like ".*sys.*".  I would have expected an exception to be through though with what is shown above.

            • 3. Re: How to exclude loading of SQLserver sys* tables?
              Marco Ardito Master

              Steven Hawkins ha scritto:

              That's actually not a valid regular expression. You probably mean something like ".*sys.*". I would have expected an exception to be through though with what is shown above.

               

              your expression works... I didn't understand those were perl-like regexp...  no exception, though with the "wrong" expressions...

               

              Thanks, Marco.

              • 4. Re: How to exclude loading of SQLserver sys* tables?
                Steven Hawkins Master

                You should see something in the logs like:

                 

                "Dangling meta character '*' near index 0"

                 

                Otherwise it doesn't seem like the setting was initially being used.

                • 5. Re: How to exclude loading of SQLserver sys* tables?
                  Marco Ardito Master

                  well, honestly I wasn't observing the logs, since it ususally loaded nicely, but without excluding tables...

                   

                  Just to make you sure that what should be logged IS logged I did some more test.

                   

                  Looking in the logs I can find this, that evidently I also tried the other day...:

                   

                  2016-11-14 16:15:41,179 WARN  [org.teiid.RUNTIME] (Worker11_async-teiid-threads19)  TEIID50036 VDB CSPMI_vendite.1 model "CSPMI_vendite" metadata failed to load. 
                  Reason:TEIID10044 Property 'importer.ExcludeTables' with value '*.sys*' is not a valid String.: org.teiid.core.util.PropertiesUtils$InvalidPropertyException: TEIID10044 Property 'importer.ExcludeTables' with value '*.sys*' is not a valid String.
                  *.sys*
                  

                   

                  And I tried again on a test vdb now, using an expression like "vdbname.sqldbname.dbo.sys*", as I did the other day, and this is what is logged about disabling and then enabling again the vdb (vdbname is "CSPMI_vendite").

                  Nothing seems to be logged...

                   

                  2016-11-16 11:03:03,335 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-4)  TEIID40120 VDB CSPMI_vendite.1 will be removed from the repository
                  2016-11-16 11:03:03,340 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-4)  TEIID40119 VDB CSPMI_vendite.1 removed from the repository
                  2016-11-16 11:03:03,343 INFO  [org.teiid.RUNTIME] (MSC service thread 1-4)  TEIID50026 VDB "CSPMI_vendite.1[CSPMI_vendite{sqlserver_CSPMI_vendite=sqlserver_CSPMI_vendite, sqlserver, java:/CSPMI_vendite}]" undeployed.
                  2016-11-16 11:03:03,348 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-4)  TEIID40120 VDB CSPMI_vendite.1 will be removed from the repository
                  2016-11-16 11:03:03,361 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-5)  WFLYSRV0028: Stopped deployment cspmi_test-vdb.xml (runtime-name: cspmi_test-vdb.xml) in 27ms
                  2016-11-16 11:03:03,391 INFO  [org.jboss.as.server] (XNIO-1 task-9)  WFLYSRV0009: Undeployed "cspmi_test-vdb.xml" (runtime-name: "cspmi_test-vdb.xml")
                  2016-11-16 11:03:18,074 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-1)  WFLYSRV0027: Starting deployment of "cspmi_test-vdb.xml" (runtime-name: "cspmi_test-vdb.xml")
                  2016-11-16 11:03:18,112 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-3)  TEIID40118 VDB CSPMI_vendite.1 added to the repository
                  2016-11-16 11:03:18,115 INFO  [org.teiid.RUNTIME] (MSC service thread 1-3)  TEIID50029 VDB CSPMI_vendite.1 model "CSPMI_vendite" metadata is currently being loaded. Start Time: 16/11/16 11.03
                  2016-11-16 11:03:18,120 INFO  [org.teiid.CONNECTOR] (Worker25_async-teiid-threads37)  SQLServerExecutionFactory Commit=true;DatabaseProductName=Microsoft SQL Server...
                  2016-11-16 11:03:18,145 INFO  [org.jboss.as.server] (XNIO-1 task-7)  WFLYSRV0010: Deployed "cspmi_test-vdb.xml" (runtime-name : "cspmi_test-vdb.xml")
                  2016-11-16 11:03:18,860 INFO  [org.teiid.RUNTIME] (Worker25_async-teiid-threads37)  TEIID50030 VDB CSPMI_vendite.1 model "CSPMI_vendite" metadata loaded. End Time: 16/11/16 11.03
                  2016-11-16 11:03:18,865 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker25_async-teiid-threads37)  TEIID40003 VDB CSPMI_vendite.1 is set to ACTIVE
                  
                  

                   

                  Also using an expression like "*sys*", I tried again and found this in the log:

                   

                  2016-11-16 11:09:34,432 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-6)  TEIID40120 VDB CSPMI_vendite.1 will be removed from the repository
                  2016-11-16 11:09:34,436 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-6)  TEIID40119 VDB CSPMI_vendite.1 removed from the repository
                  2016-11-16 11:09:34,440 INFO  [org.teiid.RUNTIME] (MSC service thread 1-6)  TEIID50026 VDB "CSPMI_vendite.1[CSPMI_vendite{sqlserver_CSPMI_vendite=sqlserver_CSPMI_vendite, sqlserver, java:/CSPMI_vendite}]" undeployed.
                  2016-11-16 11:09:34,446 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-2)  TEIID40120 VDB CSPMI_vendite.1 will be removed from the repository
                  2016-11-16 11:09:34,456 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-3)  WFLYSRV0028: Stopped deployment cspmi_test-vdb.xml (runtime-name: cspmi_test-vdb.xml) in 26ms
                  2016-11-16 11:09:34,489 INFO  [org.jboss.as.server] (XNIO-1 task-4)  WFLYSRV0009: Undeployed "cspmi_test-vdb.xml" (runtime-name: "cspmi_test-vdb.xml")
                  2016-11-16 11:09:38,355 INFO  [org.jboss.as.server.deployment] (MSC service thread 1-5)  WFLYSRV0027: Starting deployment of "cspmi_test-vdb.xml" (runtime-name: "cspmi_test-vdb.xml")
                  2016-11-16 11:09:38,394 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-7)  TEIID40118 VDB CSPMI_vendite.1 added to the repository
                  2016-11-16 11:09:38,398 INFO  [org.teiid.RUNTIME] (MSC service thread 1-7)  TEIID50029 VDB CSPMI_vendite.1 model "CSPMI_vendite" metadata is currently being loaded. Start Time: 16/11/16 11.09
                  2016-11-16 11:09:38,403 INFO  [org.teiid.CONNECTOR] (Worker26_async-teiid-threads38)  SQLServerExecutionFactory Commit=true;DatabaseProductName=Microsoft SQL Server...
                  2016-11-16 11:09:38,429 INFO  [org.jboss.as.server] (XNIO-1 task-2)  WFLYSRV0010: Deployed "cspmi_test-vdb.xml" (runtime-name : "cspmi_test-vdb.xml")
                  2016-11-16 11:09:40,085 INFO  [org.teiid.RUNTIME] (Worker26_async-teiid-threads38)  TEIID50030 VDB CSPMI_vendite.1 model "CSPMI_vendite" metadata loaded. End Time: 16/11/16 11.09
                  2016-11-16 11:09:40,090 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (Worker26_async-teiid-threads38)  TEIID40003 VDB CSPMI_vendite.1 is set to ACTIVE
                  2
                  

                   

                  2016-11-16 11:09:34,432 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-6)  TEIID40120 VDB CSPMI_vendite.1 will be removed from the repository

                  • 6. Re: How to exclude loading of SQLserver sys* tables?
                    Steven Hawkins Master

                    *.sys* - fails as expected, the leading * is not valid

                    vdbname.sqldbname.dbo.sys* - is actually a valid regular expression.  It is looking for that string with 0 or more s characters as the end

                    *sys* - should fail as well for the same reason as the first one.  I can confirm this locally.