1. Forum Rules (PLEASE CLICK HERE TO READ BEFORE POSTING) Click the link to access ADSM.ORG Acceptable Use Policy and forum rules which should be observed when using this website. Violators may be banned from this website. This message will disappear after you have made at least 12 posts. Thank you for your cooperation.

HELP!! select statement for DRM

Discussion in 'Scripting' started by garychan789, Jul 19, 2012.

  1. garychan789

    garychan789 New Member

    Joined:
    Jul 3, 2012
    Messages:
    14
    Likes Received:
    0
    Hello anyone,

    Following select statement just show tape is state of "VAULT" in all time. I would show changed the state of "VAULT" in today.

    SELECT drmedia.volume_name, volumes.stgpool_name, drmedia.state, drmedia.voltype, volumes.status, volumes.pct_utilized FROM drmedia, volumes WHERE drmedia.state='VAULT'

    Thanks

    Gary Chan
     
  2.  
  3. moon-buddy

    moon-buddy Moderator

    Joined:
    Aug 24, 2005
    Messages:
    6,114
    Likes Received:
    273
    Occupation:
    Electronics Engineer, Security Professional
    Location:
    Somewhere in the US
    What exactly do you want to get? Do you want to get a listing of tapes that are in 'VAULT' status? If that is all, then why not use 'q drm'?
     
    Last edited: Jul 19, 2012
  4. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    206
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    If you want to get all tapes that have gone into a vault state on a particular date modify your query like this

    SELECT drmedia.volume_name, drmedia.upd_date, drmedia.stgpool_name, drmedia.state, drmedia.voltype, volumes.status, volumes.pct_utilized FROM drmedia, volumes WHERE drmedia.state='VAULT' and volumes,volume_name=drmedia.volume_name and date(drmedia.upd_date)='2012-07-17'

    This only gets the storage pool volumes and not the DBB volumes
     
    Last edited: Jul 19, 2012
  5. garychan789

    garychan789 New Member

    Joined:
    Jul 3, 2012
    Messages:
    14
    Likes Received:
    0
    Thanks rmazzon

    this report will generate everyday that cannot set fix time stramp and how to show tape of dbbackup?
     
  6. garychan789

    garychan789 New Member

    Joined:
    Jul 3, 2012
    Messages:
    14
    Likes Received:
    0
    this report would show which tapes from state of mountable to vault current date.
     
  7. eddytrio

    eddytrio New Member

    Joined:
    Jul 19, 2012
    Messages:
    31
    Likes Received:
    2
    you can give below commands

    q drm wherest=vaultr
    q drm wherest=mountable
     
  8. rmazzon

    rmazzon Senior Member

    Joined:
    Dec 6, 2004
    Messages:
    206
    Likes Received:
    17
    Occupation:
    TSM Managed Services Lead
    Location:
    NC
    Gary,

    If you want to adjust any SELECT statement based on a date criteria to be dynamic, you can modify your select to use current_timestamp or current_date. For example (and these produce the same results from the day before)

    SELECT drmedia.volume_name, drmedia.upd_date, drmedia.stgpool_name, drmedia.state, drmedia.voltype FROM drmedia WHERE drmedia.state='VAULT' and date(drmedia.upd_date)=current_date - 1 day

    SELECT drmedia.volume_name, drmedia.upd_date, drmedia.stgpool_name, drmedia.state, drmedia.voltype FROM drmedia WHERE drmedia.state='VAULT' and date(drmedia.upd_date)=date(current_timestamp - 1 day)

    As far as not getting the DBB tapes in the original select, that was because you wanted to tie in the volumes table to get the status and pct_utilized. The DBB tables are not in the volumes table, so they did not appear and effectively filtered the output. Note the above two examples remove the volumes table and will give you both DBB and STG tapes.

    If this does not answer your question we need to go back to moon-buddy's question, what are you trying to get?
     
  9. garychan789

    garychan789 New Member

    Joined:
    Jul 3, 2012
    Messages:
    14
    Likes Received:
    0

    Thank you RMAZZON,

    Finished all backup jobs, and move media to state of VAULT. Then, generate a checkout report to oper and takout those tapes to offsite.

    SELECT drmedia.volume_name, drmedia.upd_date, drmedia.stgpool_name, drmedia.state, drmedia.voltype FROM drmedia WHERE drmedia.state='VAULT' and date(drmedia.upd_date)=current_date

    Above statement is expect result of myself. So, no need current_date -1 day

    Thank you for your help again!!
     

Share This Page