3:23 p.m.
[
https://issues.jboss.org/browse/TEIID-3189?page=com.atlassian.jira.plugin...
]
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)
3721
days inactive
3721
days old
0 comments
1 participants
participants (1)
-
Van Halbert (JIRA)