[This topic is migrated from our old forums. The original author name has been removed]
Been using DBVis with Teradata 13+ for nearly a year now -- HUGE improvement over either of Teradata's IDEs (SQL Assistant or Teradata Studio). However I do have to fall back to one of their tools if I want to browse "*macros*".
Basically, a Teradata stored procedure (visible in DBVis) *cannot* return a rowset. A *macro* is a parameterized view, or a stored procedure equivalent which does return a rowset. Obviously this setup is unique to Teradata, I'm not surprised that it is not natively supported by DBVis.
A simple macro would look like
SELECT *
FROM orders
WHERE customerid = :cust_id
I can call a macro via DBVis, however they are not listed anywhere in the object tree, and therefore can't be used if you don't already know they are there. My request is to have them appear in the tree. This is the only no-workaround thing I've found using DBVis with Teradata.
Thanks and keep up the good work!
Hi,
Teradata is one of the databases we would like to look into and add extended support.
Do you happen to know the SQL to get information about the macros? Is it something like:
SELECT *
FROM DBC.TABLES
WHERE TABLEKIND = 'M'
In the upcoming 9.1 version the database profile support (which is the framework used to express what should be listed in the databases tab for a database) has been enhanced to allow extending an existing database profile. Using it, it would be fairly easy to show macros for Teradata.
If you also know how to get the source for a specific macro and its interface (variables) let me know the complete SQLs and we will try extend the generic profile with support for showing at least the basic information about macros.
Regards
Roger
a
anonymous
said
over 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Teradata macros
Yes, the query you suggested works.
SELECT *
FROM DBC.TABLES
WHERE DatabaseName = 'my_db_name' and TableKind = 'M';
Also, TableKind = 'F' works for user-defined functions.
-------
> how to get the source for a specific macro
In the query above, one of the columns is called "RequestText" -- this is a DDL statement starting with CREATE or REPLACE that defines the macro (or function, view, table, etc). To get the parameters for a specific macro, you'd have to parse this statement. I don't see any fields in DBC.TABLES specifically listing parameters and types. I will try to get more info from my admin. Alternatively, you can use the syntax SHOW MACRO my_db_name.my_macro_name and this just returns the "RequestText" field for that specific macro.
Thanks very much!
Edited by: illmatic on Aug 21, 2013 11:47 PM
Roger Bjärevall
said
over 11 years ago
[This reply is migrated from our old forums.]
Re: Teradata macros
HI,
We'll try get access to a Teradata system and try this out. I'll keep you updated.
Regards
Roger
Roger Bjärevall
said
about 11 years ago
[This reply is migrated from our old forums.] [Attachment has been removed.]
Re: Teradata macros
Hi,
Sorry for the late response on this.
The attached profile for Teradata will add Function and Macro object types and DDL viewers for their source.
Save it as HOME/.dbvis/ext/profiles/teradata.xml
Note that it require at least DbVisualizer 9.1.
Then when DbVisualizer is running, open the Connection Properties tab and select "Database Profiles". Check "Manually Choose" and pick the teradata entry in the list, press Apply and then connect.
Regards
Roger
a
anonymous
said
about 11 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Teradata macros
Sorry for the late response on thanking you :) Works great!
The new DDL tab for macros makes me wish it existed for tables, and especially for views and procedures. Any chance you can add that? (viewing the source of a table/view/procedure is about the only reason I ever open Teradata's SQL Assistant IDE -- would be fantastic if this existed in DBVis)
Roger Bjärevall
said
about 11 years ago
[This reply is migrated from our old forums.]
Re: Teradata macros
Hi,
We have an open ticket to add Teradata as a supported database by DbVisualizer. This is yet to be scheduled for inclusion in a release.
The database profile for Teradata previously posted should be fairly easy to extend to accomplish what you are requesting.
I suggest you give it a try adding the new object types your self. The XML file itself is quite self explanatory along with the online documentation about how database profiles in DbVisualizer work.
http://confluence.dbvis.com/display/UG91/Database+Profiles
If you have any questions please feel free to post us and we will assist.
Regards
Roger
anonymous