Debbie, the best thing I can suggest is to look at the information in the
TSM Admin Reference for the SELECT command (or type HELP SELECT from the
command line administrative interface) for starters. Basically TSM's SQL
engine is a subset of SQL92 functionality (the HELP information describes
the limitations).
The TSM database format is proprietary, and indeed is a kind of B-tree
database. The tables that are accessible via SELECT are not the same
physical tables that reside in the TSM database, but are created "on the
fly" from those tables. In other words, when you query the ACTLOG table,
you are not necessarily accessing the records "as is" from the physical
TSM database, but instead, TSM creates that table in memory from it's
internal tables. That is where a lot of the performance issues come from;
because the tables are all virtual.
Another source of info on TSM SQL support is Appendix A of the TSM 5.1
Technical Guide which you can find at:
http://www.redbooks.ibm.com/abstracts/sg246554.html
And lots of folks right here on ADSM-L have their own customized SQL
statements that have been posted here; maybe try searching the ADSM-L
archives to see anything that might interest you.
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com
The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-02
11:29:47:
> Thanks, Andy,,
>
> I ran the script as a server script. I was not able to cancel the
session,
> we had to bounce the server.
>
> Is there any information on how to code scripts for TSM? If I remember
> correctly, it's a btrieve database. Currently, I've only dealt with
Oracle
> databases and know PL/SQL code.
>
> Thanks again,
> Debbie
>
>
>
>
>
> Andrew Raibeck <storman AT US.IBM DOT COM>
> Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
> 08/02/2005 02:28 PM
> Please respond to "ADSM: Dist Stor Manager"
>
>
> To: ADSM-L AT VM.MARIST DOT EDU
> cc:
> Subject: Re: [ADSM-L] Cancel Session
>
>
> WOW!
>
> First, exactly how did you invoke this script? For example, did you
start
> a command line admin session, then run "macro <yourscriptname>"? Or did
> you run it via some batch file or other external script? What happens if
> you try to issue "cancel session" again for this hung session?
>
> Second, I am trying to understand your SELECT command and figure out
what
> you are trying to go after. The best I can tell so far is that you are
> really trying to roll up backup information for stuff that occurred
> between yesterday at 18:00 through today at 08:00. In that case, your
date
> criteria could be made a little simpler:
>
> a.end_time >= timestamp(current_date - 1 day, '18:00:00')
> and
> a.end_time <= timestamp(current_date, '08:00:00')
>
> I'm not certain, though, that this will give you what you want. You are
> combining records from the SUMMARY and ACTLOG tables, but even with the
> WHERE criteria you have, I don't think you are guaranteed to get
accurate
> results. For example (and this is just one), the ACTLOG records are not
> filtered by date, so you could get multiple records from the actlog
where
> the message ID and schedule name match your other criteria.
>
> Lastly, try running these commands:
>
> select count(*) from actlog
> select count(*) from summary
>
> Multiply the results together... that will give you the total number of
> records in the table that TSM will have to create before it does any
> filtering... depending on how long you keep activity log and summary
> records, that could be a very large number, resulting in a very long
> processing time for the command.
>
> A compromise might be to use multiple SELECT statements to get the
> information you need, rather than trying to roll everything into a
single
> SELECT statement. Either that, create a script/program that extracts the
> information from each table separately, then rolls the results up into
the
> single report you want... that might be faster than having TSM do the
work
> on the joined tables.
>
> Regards,
>
> Andy
>
> Andy Raibeck
> IBM Software Group
> Tivoli Storage Manager Client Development
> Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
> Internet e-mail: storman AT us.ibm DOT com
>
> The only dumb question is the one that goes unasked.
> The command line is your friend.
> "Good enough" is the enemy of excellence.
>
> "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-02
> 08:16:11:
>
> > This is the script which is executing;
> >
> > set sqldatetimeformat usa
> > set sqldisplaymode wide
> > select
> > a.entity,a.successful,b.msgno,substr(b.message,posstr(b.
> > message,'transferred:')+14,8)as
> > Bytes_Backedup, substr(cast(min(a.start_time)as varchar(10)),1,10) as
> > Start_Date, substr(cast(min(a.start_time)as varchar(16)),12,12) as
> > Start_time,substr(cast(max(a.end_time)as varchar(16)),12,12) as
> > End_Time,substr(cast(min(a.End_time)as varchar(10)),1,10) as End_Date,
> > substr(cast(max(a.end_time)-(min(a.Start_time)) as varchar(10)),3,5)
as
> > Elapsed_Time,sum(a.mediaw) as media_Wait,sum(a.affected)as
> > number_files,sum(a.failed) as Number_Failed,sum(a.bytes) as
> > Bytes_Transferred, max(a.number)as Number from summary a, actlog b
where
> > b.msgno= 4961 and a.schedule_name = b.schedname and
> > (date(a.end_time)=current date -1 days and
> time(a.end_time)>='18.00.00')
> > or( date(a.end_time)=current date) and time(a.end_time)<='08.00.00'
and
> > a.activity = 'BACKUP' group by a.entity,
a.successful,b.msgno,b.message
> >
> > The bytes sent, bytes received and wait time are all 0.
> >
> > I used a predefined script, written by someone else, and wasn't aware
it
> > didn't work. I should have taken a closer look.
> >
> > Thanks for all the input,
> > Debbie
> >
> >
> >
> >
> >
> > Andrew Raibeck <storman AT US.IBM DOT COM>
> > Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
> > 08/02/2005 10:51 AM
> > Please respond to "ADSM: Dist Stor Manager"
> >
> >
> > To: ADSM-L AT VM.MARIST DOT EDU
> > cc:
> > Subject: Re: [ADSM-L] Cancel Session
> >
> >
> > Look through your activity log and try to figure out the last command
> that
> > your script executed (i.e., exactly what command did the script issue
> that
> > is hung?)
> >
> > The "run" state would suggest that the TSM server is doing processing
of
> a
> > long-running command. If you do "query session" multiple times, do you
> see
> > any of the counters for the hung session changing?
> >
> > You could search the IBM TSM web site
> >
> > http://www-306.ibm.
> > com/software/sysmgmt/products/support/IBMTivoliStorageManager.html
> >
> > for "cancel session" to see if any hits match your scenario.
> >
> > If that doesn't yield anything useful, I'd suggest contacting IBM
> > technical support for further assistance.
> >
> > Regards,
> >
> > Andy
> >
> > Andy Raibeck
> > IBM Software Group
> > Tivoli Storage Manager Client Development
> > Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
> > Internet e-mail: storman AT us.ibm DOT com
> >
> > The only dumb question is the one that goes unasked.
> > The command line is your friend.
> > "Good enough" is the enemy of excellence.
> >
> > "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 2005-08-02
> > 06:13:37:
> >
> > > I executed a script, yesterday, and it is still executing. The
session
> > > won't cancel it because it's in a run status. Is there a way to
force
> a
> > > session to cancel?
> > >
> > > Thanks,
> > > Debbie
|
