ADSM-L

Re: Sql query for file listings.

2005-03-22 15:34:41
Subject: Re: Sql query for file listings.
From: Ben Bullock <bbullock AT MICRON DOT COM>
To: ADSM-L AT VM.MARIST DOT EDU
Date: Tue, 22 Mar 2005 13:34:26 -0700
        Hmm, You are correct, when I only try to get 2 fields, in my
case HL_NAME and LL_NAME, I get it in the "one line per entry" output
that is easy to parse out. When I add a 3rd field, I get the output in
paragraph form..

        I guess I will look into the TSM ODBC connection and see if I
can extract what I need that way.

Thanks,
Ben 

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
TSM_User
Sent: Friday, March 18, 2005 7:03 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Sql query for file listings.

In testing this I found that if you don't select as many fields the wide
mode gives you what you want if you pipe it to a file. In other words
"select NODE_NAME, HL_NAME from backups > c:\wide3.txt".  Still if you
don't pipe it to a file you get it in "paragraph" form.  It appears
adding the extra fields causes the interpreter to determine it is too
much data for one line. I thought the main problem here might be that
the HL_NAME fields is 1024 characters long.  I tried using the substr
command (ex. select NODE_NAME, substr(HL_NAME,1,25) as short from
backups > c:\wide3.txt) but it still went to the paragraph form.

Maybe someone from Tivoli could tell us what forces it to the different
form in wide mode.  It can't be the number of fields because I ran a
similar select on the stgpools table and it displayed one record all on
one line.

Now you got me curious.

Kyle



Ben Bullock <bbullock AT MICRON DOT COM> wrote:
Hmm,
Couldn't find anything in the archives that would help me on this. SQL
scripting is my weakness.

What I need to do is get a listing of all the files from a node that are
bound to a certain management class. This is not a problem, however I'm
not getting it in a format I can use easily.

The simple SQL query looks something like this:

select NODE_NAME, FILESPACE_NAME, HL_NAME ,LL_NAME from archives where
class_name like 'DBDUMP_8DAY_MC' and NODE_NAME like 'NODE1'

The problem is that on path names longer than 18 characters, it wraps
the output and makes it difficult to use/import/etc.

I've put "set sqldisplaymode wide" on the front, but then the output is
in paragraph form, which also makes it difficult to use in an Excel
spreadsheet (the way the customer would like to use/sort the listings).

What I would like is output with the whole path to the files listed,
like all together as a path. Like this:

NODE1 /DUMP/p05r_1/RDB_BOSQLPROD05R.ISQL
NODE1 /DUMP/p05r_1/master.dump
NODE1 /DUMP/p05r_1/sa.dump
NODE1 /DUMP/p05r_2/model.dump

Anyone a SQL genius out there?

Thanks,
Ben




                
---------------------------------
Do you Yahoo!?
 Yahoo! Small Business - Try our new resources site!