Teiid 8.1 Query Hangs
I have a query that hangs when the return value is the lowest value from the table. Overall it is a bit complicated so let me explain.
There is a healthstate table with 4 rows and 3 columns. columns are name, severity, color. Severity is a value of 1,2,3,10.
There is a policy table that has a nubmer of rows with a ruletype column that matches the name column of the health state table. There is a rulename column that matches the below state columns.
There is a generated table with random numbers for columns that provide the value to be used. i.e. there is a cpubusy column that has a percent value, the policy table will have a cpubusy row with a compare value.
Here is the query:
select a.timeevent as HEALTHTIME,
CASE when a.cpubusy > j.rulevalue then (select h.severity from demo_policy_metadata.chorus_health_state h where h.name = j.ruletype) else 3 end as HEALTHSTATEFKEY,
primary_key('demo.demo_lpar_health_state', 'timeevent',a.timeevent, 'sysplex',a.sysplex, 'sysid',a.sysid) as OBJFKEYXML,
primary_key('demo_policy_metadata.demoLparPolicy','rulename',j.rulename, 'ruletype', j.ruletype) as POLICYFKEYXML
from demo_policy_metadata.demoLparPolicy j, demo.demo_lpar_health_state a
where j.comparefield = 'cpubusy'
The select for the CASE statement should return a value from the health state. The value should be either a 1 or a 2.
This query works when the value is 2; or if I add 'and' clauses to reduce the result set to a single row. i.e. "and j.ruletype = 'Danger' and a.sysid = 'USILCA31'"
or if i remove the 'primary_key' columns.
With the query as is and a return of a '1' (lowest value from table) from the select in the CASE - the query just hangs.
The 'primary_key()' returns an XML string built from the passed in values. nothing overly exciting, just consistent XML.
I have a log with debug and a heap dump for review.
In the file 'server.log' line 3826 is where the debug for the query starts. line 4390 is where it seems to hang.
The dump is 450mb and will not upload. Is there an alternative process?
server.log.zip 47.5 KB