StarQuest: keeping data in motion

 

StarQuest Technical Documents

StarSQL User Requirements for Dynamic SQL Catalog Queries

Last Update: 17 February 2009
Product: StarSQL
Version: 5.36 or later
Article ID: SQV00SQ051

Abstract

To improve performance when connected to DB2/zOS servers, StarSQL 5.36 and later executes dynamic SQL statements for catalog queries.  This behavior is different than in older versions of StarSQL (pre 5.36 versions), where static SQL located in the driver's host packages was used to perform all catalog queries. In order for StarSQL to use dynamic SQL for catalog queries, users must be granted SELECT privileges to certain catalog tables.

Solution

Whether or not StarSQL uses dynamic or static SQL for catalog queries is determined by the UseDynamicCatalog parameter. When using UseDynamicCatalogSQL = YES in the StarSQL DSN (which is the default setting), users need SELECT privileges to the following catalog tables:

SYSTABLES
SYSCOLUMNS
SYSINDEXES
SYSKEYS
SYSSYNONYMS
SYSRELS
SYSFOREIGNKEYS
SYSROUTINES
SYSPARMS
SYSDUMMY1

It is recommended that SELECT privilege be granted to PUBLIC so that all StarSQL users can access the catalog tables needed for specific application operations. The following is an example of a SQL staement that grants select privilege to PUBLIC:

GRANT SELECT ON SYSIBM.SYSROUTINES TO PUBLIC

If you want the StarSQL driver to use static SQL for catalog functions, set the UseDynamicCatalogSQL parameter to No in the StarSQL DSN.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.