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

Oracle: sys_context() getting all user environment variables

PROBLEM: 

Get list and values of ALL available user environment variables in an Oracle session. All in one shot. 

SOLUTION:

 select sys_context('userenv','ACTION'                   ) as FUNC,'ACTION' as PARAM  from dual union all

 select sys_context('userenv','AUDITED_CURSORID'         ),'AUDITED_CURSORID'         from dual union all

 select sys_context('userenv','AUTHENTICATED_IDENTITY'   ),'AUTHENTICATED_IDENTITY'   from dual union all

 select sys_context('userenv','AUTHENTICATION_DATA'      ),'AUTHENTICATION_DATA'      from dual union all

 select sys_context('userenv','AUTHENTICATION_METHOD'    ),'AUTHENTICATION_METHOD'    from dual union all

 select sys_context('userenv','BG_JOB_ID'                ),'BG_JOB_ID'                from dual union all

 select sys_context('userenv','CLIENT_IDENTIFIER'        ),'CLIENT_IDENTIFIER'        from dual union all

 select sys_context('userenv','CLIENT_INFO'              ),'CLIENT_INFO'              from dual union all

 select sys_context('userenv','CURRENT_BIND'             ),'CURRENT_BIND'             from dual union all

 select sys_context('userenv','CURRENT_EDITION_ID'       ),'CURRENT_EDITION_ID'       from dual union all

 select sys_context('userenv','CURRENT_EDITION_NAME'     ),'CURRENT_EDITION_NAME'     from dual union all

 select sys_context('userenv','CURRENT_SCHEMA'           ),'CURRENT_SCHEMA'           from dual union all

 select sys_context('userenv','CURRENT_SCHEMAID'         ),'CURRENT_SCHEMAID'         from dual union all

 select sys_context('userenv','CURRENT_SQL'              ),'CURRENT_SQL'              from dual union all

 select sys_context('userenv','CURRENT_SQL7'             ),'CURRENT_SQL7'             from dual union all

 select sys_context('userenv','CURRENT_SQL_LENGTH'       ),'CURRENT_SQL_LENGTH'       from dual union all

 select sys_context('userenv','CURRENT_USER'             ),'CURRENT_USER'             from dual union all

 select sys_context('userenv','CURRENT_USERID'           ),'CURRENT_USERID'           from dual union all

 select sys_context('userenv','DATABASE_ROLE'            ),'DATABASE_ROLE'            from dual union all

 select sys_context('userenv','DB_DOMAIN'                ),'DB_DOMAIN'                from dual union all

 select sys_context('userenv','DB_NAME'                  ),'DB_NAME'                  from dual union all

 select sys_context('userenv','DB_UNIQUE_NAME'           ),'DB_UNIQUE_NAME'           from dual union all

 select sys_context('userenv','DBLINK_INFO'              ),'DBLINK_INFO'              from dual union all

 select sys_context('userenv','ENTRYID'                  ),'ENTRYID'                  from dual union all

 select sys_context('userenv','ENTERPRISE_IDENTITY'      ),'ENTERPRISE_IDENTITY'      from dual union all

 select sys_context('userenv','FG_JOB_ID'                ),'FG_JOB_ID'                from dual union all

 select sys_context('userenv','GLOBAL_CONTEXT_MEMORY'    ),'GLOBAL_CONTEXT_MEMORY'    from dual union all

 select sys_context('userenv','GLOBAL_UID'               ),'GLOBAL_UID'               from dual union all

 select sys_context('userenv','HOST'                     ),'HOST'                     from dual union all

 select sys_context('userenv','IDENTIFICATION_TYPE'      ),'IDENTIFICATION_TYPE'      from dual union all

 select sys_context('userenv','INSTANCE'                 ),'INSTANCE'                 from dual union all

 select sys_context('userenv','INSTANCE_NAME'            ),'INSTANCE_NAME'            from dual union all

 select sys_context('userenv','IP_ADDRESS'               ),'IP_ADDRESS'               from dual union all

 select sys_context('userenv','ISDBA'                    ),'ISDBA'                    from dual union all

 select sys_context('userenv','LANG'                     ),'LANG'                     from dual union all

 select sys_context('userenv','LANGUAGE'                 ),'LANGUAGE'                 from dual union all

 select sys_context('userenv','MODULE'                   ),'MODULE'                   from dual union all

 select sys_context('userenv','NETWORK_PROTOCOL'         ),'NETWORK_PROTOCOL'         from dual union all

 select sys_context('userenv','NLS_CALENDAR'             ),'NLS_CALENDAR'             from dual union all

 select sys_context('userenv','NLS_CURRENCY'             ),'NLS_CURRENCY'             from dual union all

 select sys_context('userenv','NLS_DATE_FORMAT'          ),'NLS_DATE_FORMAT'          from dual union all

 select sys_context('userenv','NLS_DATE_LANGUAGE'        ),'NLS_DATE_LANGUAGE'        from dual union all

 select sys_context('userenv','NLS_SORT'                 ),'NLS_SORT'                 from dual union all

 select sys_context('userenv','NLS_TERRITORY'            ),'NLS_TERRITORY'            from dual union all

 select sys_context('userenv','OS_USER'                  ),'OS_USER'                  from dual union all

 select sys_context('userenv','POLICY_INVOKER'           ),'POLICY_INVOKER'           from dual union all

 select sys_context('userenv','PROXY_ENTERPRISE_IDENTITY'),'PROXY_ENTERPRISE_IDENTITY'from dual union all

 select sys_context('userenv','PROXY_USER'               ),'PROXY_USER'               from dual union all

 select sys_context('userenv','PROXY_USERID'             ),'PROXY_USERID'             from dual union all

 select sys_context('userenv','SERVER_HOST'              ),'SERVER_HOST'              from dual union all

 select sys_context('userenv','SERVICE_NAME'             ),'SERVICE_NAME'             from dual union all

 select sys_context('userenv','SESSION_EDITION_ID'       ),'SESSION_EDITION_ID'       from dual union all

 select sys_context('userenv','SESSION_EDITION_NAME'     ),'SESSION_EDITION_NAME'     from dual union all

 select sys_context('userenv','SESSION_USER'             ),'SESSION_USER'             from dual union all

 select sys_context('userenv','SESSION_USERID'           ),'SESSION_USERID'           from dual union all

 select sys_context('userenv','SESSIONID'                ),'SESSIONID'                from dual union all

 select sys_context('userenv','SID'                      ),'SID'                      from dual union all

 select sys_context('userenv','STATEMENTID'              ),'STATEMENTID'              from dual union all

 select sys_context('userenv','TERMINAL'                 ),'TERMINAL'                 from dual union all

 select sys_context('userenv','AUTHENTICATION_TYPE'      ),'AUTHENTICATION_TYPE'      from dual union all

 select sys_context('userenv','EXTERNAL_NAME'            ),'EXTERNAL_NAME'            from dual

;

---

Get same SQL version with extended detailed explanation of each variable:

• get_all_sys_context_variables.sql.txt

Also see documentation posts:

• Oracle Database Online Documentation, 10g Release 2 (10.2) / SYS_CONTEXT
• Oracle SYS_CONTEXT Function