Enabling parallel UDRs

About this task

This procedure includes examples for the tasks described in the previous sections.

To enable parallel UDRs:

Procedure

  1. Create a fragmented table and load data into it.
    For example, the following SQL statement creates a fragmented table: 
    CREATE TABLE natural_number (x integer)
   FRAGMENT BY round robin
   IN dbspace1, dbspace2;
  2. Write a function that is PDQ thread safe.
    For example, the following C prototype shows a function that takes an integer and determines if it is a prime number: 
    mi_boolean is_prime_number (x mi_integer);

    For more information about how to write PDQ threadsafe functions, refer to Write PDQ threadsafe UDRs.

  3. Register the function as an external UDR and specify the PARALLELIZABLE keyword.
    For example, the following SQL statement registers the is_prime_number UDR: 
    CREATE FUNCTION is_prime_number (x integer)
   RETURNS boolean
   WITH (parallelizable)
   EXTERNAL NAME "$USERFUNCDIR/math.udr"
   LANGUAGE C;
  4. Turn on PDQ and execute the UDR in a query.
    The following sample SQL statements turn on PDQ and execute the UDR in a query:
    SET PDQPRIORITY 100;
SELECT x FROM natural_number
   WHERE is_prime_number(x)
   ORDER BY x;

    The database server scans each fragment of the table natural_number with multiple scan threads executing in parallel. Each scan thread executes the UDR is_prime_number() in parallel.