View Create Option
The Create View option lets you create a view through a series of easy to use panels.
The Create View option permits the creation of a view through a series of easy to use panels. These panels include a Table selection panel and Column Selection help. A typical Create View session starts with a strategy screen, moves to the Create View screen, and can include a Table selection panel.
View Create Screen
The View Create screen provides an easy way to supply the information necessary for view creation.
- CAPSThe status of the CAPS feature.
- NULLSThe status of the NULLS feature.
- View Name and Select StatementThe default SELECT statement used to create the view.
- Check OptionThe Check option enables you to add the DB2 WITH CHECK OPTION statement to the view syntax. This option indicates all inserts and updates against the view are to be checked against the view definition (the search condition of the WHERE clause) and rejected if the inserted or updated row does not conform to that definition. If the view does not have a WHERE clause this is ignored.
- CMD (Command)Enter ISPF line commands here to insert or update the SELECT statement.
Enter the View Name and Creator ID in the appropriate fields. If this information was supplied in the Item Name and Creator fields of the header, the information is carried to this screen.
The syntax for creating a view requires a SELECT statement that begins AS SELECT. The boilerplate statement provided with the Create View screen is
( @ ) AS SELECT @ FROM
The at sign (@) serves as a placeholder for column names selected from the list of columns in the separate column scrollable area.
The SELECT statement provided as a boilerplate for all SELECT statements includes two at signs. The first (before the SELECT) will hold the place of the name assigned to the columns in the view. The second (after the SELECT) holds the names of the columns within the table. Include @ signs anywhere in the SELECT statement to hold the place of column names. If there is no @ sign and columns are selected, the columns will be placed in the SELECT statement before the FROM clause of the first SELECT.
You can access a table selection list by entering table selection criteria with PARSE on. PARSE on is the default. The asterisk (*) is not valid.
For example, to get a listing of all tables created by USER1, enter this SELECT statement:
( @ ) AS SELECT @ FROM USER1.%
A table selection panel will appear. Tables created in batch mode in the current session are listed at the top of the Table selection panel and CREATE appears in the Database column. They are selected like any other table. Select the table from which columns should be selected. The table name enters the SQL statement and the column names appear in the column scrollable area. (When tables are selected, the column listing at the bottom also adjusts.)
Columns are displayed in a separate scrollable area at the bottom of the screen. The Column Area displays the column names for any tables that appear in a FROM clause in the SELECT statement. The Column Area is scrolled using the ISPF left and right commands (F10 and F11). For information about the fields, press F1 (Help).
- Scrolling Data– Use F7 and F8 to scroll the data in the SQL area. Use the F10 and F11 to scroll the data in the column scrollable area.
- Selecting Columns– Select columns from the column scrollable area by enteringSnext to each column to select. The column names will be placed in the SELECT statement wherever a place holder (@) appears, in accordance with the rules outlined in the previous section. If a column is added to the SELECT portion of the statement using the @ place holder, the FROM TABLE portion of the statement must still be updated. If a column is selected without using a place holder, the column name will be added to the column list with the SELECT column and FROM table portion of the statement updated automatically.
- Using Correlation Letters – If correlation letters were included in the SELECT statements, the correlation letter can be used as an abbreviation for the table name in subsequent SQL. CA RC/Update will also refer to table names using correlation letters, if specified.
- Using Column Numbers– Every column displayed in the Column Area is assigned a number. Use this number in the SELECT statement as a column abbreviation. Use the column number preceded by a colon. When Enter is pressed, the column abbreviation is expanded to the full column name.
- Using ALL Command– The ALL command can be used to SELECT all the columns of a table.
There are many commands to assist with a View Creation session. A brief description is presented here.
- CAPSWorks as a toggle, turning CAPS lock on and off.
- COLS ON or OFFControls the display of a second scrollable area at the bottom of the screen called the Column Area.
- FORMATLets you format the view text into a more readable format with indentation.
- TEXTIs intended for fast entry of text using fast typing techniques. When the TE command is entered, the CMD area is removed and automatic cursor skip will occur upon reaching the end of the text line. This permits quick entry of SQL statement text without regard to cursor position. When Enter is pressed, TE is removed and the user is placed back into standard text entry. If words are split between lines, and PARSE is on, the split will be fixed when Enter is pressed.
- WORD WRAPTurns word wrap on and all lines are restructured to fit as much on a line as possible. Enter the WW command to reflow fragmented statement text into a uniform text lines.
- WORD SPLITSeparates the SQL statement text into tokens and places each token on a separate line. After entering the WS command and performing any edits, enter the WW (Word Wrap) command to reflow the tokens into uniform text lines.
- NULLSWorks as a toggle, turning on and off the NULLS feature. When NULLS is on, space at the beginning or end of the line is considered null. When NULLS is off, space at the beginning or end of the line is considered blank spaces. By default, NULLS is ON.
- PARSE ON or OFFTurns on and off the parsing feature of the editor. When PARSE is on, the entered SQL statement is parsed looking for special characters.
Confirming the Creation
The Creation Confirmation screen enables users to accept, edit, or reject the DDL to be used to create the object.