SUMMARY:

PowerBuilder offers several options for controlling the table list that appears in the Database and DataWindow painters. One or more of these options can significantly improve performance as well as 'hide' tables that are not of interest to a development team.

Document ID:	47632	Last Revised:	04/12/99
Topic:	Performance & Tuning	Document Type:	TechNote
Product:	PowerBuilder	Version:	7.0; 6.0
Platform:	PC	Operating System:	Windows 3.1, Windows 95, Windows NT

Document:

　　PowerBuilder offers several options for controlling the table list that appears in the Database and DataWindow painters. One or more of these options can significantly improve performance as well as 'hide' tables that are not of interest to a development team. This document addresses such techniques as they apply to PowerBuilder's native interfaces to the MDI Gateway, Sybase Net Gateway, DirectConnect Server, and IBM DRDA databases (for 16-bit PowerBuilder) as well as to ODBC connections via IBM's Client Application Enabler (CAE) ODBC driver.

　　Depending upon the database interface used, there are several options available; they fall into the following general categories:

1. PowerBuilder connection parameter (DBParm) settings,
2. PowerBuilder's ODBC INI file settings,
3. Driver-specific options, and
4. PowerBuilder development environment settings.

　　The following settings in the PowerBuilder database profile affect what tables are displayed in the table list. In PowerBuilder 6.0 and later, these settings can be found in the various tabs of the Database Profile painter; prior to version 6.0, these settings were placed in the DBParm string directly (below, the DBParm option name follows the tabbed dialog edit label in each section heading).

　　The examples that follow reflect setting these options as part of a DBParm. When using PowerBuilder 6.0 or later, specify the identical syntax, with the exception of the leading and trailing quotation marks, in the appropriate edit field on the tabbed dialog. Further documentation for these parameters can be found in the Connecting to Your Database reference or in PowerBuilder on-line help.

　　1.1 Group Authorization ID/GroupID (IBM DRDA and MDI Gateway).

　　N.B. The PBMDI native driver is no longer available beginning with PowerBuilder 7.0. Additionally, the MDI Gateway has reached its end-of-life as a product supported by Sybase. Customers should be migrating (or have migrated) to the DirectConnect Server and use the PBDIR native driver for PowerBuilder connectivity. PBMDI can be used as an interim solution for connectivity via the DirectConnect DB2 Access Service as long as the DirectConnect Access Service is configured to run in 'gateway compatible' mode.

　　N.B. The PBIBM interface is available in 16-bit only due to the underlying IBM CAE requirements. Additionally, this interface is based on extremely old technology that is no longer supported by IBM. Customers that have connected via this means in the past should have already changed client connectivity to other mechanisms, such as IBM's CAE ODBC driver.

　　By default the DB2 tables that are displayed in the table list are those for which the user has select privilege. Using this parameter causes tables that have select privilege granted to the specified group ID (in addition to the user id and PUBLIC) to be included in the table list. For example, a user who logs on with a user id of 'USER001' and sets the DBParm GroupID='GROUP001' will cause the following SELECT statement from the DB2 system tables to be generated:

SELECT DISTINCT SYSIBM.SYSTABAUTH.TCREATOR, SYSIBM.SYSTABAUTH.TTNAME,
SYSIBM.SYSTABLES.TYPE
FROM SYSIBM.SYSTABAUTH, SYSIBM.SYSTABLES
WHERE (SYSIBM.SYSTABAUTH.TTNAME = SYSIBM.SYSTABLES.NAME
AND SYSIBM.SYSTABAUTH.TCREATOR = SYSIBM.SYSTABLES.CREATOR
AND (SYSIBM.SYSTABAUTH.GRANTEE = 'USER001' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'GROUP001' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'PUBLIC' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'public' )
AND (SYSIBM.SYSTABAUTH.SELECTAUTH = 'G' OR
SYSIBM.SYSTABAUTH.SELECTAUTH = 'Y'))

　　

1.2 Table Criteria/TableCriteria (IBM DRDA and MDI Gateway).

　　N.B. See the notes in Section 1.1 on the support for these two native interfaces.

　　For these two database interfaces, specifying TableCriteria appends another condition to the WHERE clause of the SELECT statement described in paragraph 1.1 above. As an example, if the user sets the parameter

　　TableCriteria='NAME like 'OR%' AND CREATOR IN ('FRANS','MIKE')'

　　the following SELECT statement would be issued against the system catalogs:

SELECT DISTINCT SYSIBM.SYSTABAUTH.TCREATOR, SYSIBM.SYSTABAUTH.TTNAME,
SYSIBM.SYSTABLES.TYPE
FROM SYSIBM.SYSTABAUTH, SYSIBM.SYSTABLES
WHERE (SYSIBM.SYSTABAUTH.TTNAME = SYSIBM.SYSTABLES.NAME
AND SYSIBM.SYSTABAUTH.TCREATOR = SYSIBM.SYSTABLES.CREATOR
AND (SYSIBM.SYSTABAUTH.GRANTEE = 'USER001' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'GROUP001' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'PUBLIC' OR
SYSIBM.SYSTABAUTH.GRANTEE = 'public' )
AND (SYSIBM.SYSTABAUTH.SELECTAUTH = 'G' OR
SYSIBM.SYSTABAUTH.SELECTAUTH = 'Y')
AND (NAME LIKE 'QR%' AND CREATOR IN ('FRANS','MIKEC')))

　　1.3 Table Criteria/TableCriteria (ODBC).

　　For ODBC data sources, this parameter allows a user to specify four arguments that will be sent to the SQLTables ODBC function as invoked by PowerBuilder. Those arguments are as follows (for the first three, the wildcards '%' and '_' are supported):

table_name_criteria - display table objects matching this name
table_owner_criteria - display table objects owned by matching owner
table_qualifier_criteria - display table objects belonging to the specified
qualifier
table_type_criteria - display objects of these types in table list. The
format is a comma-delimited list of strings, each
string is enclosed in single quotes.

　　These parameters are positional, so if one or more preceding parameters do not apply, an appropriate number of commas must be included to indicate that. As an example, the following TableCriteria will list all table objects whose names begin with CUST and which are owned by SDG.

TableCriteria='CUST%,SDG'

　　This next example includes in the table list only those synonyms whose names begin with CUST. (Note the use of two single-quotation marks around SYNONYM to resolve to one single quote when parsed by PowerBuilder):

TableCriteria='CUST%,,,'SYNONYM''

　　1.4 Table Criteria/TableCriteria (Sybase Net Gateway and Sybase DirectConnect).

　　For the Sybase Net Gateway and DirectConnect, the table list that is retrieved is the result set returned by the sp_tables remote stored procedure (RSP) or remote procedure call (RPC). By using TableCriteria, one can supply arguments to this procedure to affect what tables are returned. These arguments are as follows:

table_name - display table objects matching this criterion, which is
enclosed in single quotes
table_owner - display table objects owned by schemas matching the
specified criterion, which is enclosed in single quotes
table_qualifier - not used
table_type - display objects of these types in table list. Each item
in this list is enclosed in two single quotes, and the
entire list is then enclosed in double-quotes.

　　Like with ODBC, these parameters are positional, so if one or more parameters do not apply, the appropriate number of preceding commas must be included to indicate that. As an example, the following TableCriteria will list all views and synonyms whose names begin with CUST. (Note the use of two single-quotation marks around the entries to resolve to one single quote when parsed by PowerBuilder):

TableCriteria=''CUST%',,,"'VIEW','SYNONYM'"'

　　In PowerBuilder 7.0, the table criteria are specified via two single-line edit controls (the owner name and the table name) and multiple checkboxes specifying object type (table, view, etc.).

　　

1.5 System Owner/SystemOwner (IBM and MDI Gateway).

　　N.B. See the notes in Section 1.1 on the support for these two native interfaces.

　　This parameter specifies the owner of the DB2 system tables to be used when responding to database catalog requests (such as obtaining a table list). By default, PowerBuilder uses SYSIBM; changing this value allows one to implement Shadow Catalogs (see Technical Document 44430 Improving DB2 Performance With a Shadow Catalog) that contain a subset of the catalog information for the DB2 subsystem as stored in the SYSIBM tables. When using this parameter, the table list will contain only those table objects for which the catalog information has been inserted into the appropriate system tables qualified by the value assigned to SystemOwner.

　　2. PowerBuilder's ODBC INI File Settings (ODBC Only).

　　This file, which must be located in the same directory as the instance of the PowerBuilder ODBC interface DLL that is loaded by PowerBuilder, contains settings that can modify the default behavior of any ODBC driver. For all releases of PowerBuilder, the INI file name is the same as the corresponding PowerBuilder ODBC interface DLL. For PowerBuilder 5.0, the file is PBODB050.INI; for PowerBuilder 6.0, the file is named PBODB60.INI; and for PowerBuilder 7.0, the file is PBODB70.INI. This file itself can contain sections for specific ODBC drivers and/or data sources. Within each section, the following settings can affect the display of the table list.

　　2.1 TableListType.

　　The entries in this parameter are used to filter the list of tables that has been returned by the ODBC driver, it has no effect on what is sent to the DBMS to qualify the request for tables. PowerBuilder automatically includes only those objects in the table list that have a table type of 'SYSTEM TABLE',' 'SYSTEM VIEW,' 'TABLE,' and 'VIEW.' If TableListType is specified, then objects of the types in that list will also be included in the table list presented to the user.

　　For instance, to include synonyms and aliases in the table list, the following entry in the INI file is required under the section for the desired ODBC driver or data source:

TableListType='SYNONYM,ALIAS'

　　2.2 PBTableOwner (use cautiously).

　　This item affects whether or not tables are qualified with owner names both within the table list and when constructing SQL statements within the painters. If set to 'NO', then none of the tables in the table list will be qualified with owner names, nor will qualifiers appear on the tables when DataWindows are built. If there are multiple tables of the same name but with different owners, multiple entries will appear in the list, and they will be indistinguishable.

　　Since tables may no longer be qualified with owner names, this setting can result in unexpected errors. For instance, if the PowerBuilder catalog tables are not owned by the current user, then lookups in those tables will fail. Additionally, DataWindows constructed against tables not owned by the current user will either fail to find the desired table or perform the retrieval from an identically named table owned by the current user.

　　

2.3 PBSystemOwner.

　　PBSystemOwner specifies a comma delimited list of schemas that are to be considered system tables for the given data source. Tables within the schemas included in PBSystemOwner, will not appear in the table list dialog unless the Show System Tables option is in effect. The INI file contains defaults for most data sources; however, a user may wish to add other entries. For instance, since in ODBC the PowerBuilder catalog tables can be created and accessed with any valid schema name, a user may wish to include the owner of those tables (say, PBCATOWN) in the PBSystemOwner parameter to prevent them from appearing by default in the table list:

PBSystemOwner='SYSIBM,SYSSTAT,SYSCAT,PBCATOWN'

　　3. Driver Specific Options.

　　3.1 DB2 Common Server, Universal Database (UDB), and DB2/MVS via IBM CAE ODBC.

　　IBM's CAE has the following settings that can be specified in the DB2CLI.INI file or as part of the PowerBuilder database profile. In PowerBuilder 6.0 and later, such settings can be provided in the Driver-Specific Parameter edit control on the Connection tab. In previous versions of PowerBuilder, these options are included as part of the ConnectString DBParm. In general, these settings are handled internally by the ODBC driver; therefore, they cannot be overridden by settings in PowerBuilder connection parameters or PowerBuilder's ODBC INI file. Settings there will, however, still be applied to potentially further restrict the table list.

　　The options below specifically have an effect on the ODBC function SQLTables, which PowerBuilder invokes to get a table list. This has a notable implication on the PowerBuilder repository tables. Since the SQLTables call is used to detect the presence of the repository tables, if those tables do not themselves meet whatever criteria are set by the following options, then it will appear to PowerBuilder that the repository does not exist. Consequently, an attempt will be made to create the repository tables. That attempt will fail because the tables really do exist; they are just not perceptible via the SQLTables function.

　　The documentation that appears below is largely adapted from the IBM CAE on-line help facility. Please consult those files or other CAE documentation for further information on these options.

　　3.1.1 DBNAME (DB2/MVS only).

　　DBNAME=dbname. The value of this keyword maps to the DBNAME column in the DB2 for MVS/ESA system catalog tables. Only table information is restricted; therefore, views, aliases, and synonyms will appear in the table list regardless of the database to which they belong. This keyword can be used in conjunction with SCHEMALIST and TABLETYPE to further limit the number of tables for which information will be returned.

　　3.1.2 SCHEMALIST (formerly OWNERLIST).

　　SCHEMALIST="'schema1', 'schema2', ...". This option provides a more restrictive default in the case of those applications that always give a list of every table in the DBMS. Since one can provide a list of schemas, SCHEMALIST offers more flexibility than PowerBuilder's TableCriteria parameter.

　　As for format, within the DB2CLI.INI file each schema name must be delimited with single quotes, separated by commas, and in upper case. The entire string, which can be no longer that 256 characters, must also be enclosed in double quotes. For DB2 for MVS, CURRENT SQLID can also be included in this list, but without the single quotes, for example:

SCHEMALIST="'USER1',CURRENT SQLID,'USER3'"

　　

3.1.3 TABLETYPE.

　　TABLETYPE="'TABLE'|,'ALIAS'|,'VIEW'|,'SYSTEM TABLE'|,'SYNONYM'". This option has essentially the same function as the last parameter in the PowerBuilder TableCriteria parameter. Note that like SCHEMALIST, this parameter is limited to 256 characters and is similarly formatted.

　　3.1.4 SYSSCHEMA (formerly SYSOWNER).

　　SYSSCHEMA=schema. This keyword indicates an alternative schema (also known as a shadow catalog) to be searched in place of the SYSCAT (or SYSIBM, SYSTEM) schemas when the DB2 CLI and ODBC catalog function calls are issued to obtain system catalog information.

　　To use a shadow catalog with PowerBuilder when connecting via CAE ODBC, use SYSSCHEMA or CLISCHEMA; the SystemOwner parameter does not apply to ODBC data sources.

　　3.1.5 CLISCHEMA (CAE version 5.2 and later).

　　CLISCHEMA=schema. This keyword indicates an alternative schema (also known as a shadow catalog) to be searched in place of the SYSCAT (or SYSIBM, SYSTEM) schemas when the DB2 CLI and ODBC catalog function calls are issued to obtain system catalog information. This option is implemented somewhat differently that the older SYSSCHEMA option; consult your IBM documentation or the IBM DB2 website for more information.

　　To use a shadow catalog with PowerBuilder when connecting via CAE ODBC, use SYSSCHEMA or CLISCHEMA; the SystemOwner parameter does not apply to ODBC data sources.

　 　3.2 MDI Gateway via PowerBuilder Native Interface.

　　N.B. See the notes in Section 1.1 on the support for this native interface.

　　The MDI gateway provides a number of global variables (CSP_xxxx) that control the result sets returned by catalog stored procedures, such as sp_tables. Note that PowerBuilder's MDI Gateway native interface does not directly call sp_tables; therefore, those settings have no effect upon the table list presented within PowerBuilder. PowerBuilder does invoke other catalog stored procedures, however, so one value that does have relevance to PowerBuilder is CSP_CATQUALIFIER which should be set to the same value as the SystemOwner parameter when implementing a shadow catalog (cf. Technical Document 44430).

　　3.3 DirectCONNECT DB2 Access Service via PowerBuilder Native Interface (PowerBuilder 6.5 and later).

　　In versions 6.5 and later, PowerBuilder supports native connectivity to the DirectCONNECT DB2 Access Service. The origins of this service are the MDI Gateway, so many connection options are similar; however, with the DirectConnect native driver, PowerBuilder requests for system metadata are made entirely via catalog stored procedure (CSPs), such as sp_tables which returns a list of table objects. What the sp_tables CSP returns is subject to a number of different Access Service configuration parameters. Those parameters, listed below, can be set at the service level by using the DirectCONNECT Manager or during execution by using EXECUTE IMMEDIATE statements within PowerBuilder.

　　CSPCatalogQualifier - specifies the qualifier of system tables used by the catalog stored procedures; this parameter is required to implement a shadow catalog with DirectCONNECT. This value can also be set via the CSP Catalog Qualifier parameter of the Database Profile dialog.
　　CSPDBName - specifies the name of the database in which tables to be returned by sp_tables reside; this is used in conjunction with CSPQualByDBName to limit the result set from sp_tables to a particular database.
　　CSPExclusions - limits the table list based upon user's privileges on the tables, options include.

none - no objects are excluded from the list (return all tables)
user - objects are excluded based solely on user permissions
nonauth - non-authorized and public objects are excluded from the table list
nonauthpublic - non-authorized objects are excluded but public objects are not

CSPIncludeAlias (yes|no) - include aliases in list of table objects returned
CSPIncludeSynonym (yes|no) - include synonyms in list of table objects returned
CSPIncludeSystem (yes|no) - include system tables in list of table objects returned
CSPIncludeTable (yes|no) - include tables in list of table objects returned
CPSIncludeView (yes|no) - include views in list of table objects returned

　　4. PowerBuilder Development Environment Settings.

　　4.1 Show System Tables.

　　When the table list displays, DBMS system tables as well as the PowerBuilder repository tables are filtered from the list. If the user enables the Show System Tables option, these tables will be included in the list. For PowerBuilder 6.x and earlier, the Show System Tables option is a check box on the Table List dialog; for PowerBuilder 7.0, Show System Tables is a checked menu option available via pop-up menu on the Tables folder within the Database Painter tree view.

　　Note that for ODBC, 'system tables' are defined as such by the PBSystemOwner setting in PowerBuilder's ODBC INI file; see section 2.3 of this document for additional information about that setting.

　　4.2 Refresh Table List Interval.

　　In the Database Painter, Design->Options dialog, there is an entry that allows one to specify the table list refresh interval. In order to improve performance, the table list is cached; the refresh interval indicates how long PowerBuilder will wait before issuing a request for an updated table list (to reflect changes since the last retrieval). Keep in mind that any changes to the system catalog (e.g., tables created and/or dropped) will not appear in the table list until the next refresh is done. The default interval is 1800 seconds (30 minutes).