Thefollowingtopics containinformationonbuildingJDBCand SQLJapplications. v “BuildingJDBC applets”
v “BuildingJDBC applications”onpage158
v “BuildingJDBC routines”onpage158
v “BuildingSQLJapplets”onpage160
v “BuildingSQLJapplications”onpage162
v “Javaappletconsiderations”onpage163
v “SQLJapplicationand appletoptionsforUNIX”onpage164
v “SQLJapplicationand appletoptionsforWindows”onpage164
v “BuildingSQLJroutines”onpage165
v “SQLJroutineoptionsforUNIX”onpage166
v “SQLJroutineoptionsforWindows”onpage167
Building
JDBC
applets
Applt demonstratesa dynamicSQLJavaapplettoaccessaDB2database server.
Procedure:
Tobuild andruntheJDBCapplet,Applt,bycommandsenteredat thecommand line, eitherensurethatawebserverisinstalledandrunningonyour DB2database (serverorclient),orusetheappletviewerthatcomeswith thesoftware
development kitforJava byentering thefollowingcommandintheworking directory ofyourclientmachine:
appletviewer Applt.html
Connecting withtheIBMDB2 DriverforJDBCandSQLJ:Toconnectwith theIBM DB2 DriverforJDBCand SQLJ,modifytheApplt.htmlfileaccordingtothe instructions inthefile.FortheTCP/IPportnumber,youcanusethedatabase port number,″50000″.
Building theapplet:
1. CompileApplt.javatoproducethefileApplt.classwith thiscommand:
javac Applt.java
Alternatively,youcanusetheJava makefiletobuildthisprogram.
2. Ensurethatyourworking directoryisaccessiblebyyourwebbrowser.Ifitis
not,copyApplt.classandApplt.htmlintoa directorythatisaccessible. 3. Copysqllib\java\db2jcc.jaronWindowsorsqllib/java/db2jcc.jaron
UNIX,intothesamedirectoryasApplt.classand Applt.html. 4. On yourclientmachine,start yourwebbrowserandloadApplt.html.
Related concepts:
v “Javaappletconsiderations”onpage163
Related tasks:
v “BuildingJDBC applications”onpage158
v “BuildingSQLJapplets”onpage160
Related reference:
v “JDBCsamples” onpage169
Related samples:
v “Applt.java--AJavaappletthatuseJDBCappletdrivertoaccessa database
(JDBC)”
Building
JDBC
applications
DbInfodemonstrates adynamicSQLJava applicationaccessinga DB2database server.
Procedure:
Tobuild andrunthis applicationbycommandsenteredat thecommandline: 1. CompileDbInfo.javatoproducethefileDbInfo.classwith thiscommand:
javac DbInfo.java
2. RuntheJavainterpreterontheapplication withthiscommand:
java DbInfo
YoucanalsousetheJavamakefiletobuildthisprogram.
IfyouarerunningaJava applicationonUNIXina 64-bitDB2instance butthe software developmentkitforJavais32-bit,youneed tochangetheDB2 library path beforerunningtheapplication.For example,onAIX:
v IfusingbashorKornshell:
export LIBPATH=$HOME/sqllib/lib32
v IfusingCshell:
setenv LIBPATH $HOME/sqllib/lib32 Related tasks:
v “BuildingJDBCapplets”onpage157
v “BuildingJDBCroutines”onpage158
v “BuildingSQLJapplications” onpage162
Related reference:
v “JDBCsamples” onpage169
Related samples:
v “DbInfo.java--Howtoget/setinfo inadatabase(JDBC)”
Building
JDBC
routines
DB2 providessampleprograms demonstratingJDBCroutines(stored procedures and user-definedfunctions)inthesamples/java/jdbcdirectory onUNIX,and the samples\java\jdbc directoryonWindows.Routinesare compiledandstoredona server.Whencalledbya clientapplication,theyaccesstheserverdatabase and return informationtotheclient application.
Procedure:
Thefollowingexamples showyouhowtobuildroutines comprising: v storedprocedures
v user-definedfunctionswithoutSQLstatements
v user-definedfunctionswith SQLstatements
StoredProcedures
SpServerdemonstrates dynamicSQLPARAMETERSTYLE JAVAstored procedures.
Tobuild andrunthis programontheserverfromthecommandline:
1. CompileSpServer.javatoproducethefileSpServer.classwith thiscommand:
javac SpServer.java
2. CopySpServer.classtothesqllib\functiondirectoryonWindowsoperating
systems,or tothesqllib/function directoryonUNIX.
3. Next,catalogtheroutinesbyrunningthespcat scriptontheserver.Enter:
spcat
Thisscriptconnectstothesampledatabase,uncatalogs theroutinesifthey were previouslycatalogedbycallingSpDrop.db2,then catalogsthembycalling SpCreate.db2,andfinallydisconnectsfromthedatabase.Youcanalsorunthe SpDrop.db2andSpCreate.db2scriptsindividually.
4. Then,stopandrestart thedatabase toallowthenew classfiletoberecognized.
Ifnecessary,set thefilemodefortheclassfileto″read″soitisreadablebythe fenceduser.
5. CompileandruntheSpClientclient applicationtoaccessthestored procedure
class.
User-definedfunctionswithoutSQLstatements
UDFsrvisauser-definedfunction librarythatdoesnotcontainSQLstatements.DB2 provides bothaJDBCclient application,UDFcli,and anSQLJclientapplication, UDFcli,thatcanaccesstheUDFsrvlibrary.
Tobuild andruntheUDFprogramontheserverfromthecommandline: 1. CompileUDFsrv.javatoproducethefileUDFsrv.classwith thiscommand:
javac UDFsrv.java
2. CopyUDFsrv.classtothesqllib\functiondirectoryonWindowsoperating
systems,or tothesqllib/function directoryonUNIX.
3. ToaccesstheUDFsrvlibrary,youcanuseeitherJDBCorSQLJclient
applications.Bothversions oftheclientprogramcontain theCREATEFUNCTION SQLstatementthatyouusetoregistertheUDFscontained inUDFsrvwiththe database,andalso containSQLstatementsthatmakeuseoftheUDFs,once theyhavebeenregistered.
User-definedfunctionswithSQLstatements
UDFsqlsvisauser-definedfunction librarythatcontains SQLstatements.DB2 provides aJDBCclient application,UDFsqlcl,toaccesstheUDFsqlsvlibrary.
1. CompileUDFsqlsv.javatoproducethefileUDFsqlsv.classwith thiscommand:
javac UDFsqlsv.java
2. CopyUDFsqlsv.classtothesqllib\functiondirectoryonWindowsoperating
systems, ortothesqllib/function directoryonUNIX.
3. ToaccesstheUDFsqlsvlibrary,usetheclientprogram, UDFsqlcl,whichcontains
theCREATEFUNCTIONSQLstatementthatyouusetoregistertheUDFscontained inUDFsqlsvwiththedatabase.Theclientprogramalsocontains SQLstatements thatmakeuseoftheUDFs,oncetheyhavebeenregistered.
YoucanalsousetheJavamakefiletobuildtheaboveprograms.
Related tasks:
v “BuildingJDBCapplets”onpage157
v “BuildingJDBCapplications”onpage158
v “BuildingSQLJroutines”onpage165
Related reference:
v “JDBCsamples” onpage169
Related samples:
v “spcat--Tocatalog SQLjstoredproceduresonUNIX”
v “SpClient.java--Calla varietyof typesofstored proceduresfromSpServer.java
(JDBC)”
v “SpCreate.db2--Howtocatalogthestoredprocedurescontainedin
SpServer.java”
v “SpDrop.db2--Howtouncatalogthestoredprocedurescontainedin
SpServer.java”
v “SpServer.java--Provideavarietyoftypesofstoredprocedurestobe called
from(JDBC)”
v “UDFcli.java--CalltheUDFsinUDFsrv.java(JDBC)”
v “UDFCreate.db2--Howtocatalog theJava UDFscontainedinUDFsrv.java”
v “UDFDrop.db2--HowtouncatalogtheJavaUDFscontainedinUDFsrv.java”
v “UDFsCreate.db2--HowtocatalogtheUDFscontainedinUDFsqlsv.java”
v “UDFsDrop.db2--HowtouncatalogtheUDFscontainedinUDFsqlsv.java ”
v “UDFsqlcl.java--CalltheUDFsinUDFsqlsv.java(JDBC)”
v “UDFsqlsv.java--ProvideUDFstobe calledbyUDFsqlcl.java(JDBC)”
v “UDFsrv.java--ProvideUDFstobe calledbyUDFcli.java(JDBC)”
Building
SQLJ
applets
The followingstepsshowhowtobuildtheAppltsamplethatdemonstratesan SQLJappletaccessinga DB2database.Thesestepsusethebuildfile,bldsqlj (UNIX),orbldsqlj.bat (Windows),whichcontainscommandstobuildeitheran SQLJappletorapplication.
The buildfiletakes uptosixparameters:$1,$2,$3,$4,$5,and$6onUNIX,and %1,%2,%3,%4,%5,and %6onWindows.Thefirstparameterspecifies thename of yourprogram.The secondparameterspecifiestheuserIDforthedatabase instance,thethirdparameterspecifiesthepassword. Thefourthparameter specifies theservername. Thefifthparameterspecifiestheportnumber.And the
sixthparameter specifiesthedatabasename.For allbutthefirstparameter, programname,defaultvaluescanbe used.Seethebuildfilefordetailsabout usingdefaultparametervalues.
Procedure:
Torunthisapplet,eitherensurethatawebserverisinstalledandrunningonyour DB2 machine(serverorclient),oryoucanusetheappletviewerthatcomeswith theJava DevelopmentKitbyenteringthefollowingcommandin theworking directory ofyourclientmachine:
appletviewer Applt.html
Connecting withtheIBMDB2Driver forJDBCandSQLJ
Toconnectwith theIBM DB2DriverforJDBC andSQLJ,modify theApplt.html fileaccordingtotheinstructionsinthefile.FortheTCP/IPportnumber,you shouldusethedatabase portnumber,″50000″.
BuildingtheApplet
1. Buildtheappletwiththiscommand:
bldsqlj Applt <userid> <password> <server_name> <port_number> <db_name>
whereallparametersexcepttheprogramnamecanhavedefaultvalues,as explainedinthebuildfile.
2. Ensurethatyourworking directoryisaccessiblebyyourwebbrowser,orby
your Javaappletviewer,ifyouareusingit.Ifyourdirectoryisnotaccessible, copythefollowingfilesintoa directorythatisaccessible:
v Applt.html v Applt.class v Applt_Cursor1.class v Applt_Cursor2.class v Applt_SJProfileKeys.class v Applt_SJProfile0.ser
3. Copysqllib\java\db2jcc.jaronWindowsorsqllib/java/db2jcc.jaron
UNIX,intothesamedirectoryasApplt.classand Applt.html.
4. On yourclientmachine,start yourwebbrowserorJavaappletviewer,and
loadApplt.html.
YoucanalsousetheJavamakefiletobuildthisprogram.
Related concepts:
v “Javaappletconsiderations”onpage163
Related tasks:
v “BuildingJDBC applets”onpage157
v “BuildingSQLJapplications”onpage162
v “BuildingSQLJroutines”onpage165
Related reference:
v “SQLJapplicationand appletoptionsforUNIX”onpage164
v “SQLJapplicationand appletoptionsforWindows”onpage164
Related samples:
v “bldsqlj.bat--Buildsa JavaembeddedSQL(SQLJ)application orappleton
Windows”
v “Applt.sqlj--AnSQLJappletthatusesa JDBCappletdrivertoaccessadatabase
(SQLj)”
v “bldsqlj--Builds Javaembedded SQL(SQLJ)applicationsand appletsonUNIX”
Building
SQLJ
applications
The followingstepsshowhowtobuildtheTbModsamplethatdemonstratesan SQLJapplication accessingaDB2database.Thesestepsusethebuildfile,bldsqlj (UNIX),orbldsqlj.bat (Windows),whichcontainscommandstobuildeitheran SQLJappletorapplication.
The buildfiletakes uptosixparameters:$1,$2,$3,$4,$5,and$6onUNIX,and %1,%2,%3,%4,%5,and %6onWindows.Thefirstparameterspecifies thename of yourprogram.The secondparameterspecifiestheuserIDforthedatabase instance,thethirdparameterspecifiesthepassword. Thefourthparameter specifies theservername. Thefifthparameterspecifiestheportnumber.And the sixthparameter specifiesthedatabasename.For allbutthefirstparameter, programname,default valuescanbe used.Seethebuildfilefordetailsabout usingdefaultparametervalues.
Procedure:
Tobuild TbModwiththebuildfile,bldsqlj(UNIX)orbldsqlj.bat (Windows), enterthis command:
bldsqlj TbMod <userid> <password> <server_name> <port_number> <db_name>
where allparametersexcepttheprogramnamecanhavedefaultvalues,as explained inthebuildfile.
RuntheJavainterpreterontheapplicationwith thiscommand:
java TbMod
YoucanalsousetheJavamakefiletobuildthisprogram.
IfyouarerunningaJava applicationonUNIXina 64-bitDB2instance butthe software developmentkitforJavais32-bit,youneed tochangetheDB2 library path beforerunningtheapplication.For exampleonAIX:
v IfusingbashorKornshell:
export LIBPATH=$HOME/sqllib/lib32
v IfusingCshell:
setenv LIBPATH $HOME/sqllib/lib32 Related tasks:
v “BuildingJDBCapplications”onpage158
v “BuildingSQLJapplets”onpage160
v “BuildingSQLJroutines”onpage165
Related reference:
v “SQLJapplicationand appletoptionsforWindows”onpage164
v “SQLJsamples”onpage174
Related samples:
v “bldsqlj.bat--Buildsa JavaembeddedSQL(SQLJ)application orappleton
Windows”
v “TbMod.sqlj--Howtomodify tabledata(SQLj)”
v “bldsqlj--Builds Javaembedded SQL(SQLJ)applicationsand appletsonUNIX”
Java
applet
considerations
DB2 databasescanbeaccessedbyusingJavaapplets.Keepthefollowingpointsin mind whenusingthem:
v ForalargerJDBC orSQLJappletthatconsistsofseveralJavaclasses, youmight
choosetopackageallitsclassesinasingleJARfile.ForanSQLJapplet,you wouldalsohavetopackageitsserializedprofilesalongwithitsclasses.Ifyou choosetodothis,addyourJARfileintothearchiveparameterinthe″applet″ tag.Fordetails, seethedocumentationforyoursoftwaredevelopment kitfor Java.
ForSQLJapplets,somebrowsersdonotyethavesupportforloadinga serializedobjectfromaresourcefileassociatedwith theapplet.Forexample, youwillgetthefollowingerrormessagewhen tryingtoloadthesupplied sampleappletAppltinthose browsers:
java.lang.ClassNotFoundException: Applt_SJProfile0
Asaworkaround,thereisa utilitywhichconvertsaserializedprofileintoa profilestored inJavaclassformat. Theutilityisa Javaclasscalled
sqlj.runtime.profile.util.SerProfileToClass.Ittakesa serializedprofile resourcefileasinputandproducesa Javaclasscontaining theprofileasoutput. Yourprofilecanbeconvertedusingoneofthefollowingcommands:
profconv Applt_SJProfile0.ser
or
java sqlj.runtime.profile.util.SerProfileToClass Applt_SJProfile0.ser
TheclassApplt_SJProfile0.classiscreatedasaresult. Replaceall profilesin .serformatusedbytheappletwithprofilesin.classformat, andtheproblem shouldgo away.
v Youcanplacethefiledb2jcc.jarintoa directorythatissharedbyseveral
appletsthatmightbe loadedfromyour Website.db2jcc.jarisfor appletsusing theIBM DB2DriverforJDBC andSQLJorforanySQLJapplet.Thisfileisin thesqllib\java directoryonWindowsoperatingsystems,and inthe
sqllib/javadirectoryonUNIX.Youmight needtoaddacodebaseparameter intothe″applet″ tagintheHTMLfiletoidentifythedirectory.For details,see thedocumentationforyoursoftware developmentkitfor Java.
v TheJDBCappletserver(listener),db2jd,contains signalhandlingtomakeit
morerobust.Asa result,youcannotusetheCTRL-Ckeysequencetoterminate db2jd.Therefore,theonlywaytoterminatethelisteneristokilltheprocessby usingkill-9(forUNIX) ortheTaskManager (forWindows).
Related tasks:
v “BuildingSQLJapplets”onpage160
SQLJ
application
and
applet
options
for
UNIX
The SQLJtranslatorand customizeroptionsare usedinthebldsqlj buildscripton UNIX.ThesearetheoptionsDB2 recommendsthatyouusetobuildSQLJ
applicationsand appletsonUNIXplatforms.
sqlj TheSQLJtranslator(also compilestheprogram).
"${progname}.sqlj"
TheSQLJsourcefile.Theprogname=${1%.sqlj}commandremoves the extension ifitwasincludedintheinputfilename,sowhentheextension isaddedbackagain,itisnotduplicated.
db2sqljcustomize
TheSQLJprofilecustomizer.
-url Specifiesa JDBCURLforestablishing adatabaseconnection,suchas jdbc:db2://servername:50000/sample.
-user Specifiesa userID.
-password
Specifiesa password.
"${progname}_SJProfile0"
Specifiesa serializedprofilefortheprogram.
Related tasks:
v “BuildingSQLJapplets”onpage160
v “BuildingSQLJapplications” onpage162
Related reference:
v “SQLJroutineoptionsforUNIX”onpage166
Related samples:
v “bldsqlj--Builds Javaembedded SQL(SQLJ)applicationsand appletsonUNIX”
SQLJ
application
and
applet
options
for
Windows
The followingSQLJtranslatorand customizeroptionsare usedinthebldsqlj.bat batchfileonWindowsoperatingsystems. Theseare theoptionsDB2recommends thatyouusetobuild SQLJapplicationsandapplets onWindows.
sqlj TheSQLJtranslator(also compilestheprogram).
%1.sqlj
TheSQLJsourcefile.
db2sqljcustomize
TheDB2 forJavaprofilecustomizer.
-url Specifiesa JDBCURLforestablishing adatabaseconnection,suchas jdbc:db2://servername:50000/sample.
-user Specifiesa userID.
-password
%1_SJProfile0
Specifiesa serializedprofilefortheprogram.
Related tasks:
v “BuildingSQLJapplets”onpage160
v “BuildingSQLJapplications”onpage162
Related reference:
v “SQLJroutineoptionsforWindows”onpage167
Related samples:
v “bldsqlj.bat--Buildsa JavaembeddedSQL(SQLJ)application orappleton
Windows”
Building
SQLJ
routines
DB2 providessampleprogramsdemonstrating SQLJroutines(storedprocedures and user-definedfunctions)inthesamples/java/sqljdirectory onUNIX,and the samples\java\sqlj directoryonWindows.Routinesare compiledandstoredona server.Whencalledbya clientapplication,theyaccesstheserverdatabase and return informationtotheclient application.
Inthesamedirectory,DB2 alsosuppliesthebuildfile,bldsqljs(UNIX),or bldsqljs.bat(Windows),whichcontainscommandstobuild routines.
Thebuild filetakes uptosixparameters:$1,$2,$3,$4,$5,and$6onUNIX,and %1,%2,%3,%4,%5,and %6onWindows.Thefirstparameterspecifies thename of yourprogram.Thesecond parameterspecifiestheuserIDforthedatabase instance,thethirdparameterspecifiesthepassword. Thefourthparameter specifies theservername. Thefifthparameterspecifiestheportnumber.And the sixthparameter specifiesthedatabasename.For allbutthefirstparameter, programname,defaultvaluescanbe used.Seethebuildfilefordetailsabout usingdefaultparametervalues.
Procedure:
Thefollowingexampleshowsyouhowtobuilda classfilewithstored procedures.
SpServerdemonstrates PARAMETERSTYLE JAVAstored proceduresusingthe JDBC applicationdrivertoaccessaDB2database.
Tobuild thisstoredprocedureclasswiththebuildfile,bldsqljs(UNIX)or bldsqljs.bat(Windows):
1. Enterthefollowingcommand:
bldsqljs SpServer <userid> <password> <server_name> \ <port_number> <db_name>
whereallparametersexcepttheprogramnamecanhavedefaultvalues,as explainedinthebuildfile.
2. Next,catalogtheroutinesbyrunningthespcat scriptontheserver.Enter:
spcat
Thisscriptconnectstothesampledatabase,uncatalogs theroutinesifthey were previouslycatalogedbycallingSpDrop.db2,then catalogsthembycalling
SpCreate.db2,andfinallydisconnectsfromthedatabase.Youcanalsorunthe SpDrop.db2andSpCreate.db2scripts individually.
3. Then,stopandrestart thedatabase toallowthenew classfiletoberecognized.
Ifnecessary,set thefilemodefortheclassfileto″read″soitisreadablebythe fenceduser.
4. BuildandruntheSpClientclientapplicationtocallthestoredprocedures.You
canbuildSpClientwiththeapplication buildfile,bldsqlj (UNIX)or bldsqlj.bat(Windows).
YoucanalsousetheJavamakefiletobuildtheaboveprograms.
Related tasks:
v “BuildingJDBCroutines”onpage158
v “BuildingSQLJapplets”onpage160