Using the Copy Table Rows option

To copy data by specifying an option on the Edit DB2 Table Options panel

1. Invoke a data editing session for the target table or view.For more details, see Methods-for-invoking-the-data-browsing-function.

2. On the Edit DB2 Table Options panel, set option values for editing data.

   Note

   For more details, see Setting-options-for-editing-data. To copy data by using the Copy Table Rows option, the following option values are required:

   • Edit or Browse Mode= E (edit data)
   • Copy Table Rows=Y

3. Press Enter.If another user has begun an editing session in the table or view, the Other Users Editing This Table panel is displayed. To edit the table or view, press Enter. To display the Edit DB2 Table Options panel, press END or CANCEL.

   If no other users are editing the table, the Copy Table Rows Specifications panel is displayed.

4. Set options for specifying the source and target tables or views and for customizing the SELECT and INSERT statements that Catalog Manager creates to perform the copy (see the following figure).

   DEFF-R ----------------  Copy Table Rows Specifications ----------------------
   Command  ===>
   
   Specify a table name or pattern for the source table and target table.
   Select the other options and press enter to copy rows.
   Press END or CANCEL to abandon the copy.
   
   Insert rows target table . . QZU.QZUT01_DCII5S02
   
   Select rows source table . . RDACRJ.DEF_QZUT01
   
   Edit subselect statement . . Y        Edit SQL select statement to specify
                                         WHERE clause values
   
   Edit insert statement  . . . Y        Edit SQL insert statement
   
   Delete all rows first  . . . N        Delete all rows from target table
                                         before executing the insert statement?

   To specify the source table, you can type the name of a table or view or use a pattern that includes a wildcard. For more information about using wildcards, see Supported-wildcards-in-qualifiers.

   In above figure, the following options have been selected:

   • To copy from a table
   • To edit the SELECT statement
   • To edit the INSERT statement
5. When you have specified all of the options, press Enter.The Select Statement Specification panel is displayed.

6. Customize the SELECT statement by modifying the selected columns and values on the panel (see the following figure).

   Tip

   Press HELP to display example specifications.

   DEFF ------------------  Select Statement Specification  ---------------------
   Command ===>                                                  Scroll ===> CSR
   
   Select columns to edit and type where clause values, then press ENTER
   S  NAME               TYPE     LENGTH ORDER OPER VALUE
   S  COLUMN_1           INTEGER       4    A  =
   S  COLUMN_2           SMALLINT      2    A  =
   S  COLUMN_3           CHAR         12    A  =
   S  COLUMN_4           CHAR          7    A  =
   S  COLUMN_5           CHAR          4    A  =
   S  COLUMN_6           VARCHAR      55    A  =
   S  COLUMN_7           DECIMAL   31,20    A  =
   S  COLUMN_8           DECIMAL    11,2    A  =
   S  COLUMN_9           SMALLINT      2    A  =
   S  COLUMN_10          INTEGER       4    A  =
   S  COLUMN_11          FLOAT         4    A  =
   S  COLUMN_12          FLOAT         8    A  =
   S  COLUMN_13          DATE          4    A  =
   S  COLUMN_14          TIME          3    A  =
   S  COLUMN_15          TIMESTMP     10    A  =
   S  COLUMN_16          VARCHAR      30    A  =
   S  COLUMN_17          INTEGER       4    A  =
   S  COLUMN_18          CHAR         20    A  =
   S  COLUMN_19          CHAR         24    A  =
   S  COLUMN_20          INTEGER       4    A  =

   Note

   You can substitute host variables for the Value specifications in the SELECT statement. For more information, see Using-host-variables-in-a-search.

7. Press END.The Copy Table Rows Specifications panel is displayed. You can specify whether to change other options on the panel.

8. Press Enter.An ISPF edit panel is displayed. You can edit the INSERT SQL statement to ensure that the column sequence is compatible with the column sequence in the SELECT statement (see the following figure).

   EDIT       RDACRJ.BMCCAT.WORK                              Columns 00001 00072
   Command ===>                                                  Scroll ===> PAGE
   
   ****** ***************************** Top of Data ******************************
   000001 INSERT INTO QZU.QZUT01_DCII5S02 (
   000002 COLUMN_1,
   000003 COLUMN_2,
   000004 COLUMN_3,
   000005 COLUMN_4,
   000006 COLUMN_5,
   000007 COLUMN_6,
   000008 COLUMN_7,
   000009 COLUMN_8,
   000010 COLUMN_9,
   000011 COLUMN_10,
   000012 COLUMN_11,
   000013 COLUMN_12,
   000014 COLUMN_13,
   000015 COLUMN_14,
   000016 COLUMN_15,
   000017 COLUMN_16,
   000018 COLUMN_17,
   000019 COLUMN_18,
9. Save the changes to the INSERT statement and close the ISPF edit panel.The Copy Table Rows Specifications panel is displayed.
10. Choose one of the following actions:

    • To complete the copy action, press Enter.

      The Edit DB2 Table Options panel is displayed with the CHANGES COMMITTED message.

    • To cancel the copy action, press END or CANCEL.

      The Edit DB2 Table Options panel is displayed with the COPY CANCELLED message.