HP 3000 Manuals

Information Access Server: Planning and Configuring

Worksheet 3:  View Table Definition 

View Table Definition 


          Figure 1-3.  Labeled Diagram of Configuration Worksheet 3 

Objective.   

Primary and secondary DBAs use this worksheet to work out complete
definitions for all view tables each one wants to configure in Access
Server.

Only the primary DBA can create view tables from all tables.  A secondary
DBA can create view tables only when the tables used to derive the
information are under his or her control.

Worksheet Description.   

 At the top of the worksheet are spaces for the TABLE NAME
 (A), the DESCRIPTION (B), and the DATE (C) you created the
 view table definition.

          Table 1-0.  (cont.) 

                          Six columns are provided for the definition of items, though 
                          in most cases you'll only need one column, the ITEM NAME:

(D) TABLE NAME            The name of the table from which the item comes.  The table
                          name is ordinarily not required.  If two tables being JOINed
                          both have the item ITEM1 in them, then TABLE1.ITEM1
                          designates which table to draw ITEM1 from and retains that
                          item name as the column heading in the view table.

(E) .                     The period is needed only when the TABLE NAME column is used.

(F) ITEM NAME             The name the item will have in the view table.  There are
                          three ways to represent an item:

                             *   If the item name occurs only once in the tables being
                                 combined and you don't want to rename the item in the
                                 view table, fill in the ITEM NAME column only.

                             *   If the item name occurs more than once in the tables
                                 being combined and you don't want to rename the item
                                 in the view table, fill in the first three columns
                                 (TABLE NAME through ITEM NAME) only.

                             *   If you want to rename the item and/or arithmetically
                                 manipulate it, fill in the last three columns (ITEM
                                 NAME through EXPRESSION) only.

(G) =                     The equal sign is needed only when the EXPRESSION column is
                          used.

(H) EXPRESSION            Used to fully qualify an item if its ITEM NAME is not unique
                          (and the TABLE NAME column has not been used to qualify it).
                          It can also be used to perform some arithmetic operations on
                          the data item.  You can, for example, convert an annual
                          amount (in the source table) to a monthly amount (in the view
                          table) by indicating division by 12.

(I) ,                     The comma is used to separate items in the Item Clause.

                          There are three remaining fields: 

(J) USING CLAUSE          Identifies the configured table(s) from which the view table
                          is derived.  View tables can be derived from a single table.
                          View tables can also be derived from two through 16 tables
                          using the PRODUCTION and/or JOIN functions.  JOINs can be
                          done on up to several items from each table, depending on the
                          number of tables being JOINed.

          Table 1-0.  (cont.) 

(K) WHERE CLAUSE          Specifies the type or range of records you want included in
                          the view table.  The operators you can use in this clause
                          are:

=                     Equal to

<                     Less than

>                     Greater than

<=                    Less than or equal to

>=                    Greater than or equal to

<>                    Not equal to

MATCH                 Used with wildcard characters to narrow the range

                          You can also combine these operators using ''AND and OR
                          functions.

(L) SORT CLAUSE           Specifies up to 16 items you want the records sorted on, once
                          the Item, Using, and Where Clauses have determined which
                          records are in the view table.  The default sort is
                          Ascending, but you can specify the type of sort with an A
                          (Ascending) or a D (Descending) after each item name.

To Plan for View Tables:.   

   1.  Estimate the number of view tables you need to define, and make a
       copy of Worksheet 3 for each view table.  Complete each worksheet
       only when the tables from which the view table is to be derived
       have been defined.

   2.  Provide a name and a description for each view table.  Then fill
       in the remainder of the worksheet.


NOTE  For a detailed discussion with examples of view table creation,
      refer to Appendix B, "Constructing View Tables," and Appendix C,
      "View Table Syntax," in the Information Access Server:  Database 
      Administration manual.  We suggest you review this information
      before filling out Worksheet 3.


Once you've completed Worksheet 3 for each view table you want to
configure, the actual configuring simply involves transferring the
information from Worksheet 3 to the Administrator Utility's Add View
Table screen.

You may also find, once your view tables are on paper, that there are
better ways to define them, ways which may improve Access Server's
performance for your users.  See Chapter 14, "Performance Tuning," in the
Information Access Server:  Database Administration manual for a
discussion of this topic.

MPE/iX 5.0 Documentation