The following topics introduce the functions that comprise the PSQL Distributed Tuning Objects.

You can also go directly to more detailed information on using DTO in PSQL:

Distributed Tuning Objects (DTO throughout the rest of this document) are the COM wrapper for the PSQL Distributed Tuning Interface (DTI throughout the rest of this document). DTO is a collection of objects encapsulating DTI. DTO also contains some features that go beyond the capabilities of DTI such as the ability to start and stop the database engines.

DTO enables developers to develop a range of useful, customized server administration tools and interfaces quickly and easily. The power and flexibility of DTO can be applied to the full range of database management and database definition tasks such as production, performance tuning, and metadata administration.

DTO is implemented as a dual interface, in-process server. The developer can use any OLE Automation controller, or create a COM client using many programming languages such as:

For the purpose of this manual, DTO classes are arranged in the following functional categories:

In order to be able to configure and monitor the behavior of PSQL engine, users will have to connect to the engine first. This category provides functionality necessary to connect and disconnect to PSQL database engines.

The DtoSession object manages the connection to a database engine.

This category provides functionality to monitor PSQL engines and clients and provides diagnostic information.

The DtoMonitor object and its descendents provide server monitoring and diagnostic information. You can also obtain engine information directly from DtoSession with the DtoEngineInformation object.

This category allows the user to configure the PSQL engines and clients. The DtoCategories collection and its descendents provide this functionality.

You can also add and remove product licenses using the DtoLicenseMgr object.

Functionality grouped in this category allows users to create new databases, new data dictionaries and also define and drop tables, columns, and indexes.

The DtoDictionary class and its descendents provide catalog functionality.

The DTO object tree contains three main branches, logically grouping objects for Configuration, Monitor and Catalog.

The following table shows usage information regarding the two versions of DTO:

A new DLL was released with PSQL V8 SDK that added objects and new properties to existing objects. In order to maintain backward compatibility, a new DLL was created instead of adding this to the previous DTO.DLL. Both are installed and registered with PSQL SDK and you can use either one to develop DTO applications. Using the previous DTO.DLL may be important to you if you cannot recompile your application to use the new DLL. However, if you continue to use the previous DTO.DLL, you cannot use the new objects or some new properties that were added to existing objects. DTO2.DLL supports 32-bit development environments, including the .NET framework.

The PSQL v11 SP1 SDK added 64-bit support for 64-bit environments, including the .NET framework.

In order to utilize DTO for 64-bit applications, you will need to install the 64-bit server or client with your 64-bit application. Installing the 64-bit server or client installs the 64-bit version (W64DTO2.DLL), whereas installing the 32-bit server or client installs the 32-bit version (DTO2.DLL or DTO.DLL), as described in the previous table.

To better understand how applications and DLLs interact, we will examine the following scenarios.

Assuming we have the following three DLLs:

And the following application executables:

The following tables illustrate the results when you try and run the application executables and DLLs together, on 32-bit and 64-bit machines.

This section describes how to set up DTO for use in Visual Basic, Active Server Pages (ASP), and Delphi.

Since DTO is a library of dual interface COM objects, there are two ways of working with these objects in Visual Basic. If you are using Active Server Pages, you must use the second method. The first and preferred method is to add the type library to the project. This method allows VB to do type checking and provides the developer with useful drop down options for object creation and function parameters (Intellisense).

The other method is to use the CreateObject function. This creates objects at run time and therefore lacks type checking and the Intellisense feature.

To add the Distributed Tuning Library to a Visual Basic project:

VB is now aware of all the objects included in DTO. All the objects are now browsable. To view the available objects:

You need to use this method when instantiating an object with ASP. The CreateObject syntax looks like this:

Most of the DTO objects can later be obtained from the session object.

There is no special initialization required to use DTO with ASP. However, note the following:

ASP by default does not save state information between calls. You will need to use the Microsoft IIS built-in Session object to preserve object references and variable state.

For example, to initialize a DtoSession object with DTO Version 2:

There are two ways to utilize COM objects in Delphi. As with Visual Basic, the first and preferred set-up method is to import the type library into the Delphi project.

The other method allows COM interfaces to be called directly by using CreateOleObject function. This function will instantiate a single instance of an Automation object.

In order to import the type library and generate the necessary Pascal declarations:

The Distributed Tuning Objects Reference is divided into three chapters by functional grouping. The objects in each chapter are listed in this section.

The DtoSession object is the core of DTO. It is through the DtoSession object that an application connects to PSQL servers. A DTO application creates a DtoSession object and uses the Connect method when a session is required on a specific database server.

Object DtoCategory and collection DtoCategories group database engine settings and allow the user further access to the DtoSetting objects.

Object DtoSetting and collection DtoSettings expose the specific settings for the database engines, the communication managers, and the local requester components and allow the user to change these settings. Each category typically exposes a collection of settings.

Object DtoSelectionItem and collection DtoSelectionItems contain the full range of items in a setting where selection is allowed. DtoSetting.AllPossibleSelections returns a collection of all possible values for a given setting.

Object DtoServices allows the user to start and stop PSQL database services, and to query the current status of a service.

Object DtoLicenseMgr (DTO2) allows for adding and removing licenses, as well as viewing product information .

Object DtoMonitor allows the user to retrieve real-time status information of the database engine and other related services.

Also exposed in DtoMonitor object is information about resource usage such as current, peak, and maximum settings for file handles, open files, and licenses. A peak value is the maximum value since the last engine restart.

Object DtoOpenFile and collection DtoOpenFiles contain information about active files. This allows user to monitor file access by determining how many files are open, what users have them open, and other related information.

Object DtoFileHandle and collection DtoFileHandles expose user name/agent ID, connection, task, site, network address, open mode, record lock type, wait state, and transaction state.

Object DtoMkdeClient and collection DtoMkdeClients expose the information about active clients. For a particular client, you can query to see if there is an active session of that client and if so, get data about that session and optionally terminate the client.

Object DtoMkdeClientHandle and collection DtoMkdeClientHandles expose handle information, including the name and associated information for each file.

Object DtoMkdeVersion (DTO2) exposes the major and minor version, build number, and target operating system of the PSQL engine.

Object DtoEngineInformation (DTO2) exposes the major and minor version, DTI API version, and other server and client information.

Object DtoSqlClient and collection DtoSqlClients expose the information about active SQL users such as count and list of active SQL users, and detail information about each client.

Object DtoCommStat exposes the communication statistics. You can query the current, peak, and maximum values where appropriate.

Object DtoProtocolStat and collection DtoProtocolStats expose information regarding each network protocol running on a server.

Object DtoDatabase and collection DtoDatabases are responsible for managing the database catalog information, such as database name, database flags, security, and table definitions.

Object DtoDSN and collection DtoDSNs represent the PSQL DSNs on your server. They can be used to create new DSNs and to manage existing PSQL ODBC DSNs.

Object DtoDictionary is the root object for all of the operations affecting dictionary files. Use this object to open a dictionary, create a dictionary, get table information add a table or drop a table.

The preferred method of accessing the Tables collection is using the DtoDatabase object.

Object DtoTable and collection DtoTables are responsible for managing the table information, such as name, columns, and indexes.

Object DtoColumn and collection DtoColumns are responsible for managing the information about the columns.

Object DtoIndex and collection DtoIndexes expose the indexes defined for a table.

Object DtoSegment and collection DtoSegments contain information on the segments of a given index for a table.

Collections are objects that contain other objects.

Use the Set keyword to set a variable to the collection object.

When using ASP, you normally need to instantiate only two objects directly: DtoSession and DtoDictionary. Instantiating an object with Active Server Pages uses the Create Object syntax on the built-in Server object of IIS.

However, if you create new objects, such as databases, DSNs, tables, columns, indexes, or segments, you use this same syntax. The progid for each object follows the convention shown in the preceding example.

For other collections, you can use the Set syntax documented previously for Visual Basic.

There are two ways to loop through collections in Visual Basic: using a For loop and using a counter.

The following shows the Visual Basic syntax to loop through a collection using a For/Next statement.

The following shows the Visual Basic syntax to loop through a collection using a counter.

Here is sample ASP code that displays a list of categories for PSQL configuration:

Use the Count property to determine the number of objects in the collection.

Use the Count property to determine the number of objects in the collection.

Use the Item property to obtain a specific member object of a collection.

Elements within collections can be accessed using their ordinals. Some collections support accessing elements using the element’s unique property, such as name. All ordinals are 1-based.

Since Item is a default property of the collection object, the following two statements are identical:

The PSQL SDK includes a complete DTO sample written in Visual Basic. This sample is included in your SDK installation of samples and header files at the following location: file_path\PSQL\SDK\SAMPLES\DTI\VBASIC.

The SDK components, code snippets, and samples are available on the Actian PSQL website.

For default locations of PSQL files, see Where are the PSQL files installed? in Getting Started With PSQL.

Also look at the Actian PSQL website for other developer information and resources pertaining to the DTO access method.