WFA sub-select in update query for reservations

moep — Wed, 04 Jun 2025 13:33:55 GMT

I am still working on reservations for export rules. My issue is that rules get different RuleIndex value if new rules are inserted "on top".

Example Policy:

             Policy          Rule    Access   Client                RO
Vserver      Name            Index   Protocol Match                 Rule
------------ --------------- ------ -------- --------------------- ---------
test
             esx             1       nfs      10.0.0.1              sys
test
             esx             2       nfs      10.0.0.2              sys
test
             esx             3       nfs      10.0.0.3              sys

I add a new client at Index 1, other rules get "pushed down".

             Policy          Rule    Access   Client                RO
Vserver      Name            Index   Protocol Match                 Rule
------------ --------------- ------ -------- --------------------- ---------
test
             esx             1       nfs      10.0.0.4              sys
test
             esx             2       nfs      10.0.0.1              sys
test
             esx             3       nfs      10.0.0.2              sys
test
             esx             4       nfs      10.0.0.3              sys

I wanted to replicate this behaviour with my reservation script but failed. It seems the reservation doesn't support a sub-select in the update statement.

Here is my SQL Statement for the reservation:

UPDATE
    cm_storage.export_rule rule
JOIN
    cm_storage.export_policy policy
        ON policy.id = rule.policy_id
SET
    rule.rule_index = rule.rule_index + 1
WHERE
    rule.rule_index >= '${RuleIndex}'
    AND policy.name = '${PolicyName}'
    AND (
        SELECT
            cm_storage.export_rule.rule_index
        FROM
            cm_storage.export_rule
        JOIN
            cm_storage.export_policy
                ON cm_storage.export_rule.policy_id = cm_storage.export_policy.id
        JOIN
            cm_storage.vserver
                ON cm_storage.export_policy.vserver_id = cm_storage.vserver.id
        WHERE
            cm_storage.export_policy.name = '${VolumeName}_${QtreeName}'
            AND cm_storage.vserver.name = '${VserverName}'
            AND cm_storage.export_rule.rule_index = '${RuleIndex}'
    ) is not null;

Has anyone an idea how to solve that?

topic WFA sub-select in update query for reservations in Active IQ Unified Manager Discussions

WFA sub-select in update query for reservations