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 11.0.0.0 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).
Thoughts?
------------------------------
Chris Rodgers
------------------------------
Original Message:
Sent: Tue June 30, 2020 02:35 PM
From: RICARDO Pedraza
Subject: SQL Extensions Toolkit - installation and access
Best practice is to install a database with it, say SQLEXT and grant access to it...
check these links
https://www.ibm.com/support/pages/best-practice-installing-sql-extensions-toolkit-save-storage-space-pda-netezza-appliance-and-cross-reference-across-nps-databases
https://www.ibm.com/support/knowledgecenter/SS5FPD_1.0.0/com.ibm.ips.doc/postgresql/ipsa/manual_inst_uninst.html
------------------------------
RICARDO Pedraza
Original Message:
Sent: Tue June 30, 2020 02:11 PM
From: Chris Rodgers
Subject: SQL Extensions Toolkit - installation and access
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
------------------------------
#NetezzaPerformanceServer