HP 3000 Manuals HP 3000 Manuals
Frequently Used Commands
Presented here are descriptions of Host Batch Facililty commands and
examples of their use. Note that the information is presented in modular
fashion and proceeds in the logical order that most operations are
performed. This should make it easier to select the desired functions
and to structure the flow of your command file. For purposes of clarity,
all parameters are italicized, while optional ones are enclosed in
[brackets].
Establishing Access to the Host Batch Facility
_____________________________________
| |
| |
| REMOTE [username [userpswd]] |
| |
_____________________________________
username -- name of a valid Information Access user
userpswd -- Information Access user password
_____________________________________
| |
| |
| USERINFO [username [userpswd]]|
| |
_____________________________________
username -- name of a valid Information Access user
userpswd -- password assigned to the user
REMOTE or USERINFO establish access to the Host Batch Facility. A valid
user name and password must be submitted before Access Server will
respond to any requests for data. The user name and password are checked
against the data dictionary to determine which tables can be accessed.
If the information has been supplied earlier in the command file, then
the name and password are not needed.
_____________________________________
| |
| |
| ECHO ON|OFF |
| |
_____________________________________
Toggles the display of commands. By default, the commands are displayed
as they are executed.
SQL Command
The SQL command allows you to query a view table in Access Server and
perform complex operations on it.
This command greatly simplifies table manipulation. It eliminates the
need for the following commands in the Host Batch Facility: TABLE,
COLUMN, JOIN_COL, DEFINE_Q, SEARCH, PERFORM_OUTPUT, Q_RESULT, SORT,
PERFORM_SORT, SUMMARY, and PERFORM_SUM.
_____________________________________
| |
| |
| SQL "sql_clause;" |
| |
| SQL |
| |
_____________________________________
The sql_clause can be terminated by an optional semicolon (;). When the
SQL command is used without a quoted string, it must be terminated by a
semicolon.
sql_clause -->
SELECT item_clause
FROM table_clause
WHERE where_clause
GROUP BY group_by_clause
ORDER BY order_by_clause
HAVING having_clause;
NOTE The clauses can be in any order.
item_clause --> view table item_clause or
--> aggregate_clause or
--> *
The asterisk (*) means that all items are included in all tables in the
table_clause (duplicate item names are eliminated). The syntax of the
view table item_clause is found in Appendix C.
table_clause --> view table using_clause or
--> *
The asterisk (*) means that the last loaded table be used in Host Batch.
Table names can be configured tables or saved tables. If a configured
table already exists with the EXACT same name as a saved table, the saved
table name muist be preceded by a "&" (ampersand).
The syntax of the view table using_clause is found in Appendix C.
aggregate_clause --> {item_name=} agg_item {,{item_name=} agg-item}
{,...}
item_name= is optional. If no item name is specified, a default name is
given that is the agg_type followed by a dash and then follwed by the
item name (truncated at 16 characters). For example, the default item
name for COUNT(SR-NO) would be COUNT-SR-NO. If an ORDER BY or HAVING
clause is included in the SQL command, this default name would have to be
specified in these clauses.
agg_item --> agg_type (expression | item name)
agg_type --> AVG | SUM | MIN | MAX | COUNT
The syntax of an expression is found in Appendix C.
where_clause
The syntax of the where_clause is found in Appendix C.
group_by_clause --> item_name | item_name = expression
{,item_name | item_name = expression}{,...}
sort_clause -->item_name{A|D|ASC|DESC}{,item_name}
{A|D|ASC|DESC}}{,...}
The sort_clause syntax is the same as the Sort Clause in the
Administrator Utility.
The syntax of the sort_clause is found in Appendix C.
having_clause --> where_clause
The items in the having_clause can be resultant items only, equivalent to
a query on result. This is especially useful for querying on aggregate
items.
Here is an example of using the SQL command:
SQL
* Enter SQL text -- a semicolon and RETURN terminates
SQL text entry
SELECT SR-COUNT = COUNT(SR-NO)
FROM SRS
WHERE (PROD-NO = '36895A' OR PROD-NO = '3689A') AND
date-submit > '870101'
GROUP BY PROD-NO,SEVERITY
ORDER BY PROD-NO,SR-COUNT;
Saving a Result Table
_____________________________________
| |
| |
| OUTPUT_TAB |
| |
_____________________________________
Indicates that you want to save the current table. A succeeding SELECT
command specifies the database format and a PERFORM_OUTPUT command
initiates the sequence.
_____________________________________
| |
| |
| PERFORM_OUTPUT name |
| |
_____________________________________
name -- name of the output file
Outputs the current result table. A preceding SELECT command specifies
the type of database format the file will use. Valid output types on the
HP 3000 include ASCII, SD, SAVED, VISICALC, and SQL.
Here is an example of how to save a table to a file on the HP 3000:
OUTPUT_TAB
SELECT ASCII
PERFORM_OUTPUT filename
NOTE Once a table has been output as a saved table, you may want to use
the RELEASE_TABLE command described under "Selecting and
Manipulating Tables" later on in this chapter. This allows other
users to access newly created Information Access tables.
You can use the Host Batch Facility to output to an ALLBASE/SQL table.
The syntax is:
OUTPUT_TAB
SELect SQL
PERFORM_OUTPUT tablename dbenv [ dbefileset [overwrite]]
or
OUTPUT_TAB tablename SQL dbenv [ dbefileset [overwrite]]
tablename -- ALLBASE/SQL table name
SQL -- output table type
dbenv -- DBEnvironment
dbefileset -- DBEfileset name where table is to be
created
overwrite -- if used, the existing table in the
DBEnvironment is overwritten
For ALLBASE/SQL output, you must have the appropriate authorities to
connect to the DBE and to create or overwrite tables. See your
ALLBASE/SQL documentation for information on authorities.
Concluding a Session
_____________________________________
| |
| |
| EXIT |
| |
_____________________________________
This command closes all tables and exits the Host Batch Facility.