parse sqltextinfo column in dbc.dbqlsqltbl

Database
Enthusiast

parse sqltextinfo column in dbc.dbqlsqltbl

Hi,

Has anyone parsed the sqltextinfo column of dbc.dbqlsqltbl? I have a requirement where I need to do analysis of what kind of sqls are running on our production system.

We do have querybanding, but that only shows limited information. We need information like which queries hit recent data and which ones touch historical data.

I'm thinking along the lines of parsing the sqltextinfo column somehow to find what queries use the date columns and then further conclude whether the "where" clauses of those queries use the date column to do some kind of equality or range operation or not.

To achieve the above, I need some way to parse the sqltextinfo column and extract out the where clauses from it.

For eg: if the sql looks like:

select col1,col2 from tbl1 where dt_col1='2010-01-01' and col3='x'

So when the above sql goes through the parsing program, I expect an output similar to the following:

" Table: tbl1,columns in select: col1,where clause:dt_col1='2010-01-01':col3='x' "

With the above output, I can then use substr to extract out the where clause and further work towards my requirement.

Has anyone done similar kind of anlaytics in the past?

Regards,

Suhail

Tags (1)
2 REPLIES
Enthusiast

Re: parse sqltextinfo column in dbc.dbqlsqltbl

It would be easier to export the QueryID, SqlTextInfo into a file; and then use grep/ awk in Unix to extract in the required format.

Enthusiast

Re: parse sqltextinfo column in dbc.dbqlsqltbl

It will be a very complex awk command. There are several types of complex queries and the where clause can be anywhere. Not sure if this is an ideal solution to achieve what I need.

-Suhail