1.0
Table Of Contents
- Contents
- About the SQLFire User's Guide
- Supported Configurations and System Requirements
- Getting Started with vFabric SQLFire
- Managing Your Data in vFabric SQLFire
- Designing vFabric SQLFire Databases
- Using Server Groups to Manage Data
- Partitioning Tables
- Replicating Tables
- Estimating Memory Requirements
- Using Disk Stores to Persist Data
- Exporting and Importing Data with vFabric SQLFire
- Using Table Functions to Import Data as a SQLFire Tables
- Developing Applications with SQLFire
- Starting SQLFire Servers with the FabricServer Interface
- Developing Java Clients and Peers
- Configuring SQLFire as a JDBC Datasource
- Storing and Loading JAR Files in SQLFire
- Developing ADO.NET Client Applications
- About the ADO.NET Driver
- ADO.NET Driver Classes
- Installing and Using the ADO.NET driver
- Connecting to SQLFire with the ADO.NET Driver
- Managing Connections
- Executing SQL Commands
- Working with Result Sets
- Storing a Table
- Storing Multiple Tables
- Specifying Command Parameters with SQLFParameter
- Updating Row Data
- Adding Rows to a Table
- Managing SQLFire Transactions
- Performing Batch Updates
- Generic Coding with the SQLFire ADO.NET Driver
- Using SQLFire.NET Designer
- Understanding the Data Consistency Model
- Using Distributed Transactions in Your Applications
- Using Data-Aware Stored Procedures
- Using the Procedure Provider API
- Using the Custom Result Processor API
- Programming User-Defined Types
- Using Result Sets and Cursors
- Caching Data with vFabric SQLFire
- Deploying vFabric SQLFire
- SQLFire Deployment Models
- Steps to Plan and Configure a Deployment
- Configuring Discovery Mechanisms
- Starting and Configuring SQLFire Servers
- Configuring Multi-site (WAN) Deployments
- Configuring Authentication and Authorization
- Configuring User Authentication
- User Names in Authentication and Authorization
- Configuring User Authorization
- Configuring Network Encryption and Authentication with SSL/TLS
- Managing and Monitoring vFabric SQLFire
- Configuring and Using SQLFire Log Files
- Querying SQLFire System Tables and Indexes
- Evaluating Query Execution Plans and Query Statistics
- Overriding Optimizer Choices
- Evaluating System and Application Performance
- Using Java Management Extensions (JMX)
- Best Practices for Tuning Performance
- Detecting and Handling Network Segmentation ("Split Brain")
- vFabric SQLFire Reference
- Configuration Properties
- JDBC API
- Mapping java.sql.Types to SQL Types
- java.sql.BatchUpdateException Class
- java.sql.Connection Interface
- java.sql.DatabaseMetaData Interface
- java.sql.Driver Interface
- java.sql.DriverManager.getConnection Method
- java.sql.PreparedStatement Interface
- java.sql.ResultSet Interface
- java.sql.SavePoint Class
- java.sql.SQLException Class
- java.sql.Statement Class
- javax.sql.XADataSource
- sqlf Launcher Commands
- sqlf backup
- sqlf compact-all-disk-stores
- sqlf compact-disk-store
- sqlf encrypt-password
- sqlf install-jar
- sqlf list-missing-disk-stores
- sqlf locator
- sqlf Logging Support
- sqlf merge-logs
- sqlf remove-jar
- sqlf replace-jar
- sqlf revoke-missing-disk-store
- sqlf server
- sqlf shut-down-all
- sqlf stats
- sqlf validate-disk-store
- sqlf version
- sqlf write-data-dtd-to-file
- sqlf write-data-to-db
- sqlf write-data-to-xml
- sqlf write-schema-to-db
- sqlf write-schema-to-sql
- sqlf write-schema-to-xml
- sqlf Interactive Commands
- absolute
- after last
- async
- autocommit
- before first
- close
- commit
- connect
- connect client
- connect peer
- describe
- disconnect
- driver
- elapsedtime
- execute
- exit
- first
- get scroll insensitive cursor
- GetCurrentRowNumber
- help
- last
- LocalizedDisplay
- MaximumDisplayWidth
- next
- prepare
- previous
- protocol
- relative
- remove
- rollback
- run
- set connection
- show
- wait for
- SQLFire API
- SQL Language Reference
- Keywords and Identifiers
- SQL Statements
- SQL Clauses
- SQL Expressions
- JOIN Operations
- Built-in Functions
- Standard Built-in Functions
- Aggregates (set functions)
- ABS or ABSVAL function
- ACOS function
- ASIN function
- ATAN function
- ATAN2 function
- AVG function
- BIGINT function
- CASE expressions
- CAST function
- CEIL or CEILING function
- CHAR function
- COALESCE function
- Concatenation operator
- COS function
- COSH function
- COT function
- COUNT function
- COUNT(*) function
- CURRENT DATE function
- CURRENT_DATE function
- CURRENT ISOLATION function
- CURRENT_ROLE function
- CURRENT SCHEMA function
- CURRENT TIME function
- CURRENT_TIME function
- CURRENT TIMESTAMP function
- CURRENT_TIMESTAMP function
- CURRENT_USER function
- DATE function
- DAY function
- DEGREES function
- DOUBLE function
- EXP function
- FLOOR function
- HOUR function
- INTEGER function
- LCASE or LOWER function
- LENGTH function
- LN or LOG function
- LOG10 function
- LOCATE function
- LTRIM function
- MAX function
- MIN function
- MINUTE function
- MOD function
- MONTH function
- NULLIF expressions
- PI function
- RADIANS function
- RANDOM function
- RAND function
- RTRIM function
- SECOND function
- SESSION_USER function
- SIGN function
- SIN function
- SINH function
- SMALLINT function
- SQRT function
- SUBSTR function
- SUM function
- TAN function
- TANH function
- TIME function
- TIMESTAMP function
- TRIM function
- UCASE or UPPER function
- USER function
- VARCHAR function
- XMLEXISTS operator
- XMLPARSE operator
- XMLQUERY operator
- XMLSERIALIZE operator
- YEAR function
- SQLFire Built-in Functions
- Standard Built-in Functions
- Built-in System Procedures
- Standard Built-in Procedures
- SYSCS_UTIL.EMPTY_STATEMENT_CACHE
- SYSCS_UTIL.EXPORT_QUERY
- SYSCS_UTIL.EXPORT_TABLE
- SYSCS_UTIL.IMPORT_DATA
- SYSCS_UTIL.IMPORT_DATA_EX
- SYSCS_UTIL.IMPORT_DATA_LOBS_FROM_EXTFILE system procedure
- SYSCS_UTIL.IMPORT_TABLE
- SYSCS_UTIL.IMPORT_TABLE_EX
- SYSCS_UTIL.IMPORT_TABLE_LOBS_FROM_EXTFILE
- SYSCS_UTIL.SET_EXPLAIN_CONNECTION
- SYSCS_UTIL.SET_STATISTICS_TIMING
- JAR Installation Procedures
- Callback Configuration Procedures
- Heap Eviction Configuration Procedures
- WAN Configuration Procedures
- Standard Built-in Procedures
- Data Types
- SQL Standards Conformance
- System Tables
- ASYNCEVENTLISTENERS table
- GATEWAYRECEIVERS table
- GATEWAYSENDERS table
- MEMBERS system table
- MEMORYANALYTICS system table
- STATEMENTPLANS system table
- SYSALIASES system table
- SYSCHECKS system table
- SYSCOLPERMS system table
- SYSCOLUMNS system table
- SYSCONGLOMERATES system table
- SYSCONSTRAINTS system table
- SYSDEPENDS system table
- SYSDISKSTORES system table
- SYSFILES system table
- SYSFOREIGNKEYS system table
- SYSKEYS system table
- SYSROLES system table
- SYSROUTINEPERMS system table
- SYSSCHEMAS system table
- SYSSTATEMENTS system table
- SYSSTATISTICS system table
- SYSTABLEPERMS system table
- SYSTABLES system table
- SYSTRIGGERS system table
- SYSVIEWS system table
- Exception Messages and SQL States
- ADO.NET Driver Reference
- SQLFire Data Types in ADO.NET
- VMware.Data.SQLFire.BatchUpdateException
- VMWare.Data.SQLFire.SQLFClientConnection
- VMware.Data.SQLFire.SQLFCommand
- VMware.Data.SQLFire.SQLFCommandBuilder
- VMware.Data.SQLFire.SQLFType
- VMware.Data.SQLFire.SQLFDataAdapter
- VMware.Data.SQLFire.SQLFDataReader
- VMware.Data.SQLFire.SQLFException
- VMware.Data.SQLFire.SQLFParameter
- VMware.Data.SQLFire.SQLFParameterCollection
- VMware.Data.SQLFire.SQLFTransaction
- vFabric SQLFire Limitations
- Troubleshooting Common Problems
- vFabric SQLFire Glossary
- Index
2. Query the sys.statementplans table to obtain a list of all plans that were captured. For example:
select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID |STMT_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
1ec14167-012f-b6ad-8c1e-00007dce8640|select user0_.username as username0_,
user0_.name as name0_, user0_.password as password0_ from Customer user0_
where user0_.us&
d884816c-012f-b6ad-8c1e-00007dce8640|update Booking set beds=?,
checkinDate=?, checkoutDate=?, creditCard=?, creditCardExpiryMonth=?,
creditCardExpiryYear=?, credit&
15b58164-012f-b6ad-8c1e-00007dce8640|select hotel0_.id as id2_0_,
hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country
as country2_0_, hotel0_.&
dd5cc163-012f-b6ad-8c1e-00007dce8640|select hotel0_.id as id2_0_,
hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country
as country2_0_, hotel0_.&
87cf416a-012f-b6ad-8c1e-00007dce8640|insert into Booking (beds,
checkinDate, checkoutDate, creditCardExpiryMonth, creditCardExpiryYear,
hotel_id, smoking, user_user&
00000000-ffff-ffff-ffff-000000000357|select booking0_.id as id1_,
booking0_.beds as beds1_, booking0_.checkinDate as checkinD3_1_,
booking0_.checkoutDate as checkou&
eaa841c2-012f-b6ad-5103-00003dcdc000|select hotel0_.id as id2_0_,
hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country
as country2_0_, hotel0_.&
e23801c1-012f-b6ad-5103-00003dcdc000|select user0_.username as
username0_0_, user0_.name as name0_0_, user0_.password as password0_0_
from Customer user0_ where use&
c15841bf-012f-b6ad-5103-00003dcdc000|select hotel0_.id as id2_0_,
hotel0_.address as address2_0_, hotel0_.city as city2_0_, hotel0_.country
as country2_0_, hotel0_.&
00000000-ffff-ffff-ffff-0005000002e2|select hotel0_.id as id2_,
hotel0_.address as address2_, hotel0_.city as city2_, hotel0_.country as
country2_, hotel0_.name as &
10 rows selected
3. Use the statement text to determine the STMT_ID of the query plan you want to view. Then display the plan
contents with the explain stmt_id command. For example:
explain '00000000-ffff-ffff-ffff-0005000002e2';
member vmc-ssrc-rh156(17697)<v1>:31918/53818 begin_execution
end_execution
QUERY-RECEIVE execute_time 2208000 member_node
vmc-ssrc-rh154(21955)<v4>:1227/34442
RESULT-SEND execute_time 1980000 member_node
vmc-ssrc-rh154(21955)<v4>:1227/34442
RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 696000
member vmc-ssrc-rh156(17696)<v2>:34240/56045 begin_execution
end_execution
QUERY-RECEIVE execute_time 2570000 member_node
vmc-ssrc-rh154(21955)<v4>:1227/34442
RESULT-SEND execute_time 1634000 member_node
vmc-ssrc-rh154(21955)<v4>:1227/34442
RESULT-HOLDER returned_rows 5 no_opens 1 execute_time 688000
member vmc-ssrc-rh154(21955)<v4>:1227/34442 begin_execution
end_execution
QUERY-SCATTER execute_time 141898000 member_node
QUERY-SEND execute_time 1446000 member_node
vmc-ssrc-rh156(17696)<v2>:34240/56045
QUERY-SEND execute_time 979000 member_node
261
Evaluating Query Execution Plans and Query Statistics