|The Table Create Utility provides a user with an interface for defining |and creating SQL tables. Column names and definitions can be entered on |the screen as well as other table attributes and data needed to create a table |(for example, DBSPACE name).
|The Table Create Utility has the following features: |
|The Table Create Utility, SQLTABCR, provides a means to interactively |define and create a DB2 Server for VM database table. The utility is |reached by choosing the 'TC' option from the Database Utilities Menu |or by entering 'SQLTABCR' on the CMS command line and pressing |Enter.
|When the Table Create Utility is invoked, the following menu is |displayed:
|Figure 194. Table Create Menu
+--------------------------------------------------------------------------------+ | ********************** SQLTABCR TABLE CREATE UTILITY ******************** | | Database ==> SQLDBA | | CREATOR ==> TABLE NAME ==> | | OWNER ==> DBSPACENAME ==> | | DRAW TABLE ==> | | | | Mod Colno Column Name Column Type Column Length Nulls (1/0) | | --- ----- ------------------ ---------------- ------------- ----- | | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | | GRANT SELECT TO PUBLIC ==> 0 (1=YES,0=NO) DATA CAPTURE => NONE | | COMMENT ON TABLE ==> | | Page 1 of 1 | | PF: 1 Help 3 QUIT 5 Search/List 7 Bkwd 8 Fwd 9 DRAW 10 PROCESS | | ENTER PROCESS Current Screen 11 CLEAR Definition 12 COLUMN Help | | | +--------------------------------------------------------------------------------+ |
|The Database field contains the name of the last database used. |The CREATOR and TABLE NAME must be filled in prior to choosing PF10 to process |the table create statements. The qualified table name |(CREATOR.TABLE NAME) cannot exist in the database. The OWNER and |DBSPACENAME must also be filled in prior to choosing PF10, and must exist in |the database.
|The DRAW TABLE field can be used at any time during the processing. |The value specified must be in the form CREATOR.TABLE NAME (without any |quotes even if there are imbedded blanks in the table name). When a |value is placed here on the first table create screen, the program will |display the table values, as retrieved from the system catalog, as though they |had just been entered by the user. When a value is placed in this field |after any column definitions have been processed by the program, a |confirmation message is issued because the process erases all previously |entered column definitions and places the user back on the first panel with |the values retrieved from the drawn table. Press PF9 to process the |DRAW Table command.
|Press PF5 at any time to enter the Search & List tool of Control |Center. The Search & List tool can be used to find a DBSPACE for |the new table or the name of a table to use with the Draw option. When |you are in the Search & List tool, use PF3 or PF4 to exit the Search & |List tool and return to the current screen of the Table Create Utility.
|The optional parameters at the bottom of the display can be filled in as |desired. The COMMENT ON TABLE field can be used to enter up to 53 bytes |of data for the REMARKS field of the SYSCATALOG entry for the newly created |table. The DATA CAPTURE field can have the value NONE, which is the |default, or CHANGES for those databases at level 5.1 or greater. |A GRANT SELECT to PUBLIC will be issued by the program if the value "1" is |specified in that field.
|These fields are used to define the table to be created: |
|The following are optional parameters: |
|Each data column to be defined for the table is specified on a row on the |CREATE TABLE menu. Each row on the menu must be completed (must have a |column name, type, and length if required) before you can scroll forward; |no imbedded empty rows are allowed. Each CREATE TABLE entry menu has |space to enter data for 10 columns; when 10 entries are made, you can |scroll forward to make the next 10 entries. Before scrolling, the |program checks the entries for correctness.
|When you scroll away from a menu on which you have entered data, the SQL |default length for a specific data type will be used if the LENGTH field is |blank (for example, CHARacter fields default to length 1). The default |lengths are described in the DB2 Server for VSE & VM SQL |Reference manual.
|Screen rows are scanned from the top to the bottom. If any errors |are detected, an error message is issued. When processing resumes, the |top-to-bottom scan restarts at the top of the current screen.
|PF12 can be used to view the column length, values allowed and defaults |used for each column type. This information is extracted from the |DB2 Server for VSE & VM SQL Reference manual for online help.
|The following is a sample of a small table definition:
|Figure 195. Sample Table Definition
+--------------------------------------------------------------------------------+ | ********************** SQLTABCR TABLE CREATE UTILITY ******************** | | Database ==> SQLDBA | | CREATOR ==> M760595 TABLE NAME ==> NEW_TABLE | | OWNER ==> M760595 DBSPACENAME ==> M760595B | | DRAW TABLE ==> | | | | Mod Colno Column Name Column Type Column Length Nulls (1/0) | | --- ----- ------------------ ---------------- ------------- ----- | | 1 COL1 CHAR 5 0 | | 2 COL2 INTEGER | | 3 COL3 VARCHAR 120 | | 4 COL4 DEC 5,1 | | 5 COL5 FLOAT | | 6 | | 7 | | 8 | | 9 | | 10 | | GRANT SELECT TO PUBLIC ==> 1 (1=YES,0=NO) DATA CAPTURE => CHANGES | | COMMENT ON TABLE ==> This is a 5-column table definition | | Page 1 of 1 | | PF: 1 Help 3 QUIT 7 Bkwd 8 Fwd 9 DRAW Table 10 PROCESS Definition | | ENTER PROCESS Current Screen 11 CLEAR Definition 12 COLUMN Help | +--------------------------------------------------------------------------------+ |
|When Enter is pressed, the current screen is checked for valid |entries.
|The first time PF8 Fwd is pressed, the current screen is checked. If |no errors exist and 10 columns have been processed successfully, a new display |screen is presented for the next 10 columns (the COLNO field displays the |column number). The MOD field is activated for any possible deletes or |inserts to existing screen row entries (this is explained further |below). PF7 and PF8 are used to scroll backwards and forwards.
|When you have finished defining the table, press PF10. If no errors |are found, the CREATE TABLE DBSU job is executed. The results are |displayed on the screen.
|PF11 will erase all column definitions and reset the display to the first |screen. A confirmation message is issued before the action is |taken.
|If you have not yet submitted the table create job, you can change, |insert and delete rows. Suppose that you have defined 10 columns, used |PF8 to scroll to a second screen, and have specified additional |columns. At this time, you decide to make changes to rows specified on |the first screen. Press PF7 to return to that screen. If there |are no errors in the specification of the rows on the current screen, the |previous screen will be displayed. If there are errors, they must be |fixed before you can scroll back.
|You will now notice that a new field (MOD) has been activated for the |screen. Located to the left of the column number (COLNO) field, this |field is used for indicating that you want to insert (1) or delete (0) a |column. To delete a row, enter a 0 in the field at the left of the |row. To insert a new row AHEAD of another row, type a 1 in the MOD |field of the row the new row is to be inserted ahead of. Then type over |that row with the new information; the original row data will not be |changed. Press Enter to validate the new data and redisplay the altered |screen; note that the new row has been inserted and the original |row's data is unchanged. In addition to inserts and deletes, you |can also type over existing fields that you might want to change; press |Enter to validate the new data and redisplay the changed screen.
|Note that when you do an insert, under some conditions, an already |validated row of data pushed down into the next set of ten entries may be |marked internally as not being processed. If this happens, a message is |displayed identifying the unprocessed row. To clear the message and |proceed, scroll forward until the unprocessed row is visible on the |screen. Then continue what you were doing. When you have |completed all of the changes on a screen, press a scroll key (PF7 or PF8) or |press Enter to actuate the changes. Control Center will process the new |data on the screen. Error checking is done and the screen is |redisplayed with deleted rows removed, inserted rows added, and any changes |made. Inserts and deletes cause automatic renumbering of the |rows. Make these types of changes carefully because there is not a way |to undo changes other than retyping the original data. When you have |completed all of the column definitions, press PF10 to have the DBSU run to |create the table.