Netezza: create local synonyms for SQL Extention Toolkit

PROBLEM: 

 Improve SQL and NZPLSQL code by eliminating full path references to for SQL Toolkit functions installed in customary named locations: <database>.<schema>.<functionname>. Instead, by calling local <functionname> SQL Function name in the context of a current database to execute this functions and aggregates even though, they are installed in another separate database. Make it standard, portable, and convenient. Consider this as a best-practice intended to save a lot of hassle on the long run for you.

SOLUTION:

Create a database local synonyms that point to functions and aggregates installed in a another separate database. 

Benefits of this best practice method: 

• keep only one version of SQL Toolkit installed per appliance. Install a SQL Toolkit version only once e.g. not in each database.
• toggle between different versions of SQL Toolkit by rebuilding synonyms to point to a version of SQL tookit you need.
• keep SQL/NZPLSQL code portable and standard by keeping code clean off custom database names. Only local standard function calls.  

/* Generate SQL commands to create synonyms */

set catalog SQL_EXTENSIONS;

select * from (

       select distinct 'create synonym '||function ||' for '||current_catalog||'.ADMIN.'||function ||';' from _v_function  where database = current_catalog union all

       select distinct 'create synonym '||aggregate||' for '||current_catalog||'.ADMIN.'||aggregate||';' from _v_aggregate where database = current_catalog union all

       select distinct 'alter synonym ||function ||' owner to ADMIN;'            from _v_function  where database = current_catalog union all

       select distinct 'alter synonym '||aggregate||' owner to ADMIN;'            from _v_aggregate where database = current_catalog

) as F

order by 1 desc;

/* Run the newly generated list of commands in the target database  */

SET CURRENT CATALOG MY_TARGET_DB;  --- Set target DB here where you want to create synonyms at ---

create synonym YEAR for SQL_EXTENSIONS.ADMIN.YEAR;

create synonym XMLUPDATE for SQL_EXTENSIONS.ADMIN.XMLUPDATE;

create synonym XMLSERIALIZE for SQL_EXTENSIONS.ADMIN.XMLSERIALIZE;

create synonym XMLROOT for SQL_EXTENSIONS.ADMIN.XMLROOT;

create synonym XMLPARSE for SQL_EXTENSIONS.ADMIN.XMLPARSE;

create synonym XMLEXTRACTVALUE for SQL_EXTENSIONS.ADMIN.XMLEXTRACTVALUE;

create synonym XMLEXTRACT for SQL_EXTENSIONS.ADMIN.XMLEXTRACT;

create synonym XMLEXISTSNODE for SQL_EXTENSIONS.ADMIN.XMLEXISTSNODE;

create synonym XMLELEMENT for SQL_EXTENSIONS.ADMIN.XMLELEMENT;

create synonym XMLCONCAT for SQL_EXTENSIONS.ADMIN.XMLCONCAT;

     ..... 99 create + 99 alter statements ....  

alter synonym ARRAY_COUNT owner to ADMIN;

alter synonym ARRAY_CONCAT owner to ADMIN;

alter synonym ARRAY_COMBINE owner to ADMIN;

alter synonym ARRAY owner to ADMIN;

alter synonym ADD_ELEMENT owner to ADMIN;

/* Rendered list of 199 SQL commands. Run them all in the target DB */

After running the generated code you must end up exactly with 99 synonyms (as of 2018-03-13 SQL Toolkit  v7.2.1.3)

Related posts:

• IBM Netezza SQL toolkit installation step-by-step
• IBM Netezza SQL Extensions toolkit Version 7.2.1 docuemntation