Tables and SPUPad operations

The stringpad example uses the one_dslice table to essentially “ground” the SPUPad to a single S-Blade and dataslice location. Since one_dslice has only one row and thus is stored in one dataslice, the query creates one SPUPad on the S-Blade that manages that dataslice. If you use a larger table that is distributed across all the dataslices, a similar query would create multiple SPUPads; on each S-Blade, there would be a unique SPUPad for each dataslice that it manages.

Starting in release 6.0, you can use the _v_dual_dslice view to ground the SPUPad to all the dataslices, rather than creating a user table such as multi_dslice to control the execution locus.

If you use a table that has many rows with data evenly distributed across the dataslices, the example function returns the answer multiple times. For example, assume that multi_dslice is a simple table with nine rows which are distributed evenly over eight dataslices on a Skimmer system (which has one S-Blade). Seven dataslices will contain one row of the table, and one dataslice will contain two rows. If you run the same BEGIN/COMMIT transaction commands that are shown in Run the stringpad UDFs, the sample output is as follows:

MYDB.SCHEMA(MYUSER)=> BEGIN TRANSACTION;
BEGIN
MYDB.SCHEMA(MYUSER)=> SELECT string_pad_create(10, 'netezza') FROM 
multi_dslice;
 STRING_PAD_CREATE
-------------------
 t
 t
 f
 t
 t
 t
 t
 t
 t
(9 rows)

As shown in the sample output, there are eight true (t) rows and one false (f) row because the query created eight SPUPads. There is a SPUPad for each dataslice that contains a row of the table. The false response is returned by the dataslice that has two rows of the table because the create_string_pad function checks for the existence of a SPUPad before it creates one. It found the SPUPad from the processing of the first table row on that dataslice, so it did not create another SPUPad.

The next query returns the character at index value 4 of the string ‘netezza’ which is in the SPUPads:

MYDB.SCHEMA(MYUSER)=> SELECT string_pad_get(4) FROM multi_dslice;
 STRING_PAD_GET
----------------
 z
 z
 z
 z
 z
 z
 z
 z
 z
(9 rows)

MYDB.SCHEMA(MYUSER)=> COMMIT; 
COMMIT

This query returns 9 rows, one for each row in multi_dslice.

If you do not specify a FROM clause, as in the following example, the Netezza Performance Server system attempts to run the query in the Postgres environment on the host. SPUPads are not supported in the Postgres environment.

MYDB.SCHEMA(MYUSER)=> SELECT string_pad_create(10, 'netezza');
ERROR:  CPad not supported in postgres

If you specify an external table as the FROM clause, the Netezza Performance Server system runs the query on the host and creates the SPUPad on the host. For example, assume that one_dslice_ext is an external table version of the one_dslice table. You can run the UDX as follows:

MYDB.SCHEMA(MYUSER)=> SELECT string_pad_create(10, 'netezza') FROM 
one_dslice_ext;
 STRING_PAD_CREATE
-------------------
 t
(1 row)

In this example, the function creates the SPUPad on the host in memory, then frees the SPUPad and its memory when the function completes.

If your SPUPad operations read information from a distributed user table, each SPUPad on the S-Blades has access only to the data that is on that S-Blade or that is sent to it by the UDX. If your analysis algorithm requires that the SPUPads have some uniform data across all S-Blades, you can use a mechanism in the UDX to write common data to the SPUPad, or you can create a table that contains the needed rows and also has a datasliceid identification, for example:

CREATE TABLE foo_brdcst AS SELECT d.ds_id-1 AS dsid_, t.* FROM foo t, 
_t_dslice d DISTRIBUTE ON (dsid_);

For extremely complex queries, where data is redistributed or sent to the host, you might not get meaningful or predictable results. To avoid this situation, be explicit with the distribution of tables.