DB2 10.5 for Linux, UNIX, and Windows

Returning subtype data to DB2

This section provides information about how to return subtype data to DB2®

About this task

Suppose you want to insert a structured type into a DB2 database from an application using the following syntax:

   INSERT INTO person (Oid, Name, Address)
      VALUES ('n', 'Norm', :hvaddr);

Procedure

To run the INSERT statement for a structured type:

Create a TO SQL function transform for each variation of address. The following example shows SQL-bodied UDFs that transform the Address_t and US_addr_t types:

   CREATE FUNCTION functoaddress
    (str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10))
   RETURNS Address_t
   LANGUAGE SQL
   RETURN Address_t()..street(str)..number(num)..city(cy)..state(st);

   CREATE FUNCTION functoaddress
    (str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10), 
     zp CHAR(10))
   RETURNS US_addr_t
   LANGUAGE SQL
   RETURN US_addr_t()..street(str)..number(num)..city(cy)
       ..state(st)..zip(zp);

Create transform groups, one for each type variation:

   CREATE TRANSFORM FOR Address_t
      funcgroup1 (TO SQL
      WITH FUNCTION functoaddress);

   CREATE TRANSFORM FOR US_addr_t
      funcgroup2 (TO SQL
      WITH FUNCTION functousaddr);

Create external UDFs that return the encoded address types, one for each type variation.

   CREATE FUNCTION client_to_address (encoding VARCHAR(150))
      RETURNS Address_t
      LANGUAGE C
      TRANSFORM GROUP funcgroup1
      ...
      EXTERNAL NAME 'address!client_to_address';

Write the external UDF for the Address_t version of client_to_address:

   void SQL_API_FN client_to_address (
      SQLUDF_VARCHAR *encoding,
      SQLUDF_VARCHAR *street,
      SQLUDF_CHAR    *number,
      SQLUDF_VARCHAR *city,
      SQLUDF_VARCHAR *state,

      /* Null indicators */
      SQLUDF_NULLIND *encoding_ind,
      SQLUDF_NULLIND *street_ind,  
      SQLUDF_NULLIND *number_ind,
      SQLUDF_NULLIND *city_ind,  
      SQLUDF_NULLIND *state_ind,
      SQLUDF_NULLIND *address_ind,
      SQLUDF_TRAIL_ARGS )
   {
      char c[150];
      char *pc;

      strcpy(c, encoding);

      pc = strtok (c, ":]");
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (street, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (number, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (city, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (state, pc);

      *street_ind = *number_ind = *city_ind 
      = *state_ind = *address_ind = 0;
   }

   CREATE FUNCTION client_to_us_address (encoding VARCHAR(150))
      RETURNS US_addr_t
      LANGUAGE C
      TRANSFORM GROUP funcgroup1
      ...
      EXTERNAL NAME 'address!client_to_US_addr';

Write the external UDF for the US_addr_t version of client_to_address:

   void SQL_API_FN client_to_US_addr(
      SQLUDF_VARCHAR *encoding,
      SQLUDF_VARCHAR *street,
      SQLUDF_CHAR    *number,
      SQLUDF_VARCHAR *city,
      SQLUDF_VARCHAR *state,
      SQLUDF_VARCHAR *zip,

      /* Null indicators */
      SQLUDF_NULLIND *encoding_ind,
      SQLUDF_NULLIND *street_ind,  
      SQLUDF_NULLIND *number_ind,
      SQLUDF_NULLIND *city_ind,  
      SQLUDF_NULLIND *state_ind,
      SQLUDF_NULLIND *zip_ind,
      SQLUDF_NULLIND *us_addr_ind,
      SQLUDF_TRAIL_ARGS)

   {
      char c[150];
      char *pc;

      strcpy(c, encoding);

      pc = strtok (c, ":]");
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (street, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strncpy (number, pc,14);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (city, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strcpy (state, pc);
      pc = strtok (NULL, ":]");
      pc = strtok (NULL, ":]");
      strncpy (zip, pc, 9);

      *street_ind = *number_ind = *city_ind 
      = *state_ind = *zip_ind = *us_addr_ind = 0; 
   }

Create a SQL-bodied UDF that chooses the correct external UDF for processing that instance. The following UDF uses the TYPE predicate to invoke the correct client transform. The results are placed in a temporary table:

   CREATE FUNCTION stream_address (ENCODING VARCHAR(150))
      RETURNS Address_t
      LANGUAGE SQL
      RETURN
      (CASE(SUBSTR(ENCODING,2,POSSTR(ENCODING,']')-2))
      WHEN 'address_t'
         THEN client_to_address(ENCODING)
      WHEN 'us_addr_t'
         THEN client_to_us_addr(ENCODING)
      ELSE NULL
      END);

Add the stream_address UDF as a TO SQL client transform for Address_t:

   CREATE TRANSFORM FOR Address_t
      client_group (TO SQL
      WITH FUNCTION stream_address);

Bind the application with the TRANSFORM GROUP option set to client_group.
```
   PREP myProgram2 TRANSFORM GROUP client_group
```

What to do next

When the application containing the INSERT statement with a structured type is bound, DB2 for Linux, UNIX, and Windows looks for a TO SQL client transform. DB2 for Linux, UNIX, and Windows looks for the transform in the transform group client_group because that is the TRANSFORM GROUP specified at bind time in Step 6. DB2 for Linux, UNIX, and Windows finds the transform function it needs: stream_address, which is associated with the root type Address_t in Step 5.

stream_address is a SQL-bodied function, defined in Step 4, so it has no stated dependency on any additional transform function. For input parameters, stream_address accepts VARCHAR(150), which corresponds to the application host variable :hvaddr. stream_address returns a value that is both of the correct root type, Address_t, and of the correct dynamic type.

stream_address parses the VARCHAR(150) input parameter for a substring that names the dynamic type: in this case, either 'Address_t' or 'US_addr_t'. stream_address then invokes the corresponding external UDF to parse the VARCHAR(150) and returns an object of the specified type. There are two client_to_address() UDFs, one to return each possible type. These UDFs are defined in Step 3. Each UDF takes the input VARCHAR(150), and internally constructs the attributes of the appropriate structured type, thus returning the structured type.

To return the structured types, each UDF needs a TO SQL transform function to construct the output attribute values into an instance of the structured type. The CREATE FUNCTION statements in Step 3 name the TRANSFORM GROUP that contains the transforms.

The SQL-bodied transform functions from Step 1, and the associations with the transform groups from Step 2, are named in the CREATE FUNCTION statements of Step 3.