Problem getting full Netezza view definition using toad data point

Rao_M · September 25, 2015, 2:50pm

We are currently using Aginity workbench for Netezza and switching to Toad data point. Using Toad data point base 3.7, I am not able to get the full view definition. How to fix this issue? Thanks.

RyanJohnson · September 29, 2015, 1:47pm

Hello! Sorry for the wait!

How are you attempting to get the view definition within TDP? Are you looking in the script tab?

Rao_M · September 29, 2015, 2:19pm

Yes, I look at the script tab. I can get the entire view definition when i connect to UAT or Prod servers. When i connect to dev server, i only see first two lines like:

CREATE VIEW VIEWNAME

AS

thats it. Not sure why it does not display entire view definition. I use the same netezza sql ODBC driver, setting and options all same for all connections. May be something to do with server settings.

Also I noticed another issue. There is no way i can get synonym definition in any environment. TDP has lot of features, but looks like it has some limitations when it comes to Netezza connection.

GregDavis11009 · September 29, 2015, 2:24pm

I have seen this type of problem with permissions on the server for SQL server where Toad will not display the script for an area that I did not have the correct permissions. Check with your DBA.

RyanJohnson · September 30, 2015, 5:28pm

Can you try running this query and let us know what you get back?

select DEFINITION from _V_VIEW where OBJTYPE=‘VIEW’ and OWNER = ‘{0}’ and VIEWNAME = ‘{1}’

Replace {0} and {1} with the owner and name of the view, respectively.

Rao_M · October 1, 2015, 7:11am

Hi Ryan. yes it (querying system catalog) works. This is alternate way of getting the view definition instead from the script tab in view details. Also for synonym, there is no way to get definition. So i had to use something like.

select 'CREATE SYNONYM ‘||SYNONYM_NAME||’ AS '||

REFDATABASE||’…’||REFOBJNAME||’;’

from _V_SYNONYM

where SYNONYM_NAME = ‘synonym name’

and OWNER = ‘ownername here’

AND OBJTYPE = ‘SYNONYM’;

RyanJohnson · October 1, 2015, 5:22pm

Sorry, if you could clairfy: Does the query I give you return the correct results while on the dev server? Or does it give you what you see in the Script tab?

Also, I am afraid to say, we do not have time this release to fix synonyms. I did make a task for this under QAT-6543

Rao_M · October 2, 2015, 8:19am

Yes, the query you gave me returns full view definition in dev environment. Script tab returns only the first two lines.

RyanJohnson · October 2, 2015, 10:27am

Okay, we need to put in additional logging to identify the issue. Please create a support ticket at support.quest.com/create-service-request so we can get in touch with you.

lance.christenson · October 2, 2015, 10:40am

OK, done. Ticket number 3002468

From: Ryan Johnson [mailto:bounce-RyanJohnson@toadworld.com]

Sent: Friday, October 02, 2015 10:29 AM

To: toaddatapoint@toadworld.com

Subject: RE: [Toad Data Point - Discussion Forum] Problem getting full Netezza view definition using toad data point

RE: Problem getting full Netezza view definition using toad data point

Reply by Ryan Johnson

Okay, we need to put in additional logging to identify the issue. Please create a support ticket at
support.quest.com/create-service-request so we can get in touch with you.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad Data Point - General notifications altogether.

Toad Data Point - Discussion Forum

Flag
this post as spam/abuse.

RyanJohnson · October 2, 2015, 10:54am

Lance, I apologize, but I believe you are in the wrong thread ;). I was talking about in regards to a Neteeza issue.

lance.christenson · October 2, 2015, 11:16am

Ooops, my bad. This is my first week in the discussion groups. The ticket can be cancelled.