IBM DB2 UniversalDatabase, Cloudscape,and Apache DerbyDan [email protected],[email protected]

AgendaDB2 Universal Database, Cloudscape,Apache Derby Developing with PHP extensions: Unified ODBC, PDO ODBC, ibm db2 90% of any database application: Connecting to a database serverPreparing and executing statementsFetching result setsStored proceduresPerformance tips and commonmistakes

Why IBM and PHP? “Radical simplicity”Traditional infrastructure for creatingapplications has encouraged layers ofabstraction (J2EE: entity beans) One of IBM's “simple Web app” attemptsresulted in Net.Data: Net.Data, a full-featured and easy to learn scriptinglanguage, allows you to createpowerful Web applications. Net.Data reached itsend of support date on September 30, 2004 forWindows and UNIX. The successor product forNet.Data is IBM WebSphere Application server.

Why IBM and PHP? PHP is a barrier-free programminglanguage, supporting the fullcontinuum from direct to abstractPHP itself is open-source, with abroad community contributingextensions, documentation, and helpPHP is the language of choice formany OSS applications: blogs, wikis,CMS, Web mail, CRM

IBM DB2 Universal Database The name in enterprise databases Available for z/OS, iSeries, Linux, UNIX, and WindowsIBM -- originator and leader of relational databasetechnologyFast, powerful, reasonably priced Start with DB2 UDB Express (maximum 2 CPUs, 4GBRAM) and then scale as your business grows: Scale up with SMP (64-way or beyond)Scale out with a cluster of up to 1,024 serversDB2 on Linux holds top positions in TPC-H*performance and price/performanceDB2 on AIX holds top position in TPC-C* performance*See Appendix for TPC publication information. Results referenced are current as of April 30, 2005.For the latest TPC results, visit

Apache DerbyLatest release: 10.1 alpha History Relational database technology started in 1996as Cloudscape Acquired by Informix (1999) and IBM (2001) Contributed to Apache as Apache Derby (2004) Features:2 MB footprint Unicode, ISO data types, relational constraints,stored procedures, user defined functions,triggers, indexes, views, and transactions Encryption and authentication options Supports up to 50 GB of data (single partition)

IBM Cloudscape IBM Cloudscape Stable snapshot of Apache Derby Java & native installers Java Runtime Environment Documentation Optional IBM support

Many servers, a single client DB2 and Apache Derby databaseservers speak DRDA protocolDB2 client contains CLI (and ODBC,and Java) libraries that talk DRDA Therefore, one PHP extension built onthe DB2 client can talk to manydifferent database servers DB2 Runtime Client is a freedownload from

ApacheDerbyDB2(Linux)DB2 (z/OS)DB2Client /PHP /HTTPDNetworkNetworkDB2 ConnectWebBrowser

Cataloging a database Start your DB2 command line processor(Unix only) - Inherit the DB2 instanceprofile bash source /home/db2inst1/sqllib/db2profile Catalog the database "node" (server) bash db2 CATALOG TCPIP NODE warehouse SERVER 50000 Catalog the database (DB2) bash db2 CATALOG DATABASE db2mart AT NODE warehouse Catalog the database (Derby) bash db2 CATALOG DATABASE db2mart AT NODE warehouseAUTHENTICATION SERVER

Test your connection Connect to your database bash db2 CONNECT TO db2mart [USER username [USINGPASSWORD password]] DB2 defaults to using your current, logged-inuser IDDerby always requires a user ID and passwordIf the connection fails, ensure: The database server has been started: (DB2) bash db2start(Derby) bash ./setNetworkServerCP.ksh && ./startNetworkServer.kshThe DB2 server setting DB2COMM includes TCPIP: bash db2set DB2COMM TCPIP

A wealth of PHP options Three PHP drivers support DB2:Unified ODBC (ext/odbc) Extension for DB2 and Cloudscape(ibm db2) PHP Data Objects: PDO ODBC All three PHP drivers are offeredunder open-source licenses andavailable from

Unified ODBC (ext/odbc) Built in to PHP coreNormally compiled against a genericODBC driver manager Can be compiled against DB2 libraries(./configure --with-ibm-db2) for nativeaccess Drawbacks:Scrollable cursors: slow & warning-prone No support for OUT/INOUT storedprocedures

Extension for DB2 andCloudscape (ibm db2) Available from the PECL repositoryunder the Apache 2.0 LicenseDeveloped and supported by IBM Full featured support for storedprocedures and LOBs* Fast Drawbacks: New extension undoubtedly somebugs, somewhere

PHP Data Objects: PDO ODBC Fast, light, pure-C standardized dataaccess interface for PHPPDO ODBC compiles directly against DB2libraries for native access to DB2 Standard database API simplifiesapplication development Fast, light weight OO interface Drawbacks: PHP 5 only; still under development

Compiling the ibm db2 extension Prerequisites: Through PEAR installer: DB2 client with development headers(/opt/IBM/db2/V8.1/include/sqlcli.h)bash pear install ibm db2-betaThrough CVS:bash bash bash phpize./configure --with-IBM DB2make && su -c 'make install'

Configuring PHP for ibm db2 Ensure extension dir is set in php.iniAdd extension ibm tophp.iniSource the DB2 instance environment . /home/db2inst1/sqllib/db2profileAdjust Web server environment: Modify /etc/init.d/httpd or/etc/init.d/apache[2] to source db2profile

Or just use Zend Core for IBM Free download from Installs DB2 client, PHP, ibm db2extension, and Unified ODBCextensionIncludes Web-based configuration UIAvailable for Linux x86, x86-64, Linuxon POWER, and AIX

Your first ibm db2 application ?php sql "SELECT name, breed FROM ANIMALSWHERE weight ?"; conn db2 connect( database, user, password); stmt db2 prepare( conn, sql); res db2 execute( stmt, array(10));while ( row db2 fetch assoc( res)) {print "{ row['NAME']} is a { row['BREED']}.\n";}? Pook is a cat.Bubbles is a gold fish.

Cataloged connections Assuming: database 'db2mart'; user 'db2inst1'; password 'ibm db2'; Unified ODBC conn odbc connect( database, user, password); ibm db2 conn db2 connect( database, user, password);

Uncataloged connections Assuming: host 'localhost'; port 50000; DSN "DRIVER {IBM DB2 ODBC DRIVER};PORT port;HOSTNAME host;DATABASE database;PROTOCOL TCPIP;"; Unified ODBC uconn odbc connect( DSN, null, null); ibm db2 uconn db2 connect( DSN, null, null);

Connection tips Why does it take so long to make thefirst connection to a DB2 database? DB2 automatically releases system resourcesafter a period of inactivity; the first connectionmakes it allocate system resources.You can avoid this by keeping DB2 vigilant:bash db2 ACTIVATE DATABASE db2mart What about persistent connections? Dangerous, but can be useful for read-onlyoperationsUnified ODBC and ibm db2 offer * pconnect()

Closing connections Every extension automaticallycloses connections at the end ofthe script, but also providesexplicit approaches:Unified ODBCodbc close( conn); ibm db2db2 close( conn);

Error handling Unified ODBC and ibm db2 offerprocedural interfaces: To return an SQLSTATE value: odbc error() db2 conn error(), db2 stmt error() To return an error message: odbc errormsg() db2 conn errormsg() db2 stmt errormsg()

Issuing SQL statements DB2 and Derby support prepared statementsfor superior performance and securityUse ? placeholders to bind values by position: sql 'SELECT name FROM animals WHERE breed ?'; Unified ODBC*: stmt odbc prepare( conn, sql); rc odbc execute( stmt, array('cat')); ibm db2 stmt db2 prepare( conn, sql); rc db2 execute( stmt, array('cat'));

Unified ODBC: cursor issues By default, Unified ODBC uses a dynamicscrollable cursor, which DB2 and Derbyservers don't support Every fetch and prepare request results in an SQLwarning as the cursor downgrades automatically to adynamic keyset-driven cursorAlso causes severe performance problemsWith a cataloged connection, you canautomatically downgrade the cursor toforward-only by setting a CLI configurationparameter:bash db2 UPDATE CLI CFG FOR SECTION db2mart USINGPATCH2 6

Fetching data by row UnifiedODBC:// Indexed by column numberodbc fetch into( stmt, row);// Indexed by column name row odbc fetch assoc( stmt);// PHP object; column names properties row odbc fetch object( stmt);

Fetching data by row ibm db2// Indexed by column number row db2 fetch into( stmt);// Indexed by column name row db2 fetch assoc( stmt);// Indexed by column number and name row db2 fetch both( stmt);// PHP object; column names properties row db2 fetch object( stmt);

Calling stored procedures Unified ODBCProvides no means of explicitlybinding parameters You can only call stored proceduresthat accept input (IN) parameters* PDO and ibm db2 Provide methods for binding output(OUT) and input/output (INOUT)parameters for stored procedures

Calling stored procedures: ibm db2 Rather than dynamically binding an array of inputparameters in db2 execute(), calldb2 bind param()Passing DB2 PARAM INOUT tells ibm db2 to bind theparameter as an INOUT parameter Be careful: instead of passing in a variable, you pass ina string containing the name of a variable colour 'red'; stmt db2 prepare('CALL puree fruit(?)');db2 bind param( stmt, 1, 'colour',DB2 PARAM INOUT);db2 execute();print("After CALL the colour is: colour");

Performance tips: COUNT Counting rows returned by SELECT Common (bad) pattern – does not work forforward-only cursors, very slow for scrollablecursors stmt db2 exec('SELECT * FROM animals', array(CURSOR DB2 SCROLLABLE)); num db2 num rows( stmt);if ( num) { . } Better pattern: stmt db2 exec('SELECT COUNT(*) FROM animals');list( num) db2 fetch into( stmt);if ( num) { stmt db2 exec('SELECT col, col FROM animals');} Best pattern: stmt db2 exec('SELECT col, col FROM animals');while ( row db2 fetch into( stmt)) { . }

Performance tips: cursors Scrollable cursors can be s-l-o-w and create lock escalationproblems Also, Derby currently does not support scrollable cursorsForward-only cursors meet the needs of 99% of allapplications Unified ODBC defaults to dynamic scrollable cursors, whichDB2 server has to downgrade to keyset-driven Major networking overhead in client / server modeUse UPDATE CLI CFG workaround to force downgrade at clientand avoid overheadPDO and ibm db2 default to fast "forward-only" cursors, butgive you the option of forcing a keyset-driven cursor for agiven statement If you just want a dirty snapshot of a small set of data, PDOoffers PDOStatement::fetchAll(); you can iterate through thereturned array of rows at will

Performance tips: transactions DB2, Cloudscape, and Derby defaultto AUTOCOMMIT onThis is a good default -- avoidslocking escalationHowever, if a single script performs anumber of INSERT / UPDATE /DELETE operations, consider using atransaction db2 commit() ordb2 rollback() instead

Five fast migration tips:#1 -- odbc - ibm db2 ibm db2 was designed to map easilyto Unified ODBC: Function names: s/odbc /db2 /gdb2 fetch into() returns a row, ratherthan accepting a reference row db2 fetch into( stmt) vs.odbc fetch into( stmt, row)Column functions are 0-indexed Row functions are 1-indexed

Five fast migration tips:#2 -- Counting rows db2 num rows() does not return thenumber of rows affected by a SELECTIssue the SELECT and short-circuit if firstfetch returns FALSE Issue SELECT COUNT(*) with the sameSELECT predicates Issue the SELECT using a scrollablecursor (supported by DB2 only, and slow)

Five fast migration tips:#3 -- Numeric values DB2 client is strictly SQL-compliant:character values are delimited, numericvalues are not For example, this fails if id is anINTEGER column:INSERT INTO animals (id, name) VALUES('1', 'Pook') Use prepared statements withplaceholders and let PHP convert for you stmt db2 prepare( conn, 'INSERT INTOanimals (id, name) VALUES (?, ?)');db2 execute( stmt, array('1', 'Pook'));

Five fast migration tips:#4 -- Replacing LIMIT The LIMIT clause is a non-standardextension to SQL popularized byMySQLDB2 servers (1): use the ANSI SQLROW NUMBER OVER() syntax DB2 servers (2): use scrollable cursors DB2, Cloudscape, and Derby all supportsequences and auto-generated columns;use a simple WHERE clause

Five fast migration tips:#5 -- Metadata functions Both Unified ODBC and ibm db2 offerdatabase metadata functionsUnified ODBC uses the PHP 4smashedtogetherfunctionnames() ibm db2 uses PHP 5separated function names(), but alsodefines aliases for the deprecatednaming style

Two performance pointers: #1 -ACTIVATE DATABASE By default, DB2 is a good systemcitizen and uses as few resources aspossible.However, in this state it takes time toallocate resources for the firstconnection. To prime the connection pump for aspecific database: bash db2 ACTIVATE DATABASE dbname

Two performance pointers: #2 -Prepared statements Issuing SQL with db2 exec() allowsyou to execute 'one-shot' statementsHowever, preparing and executingstatements with db2 prepare() /db2 execute() enables the databaseto cache its access planThis can even improve performancefor statements that are only issuedonce

Common problems: TCP/IPconnections If your first attempt to connect to adatabase via TCP/IP fails: Is the database running?(DB2) bash db2start (Derby) bash ./ Can you connect from the command line? bash db2 CONNECT TO db USER userDoes the server know it should talkTCP/IP? (DB2 server) bash db2set DB2COMM TCPIP

Common problems: LIMIT DB2 and Derby do not support the nonstandard LIMIT clauseDB2 implements the SQL standardROW NUMBER() OVER clause:SELECT * FROM (SELECT name, breed, ROW NUMBER()OVER (ORDER BY id)AS rows FROM animals) AS myanimalsWHERE rows skip AND rows (n skip) With Derby, you need to implement uniquerow identifiers as part of your databaseschema

Common problem: Case-sensitivity Standard states that all non-delimited SQL objectidentifiers are folded to upper caseThis causes all kinds of confusion with metadata methodsor fetching rows into associative arraysUser names versus schema names:CREATE TABLE db2inst1.animals (name VARCHAR(128))db2 tables(. 'DB2INST1' .) returns resultsdb2 tables(. 'db2inst1' .) returns no results Column names:SELECT name, weight FROM animals; row db2 fetch assoc(.); row['NAME'] exists row['name'] does not exist

Common problem: SELinux Fedora Core 3, Red Hat Enterprise Linux4.0 added SELinux policies for Apache,standard environmentDB2 client libraries are located in a'non-standard' locationResult: default SELinux policy preventsApache from loading DB2 libraries Solution #1: Disable SELinux(/usr/sbin/setenforce 0 or modify/etc/selinux/config)Solution #2: Modify SELinux policy (?)

Database abstraction layers PDO is a low level abstraction for dataaccessHigher level abstraction layers PEAR::DBMDB, MDB2ADODBdb odbtpCurrently Unified ODBC is supported inmost abstraction layers ibm db2 would be relatively straight-forwardto add due to similarity to Unified ODBC

References Databases: Apache Derby: /DB2 Universal Database: extensions: ibm db2 extension: db2/Unified ODBC article: wiki: Basics

TPC Publication Details TPC Benchmark, TPC-C, TPC-H, tpmC are trademarks of theTransaction Processing Performance Council. For further TPCrelated information, please see as of May 10, 2005 TPC-H performance: TPC-H price/performance: 100GB: IBM DB2 UDB Enterprise Server Edition 8.1 on IBM eServer 325running Suse Linux Enterprise Server 8; Metrics: 12,216 QphH; 71.00/QphH; Available: 11/08/2003300GB: IBM DB2 UDB Enterprise Server Edition 8.1 on IBM eServer 325running Suse Linux Enterprise Server 8; Metrics: 13,194 QphH; 65.00/QphH; Available: 11/08/20031000GB: IBM DB2 UDB Enterprise Server Edition 8.2 on IBM eServer x346running Suse Linux Enterprise Server 9; Metrics: 53,451 QphH; 33.00/QphH; Available: 02/14/20051000GB: IBM DB2 UDB Enterprise Server Edition 8.2 on IBM eServer x346running Suse Linux Enterprise Server 9; Metrics: 53,451 QphH; 33.00/QphH; Available: 02/14/2005TPC-C performance: IBM DB2 UDB Enterprise Server Edition 8.2 on IBM eServer p5 595 64prunning IBM AIX 5L 5.3; Metrics: 3,210,540 tpmC; 5.19/tpmC; Available:05/14/2005

DB2 on AIX holds top position in TPC-C* performance *See Appendix for TPC publication information. Results referenced are current as of April 30, 2005.For the latest TPC results, visit . Scrollable cursors can be s-l