I've been fiddling with this and have come to the conclusion there is a quirk in WFA or something I don't understand (or both). I am not a SQL expert but composed this SQL by referencing similar situations and how problems like this are solved in SQL. I've narrowed down my problem into a simple workflow (attached) that illustrates the problem.
BACKGROUND:
- 7-mode exmaple
- Want to present a list of volumes with checkboxes (will be the list of volumes to potentially create SnapMirrors for)
- Normally want to hide volumes which are already involved in a SnapMirror relationship (as either a source or destination volume)
- Optionally want to expose and show all the volumes regardless if they are in a SM relatonship
- I don't want to create separate UI fields, one for each case, but want to have a UI variable (boolean checkbox preferred) that controls what volumes are presented
I've come up with SQL below to accomplish this. It WORKS perfectly when I 'Test' it inside Designer for that UI field. It supresses volumes that are either source volumes or destination volumes and shows them when it should (i.e.; when I pass a value of 1 anyway)
At first, when I was testing it, I wasn't exactly sure if what I should type for the boolean test parameter would be true/false or 1/0. After experimenting what I found was entering 1 for true and 0 for false works and true or false were not working. This makes sense from a CompSci 101 perspective and I presume that is the only way to test a boolean this under WFA.
However, I'm hitting two problems:
- When Previewing or Executing the workflow, the query doesn't work at all. It always shows all volumes. I'm presuming there is some difference in how the flag parameter is passed when in Test mode versus real preview/execute. As a comprmise, which we don't like, I shifted to make the field an Enum with Yes/No values and got it working. But we'd really like to get an explaination, workaround or fix as checkbox are used elsewhere and familar to operators
- We 'like' this to work under WFA 2.2 (the version in production-env for next few months) but find that checking and unchecking the flag or making new array selections will eventually cause the WFA 2.2 UI to hang. I have not seen this behavior under WFA 3.1P2 and so would be interested to learn if it is just a 'known' wfa-2.2 issue or something about my SQL code kicks wfa-2.2 the wrong way. If the best we can get is a working boolean flag that only works with wfa-3.1p2 or later that is probably fine.
So, the attached workflow (.txt appended to .dar) is exported from a wfa-2.2 server and hence should import into wfa-2.2 or wfa-3.1p2. The first priority is to see this working under wfa-3.1p2. Second priority would be consistent behavior under wfa-2.2 but I'm suspecting an old-bug there.
NOTE: === FWIW: HERE IS THE SQL USED IN THE ATTACHED WORKFLOW === MUCH EASIER TO READ (w/indents) WITHIN THE WORKFLOW DESIGNER ===
SELECT
volume.name
FROM
storage.volume,
storage.array
WHERE
volume.name != 'vol0'
AND volume.array_id = array.id
AND array.name = '${priArray}'
AND ( -- AND ( ( flag is not true ) OR ( vol.id is NOT IN src.vsm AND vol.id NOT IN dst.vsm ) )
(
'${flagHideVsmVolumes}' IS NOT TRUE
)
OR (
volume.id NOT IN (
SELECT
vsm.volume_id
FROM
storage.vsm
)
AND volume.id NOT IN (
SELECT
vsm.secondary_volume_id
FROM
storage.vsm
)
)
)
