Picasso Database Query Optimizer Visualizer

©Indian Institute of Science, Bangalore, India

Sybase ASE

(The following setup procedure is known to work with Sybase ASE 15 on Windows.)

1. Install Sybase by running setup.exe and following the wizard.

2. Create a device (or two) for the new database and allocate enough space on the devices. Create a database using the Add Database utility of Sybase Central and allocate these new devices to this database.

3. Open an SQL prompt in one of the following two ways:

1. Open the Interactive SQL utility of Sybase and select the database name from the drop-down list of databases.
   

2. In the command prompt, type isql –S <servername> –U <username> -P <password> and in this prompt, give the command use <dbname> followed by a go command. The servername is the name of the machine where this DB service is running.
   The isql command file is usually found in the Sybase\OCS\bin directory. This prompt requires that there should be no semi-colon after a statement, and instead there should be a go command following each statement.

4. Create the stored procedure hist_values to obtain the information required to create statistics.

5. Create the tables. If the schema to be created is in a file, use one of the following methods depending on which of the above SQL prompts is being used:

1. In the Interactive SQL prompt, go to File → Run Script and select the required file to run.
   

2. In the command prompt, type isql –S <servername> –U <username> -P <password> -i <filename> to run the file. The file should have no semi-colon after a statement, and instead, there should be a go command after each statement.

6. Load the data into the database tables. If the data to be loaded is in files, any of these utilities can be used:

1. Data → Import menu item of the Interactive SQL window. (This utility requires the data files to be named with a ‘.csv’ extension.)

2. The bcp utility. To enable this, in the Sybase Central utility, navigate to the database name in the tree view, right-click on it and select Properties. In the Options tab, check the “select into/bulkcopy/pllsort” item. Then run the following command in the command prompt:
   

bcp <dbname>.<schema>.<tablename> in <filename> -c –t “<delimiter>” –r “<return_character>” -U<user> -P<passwd>


For example, if the file nation.tbl has pipe-delimited data with a pipe at the end of each line, and the database is tpch, schema is dbo, user is sa/sysadmin, and table is NATION, the command looks like this:


bcp tpch.dbo.NATION in nation.tbl -c –t “|” –r “|\n” -Usa -Psysadmin

7. Create the integrity constraints (primary key, foreign key and indexes) on the data. If you are using a file for this, run it similar to step 5 above.

8. Create statistical summaries for all table columns that may be used as Picasso predicates in the query templates. These summaries can be created using the following command in the SQL prompt:
   

update statistics <db_name>.<schema_name>.<table_name> (<column_name>)



To improve the scope for optimization, you could optionally create statistics for all columns of each table. This can be done using the command:

update all statistics <table_name>


Note: The diagrams on the Picasso website have been generated with statistical summaries created on all table columns.

Notes:

1. If the transaction logs become full, they can be cleared using the command:

dump tran <dbname> with no_log

2. If you get an error like “There is not enough memory in the procedure cache”, then restart the Sybase service and increase the procedure cache size by giving the command

sp_configure ‘procedure cache’, <new_size>

The original procedure cache size can be viewed using the command:

sp_configure ‘procedure cache’

Documentation Home