CODEWITHSUNDEEP
sqlviewnetezzamicrostrategy
JoPinsy
JoPinsy

Reputation: 217

Source table for view QRYSTAT in Netezza

I'm currently working in a Netezza environment on Aginity Workbench and I was planning on using some of the columns from the Management View _V_QRYSTAT to populate a graph in MicroStrategy.

Unfortunately, I cannot get MicroStrategy to recognize any of the columns in _V_QRYSTAT. I don't think that it can read columns from views, and I figured that the best way around this would be to find out which table the _V_QRYSTAT view is getting its data from, but I can't figure out a way to find the source table of a view in Netezza. Does anyone know a method that can be used in Netezza on Aginity Workbench for locating the source table of a view (specifically _V_QRYSTAT)?

I'm very new to SQL, Netezza and MicroStrategy, so I apologize if I am being unclear. Let me know if further elaboration is needed.

Upvotes: 0

Views: 1020

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

I'm pretty sure that MicroStrategy will recognize and work with views, but to answer your question directly, you can see the view definition by querying the _V_VIEW system view.

select definition from _v_view where viewname = '_V_QRYSTAT';
                                                                                                                                                                                                                                                                                                                                                       DEFINITION                                                                                                                                                                                                                                                                                                                                                       
---------
SELECT 
        QS.QS_SESSIONID, 
        QS.QS_PLANID   , 
        QS.QS_CLIENTID , 
        CASE 
                WHEN ((VU.OBJID NOTNULL 
                                ) 
                                OR ("CURRENT_USEROID"() = 4900 
                                ) 
                        ) 
                THEN QS.QS_CLIIPADDR 
                ELSE "NAME"(NULL::"VARCHAR") 
        END AS QS_CLIIPADDR, 
        CASE 
                WHEN ((VU.OBJID NOTNULL 
                                ) 
                                OR ("CURRENT_USEROID"() = 4900 
                                ) 
                        ) 
                THEN QS.QS_SQL 
                ELSE TEXT(NULL::"VARCHAR") 
        END AS QS_SQL , 
        QS.QS_STATE   , 
        QS.QS_TSUBMIT , 
        QS.QS_TSTART  , 
        QS.QS_PRIORITY, 
        QS.QS_PRITXT  , 
        QS.QS_ESTCOST , 
        QS.QS_ESTDISK , 
        QS.QS_ESTMEM  , 
        QS.QS_SNIPPETS, 
        QS.QS_CURSNIPT, 
        QS.QS_RESROWS , 
        QS.QS_RESBYTES 
FROM 
        ((DEFINITION_SCHEMA."_T_QRYSTAT" QS 
LEFT JOIN DEFINITION_SCHEMA."_T_SESSCTX" SS ON ( 
                (QS.QS_SESSIONID = SS.SESSION_ID 
                ) 
        )) 
LEFT JOIN DEFINITION_SCHEMA."_V_USER" VU ON ( 
                (SS.SESSION_USERNAME = VU.USERNAME 
                ) 
        ));


(1 row)

This will almost certainly take you a couple of recursions down, as the view you are interested in is based on either views as well as tables.

Upvotes: 1

Related Questions