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.
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.