PICASSO Installation

Picasso Database Query Optimizer Visualizer

©Indian Institute of Science, Bangalore, India

INSTALLATION INSTRUCTIONS

Overview

There are three processes involved in a Picasso setup, as shown in the figure:

(a) the Picasso Client, through which users enter query templates and visualize the associated diagrams;

(b) the Picasso Server, which converts query templates into the equivalent set of query instances, submits these queries to the database engine, and gathers the associated execution plans; and

$Description: Description: Description: Description: Description: D:\bruhathi\bruhathi\PicassoSite-Modif\PicassoSite\Picasso2Doc\Installation\architecture.jpg$

These three processes can all execute on the same machine or on different machines. Further, multiple Picasso clients can connect to a single Picasso server, which in turn can connect to multiple database engines. The client and server machines should support Java compilation and execution, while the client machine should additionally support 3D visualization. A few third-party libraries for visualization and database connection are required for Picasso to function – the details are given in License Information (for convenience, this support software is included with the Picasso code-base in the full version).

Picasso is completely written in Java and should, in principle, operate in a platform-independent manner. It has been successfully tested on the following system and database environments:

System platforms:

(a) Windows 32-bit: Windows 7 Professional, Intel® Core™2 Duo 2.10GHz, 4 GB RAM, Mobile Intel® 965 Express Chipset Family

(b) Windows 64-bit: Windows Vista Business 64-bit, Sun Ultra 24 Intel Core2 QuadCore 3GHz, 8 GB Ram, NVidia Quadro FX 570 graphics card

(d)Unix 64-bit: Ubuntu Linux (2.6.24 kernel), Sun Ultra 24 Intel Core2 QuadCore 3GHz, 8 GB Ram, NVidia Quadro FX 570 graphics card

Database engines: DB2 8/9, Oracle 9i/10g/11g, SQL Server 2000/2005/2008, Sybase ASE 15, PostgreSQL 8, MySQL 5.1/5.4.1/5.5.9
(For porting to other database engines, please refer to the porting guide.)

Sample Picasso diagrams obtained with the above database engines on the Windows platform are available from the Picasso home page.

Installation Steps

I Setup the Database Engine

1. Install a database engine. Click on the following links for setup information (on Windows) for specific database engines: DB2 Oracle SQL Server Sybase ASE PostgreSQL MySql. The installation on Unix is on similar lines – please refer the vendor product literature for details.

2. Populate the database with data.

Note: Picasso can be used with generic relational database schemas and SQL query templates. The illustrative examples in the Picasso documentation are with respect to the TPC-H benchmark, and the procedure for setting up this benchmark is given in TPC-H data generation and loading.

3. Then:

a. For DB2 and Oracle, create the explain plan tables (these tables store the query execution plans generated by these optimizers).

b. For all engines, create statistical summaries for all relational columns that may be used as Picasso predicates in the query templates. Follow these links for the creation procedure: DB2 Oracle SQL Server Sybase ASE PostgreSQL MySql.

II Download the Picasso Software

1. From Picasso Download, download the Picasso code (version 2.1) - either the full version, which includes all essential graphics and database libraries, or the no-lib version, which has only the Picasso source code. Extract its contents on the Server and Client machines. A directory called Picasso2.1 in which the entire code-base is contained will be created. All paths mentioned in this document and the supporting documentation are with reference to this directory.

2. Read through the documentation given in the PicassoDoc directory.

III Setup the Picasso Server

On the Server machine:

1. Install a Java compiler and execution engine [Sun’s JDK 6.0 has been successfully used in our testbed].

2. If you downloaded the full version of Picasso, skip to Step 3. On the other hand, if you downloaded the no-lib version, you need to manually add, in the Libraries directory, the necessary database connection libraries for each engine that you wish to have supported in Picasso.

Note: If you use different versions of the database connection libraries, you will need to suitably edit the relevant bat and sh files in the PicassoRun\Windows and PicassoRun/Unix directories, respectively.

3. Activate the Picasso interface for the desired database engines.

For Windows, execute activatedb.bat in the PicassoRun\Windows directory.
For Unix, execute activatedb.sh in the PicassoRun/Unix directory.

4. Compile the Picasso Server.

For Windows, execute compileServer.bat in the PicassoRun\Windows directory.
For Unix, execute compileServer.sh in the PicassoRun/Unix directory.

IV Setup the Picasso Client

On the Client machine:

1. Install a Java compiler and execution engine [Sun JDK 6.0 has been successfully used in our testbed].

2. Install Java3D [Sun Java3D 1.4.0_01 has been successfully used in our testbed]. We recommend the use of the OpenGL version of Java3D wherever possible.

3. If you downloaded the full version of Picasso, skip to Step 4. On the other hand, if you downloaded the no-lib version, then you need to manually add the necessary graphics libraries in the Libraries directory.

Note: If you use different versions of the graphics libraries, you will need to suitably edit the relevant bat and sh files in the PicassoRun\Windows and PicassoRun/Unix directories, respectively.

4. Compile the Picasso Client.

For Windows, execute compileClient.bat in the PicassoRun\Windows directory.
For Unix, execute compileClient.sh in the PicassoRun/Unix directory.

The installation is now complete. To learn how to use Picasso, proceed to the user guide.

Documentation Home