SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Netezza Edit view and show DDL not working

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Netezza Edit view and show DDL not working
Author Message
GeneW



Joined: 10 Apr 2012
Posts: 19
Country: United States

Post Netezza Edit view and show DDL not working Reply with quote
We have Netezza Release 7.2.1.2 [Build 47843] and I am continuing to evaluate using SQL Assistant Version 9.0.152 BETA. When I try to use the right-click context menu option to Show DDL, I get this error:
Quote:
/*
Cannot find source code for "ADMIN"."AF_EXTRACTED".
Check the "DDL Code (Netezza)" query in SQL Assistant Options.
*/

I have checked the DDL Code (Netezza) query in the options - the one that is for Minimum Version 7.1 starts with this:
Quote:
select 'CREATE ' || v.objtype || ' "' || v.schema || '"."' || v.viewname || '" AS' || chr(10) || v.definition
from "$DB_NAME$".DEFINITION_SCHEMA._v_view_xdb AS v
where
v.database = :DB_NAME
and v.schema = :SCHEMA_NAME
and v.viewname = :OBJECT_NAME


then unions to several other queries. This first part does return the DDL code for the view when I substitute the right values for the tokens in the query, but I cannot find a combination that works directly from the right-click context menu. I have even tried eliminating all the rest of the DDL query so I am just testing the DDL query for the view.

To be clear, I have tried un-checking the query options that indicate they are for "Min. Version 7" and leaving only the "Min. Version 7.1" selected, but that doesn't seem to matter. How should these be set? Does it matter that the default is to have all of them selected? Do I have to restart sql assistant after making changes? Are there some other permissions I need in the Netezza database to make this work? Since the above query works when I manually replace the tokens I would think I have permissions needed.
Tue Oct 11, 2016 11:59 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Here is how this issue can be fixed, Note it's Netezza version specific.

Open SQL Assistant options dialog. Select DB Options tab. Select DB Queries section (left bottom corner). In the expanded section locate DDL Code (Netezza) query for your version. At the very end of the query text comment out the last UNION ALL SELECT statement for sequences using /**/ comments. Click Apply button to save changes.

Fyi, the issue is caused by Netezza query processor when joining _v_sequence_xdb and _vt_sequence system views.

The above workaround has been already applied to SQL Assistant yesterday's release.
Tue Oct 11, 2016 12:19 pm View user's profile Send private message
GeneW



Joined: 10 Apr 2012
Posts: 19
Country: United States

Post Reply with quote
I installed latest version 9.0.162 (Professional Edition) and it looks like I can now get DDL for views, but using right-click context option on tables returns an error that looks like this:
Quote:
/*
Cannot find source code for "ADMIN"."XXX_HISTORY".
Check the "DDL Code (Netezza)" query in SQL Assistant Options.

*/

Looking at the DB Queries for DDL, it looks like it is only designed to get view definitions. When I run this code,
Code:
select 'CREATE ' || v.objtype || ' "' || v.owner || '"."' || v.viewname || '" AS' || chr(10) || v.definition AS CODE, v.viewname
from "MYDB".._v_view_xdb AS v ORDER BY v.viewname

I get back all of the view definitions I would expect, but nothing about tables. The explorer seems able to get details about tables - and I see popup info when the mouse cursor hovers over a table name, but clicking on the Code option in that popup generates the same error.
Tue Oct 11, 2016 6:06 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7838

Post Reply with quote
Netezza and MS Access are the two supported databases that don't provide a method for retrieving table DDL. that's why you get that message.

If you have an external command line utility that can reverse-engineer table DDL, you can configure SQL Assistant to call it internally to generate the script on demand.
Tue Oct 11, 2016 7:45 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.