Active IQ Unified Manager Discussions

WFA Select only volume where there is no CIFS share

masson
2,999 Views

Hello Team,

 

I'm trying to create a workflow where i have to select in a particular SVM only volume without any CIFS share.

I've create the following SQL Query:

SELECT
        volume.name as volname
FROM
        cm_storage.volume as volume,
        cm_storage.vserver as vserver,
        cm_storage.cluster as cluster
WHERE
        cluster.name='${ClusterName}'
    AND vserver.name='${VserverName}'
    AND volume.vserver_id=vserver.id
    AND volume.name LIKE '%vol_${VserverName}_w%'
    AND volume.name NOT LIKE '%w00%'
    AND volume.name NOT IN
(SELECT DISTINCT
    substr(cifs_share.path,2,17) as volumeName
FROM
    cm_storage.cifs_share as cifs_share,
    cm_storage.cluster as cluster,
    cm_storage.vserver as vserver
WHERE
    cluster.name='${ClusterName}'
    AND vserver.name='${VserverName}'
    AND cifs_share.path LIKE '%vol_${VserverName}_w%')
   

 

I know that using NOT IN is not recommended and indeed this query is a bit slow.

 

Do you have any idea to rewrite this query with something more efficient.

 

Regards.

1 ACCEPTED SOLUTION

coreywanless
2,988 Views

I love doing joins. Here is my rendition:

 

select
cluster.name as cluster,
vserver.name as vserver,
volume.name as volume
from
cm_storage.volume
left join cm_storage.vserver on vserver.id = volume.vserver_id
left join cm_storage.cluster on cluster.id = vserver.cluster_id
left join cm_storage.cifs_share on cifs_share.path like concat(volume.junction_path,'%')
where cifs_share.id is null

 

NOTE: Append any where statements you need

View solution in original post

2 REPLIES 2

coreywanless
2,989 Views

I love doing joins. Here is my rendition:

 

select
cluster.name as cluster,
vserver.name as vserver,
volume.name as volume
from
cm_storage.volume
left join cm_storage.vserver on vserver.id = volume.vserver_id
left join cm_storage.cluster on cluster.id = vserver.cluster_id
left join cm_storage.cifs_share on cifs_share.path like concat(volume.junction_path,'%')
where cifs_share.id is null

 

NOTE: Append any where statements you need

masson
2,937 Views

Thanks Coreywanless,

 

It works very quicky

 

Best regards.

Public