Media library information with SQL

A recent upgrade to a new virtual tape library, VTL, product introduced me to the concepts of a tape library and to a new command Work with Media Library Status, WRKMLBSTS. The system admins came to me asked if there was a way I could get to the same information as is displayed by WRKMLBSTS.

They had become frustrated with the command as it did not offer the ability to output its results to an outfile or a spool file, that they would then copy to a file.

"Is there something in SQL you can work your magic with?" they asked.

A quick search of IBM's documentation portal gave me what was needed, a view called MEDIA_LIBRARY_INFO.

I can compare the two. This is the output of the WRKMLBSTS:

Work with Media Library Status System: DEV740 Type options, press Enter. 1=Vary on 2=Vary off 3=Reset resource 4=Allocate resource 5=Allocate unprotected 6=Deallocate resource 8=Work with description Device/ Job Opt Resource Status Allocation name TAPLIBR1 ACTIVE TAP100 ACTIVE ALLOCATED SAVTEST1 TAP101 OPERATIONAL DEALLOCATED TAP102 OPERATIONAL DEALLOCATED TAP103 OPERATIONAL DEALLOCATED TAP104 OPERATIONAL DEALLOCATED TAP105 OPERATIONAL DEALLOCATED TAP106 OPERATIONAL DEALLOCATED TAP107 OPERATIONAL DEALLOCATED TAP108 OPERATIONAL DEALLOCATED More...

Which is like the results of this SQL statement:

SELECT * FROM QSYS2.MEDIA_LIBRARY_INFO ;

I have only included the first seven columns below as this was the useful information:

RESOURCE_ DEVICE DEVICE_ DEVICE DEVICE RESOURCE RESOURCE ALLOCATION _NAME STATUS _TYPE _MODEL _NAME _STATUS _STATUS -------- ------- ------ ------ -------- ----------- ------------ TAPLIBR1 ACTIVE 3573 020 TAP100 ACTIVE ALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP101 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP102 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP103 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP104 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP105 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP106 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP107 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP108 OPERATIONAL DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP109 UNAVAILABLE DEALLOCATED TAPLIBR1 ACTIVE 3573 020 TAP110 OPERATIONAL UNPROTECTED TAPLIBR1 ACTIVE 3573 020 TAP111 OPERATIONAL UNPROTECTED

These columns are:

• DEVICE_NAME:  In my limited experience this is the name of the VTL device
• DEVICE_STATUS:  Three statuses:
  1. ACTIVE:  Resource in use
  2. VARIED OFF:  Media library is varied off
  3. VARIED ON:  Media library is varied on
• DEVICE_TYPE:  Type of device
• DEVICE_MODEL:  Model number of the device
• RESOURCE_NAME:  With the VTL I was working with this was the virtual tape drive name
• RESOURCE_STATUS:  I found four statuses:
  1. ACTIVE:  Currently in use
  2. FAILED:  Resource not operational, and the system cannot communicate with it. Hardware issue
  3. OPERATIONAL:  Resource is working, and the tape drive is available to be used
  4. UNAVAILABLE:  Resource is not available, and is probably in use by another job or object outside the VTL
• RESOURCE_ALLOCATION_STATUS:  I have only seen four of these statuses:
  1. ALLOCATED:  Exclusively allocated to this system, and cannot be used by another
  2. DEALLOCATED:  Not assigned to this system, and not available to be used
  3. STAND-ALONE:  Reserved for stand-alone tape device description (I have not seen this)
  4. UNPROTECTED:  Can be used by another system when no other systems have assigned this drive
  5. *UNKNNOWN:  Optical media, not tape, current allocation cannot be ascertained (I have not seen this)

All the remaining columns contained their default values.

Now I can use this view to get the status of a particular tape drive. For example, I can use a RPG program to return the allocation status of a particular drive:

01 **free 02 dcl-s Allocation char(15) inz('???') ; 03 dcl-s Drive char(10) inz('TAP105') ; 04 exec sql SELECT RESOURCE_ALLOCATION_STATUS INTO :Allocation FROM QSYS2.MEDIA_LIBRARY_INFO WHERE RESOURCE_NAME = :Drive ; 05 dsply ('Allocation = ' + Allocation) ; 06 *inlr = *on ;

Line 2: This is the variable the result will be placed in. I have initialized it with "???". If a value cannot be returned from the SQL statement, then the value in the variable will not be changed.

Line 3: This is the name of tape drive I want to check.

Line 4: The Select statement retrieves the RESOURCE_ALLOCATION_STATUS for the tape drive, and place the returned value into the variable Allocation.

Line 5: I am using the display operation code to show the value of Allocation.

After compiling the source code to create a program, I call it and the display operation code shows:

DSPLY Allocation = DEALLOCATED

Now I can provide the system admins with all the information they need without having to manipulate the data on the WRKMLBSTS display.

 

You can learn more about the MEDIA_LIBRARY_INFO SQL view from the IBM website here.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.