SQL/MX Release 2.0 Best Practices

Contents 4
Data Locality Objectives...................................................................................................................................21
Additional Information.......................................................................................................................................22
Renaming a Table.........................................................................................................................................22
Limits ...............................................................................................................................................................22
Practical Limitations......................................................................................................................................23
Techniques for Creating Large Tables..............................................................................................................23
Using a Single CREATE TABLE Statement.....................................................................................................23
Additional Considerations Tables................................................................................................................23
Using SQLMXBUFFER to Improve Database Performance..............................................................................24
SQL/MX Session Data Space and Data Cache Guidelines.....................................................................24
Setting the SQLMXBUFFER Attribute by Using SCF......................................................................................25
Optimizing SQL/MX Memory Management...............................................................................................25
Compiling Large Queries for Tables..................................................................................................................26
Using Multiple SQL/MX Database Catalogs and Schemas...........................................................................26
Schema Ownership............................................................................................................................................26
Table Creation in Third-party Tools, Where LOCATION is an Unknown.........................................................26
MDAM
MDAM Query Technology Design Considerations.........................................................................................28
Forcing MDAM Query Technology..............................................................................................................28
Possible Implementations
Join Efficiency.....................................................................................................................................................29
Insert Efficiency...................................................................................................................................................29
Data Clustering...................................................................................................................................................29
Fact Table Partitioning Techniques...................................................................................................................30
Sequential Range Partitioning.....................................................................................................................31
Hash Partitioning............................................................................................................................................31
Other Benefits of Partitioning.......................................................................................................................33
Fact Table Partitioning Summary.................................................................................................................33
Testing the Results for SQL/MX Tables or Creating Artificial Statistics............................................................33
Dimension Table Partitioning Techniques........................................................................................................34
Managing Cache Memory Size........................................................................................................................34
Maximizing Disk Process Prefetch Capabilities................................................................................................35
Column Alignment........................................................................................................................................35
Managing and Monitoring
Catalog and Schema Placement and Maintenance...................................................................................36
Data-loading Techniques and Considerations
Create the Table for the Final Destination of the SQL/MX Table..................................................................37
Use FTP to Send the File to the Desired Location............................................................................................37
Import the File to the Table................................................................................................................................37
Select the count(*) Table for Read Uncommitted Access............................................................................37
Getting Guardian Names from the System Catalog for FUP RELOAD....................................................38
Block Slack Space..............................................................................................................................................38
Reorganizing Tables ...........................................................................................................................................39
Updating Statistics
Information to Aid “Implanting” SQL Statistics.................................................................................................42
Publish and Subscribe
Implementing an ODBC or JDBC Architecture
Performance Tuning
Improving Performance.....................................................................................................................................45
Addressing Problems with Numerous ESP Processes.................................................................................45
Minimize Table Partitions...............................................................................................................................45
Recommended Maximum Number of Partitions.......................................................................................45
Addressing Problems with Skewed Data Distributions...............................................................................45
Addressing Problems in Large Tables..........................................................................................................46
Addressing Problems in SQL Catalog Performance..................................................................................47
Analysis Methodology..................................................................................................................................48
CONTROL QUERY SHAPE...............................................................................................................................48