By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,558 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,558 IT Pros & Developers. It's quick & easy.

Describe Sql View

P: n/a
I have a situation where an SQL View was created over several table -
and the library containing the SQL view is going to be refreshed.
When the refresh occurs we will lose the SQL view.

I do not know the syntax that was used to create the SQL View - and
don't know what command will tell me the syntax. I would have hoped
"Describe Table" would have told me - but it doesn't seem to be the
right command.

This is on an iseries

(db)
dr******@existinglight.net

Aug 15 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Wed, 15 Aug 2007 23:22:49 +0000, account locked scribbled:
I have a situation where an SQL View was created over several table -
and the library containing the SQL view is going to be refreshed. When
the refresh occurs we will lose the SQL view.

I do not know the syntax that was used to create the SQL View - and
don't know what command will tell me the syntax. I would have hoped
"Describe Table" would have told me - but it doesn't seem to be the
right command.

This is on an iseries
I think the following query will give you the information you need:

SELECT VIEW_DEFINITION
FROM QSYS2.SYSVIEWS
WHERE VIEW_OWNER = 'VIEWSCHEMA'
AND TABLE_NAME = 'VIEWNAME'

(obviously replace 'VIEWSCHEMA' and 'VIEWNAME' with appropriate values)

VIEW_DEFINITION is a huge VARCHAR(10000) column which contains "the query
expression portion of the CREATE VIEW statement" according to the i5/OS
InfoCenter. In other words I'm guessing it's missing the "CREATE VIEW
viewname AS" bit, but otherwise should be complete.
HTH,

Dave.
Aug 16 '07 #2

P: n/a
Dave Hughes wrote:
On Wed, 15 Aug 2007 23:22:49 +0000, account locked scribbled:
>I have a situation where an SQL View was created over several table -
and the library containing the SQL view is going to be refreshed. When
the refresh occurs we will lose the SQL view.

I do not know the syntax that was used to create the SQL View - and
don't know what command will tell me the syntax. I would have hoped
"Describe Table" would have told me - but it doesn't seem to be the
right command.

This is on an iseries

I think the following query will give you the information you need:

SELECT VIEW_DEFINITION
FROM QSYS2.SYSVIEWS
WHERE VIEW_OWNER = 'VIEWSCHEMA'
AND TABLE_NAME = 'VIEWNAME'

(obviously replace 'VIEWSCHEMA' and 'VIEWNAME' with appropriate values)

VIEW_DEFINITION is a huge VARCHAR(10000) column which contains "the query
expression portion of the CREATE VIEW statement" according to the i5/OS
InfoCenter. In other words I'm guessing it's missing the "CREATE VIEW
viewname AS" bit, but otherwise should be complete.
Another option is using iNav. In the left pane:
My Connections
<system>
Databases
<rdb>
Schemas <right click to add yours to list>
<your schema/library>
Views
<your view>

In the right pane right click on your view and select Generate SQL...
Then click the Generate button.

--
Karl Hanson
Aug 16 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.