Finding Row number in SQL select results

[KevHaynes]

Right, I have a scenario where I have a schedule, say daily_0000, and later in the workflow I want to use that named schedule to find the next schedule in sequence, so daily_0005. My thought was to return the number of the schedule in my list, and then add one to it, then return the name. Now I have mySQL Workbench installed on the WFA server and have used the below to return the output with a list of numbers to be used as an index.

SET @row=0;

SELECT
@row:=@row+1 as num,
    schedule.name,
    cluster.primary_address AS 'cluster.primary_address'
FROM
    cm_storage.schedule,
    cm_storage.cluster
WHERE
    cluster.id = schedule.cluster_id
    AND  substring(schedule.name,1,11) =substring('daily_0000',1,11)
    AND  cluster.name = 'cluster1'

ORDER BY
    schedule.name

Which returns the below...

1      daily_0000      cluster1

2      daily_0005      cluster1

3      daily_0010      cluster1

4      daily_0015      cluster1

5      daily_0020      cluster1

...etc.

When I transfer the above code into WFA, it complains that the syntax is wrong. Can you not use @variables in WFA SQL lookups? If not can anyone think of a way for me to achieve what im after?

[lawrencg]

@KevHaynes

You might be able to break your workflow up and utilize the row repetition feature of WFA. Index and loop counts can be handled there. I do something similar with all nodes in a cluster during an SVM create workflow to create data LIF's on each node.

What is the goal of the workflow?

-Lawrence

[KevHaynes]

The goal is...

Create  a volume

Set the Snapshot Policy

Create the destination volume

Create the mirror

Set the Snapmirror Update to happen 5 minutes after the snapshot (our schedules set a nightly, and a weekly).

Hence wanting to search on one schedule, but return the next. I did contemplate an MVEL function for a while, but figured this way would be easier.

[sinhaa]

@KevHaynes

Thanks for bringing this unique case. This might be a bug with WFA, mysql User defined variables are not working User-Input queries. I don't know the root cause as yet.

I am trying to see if a workaround can get this through. I'll get you updated on this. 

sinhaa

[lawrencg]

@KevHaynes

There are still a few things that are slightly unlcear to me. You want to use daily_0000 for snapshot creation and daily_0005 for the SnapMirror updates? 

Pending the SQL variable workaround that @sinhaa might find, could the policy staggering be done a little differently to make the WFA logic simpler? The soultion below decouples schedule name from the schedule interval. (Assuming daily_0020 runs at 12:20am, daily_0005 runs at 12:05am

We have implemented something similar that included 100's of volumes per node.

SnapMirror relationship.

Prod    —DP—>    DR    —XDP—>    Archive 

Our requirement:

1. 15 min snapshots on source volumes.

2. 15 min SnapMirror updates to DR.

3. 15 min SnapVault update from DR to Archive.

Prod for snapshot creation will be:

  (job schedule cron show)

15min_snapshot_with_mirror_01     @:10,:25,:40,:55

15min_snapshot_with_mirror_02     @:13,:28,:43,:58

15min_snapshot_with_mirror_03     @:01,:16,:31,:46

15min_snapshot_with_mirror_04     @:04,:19,:34,:49

15min_snapshot_with_mirror_05     @:07,:22,:37,:52

DR for SnapMirror updates will be:

  (job schedule cron show)

15min_mirror_01     @:01,:16,:31,:46

15min_mirror_02     @:04,:19,:34,:49

15min_mirror_03     @:07,:22,:37,:52

15min_mirror_04     @:10,:25,:40,:55

15min_mirror_05     @:13,:28,:43,:58

Archive for SnapMirror updates will be:

  (job schedule cron show)

15min_mirror_01    @:10,:25,:40,:55

15min_mirror_02     @:13,:28,:43,:58

15min_mirror_03     @:01,:16,:31,:46

15min_mirror_04     @:04,:19,:34,:49

15min_mirror_05     @:07,:22,:37,:52

In our case, we manage each Prod->DR->Archive relationship as a set and all relationships will have the same schdule name on all clusters. A clean schedule for relationships end-to-end is the key. You might need more policies if clusters have both Prod and DR data for each other.

Example:

vol_0001_p - Snapshot Schedule -15min_snapshot_with_mirror_03

vol_0001_d - SnapMirror Schedule - 15min_mirror_03

vol_0001_a - SnapMirror Schedule - 15min_mirror_03

If one goal is to staggar the Snapshot loads on clusters and SnapMirror network throughput, in WFA, you can choose the correct policy to apply by getting volume counts for each policy on Prod and use the one with the lowest count. You could then use an MVEL function to get the "03" part of the Prod policy to use when applying the SnapMirror policy.

snapmirror-policy = "15_min_mirror_"+getSMRelationshipNumber("15min_snap_with_mirror_01")