Results 1 to 8 of 8
  1. #1
    Newcomer
    Join Date
    Jul 2012
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default HELP!! select statement for DRM

    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. #2
    Moderator moon-buddy's Avatar
    Join Date
    Aug 2005
    Location
    Somewhere in the US
    Posts
    5,896
    Thanks
    4
    Thanked 230 Times in 225 Posts

    Default

    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 by moon-buddy; 07-19-2012 at 02:40 PM.
    Ed

  3. #3
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    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 by rmazzon; 07-19-2012 at 03:19 PM. Reason: pasted in wrong select
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  4. #4
    Newcomer
    Join Date
    Jul 2012
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Thanks rmazzon

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

  5. #5
    Newcomer
    Join Date
    Jul 2012
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    this report would show which tapes from state of mountable to vault current date.

  6. #6
    Member
    Join Date
    Jul 2012
    Posts
    31
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Default

    you can give below commands

    q drm wherest=vaultr
    q drm wherest=mountable

  7. #7
    Senior Member
    Join Date
    Dec 2004
    Location
    NC
    Posts
    200
    Thanks
    0
    Thanked 12 Times in 11 Posts

    Default

    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?
    "If we knew what it was we were doing, it would not be called research, would it?" -- Albert Einstein

  8. #8
    Newcomer
    Join Date
    Jul 2012
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by rmazzon View Post
    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?

    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!!

Similar Threads

  1. select statement ...
    By manxx in forum Scripting
    Replies: 6
    Last Post: 10-26-2009, 09:57 PM
  2. Select Statement
    By OKTSMGURU21 in forum Scripting
    Replies: 5
    Last Post: 02-14-2007, 01:24 PM
  3. how to select statement
    By may in forum Scripting
    Replies: 1
    Last Post: 02-11-2006, 11:15 PM
  4. help with a select statement
    By rolffi in forum Scripting
    Replies: 2
    Last Post: 12-15-2005, 08:35 AM
  5. Select Statement help please.
    By rtclouse in forum Scripting
    Replies: 2
    Last Post: 10-27-2004, 02:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •