Manual:IBM DB2

The purpose of this page is to collect information and experiences about running MediaWiki on IBM DB2.

MediaWiki 1.15 was released with partial DB2 support. Improved DB2 support is in the development trunk and is slated for the 1.16 release. Current builds are linked below.

Prerequisites

• IBM DB2 9.7 (required for implicit casting support)
• MediaWiki 1.17 (tentative, full support may not be present in time for release)
• ibm_db2 1.8.4 or better (Windows build)

Database creation

Before you can run the MediaWiki web installer, you have to create a database for MediaWiki to hold its data. In the commands below, wikidb is the name of the database. If you are using the command line, you will have to issue the commands in a single line each.

db2 CREATE DATABASE wikidb AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768

Enable the statement concentrator, which should get you the performance benefits of prepared statements without changing the MediaWiki codebase to use prepared statements everywhere

db2 CONNECT TO wikidb
db2 UPDATE DATABASE CONFIGURATION USING STMT_CONC LITERALS IMMEDIATE
db2 connect reset
db2stop
db2start

Enable the ability to do limits and offsets in the where clause.

db2 connect reset
db2stop
echo Set compatibility to 01 or 0F
db2set DB2_COMPATIBILITY_VECTOR=01
db2start

Installation scripts

RightScale

RightScale is a automation wrapper around the Amazon EC2 cloud environment. It has ready-made images for running IBM DB2.

Script for installing IBM DB2 PHP extension

This will install the ibm_db2 php extension on the base Ubuntu image on RightScale. It assumes you have the IBM Data Server Driver for ODBC and CLI attached to the script.

#!/bin/bash
 echo "Installing packages"
 apt-get update && apt-get -y install libstdc++5 libaio1 php-pear php5-dev php5 libapache2-mod-php5
 
 TAR1=${ATTACH_DIR}/v9.7_linuxia32_odbc_cli.tar.gz
 TAR2=${ATTACH_DIR}/ibm_data_server_driver_for_odbc_cli_32_linuxia32_v97.tar.gz
 
 echo "Unzipping"
 if [ -f ${TAR1} ]; then
   tar --index-file /tmp/odbc_cli.tar.log -xvvf ${TAR1} -C /tmp/
 elif [ -f ${TAR2} ]; then
   tar --index-file /tmp/odbc_cli.tar.log -xvvf ${TAR2} -C /tmp/
 else
   echo "File not found"
 fi
 echo "Deploying unzipped DB2 libraries"
 cd /tmp
 cp -ru odbc_cli/clidriver/* /usr
 
 echo "Installing php extension"
 pecl install ibm_db2 << COMMANDS
 1
 /usr
 
 COMMANDS
 echo "Updating php.ini"
 echo "extension=ibm_db2.so" >> /etc/php5/apache2/php.ini
 
 echo "Restarting the web server"
 /etc/init.d/apache2 restart
 
 exit 0

Script for deploying a MediaWiki archive

This script assumes it has a MediaWiki installer attached in a MediaWiki.tar.gz file:

#!/bin/bash

mkdir /var/www/mediawiki -m 777
chown www-data:www-data /var/www/mediawiki
tar --index-file /tmp/MediaWiki.tar.log -xvvf ${ATTACH_DIR}/MediaWiki.tar.gz -C /var/www/mediawiki

exit 0

Script for creating a MediaWiki database

#!/bin/bash

echo "Creating database"
su - db2inst1 -c "db2 CREATE DATABASE wikidb AUTOMATIC STORAGE YES USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 32768"
echo "Enable the Statement Concentrator"
su - db2inst1 -c "db2 CONNECT TO wikidb && db2 UPDATE DATABASE CONFIGURATION USING STMT_CONC LITERALS IMMEDIATE"
echo "Restarting DB2"
su - db2inst1 -c "db2stop force"
su - db2inst1 -c "db2start"

exit 0

SQL data types

MySQL	Postgres	DB2	Notes
INT	INTEGER	INTEGER
INT UNSIGNED	INTEGER	BIGINT
TINYINT	SMALLINT	SMALLINT
TINYINT UNSIGNED		SMALLINT
BIGINT	BIGINT	BIGINT
BIGINT UNSIGNED		BIGINT	page.page_counter, ss_total_views, ss_total_edits, ss_good_articles
REAL	NUMERIC(15,14)	NUMERIC(15,14)
BOOL	SMALLINT	SMALLINT
ENUM	TEXT	VARCHAR(255)
BINARY(14)	TIMESTAMPTZ	TIMESTAMP(3)
BINARY(40)	TEXT	VARCHAR(40)
VARBINARY(16)	BYTEA	VARCHAR(16) FOR BIT DATA
VARBINARY(20)	TEXT	VARCHAR(20)
VARBINARY(32)	TEXT	VARCHAR(32)
VARBINARY(40)	CIDR	VARCHAR(40)	recentchanges.rc_ip
VARBINARY(60)	TEXT	VARCHAR(60)
VARCHAR(32)	TEXT	VARCHAR(32)
VARCHAR(70) BINARY	TEXT	VARCHAR(70)
VARCHAR(70) BINARY	TEXT	VARCHAR(86)	categorylinks.cl_sortkey for compatibility with MW 1.10
VARCHAR(255)	TEXT	VARCHAR(255)
VARCHAR(255) BINARY	TEXT	VARCHAR(255)
TINYBLOB	TEXT	VARCHAR(1024)
BLOB	TEXT	CLOB(64K) INLINE LENGTH 4096
MEDIUMBLOB	TEXT	CLOB(16M) INLINE LENGTH 4096
LONGBLOB	TEXT	CLOB(4G) INLINE LENGTH 4096	Not used
TINYTEXT	TEXT	VARCHAR(1024)
TEXT	TEXT	CLOB(64K) INLINE LENGTH 4096

If you see something that doesn't match the above table, please email User:Leonsp or post on the WikiTech-l mailing list.

See Also

• DB2 Express-C - Free data server
• User:Leonsp - Maintainer of DB2 support
• Snapshots with DB2 support
• Changelog for IBM DB2 support

Databases	Engines: MySQL – Oracle – PostgreSQL – SQLite – IBM DB2 Technical documentation: Schema (tables) – Access