How to exclude loading of SQLserver sys* tables?

[m.ardito]

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

[rareddy]

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?

[shawkins]

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.

[m.ardito]

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.

[shawkins]

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.

[m.ardito]

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

[shawkins]

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