Profiling supports the following DBMS platforms:
- IBM DB/2 for Windows, UNIX, and Linux
- Microsoft SQL Server
- Oracle
- Sybase
The following describe how to set up a platform to utilize Optimizer on supported database platforms:
- Configuring IBM DB/2 for Windows, UNIX, and Linux
- Configuring Microsoft SQL Server
- Configuring Oracle
- Configuring Sybase
Configuring IBM DB/2 for Windows, Unix, and Linux
The connected profiling user should be a member of the DB2 SYSMON group.
By default, DB2 Monitor flags are set to OFF. As a result, when attempting to launch a Profile job on a DB2 data source, users may encounter the following message: "One or more errors have occurred that prevent session profiling against this data source." Examine the details below and consult your data source administrator and/or the data source documentation to resolve the problem(s)."
You can resolve this error using one of two methods:
- Enabling DB2 Monitor Flags via IDERA DBArtisan
- Command Line Option
To resolve the error through DBArtisan
- Ensure the following DB2 Monitor Flags are turned on in DB2:
- dft_mon_uow
- dft_mon_stmt
- dft_mon_lock
- dft_mon_bufpool
- dft_mon_sort
- dft_mon_table
- dft_mon_timestamp
- Restart the DB2 data source to enable the changes, then launch SQL Query Tuner and begin profiling.
To resolve the error through the command line
This solution must be performed from DB2 CLP, on the DB2 server. If you attempt to perform these tasks through a client, an error message results.
- Navigate to Start > Programs > IBM DB2/COMMAND LINE TOOLS > COMMAND LINE PROCESSOR.
- Turn the monitor switches on using the following commands:
db2 update dbm cfg using dft_mon_lock on dft_mon_bufpool on dft_mon_sort on dft_mon_stmt on dft_mon_table on timestamp on dft_mon_uow on db2stop db2start - Ensure that the switches are turned on by connecting to the server with the following command:
Db2 connect to database username password password
The following screenshot provides an example of the input and output from the server:
Configuring Microsoft SQL Server
Perform the following tasks to ensure that SQL Server is compatible with Optimizer:
- The current user must meet one of the following requirements:
- Be a member of sysadmin, or have the VIEW SERVER STATE permission enabled.
- Be a member of sysadmin, or have the SELECT permission enabled.
Configuring Oracle
Oracle users need access to V$ views. In order to configure Oracle to provide users with these privileges:
- If you are setting up Oracle 10 or later, ensure you are logged in as sys or system with the sysdba role, or the SELECT_CATALOG_ROLE has been granted to user_name.
- If you are setting up an earlier version of Oracle, ensure you are logged in as sys or system with the sysdba role.
Configuring Sybase
Perform the following tasks to ensure that Sybase is compatible with DB Optimizer:
- Ensure the following system configuration properties are activated:
- Enabling Monitoring (sp_configure "enable monitoring", 1)
- Wait Event Timing (sp_configure "wait event timing", 1)
- SQL Batch Capture (sp_configure "SQL batch capture", 1)
- Max SQL Text Monitored (sp_configure "max SQL text monitored", 2048) 2048 is the suggested value, must be greater than 0. This defines the maximum size a SQL statement that can be captured.
The following options are specific to Sybase 15.0.2 and higher.
- SQL Text Pipe Active (sp_configure "sqltext pipe active", 1)
- SQL Text Pipe Max Messages (sp_configure "sql text pipe max messages", 512) 512 is the suggested value, must be greater than 0. This defines the maximum number of SQL statements that Sybase tracks at any given time. You may want to increase this number if you observe a lot of UNKNOWN statements in the Profile Editor.
- Statement Statistics Active (sp_configure "statement statistics active", 1)
- Per Object Statistics Active (sp_configure "per object statistics active", 1)
Additionally, perform the following tasks, as necessary:
- If a user does not have mon_role enabled, the user will not be able to access Adaptive Server's monitoring tables.
- If the monProcess table is missing, the user will not be able to view currently connected sessions.
- If the sysprocesses table is missing, the user will not be able to view information about Adaptive Server processes.
- If the monWaitEventInfo table is missing, the user will not be able to view information about wait events.
- If the monProcessSQLText table is missing, the user will not be able to view currently executing SQL statements.
These packages should only be installed by the DBA.
Profiling enables you to create a set of launch configurations to store the basic properties for each profiling session that you run on a regular basis. A launch configuration enables you to start profiling sessions from a single menu command, rather than re-define configuration parameters each time you want to run one.