0

Hello, I have a very difficult question to ask you. I have loads of queries for different reports and applications and I wanted to separate out columns and associated tables from each of the query. So for example, I have a normal select query with few joints to different tables and I want to get a result in this fashion:

  • Column Name | Table name | Subject Area
  • column data | table data | subject area

Make sense?

flag
Are you looking to query the data dictionary tables for metadata information based on what a query is accessing? Or are you trying to pivot the results of the query? I'm still unclear based on your explanation above. – Rob Paller Mar 16 at 14:38
I made a comment but it disappered – Ali Mar 16 at 14:45
ohh i need to type again now :( Example Select a.columnA,b.ColumnB from subjectarea1.table1 a left outer join subjectarea2.table2 b on a.id = b.id Result: ColumnA - Table1 - SubjectArea1 ColumnB - Table2 - SubjectArea2 Possible? – Ali Mar 16 at 14:46
I don't think this can be done dynamically. – Rob Paller Mar 16 at 15:49
The question is not clear but I think he wants the list of columns that the query is accessing. – Carlos A. Ibarra Mar 17 at 23:43
show 1 more comment

1 Answer

0

If DBQL is set up to log with enough detail in your system, you can use the DBC.QryLogObjects view to determine what database columns are accessed by your query. The tricky part if finding your query in the log. The following technique uses "label queries" around your query to bracket what you want to examine in the log. Make sure you wait 10 minutes or whatever your DBQL flush interval is before running the summary query.

-- These selects should bracket your query
select 'BeginQueryTracking001';
-- your query here:
  Select a.columnA,b.ColumnB from subjectarea1.table1 a left outer join subjectarea2.table2 b on a.id = b.id;
select 'EndQueryTracking001';

-- Wait 10 minutes before issuing this summary query that returns your object names.
-- Make sure that the BeginQueryTracking001/EndQueryTracking001 match what you used above.
select distinct O.objectdatabasename,O.objecttablename,O.objectcolumnname
 from  dbc.qrylog L1
 join   dbc.qrylog L2 
    on L1.sessionid=l2.sessionid
    and L1.starttime < L2.starttime
    and L1.querytext = 'select ''BeginQueryTracking001'';'
    and L2.querytext = 'select ''EndQueryTracking001'';'
join dbc.qrylog M
  on M.sessionid = L1.sessionid
  and  L1.starttime < M.starttime and M.starttime < L2.starttime
join dbc.qrylogobjects O 
   on M.queryid = o.queryid
and O.objectcolumnname is not null
order by 1,2,3
link|flag
Another option to tag the query is to leverage Query Banding. The query band would be captured in DBQL and make pinpointing the query a little easier. Query Banding was first introduced in Teradata 12. – Rob Paller Mar 19 at 3:26

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.