Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Bill Colwell 

Joe - you can get shorter headers by selecting functions on the columns,
for example -

select left(volume_name,6) as volume, left(stgpool_name,10) as stgpool -
 from volumes -
  where stgpool_name = '9840APOOL' -
 and status = 'FULL' -
 and date(last_write_date) >= current date - 7 days


Which produces this in dsmadmc --

adsm> select left(volume_name,6) as volume, left(stgpool_name,10) as stgpool -
cont>  from volumes -
cont>   where stgpool_name = '9840APOOL' -
cont>  and status = 'FULL' -
cont>  and date(last_write_date) >= current date - 7 days

VOLUME STGPOOL
-- --
92 9840APOOL
900067 9840APOOL
900176 9840APOOL
900444 9840APOOL
900584 9840APOOL
902090 9840APOOL
902281 9840APOOL
902385 9840APOOL
902387 9840APOOL
902453 9840APOOL
902460 9840APOOL
902465 9840APOOL
902469 9840APOOL
902478 9840APOOL
902500 9840APOOL
902511 9840APOOL
902522 9840APOOL
902543 9840APOOL
902545 9840APOOL
902549 9840APOOL

adsm>

Hope this helps,

--
--
Bill Colwell
C. S. Draper Lab
Cambridge, Ma.
[EMAIL PROTECTED]
--


In <[EMAIL PROTECTED]>, on 12/03/01
   at 03:49 PM, Joe Faracchio <[EMAIL PROTECTED]> said:

>Interesting ... maybe even marvelous BUT ...

>Can you tell us how to SHRINK the headers???   In specifying " " for
>volume_name I still get the - underlines.
>Like:

>   --STORAGE`_POOL_NAME-
>-- -
>20 TAPEPOOL

>SIGH!
>... joe.f.


>Joseph A Faracchio,  Systems Programmer, UC Berkeley
>Private mail on any topic should be directed to :
> [EMAIL PROTECTED]
> (510)642-7638 (w)  (209)483-JOEF (M)
> 5633


>On Mon, 3 Dec 2001, Poehlman, James wrote:

>> Within the select statement itself you can specify a header in place of the
>> default and make it the width desired.
>> IE: select
>> VOLUME_NAME,STGPOOL_NAME"--STORAGE`_POOL_NAME-",LAST_WRITE_DATE"
>> -MM-DD_HH-MM-SS_---" from volumes.
>> The parameter in quotes will be sent as the header, with that width.
>> Hope this helps.
>> Denny P.

Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Joe Faracchio 

Interesting ... maybe even marvelous BUT ...

Can you tell us how to SHRINK the headers???   In specifying " " for
volume_name I still get the - underlines.
Like:

   --STORAGE`_POOL_NAME-
-- -
20 TAPEPOOL

SIGH!
... joe.f.


Joseph A Faracchio,  Systems Programmer, UC Berkeley
Private mail on any topic should be directed to :
 [EMAIL PROTECTED]
 (510)642-7638 (w)  (209)483-JOEF (M)
 5633


On Mon, 3 Dec 2001, Poehlman, James wrote:

> Within the select statement itself you can specify a header in place of the
> default and make it the width desired.
> IE: select
> VOLUME_NAME,STGPOOL_NAME"--STORAGE`_POOL_NAME-",LAST_WRITE_DATE"
> -MM-DD_HH-MM-SS_---" from volumes.
> The parameter in quotes will be sent as the header, with that width.
> Hope this helps.
> Denny P.
>
> -Original Message-
> From: Andrew Raibeck [mailto:[EMAIL PROTECTED]]
> Sent: Monday, December 03, 2001 9:26 AM
> To: [EMAIL PROTECTED]
> Subject: Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE
>
>
> The SET SQLDISPLAYMODE command only "holds" for a given session. Thus when
> the admin session from which you issued that command ends, the next admin
> session that issues the SELECT command uses the default SQLDISPLAYMODE.
>
> How about combining your SET SQLDISPLAYMODE and SELECT commands into a
> single admin macro file, then using the admin MACRO command to process the
> macro? (See the Admin Reference for info about the MACRO command.)
>
> Another formatting issue that you might run across is the case where the
> command line displays the information in "list" format (each column of
> output is displayed on a single line) when you use the interactive admin
> CLI, but when the output is redirected, the information is displayed in
> "table" (row-column) format. For this case, have a look at the
> -DISPLAYMODE option. You can find more about this option in the Admin
> Reference.
>
> Regards,
>
> Andy
>
> Andy Raibeck
> IBM Software Group
> Tivoli Storage Manager Client Development
> Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
> Internet e-mail: [EMAIL PROTECTED]
>
> The only dumb question is the one that goes unasked.
> The command line is your friend.
> "Good enough" is the enemy of excellence.
>
>
>
>
> "Pucky, Todd M." <[EMAIL PROTECTED]>
> Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
> 12/03/2001 05:44
> Please respond to "ADSM: Dist Stor Manager"
>
>
> To: [EMAIL PROTECTED]
> cc:
> Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE
>
>
>
> TSM Server: AIX 4.3.3
>
> I have seen this issue addressed in the adsm archives, but never a clear
> answer on how to solve it.  I plan to use a script in a cron job on AIX,
> but
> I need to have SQLDISPLAYMODE set to WIDE.  Here is the part of my script
> where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query.
>
> dsmadmc -id=xx -pas=xx "set sqldisplaymode wide"
> dsmadmc -id=xx -pas=xx "select events.node_name, events.s
> tatus, nodes.contact from events, nodes where events.domain_name='SERVER'
> and ev
> ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or
> events.stat
> us='Failed') and events.node_name=nodes.node_name" > $eventtmp
>
>  I have no problems when I issue these commands from the Admin Command
> Line,
> but through the AIX script I always get SQL format of NARROW in the
> results
> of my query.  Any suggestions on how to get the SQLDISPLAYMODE WIDE
> command
> to work in the AIX script would be greatly appreciated.
>
> Thanks
>
> Todd M. Pucky
> The Timken Company
> Global Information Systems
>
>
>
> **
> This message and any attachments are intended for the
> individual or entity named above. If you are not the intended
> recipient, please do not read, copy, use or disclose this
> communication to others; also please notify the sender by
> replying to this message, and then delete it from your system.
>
> The Timken Company
> **
>
> This message was scanned for viruses on behalf of sender.
>
>
> This message was scanned for viruses on behalf of sender.
>

Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Poehlman, James 

Within the select statement itself you can specify a header in place of the
default and make it the width desired.
IE: select
VOLUME_NAME,STGPOOL_NAME"--STORAGE`_POOL_NAME-",LAST_WRITE_DATE"
-MM-DD_HH-MM-SS_---" from volumes.
The parameter in quotes will be sent as the header, with that width.
Hope this helps.
Denny P.

-Original Message-
From: Andrew Raibeck [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 03, 2001 9:26 AM
To: [EMAIL PROTECTED]
Subject: Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE


The SET SQLDISPLAYMODE command only "holds" for a given session. Thus when
the admin session from which you issued that command ends, the next admin
session that issues the SELECT command uses the default SQLDISPLAYMODE.

How about combining your SET SQLDISPLAYMODE and SELECT commands into a
single admin macro file, then using the admin MACRO command to process the
macro? (See the Admin Reference for info about the MACRO command.)

Another formatting issue that you might run across is the case where the
command line displays the information in "list" format (each column of
output is displayed on a single line) when you use the interactive admin
CLI, but when the output is redirected, the information is displayed in
"table" (row-column) format. For this case, have a look at the
-DISPLAYMODE option. You can find more about this option in the Admin
Reference.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED]

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




"Pucky, Todd M." <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
12/03/2001 05:44
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE



TSM Server: AIX 4.3.3

I have seen this issue addressed in the adsm archives, but never a clear
answer on how to solve it.  I plan to use a script in a cron job on AIX,
but
I need to have SQLDISPLAYMODE set to WIDE.  Here is the part of my script
where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query.

dsmadmc -id=xx -pas=xx "set sqldisplaymode wide"
dsmadmc -id=xx -pas=xx "select events.node_name, events.s
tatus, nodes.contact from events, nodes where events.domain_name='SERVER'
and ev
ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or
events.stat
us='Failed') and events.node_name=nodes.node_name" > $eventtmp

 I have no problems when I issue these commands from the Admin Command
Line,
but through the AIX script I always get SQL format of NARROW in the
results
of my query.  Any suggestions on how to get the SQLDISPLAYMODE WIDE
command
to work in the AIX script would be greatly appreciated.

Thanks

Todd M. Pucky
The Timken Company
Global Information Systems



**
This message and any attachments are intended for the
individual or entity named above. If you are not the intended
recipient, please do not read, copy, use or disclose this
communication to others; also please notify the sender by
replying to this message, and then delete it from your system.

The Timken Company
**

This message was scanned for viruses on behalf of sender.


This message was scanned for viruses on behalf of sender.

Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Prather, Wanda 

Todd, here's another way around the problem:

Add -commadelimited as a parm after -pa=.

That causes the output to come out in a wide string, with the fields
separated by commas, no wrapping.

Since you are doing this from AIX, it's very easy to handle that output in
the shell script.

You could, for example, pipe the dsmadmc command to a tr command that just
translates the commas back to blanks.




-Original Message-
From: Alexandre Bicas
To: [EMAIL PROTECTED]
Sent: 12/3/01 8:41 AM
Subject: RES: SQL Formatting on AIX scripts - SQLDISPLAYMODE

Todd,

This problem occurs because SQLDISPLAYMODE is a parameter set only for
the
current session.
Each time you issue dsmadmc comands you are invoking a new session and
SQLDISPLAYMODE is reset to NARROW.
You can check this issuing Q SQLSESSION after setting SQLDISPLAYMODE.

I would create a "Server Command Script" inside TSM which would have the
SQLDISPLAYMODE and the SELECT clauses into it.
Once this script is created, you just have to run it from your cron as
you
do it today (dsmadmc -id=xxx -pa=yyy "run my_script_name")

Good luck !

Alexandre Bicas Caldeira - [EMAIL PROTECTED]
Tivoli Certified Specialist - Tivoli Storage Manager
Service IT Solutions - http://www.service.com.br
   Sao Paulo:(11) 3054-1400
   Rua Vicente Pinzon, 144, cj 112
   CEP 04547-130 - Sco Paulo - SP
 Porto Alegre:   (51) 212-3666
 Curitiba:   (41) 363-5586

-Mensagem original-
De: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED]]Em nome de
Pucky, Todd M.
Enviada em: segunda-feira, 3 de dezembro de 2001 09:44
Para: [EMAIL PROTECTED]
Assunto: SQL Formatting on AIX scripts - SQLDISPLAYMODE


TSM Server: AIX 4.3.3

I have seen this issue addressed in the adsm archives, but never a clear
answer on how to solve it.  I plan to use a script in a cron job on AIX,
but
I need to have SQLDISPLAYMODE set to WIDE.  Here is the part of my
script
where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query.

dsmadmc -id=xx -pas=xx "set sqldisplaymode wide"
dsmadmc -id=xx -pas=xx "select events.node_name, events.s
tatus, nodes.contact from events, nodes where
events.domain_name='SERVER'
and ev
ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or
events.stat
us='Failed') and events.node_name=nodes.node_name" > $eventtmp

 I have no problems when I issue these commands from the Admin Command
Line,
but through the AIX script I always get SQL format of NARROW in the
results
of my query.  Any suggestions on how to get the SQLDISPLAYMODE WIDE
command
to work in the AIX script would be greatly appreciated.

Thanks

Todd M. Pucky
The Timken Company
Global Information Systems



**
This message and any attachments are intended for the
individual or entity named above. If you are not the intended
recipient, please do not read, copy, use or disclose this
communication to others; also please notify the sender by
replying to this message, and then delete it from your system.

The Timken Company
**

Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Andrew Raibeck 

The SET SQLDISPLAYMODE command only "holds" for a given session. Thus when
the admin session from which you issued that command ends, the next admin
session that issues the SELECT command uses the default SQLDISPLAYMODE.

How about combining your SET SQLDISPLAYMODE and SELECT commands into a
single admin macro file, then using the admin MACRO command to process the
macro? (See the Admin Reference for info about the MACRO command.)

Another formatting issue that you might run across is the case where the
command line displays the information in "list" format (each column of
output is displayed on a single line) when you use the interactive admin
CLI, but when the output is redirected, the information is displayed in
"table" (row-column) format. For this case, have a look at the
-DISPLAYMODE option. You can find more about this option in the Admin
Reference.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: [EMAIL PROTECTED]

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




"Pucky, Todd M." <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
12/03/2001 05:44
Please respond to "ADSM: Dist Stor Manager"


To: [EMAIL PROTECTED]
cc:
Subject:SQL Formatting on AIX scripts - SQLDISPLAYMODE



TSM Server: AIX 4.3.3

I have seen this issue addressed in the adsm archives, but never a clear
answer on how to solve it.  I plan to use a script in a cron job on AIX,
but
I need to have SQLDISPLAYMODE set to WIDE.  Here is the part of my script
where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query.

dsmadmc -id=xx -pas=xx "set sqldisplaymode wide"
dsmadmc -id=xx -pas=xx "select events.node_name, events.s
tatus, nodes.contact from events, nodes where events.domain_name='SERVER'
and ev
ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or
events.stat
us='Failed') and events.node_name=nodes.node_name" > $eventtmp

 I have no problems when I issue these commands from the Admin Command
Line,
but through the AIX script I always get SQL format of NARROW in the
results
of my query.  Any suggestions on how to get the SQLDISPLAYMODE WIDE
command
to work in the AIX script would be greatly appreciated.

Thanks

Todd M. Pucky
The Timken Company
Global Information Systems



**
This message and any attachments are intended for the
individual or entity named above. If you are not the intended
recipient, please do not read, copy, use or disclose this
communication to others; also please notify the sender by
replying to this message, and then delete it from your system.

The Timken Company
**

Re: SQL Formatting on AIX scripts - SQLDISPLAYMODE

2001-12-03 Thread Ilja G. Coolen 

Well Todd,

This is the TSM default. In order to get the output in the required format
you need to use the -outfile option. This way you will get the output as if
you are at the admin command line.

Like this:
dsmadmc -id= -pa= -outfile= "your select goes
here"


Ilja G. Coolen


  _

ABP / USZO
CIS / BS / TB / Storage Management
Telefoon : +31(0)45  579 7938
Fax  : +31(0)45  579 3990
Email: [EMAIL PROTECTED] 
Intranet
: Storage Web 

  _

- Everybody has a photographic memory, some just don't have film. -


-Oorspronkelijk bericht-
Van: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED]]Namens Pucky,
Todd M.
Verzonden: maandag 3 december 2001 13:44
Aan: [EMAIL PROTECTED]
Onderwerp: SQL Formatting on AIX scripts - SQLDISPLAYMODE


TSM Server: AIX 4.3.3

I have seen this issue addressed in the adsm archives, but never a clear
answer on how to solve it.  I plan to use a script in a cron job on AIX, but
I need to have SQLDISPLAYMODE set to WIDE.  Here is the part of my script
where I attempt to set the SQLDISPLAYMODE to WIDE and issue the query.

dsmadmc -id=xx -pas=xx "set sqldisplaymode wide"
dsmadmc -id=xx -pas=xx "select events.node_name, events.s
tatus, nodes.contact from events, nodes where events.domain_name='SERVER'
and ev
ents.schedule_name='SERVER_0300_0500' and (events.status='Missed' or
events.stat
us='Failed') and events.node_name=nodes.node_name" > $eventtmp

 I have no problems when I issue these commands from the Admin Command Line,
but through the AIX script I always get SQL format of NARROW in the results
of my query.  Any suggestions on how to get the SQLDISPLAYMODE WIDE command
to work in the AIX script would be greatly appreciated.

Thanks

Todd M. Pucky
The Timken Company
Global Information Systems



**
This message and any attachments are intended for the
individual or entity named above. If you are not the intended
recipient, please do not read, copy, use or disclose this
communication to others; also please notify the sender by
replying to this message, and then delete it from your system.

The Timken Company
**