Option 2: Create Alternative View

SQLM menu option 2 allows you to create a new table that includes some or all of the column definitions defined for an existing table. Typically, this option is used to create a table with a subset of the existing table's columns, thereby offering a restricted view of a table to particular users for security purposes.

Note

Privileges for the new table are available initially only to the owner (the user who created it), who has full privileges. To grant another user access, the owner must use SQLM Updating Privilege Grants. 'Update permission' on a table is set to N  initially. This overrides privileges and prevents updates, even by the owner. To change this, use SQLM option 4, sub-option 6.

The procedure to create an alternative view of a table is as follows:

  1. Select option 2 from the SQLM main menu. You will be prompted:

    Define Tablenames to be Processed....
Enter Tablename :

    Enter the name of the SQL table for which you wish to create an alternative view. If you enter ?, a list of tables is displayed, from which you can select. See Using the SQLM Menu Options.

  2. You will then be prompted to enter the new table name:

    Define Table name to be generated....
Enter Tablename :

  3. A list of columns currently defined for the source table is then displayed. For example:

                     Columns Currently Defined for Table 'GUESTS'
                 ============================================
Selected items will be set in alternative view Table 'GUEST_LIST'
 1. ADDRESS
 2. ARRIVAL-DATE
 3. ARRIVE-DATE
 4. BILL-AMOUNT
 5. BILL-AMOUNT.IND
 6. BILL-AMT
 7. BILL-CODE
 8. BILL-DATE
 9. BILL-DESC
10. BILL-DETAIL
11. BILL-TOTAL
12. BILL-TYPE
13. BILL_AMT
14. CITY
15. COUNTRY
16. DICT
Enter # to select (*)=all, (FI)le or (EX)it :
OR Enter (F)orward (B)ackward to page.

    Select the column definitions that you want to include in the new table, as described in Selecting from a List.

  4. A message similar to the following is then displayed:

    5 items have been transferred. Enter <CR>:

    Press RETURN to continue.

  5. The indexes available in the source table are then listed. Select the indexes required in the same way as for the column definitions.

  6. When you have selected the indexes you require, you will see prompts similar to the following:

    Copied selected Columns and Indexes from table GUESTS
Alternative view (GUEST_LIST) created. Enter <CR>:

    Press RETURN to display the main menu.

You can add, modify or delete column definitions for the new table, or delete the whole table, using the appropriate SQLM options (see Main Menu).