Describe Sql View

[account locked]

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)
drbohner@existi nglight.net

[Dave Hughes]

Re: Describe Sql View

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.

[Karl Hanson]

Re: Describe Sql View

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