sqldatabasenetezzapostgresql-extensions

How to find the database name in netezza in which SQL Extensions Toolkit is intalled?


I am trying to find out the database in which SQL Extensions Toolkit was installed.


Solution

  • One thing to keep in mind is that the SQL Extension Toolkit is a collection of user defined functions, and they can be installed in multiple databases, and each installation could be at a different level.

    If you have access to the Netezza host, you can use nz_find_object to look for the REGEXP_VERSION function which is in every SQL Extensions installation (and will also then tell you what version is installed).

    [nz@netezza ~]$ /nz/support/bin/nz_find_object REGEXP_VERSION

      The Object Name Is   | It Is Of Type | Its 'objid' Is | In The Database | In The Schema 
    -----------------------+---------------+----------------+-----------------+---------------
     REGEXP_VERSION#239027 | FUNCTION      |         239027 | SQLEXT          | ADMIN
    (1 row)
    

    Or you can use this SQL:

    select OBJNAME, DATABASE from _t_object o
    left outer join _v_database d on o.objdb = d.objid
    where OBJNAME like 'REGEXP_VERSION#%';
            OBJNAME        | DATABASE 
    -----------------------+----------
     REGEXP_VERSION#239027 | SQLEXT
    (1 row)
    
    select sqlext..REGEXP_VERSION();                                                                            REGEXP_VERSION                                    
    --------------------------------------------------------------------------------------
     IBM Netezza SQL Extensions XML / Regular Expression Library Version 7.2.1.3 Build ()
    (1 row)