SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 26
• Parallelism among a large number of ESPs
• Running plans that use sort and grouping operators
• The optimization of complex plans
The heavy consumption of addressable memory space by SQL/MX processes can lead to insufficient
swap-file space. Therefore, you should provide more kernel-managed swapped space on each CPU by
increasing the size of existing swap files or by adding new swap files.
You should periodically monitor your kernel-managed swap files while SQL/MX programs are being
compiled and run to ensure that adequate swap-file space is available. If the required swap-file space is
not available, an SQL/MX compilation might fail, or a running statement might return an “insufficient
memory” error.
Use NSKCOM to do any or all these tasks:
• Monitor kernel-managed swap-file use
• Monitor swap-file use by process
• Change your KMSF configuration
For more information, see the Kernel-Managed Swap Facility (KMSF) Manual.
To identify and avoid possible memory contention between the application, the master executor, and
other system components in process space such as QIO, see the discussion on configuring the QIO
subsystem in the QIO Configuration and Management Manual.
Compiling Large Queries for Tables
The compiler can run out of memory if the plan search space is large. One workaround is to reduce
search space by forcing a change in the join order, join strategy, and so on. To limit the frequency with
which the optimizer searches for available plans, set the CONTROL QUERY DEFAULT OPTIMIZATION LEVEL.
The lower the setting (0 is the lowest and 5 is the highest), the fewer plans are built and evaluated. For
more information, see the SQL/MX Query Guide.
Using Multiple SQL/MX Database Catalogs and Schemas
You can create multiple SQL/MX catalogs and schemas for databases that are distinct from one another,
such as those used for different applications.
Try to limit the number of separate schemas you create because too many separate schemas make
managing and programming linking tables across multiple environments difficult.
Schema Ownership
ANSI schema-ownership specifications prohibit tables from being created in a schema by a user ID other
than the owner of the schema. This requirement creates problems for ODBC tools, which depend on
temporary table creation for intermediated query results. Here are two workarounds:
• Give users a SAFEGUARD alias that maps to the owner's ID. This arrangement allows for table
creation by other users using their own alias, but the tables are actually created using the owner's ID.
This workaround may not be the best solution depending on the security requirements of the
customer.
• Use the undocumented PUBLIC_ACCESS_SCHEMA feature. The PUBLIC_ACCESS_SCHEMA must be
created by the super ID (255,255) in the catalog that also contains the owner's schema. Any user ID
can create tables in the PUBLIC_ACCESS_SCHEMA. Any references to those newly created tables
must contain the schema name (three-part ANSI name) as well.
Table Creation in Third-party Tools, Where LOCATION is an
Unknown
The LOCATION clause of the CREATE TABLE statement is not ANSI standard, so third-party ANSI-standard
ODBC tools cannot specify the Guardian location of the table in the CREATE statement. If the location