[teiid-issues] [JBoss JIRA] (TEIID-3189) Defined materialization in the dynamic VDB fails to deploy
Van Halbert (JIRA)
issues at jboss.org
Mon Oct 27 10:23:36 EDT 2014
[ https://issues.jboss.org/browse/TEIID-3189?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13015285#comment-13015285 ]
Van Halbert edited comment on TEIID-3189 at 10/27/14 10:23 AM:
---------------------------------------------------------------
I replaced "teiid_rel:MATVIEW_TTL" 120000, with a cache hint with the following:
{code}
CREATE view stockPricesMatView
(
product_id integer,
symbol string,
price bigdecimal,
company_name varchar(256)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'Accounts.h2_stock_mat',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table h2_stock_mat'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute accounts.native('''')',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''stock'' AND schemaname = ''Stocks''',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'Accounts.h2_stock_mat',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'status',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')
AS /*+ cache(ttl:180000)*/ SELECT A.ID, S.symbol, S.price, A.COMPANY_NAME
FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A
WHERE S.symbol = A.SYMBOL;
{code}
And got the following message:
09:19:31,364 WARN [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID50036 VDB Portfolio.1 model "StocksMatModel" metadata failed to load. Reason:TEIID31154 One or more of required properties for materialization management is missing. Required properties are teiid_rel:MATVIEW_STATUS_TABLE, teiid_rel:MATERIALIZED_STAGE_TABLE if teiid_rel:MATVIEW_LOAD_SCRIPT is not provided, teiid_rel:MATVIEW_ONERROR_ACTION and teiid_rel:MATVIEW_TTL or Cache Hint in query.
was (Author: van.halbert):
I replaced "teiid_rel:MATVIEW_TTL" 120000, with a cache hint with the following:
CREATE view stockPricesMatView
(
product_id integer,
symbol string,
price bigdecimal,
company_name varchar(256)
) OPTIONS (MATERIALIZED 'TRUE', UPDATABLE 'TRUE',
MATERIALIZED_TABLE 'Accounts.h2_stock_mat',
"teiid_rel:MATVIEW_BEFORE_LOAD_SCRIPT" 'execute accounts.native(''truncate table h2_stock_mat'');',
"teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" 'execute accounts.native('''')',
"teiid_rel:ON_VDB_DROP_SCRIPT" 'DELETE FROM Accounts.status WHERE Name=''stock'' AND schemaname = ''Stocks''',
"teiid_rel:MATERIALIZED_STAGE_TABLE" 'Accounts.h2_stock_mat',
"teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true',
"teiid_rel:MATVIEW_STATUS_TABLE" 'status',
"teiid_rel:MATVIEW_SHARE_SCOPE" 'NONE',
"teiid_rel:MATVIEW_ONERROR_ACTION" 'THROW_EXCEPTION')
AS /*+ cache(ttl:180000)*/ SELECT A.ID, S.symbol, S.price, A.COMPANY_NAME
FROM Stocks.StockPrices AS S, Accounts.PRODUCT AS A
WHERE S.symbol = A.SYMBOL;
And got the following message:
09:19:31,364 WARN [org.teiid.RUNTIME] (teiid-async-threads - 2) TEIID50036 VDB Portfolio.1 model "StocksMatModel" metadata failed to load. Reason:TEIID31154 One or more of required properties for materialization management is missing. Required properties are teiid_rel:MATVIEW_STATUS_TABLE, teiid_rel:MATERIALIZED_STAGE_TABLE if teiid_rel:MATVIEW_LOAD_SCRIPT is not provided, teiid_rel:MATVIEW_ONERROR_ACTION and teiid_rel:MATVIEW_TTL or Cache Hint in query.
> Defined materialization in the dynamic VDB fails to deploy
> ----------------------------------------------------------
>
> Key: TEIID-3189
> URL: https://issues.jboss.org/browse/TEIID-3189
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1
> Reporter: Van Halbert
> Assignee: Steven Hawkins
> Attachments: customer-schema.sql, portfolio-vdb.xml
>
>
> The attached dynamic VDB that's in the dynamicvdb-data-federation quick start, fails to deploy based on the defined materialization (remove it and the VDB deploys fine):
> 15:33:39,870 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue12) TEIID30020 Processing exception for request Za7hE/1FAkqH.0 'TEIID30168 Couldn't execute the dynamic SQL command "EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1" with the SQL statement "(updateStmt || ' AND loadNumber = DVARS.loadNumber')" due to: TEIID30082 Cannot set symbol 'LoadNumber' with expected type integer to expression 'DVARS.LoadNumber''. Originally QueryProcessingException 'TEIID30082 Expected value of type 'integer' but 'DVARS.LoadNumber' is of type 'long' and no implicit conversion is available.' ResolverUtil.java:217. Enable more detailed logging to see the entire stacktrace.
> 15:33:39,872 WARN [org.teiid.PROCESSOR.MATVIEWS] (teiid-async-threads - 3) TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1\" with the SQL statement \"(updateStmt || ' AND loadNumber = DVARS.loadNumber')\" due to: TEIID30082 Cannot set symbol 'LoadNumber' with expected type integer to expression 'DVARS.LoadNumber'" ]: org.teiid.jdbc.TeiidSQLException: TEIID50100 Trigger execution result: org.jboss.as.controller.OperationFailedException [ "TEIID30168 org.teiid.api.exception.query.QueryProcessingException: TEIID30168 Couldn't execute the dynamic SQL command \"EXECUTE IMMEDIATE (updateStmt || ' AND loadNumber = DVARS.loadNumber') USING loadNumber = VARIABLES.loadNumber, vdbName = VARIABLES.vdbName, vdbVersion = VARIABLES.vdbVersion, schemaName = schemaName, viewName = viewName, updated = now(), LoadState = 'FAILED_LOAD', valid = (VARIABLES.valid) AND (NOT (invalidate)), cardinality = -1\" with the SQL statement \"(updateStmt || ' AND loadNumber = DVARS.loadNumber')\" due to: TEIID30082 Cannot set symbol 'LoadNumber' with expected type integer to expression 'DVARS.LoadNumber'" ]
> at org.teiid.jboss.MaterializationManagementService$1.executeQuery(MaterializationManagementService.java:104) [teiid-jboss-integration-8.7.1.redhat-4.jar:8.7.1.redhat-4]
> at org.teiid.runtime.MaterializationManager$QueryJob$1.run(MaterializationManager.java:267) [teiid-runtime-8.7.1.redhat-4.jar:8.7.1.redhat-4]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_55]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_55]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_55]
> at org.jboss.threads.JBossThread.run(JBossThread.java:122)
--
This message was sent by Atlassian JIRA
(v6.3.1#6329)
More information about the teiid-issues
mailing list