Deploying IBM DB2 inside a Solaris 10 Container

1. Creating the ZFS filesystem:
# zfs create rpool/export/zones

2. Configuring the DB2 zone:
# zonecfg -z db2
db2: No such zone configured
Use 'create' to begin configuring a new zone.
zonecfg:db2> create
zonecfg:db2> set zonepath=/export/zones/db2
zonecfg:db2> set autoboot=true
zonecfg:db2> add net
zonecfg:db2:net> set address=192.168.1.100/24
zonecfg:db2:net> set physical=iwk0
zonecfg:db2:net> end
zonecfg:db2> verify
zonecfg:db2> commit
zonecfg:db2> exit

3. Installing the DB2 zone:
# zoneadm -z db2 install
A ZFS file system has been created for this zone.
Preparing to install zone .
Creating list of files to copy from the global zone.
Copying <9648> files to the zone.
Initializing zone product registry.
Determining zone package initialization order.
Preparing to initialize <1319> packages on the zone.
Initialized <1319> packages on zone.
Zone is initialized.
Installation of these packages generated errors:
Installation of these packages generated warnings:
The file contains a log of the zone installation.

4. Listing the zones:
# zoneadm list -cv
ID NAME STATUS PATH BRAND IP
0 global running / native shared
- db2 installed /export/zones/db2 native shared

5. Booting the DB2 zone:
# zoneadm -z db2 boot

6. Logging into the zone:
# zlogin -C db2
[Connected to zone 'db2' console]

Configure the initial system (locale, etc).

7. Install IBM DB2 Database 9:
db2# gunzip db2_v9fp1_ese_solaris_x64.tar.gz
db2# tar xvf db2_v9fp1_ese_solaris_x64.tar
db2# cd ese/disk1/
db2# ./db2_install


Default directory for installation of products - /opt/IBM/db2/V9.1

***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no


Specify one or more of the following keywords,
separated by spaces, to install DB2 products.

CLIENT
RTCL
ESE

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
CLIENT RTCL ESE
DB2 installation is being initialized.

Total number of tasks to be performed: 41
Total estimated time for all tasks to be performed: 915

Task #1 start
Description: Checking license agreement acceptance
Estimated time 1 second(s)
Task #1 end

Task #2 start
Description: GSKit support
Estimated time 19 second(s)
...

8. Add a regular user account to your DB2 zone:

db2# mkdir -p /export/home/cmihai
db2# useradd -s /usr/bin/zsh -d /export/home/cmihai cmihai
db2# chown cmihai /export/home/cmihai
db2# passwd cmihai
New Password:
Re-enter new Password:
passwd: password successfully changed for cmihai
db2# su - cmihai
db2% cd /opt/IBM/db2/V9.1/bin

Check locale(1) and export LC_ALL=C if needed or db2 will complain:

db2% ./db2fs
couldn't set locale correctly

Make sure you read the install log in /tmp.

Here's a tip though: if you can, use the Graphical installer (ssh -X and run db2setup instead of db2_install).
All you need now is add various tuning, limitations and zfs quotas, etc.

If you need to start over, there's always ZFS snapshots or db2_deinstall.

Either way, if you need to create an instance:

# ./db2icrt -s wse -u db2fenc1 db2inst1
Sun Microsystems Inc. SunOS 5.11 snv_90 January 2008
Sun Microsystems Inc. SunOS 5.11 snv_90 January 2008
DBI1070I Program db2icrt completed successfully.

You can now use db2 to create a database and connect to it.

# db2
db2=> CREATE DATABASE test
db2 => CONNECT TO test
Database Connection Information

Database server = DB2/SUN64 9.1.1
SQL authorization ID = DB2INST1
Local database alias = TEST

db2 => CREATE TABLE clients (name char(25), surname char(50))
DB20000I The SQL command completed successfully.
db2 => LIST TABLES

Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
CLIENTS DB2INST1 T 2008-06-11-05.39.58.167896

1 record(s) selected.

db2 => INSERT INTO clients VALUES ('Some','Guy')
DB20000I The SQL command completed successfully.
db2 => SELECT * FROM clients

NAME SURNAME
------------------------- --------------------------------------------------
Some Guy

1 record(s) selected.

Staring SQL Developer or JDeveloper fails due to hidden messagebox

Can you spot why Oracle SQL Developer and JDeveloper aren't starting in this scenario?

You can't even see it on a white background (and there is no window in the taskbar) but there is a question box right behind the slash screen :-). Pay attention to the right edge of the splash...



Still, I love the new Java look.

OpenBSD Web Portal server (Drupal)

Configuring an OpenBSD Web Portal server Running Drupal on chrooted Apache + MOD_SSL + PHP + MySQL or PostgreSQL

Final goal: Drupal Web portal with TinyMCE JavaScript editor, Unicode and Locales support running on OpenBSD 4.3.

1. Installing and configuring OpenBSD – read the FAQ, partition.

a. You can install OpenBSD via cdrom (install43.iso or cd43.iso for netinstall) or netboot via TFTP / BOOTP / DHCPD. Read diskless(8) for details. On Windows, you can use TFTPD32 for DHCP/TFTP.

b. Partition the disk (read the FAQ on partitioning). Example partitioning scheme:

Partition	Mount location	Filesystem	Mount options
/dev/sd0a	/	ffs	rw,softdep	1 1
/dev/sd0b	swap	swap
/dev/sd0c	Whole disk
/dev/sd0d	/tmp	ffs	rw,softdep,nodev,nosuid	1 2
/dev/sd0e	/var	ffs	rw,softdep,nodev,nosuid	1 2
/dev/sd0f	/usr	ffs	rw,softdep,nodev	1 2
/dev/sd0g	/home	ffs	rw,softdep,nodev,nosuid	1 2

c. Disable non-critical services (like ident, time, daytime, etc.) from /etc/inetd.conf. Just comment out the lines you don’t want with a #.

d. Make sure you enable Soft Updates (softdep) mounts – they increase the Filesystem performance tenfold when it comes to writing a lot of small files. This is done by adding “softdep” to mount options in /etc/fstab.

e. Add a regular user account. You can use the useradd(8) script. Make sure you add the user to the wheel group to allow su – root.

f. For added security configure „sudo” by running visudo(8). You can permit all users in the wheel group to sudo root: %wheel ALL=(ALL) NOPASSWD: SETENV: ALL

i. It’s usually best to disable root logins and use “sudo” for root access (sudo –i or su – if you need a root sheel). Edit /etc/ssh/sshd_config. You can also change the default ssh port from 22 to some random port to mitigate against brute force attacks. Keeps them from filling out your logs with junk. Consider using ssh keys also.

ii. Port 6969

iii. PermitRootLogin no

iv. Restart the OpenSSH daemon: „pkill –HUP sshd”

g. Configure your user profile to use a ftp mirror. Add the following to your ~/.profile: (or whatever shell you’re using, eg: .bashrc, .zshrc): PKG_PATH=ftp://ftp.su.se/pub/OpenBSD/4.3/packages/i386/; export PKG_PATH

h. Restart the system (to mount softdep, etc.).

i. Install some “critical” packages:

i. pkg_add -vi mc zsh screen elinks zip unzip bzip2

j. Install ports

i. cd /usr
sudo ftp ftp://ftp.su.se/pub/OpenBSD/4.3/ports.tar.gz
sudo tar zxf ports.tar.gz

k. Unpack the OpenBSD source code (src and kernel – sys) – these are required for patching OpenBSD:

i. cd /usr/src
sudo ftp ftp://ftp.su.se/pub/OpenBSD/4.3/src.tar.gz
sudo ftp ftp://ftp.su.se/pub/OpenBSD/4.3/sys.tar.gz
sudo tar zxvf src.tar.gz
sudo tar zxvf sys.tar.gz

l. Apply OpenBSD security patches. You download patches from http://www.openbsd.org/errata43.html and you apply them using the instructions in the patch headers:

i. ftp ftp://ftp.openbsd.org/pub/OpenBSD/patches/4.3.tar.gz
You can put 001_openssh.patch, 002_openssh2.patch and the following patches in /usr/src and apply them. Read the header for the patches for instructions.

% head -11 001_openssh.patch
Apply by doing:
cd /usr/src
patch -p0 < install ="="> lib
===> ssh
install -c -s -o root -g bin -m 555 ssh /usr/bin/ssh
install -c -o root -g bin -m 444 ssh.cat1 /usr/share/man/cat1/ssh.0
install -c -o root -g bin -m 444 ssh_config.cat5 /usr/share/man/cat5/ssh_config.0
/usr/share/man/cat1/slogin.0 -> /usr/share/man/cat1/ssh.0
/usr/bin/slogin -> /usr/bin/ssh
===> sshd
install -c -s -o root -g bin -m 555 sshd /usr/sbin/sshd
install -c -o root -g bin -m 444 sshd.cat8 /usr/share/man/cat8/sshd.0
install -c -o root -g bin -m 444 sshd_config.cat5 /usr/share/man/cat5/sshd_config.0
===> ssh-add
install -c -s -o root -g bin -m 555 ssh-add /usr/bin/ssh-add
install -c -o root -g bin -m 444 ssh-add.cat1 /usr/share/man/cat1/ssh-add.0
===> ssh-keygen
install -c -s -o root -g bin -m 555 ssh-keygen /usr/bin/ssh-keygen
install -c -o root -g bin -m 444 ssh-keygen.cat1 /usr/share/man/cat1/ssh-keygen.0
===> ssh-agent
install -c -s -o root -g _sshagnt -m 2555 ssh-agent /usr/bin/ssh-agent
install -c -o root -g bin -m 444 ssh-agent.cat1 /usr/share/man/cat1/ssh-agent.0
===> scp
install -c -s -o root -g bin -m 555 scp /usr/bin/scp
install -c -o root -g bin -m 444 scp.cat1 /usr/share/man/cat1/scp.0
===> sftp-server
install -c -s -o root -g bin -m 555 sftp-server /usr/libexec/sftp-server
install -c -o root -g bin -m 444 sftp-server.cat8 /usr/share/man/cat8/sftp-server.0
===> ssh-keysign
install -c -s -o root -g bin -m 4555 ssh-keysign /usr/libexec/ssh-keysign
install -c -o root -g bin -m 444 ssh-keysign.cat8 /usr/share/man/cat8/ssh-keysign.0
===> ssh-keyscan
install -c -s -o root -g bin -m 555 ssh-keyscan /usr/bin/ssh-keyscan
install -c -o root -g bin -m 444 ssh-keyscan.cat1 /usr/share/man/cat1/ssh-keyscan.0
===> sftp
install -c -s -o root -g bin -m 555 sftp /usr/bin/sftp
install -c -o root -g bin -m 444 sftp.cat1 /usr/share/man/cat1/sftp.0
===> scard
install -c -m 444 -o root -g bin Ssh.bin /usr/libdata/ssh

• 2. Restart the system to apply patches (or just the affected services by using pkill –HUP for example if you didn’t patch the kernel).

3. Installing additional OpenBSD filesets if required by say php5-gd: (if you skipped this at install). You can read the FAQ, but this is basically it:

a. % cd / && sudo ftp ftp://ftp.su.se/pub/OpenBSD/4.3/i386/xbase43.tgz
% sudo tar xzvphf xbase43.tgz

% sudo ldconfig -m /usr/X11R6/lib

4. Install PHP 5 (and various libraries for image manipulation, database access and Unicode support):

a. % sudo pkg_add -vi php5-core php5-mysql php5-curl php5-mbstring php5-gd
Ambiguous: choose package for php5-gd
0:
1: php5-gd-5.2.5
2: php5-gd-5.2.5-no_x11
Your choice: 1
parsing php5-gd-5.2.5

b. Activate PHP modules by creating the various symbolic links:

i. % sudo ln -s /var/www/conf/modules.sample/php5.conf /var/www/conf/modules

ii. % sudo ln -fs /var/www/conf/php5.sample/curl.ini /var/www/conf/php5/curl.ini

iii. % sudo ln -fs /var/www/conf/php5.sample/gd.ini /var/www/conf/php5/gd.ini

iv. % sudo ln -fs /var/www/conf/php5.sample/mbstring.ini /var/www/conf/php5/mbstring.ini

v. % sudo ln -fs /var/www/conf/php5.sample/mysql.ini /var/www/conf/php5/mysql.ini

5. Install, configure and secure the MySQL database:

a. % sudo pkg_add -vi mysql-server

b. Installing the default database

i. % sudo /usr/local/bin/mysql_install_db

c. Starting the MySQL service:

i. % sudo /usr/local/bin/mysqld_safe&
[1] 32390
% Starting mysqld daemon with databases from /var/mysql

d. Secure the installation (delete anonymous users, the test database and set a root password). PS: don’t use # in your password, there’s a bug in the script. Set that manually if you want.

i. % sudo /usr/local/bin/mysql_secure_installation

e. Tune some sysctl parameters for MySQL:

i. Edit /etc/sysctl.conf:
kern.shminfo.shmall=32768
kern.maxfiles=8192

ii. Apply the changes
% sudo sysctl kern.shminfo.shmall=32768
kern.shminfo.shmall: 8192 -> 32768
% sudo sysctl kern.maxfiles=8192
kern.maxfiles: 3580 -> 8192

iii. Add a mysql login in /etc/login.conf:
mysql:\
:openfiles-cur=1536:\
:openfiles-max=3096:\
:tc=daemon:

iv. % sudo cap_mkdb /etc/login.conf

6. Configure SSL (Secure Sockets Layer) for Apache mod_ssl (https). Generate a self signed certificate (or sign one) by reading ssl(8).

a. % sudo openssl genrsa -out /etc/ssl/private/server.key 1024
% sudo openssl req -new -key /etc/ssl/private/server.key -out /etc/ssl/private/server.csr
% sudo openssl x509 -req -days 365 -in /etc/ssl/private/server.csr -signkey /etc/ssl/private/server.key -out /etc/ssl/server.crt

b. Test SSL support (lynx is ssl enabled):
% sudo apachectl startssl
% lynx https://localhost

7. Configure the Apache webserver:

a. Edit the Apache configuration file and setup PHP and server details /var/www/conf/httpd.conf
AddType application/x-httpd-php .php
ServerAdmin cmihai@website
DirectoryIndex index.html index.php
ServerName hostname(fqdn)

b. Configure PHP:

i. Edit the PHP config /var/www/conf/php.ini
upload_max_filesize = 12M

c. Test PHP:
ed /var/www/htdocs/index.php
a

Test PHP:
.
w
q
% lynx http://localhost/index.php

d. Configure Sendmail for supporting the Apache chroot[1]
% sudo pkg_add -vi mini_sendmail-chroot
parsing mini_sendmail-chroot-1.3.6p0
mini_sendmail-chroot-1.3.6p0: complete

% sudo mkdir -p /var/www/usr/sbin/
% sudo ln /var/www/bin/mini_sendmail /var/www/usr/sbin/sendmail
% sudo cp /bin/sh /var/www/bin

8. Configure services to start at boot (Apache and MySQL):

a. % sudo ed /etc/rc.conf
httpd_flags="-DSSL"

b. % sudo ed /etc/rc.local
if [ -x /usr/local/bin/mysqld_safe ] ; then
su -c mysql root -c '/usr/local/bin/mysqld_safe >/dev/null 2>&1 &'
echo -n ' mysql'
fi
# Crearea unui link pentru socketul MySQL in chrootul Apache
mkdir -p /var/www/var/run/mysql
sleep 5
ln -f /var/run/mysql/mysql.sock /var/www/var/run/mysql/mysql.sock

c. Reboot to test changes.

9. Crate a database for Drupal and a user. Grand permissions:

a. % sudo mysql -u root -p
CREATE USER drupal IDENTIFIED BY 'password';
CREATE DATABASE drupal CHARACTER SET utf8;
GRANT ALL PRIVILEGES ON drupal.* to USER drupal@localhost IDENTIFIED BY 'password';

10.Install Drupal and modules:

a. % sudo pkg_add -vi drupal5-tinymce drupal5-link-to-content drupal5-imce drupal5-image drupal5-backup-migrate drupal5-autolocale drupal5-token drupal5-securelogin ImageMagick

11.Optionally configure .httauth for security:

a. % cd /var/www
% sudo htpasswd -c htpasswd username

Add .htaccess to /var/www/htdocs
AuthName "Nu este permis accesul"
AuthType Basic
AuthUserFile ../htpasswd
require valid-user

b. Add “Add AllowOverride All” în /var/www/conf/httpd.conf.

c. Restart Apache (apachectl stop && apachectl startssl).

You can now configure Drupal and the various modules. Be sure to setup a backup schedule. You can use the Drupal database backup and restore module.

You can also install Apache mod_security for additional security:
% sudo pkg_add -vi modsecurity-apache
% sudo /usr/local/sbin/mod_security-enable
% sudo apachectl stop && sudo apachectl startssl

Make sure you read /usr/local/share/doc/mod_security/modsecurity-manual.pdf

You should also keep an eye on your webserver. For monitoring, I recommend top(1), vmstat, (8), sysctl hw, pftop and mytop.

% sudo pkg_add -vi mytop pftop
% sudo /usr/local/sbin/pftop

To use mytop you'll need a ~/.mytop file (and set proper permissions on it). You can add something like:

prompt=1
pass=
user=drupal
db=drupal
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

% mytop


MyTOP in Action

You'll also want to setup Packet Filter. Read the PF User's Guide, pf.conf(5) and the example rulesets in /usr/share/pf/examples. Enable pf in /etc/rc.conf (pf=YES).

---

[1] Read chroot(2) ssl(8) afterboot(8), etc.

Kicking it up a gear with Oracle Rdb Server on OpenVMS

$ show system
OpenVMS V8.3 on node DEIMOS 8-MAR-2008 16:15:25.04 Uptime 0 13:09:50

Time to install a database server on this box :-). And nothing says "database" quite like Oracle Rdb (ex DEC product btw).

Grab Oracle Rdb, unzip it (unzip SYS$SYSROOT:[SYSMGR.RDBKIT]rdbv72200kit_amv.zip;1), and install it :-).

@SYS$UPDATE:VMSINSTAL RDBV72200AM SYS$SYSROOT:[SYSMGR.rdbkit]

Now we can restart the service, and create our database.

$ @SYS$MANAGER:RMONSTOP72.com
$ @SYS$STARTUP:RMONSTART72.COM

$ UCX show services

Service Port Proto Process Address State

BIND 53 TCP,UDP TCPIP$BIND 0.0.0.0 Disabled
FTP 21 TCP TCPIP$FTP 0.0.0.0 Enabled
IMAP 143 TCP TCPIP$IMAP 0.0.0.0 Enabled
POP 110 TCP TCPIP$POP 0.0.0.0 Enabled
RDBSERVER 611 TCP RDB72 0.0.0.0 Enabled
SSH 22 TCP TCPIP$SSH 0.0.0.0 Enabled
TELNET 23 TCP not defined 0.0.0.0 Enabled

$ run SYS$SYSTEM:SQL$72.exe
SQL> SHOW VERSION
Current version of SQL is: Oracle Rdb SQL V7.2-200
SQL> DROP DATABASE filename PRODUCTS;
SQL> CREATE DATABASE filename PRODUCTS;
SQL> CREATE TABLE clients
cont> (
cont> ClientID char(9)
cont> CONSTRAINT CL_ID
cont> PRIMARY KEY,
cont> FirstName varchar(25),
cont> LastName varchar(25)
cont> );
SQL> INSERT INTO clients VALUES ('1','Some','Guy');
1 row inserted
SQL> INSERT INTO clients VALUES

('2','Other','Bloke');
1 row inserted
SQL> SELECT * from clients;
CLIENTID FIRSTNAME LASTNAME
1 Some Guy
2 Other Bloke
2 rows selected
SQL>

All we have to do now is fire up Phobos and cluster them :-).

If you install SQL Services on the database server, you can install the Rdb Client on your Windows box in order to administer Oracle Rdb on OpenVMS.

MySQL, Oracle, DB2, PgSQL and Firebird versus Leap Years and Division by Zero

Oracle first:

SQL> CREATE TABLE leaptest (thedate date);

Table created.

SQL> INSERT INTO leaptest VALUES ('28-feb-2008');

1 row created.

SQL> INSERT INTO leaptest VALUES ('29-feb-2008');

1 row created.

SQL> INSERT INTO leaptest VALUES ('30-feb-2008');
INSERT INTO leaptest VALUES ('30-feb-2008')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> INSERT INTO leaptest VALUES ('29-feb-2007');
INSERT INTO leaptest VALUES ('29-feb-2007')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

SQL> SELECT * from leaptest;

THEDATE
---------
28-FEB-08
29-FEB-08

As you can see, Oracle knows 2008 is a leap year, and that 2007 is not.

Let's try another good database, PostgreSQL:

goods=> CREATE TABLE leaptest (thedate date);
CREATE TABLE
goods=> INSERT INTO leaptest VALUES ('28-feb-2008');
INSERT 0 1
goods=> INSERT INTO leaptest VALUES ('29-feb-2008');
INSERT 0 1
goods=> INSERT INTO leaptest VALUES ('30-feb-2008');
ERROR: date/time field value out of range: "30-feb-2008"
goods=> INSERT INTO leaptest VALUES ('29-feb-2007');
ERROR: date/time field value out of range: "29-feb-2007"
goods=> SELECT * FROM leaptest;
thedate
------------
2008-02-28
2008-02-29
(2 rows)

No problems here either. What about IBM DB2?

db2 => CREATE TABLE leaptest (thedate date)
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-28')
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-29')
DB20000I The SQL command completed successfully.
db2 => INSERT INTO cmihai.leaptest VALUES ('2008-02-30')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007
db2 => INSERT INTO cmihai.leaptest VALUES ('2007-02-29')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0181N The string representation of a datetime value is out of range.
SQLSTATE=22007
db2 => SELECT * FROM cmihai.leaptest

THEDATE
----------
02/28/2008
02/29/2008

2 record(s) selected.

Let's even try Firebird:

SQL> CREATE TABLE leaptest (thedate date);
SQL> INSERT INTO leaptest VALUES ('28-feb-2008');
SQL> INSERT INTO leaptest VALUES ('29-feb-2008');
SQL> INSERT INTO leaptest VALUES ('30-feb-2008');
Statement failed, SQLCODE = -413
conversion error from string "30-feb-2008"
SQL> INSERT INTO leaptest VALUES ('29-feb-2007');
Statement failed, SQLCODE = -413
conversion error from string "29-feb-2007"
SQL> SELECT * FROM leaptest;

THEDATE
===========
2008-02-28
2008-02-29

Well now, let's try MySQL:

mysql> CREATE TABLE leaptest (thedate date);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('28-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('29-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('30-feb-2008');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO leaptest VALUES ('29-feb-2007');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM leaptest;
+------------+
| thedate |
+------------+
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
| 0000-00-00 |
+------------+
4 rows in set (0.00 sec)

As you can see, MySQL happily takes the input, but when we try to read it.. surprise surprise, your data isn't there! It should respond with an error code..

As Komal Shah points out, that's only due to an invalid date format, using ISO dates dates works fine.

INSERT INTO leaptest VALUES ('2008-02-28');

An invalid date will still generate a warning and a 0000-00-00 entry.

Here's another fine piece of MySQL behavior:
Division by 0:
Well, let's first see how Oracle and PgSQL handle this:

Oracle

SQL> SELECT 0/0 FROM dual;
SELECT 0/0 FROM dual
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

PostgreSQL

goods=> SELECT 0/0;
ERROR: division by zero

IBM DB2:

db2 => SELECT 0/0 FROM cmihai.leaptest
SQL0801N Division by zero was attempted. SQLSTATE=22012

Firebird:

SQL> SELECT 0/0 FROM leaptest;

=====================
Statement failed, SQLCODE = -802
arithmetic exception, numeric overflow, or string truncation
SQL> SELECT 0/1 FROM leaptest;


=====================
0
0
SQL> SELECT 1+1 FROM leaptest;


=====================
2
2

MySQL
mysql> SELECT 0/0;
+------+
| 0/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

So there you have it, MySQL unexpected behavior.

If anyone cares, tested versions: DB2 9.5, Oracle 10g and 11g, PosgreSQL 8.2, 8.3 and Firebird 2. MySQL was 5.0.45.

Getting started with PostgreSQL - a Database Primer

A quick guide to getting started with the PostgreSQL database server:

Download and install PostgreSQL. If you're using some kind of BSD or Linux, it's probably already in the ports / packages repository. You may need to use initdb to create a new database cluster, and edit pg_hba.conf to grant your users / machines connect privileges to the server. If you're in a UNIX-like OS, you'll need to "su - postgres". After that:

Connect as postgres:

psql -U postgres

Create a role:

CREATE ROLE testuser LOGIN PASSWORD 'test123';

Create a database:

CREATE DATABASE testdb;

Note that the database is created in the cluster ENCODING. If you want to specify a different encoding (not recommended) you can use something like:

CREATE DATABASE testdb OWNER = testuser ENCODING = 'UTF8';

List databases, groups, users, quit:

\l+ \dg+ \du+ \q

Connect as the new user to the new database:

psql -U testuser testdb

Create a table:

CREATE TABLE test (ID serial PRIMARY KEY, name varchar(25) NOT NULL UNIQUE);

List table, relations, schemas, describe table:

\d

\dt

\dn

\d test

Insert some data:

INSER INTO test VALUES (default, 'quux');

Do a query:

SELECT * FROM test;

\q

Delete a database:

psql -U postgres

DROP DATABASE testdb;

Once you've got the hang of things, you can use \? and \h for help:
\?
\h
\h CREATE DATABASE

You can now delete the testuser role:

psql -U postgres
DROP ROLE testuser;

Create your new username and database, and connect to it. Create a simple database schema.

Here's a fun PostgreSQL feature: HTML output. \H turns it on, \H again turns it of:

We have a very simple table with 3 entries:

goods=> \d countries;
Table "public.countries"
Column | Type | Modifiers
---------+-----------------------+-----------
country | character varying(50) | not null
Indexes:
"countries_pkey" PRIMARY KEY, btree (country)

goods=> SELECT * FROM countries;
country
---------
A
B
C
(3 rows)

\H
goods=> \H
Output format is html.
goods=> SELECT * FROM countries;

We can use \o to redirect the output to a file:

goods=> \o countries.html
goods=> SELECT * FROM countries;

\q and view the file in your web browser :-).



Now that you've got the feel of psql, you can use PgAdmin III (or PHPPgAdmin or whatever interface you like, like OpenOffice.Org Base or Microsoft Office Access). PgAdmin III also shows the SQL commands it's going to run, so it's a good way to learn SQL.




Use this for a tutorial and this for reference.

Using OpenOffice.org Base to access PostgreSQL databases

Just like Access can connect to a PostgreSQL database via ODBC or JDBC, so can OpenOffice.org Base. But the good news is we don't need to use ODBC or JDBC, as there is a native PostgreSQL SDBC driver for OpenOffice.

Download the postgresql-sdbc-ver.zip file, start OpenOffice (if you start writer type Ctrl-W to close it) so you can access Tools - Extension Manager. Add the downloaded driver, then restart OpenOffice (you also need to exit the QuickStarter. Just right click the SystemTray icon - Exit).



Now you can start OpenOffice.org Base and connect to a PostgreSQL database:



In the Connections Setting type: "dbname=YourDBName host=YourDBServerIP"



Setup a username and password:



That's pretty much it for the database setup, now you can start creating your database. You can use data already available in the PostgreSQL server, or create new tables. You then build Queries, Forms and Reports based on those tables.

You can use the Table Design or Table Wizard to create tables:



You can use the Relationship Editor to create Foreign Key constraints:



You can use the Form Design and Form Wizard to create Forms and the Report Design / Wizard for Reports:



It's usually a good idea to take a look with psql every now and then. OpenOffice.org Base isn't exactly perfect when it comes to creating the database schema, and especially when it comes to constraints. Of course, neither is the SDBG drivers, so it's best to consider this "just for fun" for the moment.

PostgreSQL 8.3 Released

PostgreSQL released version 8.3 of "the world's most advanced open source database".

Remember to dump/restore with pg_dump if you're planning to upgrade :-).

Here's a list of "What's new in 8.3".

Benchmarks also show a huge increase in performance from 8.2, some benchmarks even showing a 2.x performance boost from 8.2:

http://www.ajaxonomy.com/2007/sql/postgresql-83-vs-82-a-simple-benchmark

http://archives.postgresql.org/pgsql-general/2008-01/msg00417.php

Version  7.3.15 7.4.13  8.0.8   8.1.4   8.2.beta1 8.3beta1
tps      311    340     334     398     423       585

Improvements have also been done in terms of scalability.

Accessing PostgreSQL and Oracle with Access and SQL Developer

Microsoft Office Access is a RDBMS that uses a GUI interface and RAD tools and a Jet Database Engine backend. It can also use external data stored in SQL servers such as MSSQL or Oracle, PostgreSQL, DB2, etc. via ODBC.

PostgreSQL is "the most advanced" open source database. It is a BSD-style licensed ORDBMS that supports advanced SQL features such as referential integrity constraints (foreign keys, column checks, etc), full ACID compliance, ANSI SQL compliance, views, rules, sub-selects, transactions, triggers, sequences, inheritance and has a built in language (PL/pgSQL) similar to Oracle PL/SQL. It also exhibits almost linear scalability up to 16 cores, being much more scalable then say, MySQL. As of version 8.3 it gets a performance and scalability boost too. The pgbench benchmark here shows an almost 50% performance boost (under certain workloads) from 8.1.

Using an ODBC connection you can use Access as a Rapid Application Development interface to develop Forms, Reports and Applications using PostgreSQL as a backend database, via PSQLODBC.

Install psqlodbc on your Access machine, and all you need to get started now is a PosgreSQL server (if you don't have one on your network you can install one on your Windows machine from:
http://www.postgresql.org/download/

Fire up Access and open up a database. We are going to create the ODBC connection and save the settings. First, let's export some table to the PostgreSQL database via ODBC. We can later link or import the table (in any database).



Create a new Data Source using the "PostgreSQL Unicode" driver. The goods database was created using "CREATE DATABASE goods OWNER cmihai ENCODING 'UTF8'" and we are planning on storing Unicode data.




Once you've selected a Server hostname (or IP), username, password and database (like the newly created "goods" database in your PostgreSQL server) you need to set some advanced options. Uncheck the "Bools as Char" box.



Check "True is -1":



Now we can Import a table using an external data source (it will import the structure and data of a Table in PostgreSQL as a table in Access. The tables will not be linked).

We can also Link tables using an external data source. This means that the data and structure of the table (schema) is only modified in the PostgreSQL database. We can link multiple applications to the same database (and tables). Just use Get External Data - Link to Data Source and select the PostgreSQL ODBC connection.




As you have noticed, PostgreSQL is case sensitive.

The linked tables are shown in the Table view with a sphere icon.



While you can use the Query Builder (in Design view or SQL view) to query data in the Access database (it can use both access tables and ODBC connected tables), there is an option to pass the SQL query directly to PostgreSQL, in Pass-Through mode.

SELECT public_clienti.nume, public_clienti.prenume, public_tari.tara
FROM public_clienti INNER JOIN public_tari ON public_clienti.tara = public_tari.tara
WHERE (((public_clienti.nume)="Gigi"))
ORDER BY public_clienti.nume, public_clienti.prenume;

The PostgreSQL query wouldn't use the public_clienti prefix, we can just do a simple Pass-Through query:



You can use Pass-Through queries in your Forms and Reports too.

Of course, you can also use Access with Microsoft SQL Server 2005 Express (freely available) or even the free Oracle 10g Express, using the SQL Developer Quick Migration tool to export MDB files to Oracle.



You can use the Quick Migration Wizzard:



Or the full blown "Oracle Migration Workbench Exporter for Access" to export your .mdb file for SQL Developer and Oracle Application Express.

PostgreSQL not for sale

Here's why PostgreSQL is NOT for sale.

Sun buys MySQL: So far, so good... so what?

Sun Microsystems Inc. CEO Jonathan Schwartz answered some questions on his blog regarding Sun's future involvement with PostgreSQL, JavaDB Oracle relations and their plans with MySQL.

Sun also release a "Press Kit" on their webpage.

Some people on the other hand aren't too excited about the Sun - MySQL AB deal. Some even think this is a plot mastered by Larry Ellison to destroy MySQL (tinfoil hat, anyone?). Here's what Marketwatch has to say on the subject:

"BERKELEY, Calif. (MarketWatch) -- Sun Microsystems Inc. gobbling up MySQL is perhaps the worst single event I have ever witnessed in the history of tech mergers and acquisitions."

Sun Microsystems buys MySQL for 1 billion USD

" We announced big news today - our preliminary results for our fiscal second quarter, and as importantly, that we're acquiring MySQL AB." - Jonathan Schwartz - CEO of Sun Microsystems, Inc.

Read more here. Sun & MySQL AB press conference here.

I'm also not quite sure on how will this impact Sun's long term policy on PostgreSQL. Sun offers PostgreSQL support (PostgresSQL on Solaris even has native DTrace probes), and was a big contributor to the project. I don't think they would drop PostgreSQL support though. Maybe port PostgreSQL to MySQL as a storage engine? :-).

I'd still like to know how Sun expects to make money off MySQL. Well, enough money to justify the 1 billion investment. Sun does tend to make money off support contracts, services, hardware and all that, but MySQL AB hardly managed to pull in $50 million last year. And they have around 350 employees. And besides, nothing stopped Sun from selling MySQL support and all that before.

http://finance.google.com/finance?q=NASDAQ:JAVA

Sun Microsystems also released the Preliminary Results for Second Quarter Fiscal Year 2008 Report:

"Sun expects to report revenues for the second quarter of fiscal 2008 of approximately $3.60 billion, an increase of approximately 1 percent as compared with $3.57 billion for the second quarter of fiscal 2007. Net bookings for the second quarter of fiscal 2008 were approximately $3.85 billion, an increase of approximately 7% year over year."

I'm waiting to see how Oracle (who already bought Open Source databases Berkeley DB2 and the MySQL InnoDB engine) will react to this one. Remember what Oracle did when RedHat bought JBoss? Shortly after, Oracle "Unbreakable" Enterprise Linux (based on RHEL sources) "just happened". What now? OracleSolaris? :-). Oracle also tried to buy MySQL AB before.

On a side note, Oracle bought BEA today, for $8.5 billion, after BEA turned down their original offer of $6.7 billion. Here's the Oracle Press Release. A nice addition to their "strategic acquisition" list, which already gathered almost 40 entries since 2005.