Create View DDL

Defining a view in DDL

A view is a virtual table, defined by a SELECT statement. The definitionThe CREATE VIEW statement creates a view on one or more tables or views at the current server. The definition must include its name and the names and attributes of its columns.

SYSTABLES identifies tables: Long names are in the column table_name, while ten character (short) names are in the column table_name. Table_schema represents the library name. 

SELECT system_table_name FROM qsys2.systables WHERE table_name = 
'OAS_ELEMENT' and table_schema = 'CODA12TST'                     

SELECT system_table_name, TABLE_NAME FROM qsys2.systables WHERE
table_schema = 'CODA12TST'

Syntax

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. The view consists of the set of rows and columns that the SELECT statement in the view definition returns each time you refer to the view in a query. 

>>-CREATE VIEW--view--+-------+--+------------------+--AS------->
                      '-owner-'  |    .-,------.    |       
                                 |    V        |    |       
                                 +-(----column-+--)-+       
                                 '-OF TYPE row_type-'       

                                  (1)                          
>--| Subset of SELECT Statement |------+-------------------+---><
                                       '-WITH CHECK OPTION-'

Sample View

CREATE VIEW new_books 
AS 
SELECT 
    title, 
    rating, 
    isbn, 
    published_date
FROM 
    books
WHERE 
    published_date > '2018-01-01';


Views are a handy tool to use in to increase program performance. Since the view has already performed the SELECT process, using the VIEW in an RPG program means the program does not have to perform the data selection--a sort of write-once-use-anywhere approach. Create as many Views over data in as many ways as they data might be accessed for an application suite. It is more useful than writing a myriad similar extract and select statements in RPG, or like statements in multiple RPG programs with embedded SQL.