Manual:IBM DB2

From MediaWiki.org
Jump to: navigation, search

MediaWiki had experimental IBM DB2 support between 1.15 and 1.21 where it was removed due to lack of maintainership.

Prerequisites[edit]

Database creation[edit]

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[edit]

RightScale[edit]

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[edit]

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[edit]

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[edit]

#!/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[edit]

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[edit]

DatabasesManual:Database layout Engines: MySQL/MariaDBManual:MySQLOracleManual:OraclePostgreSQLManual:PostgreSQLSQLiteManual:SQLiteSQL ServerManual:Microsoft SQL Server

Technical documentation: SchemaManual:Database layout (tablesCategory:MediaWiki database tables) – API property associationsAPI:Database field and API property associationsField prefixesDatabase field prefixesPrimary key storage in other fieldsManual:Primary key storage in other fieldsWikimedia extension tablesCategory:Wikimedia extension database tables
Configuration: SettingsManual:Configuration_settings#Database_settingsSharingManual:Shared database
Development: AccessManual:Database accessOptimizationDatabase optimizationPolicyDevelopment policy#Database_patchesUpdaterManual:DatabaseUpdater.phpExtension schema updatesManual:Hooks/LoadExtensionSchemaUpdatesPatch fileManual:SQL patch file

Core tables: archivebot_passwordscategorycategorylinkschange_tagconfigexternallinksfilearchivehitcounterimageimagelinksinterwikiiwlinksipblocksjobl10n_cachelanglinkslogginglog_searchmsg_resourcemsg_resource_linksmodule_depsobjectcacheoldimagepagepagelinkspage_propspage_restrictionsprotected_titlesquerycachequerycachetwoquerycache_inforecentchangesredirectrevisionsearchindexsitessite_identifiers - site_statstag_summarytemplatelinkstexttranscacheupdateloguploadstashuseruser_former_groupsuser_groupsuser_newtalkuser_propertiestag_summaryvalid_tagwatchlist