Netezza Performance Server

 View Only
Expand all | Collapse all

SQL Extensions Toolkit - installation and access

  • 1.  SQL Extensions Toolkit - installation and access

    Posted Tue June 30, 2020 02:12 PM
    How many folks out there use the SQL Extension Toolkit?

    Where did you install it and how do your users access it?

    For example, did you install it into every database that users would need to access the objects? 
    Or did you install it into a single database (per server) and put synonyms in other databases that need access to the objects? 
    Do you have another installation and access methodology for the SQL Extensions Toolkit?

    Chris Rodgers


  • 2.  RE: SQL Extensions Toolkit - installation and access

    Posted Tue June 30, 2020 02:36 PM
    Best practice is to install a database with it, say SQLEXT and grant access to it...

    check these links

    RICARDO Pedraza

  • 3.  RE: SQL Extensions Toolkit - installation and access

    Posted Tue June 30, 2020 03:45 PM
    I agree that the toolkit does not need to be loaded into every database.   I see the suggested method is to load into a single database (per server). 

    The access method suggested is to have users explicitly use cross-database syntax to specify the database that the objects are loaded into OR to set a global search path.  These methods will possibly limit or complicate your access when it comes to upgrades to the toolkit.  

    If you were to upgrade the SQL Extension Toolkit, you could overwrite all objects in the existing database that the previous version of the toolkit is loaded into or you could load into a new database.  If you overwrite the existing database, this requires all apps and users to immediately begin using the new version all at the same time.  You may not be able to coordinate validation or cut-over times across all users of a server at once.  Additionally, if you find issues with an object that someone uses, you may want to keep that app/group on the previous version of the object while upgrading everyone else on the server.  Overwriting the objects in the same database does not allow for two different versions of the same object.  You may also have a scenario where one group of users wants the features of a newer version, but other users/apps on the server do not want to take the time for validation and prefer to continue using the existing version.  Again, that would require two versions of the toolkit, but you would only have one database.

    You could choose to put a newer version of the toolkit into a new database.  Then you have a window of time where users can access both versions.  This is advantageous when users want to do a side-by-side test two different versions with the same production data (same size data, same size appliance, all things being equal).  Having the new version in a new database allows access to multiple versions.  The database names would be different, so the access methodology of the global search path would not work in this scenario. If you have users specifying the database name in cross-database queries, then they would all have to change their queries to start using the objects in the new database.

    A more flexible approach would be to load the SQL Extension Toolkit into a database named with a reference to the version in the name.  For example, create the database SQLEXT_11_0_0_0 for the version of the toolkit.  For users to access the objects, you could create synonyms in the users' database that point to each object in the SQLEXT_11_0_0_0 database.  The SQL used to access the objects will not specify any locations, so the objects will appear as local.  When you want to upgrade, you would create a new database for the new version, such as SQLEXT_11_0_5_0.  As groups validate and approve production use, they can be upgraded simply by switching out the synonyms in their database with synonyms that reference the objects in the new database.  No user/app SQL would have to change and not all users/apps have to upgrade at the same time.  Another advantage is the ability to keep one set of users on the older version while other users are on a newer version (maybe a specific bug-fix for that group). 

    With the synonym access method, it is very clear to see which version a database is accessing just by looking at the synonym definitions.  Once no synonyms point to the database with the old version, the old database can be dropped. Having users/apps do cross-database access to the objects does not make it clear when all users are no longer referencing the older version (weekly processes, quarterly processes, yearly processes, etc). 


    Chris Rodgers

  • 4.  RE: SQL Extensions Toolkit - installation and access

    Posted Tue June 30, 2020 03:59 PM

    Haven't tried this personally, but maybe you could also install each version of SQL Extensions into a new schema (based on version number) in the same database, & point the synonyms to those instead. 

    Of course this is assuming IBM ever releases a new version of SQL Extensions for Netezza (I wouldn't hold your breath waiting for it)

    Huw Ringer

  • 5.  RE: SQL Extensions Toolkit - installation and access

    Posted Tue June 30, 2020 04:09 PM
    Edited by System Fri January 20, 2023 04:12 PM
    True - they may never have an upgrade again to the toolkit... it probably has been quite a while. 

    That approach sounds quite doable, very similar. That also assumes that the system is set up with schemas turned on.  We started long before Netezza supported schemas and we have not had a compelling reason to turn them on and change our architecture, so we have left schemas disabled.

    Chris Rodgers

  • 6.  RE: SQL Extensions Toolkit - installation and access

    Posted Wed July 01, 2020 07:56 PM

    I recommend installing SQLEXTN in a standalone database and adding "search_path = <database>" at the bottom of the "/nz/data/postgresql.conf", which makes it local to each application database.

    Chandhra Vadlamudi

  • 7.  RE: SQL Extensions Toolkit - installation and access

    Posted Thu July 02, 2020 09:58 AM
    It's best to install the toolkit in a single location because it makes  debugging and updating easier.  While it's likely best to use fully qualified references to toolkit functions it's a fair amount of typing, so an alternative is to set the PATH session variable to include the toolkit location. The system will look in the current database for the function and if it's not found it will look at the PATH.  PATH is describe in the stored procedures guide.

    John Skier
    Integration Architect
    682 220 6587

  • 8.  RE: SQL Extensions Toolkit - installation and access

    Posted Thu July 02, 2020 10:18 AM
    What is the likelihood that we will see an upgrade for the toolkit?  When was the last change to the toolkit, besides recompiles for the new 64bit Netezza?

    Chris Rodgers

  • 9.  RE: SQL Extensions Toolkit - installation and access

    Posted Thu July 02, 2020 10:49 AM
    What features are you looking for?   I urge you and everyone else in the community to  look at, vote on, comment on, and add feature request in the AHA  portal. This is used to prioritized enhancements.   There are AHA enhancement pages for most IBM offerings. 
    In today's container world it's possible a suggestion that a new feature be added to the toolkit or INZA package independent of the Core engine may be taken positively. 
    Here is the link to the overview
    Register if needed, login if your registered and present your desires. 
    John Skier 
    IBM Data and AI, Hybrid Data Management, OM  3rd party dbms support

  • 10.  RE: SQL Extensions Toolkit - installation and access

    Posted Thu July 02, 2020 03:25 PM
    John, I am not looking for any new features, I was just asking how long it has been since the last update and if we could expect updates in the future?  Are there any updates currently planned for this product?  I use less than 10% of the functionality, but what I do use is either critical or used heavily for convenience.  We have several different user groups and applications using some of the features of the toolkit, including Analytics and ETL. 

    The only reason we went to synonym access instead of a global path was due to scenarios we had in the past with upgrades.  Since it looks like there have not been any upgrades in a while, I am thinking of moving to the global path access method, and will keep the synonym method in mind if/when an upgrade is available.  That gives us the most flexibility without having users hard-code database names into their code for cross-database access.

    Chris Rodgers

  • 11.  RE: SQL Extensions Toolkit - installation and access

    Posted Thu July 02, 2020 05:00 PM
    The SQLToolkit isn't a product per se.  It was built to fill in gaps in the Netezza offering by a Netezza partner and picked up by Netezza to ensure support for the customer base.   If new functions are built by the user community they could become part of the toolkit.  New functions developed by IBM will likely go directly into the engine or into the INZA package as these are the "invented here" offerings. However, there is a possibility the development team could choose to add features to the toolkit. 
    A closing thought while the toolkit hasn't seen much enhancement the engine  and INZA packages have seen enhancements.

  • 12.  RE: SQL Extensions Toolkit - installation and access

    Posted Mon July 06, 2020 09:46 AM
    SQL toolkit is in active development and there will be defect fixes and minor enhancements from time to time.  If there are particular features/functions that you would like to suggest, please do so and we will evaluate it for future releases.

    Rajshekar Iyer

  • 13.  RE: SQL Extensions Toolkit - installation and access

    Posted Mon July 06, 2020 10:11 AM
    Thanks, Shekar. I see that the version (from Aug 24, 2019, not to be confused with the version from Aug 19, 2019) has a release notes readme file from version  In the future, will the release noted be update for the packaged version?  I would have expected an entry for version mentioning the recompile for 64bit.  Were there any other bug fixes, enhancements, etc, between and the version that is available for download now?

    Chris Rodgers

  • 14.  RE: SQL Extensions Toolkit - installation and access