In preparation for the March IPROF Meeting, I am updating the ALLBASE
enhancement list. Those of you interested in either ALLBASE or SQL
access to IMAGE databases are invited to make additions, corrections or
clarifications to this list. I look forward to your comments.
Gary
SIG ALLBASE
Enhancement List for IPROF 98
1) Provide a mechanism for limiting the number of
rows/columns returned in response to an ODBC request
(see also # 98A05)
At times a system administrator or developer may
want to limit the size of the result set produced by an
ODBC request to prevent a `rogue' request from seriously
impacting network resources or as an aid in speeding
development. (Sponsored by: Gary Biggs) MPE SIB #42 97
Votes 97A01
2) Throttling control for the ODBCLINK Service
Process
Many systems administrators have expressed the
desire to control execution priority and the number of
simultaneous connections to the ODBC. Execution Priority
can be controlled on a user by user basis using the
Workload Manager but there is no mechanism to limit the
number of ODBC connections that a host may service at
one time. Currently a system administrator can limit the
number of sessions and jobs using the MPE/iX :LIMIT
command but no such control exists for ODBC. (Sponsored
by: Gary Biggs) MPE SIB #35 158 Votes 97A02
3) Change the functionality of the CAST and TO_DATE
functions to allow the conversion of Binary Zero, ASCII
Spaces, Zero and Null characters to an SQL NULL Date
Since Image/SQL has no Date/Time data types, these
values have been used by many developers to represent a
null, optional or missing date. Currently, casting of
these values results in the termination of an ODBC or
SQL request with an error condition. This behavior
should be changed or controlled, possibly by adding a
character to the format specification that specifies
that Zero or other invalid dates should be accepted and
converted to NULL. (I.e.: A format of `YYMMDDZ' says
that Zero is OK and should be treated as converted to
NULL. See also: date types 4, 18 and 38 defaults
proposed in the working draft for the HP Year 2000
solution, New Date Intrinsics) (Proposed by Gary Biggs,
IPROF 1997) MPE SIB #59 56 Votes 97A03
4) Multi-Column INDEXED retrievals on ALLBASE and
IMAGE/SQL tables
Advanced techniques are available that allow the
rapid processing of multiple indices without retrieving
any data. (a la: The Rushmore technology Microsoft
acquired from FoxPro for incorporation in their Access
product.) Basically, these techniques involve requesting
the record addresses or tuple ids of each row associated
with an index, sorting them and then merging or
intersecting the two result sets to implement the select
conditions (and/or) that link the associated columns.
(Sponsored by: Gary Biggs) MPE SIB #60 54 Votes 97A04
5) Additional String Manipulation and Mathematical
Functions in ALLBASE/SQL
SIG ALLBASE has developed a prioritized list of
data manipulation functions that should be added to
ALLBASE/SQL, see appendix 1, ALLBASE/SQL Functionality
Improvement Ballot (Documented by: Gary Biggs from
results of balloting since IPROF 1997) MPE SIB #65 42
Votes 97A05
6) Implementation of the ANSI/SQL CASE statement or
equivalent functionality (also suggested Oracle DCODE
and Microsoft IIF functions)
ALLBASE/SQL needs the capability of conditional
column definitions in SELECT clauses. (Proposed by: Gary
Biggs, HP World 1996) MPE SIB #53 70 Votes 97A06
7) The ability to drop tables without also dropping
the dependent views, rules and procedures when
restructuring an ALLBASE table
Currently, dropping a table or view also drops all
the associated views. This often creates extra work for
the DBA because it requires the rebuilding of the
dependent views after a minor change (adding a condition
or column). An option should be provided to control this
behavior. (Documented by: Gary Biggs, IPROF 1997) MPE
SIB #52 71 Votes 97A07
8) Enhance utilities like SQLGEN to recognize
IMAGE/SQL tables and not generate schemas for IMAGE data
sets
Schema generation of IMAGE tables is controlled by
DBSCHEMA and IMAGESQL so this functionality is useless
and annoying (Proposed by Michael Berkowitz, IPROF 1997)
MPE SIB #72 24 Votes 97A08
9) Enhance the ODBCLINK/SE Server process to run
under INETD
INETD allows `firewalling' be restricting socket access
to specific IP addresses or networks. It also eliminates
the need for a separate MPE job to run the listener.
(Documented by Gary Biggs, IPROF 1997) MPE SIB #46 87
Votes 97A09
10) Allow the creation of temporary ALLBASE tables
that are specific to a connection.
This would allow multiple users of the same DBE to
manipulate the same table name and the table would be
dropped upon the termination if the connection. This
functionality is provided in Microsoft Server. (Proposed
by Michael Berkowitz, IPROF 1997) MPE SIB #64 43 Votes
97A10
11) Allow a stored procedure to call another stored
procedure (Proposed by Michael Berkowitz, IPROF 1997)
MPE SIB #61 52 Votes 97A11
12) Enhance stored procedures to allow a variable
to be used as the owner part of a table name
I.e.: select * from: (variable).MyTable;
(Proposed by Michael Berkowitz, IPROF 1997) MPE SIB
#74 11 Votes 97A12
13) Minimize the number and duration of locks on
SYSTEM.TABLE upon a user SELECT statement (Proposed by
Michael Berkowitz, IPROF 1997) MPE SIB #71 25 Votes
97A13
14) Enhance the ODBCLINK/SE client and server to
use data encryption and/or encoding to protect sensitive
data across Internets
ODBC data is currently vulnerable to packet sniffing.
This is considered a problem at many universities and
corporate sites with connection to the Internet.
(Documented by Gary Biggs, IPROF 1997) MPE SIB #41 118
Votes 97A14
15) SQL Enabling Extensions for the PERL scripting
language)
Provides an enhanced version of UNIX PERL that
extends that scripting language to include data
retrieval for ALLBASE and IMAGE/SQL Databases. This is a
favorite tool among web masters using other DBMS for
extracting data to include in dynamically generated HTML
from CGI requests to Web servers. (Proposed by Gary
Biggs, HP World 1995) MPE SIB #57 61 votes 97A15
16) Improved distribution mechanisms for ALLBASE
documentation
ALLBASE/SQL documentation is currently out of date
and poorly organized. CD-ROM, HTML and other delivery
mechanisms should be investigated to provide for the
prompt delivery of documentation (Documented by: Gary
Biggs, IPROF 1996) MPE SIB #70 27 Votes 97A16
17) Improved user interface for the ALLBASE DBA
utilities
Development of a integrated GUI front end for
ALLBASE that combines the functionality of SQLUTIL,
SQLGEN, ISQL, etc. in single, consistent user interface.
Possible Third Party Solution is currently available.
(Documented by: Gary Biggs, IPROF 1996) MPE SIB #63 44
Votes 97A17
18) Increase the number of ALLBASE tables and/or views
allowed in a Select statement
A maximum of 31 tables and views (views each count as
one table) can be used in a single select statement.
Some advanced queries (particularly UNIONS) need more
than this. (Documented by Gary Biggs, HP World 1997)
98A01
19) Implement ANSI AS in the Select statement to name
columns
The ALLBASE Select statement need to be enhanced to
allow the AS syntax to name expressions as columns. Some
SQL tools have a difficult time with column names that
result from expressions.
Select sum(number_of_widgets) as sum_of_widgets .
(Proposed by Gary Biggs, HP World 1997) 98A02
20) User Exits (custom functions) for ALLBASE
ALLBASE is not particulary rich in data
manipulation functions. Much of this limitation could be
overcome by providing a mechanism for calling functions
written in C or other traditional computer languages
from ALLBASE. This would be useful in porting
applications to ALLBASE since it would allow the
developer the opportunity to write custom data
manipulation routines that ALLBASE might lack. (From HP
World 1997 Database Roundtable, documented by Gary
Biggs) 98A03
21) Enhance the ALLBASE Optimizer to support TID
scans if the predicate contains more than one TID joined
by OR's
Create view my_view (my_data, TID_COLUMN) as Select
my_data, TID(*) from some_table;
Select * from my_view where TID_COLUMN = 1 or
TID_COLUMN = 2;
Results in a parallel scan of the table (or at least
IMAGE/SQL tables). Changing this behavior would allow
one to create the UNIQUE KEYS on IMAGE/SQL tables that
are needed by many products using the Microsoft Jet
Database Engine. (Submitted by Gary L. Biggs, HP World
1997) 98A04
22) Enhance ALLBASE to limit the number of rows
returned by any SELECT statement using a set option or
an optional parameter on the Select statement.
In the event that the number of qualifying rows exceeds
the threshold, it is suggested that the statement should
return nothing. (from SIG Meeting @ HP World 1997) 98A05
23) Allow ALLBASE functions to be usable as arguments
in procedure statements not just selects.
This would allow the direct assignment of a host
variable without using a select statement:
Example: my_date_value = TO_DATE('08/27/97','MM/DD/YY')
(From SIG Meeting @ HP Wold 1997) 98A06
24) Provide a mechanism to HINT the optimizer regarding
the best or most efficient access path.
Other implementations of SQL are allowing the inclusion
of 'comment' like hint text to specify what index might
yield the best access plan. (From SIG Meeting @ HP World
1997) 98A07
25) SQLMON to display the JOB or SESSION name of the
processing accessing a particular resource.
Currently, it is extremely difficult to determine the
origin a process if they have to share a single logon, a
la ODBC. Inclusion of the JOB or SESSION ID string from
the logon would be very helpful. (From SIG Meeting @ HP
World 1997) 98A08
26) Allow DBA to Enable/Disable DDL from ISQL
DBUTIL is currently used to perform this function. Since
most modifications to DBE structures (tables and views)
occur from scripts used to rebuild a DBE through ISQL, a
DBA should be able to perform this function during an
ISQL session without invoking another utility. The ISQL
SET command already performs this function for a wide
variety of other SQL settings.
>ISQL
connect to 'MYDBE';
set echo on;
set DDL on;
update statistics on MY_OWNER.MY_TABLE;
set DDL off;
exit;
(Documented by Gary Biggs -- Nov. 7, 1997 from Phone
Conversation with SIG Member ) 98A09
27) Provide an AUTOINCREMENT Datatype to ALLBASE
Columns specified as AutoIncrement contain a serialized
value such that with each insertion into the table, the
column is given the next sequential value (ex. the
value of the same column in the last previously inserted
row).
Several other database products contain a similar
DataType. When attempting to port these data structures
(and the applications which use them) to a HP3000 /
HP9000 ALLBASE environment, the developer must make
significant application changes to retain this
functionality. This becomes an impediment to the
migration of additional applications to these systems.
(Submitted by Michael L Gueterman -- Jan 19, 1998) 98A10
Gary L. Biggs, N5TTO
[log in to unmask]
Interex SIG Allbase Chair
"Abandon all hope, Ye who Inter(net) here" --
Dante, over the portal(router) to Hell
|
