Welcome! » Log In » Create A New Profile

SQL statement

Posted by Anonymous 
SQL statement
June 04, 2010 04:33AM
Hi TSM-ers!
I'm trying to create a SQL query to check whether all Oracle files
(Oracle databases are backed up by the TDP client) are excluded from the
standard BA client backup.
Our database admins recommends the follow exclude statements to be added
to the BA client:

/mount/data*/ora_*/log
/mount/data*/ora_*/archive[12]
/mount/data*/ora_*/dbdata
/mount/data*/ora_*/dbindex
/mount/data*/ora_*/*dump
/mount/data*/ora_*/audit
/mount/appl00001/.../network/log/*

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

Could some SQL wizard please help me out here?
Thank you VERY much in advance!!!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines
</pre>********************************************************<br>For information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.<br><br>Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.<br>Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 3014286 <br>********************************************************<pre>
SQL statement
June 04, 2010 07:37AM
I worked with Eric on this. In case anyone else runs into similar...

File system directory and file names may well contain underscore (_) characters, which is fine. What may not be realized, however, is that in a Select LIKE, an underscore is a wildcard for any single character.

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME) like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the convention that the backslash char () be an escape, to turn off the special meaning of the underscore; but you can define it to be the escape char.

By example...

You have a storage pool named STGP_ARCHIVE_3592
which can be verified by reporting via
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME='STGP_ARCHIVE_3592'

Now try to 'escape' the underscore in a LIKE:

select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like 'STGP_ARCHIVE_3592'
ANR2034E SELECT: No match found using this criteria.

So instead do:
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like 'STGP_ARCHIVE_3592' ESCAPE ''
and it will be reported.

Life is seldom simple.

Richard Sims http://people.bu.edu/rbs/

On Jun 4, 2010, at 7:31 AM, Loon, EJ van - SPLXM wrote:

[quote]Hi TSM-ers!
I'm trying to create a SQL query to check whether all Oracle files
(Oracle databases are backed up by the TDP client) are excluded from the
standard BA client backup.
Our database admins recommends the follow exclude statements to be added
to the BA client:

/mount/data*/ora_*/log
/mount/data*/ora_*/archive[12]
/mount/data*/ora_*/dbdata
/mount/data*/ora_*/dbindex
/mount/data*/ora_*/*dump
/mount/data*/ora_*/audit
/mount/appl00001/.../network/log/*

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

Could some SQL wizard please help me out here?
Thank you VERY much in advance!!!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines
</pre>********************************************************<br>For information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.<br><br>Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.<br>Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 3014286 <br>********************************************************<pre>[/quote]
SQL statement
June 04, 2010 07:43AM
Wow... Richard, respect!
This one works, you again saved my day!!!!
Here is the final working statement, so it's 'logged' on the ADSM-L
list:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_%' ESCAPE
'' and node_name='KL100AT0'

Thank you VERY much!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines

-----Original Message-----
From: Richard Sims [mailto]
Sent: vrijdag 4 juni 2010 16:07
To: Loon, EJ van - SPLXM
Subject: Re: SQL statement

On Jun 4, 2010, at 9:23 AM, Loon, EJ van - SPLXM wrote:

[quote]Hi Richard!
Thanks, but still not what I expect, an example of one of the many
[/quote]files
[quote]returned:

KL100AT0,/home,/oracle/xt03js/,statspack_a064.log

Since I use upper(hl_name) like '%ORA_%' I would not expect a
/oracle/xt03js/ in the list...
[/quote]
No, you wouldn't. But... :-)
Your thoughts are keyed on dealing with your site's directory name being
like mount/data*/ora_*
where the architects decided to have an underscore in the name, which is
fine.
But you're now being defeated by that underscore because, in a Select
LIKE, an underscore is a wildcard for any single character!

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME)
like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the standard that the
backslash char be an escape, to turn off the special meaning of the
underscore as in like '%ORA_%'
but, fortunately, it allows you to define an escape character, as in
like '%ORA_%' ESCAPE ''
So, mull that over, and give it a try.

Life is seldom simple.

R.

********************************************************
For information, services and offers, please visit our web site: http://www.klm.com. This e-mail and any attachment may contain confidential and privileged material intended for the addressee only. If you are not the addressee, you are notified that no part of the e-mail or any attachment may be disclosed, copied or distributed, and that any other action related to this e-mail or attachment is strictly prohibited, and may be unlawful. If you have received this e-mail by error, please notify the sender immediately by return e-mail, and delete this message.

Koninklijke Luchtvaart Maatschappij NV (KLM), its subsidiaries and/or its employees shall not be liable for the incorrect or incomplete transmission of this e-mail or any attachments, nor responsible for any delay in receipt.
Koninklijke Luchtvaart Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered in Amstelveen, The Netherlands, with registered number 33014286
********************************************************
SQL statement
June 04, 2010 07:56AM
Ow.
Richard, do you know if it works this way still in V6?

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto] On Behalf Of
Richard Sims
Sent: Friday, June 04, 2010 10:36 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL statement

I worked with Eric on this. In case anyone else runs into similar...

File system directory and file names may well contain underscore (_)
characters, which is fine. What may not be realized, however, is that
in a Select LIKE, an underscore is a wildcard for any single character.

An example of using this:
select NODE_NAME, PLATFORM_NAME from NODES where upper(PLATFORM_NAME)
like 'LINUX__'
to report any Linux86 clients, versus LinuxPPC.

Unfortunately, TSM's Select does not conform to the convention that the
backslash char () be an escape, to turn off the special meaning of the
underscore; but you can define it to be the escape char.

By example...

You have a storage pool named STGP_ARCHIVE_3592
which can be verified by reporting via
select STGPOOL_NAME from STGPOOLS where
STGPOOL_NAME='STGP_ARCHIVE_3592'

Now try to 'escape' the underscore in a LIKE:

select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like
'STGP_ARCHIVE_3592'
ANR2034E SELECT: No match found using this criteria.

So instead do:
select STGPOOL_NAME from STGPOOLS where STGPOOL_NAME like
'STGP_ARCHIVE_3592' ESCAPE ''
and it will be reported.

Life is seldom simple.

Richard Sims http://people.bu.edu/rbs/

On Jun 4, 2010, at 7:31 AM, Loon, EJ van - SPLXM wrote:

[quote]Hi TSM-ers!
I'm trying to create a SQL query to check whether all Oracle files
(Oracle databases are backed up by the TDP client) are excluded from
[/quote]the
[quote]standard BA client backup.
Our database admins recommends the follow exclude statements to be
[/quote]added
[quote]to the BA client:

/mount/data*/ora_*/log
/mount/data*/ora_*/archive[12]
/mount/data*/ora_*/dbdata
/mount/data*/ora_*/dbindex
/mount/data*/ora_*/*dump
/mount/data*/ora_*/audit
/mount/appl00001/.../network/log/*

So I want to check if the BA client repository does not contain these
files. Thus far I created this query, but it's not correct:

select node_name, filespace_name, hl_name, ll_name from backups where
(upper(ll_name) like '%LOG%' or upper(ll_name) like '%ARCHIVE%' or
upper(ll_name) like '%DBDATA%' or upper(ll_name) like '%DBINDEX%' or
upper(ll_name) like '%DUMP%' or upper(ll_name) like '%AUDIT%') and
upper(hl_name) like '%ORA_`%' and node_name='KL100AT0'

Could some SQL wizard please help me out here?
Thank you VERY much in advance!!!
Kind regards,
Eric van Loon
KLM Royal Dutch Airlines
</pre>********************************************************<br>For
[/quote]information, services and offers, please visit our web site:
http://www.klm.com. This e-mail and any attachment may contain
confidential and privileged material intended for the addressee only. If
you are not the addressee, you are notified that no part of the e-mail
or any attachment may be disclosed, copied or distributed, and that any
other action related to this e-mail or attachment is strictly
prohibited, and may be unlawful. If you have received this e-mail by
error, please notify the sender immediately by return e-mail, and delete
this message.<br><br>Koninklijke Luchtvaart Maatschappij NV (KLM), its
subsidiaries and/or its employees shall not be liable for the incorrect
or incomplete transmission of this e-mail or any attachments, nor
responsible for any delay in receipt.<br>Koninklijke Luchtvaart
Maatschappij N.V. (also known as KLM Royal Dutch Airlines) is registered
in Amstelveen, The Netherlands, with registered number 3014286
<br>********************************************************<pre>
SQL statement
June 04, 2010 08:03AM
On Jun 4, 2010, at 10:41 AM, Prather, Wanda wrote:

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be made by a new VP, so I haven't that experience. I would expect DB2 to be much more conformant to SQL standards than the Select accommodations in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard
SQL statement
June 04, 2010 08:17AM
Hello,

Could anyone provide me a sql statement which can check if any copy storage
pool volume is mounted in tape drive at the moment? I need this to configure
script for my DRP.

Thanks in advance.

--
Best Regards
Ashish Sharma
ST Microelectronics Ltd.
919717003853
SQL statement
June 04, 2010 08:31AM
Connecting directly to DB2:

Database Connection Information

Database server = DB2/AIX64 9.7.1
SQL authorization ID = TSMTEST1
Local database alias = TSM1

select tabschema,tabname,owner from syscat.tables where
tabname='STGPOOLS'

TABSCHEMA
TABNAME
OWNER

------------------------------------------------------------------------
--------------------------------------------------
------
------------------------------------------------------------------------
-------------------------------------------
-------------
------------------------------------------------------------------------
------------------------------------
--------------------
TSMDB1
STGPOOLS
TSMTEST1

1 record(s) selected.

select stgpool_name from tsmdb1.stgpools where stgpool_name like '%_%'
escape ''

STGPOOL_NAME
-------------------------------
TEST_POOL

1 record(s) selected.

select stgpool_name from tsmdb1.stgpools where stgpool_name like '%_%'

STGPOOL_NAME
-------------------------------

0 record(s) selected.

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto] On Behalf Of
Richard Sims
Sent: Friday, June 04, 2010 11:02 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL statement

On Jun 4, 2010, at 10:41 AM, Prather, Wanda wrote:

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged and confidential information.
It is intended only for the use of the person(s) named above. If you are not the intended
recipient, you are hereby notified that any review, dissemination, distribution or
duplication of this communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original message.
To reply to our email administrator directly, please send an email to postmaster < at > sbsplanet.com.
SQL statement
June 04, 2010 08:33AM
Storage Management Server for AIX - Version 6, Release 2, Level 0.0
(AIX v5.3)

tsm: TSMTEST1>select stgpool_name from stgpools where stgpool_name like
'%_%' escape ''

STGPOOL_NAME
--------------------------------
TEST_POOL

tsm: TSMTEST1>select stgpool_name from stgpools where stgpool_name like
'%_%'
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto] On Behalf Of
Richard Sims
Sent: Friday, June 04, 2010 11:02 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL statement

On Jun 4, 2010, at 10:41 AM, Prather, Wanda wrote:

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged and confidential information.
It is intended only for the use of the person(s) named above. If you are not the intended
recipient, you are hereby notified that any review, dissemination, distribution or
duplication of this communication is strictly prohibited. If you are not the intended recipient,
please contact the sender by reply email and destroy all copies of the original message.
To reply to our email administrator directly, please send an email to postmaster < at > sbsplanet.com.
SQL statement
June 04, 2010 08:34AM
Ooh, cool! Thanks!

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto] On Behalf Of
Cowen, Richard
Sent: Friday, June 04, 2010 11:31 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL statement

Connecting directly to DB2:

Database Connection Information

Database server = DB2/AIX64 9.7.1
SQL authorization ID = TSMTEST1
Local database alias = TSM1

select tabschema,tabname,owner from syscat.tables where
tabname='STGPOOLS'

TABSCHEMA
TABNAME
OWNER

------------------------------------------------------------------------
--------------------------------------------------
------
------------------------------------------------------------------------
-------------------------------------------
-------------
------------------------------------------------------------------------
------------------------------------
--------------------
TSMDB1
STGPOOLS
TSMTEST1

1 record(s) selected.

select stgpool_name from tsmdb1.stgpools where stgpool_name like '%_%'
escape ''

STGPOOL_NAME
-------------------------------
TEST_POOL

1 record(s) selected.

select stgpool_name from tsmdb1.stgpools where stgpool_name like '%_%'

STGPOOL_NAME
-------------------------------

0 record(s) selected.

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto] On Behalf Of
Richard Sims
Sent: Friday, June 04, 2010 11:02 AM
To: ADSM-L < at > VM.MARIST.EDU
Subject: Re: [ADSM-L] SQL statement

On Jun 4, 2010, at 10:41 AM, Prather, Wanda wrote:

[quote]Ow.
Richard, do you know if it works this way still in V6?
[/quote]
Hi, Wanda -

We remain a v5 shop, pending a lot of infrastructure decisions to be
made by a new VP, so I haven't that experience. I would expect DB2 to
be much more conformant to SQL standards than the Select accommodations
in "TSM Classic". Perhaps a customer with v6 can lend to this.

Richard

The information contained in this transmission may contain privileged
and confidential information.
It is intended only for the use of the person(s) named above. If you are
not the intended
recipient, you are hereby notified that any review, dissemination,
distribution or
duplication of this communication is strictly prohibited. If you are not
the intended recipient,
please contact the sender by reply email and destroy all copies of the
original message.
To reply to our email administrator directly, please send an email to
postmaster < at > sbsplanet.com.
SQL statement
June 04, 2010 09:19AM
-----Ashish Sharma wrote: -----

[quote]Could anyone provide me a sql statement which can check if any copy
storage pool volume is mounted in tape drive at the moment? I need
this to configure script for my DRP.
[/quote]
select volume_name from drives where volune_name in
(select volume_name from stgpools where stgpool_name in
(select stgpool_name from stgpools where pooltype='COPY'))

I am not at all sure this will work if your TSM server runs under
z/OS or one of its ancesters; z/OS handles a lot of the tape and
library support that is built into TSM on other platforms.

There seem to be some timing issues in TSM reporting of tape
status. We used to have an automation script that detected the
dismounting of the last copy pool tape by executing a 'query
mount' command each time a message reporting that a tape had
been dismounted appeared in the consoled message stream. The
'query mount' command would routinely report a tape volume as
still mounted for a fraction of a second after TSM displayed
a message reporting that the volume had been dismounted. We
had to put a 'sleep' command in the script to delay execution
of 'query mount' by one second.
Marc Lanteigne
Re: SQL STATEMENT
January 22, 2018 06:59AM
Hi James,

There is probably an example like you are looking for here:
http://thobias.org/tsm/sql/

-
Thanks,
Marc...
________________________________________________________
Marc Lanteigne
Accelerated Value Specialist for Spectrum Protect
416.478.0233 | marclanteigne@ca.ibm.com
Office Hours: Monday to Friday, 7:00 to 16:00 Eastern

Follow me on: Twitter, developerWorks, LinkedIn


-----Original Message-----
From: Lepre, James [mailto:james.lepre@SOLIXINC.COM]
Sent: Monday, January 22, 2018 10:45 AM
To: ADSM-L@VM.MARIST.EDU
Subject: [ADSM-L] SQL STATEMENT

Hey Everyone -

I am looking for a sql select statement that show completions, missed,
and failures of daily schedules. This output should be similar to the q
event command

Thank you in advance for the assistance



James Lepre




---------------------------------------------------------------

Confidentiality Notice: The information in this e-mail and any

attachments thereto is intended for the named recipient(s) only

.. This e-mail, including any attachments, may contain information

that is privileged and confidential and subject to legal restrictions

and penalties regarding its unauthorized disclosure or other use.

If you are not the intended recipient, you are hereby notified

that any disclosure, copying, distribution, or the taking of any

action or inaction in reliance on the contents of this e-mail and

any of its attachments is STRICTLY PROHIBITED. If you

have received this e-mail in error, please immediately notify

the sender via return e-mail; delete this e-mail and all attachments

from your e-mail system and your computer system and network;

and destroy any paper copies you may have in your possession.

Thank you for your cooperation.
This message was imported via the External PhorumMail Module
Sorry, only registered users may post in this forum.

Click here to login