Re[3]: SQL-defined files/tables

["Eric N. Wilson" <doulos1@xxxxxxxx>]

Hello D,

Friday, December 08, 2000, 6:34:00 AM, you wrote:

> Eric,

> How about an example?  For an SQL newbie...

Here you go! Straight from the IBM web site at:
http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafzmstch2data.htm#Header_107

Distinct Types A distinct type is a user-defined data type that shares
its internal representation with a built-in data type (its "source
type"), but is considered to be a separate and incompatible type for
most operations. For example, the semantics for a picture type, a text
type, and an audio type that all use the built-in data type BLOB for
their internal representation are quite different. A distinct type is
created with the SQL statement CREATE DISTINCT TYPE.

For example, the following statement creates a distinct type named AUDIO: 

  CREATE DISTINCT TYPE AUDIO AS BLOB (1M)

Although AUDIO has the same representation as the built-in data type
BLOB, it is considered to be a separate type that is not comparable to
a BLOB or to any other type. This inability to compare AUDIO to other
data types allows functions to be created specifically for AUDIO and
assures that these functions cannot be applied to other data types.

The name of a distinct type is qualified with a collection name. The
implicit collection name for an unqualified name depends upon the
context in which the distinct type appears. If an unqualified distinct
type name is used:


In a CREATE DISTINCT TYPE or the object of DROP, COMMENT ON, GRANT, or
REVOKE statement, the database manager uses the normal process of
qualification by authorization ID to determine the collection name.
For more information about qualification rules, see Unqualified
Function, Procedure, Specific, and Data Type Names.

In any other context, the database manager uses the SQL path to
determine the collection name. The database manager searches the
collections in the path, in sequence, and selects the first collection
that has a distinct type that matches. For a description of the SQL
path, see CURRENT PATH, CURRENT_PATH, or CURRENT FUNCTION PATH. A
distinct type does not automatically acquire the functions and
operators of its source type, since these may not be meaningful. (For
example, the LENGTH function of the AUDIO type might return the length
of its object in seconds rather than in bytes.) Instead, distinct
types support strong typing. Strong typing ensures that only the
functions and operators that are explicitly defined for a distinct
type can be applied to that distinct type. However, a function or
operator of the source type can be applied to the distinct type by
creating an appropriate user-defined function. The user-defined
function must be sourced on the existing function that has the source
type as a parameter.

A distinct type is subject to the same restrictions as its source
type. For example, the maximum length of a distinct type sourced on a
DataLink is 32718.

The comparison operators are automatically generated for distinct
types, except for distinct types that are sourced on a DataLink. In
addition, the database manager automatically generates functions for a
distinct type that support casting from the source type to the
distinct type and from the distinct type to the source type. For
example, for the AUDIO type created above, these cast functions are
generated:

  FUNCTION collection-name.BLOB (collection-name.AUDIO) RETURNS BLOB (1M)
 
  FUNCTION collection-name.AUDIO (BLOB (1M)) RETURNS AUDIO

And for further command definition
http://publib.boulder.ibm.com/pubs/html/as400/v4r4/ic2924/info/db2/rbafzmst81.htm#Header_635

Finally let's show a create Table Statement with the above defined
data type used.

Create Distinct Type WV_UUID As CHAR(32); -- Character Representation
                                          -- of UUID
Create Distinct Type AUDIO As Blob (1M);  -- Sound bite

Create Distinct Type WV_SysName as CHAR(10); -- User IDs Program Names
                                             -- Etc

   CREATE TABLE Lectures
    (UUID                               WV_UUID      NOT NULL WITH DEFAULT,
     Subject                            VarChar(256) NOT NULL,
     SoundBite                          AUDIO,
     Create_User    For Column CRTUSER  WV_SysName   NOT NULL WITH DEFAULT,
     Create_Program For Column CRTPGM   WV_SysName   NOT NULL WITH DEFAULT,
     Create_Stamp   For Column CRTSTAMP TimeStamp    NOT NULL WITH DEFAULT,
     Change_User    For Column CHGUSER  WV_SysName   NOT NULL WITH DEFAULT,
     Change_Program For Column CHGPGM   WV_SysName   NOT NULL WITH DEFAULT,
     Change_Stamp   For Column CHGSTAMP TimeStamp    NOT NULL WITH DEFAULT,
     PRIMARY KEY(WV_UUID));

Note: I am not advocating that you put all these statements into a
single source member :-) I would recommend that you do not.

Secondly everything in the above table that is defined "NOT NULL WITH
DEFAULT" is populated via a trigger program on the *BEFORE *INSERT and
*BEFORE *UPDATE (Only on *CHANGE).

Hope that helps
Eric





----------------------------------
Eric N. Wilson
President
Doulos Software and Computer Services


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---