Connectivity Cheat Sheet For Application Developers (Part 4)

3/15/12
Connectivit cheat sheet for Application Developers (Part 4)
English
Sign in (or register)
Technical topics
Evaluation software
Communit
Events
Connectivity cheat sheet for Application Developers (Part 4)

Raul Chong (rfchong@ca.ibm.com), Database consultant, SDI Corp. Jalud Abdulmenan, Database consultant Summary: This is the fourth article discussing connectivity scenarios involving DB2 UDB for Linux, UNIX, and Windows Version 8, DB2 UDB for iSeries 5.2 and DB2 UDB for z/OS V8. The focus of this article is on workstation application development connectivity using C and Java languages. Date: 15 Jan 2004 Level: Introductory Activity: 21063 views Comments: 0 (View | Add comment - Sign in) Average rating (31 votes) Rate this article Introduction This is the fourth article discussing connectivity scenarios involving IBM DB2 Universal DatabaseTM (UDB) for Linux, UNIX and Windows version 8, DB2 UDB for iSeriesTM 5.2 and DB2 UDB for z/OS V8 (At the time this article was written, DB2 UDB for z/OS version 8 had not yet been released for general availability. DB2 for OS/390 and z/OS v7 and a beta version of V8 were used to test the scenarios.). As in the third part of the article, the latest versions are being used; however, the instructions shown may apply to other versions. The focus of this article is on workstation application development connectivity using C and Java languages. We will provide sample programs developed on workstation platforms (Linux, UNIX and Windows) using JDBCTM, SQLJ, ODBC and so on, where each program connects to a DB2 UDB database (any platform), selects from a table, and disconnects. The programs have been tested on a Microsoft Windows 2000 platform; however, they should be portable to UNIX and Linux platforms. Due to space limitations, we provide the code listing of each sample program with minimal comments and error checking (though still fully functional). For the complete functional sample programs, please download the accompanying file to this article. A fifth article in this series will cover ADO, and ADO.NET, and a sixth article in the series will cover other languages as well. Programs written natively in z/OS, OS/390 or iSeries will not be discussed. Please note we assume you have some programming background; therefore, we will not be discussing programming tips, or how to code in general. Working as consultants, we have encountered situations where a sample program would have been handy to test if the application connectivity setup was correct. The sample programs in this article can be used for this purpose. With DB2 UDB for Linux, UNIX and Windows V8 you can now test several types of connections using the Configuration Assistant (CA); however, the sample programs will be handy for previous version of DB2 UDB and also they give you more detail on the connectivity setup as the source code is provided. This article should be useful also to DBAs who normally dont need to code programs. Having a basic understanding about how to write a program against a DB2 database should be advantageous to them when interacting with their application developers. The programs in this article have been based on the sample programs supplied with the DB2 UDB Application Development client (under directory sqllib/samples); however, they have been simplified for ease of understanding. This article will describe the following connectivity scenarios: C/C++: Scenario 1: Connecting to a DB2 UDB database using CLI Scenario 2: Connecting to a DB2 UDB database using ODBC Scenario 3: Connecting to a DB2 UDB database using embedded SQL and C host language
www.ibm.com/developerworks/data/librar /techarticle/dm-0401chong/ 1/28
3/15/12
Java: Scenario 4: Connecting to a DB2 UDB database using the SUN type 1 driver (JDBC ODBC Bridge) Scenario 5: Connecting to a DB2 UDB database using the IBM DB2 UDB type 2 driver (App Driver) Scenario 6: Connecting to a DB2 UDB database using the IBM DB2 UDB type 2 driver (Universal Driver) Scenario 7: Connecting to a DB2 UDB database using the IBM DB2 UDB type 3 driver (Net Driver) Scenario 8: Connecting to a DB2 UDB database using the IBM DB2 UDB type 4 driver (Universal Driver) Scenario 9: Connecting to a DB2 UDB database using SQLJ It is assumed the protocol used is the TCP/IP protocol given that it is the one most often used. Only basic setup information will be shown. Note that we describe the scenarios in a two-tier environment. For an -tier environment, just consider machine 1 as the system where the C or Java program is being executed from. For all the scenarios described below, the following Windows 2000 environment variables were set in our test machines. Most of these environment variables are automatically set when installing DB2 UDB. Note that for some of these sample programs, we are using MS Visual Studio V6 for compilation, and this is reflected in some of the environment variable values. In UNIX/Linux platforms some of these variable names are slightly different. Make sure to set these variables appropriately according to your environment: Table 1. Windows 2000 Environment Variables required
En i onmen Val e Va hiable CLASSPATH.;C:\PROGRA~1\SQLLIB\java\db2java.zip;C:\PROGRA~1\SQLLIB\java\db2jcc.jar;C:\PROGRA~1\SQLLIB\java\sqlj.zip;C:\PROGRA INCLUDE C:\PROGRA~1\SQLLIB\INCLUDE;C:\PROGRA~1\SQLLIB\LIB LIB C:\WINNT\system32;C:\Program Files\Microsoft Visual Studio\VC98\mfc\lib;C:\Program Files\Microsoft Visual Studio\VC98\lib;C:\PROG Path C:\Program Files\SQLLIB\java\jdk\bin;C:\PROGRA~1\SQLLIB\BIN;C:\PROGRA~1\SQLLIB\FUNCTION; Scenario 1 Connecting to a DB2 UDB database (any platform) using CLI The DB2 UDB CLI Driver (which is also used as the DB2 UDB ODBC Driver) can be obtained by downloading (free of charge) any of the IBM DB2 UDB clients at: http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/index.d2w/report Figure 1. DB2 UDB for Linux, UNIX and Windows CLI application client to DB2 UDB server (any platform)
Listing 1. dbconn_ODBC_CLI.c (With minimal comments and no error checking)

3/15/12
/************************************** /************************************* / SUC FL NM:dcn_DCCIc / ORE IE AE bonOB_L. /************************************** /************************************* / Sadr CC+Haes* * tnad /+ edr / #nld <ti.> icue sdoh #nld <tlbh icue sdi.> #nld <tigh icue srn.> / D2OB Hae * * B DC edr / #nld <qci.> icue sll1h / Mi Porm * an rga *itmi(n ag,ca* ag) /n anit rc hr* rv { / Hnls* * ade / SLEV hn =SLNL_EV QHN ev Q_ULHN; SLDC hb =SLNL_DC QHB dc Q_ULHB; SLSM htt SLNL_SM; QHTT sm= Q_ULHTT / Lclvrals* * oa aibe / SLEUN QRTR r; c SLHR QCA saeet371; ttmn[26] SLHR QCA mclu[] yoot2; SLNEE QITGR mcluln=0 yoote ; ca hr dnm[ +1; bae8 ] ca hr ue[8+1; sr1 ] ca hr pw[0+1; sd3 ] i (rc<4 { f ag ) pit (Uae dcn_DCCI<aaaenm><srd <asod\"; rnf "sg: bonOB_L dtbs ae uei> pswr>n) ei () xt 0; } srp (bae ag[]; tcy dnm, rv1) srp (sr tcy ue, ag[]; rv2) srp (sd tcy pw, ag[]; rv3) r =SLloHnl(Q_ADEEV c QAlcadeSLHNL_N, SLNL_ADE Q_ULHNL, &ev; hn) r =SLeEvtrhn,SLAT_DCVRIN c QStnAt(ev Q_TROB_ESO, (QPITRSLO_DC,SLI_NEE) SLONE)Q_VOB3 Q_SITGR; r =SLloHnl(Q_ADEDC c QAlcadeSLHNL_B, hn, ev &dc; hb) / cnett tedtbs * * onc o h aaae / c=SL QC e (dc c hb, (QCA *d ae SLHR )b , SLNS Q_T, (QCA * e, SLHR ) SLNS Q_T, (QCA * SLHR ) d , SLNS; Q_T) pit (\ Scesu cneto t 's d uigOB/L \"dnm) rnf "n ucsfl oncin o %' b sn DCCI n,bae; r =SLloHnl(Q_ADESM, c QAlcadeSLHNL_TT hb, dc &sm) htt; srp(ttmn,SLC IMED) tcysaeet"EET BRQ"; sra(ttmn, FO SSB.YDMY;) tctsaeet" RM YIMSSUM1"; r =SLxciethtt c QEeDrc(sm, saeet ttmn, SLNS; Q_T) r =SLido(sm,1 c QBnClhtt , SLCCA, Q__HR mclu, yoot szo(yoot, iefmclu)
3/15/12
&yoote) mcluln; wie(c=SLec(sm) = SLSCES hl(r QFthhtt) = Q_UCS | r = SLSCESWT_NO | c = Q_UCS_IHIF) { / Pitdt * * rn aa / pit( Scesu rtivlo rcr. Clm 'BRQ'hsavleo 's\"mclu) rnf" ucsfl erea f eod oun IMED a au f %'n,yoot; } r =SLlsCro(sm) c QCoeusrhtt; r =SLretthtt SLRSTPRM) c QFeSm(sm, Q_EE_AAS; r =SLreadeSLHNL_TT htt; c QFeHnl(Q_ADESM, sm) r =SLicnethb) c QDsonc(dc; pit ( Scesu Dsoncinfo dtbs 's\"dnm) rnf " ucsfl icneto rm aaae %'n,bae; r =SLreadeSLHNL_B,hb) c QFeHnl(Q_ADEDC dc; r =SLreadeSLHNL_N,hn) c QFeHnl(Q_ADEEV ev; pit ( Edo Pormn) rnf " n f rga\"; rtr 0 eun ; } / dcn_DCCI / bonOB_L
Table 2. DB2 UDB for Linux, UNIX and Windows CLI client application to DB2 UDB server (any platform) Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring Connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Compiling and linking the CLI program In Windows using MS Visual Studio 6: cl -Zi -Od -c -W2 -DWIN32 dbconn_ODBC_CLI.c link -debug -out:dbconn_ODBC_CLI.exe dbconn_ODBC_CLI.obj db2cli.lib db2api.lib For this article we put the two commands above in a file called bldappCLI.bat which takes the program name (without extension) as an argument. Note that we are linking the program using db2cli.lib (CLI driver) as opposed to odbc32.lib (ODBC Driver Manager). This means this is a CLI program and thus, it will not go through an ODBC Driver Manager; therefore, the program will only work against a DB2 UDB database. * Note: The DB2 runtime client is enough to run this CLI program; however, if you are developing the program as in this case where you need to compile it, then you need the DB2 Application Development Client. Make sure your environment variables are correctly set (see table 1 for an example). Part III: Executing the program dbconn_ODBC_CLI sample db2admin mypsw This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> sample is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias. sample = Database name db2admin = User id on the server mypsw = Password on the server
Figure 2 shows the output of the DB2 UDB list db directory command issued in the Windows system we have used to develop our programs. The first entry NEW_JERS represents a DB2 UDB for z/OS database, the second entry TORISC6 represents a DB2 UDB for iSeries database, and finally, the third entry SAMPLE represents a DB2 UDB for Linux, UNIX and Windows database. We will use all the sample
3/15/12
programs in this article against these three databases where applicable. Figure 2. List db directory output (System database directory)
Figure 3 shows the output of the program db2conn_ODBC_CLI.c when run against the three databases shown in Figure 2. Figure 3. dbconn_ODBC_CLI.c program output
Scenario 2 Connecting to a DB2 UDB database (any platform) using ODBCI DB2 UDB conforms to ODBC 3.51. As mentioned in Scenario 1, the DB2 UDB CLI Driver acts as the DB2 UDB ODBC Driver when interacting with an ODBC Driver manager.
3/15/12
In Windows platforms, the MS ODBC driver manager is also installed with DB2 UDB by default (The DB2 UDB installation will not overwrite a newer version of the driver manager if found). In UNIX and Linux platforms a driver manager is not provided. Starting with DB2 UDB V8, however, IBM officially supports the open source unixODBC Driver manager. Figure 4. DB2 UDB for Linux, UNIX and Windows ODBC application client to DB2 UDB server (any platform)
Note that for this scenario, we use exactly the same program as shown in Listing 1. Program db2conn_ODBC_CLI.c is a CLI program when you link it directly with the db2cli.lib library which is the CLI driver. When linked with the odbc32.lib library, this same program would be considered an ODBC program. Table 3. DB2 UDB for Linux, UNIX and Windows ODBC client application to DB2 UDB server (any platform) Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Commands to run on this machine:
Part I: Configuring connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Registering the database as an ODBC data source To check if the DB2 UDB database had been registered before, list your current data sources with the following command:
d2ls sse ob dt sucs b it ytm dc aa ore
To register your DB2 UDB database as an ODBC Data Source issue:

d2ctlgsse ob dt suc sml b aao ytm dc aa ore ape
If you made a mistake in the previous command, you can remove the entry as follows:
3/15/12
d2uctlgsse ob dt suc sml b naao ytm dc aa ore ape
In the Windows 2000 platform you can also perform the above by going to: Control Panel -> Administrative Tools -> Data Sources (ODBC) In UNIX and Linux platforms some entries are required in the .odbc.ini file (or odbc.ini if Linux) and optionally in other configuration files. For example, using the unixODBC Driver Manager in a Linux environment, the following was sufficient in file odbc.ini:
[ape sml] Die =/p/B/b/81lblbb.o rvr otIMd2V./i/id2s
Environment variable O B I I should also be set to the full path of file odbc.ini. Eg: DCN
epr OBII/t/dcii xot DCN=ecob.n
Because there are slight differences between the UNIX platforms, we will not cover all the cases in this article. For details please refer to the DB2 Connect EE Quick Beginnings Manual Part III: Compiling and linking the ODBC program In Windows using MS Visual Studio 6: cl -Zi -Od -c -W2 -DWIN32 dbconn_ODBC_CLI.c link -debug -out:dbconn_ODBC_CLI.exe dbconn_ODBC_CLI.obj odbc32.lib For this article we put the two commands above in a file called bldappODBC.bat which takes the program name (without extension) as an argument. Note that we are linking the program using odbc32.lib (ODBC Driver Manager). This library is supplied with MDAC SDK (formerly ODBC SDK). For this article, we used this library supplied with MS Visual Studio. Since you are linking your program to the ODBC Driver Manager library, this program will be considered an ODBC one. The ODBC Driver Manager will review the entries created in part II, and will determine which driver to use. For this example, it will determine that sample database is a DB2 UDB database; therefore it will use the DB2 UDB ODBC driver (a.k.a., CLI driver). Once the DB2 UDB ODBC Driver is invoked, it will look at the entries in the DB2 system database directory (obtained with the command l s d d r c o y See it b ietr. Figure 2), and it will be able to connect. * Note: The DB2 runtime client is enough to run this ODBC program. Make sure your environment variables are correctly set (see table 1 for an example) Part IV: Executing the program dbconn_ODBC_CLI sample db2admin mypsw sample = Database name
This sample program was designed to take 3 arguments in this order: <database name> <userid> <password> sample is the alias used for the database name specified in the catalog db command at this client machine. By default, if an alias is not specified in this command, the db name is equal to the alias. db2admin = User id on the server mypsw = Password on the server Figure 5 shows the output of the list system odbc data sources command. Figure 5. List system odbc data sources output (System ODBC Data Sources)
www.ibm.com/developerworks/data/librar /techarticle/dm-0401chong/
7/28
3/15/12
The output of program dbconn_ODBC_CLI.c was shown in Figure 3. The only difference in that figure for this scenario is the fact that batch program bldappODBC.bat is used to compile the program rather than batch program bldappCLI.bat. Scenario 3 Connecting to a DB2 UDB database (any platform) using embedded SQL and C host language Embedded SQL does not mean it is static SQL. An embedded SQL can contain either dynamic or static SQL depending on whether everything in a SQL statement is known before run time or not. The example below shows a static embedded SQL program as it is easier to code. The emphasis of this article is on connectivity, so we will not discuss any further the difference between static versus dynamic SQL. Figure 6. DB2 UDB for Linux, UNIX and Windows C embedded SQL application client to DB2 UDB server (any platform)
The sample embedded SQL C program dbconn_embed.sqc is provided is Listing 2. Listing 2. dbconn_embed.sqc
/************************************** /************************************* / SUC FL NM:dcn_me.q / ORE IE AE bonebdsc /************************************** /*************************************
3/15/12
#nld <ti.> icue sdoh #nld <tlbh icue sdi.> #nld <tigh icue srn.> #nld <qevh icue sln.> #nld <qc.> icue slah itmi(n ag,ca* ag) n anit rc hr* rv { EE SLICUESLA XC Q NLD QC; EE SLBGNDCAESCIN XC Q EI ELR ETO; ca dnm[5 hr bae1] ; ca ue[2 +1 ; hr sr18 ] ca pw[5 hr sd1] ; ca mclu[] ; hr yoot2 EE SLEDDCAESCIN XC Q N ELR ETO; i (rc<4 { f ag ) pit (Uae dcn_me <aaaenm><srd <asod\"; rnf "sg: bonebd dtbs ae uei> pswr>n) ei () xt 0; } srp (bae ag[]; tcy dnm, rv1) srp (sr tcy ue, ag[]; rv2) srp (sd tcy pw, ag[]; rv3) / cnett adtbs * * onc o aaae / EE SLCNETT :baeUE :srUIG:sd XC Q ONC O dnm SR ue SN pw; i (QCD ! 0 { f SLOE = ) pit (\ **Err**n) rnf "n * ro *\"; ei () xt 0; } es { le pit (\ Scesu cneto t 's d uigCebde sai SLn,bae; rnf "n ucsfl oncin o %' b sn medd ttc Q\"dnm) } EE SLSLC IMEDIT :yootFO SSB.YDMY; XC Q EET BRQ NO mclu RM YIMSSUM1 pit( Scesu rtivlo rcr. Clm 'BRQ'hsavleo 's\"mclu) rnf" ucsfl erea f eod oun IMED a au f %'n,yoot; / Dsonc fo adtbs * * icnet rm aaae / EE SLCNETRST XC Q ONC EE; pit ( Scesu Dsoncinfo dtbs 's\"dnm) rnf " ucsfl icneto rm aaae %'n,bae; pit ( Edo Pormn) rnf " n f rga\"; rtr 0 eun ; }/ edmi * * n an /
Table 4. DB2 UDB for Linux, UNIX and Windows embedded SQL C client application to DB2 UDB server (any platform) Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Precompiling and binding db2 connect to sample user db2admin using mypsw db2 prep dbconn_embed.sqc bindfile db2 bind dbconn_embed.bnd db2 connect reset The precompile command (prep) will parse the dbconn_embed.sqc file and create two files:dbconn_embed.c (pure C code)dbconn_embed.bnd (Bind file with only SQL) Then you need to bind the bind file dbconn_embed.bnd to create a package in the database; thus, you
3/15/12
should connect to the database prior to issuing this command. Part III: Compiling the program cl -Zi -Od -c -W2 -DWIN32 dbconn_embed.c link -debug -out:dbconn_embed.exe dbconn_embed.obj db2api.lib * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. Make sure your environment variables are correctly set (see table 1 for an example) Part IV: Executing the program dbconn_embed sample db2admin mypsw sample = Database name
This sample program was designed to take 3 arguments in this order: <database name> <userid> db2admin = User id on the server <password> sample is the alias used for the database name specified in the catalog db command at this client mypsw = Password on the server machine. By default, if an alias is not specified in this command, the db name is equal to the alias. The Windows batch file bldapp_embed.bat contains the precompile, bind, compile and link steps. Figure 7 shows the execution of this batch file as well as the execution of the embedded SQL C program. Due to space limitation, we will only show the output when connecting to a DB2 UDB for Linux, UNIX and Windows database. Figure 7. dbconn_embed.sqc program Output
Scenario 4 Connecting to a DB2 UDB database (any platform) using the SUN type 1 driver (JDBC ODBC Bridge) Since the type 1 driver is converting JDBC method calls into ODBC function calls, it normally provides a slower performance than the other driver types. For completeness we will cover this scenario; however, we recommend you to use any of the other IBM DB2 JDBC drivers. You can obtain them by downloading (free of charge) any of the IBM DB2 UDB Clients at: http://www-3.ibm.com/cgibin/db2www/data/db2/udb/winos2unix/support/index.d2w/report Given that this driver will be using ODBC, make sure you registered your ODBC Data Source as described in scenario 2 before using this JDBC driver.
10/28
3/15/12
Figure 8. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using SUN s type 1 driver
The sample JDBC program DBConn_Type1.java is provided is Listing 3. Listing 3. DBConn_Type1.java

/************************************** /************************************* /SUC FL NM:DCn_ye.aa /ORE IE AE BonTp1jv /************************************** /************************************* ipr jv.q.; mot aasl* casDCn_ye ls BonTp1 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] { / Cekagmns / hc ruet i (rvlnt <3 { f ag.egh ) Sse.u.rnl(Uae jv DCn_ye <aaaenm><srd <asod"; ytmotpitn"sg: aa BonTp1 dtbs ae uei> pswr>) rtr; eun } ty r { Srn dnm tig bae =ag[] rv0; Srn ueI tig srd =ag[] rv1; Srn pswr =ag[] tig asod rv2; Srn ul tig r ="dc dc"+d ae jb: b: b ; Srn vIMED=nl; tig _BRQ ul / Rgseigdie adcnetn / eitrn rvr n oncig Casfrae" .dc dcJbObDie".eIsac(; ls.oNm( jb. b.dcdc )nwntne) Cneto cn=DieMngrgtoncin ul ueI,pswr ) oncin o rvraae.eCneto( r, srd asod ; Sse.u.rnl( ytmotpitn " Scesu cneto t ' +dnm +" d uigJB tp 1die") ucsfl oncin o " bae ' b sn DC ye rvr ; / Rtivn arcr fo atbe / ereig eod rm al
3/15/12
Saeetsm =cncetSaeet) ttmn tt o.raettmn(; RslStr eute s =sm.xctQey"EETIMEDFO SSB.YDMY"; tteeueur(SLC BRQ RM YIMSSUM1) / Teei ol oercr i SSB.YDMY. Nt ta i yuaent / hr s ny n eod n YIMSSUM1 oe ht f o r o / aD2isac onr yumyhv t iseti cmadbfr rnigti porm / B ntne we, o a ae o su hs omn eoe unn hs rga: / d2GATslc o ssb.ydmy t pbi / b RN eet n yimssum1 o ulc wie(r.et)) hl snx( { vIMED=r.eSrn() _BRQ sgttig1; } Sse.u.rnl( ytmotpitn " Scesu rtivlo rcr. Clm 'BRQ'hsavleo ' +vIMED+""; ucsfl erea f eod oun IMED a au f " _BRQ ') / Coigsaeet / lsn ttmn sm.ls(; ttcoe) / Dsoncig / icnetn cncoe) o.ls(; Sse.u.rnl( ytmotpitn " Scesu Dsoncinfo dtbs ' +dnm +"") ucsfl icneto rm aaae " bae ' ; Sse.u.rnl( Edo Porm) ytmotpitn" n f rga"; }cth(xeto e ac Ecpin ) { Sse.u.rnl(**Err**) ytmotpitn"* ro *"; Sse.u.rnl(etSrn( ) ytmotpitn .otig) ; } }/ mi / an }/ DCn_ye / BonTp1
Table 5. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using Suns JDBC Type 1 Driver Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Registering the database as an ODBC data source Refer to Scenario 2 of this article for details. Part III: Compiling the JDBC program that uses SUN s Type 1 driver javac DBConn_Type1.java Things to notice in the JDBC program: The URL must have the format: jdbc:odbc:<dbname> The type 1 driver included with Java 2 is specified by:sun.jdbc.odbc.JdbcOdbcDriver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The type 1 driver is supplied with the JDK that comes with DB2 UDB in sqllib\java\jdk Other demo programs can be found in sqllib\java\jdk\demo Make sure your environment variables are correctly set (see table 1 for an example) Part IV: Executing the program java DBConn_Type1 sample db2admin mypsw sample = Database name
This sample program was designed to take 3 arguments in this order: <database name> <userid> db2admin = User id on the server <password> sample is the alias used for the database name specified in the catalog db command at this client mypsw = Password on the server machine. By default, if an alias is not specified in this command, the db name is equal to the alias.
3/15/12
Figure 9. DBConn_Type1.java program Output
Scenario 5 Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 2 driver (App Driver) Figure 10. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using IBM DB2 UDB type 2 App driver
The sample JDBC program DBConn_Type2.java is shown in Listing 4. Listing 4. DBConn_Type2.java

/************************************** /************************************* / SUC FL NM:DCn_ye.aa / ORE IE AE BonTp2jv /************************************** /************************************* ipr jv.q.; mot aasl* casDCn_ye ls BonTp2 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] {
3/15/12
/ Cekagmns / hc ruet i (rvlnt <3 { f ag.egh ) Sse.u.rnl(Uae jv DCn_ye <aaaenm><srd <asod"; ytmotpitn"sg: aa BonTp2 dtbs ae uei> pswr>) rtr; eun } ty r { Srn dnm tig bae =ag[] rv0; Srn ueI tig srd =ag[] rv1; Srn pswr =ag[] tig asod rv2; Srn ul tig r ="dcd2"+d ae jb:b: b ; Srn vIMED=nl; tig _BRQ ul / Rgseigdie adcnetn / eitrn rvr n oncig Casfrae"O.b.b.dca .BDie".eIsac(; ls.oNm(CMi d2jb. D2 )nwntne) Cneto cn=DieMngrgtoncin ul ueI,pswr ) oncin o rvraae.eCneto( r, srd asod ; Sse.u.rnl( ytmotpitn "Scesu cneto t ' +dnm +" d uigJB tp 2Apdie") ucsfl oncin o " bae ' b sn DC ye p rvr ; / !!Nt:Tecd atrteaoeln i eatytesm a i lsig3 s w hv / ! oe h oe fe h bv ie s xcl h ae s n itn , o e ae / / rmvdi fo ti lsigdet saelmttosi teatce!! eoe t rm hs itn u o pc iiain n h ril !} / DCn_ye / BonTp2
Table 6. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 JDBC Type 2 Driver (App Driver) Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Compiling the JDBC program that uses DB2 UDB Type 2 Driver (App Driver) javac DBConn_Type2.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2:<dbname> The type 2 App driver is specified by:COM.ibm.db2.jdbc.app.DB2Driver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The DB2 type 2 App driver is stored in file db2java.zip under sqllib\java Make sure your environment variables are correctly set (see table 1 for an example) Part III: Executing the program java DBConn_Type2 sample db2admin mypsw sample = Database name
This sample program was designed to take 3 arguments in this order: <database name> <userid> db2admin = User id on the server <password> sample is the alias used for the database name specified in the catalog db command at this client mypsw = Password on the server machine. By default, if an alias is not specified in this command, the db name is equal to the alias. Figure 11. DBConn_Type2.java program output
14/28
3/15/12
Scenario 6 Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 2 driver (Universal Driver) Figure 12. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using the type 2 driver (Universal Driver)
The sample JDBC program DBConn_Type2U.java is shown in Listing 5. Listing 6. DBConn_Type2U.java

/************************************** /************************************* / SUC FL NM:DCn_yeUjv / ORE IE AE BonTp2.aa /************************************** /************************************* ipr jv.q.; mot aasl* casDCn_yeU ls BonTp2 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] { / Cekagmns / hc ruet i (rvlnt <3 f ag.egh ) {
3/15/12
Sse.u.rnl(Uae jv DCn_yeU<aaaenm><srd <asod"; ytmotpitn"sg: aa BonTp2 dtbs ae uei> pswr>) rtr; eun } ty r { Srn dnm tig bae =ag[] rv0; Srn ueI tig srd =ag[] rv1; Srn pswr =ag[] tig asod rv2; Srn ul tig r ="dcd2"+d ae jb:b: b ; Srn vIMED=nl; tig _BRQ ul / Rgseigdie adcnetn / eitrn rvr n oncig Casfrae" .b.b.c.BDie".eIsac(; ls.oNm(c i d2jcD2 )nwntne) Cneto cn=DieMngrgtoncin ul ueI,pswr ) oncin o rvraae.eCneto( r, srd asod ; Sse.u.rnl( ytmotpitn " Scesu cneto t ' +dnm +" d wt JB tp 2Uiesldie"; ucsfl oncin o " bae ' b ih DC ye nvra rvr) / !!Nt:Tecd atrteaoeln i eatytesm a i lsig3 s w hv / ! oe h oe fe h bv ie s xcl h ae s n itn , o e ae / / rmvdi fo ti lsigdet saelmttosi teatce!! eoe t rm hs itn u o pc iiain n h ril ! }/ DCn_yeU / BonTp2
Table 7. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 JDBC Type 2 Driver (Universal Driver) Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
Part I: Configuring connectivity Refer to Part 1 of this article series for detailed connectivity setup instructions. Part II: Compiling the JDBC program that uses DB2 Type 2 Universal Driver javac DBConn_Type2.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2:<dbname> The type 2 Universal driver is specified by:com.ibm.db2.jcc.DB2Driver * Note: The DB2 runtime client is enough to run the embedded SQL C program; however, if you are developing the program as in this case where you need to compile the program, then you need the DB2 Application Development Client. The DB2 type 2 Universal driver is stored in file db2jcc.jar under sqllib\java Make sure your environment variables are correctly set (see table 1 for an example) Part III: Executing the program java DBConn_Type2U sample db2admin mypsw sample = Database name
This sample program was designed to take 3 arguments in this order: <database name> <userid> db2admin = User id on the server <password> sample is the alias used for the database name specified in the catalog db command at this client mypsw = Password on the server machine. By default, if an alias is not specified in this command, the db name is equal to the alias. Figure 13. DBConn_Type2U.java program Output
16/28
3/15/12
As you can see in Figure 13, type 2 Universal Driver did not work correctly against an iSeries database. The problem has been reported to iSeries Defect support, and this article may be updated in the future. Scenario 7 Connecting to a DB2 UDB database using the IBM DB2 UDB type 3 driver (Net Driver) The JDBC Type 3 driver is deprecated (still supported but it will not be enhanced in the future). The recommendation starting with DB2 UDB for Linux, UNIX and Windows V8 is to use the type 4 driver instead, which provides better performance. We will include this scenario in this article for completeness, but only for DB2 UDB for Linux, UNIX and Windows. Figure 14. JDBC application client to DB2 UDB for Linux, UNIX and Windows server using the IBM DB2 UDB type 3 driver
The sample JDBC program DBConn_Type3.java is provided in Listing 6. Listing 6. DBConn_Type3.java

/************************************** /************************************* /SUC FL NM:DCn_ye.aa /ORE IE AE BonTp3jv /************************************** /************************************* ipr jv.q.; mot aasl* casDCn_ye ls BonTp3 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] { / Cekagmns / hc ruet i (rvlnt <5 { f ag.egh ) Sse.u.rnl(Uae jv DCn_ye <aaaenm><srd <asod"; ytmotpitn"sg: aa BonTp3 dtbs ae uei> pswr>) Sse.u.rnl( ytmotpitn" <evrhsnm><B' Jv Ape Sre pr>) sre otae D2s aa plt evr ot"; rtr; eun
3/15/12
} ty r { Srn dnm tig bae Srn ueI tig srd Srn pswr tig asod Srn sre tig evr Srn prNme tig otubr Srn ul tig r Srn vIMED tig _BRQ
=ag[] rv0; =ag[] rv1; =ag[] rv2; =ag[] rv3; =ag[] rv4; ="dcd2/"+ e e +""+ jb:b:/ : =nl; ul
N b +""+d ae e / b ;
/ Rgseigdie adcnetn / eitrn rvr n oncig Casfrae"O.b.b.dc e.BDie".eIsac(; ls.oNm(CMi d2jb. D2 )nwntne) Cneto cn=DieMngrgtoncin ul ueI,pswr ) oncin o rvraae.eCneto( r, srd asod ; Sse.u.rnl( ytmotpitn " Scesu cneto t ' +dnm +" d uigJB tp 3die") ucsfl oncin o " bae ' b sn DC ye rvr ;
/ !!Nt:Tecd atrteaoeln i eatytesm a i lsig3 s w hv / ! oe h oe fe h bv ie s xcl h ae s n itn , o e ae / / rmvdi fo ti lsigdet saelmttosi teatce!! eoe t rm hs itn u o pc iiain n h ril ! }/ DCn_ye / BonTp3
Table 8. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 UDB JDBC Type 3 Driver Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Part I: Compiling the JDBC program that uses DB2 UDB JDBC Type 3 Driver javac DBConn_Type3.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2://<server hostname or IP address>:<DB2 JDBC Applet Server port number> / <dbalias> The type 3 driver is specified by:COM.ibm.db2.jdbc.net.DB2Driver * Note: No DB2 UDB client needs to be installed at the client machine. The IBM DB2 UDB JDBC type 3 driver will be downloaded from the server to this client machine when you load your html page with an applet tag instructing the browser to download the driver. The DB2 UDB type 3 driver is stored in file db2java.zip under sqllib\java (the same file as for the type 2 App driver) Make sure your environment variables are correctly set (see table 1 for an example) Part III: Executing the program java DBConn_Type3 sample db2admin mypsw 9.23.190.24 3320 Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
The DB2 UDB JDBC Applet Server is started with the command: d 2 s r < bjtt b > e When a port number is not provided, the default value of 6789 is used. Note that this is the port number you need to specify in the URL of the JDBC program, NOT the DB2 instance port number. Every time the db2jstrt command is executed, a db2jd process is created.
sample = Database name
db2admin = User id on the server This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 mypsw = Password on the server JDBC applet server port number> Note that sample is NOT the alias used for the database name specified 9.23.190.24 = IP Address of server in the catalog db command, but the actual name of the database as defined in3320 = Port number of the DB2 JDBC applet server started the server. In fact, for this scenario, there is no need to perform any catalogwith the command: db2jstrt 3320 command.
18/28
3/15/12
Figure 15. DBConn_Type3.java program output
In Figure 15 we issued db2jstrt in the same machine as where the application executes because the client and server machines are the same. Otherwise, this command would only be required at the server machine. Scenario 8 Connecting to a DB2 UDB database (any platform) using the IBM DB2 UDB type 4 driver (Universal Driver) Figure 16. DB2 UDB for Linux, UNIX and Windows JDBC application client to DB2 UDB server (any platform) using the IBM DB2 UDB type 4 driver
The sample JDBC program DBConn_Type4.java is shown in Listing 7. Listing 7. DBConn_Type4.java

/************************************** /************************************* / SUC FL NM:DCn_ye.aa / ORE IE AE BonTp4jv /************************************** /************************************* ipr jv.q.; mot aasl* casDCn_ye ls BonTp4 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] { / Cekagmns / hc ruet i (rvlnt <5 { f ag.egh ) Sse.u.rnl(Uae jv DCn_ye <aaaenm><srd <asod"; ytmotpitn"sg: aa BonTp4 dtbs ae uei< pswr>) Sse.u.rnl( ytmotpitn" <evrhsnm><B Isac Pr>) sre otae D2 ntne ot";
3/15/12
rtr; eun } ty r { Srn dnm tig bae Srn ueI tig srd Srn pswr tig asod Srn sre tig evr Srn prNme tig otubr Srn ul tig r Srn vIMED tig _BRQ
=ag[] rv0; =ag[] rv1; =ag[] rv2; =ag[] rv3; =ag[] rv4; ="dcd2/"+ e e +""+ jb:b:/ : =nl; ul
N b +""+d ae e / b ;
/ Rgseigdie adcnetn / eitrn rvr n oncig Casfrae" .b.b.c.BDie".eIsac(; ls.oNm(c i d2jcD2 )nwntne) Cneto cn=DieMngrgtoncin ul ueI,pswr ) oncin o rvraae.eCneto( r, srd asod ; Sse.u.rnl( ytmotpitn "ucsflcneto t ' +dnm +" d uigJB tp 4Uiesldie") Scesu oncin o " bae ' b sn DC ye nvra rvr ;
/ !!Nt:Tecd atrteaoeln i eatytesm a i lsig3 s w hv / ! oe h oe fe h bv ie s xcl h ae s n itn , o e ae / / rmvdi fo ti lsigdet saelmttosi teatce!! eoe t rm hs itn u o pc iiain n h ril ! }/DCn_ye / BonTp4
Table 9. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using IBM DB2 UDB JDBC Type 4 Universal Driver Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1: Note that for the type 4 driver, the DB2 instance port number is provided rather than the DB2 JDBC Applet Server port number as in scenario 7. The DB2 UDB JDBC Applet Server does not need to be running.
Part I: Compiling the JDBC program that uses DB2 UDB Type 4 Universal Driver javac DBConn_Type4.java Things to notice in the JDBC program: The URL must have the format: jdbc:db2://<server hostname or IP address>:<DB2 UDB Instance port number> / <dbalias> The type 4 Universal driver is specified by:com.ibm.db2.jcc.DB2Driver * Note: No DB2 client needs to be installed at this client machine; however, the IBM DB2 JDBC type 4 Universal driver (and the other JDBC and ODBC drivers) is provided with the DB2 UDB Clients. We recommend you download the Application Development Client if you are planning to develop JDBC applications on this machine; otherwise, if you only plan to execute JDBC applications, download the runtime client. If developing applets, the driver will be downloaded from the server to this client machine when you load your HTML page with an applet tag instructing the browser to download the driver. The DB2 type 4 Universal driver is stored in file db2jcc.jar under sqllib\java (This Universal Driver implements Type 2 and Type 4 drivers). Type 4 only works with the TCP/IP protocol. Make sure your environment variables are correctly set (see table 1 for an example) Part III: Executing the program java DBConn_Type4 sample db2admin mypsw 9.23.190.24 50000
This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 Instance Port number> Note that sample is NOT the alias used for the database name specified in the catalog db db2admin = User id on the server command, but the actual name of the database as defined in the server. In fact, for this scenario, mypsw = Password on the server there is no need to perform any catalog command.
sample = database name (for DB2 zSeries it would be the value of LOCATION NAME; for DB2 iSeries it would be the value of the local RDB name)
20/28
3/15/12
Special considerations when connecting to DB2 UDB for z/OS, OS/390 and iSeries: 9.23.190.24 = IP Address of server The DB2 Connect software is required because it comes with a required license file: 50000 = Port number of the DB2 UDB db2jcc_license_cisuz.jar but not because of technical reasons. Instance at this server. (For DB2 zSeries Make sure to use upper case when you specify the database name. The userid and and iSeries, its normally 446). Review password are not case sensitive. This is shown in the output of the program. - The DB2 UDB type 4 driver requires prerequisite stored procedures and views on the Part I (scenarios 2 and 4) of this article target DB2 UDB server. DB2 UDB for Linux, UNIX and Windows V8 has the required series for more details about how to obtain the port number for DB2 UDB zSeries stored procedures and views. For DB2 on OS/390, the stored procedures must be and iSeries. installed manually.
Figure 17. DBConn_Type4.java program output
Scenario 9 Connecting to a DB2 UDB database (any platform) using SQLJ Figure 18. DB2 UDB for Linux, UNIX and Windows SQLJ application client to DB2 UDB (any platform)
21/28
3/15/12
The sample SQLJ program DBConn_Type3j.sqlj is provided is Listing 8. Listing 8. DBConn_Type3j.sqlj

/************************************************* /************************************************ / SUC FL NM:DCn_yejsl / ORE IE AE bonTp3.qj /************************************************* /************************************************ ipr jv.q.; mot aasl* i j . ie* .; i j . ie e.; . f* casDCn_yej ls BonTp3 { pbi sai vi mi(tigag[) ulc ttc od anSrn rv] { / Cekagmns / hc ruet i (rvlnt <5 { f ag.egh ) Sse.u.rnl(Uae jv DCn_yej<aaaenm><srd <asod <evrhsnm> ytmotpitn"sg: aa BonTp3 dtbs ae uei> pswr> sre otae <B Jv Ape Sre Pr nme>) D2 aa plt evr ot ubr"; rtr; eun } ty r { Srn dnm tig bae =ag[] rv0; Srn ueI tig srd =ag[] rv1; Srn pswr tig asod =ag[] rv2; Srn sre tig evr =ag[] rv3; Srn prNme tig otubr =ag[] rv4; Srn ul tig r ="dcd2/"+ e e +""+ jb:b:/ : N b +""+d ae e / b ; Srn himed tig _brq =nl; ul / RgseigteTp 3die adcnetn / eitrn h ye rvr n oncig Casfrae"O.b.b.dc e.BDie".eIsac(; ls.oNm(CMi d2jb. D2 )nwntne) DieM ae.eC e i ( a g g c , eI, a d d) ; Df ea C e c = e Df ea C e ( ieM ae.eC e i ( D a g g c a d ) ) ;
eI, d
22/28
3/15/12
Df ea
e .eDf ea
e ( ) c ;
Sse.u.rnl( ytmotpitn " Scesu cneto t ' +dnm +" d uigSL wt JB tp 3Ntdie") ucsfl oncin o " bae ' b sn QJ ih DC ye e rvr ; / Rtivn arcr fo atbe / ereig eod rm al / Teei ol oercr i SSB.YDMY. Nt ta i yuaent / hr s ny n eod n YIMSSUM1 oe ht f o r o / aD2isac onr yumyhv t iseti cmadbfr rnigti porm / B ntne we, o a ae o su hs omn eoe unn hs rga: / d2GATslc o ssb.ydmy t pbi / b RN eet n yimssum1 o ulc #q [t]{eetIMEDIT :_brq fo ssb.ydmy} sl cx slc BRQ NO himed rm yimssum1; Sse.u.rnl( ytmotpitn " Scesu rtivlo rcr. Clm 'BRQ'hsavleo ' +himed+""; ucsfl erea f eod oun IMED a au f " _brq ') / Coigcnet dsoncig / lsn otx, icnetn i(cx! nl ) f t = ul { cxcoe) t.ls(; } Sse.u.rnl( ytmotpitn " Scesu Dsoncinfo dtbs ' +dnm +"") ucsfl icneto rm aaae " bae ' ; Sse.u.rnl( Edo Porm) ytmotpitn" n f rga"; }cth(xeto e ac Ecpin ) { Sse.u.rnl(**Err**) ytmotpitn"* ro *"; Sse.u.rnl(etSrn( ) ytmotpitn .otig) ; } }/ mi / an }/ DCn_yej / BonTp3
Table 10. DB2 UDB for Linux, UNIX and Windows to DB2 UDB server (any platform) using SQLJ Machine 1 ('m bl e') DB2 UDB fo Lin , UNIX and Wihndo Commands to run on this machine: Part I: Configuring Connectivity Refer to scenarios 4, 5, 6, 7 and 8 depending on the type of JDBC driver you are planning to use. Part II: Compiling and customizing the SQLJ program For this example the sample program to compile uses the IBM DB2 UDB JDBC Type 3 driver. To compile, issue: sqlj DBConn_Type3j.sqlj. To profile customize the program, we use the db2sqljcustomize command (in previous versions of DB2, the command was db2profc). This command is also a Java program using type 2 or type 4 universal JDBC drivers. Based on the way you invoke db2sqljcustomize, the customization will be done by connecting to the database using either of these JDBC driver types. Profile customizer using Type 2 Universal Driver:db2sqljcustomize -url jdbc:db2:sample user db2admin -password mypsw DBConn_Type3j_SJProfile0 OR Profile customizer using Type 4 Universal Driver (since server and port parameters are provided): db2sqljcustomize -url jdbc:db2://9.23.190.24:50000/sample user db2admin -password mypsw DBConn_Type3j_SJProfile0 Make sure your environment variables are correctly set (see table 1 for an example). Part III: Executing the program
Machine 2 ('a ie ') DB2 UDB e e (an pla fo m) Information you need to obtain from this machine, to perform the commands on machine 1:
3/15/12
java DBConn_Type3j sample db2admin sample db2admin mypsw 9.23.190.24 5678 This sample program was designed to take 5 arguments in this order: <database alias> <userid> <password> <host name or IP address> <DB2 JDBC applet server port number> Note that sample is NOT the alias used for the database name specified in the catalog db command, but the actual name of the database as defined in the server. In fact, for this scenario, there is no need to perform any catalog command.
The DB2 UDB JDBC Applet Server is started with the command:
d2sr < bjtt b > e
When a port number is not provided, the default value of 6789 is used. Note that this is the port number you need to specify in the URL of the JDBC program, NOT the DB2 instance port number. Every time the db2jstrt command is executed, a db2jd process is created. sample = Database name db2admin = User id on the server mypsw = Password on the server 9.23.190.24 = IP Address of server 5678 = Port number of DB2 Java Applet Server
Figure 19. DBConn_Type3j.sqlj program Output
In Figure 19 we issued db2jstrt in the same machine as where the application executes because the client and server machines are the same. Otherwise, this command would only be required at the server machine. Due to space constraints in this article we are not showing other sample programs using SQLJ and type 1, type 2 (App Driver), type 2 (Universal Driver) or type 4 drivers; however, we did test those programs, and they are provided in the accompanying file to this article. Tests were also performed using all of these programs against a DB2 UDB for zSeries and DB2 UDB for iSeries server. Table 11 below summarizes the results obtained. The file SQLJResults.pdf also provided, includes print screens of all the cases shown in this table. To save space, we will use the following naming conventions for Table 11: DB2 LUW = DB2 UDB for Linux, UNIX and Windows DB2 z/OS = DB2 UDB for zSeries DB2 iSeries = DB2 UDB for iSeries Table 11. SQLJ sample program test results Te P ofile C omi e SQLJ Sample P og am Name (Name ell # ba ch file ed hich JDBC d i e i being ed) 1 bldsqljType2U.bat -DBConn_Type1j.sqlj
Da aba e Re l Pla fo m -DB2 LUW-OK

24/28
3/15/12
2 3 4 5
bldsqljType2U.bat DBConn_Type1j.sqlj bldsqljType2U.bat DBConn_Type1j.sqlj bldsqljType2U.bat DBConn_Type2j.sqlj bldsqljType2U.bat DBConn_Type2j.sqlj
DB2 z/OS OK DB2 iSeriesDuring Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support DB2 LUW OK DB2 LUW During execution:Successful connection but error retrieving record:SQL0804N The application program parameters for the current request are not valid. Reason code "". Problem has been reported to DB2 UDB Defect Support
bldsqljType2U.bat DBConn_Type2j.sqlj
DB2 UDB During Profile Customization:SQLCODE -4499 for iSeries Problem has been reported to iSeries Defect Support DB2 LUW OK DB2 z/OS OK DB2 iSeriesDuring Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
7 8 9
bldsqljType2U.bat DBConn_Type2j.sqlj bldsqljType2U.bat DBConn_Type2j.sqlj bldsqljType2U.bat DBConn_Type2j.sqlj
10 11 12 13 14 15
bldsqljType2U.bat bldsqljType2U.bat bldsqljType2U.bat bldsqljType2U.bat bldsqljType2U.bat bldsqljType2U.bat
DBConn_Type3j.sqlj DBConn_Type3j.sqlj DBConn_Type3j.sqlj DBConn_Type4j.sqlj DBConn_Type4j.sqlj DBConn_Type4j.sqlj
DB2 LUW OK (Same as scenario 9) DB2 z/OS Type 3 not supported DB2 iSeriesType 3 not supported DB2 LUW OK DB2 z/OS OK DB2 iSeriesDuring Profile Customization:SQLCODE -4499 Problem has been reported to iSeries Defect Support
16 17 18 19 20
bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat
DBConn_Type1j.sqlj DBConn_Type1j.sqlj DBConn_Type1j.sqlj DBConn_Type2j.sqlj DBConn_Type2j.sqlj
DB2 LUW OK DB2 z/OS OK DB2 iSeriesOK DB2 LUW OK DB2 z/OS During execution:Successful connection but error retrieving record: SQL0607N "SYSIBM .SYSDUMMY1 " is not defined for system objects. Problem has been reported to DB2 UDB Defect Support
21 bldsqljType4.bat
DBConn_Type2j.sqlj
DB2 iSeriesDuring execution:Successful connection but error retrieving record: SQL0804N The application program parameters for the current request are not valid. Reason code "". Problem has been reported to DB2 UDB Defect Support
22 23 24 25 26 27 28 29 30
bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat bldsqljType4.bat
DBConn_Type2Uj.sqlj DBConn_Type2Uj.sqlj DBConn_Type2Uj.sqlj DBConn_Type3j.sqlj DBConn_Type3j.sqlj DBConn_Type3j.sqlj DBConn_Type4j.sqlj DBConn_Type4j.sqlj DBConn_Type4j.sqlj
DB2 LUW OK DB2 z/OS OK DB2 iSeriesOK DB2 LUW OK DB2 z/OS Type 3 not supported DB2 iSeriesType 3 not supported DB2 LUW OK DB2 z/OS OK DB2 iSeriesOK
Table 12. Solutions to common errors

3/15/12
E o 1 java.sql.SQLException: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Applicable Sol ion o JDBC Since the Type 1 JDBC driver is based on the ODBC driver, Type 1 (In make sure the ODBC data source is cataloged. See scenario 2 JDBC or of this article for details. SQLJ) Make sure you specify the correct name for your database. Test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw>
2 SQL30082N Attempt to establish connection failed with security All reason "24" ("USERNAME AND/OR PASSWORD INVALID"). SQLSTATE=08001 3 SQL1013N The database alias name or database name " <dbname>" could not be found. SQLSTATE=42705 All
Make sure you specify the correct user id and password. If using ODBC, CLI, JDBC Type 1, 2, 2U, then test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw> Make sure you specify the correct name for your database. If using ODBC, CLI, JDBC Type 1, 2, 2U, then test if the database has been catalogued correctly by connecting from the CLP: db2 connect to <dbname> user <userid> using <psw> If using JDBC type 3 or type 4, make sure you are not using the database names as indicated in the catalog commands at the client machine (system db directory) but the actual names in the database server machine. If using type 4 and connecting against DB2 for zSeries or DB2 for iSeries, make sure the database name is in upper case.
4 COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0616E Error opening socket. SQLSTATE=08S01
JDBC type You may need to start the DB2 JDBC Applet Server using 3 db2jstrt <port number> You may have specified a wrong port number, or an incorrect hostname/IP Address
5 COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0106E Connection is closed. 6 [IBM][JDBC Driver] CLI0615E Error receiving from socket, server is not responding
JDBC type Your userid/password may be incorrect. The dbname may be 3 incorrect JDBC type DB2 for zSeries does not support type 3 driver programs. If you 3 are attempting to connect to such database server, you may get this error. 7 [ibm][db2][jcc][sqlj][Throwable@2470b8] Message = SQLJ usingA serialized profile will only be created if there are SQL Serialized Profile <name> not found any driver statements in the program other than the connection statements. type Make sure your program has any embedded SQL statements. 8 [ibm][db2][jcc][sqlj] SQL Error - SQLCODE : -551, SQLJ usingMake sure the user id has the right privileges in the target SQLSTATE : 42501 any driver database server using the GRANT statement. type 9 COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQLJ usingDBCONN01, DBCONN02, DBCONN03, and DBCONN04 SQL0805N Package any driver are packages bound during the profile customization for an SQLJ "NEW_JERSEY.NULLID.DBCONN02.4E42576D56444B70"type program. You may have attempted to execute the program was not found. SQLSTATE=51002 without correctly compiling it or running the profile customization program db2sqljcustomize (included in the batch files bldsqljType2U.bat and bldsqljType4.bat). 10Review the errors per part 1 of this article series ODBC, CLI, JDBC Type 1, 2, 2U Drivers
26/28
3/15/12
Resources DB2 Version 8 Connectivity Cheat Sheet (Part 1) Connectivity Cheat Sheet for DB2 Universal Database Part 2 - DB2 for z/OS DB2 and Java: The Big Picture Developing Enterprise Java Applications Using DB2 Version 8 Application Development Guide Overview of Java Development in DB2 UDB for Linux, UNIX, and Windows: Version 8.1 Update An Overview of DB2 and Java DataBase Connectivity (JDBC) To download MDAC SDK (formerly ODBC SDK): http://www.microsoft.com/downloads About the authors Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked at IBM for six years, three years in DB2 Technical Support, and the other three as a consultant specializing in database application development and migrations from other RDBMS to DB2. Jalud Abdulmenan is a former IBM employee who left the company in 2002 to continue his professional studies. While at IBM, Jalud was responsible for Quality Assurance of two DB2 migration toolkits, and was instrumental in the development of the DB2 Scholars Support program, which helped promote DB2 UDB in universities. Currently, Jalud is in the process of setting up his own database consulting business. Close [x]
developerWorks: Sign in
IBM ID: Need an IBM ID? Forgot your IBM ID? Password: Forgot your password? Change your password Keep me signed in. By clicking Submit, you agree to the developerWorks terms of use.
S bmi Cancel
The first time you sign into developerWorks, a profile is created for you. Select information in your developerWorks profile is displayed to the public, but you may edit the information at any time. Your first name, last name (unless you choose to hide them), and display name will accompany the content that you post. All information submitted is secure. Close [x]
Choose your display name

3/15/12
The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks. Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons. Display name: (Must be between 3 31 characters.)
By clicking Submit, you agree to the developerWorks terms of use.

S bmi Cancel
All information submitted is secure. Average rating (31 votes) 1 star 2 stars 3 stars 4 stars 5 stars
S bmi
1 star 2 stars 3 stars 4 stars 5 stars
Add comment: Sign in or register to leave a comment. Note: HTML elements are not supported within comments.
Notify me when a comment is added1000 characters left
Po
Be the first to add a comment

Print this page Share this page Follow developerWorks
About Help Contact us Submit content
Feeds and apps Newsletters
Report abuse Terms of use IBM privac IBM accessibilit
Facult Students Business Partners
28/28

Connectivity Cheat Sheet For Application Developers (Part 4)

Uploaded by

Copyright:

Available Formats

Connectivity Cheat Sheet For Application Developers (Part 4)

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Connectivity Cheat Sheet For Application Developers (Part 4)

Uploaded by

Copyright:

Available Formats

3/15/12

Connectivit cheat sheet for Application Developers (Part 4)

Sign in (or register)