sql to list just the processes using tape along with the drive and volume

[rdemaat]

Hello all, I would like to have a script / select that shows me only the processes currently running that are using tapes. It should display - process number, process name, drive name, volume name. I'm using the processes table and the status column to pull out the volume - LIKE '%500___L4% . All our tapes are 500---L4 . And I'm using the drives table to get the drive and volume name. So I need to compare the processes table LIKE results to the drives table volume name and display when they match. This is the closest I can come but it lists all the processes with each volume in use. As in: if there are 3 processes running using 3 different tapes I get 9 lines displayed.


select processes.process_num, processes.process, processes.status, drives.drive_name, drives.volume_name from processes, drives where processes.status like '%500___L4%' and drives.volume_name like '%500___L4%'

Any help is greatly appreciated! Thank you !!!

 

[marclant]

You would need a join, however there are no matching values. Can't do "where processes.status=drives.volume_name". You'd need a way to strip out the volume_name from processes.status to match it to drives.volume_name.

And to make things more complex, some processes would have 2 volumes listed in the status like reclamation or backup stgpool which would have an input volume and an output volume. Not saying it's impossible, just that it's not an easy one.

 

[rdemaat]

Hi marclant, thanks for your reply. Yeah I was hoping I could combine LIKE and AS. LIKE '%500___L4%' AS status_vol or something similar but maybe you cant do that in sql. And then a WHERE status_vol = drives.volume_name. And yes you make a good point about the running processes that might have 2 volumes in the STATUS field. I didn't think about that. Well if you or anyone else think of anything else let me know.

Thanks again.....