HELP!! select statement for DRM

[garychan789]

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

[moon-buddy]

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'?

[rmazzon]

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

[garychan789]

Thanks rmazzon

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

[garychan789]

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

[eddytrio]

you can give below commands

q drm wherest=vaultr
q drm wherest=mountable

[rmazzon]

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?

[garychan789]

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