0

Am trying to load the output of show stats into a table post which i am thinking to apply some regular expression function on it.

is there a way to load the output of show stats into a table in TERADATA ?

Aravindh
  • 11
  • There's no easy SQL way (besides a Java-XSP showed by Glenn McCal https://downloads.teradata.com/extensibility/articles/running-unsupported-queries-from-a-stored-procedure) to work with the result of a SHOW. The client has to retrieve the result and convert it into an INSERT. Would be simpler using `SHOW IN XML STATS VALUES...` which returns a single LOB. As you probably want to extract the list of biased values, etc., it can be processed by XPath/XQuery/etc. instead of RegExes – dnoeth Nov 03 '19 at 10:59
  • Thank you so much Dnoeth , first - i would like to take a moment to thank you for all the excellent posts/answers for all Teradata questions in various forums !! ( AM a huge fan of your answers) Coming back to the Answer for this question - SHOW IN XML is definitely an easier way , but the goal is to fetch the most occuring value of a field into a table. So am trying to use SHOW stats & load it into a table , is it even possible INSIDE STORED PROCEDURE ? – Aravindh Nov 04 '19 at 07:50
  • 'Consumer' -- whatever is enclosed within Field value should be loaded INTO a field in a table .. – Aravindh Nov 04 '19 at 08:12
  • Is there a DBC table where we can find the most occurring column after collecting stats on a column ? – Aravindh Nov 04 '19 at 08:22
  • Inside an SP requires the Java-XSP approach. `dbc.StatsTbl`stores all stats details, but in a BLOB (and the layout is not properly documented). The min/max/modal value returned by SHOW STATS VALUES can be retrieved using my StatsQuery, but only for numeric/datetime columns. See https://support.teradata.com/community?id=community_blog&sys_id=1298df271b9bfb00682ca8233a4bcbb1 I just notice there's only the latest source code, but the older description file is missing, you can still download it at https://downloads.teradata.com/sites/all/files/stats_td14_20130830.zip – dnoeth Nov 04 '19 at 11:12
  • Thank you Dnoeth , let me try !! Thanks – Aravindh Nov 06 '19 at 10:12

1 Answers1

0

You could write a python script that executes the query and applies regular expression functions, then insert the results back into Teradata. Teradata has a REST API called Query Service that you could install and connect to the database with.

Buhlahkay
  • 11
  • 1
  • 4