Wednesday, March 9, 2022

Media library information with SQL

media library information and status

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.

3 comments:

  1. S.E. Yarbrough JrMarch 9, 2022 at 9:40 AM

    Simon, very good read and information. Thanks for sharing and have a great day..Again, thanks for sharing.

    ReplyDelete
  2. Do you want to disclose, which VTL product you use?

    ReplyDelete
    Replies
    1. No I do not. What works for my employer may not appropriate for another site.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.