I was asked to show how SQL could be used to identify when IBM i NetServer is sharing IFS paths, but some of those paths are unavailable.

NetServer Shares and IFS path availability.sql

--
-- How do you determine whether IFS paths being shared via IBM� i NetServer are available or unavailable?
-- ======================================================================================================
--
--
-- IBM� i NetServer shares - Unavailable IFS share detail
--
select SERVER_SHARE_NAME, PATH_NAME, 'Unavailable' as Share_availability, TEXT_DESCRIPTION
  from qsys2.server_share_info
  where share_type = 'FILE' and
        0 = (select count(*)
            from table (
                QSYS2.IFS_OBJECT_references_INFO(PATH_NAME => path_name, IGNORE_ERRORS => 'YES')
              ));
stop;

--
-- IBM� i NetServer shares - IFS stream files being shared
--
select server_share_name, path_name, permissions
  from qsys2.server_share_info
  where share_type = 'FILE';

--
-- IBM� i NetServer shares - Raw count of IFS paths being shared
--
select count(*) as IFS_path_count
  from qsys2.server_share_info
  where share_type = 'FILE';

--
-- IBM� i NetServer shares - Available IFS share count
--
select count(*) as Available_IFS_path_count
  from qsys2.server_share_info
  where share_type = 'FILE' and
        1 = (select count(*)
            from table (
                QSYS2.IFS_OBJECT_references_INFO(PATH_NAME => path_name, IGNORE_ERRORS => 'YES')
              ));
              
--
-- IBM� i NetServer shares - Unavailable IFS share count
--
select count(*) as Unavailable_IFS_path_count
  from qsys2.server_share_info
  where share_type = 'FILE' and
        0 = (select count(*)
            from table (
                QSYS2.IFS_OBJECT_references_INFO(PATH_NAME => path_name, IGNORE_ERRORS => 'YES')
              ));