1

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

Ashutosh
  • 75
  • 1
  • 10
  • I'm not sure I understand your reason for asking: are you not sure wether the sql extensions are installed or not? Do you know that the are installed, but need to 'prefix' the function names in order to run them? Do you suspect that you need execute rights to the functions to use them? – Lars G Olsen Apr 20 '17 at 04:26

2 Answers2

2

To list all the databases run:

select database
from _v_database

The try this command for each database till it works and that is the one with Sql extensions installed on it.

select {databaseName}.admin.greatest(1,2)
Niederee
  • 4,155
  • 25
  • 38
2

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)
ScottMcG
  • 3,867
  • 2
  • 12
  • 21