How to push down group_concat function to Netezza in TIBCO Data Virtualization (TDV)?

How to push down group_concat function to Netezza in TIBCO Data Virtualization (TDV)?

book

Article ID: KB0075092

calendar_today

Updated On:

Products

TIBCO Data Virtualization

Description

How to push down group_concat function to Netezza in TIBCO Data Virtualization (TDV) even though the query works in TDV ?

In other words, how to resolve : No Push Reason:Cannot push selectable because the data source Netezza does not support the function --GROUP_CONCAT(USER2.FIRST_NAME, ',')-- while executing a query with group_concat on a Netezza data source ?

User-added image
 

Issue/Introduction

How to pushdown group_concat function to Netezza in TIBCO Data Virtualization (TDV)?

Resolution

Netezza does not natively support the Group_Concat function (See referenced links below) . Netezza  Group concat is a User defined function (UDF) provided by IBM . It may require for the Netezzza SQL Extensions toolkit to be installed in the database that is queried from TIBCO Data Virtualization (TDV) . If the Netezza SQL Extensions Toolkit has been installed in the database, the query with group_concat function can be pushed down to Netezza by following the below steps :

1. Navigate to [TDV Installation Directory]/apps\dlm\cis_ds_netezza\conf\ and locate the netezza capabilities file named - netezza_7_0.netezza.capabilities and the below listed lines:
   GROUP_CONCAT(~string): GROUP_CONCAT($1)
   GROUP_CONCAT(~string,~string): GROUP_CONCAT($1, $2)
   
   User-added image

2. Restart the TDV Server Service. If using windows, you can launch Control Panel>>System and security>>Administrative Tools>>Services and find the service named "TDV Server [Version] and click on Restart. If using Linux, navigate to [TDV Install direcory]/bin and execute composite.sh Server stop followed by composite.sh Server Start

3. Reconnect the TDV Studio session and execute the query. The group_concat function is now pushed down to the Netezza database.

User-added image

NOTE: If the query execution fails with the below error :

"Cause: ERROR:  Function 'GROUP_CONCAT(VARCHAR, UNKNOWN)' does not exist
 Unable to identify a function that satisfies the given argument types"

This is likely because the Netezza SQL Extensions Toolkit is not installed in the Netezza database.
 

Additional Information