Notes For Specific Database Managers
======================================


1 Installation Porting Issues
=============================


Postgresql
----------

This database engine was used as one of the prototypes and as such no changes
are required to run the tests.


Mysql
-----

This database engine was used as one of the prototypes and as such no changes
are required to run the tests. If you are using 4.0.x you may need to allow
load from local files or edit the load script and remove the keyword 'LOCAL'.

It is allowable to edit the schema script to specify different table types
(e.g. InnoDB ).

 
DB2
---

This database engine was used as one of the prototypes and as such no changes
are required to run the tests.

It is allowable to edit the schema scripts to specify tablespaces etc.


Oracle
------

Oracle does not like an alias with 'AS' keyword in a query from clause,
this can be fixed by changing "FROM dim0 AS d0" type text to "FROM dim0 d0".
I am a little surprised that this is necessary.

As in the DB2 case its ok to edit the schema scripts to include tablespaces,
storage etc.

Note that the load generator produces a SQL Loader control file for each
table.
 

Sapdb
----- 

The command line interpreter does not handle the 'usual' style of SQL scripts
i.e : commands terminated with ';' with linefeeds in commands.
In addition it requires keywords to tell the interpreter whether a command is
an SQL command or utility etc.

There are two ways to resolve this - either reformat the schema, index and 
query files, or dynamically filter and reformat using a perl script :

$ cat schema.sql|./sql2sap.pl |dbmcli -d dbbenchw -u dbm,dbs -uSQL <usr>,<pwd>

See the Sapdb Community Pages for some examples of the such a script.

Similarly to Oracle, the from clause alias plus 'AS' is not handled -
been using Oracle as a model here...? See the Oracle section.

Note that the load generator produces a REPMCLI script for each table.


Firebird
--------

Loading from flat files is a little different from the norm in this 
case - it is done using the concept of external tables. The current
version (1.0.2) will only handle fixed format flat file, so it is
necessary to run the data generator using the options :

$ datagen -m fixed

If you want to generate datasets using size bigger than 1, then ensure
that your firebird binary can handle files bigger than 2G - currently
many cannot.

The load script makes use of the udf RTRIM to remove trailing spaces
from fixed length charactor data. This does not exist by default - 
the udf examples need be built run against the target database.
However, the vanilla definition of RTRIM only allows 80 characters in
or out. This needs to be altered to 100'ish (max is 32K... why use 80?).

Queries suffer similarly to Oracle - from clause alias 'AS' is not handled -
does everybody use Oracle as a model ? Anyway see the Oracle section.


2 Optimization Issues
=====================

These are some optimization settings that were used for testing a size = 1
dataset. These should be viewed as a starting point as opposed to the last
word.

The idea is to have about 100M of buffers and 20M or sort.
  

Postgresql
----------
Version 7.3.1

shared_buffers			= 10000
sort_mem				= 20480
effective_cache_size	= 48000
random_page_cost		= 0.8


Mysql
-----
Version 4.0.6

key_buffer				= 20M
sort_buffer				= 20M
record_buffer			= 100M
join_buffer				= 20M
myisam_sort_buffer_size	= 20M


DB2
---
Version 8.1.0
Database managed space

1 4K buffer@pages		= 25000
util_heap_sz			= 5000
sortheap				= 5000


Oracle
------
Version 9.2.0
Based on initdw.ora

db_cache_size			= 100M
pga_aggregate_target	= 20M
shared_pool_size		= 10M
java_pool_size			= 0
undo_tablespace			= UNDO
undo_management			= AUTO
nls_date_format			= 'yyyy-mm-dd'


Sapdb
-----
Version 7.3
Based on tst database creation script

data_cache				= 25000
cat_cache_supply		= 1000
date_time_format		= iso
?sort - where to set this?                  


Firebird
--------
Version 1.0.2 classic
Buffers 				= 25000
?sort
