HP 3000 Manuals HP 3000 Manuals
Table Menu
At the Table Menu (T), you choose the table function you want to perform.
You specify the type of table (view, IMAGE, or file table) when you add a
new table. Once the table's definition is stored in the data dictionary,
all you need to modify, examine (Show or List), or copy a specific table
is its name. The Administrator Utility will verify what type of table it
is and display the appropriate fields automatically.
When you want to examine tables with the List function, you can choose to
list all configured tables or all tables of a particular type.
To add, change, delete, examine, copy, or rename any of the various types
of tables:
* Go directly to the screen you want by typing its screen name in
the Go to screen field of any menu and pressing Enter, or
* Choose a function label at the Table Menu (T).
The Table Menu looks like this.
______________________________________________________________________________________________
| |
| |
| CONFIG Table Menu Screen: T |
| Choose a function, or type in a screen name and press ENTER. |
| |
| |
| Add Table - Add the definition of a table. |
| |
| Change Table - Change the definition of a table. |
| |
| Delete Table - Delete the definition of a table. |
| |
| Show One Table - Show the definition of one table. |
| |
| List All Tables - List the names of all tables or a specified |
| subset of tables. |
| |
| Copy Table - Copy the definition of a table or rename a table. |
| |
| Done - Return to the Configuration Main Menu. |
| |
| Go to screen |
| |
| Add Change Delete Show One List All Copy Done |
| Table Table Table Table Tables Table |
| |
______________________________________________________________________________________________
Adding a New Table
In order to add a new table to the data dictionary, you must first
specify which type of table it will be.
Ordinarily in the Administrator Utility, the level of menu screen that
lets you add, change, delete, or examine various things takes you
directly to a screen where you enter data. However, because adding each
different type of table requires different fields, each operation has its
own screen format.
An intervening menu, the Add Table Menu (AT), lets you choose one of
three types of tables: view, IMAGE, and file.
The specifics of adding each type of table to the data dictionary are
presented in detail in the next three sections of this chapter.
To choose which type of table definition to add to the data dictionary:
* Go directly to the screen you want by typing its screen name in
the Go to screen field of any menu and press Enter, or
* Choose a function label at the Add Table Menu (AT).
The Add Table Menu is looks like this.
______________________________________________________________________________________________
| |
| |
| CONFIG Add Table Menu Screen: AT |
| Choose a function, or type in a screen name and press ENTER. |
| |
| |
| View Table - Create a view table from existing tables and add its |
| definition to the data dictionary. |
| |
| IMAGE Table - Define all or part of an IMAGE dataset as an IMAGE table |
| in the data dictionary. |
| |
| File Table - Define all or part of a file as a file table in the data |
| dictionary. |
| |
| Done - Return to the Table Menu. |
| |
| |
| |
| |
| Go to screen |
| |
| View IMAGE File Done |
| Table Table Table |
| |
______________________________________________________________________________________________
Adding a View Table Definition
A view table is a table created from one or more already configured
tables. It is a new view of existing data from already configured IMAGE,
file, and view tables.
Before you can define a view table, the table(s) from which it will
derive its information must already be defined in the data dictionary.
(For a more detailed discussion, with examples, of view table creation,
see Appendix B. For view table syntax, see Appendix C.)
You have two ways to add the definition of a view table to the data
dictionary: with the Add View Table (AVT) screen or with an editor.
(See "Configuring a View Table Editor" under "Environment Configuration
Menu" in Chapter 2.) You use the Add View Table screen only if no view
table editor has been configured.
DBA Capabilities.
If you are a secondary DBA, you can define a view table only from other
tables that you control.
Using the Add View Table Screen.
Add the definition of a view table to the data dictionary at the Add View
Table (AVT) screen, shown on the next page. This screen includes more
fields than can be displayed at one time. You can toggle between the two
groups of fields using function labels. Table 5-1 describes these
fields.
______________________________________________________________________________________________
| |
| |
| CONFIG Add View Table Screen: AVT |
| Fill in these fields and "Next Fields", then press ENTER. |
| |
| Table Sample Values Security |
| |
| Table Description |
| |
| Item Clause (separated by commas) |
| |
| |
| |
| |
| |
| |
| |
| |
| Next Help Cancel |
| Fields Add |
| |
______________________________________________________________________________________________
______________________________________________________________________________________________
| |
| |
| CONFIG Add View Table |
| Fill in these fields and "Previous Fields", then press ENTER. |
| |
| Using Clause (separated by spaces) |
| |
| |
| |
| Where Clause |
| |
| |
| |
| |
| |
| Sort Clause (separated by commas) |
| |
| |
| |
| Previous Help Cancel |
| Fields Add |
| |
______________________________________________________________________________________________
Table 5-1. Add View Table Screen Fields
--------------------------------------------------------------------------------------------
| | |
| Field | Description |
| | |
--------------------------------------------------------------------------------------------
| | |
| Table | Name of the view table. View table name syntax conforms to IMAGE |
| | syntax for dataset names, except that table names cannot be Access |
| | Server reserved words. |
| | |
| | The name can be 1 to 16 characters long. It must begin with a |
| | letter. That letter can be followed by any of the following: the |
| | letters A through Z, the digits 0 through 9, and any of the |
| | characters + - * / ? ' # % & @. |
| | |
| | The following reserved words can be used for table names (as well as |
| | item names) if they are prefixed with an exclamation point (!): |
| | AND, CASE, DEFAULT, DIV, ELSE, END, F, FALSE, IS, JOIN, LEFTJOIN, |
| | LIKE, LJ, MATCH, MOD, NOT, OR, T, and TRUE. (See "Reserved Words as |
| | Table and Item Names" under "Ground Rules" in Chapter 2.) |
| | |
--------------------------------------------------------------------------------------------
| | |
| Sample Values | For sample value viewing by PC users, this is the way to enforce (or |
| Security | relax) the access to records provided by the Where Clause. |
| | |
| | A blank means ignore the Where Clause if the PC user asks to see |
| | sample values. The PC user will then see records that might |
| | otherwise be inaccessible. Sample records are more quickly available |
| | this way. |
| | |
| | Any non-blank character means the PC user requesting sample values |
| | will see only those records allowed by the Where Clause, thus |
| | maintaining security. Use sample values security if there is |
| | sensitive data that the Where Clause automatically excludes. |
| | |
--------------------------------------------------------------------------------------------
| | |
| Table | (Optional.) Phrase, up to 50 characters, to remind you and your |
| Description | users what the view table contains. |
| | |
--------------------------------------------------------------------------------------------
Table 5-1. Add View Table Screen Fields (cont.)
--------------------------------------------------------------------------------------------
| | |
| Field | Description |
| | |
--------------------------------------------------------------------------------------------
| | |
| Item Clause | Items to be included in the view table (up to 64). See Note below. |
| | |
| | Items must be separated by commas. Item names must be unique. If an |
| | item name is not unique (that is, if it occurs, identically spelled, |
| | in more than one table named in the Using Clause), you should qualify |
| | the item name with its table name (for example TABLE.ITEM). If |
| | ambiguous item names are not fully qualified, Access Server decides |
| | which occurrence to use. Because the item used will not necessarily |
| | correspond to the first table (with that item) named in the Using |
| | Clause, we strongly recommend that you always fully qualify ambiguous |
| | item names to avoid the possibility of incorrect data being |
| | retrieved. (Examples in Appendix B illustrate how to qualify an item |
| | name, as well as how to use arithmetic operators to specify items of |
| | interest in more complex ways.) |
| | |
| | Item names can be Access Server reserved words only if the reserved |
| | words are prefixed with an exclamation point (!). (These words are |
| | listed in the Table field definition, above.) |
| | |
| | The maximum record length allowed is 2048 bytes. Determine record |
| | length by adding all item lengths. If conversion of data types is |
| | involved, record length both before and after conversion must not |
| | exceed 2048 bytes for the table definition to be valid. |
| | |
| | An asterisk (*) as the only entry in the Item Clause means that all |
| | items will be included in the table. For syntax examples, see "Item |
| | Clause" in Appendix B, "Constructing View Tables." |
| | |
--------------------------------------------------------------------------------------------
| | |
| Using Clause | Name(s) of one to 16 configured table(s) from which this view table |
| | is derived. View tables can be derived from a single table. View |
| | tables can also be derived from two to 16 tables using the PRODUCTION |
| | and/or JOIN functions. (PRODUCTION and JOIN are described in the |
| | examples in Appendix B, "Constructing View Tables.") |
| | |
| | For syntax examples, see "Using Clause" in Appendix B, "Constructing |
| | View Tables." |
| | |
--------------------------------------------------------------------------------------------
Table 5-1. Add View Table Screen Fields (cont.)
--------------------------------------------------------------------------------------------
| | |
| Field | Description |
| | |
--------------------------------------------------------------------------------------------
| | |
| Where Clause | (Optional.) Type or range of records you want included in the view |
| | table. If the Where Clause is blank, all records are included. |
| | |
| | Typically, this clause narrows the number of records in the table for |
| | quicker, more efficient access. You can also use it to exclude |
| | sensitive data from access by selected groups of users. |
| | |
| | (The examples in Appendix B illustrate some ways to use the Where |
| | Clause.) |
| | |
| | Operators you can use in this clause are =, <, >, <=, >=, <> and |
| | MATCH. You can also combine operators using the AND, OR, and NOT |
| | functions. |
| | |
| | Any items from the tables named in the Using Clause can be used here, |
| | whether or not the items appear in the Item Clause. Items newly |
| | defined for this view table cannot be used. |
| | |
| | For syntax examples, see "Where Clause" in Appendix B, "Constructing |
| | View Tables." |
| | |
--------------------------------------------------------------------------------------------
| | |
| Sort Clause | (Optional.) Items (up to 16) upon which you want the records sorted |
| | once the Item, Using, and Where Clauses have determined which records |
| | will be included in the table. This clause gives you control over |
| | the order in which records appear in the view table. |
| | |
| | You must use item names that appear in the Item Clause. Items newly |
| | defined for this view table can be used. |
| | |
| | Default sort type is Ascending. For a Descending sort, add a blank |
| | and the letter D after the item name. To explicitly choose an |
| | Ascending sort, add a blank and the letter A. |
| | |
| | For syntax examples, see "Sort Clause" in Appendix B, "Constructing |
| | View Tables." |
| | |
--------------------------------------------------------------------------------------------
NOTE Up to 64 items can be specified in a view table, depending on the
complexity of the items. CASE statements, SUB functions, or other
complex operations can fill the internal data structure before the
64 item limit is reached. Potential errors that could occur are:
ERROR-Access Code Overflow (AC23200)
INTERNAL ERROR-EXPTREE area overflow (AC23400)
INTERNAL ERROR-String Pool overflow (AC23151)
These limits are less likely to be reached if you are running
Access Server on MPE/XL.
Compound Items.
Compound items can be included in an IMAGE table, but your PC users will
not be able to access those compound items in the table. (The column for
that item simply does not appear.)
After the IMAGE table has been configured, create one or more view tables
from it. Use the view table(s) to resolve the compound data item into
simple data items. PC users can then access the (resolved) compound data
in the view table.
Suppose, for example, that COM is a compound data item with four
sub-items, and that COM is a part of the IMAGE table DTABLE. Then in
VTABLE, a view table derived from DTABLE, your Item Clause could include:
SIM1=COM[1],SIM2=COM[2],SIM3=COM[3],SIM4=COM[4]
In this case, SIM1 through SIM4 are simple data items of the same type as
the compound data item. The compound data unavailable in DTABLE is now
available as simple data items in VTABLE.
Binary File Output.
You can create a binary file when you output data from Access PC or from
the Host Batch Facility. Binary file output is particularly useful with
view tables. When data is output to a binary file, conversion of packed,
zoned, and integer numeric data types is bypassed. (Conversion of
numeric data types is explained later on this chapter.)
A view table can contain literal character fields consisting of, for
example, a specified number of spaces. Literal character fields can be
used to place fields in the desired byte location within the output
record.
Binary file output in conjunction with field placement enables files to
be properly formatted for passing data to existing COBOL applications.
Here is an example of how this is done.
The COBOL working storage area is:
01 INPUT-RECORD
05 PRODUCT-NUMBER PIC X(16)
05 FILLER PIC X(3) VALUE SPACES.
05 COST-OF-SALES PIC S9(9)V99 COMP-3.
05 FILLER PIC X(4) VALUE SPACES.
05 TRANS-NUMBER PIC S9(9) COMP.
The Item Clause for the view table is:
PRODUCT-NUMBER,FILLER1=" ",COST-OF-SALES,FILLER2=" ",
TRANS-NUMBER
The newly defined items FILLER1 and FILLER2 are given values of 3 and 4
spaces, respectively. These values correspond to the FILLER items in the
COBOL working storage area. Note that positioning of fields on even and
odd byte boundaries is possible depending on the number of spaces
assigned to the intervening FILLER items.
IMAGE data types for the items are:
PRODUCT-NUMBER X16
COST-OF-SALES P12
TRANS-NUMBER I2
When data is retrieved using this view table definition and output to a
binary file on the HP 3000, the items COST-OF-SALES and TRANS-NUMBER will
remain unconverted.
To Add a View Table with the Add View Table Screen:.
1. Go to the Add View Table screen by typing AVT in the Go to screen
field of any menu, or by stepping through two menus: choose Add
Table f1 at the Table Menu and then View Table f1 at the Add
Table Menu.
2. Fill in at least the Table and Item Clause fields. The Sample
Values Security and Table Description fields are optional.
3. To display additional fields, choose Next Fields f3.
4. Fill in at least the Using Clause field. The Where Clause and
Sort Clause fields are optional.
To make changes to fields no longer displayed, choose Previous
Fields f2.
5. To put the changes into effect, press Enter. (You can press Enter
whether the first or second group of fields is displayed.)
The table definition is added to the data dictionary.
If any field contains an error, or if you pressed Enter before
filling in all required fields, an error message appears and that
field is highlighted. Type in the correct information and press
Enter.
6. When the screen clears, you can add another view table definition.
7. To leave this screen, choose Cancel Add f8. This returns you to
the Add Table Menu. From there, you can go to another screen or
exit the utility.
Using an Editor to Add a View Table.
If you have an HP 3000-based editor, you can use it to add view table
definitions. Access Server supports three editors: EDITOR, TDP/3000,
and HP Edit. However, you can configure other HP 3000 editors such as
QEDIT and QUAD.
To configure an editor, or to change from one editor to another, see
"Configuring a A View Table Editor" under "Environment Configuration" in
Chapter 2.
To Add a View Table With an Editor:.
1. Go to the Add View Table screen by typing AVT in the Go to screen
of any menu, or by choosing Add Table f1 at the Table Menu and
then choosing View Table f1 at the Add Table Menu.
The Add View Table screen is displayed showing only the Table,
Sample Table Security, and Table Description fields.
2. Fill in the Table field. The Sample Table Security and Table
Description fields are optional.
3. Press Enter to assign the table name to the view table.
After a few seconds, the editor screen appears. The Administrator
Utility provides a template showing the four fields in which you
type the view table definition, beginning with the Item Clause
field. (See Table 5-1 for field descriptions.) You need to
display this template before proceeding.
_________________________________________________________________
NOTE Some editors, such as HP Edit, display the template as soon
as the editor screen appears. In this case, skip step 4 and
go directly to step 5 to add your view table definition.
_________________________________________________________________
4. To display the template, use the command your editor requires for
listing the contents of a document. For example, if you are using
EDITOR or TDP, type List All. The template appears on the screen
and looks like this:
*** Item ***
*** Using ***
*** Where ***
*** Sort ***
5. Add your Item Clause below the *** Item *** line.
6. Add your Using Clause below the *** Using *** line.
7. If necessary, add your Where Clause and Sort Clause after the ***
Where *** and *** Sort *** lines. (These clauses are optional;
the Item Clause and Using Clause must be specified.)
8. When you have completed the view table definition, save it using
the editor's Save or Keep command.
The Administrator Utility places the view table definition in a
temporary file and assigns it a temporary file name.
9. Exit the editor. The view table definition is added to the data
dictionary.
If any clause in the view table contains an error, a error message
appears and that clause is shown with the error highlighted.
To correct the error, return to the editor by choosing Edit Again
f1. Type the correct information, save the view table definition,
and exit the editor.
You are then returned to the Add View Table screen where you can
add another view table definition.
10. To leave this screen, choose Cancel Add f8. This returns you to
the Add Table Menu. From there, you can go to another screen, or
exit the utility.
Maintaining Security.
If the source of data for a view table contains sensitive information,
you can use the Where Clause to maintain security by limiting access to
such data.
For additional security, you can make sure no PC users requesting to see
sample values will be able to look at records with sensitive information.
Ordinarily, a sample values request will override the limits provided in
the Where Clause. To enforce these limits for sample values requests,
fill the Sample Values Security field with any character.