Identify the queries

Database
Enthusiast

Identify the queries

While working on multiple queries for tuning... How i can identify the queries while getting the DBQL details

For ex: I have 20 queries from developer, need to provide recommendations for performance. I need to run thru multiple iterations, making changes, again run, & check for CPU, Spool, rows etc.

Is there any easy ways i can add like Query1, Query2 in querytext or any other way to identify the query while working on them simultaneously.

9 REPLIES
Enthusiast

Re: Identify the queries

I am not able to understand clearly, what you want to do. Do you use viewpoint? or DBQLs....

Supporter

Re: Identify the queries

Check the queryband option - you can set for each query a different queryband parameter.

This will be visiable in DBQL.

Or add a comment to the query text like

select 
-- my query 1
col1,
col2
from mytab;

in this case you can see it from the querytext in dbql.

Enthusiast

Re: Identify the queries

Thanks ulrich !

But these comments are not visible in querytext/SqlTextInfo in DBQL...Thats the problem...

Do you think this could be due to some logging option on my side?

Could you pls let me know the quick way to set queryband for the query?

Raja: I use both VP & DBQL.  What i was looking like ... I have many queries to tune.

Those take long time to run. I'll run those at sametime and i need to see their resouces consumption. And i need to do many iterartions in order to make chages & again see the improvement.

 hope i am not confusing you  :-)

Supporter

Re: Identify the queries

SET QUERY_BAND = 'sqltype=perftest;sqlnr=1;runid=1;' UPDATE for SESSION;

and you need to run this before each SQL and change the numbers;

try 

/*my query 1 */

at least I see sometimes SQL in DQBL with comments in it...

Teradata Employee

Re: Identify the queries

Hello Gian,

There is an option which needs to be enabled/disabled to ensure that comments are also getting logged into DBQL as part of SQL Text. The exact option cannot be recalled by me now. I encountered the same scenario as you & used the comments to identify the different iterations.

Check the options (Basic & Advanced) available in the ODBC Setting.

Thanks,

Smarak

Enthusiast

Re: Identify the queries

Thanks Smarak. Will check that.

Enthusiast

Re: Identify the queries

 couldn't find such option.

Anybody...How we can see the comments made within the query when looking at 'QueryText' in DBQL?

Supporter

Re: Identify the queries

As above statements indicate it also depends on how you connect to the DB - but so far you didn't say which tool you use, how you connect (.net, ODBC, JDBC) etc.

Did you try the queryband option?

Enthusiast

Re: Identify the queries

I use ODBC.

Query band worked fine,

Thanks ! Ulrich. That solved my purpose.

Comment thing i wanted to check just in case...