Data Infrastructure Management Software Discussions

WFA Select only volume where there is no CIFS share

masson

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.

2 REPLIES 2

Re: WFA Select only volume where there is no CIFS share

coreywanless

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

Re: WFA Select only volume where there is no CIFS share

masson

Thanks Coreywanless,

 

It works very quicky

 

Best regards.

Earn Rewards for Your Review!
GPI Review Banner
All Community Forums
Public