0

I'm working with CA (Broadcom) UIM. I want the most efficient method of pulling distinct values from several views. I have views that start with "V_" for every QOS that exists in the S_QOS_DATA table. I specifically want to pull data for any view that starts with "V_QOS_XENDESKTOP."

The inefficient method that gave me quick results was the following:

  1. select * from s_qos_data where qos like 'QOS_XENDESKTOP%';
  2. Take that data and put it in Excel.

  3. Use CONCAT to turn just the qos names into queries such as:

    SELECT DISTINCT samplevalue, 'QOS_XENDESKTOP_SITE_CONTROLLER_STATE' AS qos FROM V_QOS_XENDESKTOP_SITE_CONTROLLER_STATE union

  4. Copy the formula cell down for all rows and remove Union from the last query as well as add a semicolon.

This worked, I got the output, but there has to be a more elegant solution. Most of the answers I've found related to iterating through SQL uses numbers or doesn't seem quite what I'm looking for. Examples: Multiple select queries using while loop in a single table? Is it Possible? and Syntax of for-loop in SQL Server

S3S
  • 24,809
  • 5
  • 26
  • 45
Dan Gill
  • 3
  • 1
  • 3
    You should create one view for all QOS with a column. Having a separate row per QOS is not a good idea -- as you have found out. – Gordon Linoff Dec 06 '18 at 20:01
  • Is it possible to combine these `V_(some QOS from S_QOS_DATA)` into a single view so you don't have to UNION views? OR... is it the architecture of this system that each `V_(some QOS from S_QOS_DATA)` is itself built on top of a single table for that holds data for that QOS (totally not familiar with this CA broadcom UIM datamodel). If that is the case that each QOS has it's own table and these `V_` views help align columns, then a UNION is probably your best bet here, but it is ugly. – JNevill Dec 06 '18 at 20:09
  • @JNevill Yes, each V_ is a join of an RN_, HN_, and DN table that contains the raw, hourly, and daily data along with the S_QOS_DATA table. As an example RN_10567 could be the table that contains all raw data for the QOS_XENDESKTOP_SITE_CONTROLLER_STATE qos. – Dan Gill Dec 06 '18 at 20:19
  • 1
    I think I agree then with @GordonLinoff suggestion. Perhaps you could share some of those `V_` views and we could suggest a way to combine them into a `V_QOS_XENDESKTOP` view that has all of the XENDESKTOP QOS data in it without resorting to a UNION. – JNevill Dec 06 '18 at 20:24
  • I was thinking along the lines of SELECT * FROM sys.views ; and then in where you put where [name] like '%your_condition_here%'; but this is only part of solution for you. Step 2) a dynamic query to build all the select *'s for you. And then you just fire that. – junketsu Dec 06 '18 at 20:31
  • @GordonLinoff, if I attempt to create anything I have to use standard SQL (no T/SQL or anything of the sort). I don't actually own the database and I've only been given read permissions when it comes to using SQL Management Studio, but the product does allow me to run standard SQL commands via a web interface. Here is a link to the script that creates the original views: https://communities.ca.com/docs/DOC-231164743 – Dan Gill Dec 06 '18 at 21:12

1 Answers1

0

The most efficient method to do what you want to do is to do something like what CA's scripts do (the ones you linked to). That is, use dynamic SQL: create a string containing the SQL you want from system tables, and execute it.

A more efficient method would be to write a different query based on the underlying tables, mimicking the criteria in the views you care about.

Unless your view definitions are changing frequently, though, I recommend against dynamic SQL. (I doubt they change frequently. You regenerate the views no more frequently than you get a new script, right? CA isn't adding tables willy nilly.) AFAICT, that's basically what you're doing already.

Get yourself a list of the view names, and write your query against a union of them, explicitly. Job done: easy to understand, not much work to modify, and you give the server its best opportunity to optimize.

I can imagine that it's frustrating and error-prone not to be able to put all that work into your own view, and query against it at your convenience. It's too bad most organizations don't let users write their own views and procedures (owned by their own accounts, not dbo). The best I can offer is to save what would be the view body to a file, and insert it into a WITH clause in your queries

WITH (... query ...) as V select ... from V
James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • Thanks. Whenever I do any task once, I try to look for a way to script it so if I need to do it again I can just change one parameter and it will spit out similar results for a different input. I don't know if this will be needed again, but if it was I wanted to find the cleanest and most efficient way to handle that. You make a good point about the CA script, I didn't even build in the logic to replace special character when I created my version since I knew xendesktop didn't have any in the name. I think you're right that if I truly want to script this out, I should look at what they have. – Dan Gill Dec 06 '18 at 22:48