Debbie,

I too was having troubles decoding exactly what it was you were trying
to get from your select statement.  My assessment was about the same as
Andrew's, so I am not going to repeat his advice.  This is a rarity but
twice in the same day I have seen a need for the TSM Data Warehouse
product that comes with TSM.  I think the information you are trying to
get would be better kept in the Data Warehouse where it could be
retrieved from a real database without putting a major load on the TSM
server itself.

--
Regards,
Mark D. Rodriguez
President MDR Consulting, Inc.

===============================================================================
MDR Consulting
The very best in Technical Training and Consulting.
IBM Advanced Business Partner
SAIR Linux and GNU Authorized Center for Education
IBM Certified Advanced Technical Expert, CATE
AIX Support and Performance Tuning, RS6000 SP, TSM/ADSM and Linux
Red Hat Certified Engineer, RHCE
===============================================================================



Debbie Bassler wrote:

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