CODEWITHSUNDEEP
query-optimizationnetezzadatabase-tuning
Some Newbie
Some Newbie

Reputation: 1089

Write a tool to programmatically check Netezza (Pure Data) for skew and get query plan

I am pondering on the idea of writing a .NET tool that connects to a Netezza box with the following functionality: 1. Check distribution/skew of an input list of tables 2. Check query plan of an input query

I know can do that using Aginity Workbench and/or NZADmin, but I would like to know if there are any way to do that in code so that I can do some automated tuning?

I checked online for answers but it seems like there are very little information on how to do that or if it is even possible.

Upvotes: 0

Views: 1354

Answers (2)

ScottMcG
ScottMcG

Reputation: 3887

You can also use the myriad scripts under /nz/support/bin as great templates to draw from.

  • nz_skew
  • nz_plan
  • nz_view_plan_file
  • and many others

Upvotes: 1

r.m
r.m

Reputation: 374

It's possible, but it will likely involve quite some work.

Aginity uses Netezza's flavor of SQL to communicate with Netezza. You could write your own client in the same way.

For example, to get the query plan of a query, use "EXPLAIN VERBOSE"

EXPLAIN VERBOSE SELECT * FROM foo;

to get the skew, run this query

 select count(*) , datasliceid from mytable group by datasliceid order by datasliceid;

and so forth..

Even if Aginity or nzAdmin use undocumented features (using system views to get HW status and such), it's possible to do the same from .Net. You could try tracking down what exactly they do by enabling odbc tracing or by enabling logging on the host.

Upvotes: 3

Related Questions