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

    How to exclude loading of SQLserver sys* tables?

    m.ardito
    m.ardito Nov 15, 2016 5:27 AM

      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

      • 235 Views
      • Tags:
        • 1. Re: How to exclude loading of SQLserver sys* tables?
          rareddy
          rareddy Nov 15, 2016 9:04 AM (in response to m.ardito)

          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?

            • Actions
          • 2. Re: How to exclude loading of SQLserver sys* tables?
            shawkins
            shawkins Nov 15, 2016 9:10 AM (in response to rareddy)

            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.

              • Actions
            • 3. Re: How to exclude loading of SQLserver sys* tables?
              m.ardito
              m.ardito Nov 15, 2016 9:25 AM (in response to shawkins)

              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.

                • Actions
              • 4. Re: How to exclude loading of SQLserver sys* tables?
                shawkins
                shawkins Nov 15, 2016 2:05 PM (in response to m.ardito)

                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.

                  • Actions
                • 5. Re: How to exclude loading of SQLserver sys* tables?
                  m.ardito
                  m.ardito Nov 16, 2016 5:18 AM (in response to shawkins)

                  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
                    • Actions
                  • 6. Re: How to exclude loading of SQLserver sys* tables?
                    shawkins
                    shawkins Nov 16, 2016 8:18 AM (in response to m.ardito)

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

                      • Actions