The Create Table function provides you with an easy, interactive, interface for defining DB2 tables and having them created by the Table Utility. You are presented with a Create Table screen (see Figure 50).
Figure 50. Create Table Data Entry Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*------------------------------- CREATE TABLE -------------------------------* | || DATABASE => SQLDBA | | || CREATOR => SQLDBA OWNER => ________ | | || TABLE => ACTIVITY2 DBSPACE => _________________ | | || | | || COL CNAME COLTYPE LENGTH NULLS (1/2) | | || 001 __________________ _______________ _____ _ | | || 002 __________________ _______________ _____ _ | | || 003 __________________ _______________ _____ _ | | || 004 __________________ _______________ _____ _ | | || 005 __________________ _______________ _____ _ | | || 006 __________________ _______________ _____ _ | | || 007 __________________ _______________ _____ _ | | || 008 __________________ _______________ _____ _ | | || 009 __________________ _______________ _____ _ | | || 010 __________________ _______________ _____ _ | | || *********************** OPTIONAL PARAMETERS ******************* | | || GRANT SELECT TO PUBLIC => 2 (1=YES/2=NO) DATA CAPTURE => NONE___ | | || COMMENT ON TABLE => _____________________________________________________ | | |*------------------------------------------------------------------ SQC65 ----* | | | |ENTER DATA AND A VALID FKEY TO CONTINUE PROCESSING | |ENTER F1=HELP F3=EXIT F8=FWD F12=CANCEL | +--------------------------------------------------------------------------------+
Screen rows are scanned from the top to the bottom (including the DATABASE, CREATOR, OWNER, TABLE, and DBSPACE). If any errors are detected, an error message is issued; when processing resumes, the top-to-bottom scan restarts at the top of the screen.
A data column must have a name (CNAME), column type (COLTYPE), and if required by the column type, a length (default lengths are described just below). An embedded blank row (column definition) is not allowed. When you press ENTER, the SQL statements corresponding to the table structure are generated and made ready for job submission.
| Note: | In the remainder of the Table Create discussion, the term row refers to a row on the screen; this is synonymous with table column. |
Each data column to be defined for the table is specified on a row on the CREATE TABLE screen. Each row on the screen must be completed (at a minimum, have a column name, type, and length if required) before you can scroll down to the next screen. No intermediate, empty, rows are allowed. Each CREATE TABLE entry screen has space to enter data for 10 data columns; when 10 entries are made, you can scroll down to make the next 10 entries. Before you can scroll down, the entries you have made must be correct for the fields you have entered. When you scroll away from a screen on which you have entered data, Control Center, if you have not entered a length, will fill in the length field with the SQL default length length for the specific data type, such as, character fields default to length 1.
| Note: | SQL has a limitation on the length of a row in a database table. The CREATE TABLE statement syntax (in the DB2 Server for VSE & VM SQL Reference manual) describes the limitation. The limit is approximately 4K bytes, however, variable length, and large character strings, each require only a few bytes of the 4K, so this limit is usually not significant. |
There are many infrequently used SQL options that you may want to specify when defining a table column, such as UNIQUE. Control Center cannot directly support these due to their number and complexity; however, there is a way for you to specify them. In the description of the job submission DISP parameter, note that you can specify that after a job is submitted, it should be held rather than run. When you create a table and you need to include specifications that Control Center does not directly support, use DISP=HOLD. Then, you can copy the job from the VSE/POWER queue to the ICCF library, edit it, add the additional specifications, and then resubmit the job.
Control Center does not directly support specifying a primary key, although you can do this by using DISP=HOLD as described immediately above. The designers of Control Center assume that if you are creating a table, you will usually follow that with a bulk load. Existence of a primary key when a table is initially loaded can cause serious performance degradation. It is much more efficient to do the load first and then specify the primary key.
DO NOT PRESS ENTER UNTIL YOU HAVE ENTERED ALL OF THE COLUMN DATA YOU PLAN TO ENTER FOR ALL OF THE ROWS.
Pressing the ENTER key indicates that you have completed entering the table's definition. If no errors are found, the CREATE TABLE job submission screen (see Figure 52) is displayed. If you accidentally press ENTER, when the CREATE TABLE job submission screen is displayed, press PF12 to cancel the job submission and return to the CREATE TABLE screen.
If you have not yet submitted the table create job, you can change, insert, and delete rows. Suppose that you have specified columns 1 through 10, 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 the previous 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.
You will now notice that a new field, with no heading, has been added to the screen. Located to the left of the column number (COLNO) field, this field is used for indicating that you want to insert or delete a column. The field has no meaningful use when an empty CREATE TABLE screen is first presented, so it does not appear the first time a screen is displayed for column data entry. You must scroll away from a screen and then back to it for the new field to be displayed.
You can use this field to insert or delete a row. To delete a row, type a 2 in the new field at the left end of the row. To insert a new row ahead of another, type a 1 in the new field and then overtype the row's data with the new information; the original row's data will not be changed. In addition to inserts and deletes, you can also overtype existing fields that you want to change, and, finish any partially complete entries; over-type changes are automatically recognized by Control Center.
|Pressing ENTER after INS or DEL displays the changed screen. |You must press ENTER again if you are finished with the table's |definition.
| Note: | When you do an insert, under some conditions, a row of data that is pushed down onto 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 (data column). To clear the message and proceed, scroll down until the unprocessed row is visible on the screen. Then continue what you were doing. If the message was displayed because you pressed ENTER, press it again. If there is another unprocessed row, the message will be displayed again. Scroll down until it is displayed and continue what you were doing. Scrolling off a screen with such a row or pressing ENTER will cause it to be processed. |
When you have completed all of the changes on a screen, press a scroll key, or if you are finished with the table definition, press ENTER to actuate the inserts, deletes, and changes. Control Center will process the new information on the screen, inserting and deleting rows, and changing any other data you have entered. Error checking is performed as usual and the screen is re-displayed with deleted rows removed, inserted rows added, and any other changes made. Inserts and deletes cause automatic renumbering of the rows. Make these types of changes carefully; there is no way to undo a change other than retyping the original data, or using the DISP=HOLD technique.
When you have completed all of the column definitions, press ENTER to display the CREATE TABLE job submission screen. When the job is submitted, Control Center creates the JCL and DDL statements to define the table, and submits the job to the VSE/POWER queue for execution.
The DATABASE, CREATOR and TABLE fields are required and are automatically filled in from the Table Utility main menu. The COLNO parameter is initialized to column numbers 1 - 10 on the first screen displayed. When the screen is scrolled forward, columns 11 - 20 will be displayed (and so on with any further screen entries). A maximum of 255 column entries is allowed.
The OWNER and DBSPACE parameters are required because they are needed in the CREATE TABLE DDL statement generated by Control Center.
Parameters below the "OPTIONAL PARAMETERS" line are not required.
These fields are used to define the table to be created:
You can add additional column specification parameters if necessary. To do this, when you submit the Create Table job, use DISP=H as the disposition parameter on the Create Table Submit Screen. Then, you can remove the job from the READER queue, edit it, add the additional parameters, and resubmit the job for execution.
These are the "OPTIONAL PARAMETERS":
Figure 51 is an example of a filled in Create Table screen.
After entering the desired parameters and column descriptions, press F8 to scroll forward for entry of more column definitions or press ENTER to proceed to the Create Table Submit screen.
Figure 51. Create Table Data Entry Screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*------------------------------- CREATE TABLE --------------------------------* | || DATABASE => SQLDBA | | || CREATOR => SQLDBA OWNER => owner | | || TABLE => ACTIVITY2 DBSPACE => dbspacename | | || | | || COL CNAME COLTYPE LENGTH NULLS (1/2) | | || 001 name-last char 20 | | || 002 name-first char 25 | | || 003 name-middle char 25 1 | | || 004 title char 10 | | || 005 address 1 char 25 | | || 006 address 2 char 25 1 | | || 007 address 3 char 25 1 | | || 008 city char 25 | | || 009 state char 2 | | || 010 zip-5 char 5 | | || *********************** OPTIONAL PARAMETERS ******************* | | || GRANT SELECT TO PUBLIC => 2 (1=YES/2=NO) DATA CAPTURE => NONE | | || COMMENT ON TABLE => | | |*------------------------------------------------------------------ SQC65 ----* | | | |ENTER DATA AND A VALID FKEY TO CONTINUE PROCESSING | |ENTER F1=HELP F3=EXIT F4=TOP F5=BOT F7=BWD F8=FWD F12=CANCEL | +--------------------------------------------------------------------------------+
When you press ENTER, the Create Table Submit screen is displayed; see Figure 52.
Figure 52. Create Table Submit screen
+--------------------------------------------------------------------------------+ | mm/dd/yyyy CONTROL CENTER hh:mm:ss | |*------------------------ CREATE TABLE SUBMIT SCREEN -------------------------* | || | | || JOBNAME => ________ CLASS => A | | || | | || PRI => 3 DISP => D (D,H,K,L) | | || | | || FROM => ________ LST CLASS => A | | || | | || DUETIME => ____ (HHMM) DUEDATE => ______ | | || | | || DUEDAY => __________________________________________ | | || | | || | | || OTHER => ______________________________________________________________ | | || | | || | | || | | || | | || | | |*------------------------------------------------------------------ SQC66 ----* | | | |PRESS ENTER TO PROCESS | |ENTER F1=HELP F3=EXIT F12=CANCEL | +--------------------------------------------------------------------------------+
The first parameter, JOBNAME, is the only one that is required because the other parameters have default values.
For a detailed description of the job submission parameters, see the description of the job submission parameters on page job submission parameters.
| Note: | In the discussion of the job submission DISP parameter, be sure to note that using DISP=K as a means of recovering from some of the errors you might make when creating a table. |
|A job return code of 6 is normal.