Eric,
If you examine the COLUMNS table for the BACKUPS table, you will see that
the TYPE column is of type "ENUMERATED(OBJECT_TYPE)":
tsm: TSMSERVER1>select colname,typename from columns where
tabname='BACKUPS'
COLNAME TYPENAME
------------------ ------------------
NODE_NAME VARCHAR
FILESPACE_NAME VARCHAR
FILESPACE_ID INTEGER
STATE ENUMERATED(BACKUP-
STATE)
TYPE ENUMERATED(OBJECT-
_TYPE)
HL_NAME VARCHAR
LL_NAME VARCHAR
OBJECT_ID DECIMAL
BACKUP_DATE TIMESTAMP
DEACTIVATE_DATE TIMESTAMP
OWNER VARCHAR
CLASS_NAME VARCHAR
tsm: TSMSERVER1>
Next, examine the ENUMTYPES table for TYPENAME OBJECT_TYPE:
tsm: TSMSERVER1>select * from enumtypes where typename='OBJECT_TYPE'
TYPEINDEX TYPENAME VALUECOUNT VALUES
REMARKS
----------- ------------------ ----------- ------------------
------------------
0 OBJECT_TYPE 16 DIR(0), FILE(1),
Object Type
IMAGE(2),
BACKUPSET
FULL(3), IMAGE
BASE(4), IMAGE
DELTA(5), IMAGE
VOL EXTENT(6),
IMAGE VOL
DATA(7), IMAGE
OBF EXTENT(8),
IMAGE OBG
DATA(9),
UNKNOWN(10),
GROUP BASE(11),
GROUP DIFF(12),
GROUP LIST(13),
TOC(14),
BACKUPSET
DIFF(15)
tsm: TSMSERVER1>
This table tells you the values you can use for the TYPE filed in the
BACKUPS table.
Note: The real trick here is that you specify the symbolic name for the
type, not the numeric value. That is, you specify "DIR" instead of "0". The
symbolic names are not CHAR or VARCHAR values as such, so you cannot use
wildcards. That is, you must specify the full symbolic name(s). In fact,
because these are not string values, the casing is not important; you can
specify "image" or "IMAGE".
So your SELECT statement should work correctly like this:
select * from backups where node_name='KL127EAC'
and type in ('IMAGE', 'IMAGE BASE', ...)
(You need to list all the individual names that begin with "IMAGE".)
Best regards,
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Product Development
Level 3 Team Lead
Internal Notes e-mail: Andrew Raibeck/Hartford/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com
IBM Tivoli Storage Manager support pages:
http://www.ibm.com/support/entry/portal/Overview/Software/Tivoli/Tivoli_Storage_Manager
http://www.ibm.com/developerworks/wikis/display/tivolidoccentral/Tivoli
+Storage+Manager
https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/Tivoli
+Storage+Manager/page/Home
"ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu> wrote on 2013-03-12
05:49:27:
> From: "Loon, EJ van - SPLXM" <Eric-van.Loon AT KLM DOT COM>
> To: ADSM-L AT vm.marist DOT edu,
> Date: 2013-03-12 05:50
> Subject: SQL error
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT vm.marist DOT edu>
>
> Hi TSM-ers!
>
> I'm trying to retrieve all image backups for a specific node. I'm
> issuing the following query:
>
>
>
> select * from backups where node_name='KL127EAC' and type like 'IMAGE%'
>
>
>
> This returns the following error:
>
>
>
> ANR2921E The SQL data type of expression 'TYPE' is
> ENUMERATED(OBJECT_TYPE); expecting a character string expression.
>
>
>
> This query works fine:
>
>
>
> select * from backups where node_name='KL127EAC'and type='IMAGE' works
> fine...
>
>
>
> That's where where my limited SQL knowledge stops.
>
> Thank you very much for any help!
>
> Kind regards,
>
> Eric van Loon
>
> AF/KLM Storage Engineering
>
> ********************************************************
> 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
> ********************************************************
>
|