Group by and substring

[Desdic]

Hi,

Now its more of a sql question (but it works on postgresql) ..
We have our node names based on the customer and a uniq machine id. Now my problem is that I cannot group by on a substring

Now I have tried the following:

select sum(PHYSICAL_MB)/1024 as GB_in_Storagepool, substr(node_name,1,posstr(node_name,'-')-1) from OCCUPANCY where stgpool_name='TAPEPOOL' group by substr(node_name,1,posstr(node_name,'-')-1)

select sum(PHYSICAL_MB)/1024 as GB_in_Storagepool, substr(node_name,1,posstr(node_name,'-')-1) as xxx from OCCUPANCY where stgpool_name='TAPEPOOL' group by xxx

Normal group by is working fine but its the group by and substring/alias that does seem to work .. does anyone have a workaround for that ?

 

[DanGiles]

Hopefully someone will pipe in and tell me I'm wrong, but I believe you are SOL. Being able to do full-fledges SQL queries is one of the things I am hoping for when they cut over to DB2 for the TSM database!

 

[sgaudet]

Customized TSM select

I know I'm late. I stumbled here by chance today, and got especially curious when I saw Dan Giles had been by.

I thought this was an interesting challenge - and while now I understand why it doesn't work in TSM 5.x, I had an idea that might help you...

Assuming you either do not use any client option sets, or have just 1 such client option set per customer, you could opt to define 1 option set per customer making this name the customer identifier, and assigning this unique client option set to each nodes owned by the matching customer.

select sum(PHYSICAL_MB)/1024 as GB_in_Storagepool, option_set from OCCUPANCY,nodes where occupancy.node_name=nodes.node_name and stgpool_name='TAPEPOOL' group by option_set

This would result in exactly what you were trying to do.

Alternatively, assign all the nodes belonging to a given customer to a unique domain name, and use "domain_name" in place of each occurences of "option_set" above.

Dan, if you see this, "Hi" to ya!

Serge