Teradata query parser

Tools

Teradata query parser

I have a huge number of very large, complex queries which I want to deconstruct into their components and store in a database or XML format. I found one program at http://www.sqlparser.com/ but only their Java version supports Teradata, and it seems a little more complex than I really need or want (and I don't know Java).

One goal is to register the various tables and fields that are used, so that I can make a more targeted help file that primarily concentrates on those, and also to recognize if/where deprecated fields or columns are being used.

Another goal is to compare the various queries to each other to find similarities and differences, with the eventual purpose of building a code snippet library for different purposes, and to validate that existing queries are using the same logic for the same purpose - many are as much as a decade old and the systems they report on have changed significantly over that period.
Tags (1)
4 REPLIES
Junior Contributor

Re: Teradata query parser

In dbc.QryLogObjects there's info about tables/columns used within a query, TD13.10 also populates the TypOfUse.

Since TD13 there's a built-in facility to create an XML plan (and store it in DBQL or QCD), simply run an "EXPLAIN IN XML your query" to see what's returned.
I didn't work with that feature yet, but in the manuals you'll find some examples how to extract info using Teradata's XML Services.

Dieter
Enthusiast

Re: Teradata query parser

"EXPLAIN IN XML" may return a very long XML encoded in UTF16, which is longer than 64K bytes, then the output is truncated. Then we can't use such XML.

Is there fix or workaround for this?

Junior Contributor

Re: Teradata query parser

IIRC this limitations has been removed in TD13.10.

Dieter

Enthusiast

Re: Teradata query parser

My XML explain plan output seems to be truncated in 13.10 still. It might be safe for me to establish the session in ASCII mode.