Retrieving subtype data from Db2

If your data model takes advantage of subtypes, a value in a column could be one of many different subtypes. You can dynamically choose the correct transform functions based on the actual input type.

   SELECT Address
      FROM Person
      INTO :hvaddr;

To ensure that the proper transforms are invoked:

1. Create a FROM SQL function transform for each variation of address:

     CREATE FUNCTION addresstofunc(A address_t)
        RETURNS ROW 
        (Street VARCHAR(30), Number CHAR(15), City
        VARCHAR(30), STATE VARCHAR (10))
        LANGUAGE SQL
        RETURN VALUES 
        (A..Street, A..Number, A..City, A..State)
   
      CREATE FUNCTION US_addresstofunc(A US_addr_t)
        RETURNS ROW 
        (Street VARCHAR(30), Number CHAR(15), City
        VARCHAR(30), STATE VARCHAR (10), Zip
        CHAR(10))
        LANGUAGE SQL
        RETURN VALUES 
        (A..Street, A..Number, A..City, A..State, A..Zip)

2. Create transform groups, one for each type variation:

     CREATE TRANSFORM FOR Address_t
        funcgroup1 (FROM SQL WITH FUNCTION addresstofunc)
   
     CREATE TRANSFORM FOR US_addr_t
        funcgroup2 (FROM SQL WITH FUNCTION US_addresstofunc)

3. Create external UDFs, one for each type variation.

   Register the external UDF for the Address_t type:

     CREATE FUNCTION address_to_client (A Address_t)
        RETURNS VARCHAR(150)
        LANGUAGE C
        EXTERNAL NAME 'addressudf!address_to_client'
        ...
        TRANSFORM GROUP funcgroup1

   Write the address_to_client UDF:

     void SQL_API_FN address_to_client(
        SQLUDF_VARCHAR *street,
        SQLUDF_CHAR    *number,
        SQLUDF_VARCHAR *city,
        SQLUDF_VARCHAR *state,
        SQLUDF_VARCHAR *output,
   
        /* Null indicators for attributes */
        SQLUDF_NULLIND *street_ind,
        SQLUDF_NULLIND *number_ind,
        SQLUDF_NULLIND *city_ind,  
        SQLUDF_NULLIND *state_ind,
        /* Null indicator for instance */
        SQLUDF_NULLIND *address_ind,
        /* Null indicator for output */
        SQLUDF_NULLIND *output_ind,
        SQLUDF_TRAIL_ARGS)
   
     {
        sprintf (output, "[address_t] [Street:%s] [number:%s]
        [city:%s] [state:%s]",
        street, number, city, state);
        *output_ind = 0;
     }

   Register the external UDF for the US_addr_t type:

     CREATE FUNCTION address_to_client (A US_addr_t)
        RETURNS VARCHAR(150)
        LANGUAGE C
        EXTERNAL NAME 'addressudf!US_addr_to_client'
        ...
        TRANSFORM GROUP funcgroup2

   Write the US_addr_to_client UDF:

     void SQL_API_FN US_address_to_client(
        SQLUDF_VARCHAR  *street,
        SQLUDF_CHAR     *number,
        SQLUDF_VARCHAR  *city,
        SQLUDF_VARCHAR  *state,
        SQLUDF_CHAR     *zip,
        SQLUDF_VARCHAR  *output,
   
        /* Null indicators */
        SQLUDF_NULLIND  *street_ind,
        SQLUDF_NULLIND  *number_ind,
        SQLUDF_NULLIND  *city_ind,  
        SQLUDF_NULLIND  *state_ind, 
        SQLUDF_NULLIND  *zip_ind,
        SQLUDF_NULLIND  *us_address_ind,
        SQLUDF_NULLIND  *output_ind,
        SQLUDF_TRAIL_ARGS)
   
     {
        sprintf (output, "[US_addr_t] [Street:%s] [number:%s]
        [city:%s] [state:%s] [zip:%s]",
        street, number, city, state, zip);
        *output_ind = 0;
     }

4. Create a SQL-bodied UDF that chooses the correct external UDF to process the instance.
   The following UDF uses the TREAT specification in SELECT statements combined by a UNION ALL clause to invoke the correct FROM SQL client transform:

     CREATE FUNCTION addr_stream (ab Address_t)
        RETURNS VARCHAR(150)
        LANGUAGE SQL
        RETURN
        WITH temp(addr) AS
        (SELECT address_to_client(ta.a)
           FROM TABLE (VALUES (ab)) AS ta(a)
           WHERE ta.a IS OF (ONLY Address_t)
           UNION ALL
        SELECT address_to_client(TREAT (tb.a AS US_addr_t))
           FROM TABLE (VALUES (ab)) AS tb(a)
           WHERE tb.a IS OF (ONLY US_addr_t))
        SELECT addr FROM temp;

   At this point, applications can invoke the appropriate external UDF by invoking the Addr_stream function:

     SELECT Addr_stream(Address)
        FROM Employee;

5. Add the Addr_stream external UDF as a FROM SQL client transform for Address_t:

     CREATE TRANSFORM GROUP FOR Address_t
        client_group (FROM SQL
        WITH FUNCTION Addr_stream)

   Note: If your application might use a type predicate to specify particular address types in the query, add Addr_stream as a FROM SQL to client transform for US_addr_t. This ensures that Addr_stream can be invoked when a query specifically requests instances of US_addr_t.
6. Bind the application with the TRANSFORM GROUP option set to client_group.

     PREP myprogram TRANSFORM GROUP client_group