Max Flexvol volume filter for aggregates in Cluster mode

[rogerhinson]

Hey all,

I found that there is a certified Max Flexvol filter for aggregates in 7-Mode, but not for Clustered Mode.

I did some tweaking on the 7-Mode filter and came up with this.

SELECT
    aggr.name AS 'name',
    nodeName AS 'nodeName',
    nodeIP AS 'anotherIP',
    matching_arrays.nodeIp AS 'node.ip'
FROM
    cm_storage.aggregate AS aggr,
    ( SELECT
  node.id AS nodeId,
  node.name AS nodeName,
  node.primary_address AS nodeIp
FROM
  cm_storage.node
LEFT JOIN
  cm_storage.aggregate AS aggr
    ON aggr.node_id = node.id
LEFT JOIN
  cm_storage.volume AS vol
    ON vol.aggregate_id = aggr.id
    AND vol.type = 'Flexible'
GROUP BY
  vol.aggregate_id
HAVING
  COUNT(vol.id) <= ${max_flex_vol} ) AS matching_arrays
WHERE
    aggr.node_id = matching_arrays.nodeId

It works when I use Toad on the DMF database.  When I bring it into WFA and test it, I get the below error.

Filter 'Filter aggregates by max flexvols_copy' returned attributes does not contain all natural keys.
Filter's returned attributes '[anotherIP, name, node.ip, nodeName]' ,
dictionary entry natural keys '[node.name, name, node.cluster.primary_address]'

Can anyone help out?

Thanks,

Roger

[geringer]

Roger,

   It looks like WFA is checking for valid return variables.  If you look in the filter editor, it looks like you are trying to return 4 variables.  The last one "matching_arrays.nodeIp" does not exist in the cm_storage dictionary, and it is not defined as a variable in your query.  I think you may have a typo in the last line of your query, you type nodeId, not nodeIp.  In any case, I am not sure why you need to return anything other than the aggr.name and the node.name or node.primary_address.  So I would remove the last two lines of the select and try that.  You may want to look at the query and return the total remaining volume counts for the nodes with each aggregate if you intend to sort the results by aggregates with the largest number of available volumes.  I am on the train, and do not have reliable access to Lab on Demand, so I can not test my theory at this time.  If you need me to tweek the query and test it, I can do that first thing tomorrow morning.  Let me know.

Mike

[rogerhinson]

Hey Mike,

This is pretty much a copy of the 7-Mode filter to filter out aggregates with too many volumes.  We only use one aggregate per node (not counting aggr0), so it functions as a filter for too many volumes on a node.  It took me a little bit looking through it, but there are actually two SELECT statements.  The second one is part of the FROM in the first SELECT statement. 

The second SELECT statement result from within the FROM statement is assigned to the AS matching_arrays.  That's where the matching_arrays.nodeIp comes from.

I tried remove the last two lines of the first SELECT statement as received the same error about the natual keys.

If you could run it the lab, that would be great.  It works as I expect when I run it from Toad on the WFA Windows server directly.

Thanks,

Roger

[sinhaa · Accepted solution]

Roger,

If you read the part of the error message:

===

Filter's returned attributes '[anotherIP, name, node.ip, nodeName]' ,
dictionary entry natural keys '[node.name, name, node.cluster.primary_address]'

===

In your query you are selecting [anotherIP, name, node.ip, nodeName] . But the WFA filter needs at least all these: [node.name, name, node.cluster.primary_address] along with whatever you want. So your query will have to return values with heading i.e. AS node.name, name, node.cluster.primary_address along with anything else.

sinhaa

[rogerhinson]

Thanks..  That did it.  It's odd that sometimes the AS will auto fill in and other times, you need to mistype it and then come back and correct it.  I made a small tweak so that it actually filters by the node rather than the aggregate. 

Here it is, in case it helps anyone else.

SELECT
    aggr.name AS 'name',
    nodeName AS 'node.name',
    matching_arrays.nodeIp AS 'node.cluster.primary_address'
FROM
    cm_storage.aggregate AS aggr,
    (
    SELECT
        node.id AS nodeId,
        node.name AS nodeName,
        node.primary_address AS nodeIp
    FROM
        cm_storage.node
    LEFT JOIN
        cm_storage.aggregate AS aggr
            ON aggr.node_id = node.id
    LEFT JOIN
        cm_storage.volume AS vol
            ON vol.aggregate_id = aggr.id
            AND vol.type = 'Flexible'
    GROUP BY
        node.id
    HAVING
        COUNT(vol.id) <= ${max_flex_vol} ) AS matching_arrays
WHERE
    aggr.node_id = matching_arrays.nodeId