Re: Sql query for file listings.

        Ahh, yes, I failed to read the fine print on that post. I
believe that Andy has found the solution for me.

Thanks,
Ben
 

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

You could use the ODBC but if you look at Andy's post.  If you start the
dsmadc with the option -commadelimited then the output stays on one line
all together no matter how many fields you use.

Kyle

Ben Bullock <bbullock AT MICRON DOT COM> wrote:
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 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!

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com