Hi Nebojsa,
In order to achieve your objective of listing available LUN IDs for a given igroup, you would have to create a table with entries from 0-255 and then perform a diff of the lun map table against this one. Following steps will help you achieve that:
1. Login to your mysql database as the 'wfa' user. Create a table, say 'all_ids' with just a single column called 'id', in the playground database:
CREATE TABLE `all_ids` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;
2. Populate this table with values from 0 to 255. I have short procedure for this which i can share if required.
3. Use the following sql query for your user input, that will return ids in the range 0-255 which have not been used to map against that specific igroup.
SELECT avail_ids.id
FROM
playground.all_ids AS avail_ids
LEFT JOIN
(
SELECT storage.lunmap.lun_map_value
FROM
storage.lunmap JOIN storage.igroup
ON storage.lunmap.igroup_id=storage.igroup.id
WHERE
storage.igroup.name = '${igroup_name}'
) AS temp
ON avail_ids.id=temp.lun_map_value
WHERE temp.lun_map_value IS NULL
OR
avail_ids.id<>temp.lun_map_value
Note: I have made use of a variable ${igroup_name}. Please replace this with the variable you are using to identify the igroup_name.
This is a query that will work with the storage scheme. If you are working with clusters, you will have to replace the scheme names in this query to cm_storage.
Let me know if any other help with this required.
Regards,
Dhruv
