DB2. Getting Started with Database Application Development. DB2 Version 9 SC

(1)

DB

2

®

Getting

Started

with

Database

Application

Development

DB2Version9

forLinux,UNIX,andWindows

(2)

(3)

DB

2

®

Getting

Started

with

Database

Application

Development

DB2Version9

forLinux,UNIX,andWindows

(4)

Beforeusingthisinformationandtheproductitsupports,besuretoreadthegeneralinformationunderNotices.

EditionNotice

ThisdocumentcontainsproprietaryinformationofIBM.Itisprovidedunderalicenseagreementandisprotected bycopyrightlaw.Theinformationcontainedinthispublicationdoesnotincludeanyproductwarranties,andany statementsprovidedinthismanualshouldnotbeinterpretedassuch.

YoucanorderIBMpublicationsonlineorthroughyourlocalIBMrepresentative. v

Toorderpublicationsonline,gototheIBMPublicationsCenteratwww.ibm.com/shop/publications/order v

TofindyourlocalIBMrepresentative,gototheIBMDirectoryofWorldwideContactsatwww.ibm.com/ planetwide

ToorderDB2publicationsfromDB2MarketingandSalesintheUnitedStatesorCanada,call1-800-IBM-4YOU (426-4968).

WhenyousendinformationtoIBM,yougrantIBManonexclusiverighttouseordistributetheinformationinany wayitbelievesappropriatewithoutincurringanyobligationtoyou.

(5)

Part

1. The

DB2

database

application

development

environment

.

. 1

Chapter

1. The

DB2

database

application

development

environment

.

. 3

Chapter

2. Support

for

elements

of

the

database

application

development

environment

.

. 5

Supportforelementsofthedatabaseapplication developmentenvironment. . . 5

Supportedoperatingsystemsfordatabaseapplication development . . . 7

Supportedoperatingsystemsfordatabase applicationdevelopment . . . 7

InstallationrequirementsforDB2clientsand servers(AIX) . . . 7

InstallationrequirementsforDB2clientsand servers(HP-UX) . . . 9

InstallationrequirementsforDB2clientsand servers(Linux) . . . 10

InstallationrequirementsforDB2clientsand servers(SolarisOperatingSystem). . . 13

InstallationrequirementsforDB2clientsand servers(Windows) . . . 15

DB2Clientsupportfordatabaseapplication development . . . 17

Supporteddatabaseapplicationprogramming interfaces . . . 18

ADO.NETdevelopmentforDB2databases . . . 19

IntroductiontoDB2CLIandODBC . . . 21

IntroductiontoembeddedSQL. . . 23

SupporteddriversforJDBCandSQLJ . . . . 24

IBMOLEDBProviderforDB2 . . . 26

PerlDBI . . . 27

IntroductiontoPHPapplicationdevelopmentfor DB2 . . . 27

Choosinganapplicationprogramminginterface 28 Supportedprogramminglanguagesandcompilers fordatabaseapplicationdevelopment . . . 33

Supportedprogramminglanguagesand compilersfordatabaseapplicationdevelopment . 33

Supported.NETdevelopmentsoftware . . . . 33

Supportfordatabaseapplicationdevelopmentin C. . . 34

Supportfordatabaseapplicationdevelopmentin C++ . . . 35

Supportfordatabaseapplicationdevelopmentin COBOL. . . 37

Supportfordatabaseapplicationdevelopmentin Fortran . . . 38

SupportedJavaapplicationdevelopment software . . . 38

Supportfordatabaseapplicationdevelopmentin Perl . . . 39

Supportfordatabaseapplicationdevelopmentin PHP. . . 40

Supportfordatabaseapplicationdevelopmentin REXX . . . 41

Supportedtransactionmanagers . . . 41

Supporteddatabaseapplicationdevelopmenttools 42

Chapter

3. Configuring

the

database

application

development

environment

. 45

Configuringthedatabaseapplicationdevelopment environment . . . 45

Configuringtheoperatingsystemfordatabase applicationdevelopment . . . 46

SettinguptheUNIXapplicationdevelopment environment . . . 47

UNIXenvironmentvariablesettings . . . 48

SettinguptheWindowsapplicationdevelopment environment . . . 48

ConfiguringaDB2clientfordatabaseapplication development . . . 51

Configuringthedevelopmentenvironmentfor applicationprogramminginterfaces . . . 52

SettinguptheCLIenvironment . . . 53

ConfigurationconsiderationsforXAtransaction managers . . . 54

Part

2. Appendixes

.

. 57

Appendix

A. DB2

Database

technical

information

.

. 59

OverviewoftheDB2technicalinformation. . . . 59

Documentationfeedback . . . 59

DB2technicallibraryinhardcopyorPDFformat. . 60

OrderingprintedDB2books. . . 62

DisplayingSQLstatehelpfromthecommandline processor . . . 63

AccessingdifferentversionsoftheDB2Information Center . . . 64

Displayingtopicsinyourpreferredlanguageinthe DB2InformationCenter . . . 64

UpdatingtheDB2InformationCenterinstalledon yourcomputerorintranetserver . . . 65

DB2tutorials . . . 67

DB2troubleshootinginformation . . . 67

(6)

Appendix

B. Notices

.

. 69

Trademarks . . . 71

Index

.

. 73

(7)

Part

1. The

DB2

database

application

development

environment

(8)

(9)

Chapter

1. The

DB2

database

application

development

environment

TheDB2®databaseapplication developmentenvironmentiscomposedofseveral software elements:

v operatingsystem;

v DB2Client;

v databaseapplication programminginterface(API);

v programminglanguage;

v transactionmanager;and

v developmenttools.

For eachoftheseelements,thereare multiplechoicesavailable.For example:there might beseveraloperatingsystemsavailableforyour hardware;DB2database supportsseveralAPIs;and anyXA-complianttransactionmanagercanbeusedto manage distributedtransactionswith DB2databases.

For eachoftheseelements,choosingwhichsoftwaretousedependsona variety of factors:

v whatissupported;

Forexample:DB2supportsafinite numberofoperatingsystems,and database APIs;and foreachAPI, thereisafinite listofsupportedprogramming

languages. v easeofuse;

Thereare someintegrateddevelopmentenvironmentsthatmakedeveloping databaseapplicationsmucheasier.

v thenatureofthedatabaseapplicationyouaredeveloping;

Aweb-based applicationhasparticularneedsthatotherapplicationsdon’thave. v strengthsoftheparticular software.

Forexample:static,embeddedSQLmightperform betterthandynamicSQLin somecircumstances.

Related tasks:

v “Configuringthedatabase applicationdevelopmentenvironment”onpage45 Related reference:

v “Supportforelementsofthedatabaseapplicationdevelopment environment”on

(10)

(11)

Chapter

2. Support

for

elements

of

the

database

application

development

environment

Support

for

elements

of

the

database

application

development

environment

Supported operatingsystemsfordatabaseapplicationdevelopment:

DB2 databaseapplicationdevelopment issupportedonthefollowingoperating systems: v AIX ® v HP-UX v Linux ® v Solaris v Windows ®

Thereare restrictionsaboutwhenyoucanuseavailable32-bit or64-bit architectures:

v WithLinuxonx86and32-bit Windowsoperatingsystems, only32-bit DB2

databaseinstancesare supported.On allotheroperatingsystems,only64-bit db2;databaseinstancesaresupported.

v 32-bitdatabaseapplicationscanberunwitheithera 32-bitor64-bit DB2client,

anddevelopedwitheithera 32-bitor64-bit DB2Client.

v Thereare differencesforbuildingapplicationsin32-bit and64-bitenvironments

inmostcasesontheseoperatingsystems.

DB2Client supportfordatabaseapplicationdevelopment:

ADB2Clientisrequiredfordatabaseapplicationdevelopment forDB2.

Supported databaseapplicationprogramminginterfaces:

Thefollowingapplication programminginterfacescanbeusedtoaccessa DB2 database: v CLIorODBC v embeddedSQL v JDBCorSQLJ v OLEDB v PerlDBI v PHP v ADO.Net – OLEDB.Net – ODBC.Net – DB2 .Net

Supported programminglanguagesandcompilers fordatabaseapplication development:

(12)

DB2 databaseapplicationdevelopment issupportedinthefollowinglanguages: v C v C++ v COBOL v Fortran v Java ™ v Perl v PHP v REXX v .Netlanguages

Supported transactionmanagersforusewithdatabaseapplications:

AnytransactionmanagerthatusestheXAinterface canbe usedto manageDB2 distributed transactions.

Supported developmenttoolsfordatabaseapplicationdevelopment:

The followingintegrateddevelopment environmentsand developmenttoolscanbe usedtodevelop DB2databaseapplications:

v IBMintegrateddatabaseapplication developmentenvironments

– TheDeveloperWorkbench

– RationalApplicationDeveloper

v Databaseapplication plug-insforintegrateddevelopmentenvironments

– IBM DB2DevelopmentAdd-InforVisualStudio

– EclipseDB2 Plug-In

v Toolsfor developingSQLStatements

– SQLAssist

– SQLBuilder

– SQLEditor

– Commandlineprocessor

v Databaseapplication monitoringandperformancetuningtools

– Event Monitor

– Explaintools

– CLI/ODBC/JDBC StaticProfiling

v Toolsfor developingwebapplications

– WebSphereStudioApplicationDeveloper

– DB2Alphablox Related concepts:

v “Supporteddatabase applicationdevelopmenttools” onpage42

v “Supporteddatabase applicationprogramminginterfaces”onpage18

v “Supportedtransactionmanagers”onpage41

v Chapter1,“TheDB2databaseapplication developmentenvironment,” onpage3 Related tasks:

(13)

Related reference:

v “DB2Clientsupport fordatabaseapplicationdevelopment”onpage17

v “Supportedoperatingsystemsfordatabaseapplication development”onpage7

v “Supportedprogramming languagesandcompilersfordatabase application

development”onpage33

Supported

operating

systems

for

database

application

development

Supported

operating

systems

for

database

application

development

YoucandevelopDB2 databaseapplicationsonthefollowingoperatingsystems: v AIX

v HP-UX

v Linux

v Solaris

v Windows

Thereare restrictionsaboutwhenyoucanuseavailable32-bit or64-bit architectures:

v WithLinuxonx86andWindows operatingsystemsonx86, only32-bit DB2

databaseinstancesare supported.On allotheroperatingsystems,only64-bit DB2databaseinstancesaresupported.

v 32-bitdatabaseapplicationscanberunwitheithera 32-bitor64-bit DB2client,

anddevelopedwitheithera 32-bitor64-bit DB2Client.

v Thestepsforbuildingapplicationsina32-bit environmentaresometimes

differentfromthestepsforbuildingapplicationsina 64-bitenvironment.

page5

v “InstallationrequirementsforDB2clientsand servers(Linux)”onpage10

v “InstallationrequirementsforDB2clientsand servers(SolarisOperating

System)”onpage13

v “InstallationrequirementsforDB2clientsand servers(Windows)”onpage15

v “InstallationrequirementsforDB2clientsand servers(AIX)”onpage7

v “InstallationrequirementsforDB2clientsand servers(HP-UX)”onpage9

Installation

requirements

for

DB2

clients

and

servers

(AIX)

For themostup-to-dateprerequisiteinformation,seehttp://www.ibm.com/ software/data/db2/udb/sysreqs.html.

Toinstall aDB2 clientorserverproduct,thefollowingoperatingsystem,hardware, and communicationsprerequisitesmust bemet:

(14)

Table1.AIXinstallationprerequisites

AIXVersion OperatingSystem Hardware

AIXVersion5.2.0 DB2clientisavailableusingthe64-bitAIX kernel.

DB2EnterpriseServerEdition,foreither single-partitionorpartitioneddatabase environment,isavailablewiththerequired 64-bitAIXkernel.

Oneof: v IBM ® RISC/6000 v eServer ™ pSeries®

AIXVersion5.3.0 DB2clientisavailableusingthe64-bitAIX kernel.

DB2EnterpriseServerEdition,foreither single-partitionorpartitioneddatabase environment,isavailablewiththerequired 64-bitAIXkernel.

Oneof: v IBMRISC/6000 v eServerpSeries Softwareconsiderations:

v (Clientsonly:)IfyouplantouseKerberosAuthentication,yourequireIBM

NetworkAuthenticationServiceclientv1.3orlater.TheNASclientisprovided withtheAIXBonusCD.

v Usethebosbootcommandtoswitchtothe64-bit kernel.

Toswitchtoa 64-bitkernel,yourequirerootauthorityandshouldenterthe followingcommands:

ln -sf /usr/lib/boot/unix_64 /unix

ln -sf /usr/lib/boot/unix_64 /usr/lib/boot/unix bosboot -a

shutdown -Fr

v DB2Version9requiresthe“IBMC++RuntimeEnvironment Componentsfor

AIX”whichincludesxlC.rte 8.0.0.4.ThisisavailablefromtheIBMAIXsupport website.

v Oneofthefollowingbrowsersisrequiredtoviewonline helpandtorunFirst

Steps(db2fs):

– Mozilla 1.4and up

– Firefox 1.0and up

– Netscape7.0andup

DB2product installationonNFS(NetworkFileSystem):

The installationofDB2productsonNFS(Network FileSystem)isnot recommended. RunningDB2 productsonNFS(forexample,NFSmounting /opt/IBM/db2/V9.1 andthenrunningoffcodethatwasphysicallyinstalledona remote system)requiresseveralmanualsetupsteps. Thereare alsoa numberof potentialissueswithsettingupNFSforDB2.Theseinclude possibleproblemsthat involve:

v Performance(impactedbynetworkperformance)

v Availability(youareallowing asinglepointof failure)

v Licensing(thereisnocheckingdone acrossmachines)

v DiagnosingNFSerrorscanbedifficult

Asmentioned, thesetup forNFSwillrequireseveralmanualactionsincluding: v Ensuringthatthemountpointpreservetheinstallpath

(15)

v Permissionmust becontrolled(forexample,writepermissionshouldnotbe

giventothemounting machine)

v DB2registrieshavetobe setupmanuallyandmaintainedacrossallmounting

machines

v ThelistinstalledDB2 productsand featurescommand(db2ls)mustbeset up

andmaintainedproperlyifyouneedtodetectDB2productsandfeatures v MorecareisrequiredwhenupdatingyourDB2productenvironment

v Morestepsare requiredwhencleaningupontheexportingmachineandthe

mountingmachine

For detailedinstructions,lookforthe“SettingUpDB2 onNFSMountedFile System” whitepaperwhichwillbe availablesoonafter DB2Version9ismade available.

v “Anoverview ofinstallingyourDB2product(LinuxandUNIX)”in Quick

BeginningsforDB2 Servers

v “IBMSoftwareDevelopmentKitforJavalevelsforDB2 products”inQuick

v “Communicationprotocolssupported”inQuickBeginningsforDB2Clients

Installation

requirements

for

DB2

clients

and

servers

(HP-UX)

Table2.HP-UXinstallationprerequisitesforHP-UX11iv2

OperatingSystem Hardware

DB2productscanrunonHP-UX11iv2(11.23.0505)forPA-RISC 2.x-based(PA-8x00)andItanium-basedsystemswith:

v

May2005BaseQuality(QPKBASE)bundle v

May2005ApplicationsQuality(QPAPPS)bundle

andthePHNE_32606patch.(64-bitHP-UXkernelisrequired;server only)

Oneof:

v HP9000Series700or800system v HPIntegritySeriesserver

Kernelconfigurationconsiderations:

Asystemrestart isrequiredifyouupdatethekernelconfigurationparameters.The kernelconfigurationparametersare setin/etc/system.Dependingonthevalues of yourkernelconfigurationparameters, youmight needtomodifysomeofthem before youinstall theDB2Version9clientorserverproducts.Ifthekernel configurationparametersaremodified,a rebootisnecessarytomakethechanges to/etc/system effective.

Softwareconsiderations:

v IfyouareinstallingtheApplicationDevelopmentClient,youmust havea C

compilertobuild SQLStoredProcedures.

v Oneofthefollowingbrowsersisrequiredtoviewonlinehelp andtorunFirst

(16)

v Availability(youareallowing asinglepointof failure)

v Licensing(thereisnocheckingdone acrossmachines)

Asmentioned, thesetup forNFSwillrequireseveralmanualactionsincluding: v Ensuringthatthemountpointpreservetheinstallpath

v DB2registrieshavetobe setupmanuallyandmaintainedacrossall mounting

machines

v Morestepsarerequiredwhencleaningupontheexportingmachineandthe

mountingmachine

For detailedinstructions,lookfor the“SettingUpDB2onNFSMountedFile System” whitepaperwhichwillbe availablesoonafter DB2Version9ismade available.

v “Modifyingkernelparameters(HP-UX)”inQuickBeginningsforDB2Servers Related reference:

Installation

requirements

for

DB2

clients

and

servers

(Linux)

For thelatestinformationonsupportedLinuxdistributions, pointyourbrowserto http://www.ibm.com/db2/linux/validate.

The supportedoperatingsystemsforLinuxinclude: v RedHatEnterpriseLinux4 (RHEL4)Update2

(17)

v SUSELinuxEnterpriseServer9 (SLES9)ServicePack2

Toinstall aDB2 clientorserverproduct,thefollowingdistribution requirements, hardware,andcommunicationsprerequisites mustbemet:

Table3.Linuxprerequisites

DistributionRequirements Hardware

Youshouldupdateyourkernelconfigurationparameters inpreparationforyourLinuxdistribution.Thedefault valuesforparticularkernelparametersarenotsufficient whenrunningaDB2databasesystem.

Youmayalsohaveotherproductsorapplicationsthat requireLinuxsystemresources.Youshouldsetthe kernelconfigurationparametersbasedontheneedsof yourLinuxsystemworkingenvironment.

Thekernelconfigurationparametersaresetin

/etc/sysctl.conf.SeetheModifyingkernelparameters (Linux)sectionoftheDB2InformationCenter.

Refertoyouroperatingsystemmanualforinformation onsettingandactivatingtheseparametersusingthe

sysctlcommand.

DB2serverproductsaresupportedon: v x86(Intel

®

Pentium®

,IntelXeon®

,andAMDAthlon) v

x86–64(IntelEM64TandAMD64) v

IA64(IntelItanium

® 2orhigher) v POWER ™ (anyiSeries™

orpSeriesthatsupportLinux) v

(Serveronly)eServerzSeries

®

.

– TheDatabasePartitioningFeature(DPF)isnot supported.

v (Clientonly)64-bitzSeriesorSystemz9

™

boxes.

Packagerequirements (serveronly):

ThefollowingtableslistthepackagerequirementsforSUSELinuxandRed Hat distributionsforDB2Version9 partitionedservers.

v ThepdkshKornShellpackage isrequiredforallDB2systems.

v Aremote shellutilityisrequiredforpartitioneddatabasesystems. DB2supports

thefollowingremoteshellutilities: – rsh

– ssh

Bydefault,DB2usesrshwhenexecutingcommandsonremoteDB2nodes,for example,when startinga remoteDB2database partition.TousetheDB2default, thersh-serverpackage mustbe installed(seetablebelow).For acomparison betweenrshand ssh,seethe“Securityissueswhen installingDB2”sectionofthe

AdministrationGuide:Implementation.

Ifyouchoosetousethershremoteshellutility,inetd(orxinetd)must be installedand runningaswell.Ifyouchoosetousethessh remoteshellutility, youneedtoset theDB2RSHCMDregistryvariable immediatelyafter theDB2 installationiscomplete. Ifthisregistryvariableisnotset,rshisused.Formore informationontheDB2RSHCMDregistryvariable,seethePerformanceGuide

book.

v Thenfs-utilsNetworkFileSystemsupportpackage isrequiredforpartitioned

databasesystems.

All requiredpackagesshouldbeinstalledandconfiguredbefore continuingwith theDB2setup.For generalLinuxinformation,seeyour Linuxdistribution documentation.

PackagerequirementsforSUSELinux

Packagename Description

pdksh KornShell.Thispackageisrequiredforpartitioneddatabase environments.

(18)

PackagerequirementsforSUSELinux

Packagename Description

openssh Thispackagecontainsasetofserverprogramswhichallow userstoruncommandson(andfrom)remotecomputersviaa secureshell.Thispackageisnotrequiredifyouusethedefault configurationofDB2withrsh.

rsh-server Thispackagecontainsasetofserverprogramswhichallow userstoruncommandsonremotecomputers,loginintoother computers,andcopyfilesbetweencomputers(rsh,rexec,rlogin, andrcp).ThispackageisnotrequiredifyouconfigureDB2to usessh.

nfs-utils NetworkFileSystemsupportpackage.Itallowsaccesstolocal filesfromremotecomputers.

PackagerequirementsforRedHat

Directory Packagename Description

/SystemEnvironment/Shell pdksh KornShell.Thispackageisrequiredfor partitioneddatabaseenvironments. /Applications/Internet openssh Thispackagecontainsasetofclient

programswhichallowuserstorun commandsonaremotecomputerviaa secureshell.Thispackageisnot requiredifyouusethedefault configurationofDB2withrsh. /SystemEnvironment/

Daemons

openssh-server Thispackagecontainsasetofserver programswhichallowuserstorun commandsfromaremotecomputervia asecureshell.Thispackageisnot requiredifyouusethedefault configurationofDB2withrsh. /SystemEnvironment/

Daemons

rsh-server Thispackagecontainsasetofprograms whichallowuserstoruncommandson aremotecomputer.Requiredfor partitioneddatabaseenvironments.This packageisnotrequiredifyouconfigure DB2tousessh.

/SystemEnvironment/ Daemons

nfs-utils NetworkFileSystemsupportpackage. Itallowsaccesstolocalfilesfrom remotecomputers.

Steps(db2fs):

v AnXWindowSystemsoftwarecapableofrendering agraphicaluser interfaceis

requiredifyouwanttousetheDB2SetupwizardtoinstallDB2orif youwant touseanyDB2graphicaltools.(AvailableonlyonLinuxforx86andLinuxon

AMD64/EM64T.)

(19)

Theinstallation ofDB2productsonNFS(NetworkFile System)isnot recommended. RunningDB2 productsonNFS(forexample,NFSmounting /opt/IBM/db2/V9.1 andthenrunningoffcodethatwasphysicallyinstalledona remote system)requiresseveralmanual setupsteps. Thereare alsoa numberof potentialissueswithsettingupNFSforDB2.Theseinclude possibleproblemsthat involve:

v Availability(youareallowing asinglepointoffailure)

v Licensing(thereisnocheckingdoneacrossmachines)

Asmentioned, thesetup forNFSwillrequireseveralmanualactionsincluding: v Ensuringthatthemountpointpreservetheinstall path

machines

mountingmachine

Related concepts:

v “SecurityissueswheninstallingtheDB2 databasemanager”inAdministration

Guide:Implementation

v “Modifyingkernelparameters(Linux)”inQuickBeginningsforDB2Servers

v “PreparingtoinstallDB2forLinuxonzSeries”inQuickBeginningsforDB2

Servers

v “Communicationsvariables”inPerformanceGuide

Installation

requirements

for

DB2

clients

and

servers

(Solaris

Operating

System)

(20)

Table4.SolarisOperatingSysteminstallationprerequisites

OperatingSystem Hardware

DB2clientandserverproductsaresupportedonthefollowing SolarisOperatingSystemversions:

v

Solaris9

Thefollowingpatchesarealsorequired: – 111711-12

– 111712-12 v Solaris10

SolarisUltraSPARC-basedcomputer

Kernelconfigurationconsiderations:

Asystem restartisrequiredifyouupdatethekernelconfigurationparameters.The kernelconfigurationparametersare setin/etc/systemand iftheseparameters require modificationtoaccommodate theDB2client,a rebootisnecessarytomake thechanges to/etc/systemeffective.Theseparametersmust beset beforeyou install aDB2 client.

v (Clientsonly:)IfyouplantouseKerberosAuthentication,yourequireSolaris

OperatingSystem9or higherwithIBMNetworkAuthenticationService(NAS) clientv1.4orhigher.TheNASclientcanbedownloadedfromWebsite:

https://www6.software.ibm.com/dl/dm/dm-nas-p.

v IfyouareinstallingtheApplicationDevelopmentClient,youmust havea C

compilertobuild SQLStoredProcedures.

Steps(db2fs):

″Recommended&SecurityPatches″ canbe obtainedfromthe

http://sunsolve.sun.comWebsite.From theSunSolveOnlineWebsite, clickonthe ″Patches″menuitemintheleftpanel.

The Java2Standard Edition(J2SE) SolarisOperatingSystemPatchClustersandthe SUNWlibCsoftwarearealso requiredand canbe obtainedfromthe

http://sunsolve.sun.comWebsite.

For DB2on64-bitFujitsu PRIMEPOWERsystems,yourequirethefollowing: v Solaris9 KernelUpdatePatch112233-01orlatertogetthefixforpatch

912041-01.

The FujitsuPRIMEPOWERpatchesfortheSolaris OperatingSystemcanbe downloadedfromFTSI at:http://download.ftsi.fujitsu.com/.

(21)

v Availability(youareallowing asinglepointoffailure)

v Licensing(thereisnocheckingdoneacrossmachines)

Asmentioned, thesetup forNFSwillrequireseveralmanualactionsincluding: v Ensuringthatthemountpointpreservetheinstall path

machines

mountingmachine

v “Modifyingkernelparameters(SolarisOperatingEnvironment)”inQuick

Installation

requirements

for

DB2

clients

and

servers

(Windows)

For themostup-to-dateprerequisiteinformation,see v www.ibm.com/software/data/db2/udb/sysreqs.html.

Toinstall aDB2 clientorserverproduct,thefollowingoperatingsystem,software, and hardwareprerequisites mustbe met:

(22)

Table5.Windowsinstallationprerequisites

OperatingSystem ServicePack Hardware

WindowsXPProfessional (32-bit)

WindowsXPProfessionalx64

ServicePack2or later

AllIntelandAMDprocessors capableofrunningthe supportedWindowsoperating systems(32-bitand64-bit) Windows2003StandardEdition

(32-bitand64-bit)

Windows2003AdvancedEdition (32-bitand64-bit)

Windows2003Datacenter Edition(32-bitand64-bit)

ServicePack1or later

Additionalclientconsiderations

DB2 productscontinuetobesupportedonWindows98,WindowsME, Windows NT®_,_and _Windows₂₀₀₀_until_DB2_Universal _Database_Version 8.1goesoutofservice.Forexistinginstallations andnewinstallations of DB2 products,upgradingtoa morecurrentoperatingsystemversionof Windows (suchasWindows 2003)isrecommended.Version9clients cannotbeinstalledonWindows98,WindowsME,orWindows NTServer 4 operatingsystems.IfyouwishtocontinuerunningDB2productson backlevelreleasesofWindows,yourequirethefollowingmaintenance levels:

v WindowsNTVersion4 withServicePack6aorlater

v Windows2000withServicePack4 orlater

v InternetExplorer4.01ServicePack2

Windows XPServicePack1 isrequiredforrunningDB2applicationsin either ofthefollowingenvironments:

v ApplicationsthathaveCOM+objectsusingODBC;or

v ApplicationsthatuseOLEDBProviderforODBCwithOLEDB

resourcepoolingdisabled

Ifyouareunsureaboutwhetheryourapplication environmentqualifies, then itisrecommendedthatyouinstalltheappropriateWindowsservice level.

For moreinformationaboutthisCOM+issue,seethefollowingMicrosoft® Knowledge Basearticle:

v http://support.microsoft.com/default.aspx?scid=KB;EN-US;306414 Additionalsoftwareconsiderations

v MDAC2.8isrequired.TheDB2Setupwizardwillinstall MDAC2.8ifit

isnotalreadyinstalled.

Note: Ifaprevious versionofMDAC(forexample,2.7)isalready

installed,DB2installwillupgradeMDACto2.8.For atypical install,MDAC2.8isinstalled.Fora compactinstall,MDAC2.8is notinstalled.For acustominstall, MDAC2.8isinstalledbutonly ifyouhavenotdeselected thedefaultwhichistoinstallit.Ifyou deselectMDACaspartofa custominstall,itwillnotbe installed.

(23)

v If youplantouseLDAP(Lightweight DirectoryAccessProtocol),you

shoulduseeitheraMicrosoft LDAPclientortheIBMTivoli®Directory Server v6client(alsoknown astheIBM LDAPclientwhichisincluded with DB2).PriortoinstallationoftheMicrosoftActiveDirectory,you willneedtoextendyourdirectory schemausingthedb2schexutility, whichcanbe foundontheinstallationmedia.

The MicrosoftLDAPclientisincludedwith theoperatingsystem for Windows XP,andWindowsServer 2003.

v If youplantousetheTivoliStorageManagerfacilitiesforthebackup

and restorationofyourdatabases,youneedtheTivoliStorageManager Client Version4.2.0orlater.

– For Windows64-bitoperatingsystems, yourequire theTSMclient

APIVersion5.1.

v If youhavetheIBMAntivirusprograminstalledonyour operating

system,it mustbe disabledoruninstalledtocompletea DB2installation. v TCP/IPsupportmust beenabled toviewonline help.

v Oneofthefollowingbrowsersisrequiredtoview onlinehelp,runthe

DB2 installlaunchpad(setup.exe),andtorunFirstSteps(db2fs): – Internet Explorer5.5and up

– Netscape7.0andup Related concepts:

v “Supportchangesfor32-bit and64-bit DB2servers”inMigrationGuide Related tasks:

v “Anoverview ofinstallingyourDB2product(Windows)”in QuickBeginningsfor

DB2Servers

DB2

Client

support

for

database

application

development

Thereare twoDB2clients:theDB2Client;and theDB2Runtime Client.Ofthe two, onlytheDB2Clientissuitablefordatabaseapplicationdevelopment. TheDB2 Clientincludesthefollowing:

v PrecompilersforC/C++,COBOL,andFortran,(providingthelanguageis

supportedforthatplatform).

v EmbeddedSQLapplicationsupport,includingprogramminglibraries,include

filesandcodesamples.

v ODBCandDB2CallLevel Interface(DB2CLI)applicationsupport,including

programminglibraries,includefiles,andcode samplesfor developing applicationswhichare easilyportedtoODBCandcompiledwith anODBC SDK.AnODBC SDKisavailable fromMicrosoftforWindows operating systems,andfromvariousothervendorsformanyof theothersupported platforms.OnWindowsoperatingsystems, theODBCandCLIdriverisinstalled

(24)

bydefaultwith theDB2 Client,supportingapplicationsdevelopedwiththe MicrosoftODBCSoftwareDeveloper’sKit.Forall otherplatforms,theODBC andCLIdrivercanoptionallybe installedwith theDB2Client,supporting applicationsthatcanbe developedwith anODBCSDKforthatplatform,ifone exists.

v TheIBMDB2DriverforJDBCandSQLJ,whichincludes:

– JDBC support,fordevelopingJavaapplicationswithdynamicSQL

– SQLJsupport,fordevelopingJavaapplicationswith staticSQL

v AnSDK forJavaisshippedwithDB2 clientsforallsupportedoperating

systems.

v ActiveXDataObjects(ADO)andObjectLinkingandEmbedding(OLE) automationroutines(UDFsandStoredProcedures)onWindows operating systems,includingcodesamplesimplementedinMicrosoftVisualBasicand MicrosoftVisualC++.Also, codesampleswithRemoteDataObjects(RDO) implementedinMicrosoftVisualBasic.

v ObjectLinkingandEmbeddingDatabase(OLEDB)tablefunctionson

Windowsoperatingsystems.

v C#andVisualBasic.NET applicationsandCLR.NET routinesonWindows

operatingsystems.

v DB2DeveloperWorkbenchisan Eclipse-basedtoolthatreplacesthe

DevelopmentCenterfromDB2Universal Database™_(DB2_UDB)_for_Linux, UNIX®,andWindowsVersion8.DeveloperWorkbenchisacomprehensive developmentenvironmentforcreating,editing,debugging,deploying, and testingDB2stored proceduresanduser-definedfunctions.Youcanalsouse DeveloperWorkbenchtodevelop SQLJapplications,andcreate,edit,andrun SQLstatementsandXMLqueries.

v InteractiveSQLthroughtheCommandEditororCommandLine Processor

(CLP)toprototype SQLstatementsortoperformadhocqueries againstthe database.

v AsetofdocumentedAPIstoenableotherapplication developmenttoolsto

implementprecompilersupport forDB2directlywithin theirproducts.For example,IBM COBOLonAIXusesthisinterface.

v “Typesofclients-DB2 RuntimeClientandDB2Client”inQuickBeginningsfor

DB2Clients

v “Supportforelementsofthedatabaseapplication developmentenvironment”on

page5

Supported

database

application

programming

interfaces

Supported

database

application

programming

interfaces

Youcanuseseveraldifferentprogramming interfacestomanage oraccessDB2 databases. Youcan:

v UseDB2APIsto performadministrativefunctionssuchasbackingupand

restoringdatabases.

(25)

v CodeDB2CallLevelInterface(DB2CLI)functioncalls inyourapplicationsto

invokedynamicSQLstatements.

v DevelopJavaapplicationsandappletsthatcalltheJavaDatabaseConnectivity

applicationprogramminginterface (JDBCAPI).

v DevelopMicrosoftVisualBasicand VisualC++applicationsthatconformto

DataAccessObject(DAO)and RemoteData Object(RDO)specifications,and ActiveXDataObject(ADO)applicationsthatusetheOLEDBBridge.

v DevelopADO.NETapplicationsusingDB2.NET DataProvider,OLEDB.NET

DataProviderorODBC.NET DataProvider.

v DevelopapplicationsusingIBMorthird-party toolssuchasNet.Data

®_,_Excel, Perl,andOpenDatabaseConnectivity (ODBC)end-usertoolssuchasLotus® Approach®_,_and_its_programming_language,_LotusScript.

v “OLEDB.NETData Provider”onpage21

v “ADO.NETdevelopmentforDB2databases”onpage19

v “DB2.NET DataProvider” onpage19

v “ODBC.NET DataProvider”onpage20

v “IBMOLEDBProviderforDB2”onpage26

v “IntroductiontoDB2 CLIand ODBC”onpage21

v “Introductiontoembedded SQL”onpage23

v “Supporteddrivers forJDBCandSQLJ”onpage24

v “PerlDBI”onpage27

v “IntroductiontoPHPapplicationdevelopment forDB2”onpage27 Related reference:

page5

ADO.NET

development

for

DB2

databases

ADO.NET

development

for

DB2

databases

TheDB2 .NETData Providerisanextensionof theADO.NETinterface thatallows .NET applicationstoaccessa DB2databasethrough asecureconnection,execute commands,andretrieveresult sets.

ReferencedocumentationisincludedwiththeDB2.NET DataProvider,presenting detailedinformationaboutalltheDB2.NETData Providerobjectsandtheir members.DuringtheDB2installationprocess,this documentationisregistered with MicrosoftVisualStudio.NET.Toview theDB2 .NETDataProvider documentationfromMicrosoftVisualStudio.NET,selecttheHelpmenuoption, and Contents.Once thehelp vieweropens,filterbyIBM DB2.NETDataProvider Help.

DB2

.NET

Data

Provider

TheDB2 .NETData Providerextends DB2supportfortheADO.NET interface.The DB2 .NETData Providerdelivershigh-performing,secureaccesstoDB2 data. TheDB2 .NETData Providerallows your.NETapplicationstoaccessthefollowing database managementsystems:

(26)

v DB2UniversalDatabaseVersion8forWindows,UNIX,andLinux-based

computers

v DB2UniversalDatabaseVersion6(orlater)forOS/390

®_and _z/OS®_,_through DB2Connect™

v DB2UniversalDatabaseVersion5,Release1(orlater)forAS/400

® _and_iSeries, throughDB2Connect

v DB2UniversalDatabaseVersion7.3(orlater)forVSE&VM,through DB2

Connect

Todevelop andrunapplicationsthatuseDB2.NET DataProvideryouneed the .NET Framework,Version2.0or1.1.

InadditiontotheDB2.NET DataProvider,theIBMDatabaseDevelopment Add-Inenablesyoutoquickly andeasilydevelop.NET applicationsforDB2 databasesinVisualStudio2005.YoucanalsousetheAdd-Intocreatedatabase objectssuchasindexesandtables,anddevelop server-sideobjects,suchasstored proceduresand user-definedfunctions.

ODBC

.NET

Data

Provider

The ODBC.NETDataProvidermakesODBCcallstoa DB2datasourceusingthe DB2 CLIDriver.Therefore,theconnectionstringkeywordssupportedbythe ODBC .NETDataProviderare thesame asthose supportedbytheDB2CLIdriver. Also, theODBC .NETDataProviderhasthesamerestrictionsastheDB2CLI driver.Thereareadditionalrestrictions fortheODBC.NET DataProvider,which are identifiedinthetopic:ODBC.NETData Providerrestrictions.

InordertousetheODBC.NETData Provider,youmusthavethe.NET

FrameworkVersion1.1orVersion2.0installed.ForDB2 UniversalDatabasefor AS/400and iSeries,thefollowingfixisrequiredontheserver:APARII13348. The followingare thesupportedconnectionkeywordsfortheODBC.NETData Provider:

Table6.ConnectionStringkeywordsfortheODBC.NETDataProvider

Keyword Value Meaning

DSN databasealias TheDB2databasealiasas

catalogedinthedatabase directory

UID userID TheuserIDusedtoconnect

totheDB2server

PWD password ThepasswordfortheuserID

usedtoconnecttotheDB2 server

The followingisanexampleofcreating anOdbcConnectiontoconnecttothe SAMPLEdatabase:

[Visual Basic .NET]

Dim con As New OdbcConnection("DSN=sample;UID=userid;PWD=password;") con.Open()

[C#]

OdbcConnection con = new OdbcConnection("DSN=sample;UID=userid;PWD=password;"); con.Open()

(27)

OLE

DB

.NET

Data

Provider

TheOLEDB.NET DataProviderusestheIBMDB2OLEDBDriver,whichis referredtoina ConnectionStringobjectasIBMDADB2.Theconnectionstring keywordssupportedbytheOLEDB.NETData Providerare thesame asthose supportedbytheIBM OLEDBProviderforDB2.Also,theOLEDB.NET Data ProviderhasthesamerestrictionsastheIBMDB2OLEDBProvider.Thereare additionalrestrictions fortheOLEDB.NET DataProvider,whichareidentifiedin thetopic:OLEDB.NETData Providerrestrictions.

InordertousetheOLE DB.NET DataProvider,youmust havethe.NET FrameworkVersion1.1orVersion2.0installed.

For DB2UniversalDatabaseforAS/400andiSeries,thefollowingfixisrequired ontheserver:APARii13348.

Thefollowingare allthesupportedconnectionkeywordsfortheOLEDB.NET Data Provider:

Table7.ConnectionStringkeywordsfortheOLEDB.NETDataProvider

Keyword Value Meaning

PROVIDER IBMDADB2 SpecifiestheIBMOLEDB

ProviderforDB2(required) DSN orDataSource databasealias TheDB2databasealiasas

catalogedinthedatabase directory

UID userID TheuserIDusedtoconnect

totheDB2server

PWD password ThepasswordfortheuserID

usedtoconnecttotheDB2 server

Thefollowingisanexampleofcreating anOleDbConnectiontoconnecttothe SAMPLEdatabase:

[Visual Basic .NET]

Dim con As New OleDbConnection("Provider=IBMDADB2;" + "Data Source=sample;UID=userid;PWD=password;") con.Open()

[C#]

OleDbConnection con = new OleDbConnection("Provider=IBMDADB2;" + "Data Source=sample;UID=userid;PWD=password;" ); con.Open()

Introduction

to

DB2

CLI

and

ODBC

DB2 CallLevel Interface(DB2CLI)isIBM’scallableSQLinterface totheDB2 familyofdatabase servers.Itisa ’C’and’C++’applicationprogramming interface forrelationaldatabaseaccessthatusesfunctioncallstopass dynamicSQL

statementsasfunction arguments.ItisanalternativetoembeddeddynamicSQL, but unlikeembeddedSQL,DB2 CLIdoesnotrequirehostvariablesora

precompiler.

DB2 CLIisbasedontheMicrosoft**OpenDatabaseConnectivity**(ODBC) specification,and theInternationalStandardfor SQL/CLI.Thesespecifications were chosenasthebasisfortheDB2CallLevelInterfaceinanefforttofollow

(28)

industrystandardsand toprovideashorterlearningcurveforthose application programmers alreadyfamiliar witheitherofthese databaseinterfaces.Inaddition, someDB2 specificextensionshavebeen addedtohelptheapplicationprogrammer specificallyexploitDB2features.

The DB2CLIdriveralso actsasanODBCdriverwhenloaded byanODBCdriver manager.ItconformstoODBC 3.51.

DB2CLIBackgroundinformation:

TounderstandDB2CLIoranycallable SQLinterface,itishelpfultounderstand whatitisbasedon,andtocompare itwithexistinginterfaces.

The X/OpenCompanyandtheSQLAccessGroupjointly developeda specification for acallableSQLinterface referredtoastheX/OpenCallLevelInterface.Thegoal of this interfaceistoincreasetheportabilityofapplicationsbyenabling themto becomeindependentofanyonedatabasevendor’sprogramming interface.Mostof theX/OpenCallLevelInterfacespecificationhasbeenacceptedaspartoftheISO CallLevel InterfaceInternationalStandard (ISO/IEC9075-3:1995SQL/CLI). Microsoft developedacallableSQLinterface calledOpenDatabaseConnectivity (ODBC) forMicrosoftoperatingsystemsbased onapreliminarydraftofX/Open CLI.

The ODBCspecificationalsoincludesanoperatingenvironmentwheredatabase specific ODBCDriversaredynamicallyloaded atruntimebyadrivermanager based onthedatasource(database name)providedontheconnectrequest.The application islinkeddirectlytoa singledrivermanagerlibraryratherthantoeach DBMS’s library.Thedrivermanager mediatestheapplication’sfunctioncalls atrun timeandensures theyaredirectedtotheappropriateDBMSspecificODBCdriver. SincetheODBCdrivermanageronlyknowsabouttheODBC-specificfunctions, DBMS-specific functionscannotbeaccessedinan ODBCenvironment.

DBMS-specific dynamicSQLstatementsaresupportedviaamechanismcalledan escapeclause.

ODBC isnotlimitedtoMicrosoft operatingsystems;otherimplementationsare available onvariousplatforms.

The DB2CLIload librarycanbeloaded asanODBCdriverbyan ODBCdriver manager.For ODBCapplicationdevelopment,youmustobtainanODBCSoftware DevelopmentKit.For theWindows platform,theODBCSDKisavailable aspart of theMicrosoft DataAccessComponents(MDAC)SDK,available fordownloadfrom http://www.microsoft.com/data/. Fornon-Windowsplatforms,theODBCSDKis provided byothervendors.WhendevelopingODBCapplicationsthatmayconnect toDB2 servers,usetheCallLevelInterfaceGuideandReference, Volume1and theCallLevelInterfaceGuideand Reference,Volume2 (forinformationonDB2 specific extensionsanddiagnosticinformation),inconjunctionwith theODBC Programmer’sReferenceandSDKGuideavailable fromMicrosoft.

Applicationswritten directlytoDB2CLIlinkdirectlyto theDB2CLIloadlibrary. DB2 CLIincludessupportfor manyODBCand ISOSQL/CLIfunctions,aswell as DB2 specificfunctions.

The followingDB2 featuresare availabletobothODBCandDB2 CLIapplications: v doublebyte(graphic) datatypes

(29)

v storedprocedures

v DistributedUnitofWork(DUOW),twophasecommit

v compoundSQL

v userdefinedtypes(UDT)

v userdefinedfunctions(UDF) Related concepts:

v “ComparisonofDB2CLIandMicrosoft ODBC”inCallLevelInterfaceGuideand

Reference,Volume1

v “DB2CallLevelInterface(CLI)versusembedded SQL”onpage30

Introduction

to

embedded

SQL

Embedded SQLdatabaseapplicationsconnecttodatabasesandexecuteembedded SQLstatements.Embedded SQLstatementsareembedded withina hostlanguage application. EmbeddedSQLdatabaseapplicationssupporttheembeddingofSQL statementsto beexecutedstaticallyordynamically.

Youcandevelopembedded SQLapplicationsforDB2inthefollowinghost programming languages:C,C++,COBOL, FORTRAN,andREXX.

Note: SupportforembeddedSQLinFORTRANand REXXhasbeendeprecated

andwillremainat theDB2 UniversalDatabase,Version5.2level. Building embeddedSQLapplicationsinvolvestwoprerequisitestepspriorto application compilationandlinking.

v Preparingthesourcefilescontainingembedded SQLstatementsusingtheDB2

precompiler.

ThePREP(PRECOMPILE)commandisusedtoinvoketheDB2 precompiler,which readsyour sourcecode,parsesandconvertstheembedded SQLstatementsto DB2run-timeservicesAPIcalls,and finallywritestheoutputtoa newmodified sourcefile.TheprecompilerproducesaccessplansfortheSQLstatements,which arestoredtogetherasa packagewithin thedatabase.

v Bindingthestatementsintheapplicationtothetargetdatabase.

Bindingisdonebydefaultduringprecompilation(thePREPcommand).If bindingistobe deferred(forexample,runningtheBINDcommandlater),then theBINDFILEoptionneedstobespecified atPREPtimein orderforabind fileto begenerated.

Once youhaveprecompiledandboundyour embeddedSQLapplication, itis readytobe compiledandlinkedusingthehostlanguage-specificdevelopment tools.

Toaidinthedevelopment ofembeddedSQLapplications,youcanrefertothe embedded SQLtemplateinC. Examplesofworkingembedded SQLsample applicationscanalsobe foundinthe%DB2PATH%\SQLLIB\samplesdirectory.

Note: %DB2PATH%referstotheDB2installationdirectory Static anddynamicSQL:

(30)

StaticallyexecutedSQLstatements

For staticallyexecutedSQLstatements,thesyntax isfullyknownat precompiletime.Thestructure ofanSQLstatementmustbe completely specifiedfor astatementtobeconsideredstatic.Forexample,thenames forthecolumns andtablesreferenced inastatementmust befullyknown at precompiletime. Theonlyinformationthatcanbespecified atruntime are valuesforanyhostvariablesreferencedbythestatement.However, hostvariable information,suchasdatatypes, muststillbe precompiled. Youprecompile, bind,andcompilestaticallyexecutedSQLstatements before yourunyourapplication.StaticSQLisbestusedondatabases whosestatisticsdonotchangeagreat deal.

Dynamically executedSQLstatements

DynamicallyexecutedSQLstatementsare builtand executedbyan application atrun-time.An interactiveapplicationthatpromptstheend user forkeypartsofan SQLstatement,suchasthenamesofthetablesand columns tobesearched,isagoodexampleofa situationsuitedfor

dynamicSQL.

v “EmbeddedSQLstatementsinREXXapplications”inDevelopingEmbeddedSQL

Applications

v “EmbeddedSQLstatementsinCand C++applications” inDevelopingEmbedded

SQLApplications

v “EmbeddedSQLstatementsinCOBOLapplications”inDevelopingEmbeddedSQL

Applications

v “EmbeddedSQLstatementsinFORTRANapplications”inDevelopingEmbedded

SQLApplications

v “PRECOMPILEcommand”inCommand Reference

Supported

drivers

for

JDBC

and

SQLJ

According totheJDBCspecification,therearefour typesofJDBC driver architectures:

Type1

DriversthatimplementtheJDBCAPIasa mappingtoanotherdataaccessAPI, suchasOpenDatabaseConnectivity(ODBC).Driversofthistype aregenerally dependentona nativelibrary,whichlimitstheirportability.TheDB2database system doesnotsupport atype1 driver.

Type2

Driversthatarewritten partlyintheJava programminglanguageandpartlyin nativecode.Thedrivers usea nativeclientlibraryspecific tothedatasourceto whichtheyconnect.Becauseofthenativecode,theirportabilityislimited.

Type3

DriversthatuseapureJavaclientand communicatewith aserverusinga database-independent protocol.Theserverthencommunicatestheclient’s requeststothedatasource.TheDB2databasesystem doesnotsupporta type 3 driver.

Type4

DriversthatarepureJava andimplementthenetworkprotocolfora specific datasource.Theclientconnectsdirectlytothedatasource.

(31)

DB2 Version9.1supportsadriverthatcombinestype2 andtype4 JDBC implementations.DB2Version9.1alsosupportsa.type2 driver,althoughthis support isdeprecated. TheDB2Version9.1type 2drivercontinuestousetheDB2 CLIinterfaceto communicatewithDB2 databaseservers.Thedriversthatare supportedinDB2Version9.1are:

DB2JDBCType2DriverforLinux,UNIXandWindows(DB2JDBCtype2 driver)(deprecated):

TheDB2 JDBCtype2 driverletsJava applicationsmakecallstoDB2through JDBC.Calls totheDB2 JDBCtype2 driveraretranslatedtoJava nativemethods. TheJava applicationsthatusethisdrivermust runona DB2client,throughwhich JDBC requestsflowtotheDB2 server.DB2ConnectVersion9.1mustbe installed before theDB2JDBCapplicationdrivercanbe usedtoaccessDB2UDB foriSeries datasources ordatasourcesintheDB2forOS/390orz/OSenvironments.

TheDB2 JDBCtype2 driversupportsthese JDBCandSQLJfunctions:

v MostofthemethodsthataredescribedintheJDBC1.2specification,andsome

ofthemethodsthataredescribedin theJDBC 2.0specification.SeeComparison ofdriversupportforJDBCAPIs.

v SQLJstatementsthatperform equivalentfunctionstoall JDBCmethods

v Connectionpooling

v Distributedtransactions

v Javauser-definedfunctionsandstoredprocedures

TheDB2 JDBCType2 DriverforLinux,UNIXandWindows willnotbesupported infuturereleasesoftheDB2databasesystem.Youshouldthereforeconsider moving totheIBM DB2DriverforJDBC andSQLJ.

IBMDB2DriverforJDBCandSQLJ(type2 andtype4):

TheIBM DB2DriverforJDBC andSQLJisa singledriverthatincludesJDBCtype 2 andJDBC type4behavior,aswell asSQLJsupport.Whenanapplicationloads theIBMDB2 DriverforJDBCand SQLJ,a singledriverinstanceisloadedfortype 2 andtype 4implementations.Theapplicationcanmaketype 2andtype 4

connectionsusingthissingledriverinstance.Thetype 2andtype 4connections canbe madeconcurrently.IBMDB2Driver forJDBCandSQLJtype 2driver behaviorisreferredtoasIBM DB2DriverforJDBCandSQLJ type2 connectivity.

IBM DB2DriverforJDBC andSQLJtype4 driverbehaviorisreferredto asIBM DB2 DriverforJDBCandSQLJtype4 connectivity.

TheIBM DB2DriverforJDBC andSQLJsupportsthese JDBCandSQLJfunctions: v Allofthemethodsthatare describedintheJDBC3.0specifications.See

ComparisonofdriversupportforJDBCAPIs.

v SQLJstatementsthatperform equivalentfunctionstomostJDBCmethods.

v Connectionsthatareenabled forconnectionpooling.WebSphere

®_Application Serveroranotherapplication serverdoestheconnectionpooling.

v Javauser-definedfunctionsandstoredprocedures(IBM DB2DriverforJDBC

andSQLJtype2connectivity only).

v GlobaltransactionsthatrununderWebSphereApplicationServerVersion5.0

andabove.

v Supportfordistributed transactionmanagement.Thissupportimplements the

Java2Platform,EnterpriseEdition(J2EE)Java TransactionService(JTS)andJava TransactionAPI(JTA)specifications,whichconformtotheX/Openstandardfor distributedtransactions(DistributedTransactionProcessing:TheXASpecification,

(32)

v “HowJDBC applicationsconnecttoa datasource”inDevelopingJavaApplications

v “SecurityundertheIBMDB2Driver forJDBCand SQLJ”inDevelopingJava

Applications

v “DriversupportforJDBCAPIs”inDevelopingJavaApplications

v “JDBCdifferencesbetweentheIBMDB2 DriverforJDBCand SQLJand other

DB2JDBCdrivers”inDevelopingJavaApplications

v “SQLJdifferencesbetweentheIBM DB2DriverforJDBC andSQLJandother

DB2JDBCdrivers”inDevelopingJavaApplications

IBM

OLE

DB

Provider

for

DB2

Microsoft OLEDBisa setofOLE/COMinterfacesthatprovides applicationswith uniform accesstodatastored indiverseinformationsources.TheOLEDB

architecture definesOLEDBconsumersandOLEDBproviders.An OLEDB consumer isanysystemorapplication thatusesOLEDBinterfaces;an OLEDB providerisacomponentthatexposesOLEDBinterfaces.

The IBMOLEDBProviderfor DB2allowsDB2toactasaresourcemanagerfor theOLEDBprovider.ThissupportgivesOLEDB-basedapplicationstheabilityto extractorqueryDB2datausingtheOLEinterface.TheIBMOLEDBProviderfor DB2,whoseprovidernameisIBMDADB2,enablesOLEDBconsumerstoaccess dataona DB2databaseserver.IfDB2Connectisinstalled,theseOLEDB consumerscanalsoaccessdataonahostDBMSsuchasDB2 forMVS™_,_DB2_for VM/VSE,orSQL/400®_.

The IBMOLEDBProviderfor DB2offersthefollowingfeatures:

v Supportlevel0of theOLEDBproviderspecification,includingsomeadditional

level1 interfaces.

v Afreethreaded providerimplementation,whichenablestheapplicationto

createcomponentsinonethreadandusethosecomponentsinanyotherthread. v AnErrorLookupServicethatreturnsDB2errormessages.

NotethattheIBMOLEDBProviderresidesontheclient andisdifferentfromthe OLEDBtablefunctions,whicharealsosupportedbyDB2databasesystems. Subsequentsectionsofthisdocumentdescribethespecificimplementationofthe IBM OLEDBProviderforDB2.For moreinformationontheMicrosoftOLEDB2.0 specification,refertotheMicrosoftOLEDB2.0Programmer’sReferenceandData AccessSDK,available fromMicrosoftPress.

Version Compliance:

The IBMOLEDBProviderfor DB2complieswithVersion2.7oftheMicrosoftOLE DBspecification.

SystemRequirements:

Refer totheannouncementletterfortheIBMOLE DBProviderforDB2Serversto seethesupportedWindows operatingsystems.

(33)

Toinstall theIBMOLEDBProviderforDB2,youmustfirst berunningononeof thesupportedoperatingsystemslisted above.Youalso needtoinstalltheDB2 Client.ThisclientincludesMicrosoftDataAccessComponents(MDAC).

v “IBMOLEDBProvidersupportforOLEDBcomponentsandinterfaces” in

DevelopingADO.NETand OLEDBApplications

Perl

DBI

DB2 supportsthePerlDatabaseInterface(DBI)specificationfordataaccess through theDBD::DB2driver.TheDB2Perl DBIwebsiteislocatedat:

http://www.ibm.com/software/data/db2/perl/

and containsthelatestDBD::DB2driver,and relatedinformation.

Perl isaninterpretedlanguageandthePerlDBIModuleusesdynamicSQL.These propertiesmakePerl anideallanguageforquickly creatingandrevisingDB2 applications.ThePerlDBIModuleusesaninterface thatisquitesimilartotheCLI and JDBCinterfaces,whichmakesiteasytoportPerl applicationstoCLIand JDBC,or toportCLIand JDBCapplicationstoPerl.

v “ProgrammingConsiderations forPerl”inDevelopingPerlandPHPApplications

Introduction

to

PHP

application

development

for

DB2

PHP: HypertextPreprocessor(PHP)isaninterpretedprogramminglanguage primarilyintended forthedevelopment ofWebapplications.Thefirst versionof PHPwascreatedbyRasmusLerdorfand contributedunderanopensourcelicense in1995.PHPwas initiallyaverysimple HTMLtemplatingengine,but overtime thedevelopersofPHPaddeddatabase accessfunctionality,rewrotetheinterpreter, introducedobject-orientedsupport,and improvedperformance.Today,PHPhas becomea popularlanguageforWebapplication developmentbecauseofitsfocus onpracticalsolutionsand supportforthemostcommonlyrequiredfunctionalityin Webapplications.

For theeasiestinstall andconfigurationexperienceonLinux,UNIX,orWindows operatingsystems, youcandownloadandinstall ZendCoreforIBMfor usein productionsystems.Paidsupport forZendCoreforIBM isavailable fromZend. On Windows,precompiledbinaryversionsofPHPareavailablefordownload fromhttp://php.net/. MostLinuxdistributionsincludea precompiledversionof PHP. OnUNIXoperatingsystemsthatdonotincludea precompiledversionof PHP, youcancompileyourown versionofPHP.

PHPisa modularlanguagethatenablesyoutocustomizetheavailable

functionalitythrough theuseofextensions.Theseextensionscansimplifytasks suchasreading,writing,and manipulatingXML,creating SOAPclientsand servers,and encryptingcommunicationsbetweenserverand browser.Themost popularextensionsforPHP,however,providereadand writeaccesstodatabases sothatyoucaneasilycreatea dynamicdatabase-drivenWebsite. IBMsupports accesstoDB2databasesfromPHPapplicationsthroughtwoextensionsoffering distinct setsof features:

v ibm_db2isanextensionwritten,maintained,and supportedbyIBMforaccess

(34)

programminginterface(API)that,inadditiontothenormalcreate,read,update, andwritedatabaseoperations,alsooffers extensiveaccesstothedatabase metadata.Youcancompiletheibm_db2extension witheitherPHP4 orPHP5. v PDO_ODBCisa driverforthePHPDataObjects(PDO)extensionthatoffers

accesstoDB2 databasesthrough thestandardobject-orienteddatabaseinterface introducedinPHP5.1.Despite itsname, youcancompilethePDO_ODBC extensiondirectlyagainsttheDB2librariestoavoidthecommunications overheadandpotentialinterferenceof anODBCdrivermanager.

Athirdextension,UnifiedODBC,hashistoricallyofferedaccesstoDB2database systems. Itisnotrecommended thatyouwritenewapplicationswiththis extension becauseibm_db2andPDO_ODBCbothoffersignificantperformance and stabilitybenefitsoverUnifiedODBC.Theibm_db2extensionAPImakes porting anapplicationthatwas previouslywritten forUnifiedODBCalmost as easy asgloballychangingtheodbc_ functionnametodb2_throughoutthesource code ofyourapplication.

v “SettingupthePHPenvironmentonLinuxorUNIX”inDevelopingPerlandPHP

Applications

v “SettingupthePHPenvironmentonWindows”inDevelopingPerland PHP

Applications

Choosing

an

application

programming

interface

Choosing

an

application

programming

interface

Which applicationprogramming interface(API)youchoosetousewithyour DB2 database applicationshasalargeimpactonthedevelopmentoftheapplication.

Procedure:

Choosing anAPIisacombinationofeliminatingtheAPIsthatare unsuitable,and then selectingthemostappropriatefromtheAPIsthatremain.

WhileresearchingtheavailableAPIsyoumight discoverfunctionalitythatyoudid notknowabout.Becauseof this,youmight actuallychooseanAPIthatrequires youtochangeyourenvironment andapplicationdesign.

Tochoose anAPItoaccessaDB2database,do thefollowing: 1. EliminateAPIsthatdon’tsupport thefunctionalityyourequire.

v ApplicationenvironmentchoicesyouhavealreadymaderestrictyourAPI

choices.

Theremaybe restrictionsonwhichAPIsyoucanusebecauseofoperating system,programming language,orotherchoicesyouhavealreadymade aboutyour applicationenvironment.

Before choosinganAPI:listknownenvironment details,readtherestrictions fortheAPIsyouareconsideringusing,andeliminatethoseAPIsthatare not supportedwithyour environment.

v AllAPIsmight notsupport thefunctionalityyour applicationsrequire.

Theremaybe somefunctionalitythatyouwantforyour applicationsthat someAPIsdon’tsupport,ordon’tsupportwell.

(35)

Before choosinganAPI:listfunctionalitythatyouknowyouneed inyour applications,readthesupportedfunctionalityof theAPIsyouare

consideringusing,andeliminatethoseAPIsthatdo notsupport the functionalityyouneed.

2. OftheAPIsthathavenotbeeneliminated,compare desirablequalities.

v SomeAPIsmight supportdesirablefunctionalitythatotherAPIsdo not

support.

Even ifmorethanoneAPIsupport thefunctionalityyouknowyour

applicationsrequire,oneAPImight supportfunctionalityyouare interested tolearnmoreaboutandthatyoumightuseinthefuture.

Before choosinganAPI:listandcompare thedesirable functionality supportedbytheAPIs.

v SomeAPIsareeasier tolearnandsimplertousethanothers.

IfmanyAPIs satisfyyour basicrequirements,choosing anAPIthatiseasier foryour developerstolearnand tousewillsaveyoudevelopmenttimeand money.

Before choosinganAPI:learnaboutthearchitectureand designoftheAPIs, read thesupportingdocumentation,and chooseanAPIthatisintuitiveto useandwell documented.

v Youmight alreadybe usinganAPIwithexistingapplications.

Ifyouhaveexistingapplications,itmaysave youtimeormoneytousethe sameAPIsthattheexistingapplicationsuse. Thisisbecause,amongother things:

– yourdevelopersare familiarwiththeAPIstheyhaveusedbefore;

– youmightbeable toreusecodefromexistingapplications;and

– yourdevelopment andproductionenvironmentswould alreadybe

configuredtousethoseAPIs,soadditionalinstallationorconfiguration mightnotbe requiredtodevelop ordeploynewapplicationsusingthe sameAPIs.

Before choosinganAPI:investigatewhatAPIsarebeingusedbyexisting applications.

3. Consider changingyourrequirementsifanAPIisoutstanding.

Insteadofchoosing yourAPIbased onenvironmentandapplication

requirements,youmightchoosetoalteryourenvironment andapplication designtosuitanAPI.

WhilecomparingtherelativemeritsofdifferentAPIs,youmightdiscover thatoneAPIsupportsfunctionalitythattheotherAPIsdon’tsupport and thatissodesirableyouwould choosetousethatAPIsolelybecauseofthat functionality.Ifyourenvironmentdoesn’tsupportthatAPI, youmight choosetochangeyourenvironment.Ifyour applicationsdon’tevenrequire thatfunctionality,youmightchoose toredesignyour applicationstomake useofthefunctionality.

Thereisriskinmakingchanges toyourenvironmentand applicationdesign forthesake ofanAPI. However,theprocessofreviewingthefunctionality ofthedifferentAPIs,and theprocessofconsideringyourrequirements mightresultinan improveddatabaseapplication.

BeforeeliminatinganAPIbecauseofenvironmentor application

restrictions,considerthemeritsofchangingyour requirementsand using theAPI.

(36)

v “ApplicationTypesSupportedbytheIBMOLEDBProviderforDB2”in

DevelopingADO.NETand OLEDBApplications

v “DB2.NET DataProviderdatabasesystem requirements”inDeveloping

ADO.NETand OLEDBApplications

v “IBMOLE DBProviderrestrictions”inDevelopingADO.NETandOLEDB

Applications

v “LimitationsforADOapplications” inDevelopingADO.NETandOLEDB

Applications

v “HowtodeterminewhichtypeofIBM DB2DriverforJDBC andSQLJ

connectivitytouse”inDevelopingJavaApplications

v “Javaappletconsiderations”inDevelopingJavaApplications

v “DeterminingwhentoexecuteSQLstatementsstaticallyordynamicallyin

embeddedSQLapplications” inDevelopingEmbeddedSQLApplications

v “RestrictionsonembeddedSQLapplications” inDevelopingEmbeddedSQL

Applications

v “ComparisonofDB2CLIand MicrosoftODBC”inCallLevelInterfaceGuideand

Reference,Volume1

v “Considerationsfor mixingembedded SQLandDB2CLI” inCallLevelInterface

Guideand Reference,Volume1

v “DB2CallLevelInterface(CLI)versusembedded SQL”onpage30 Related reference:

v “OLEDB.NETData Providerrestrictions”inDevelopingADO.NETand OLEDB

Applications

v “DriversupportforJDBCAPIs”inDevelopingJavaApplications

v “Recommendationsfordevelopingembedded SQLapplicationswithXMLand

XQuery”inDevelopingEmbeddedSQLApplications

v “Supportforelementsofthedatabaseapplication developmentenvironment”on

page5

DB2

Call

Level

Interface

(CLI)

versus

embedded

SQL

Two importantdifferencesbetweenembedded SQLandDB2 CLIare: 1. themethodforbuildingapplications;

Tobuildanembeddedapplication:

a. connecttothetargetdatabase(s)andprecompiletheembeddedSQL;

b. compiletheapplicationcode;and

c. bind theapplicationtothetargetdatabase(s).

Afterthis,youcanruntheapplicationagainstthetarget database(s).

TobuildaCLIapplication: a. compiletheapplicationcode.

Afterthis,youcanruntheapplicationagainstanyDB2database to

whichthebindfilesshippedwithCLIhavebeenbound.

Becauseofthisdifference,deployingCLIapplicationsiseasier than

deployingembedded SQLapplications,particularlyinenvironmentswhere thetarget databasesare notknown atbuildtimeorwherethetarget databasesmight change.