Resolve Fast Unload message PFU0073E FUNCTION PART XXX INVALID.

Products

• Fast Unload for DB2 for z/OS

Issue/Introduction

Executing Fast Unload for Db2 for z/OS (PFU) to extract data with a SELECT statement from existing partitioned
tables to load into an altered version of that same table.  Requirement to unload by partition due to the size of the
tables and it is more efficient for the load process as well.  The SELECT statement that is required now includes
CASE and SUBSTR syntax. 

PFU execution fails with a message referencing the PART clause: 
     
PFU0130I -- THIS REQUIRES SQL ACCESS WHICH WILL BE USED

PFU0073E - FUNCTION: "PART XXX" INVALID WITH THIS SELECT --           
PFU0074E --THIS FUNCTION NOT AVAILABLE VIA SQL ACCESS, WHICH THIS     
           SELECT REQUIRES                                            
PFU0075E - INVALID SELECT STATEMENT - SEE DOCUMENTATION

Resolution

The SELECT statement specified with CASE and SUBSTR syntax within the PFU execution must be passed
to native Db2 for processing.

One option would be to remove the PART keyword from the SELECT and specify the WHERE clause to unload
by partition based on the partition keys.  If the PARTITION BY RANGE key is a compound key including multiple
columns, it will not be possible to unload truly by partition by specifying the key ranges within the WHERE clause.  

If the unloaded data must be converted in this manner with the CASE, SUBSTR, and concatenation options; the
best choice may be to unload the entire table instead of by partition to make it easier and guarantee success. 

Another option would be to load a non-partition table from a SELECT * BY PART and then do the PFU execution
with CASE|SUBSTR against the "dummy" table, then move to the next partition.