Creating SQL Views on AS400 v5r4

[kellyj00]

Hello.

I'm new to the world of the as400, but not to the world of SQL and
relational databases. I would like to create a view on our as400
machine, but I understand that creating a view will create some sort of
lock on any 'files' that our RPG code references so they cannot be
modified unless the view is invalidated and recompiled after the
changes are made.

Here's the real problem with this, when I create a view using Iseries
navigator, run SQL Script, create view my_view as (bow wow, woof woof)
The view "my_view" will not show up for the RPG programmers to be able
to gather source code and recompile...something they refer to as
"Function 14'ing" it, if that rings a bell to anyone.

How can I create a View with the basic SQL "create view" statement in
such a way that they can delete and recompile it as we need to modify
our table structure?

Thank you for your help and patience.
Jason

[Karl Hanson]

ke******@gmail.com wrote:

Hello.

I'm new to the world of the as400, but not to the world of SQL and
relational databases. I would like to create a view on our as400
machine, but I understand that creating a view will create some sort of
lock on any 'files' that our RPG code references so they cannot be
modified unless the view is invalidated and recompiled after the
changes are made.

Here's the real problem with this, when I create a view using Iseries
navigator, run SQL Script, create view my_view as (bow wow, woof woof)
The view "my_view" will not show up for the RPG programmers to be able
to gather source code and recompile...something they refer to as
"Function 14'ing" it, if that rings a bell to anyone.

How can I create a View with the basic SQL "create view" statement in
such a way that they can delete and recompile it as we need to modify
our table structure?

Not sure I follow the lock conflict you describe, but using iNav after
creating the view, drill down to the view under:
My Connections
Databases
<rdb>
Schemas
<schema>
Views

Find your view in the list (right pane), right click on it and pick
Generate SQL. In the new window make changes (if desired) and click the
Generate button. From there you can do File-Save As, copy/paste, etc.

To do this programatically, see the Generate Data Definition Language
(QSQGNDDL) API:
http://publib.boulder.ibm.com/infoce...s/qsqgnddl.htm

--
Karl Hanson