Enabling Parallel UDRs

By default, a UDR does not execute in parallel. To enable parallel execution of UDRs, you must take the following actions:

Specifying the PARALLELIZABLE Modifier

When you register a UDR, you must specify the PARALLELIZABLE modifier in the CREATE FUNCTION or ALTER FUNCTION statement. However, an SPL routine is not parallelizable even if it is declared as parallelizable.

Writing PDQ Thread-Safe UDRs

External-language UDRs can execute in parallel as long as they are PDQ thread-safe DataBlade API functions.

If an external-language UDR calls a non-PDQ thread-safe function that was created with the PARALLELIZABLE modifier, the database server aborts the query and issues the following error message:

The database server substitutes the name of the DataBlade API function for the %s string in this error message.

Turning On PDQ and Reviewing Other Configuration Parameters

Parallel execution of queries is turned off by default. To turn on parallel execution, use one of the following methods:

The PDQ configuration parameters have the same effect on parallel UDRs as on regular PDQ queries. For example, the DS_MAX_SCANS parameter specifies the maximum number of scan threads that the database server can execute concurrently.

For information on how to tune the PDQ configuration parameters, refer to the IBM Informix: Performance Guide.

Step-By-Step Procedure to Enable Parallel UDRs

The following procedure includes examples for the tasks described in the previous sections.

To enable parallel UDRs

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;
```
Write a function that is PDQ thread safe.
C Language Support
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);
```
End of C Language Support

For more information on how to write PDQ thread-safe functions, refer to Writing PDQ Thread-Safe UDRs.
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;
```
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.