-
1. Re: How to exclude loading of SQLserver sys* tables?
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?
-
2. Re: How to exclude loading of SQLserver sys* tables?
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.
-
3. Re: How to exclude loading of SQLserver sys* tables?
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.
-
4. Re: How to exclude loading of SQLserver sys* tables?
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.
-
5. Re: How to exclude loading of SQLserver sys* tables?
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
-
6. Re: How to exclude loading of SQLserver sys* tables?
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.