doc-DOC_W7_DB2AN_S1.ps

Transcription

1 BM DB for inux, UNX, and Windows DO.NET OE DB S (S )

2

3 BM DB for inux, UNX, and Windows DO.NET OE DB S (S )

4 97 B. BM BM BM BM Publications enter ( BM BM Directory of Worldwide ontacts ( planetwide/) BM S BM DB for inux, UNX, and Windows Deeloping DO.NET and OE DB pplications Updated October, 2014 opyright BM orporation 2006, 2014.

5 1 DO.NET 1.NET (Windows)... 2.NET Visual Studio DB BM Data Serer Proider for.net testconn BM Data Serer Proider for.net NET OE DB.NET Data Proider OE DB.NET Data Proider ODB.NET Data Proider ODB.NET Data Proider BM OE DB Proider for DB BM OE DB Proider for DB OE DB BM OE DB Proider BM OE DB Proider BM OE DB Proider BM OE DB Proider OE DB BM OE DB Proider DB2 OE DB OE DB DB DB2 OE DB BM OE DB Proider BM OE DB Proider OE DB BM OE DB Proider OE DB BM OE DB Proider DO DO Visual Basic DO DO DO BM OE DB Proider DO /++ BM OE DB Proider BM OE DB Proider / OM+ BM OE DB Proider /++ OM BM Data Serer Proider for.net BM.Data.DB BM.Data.DB2Types DB DB2 ( PDF ) SQ DB2 DB B opyright BM orp. 2006, 2014 iii

6 i DO.NET OE DB

7 1 DO.NET Microsoft Windows.NET Framework.NET Framework Microsoft omponent Object Model (OM).NET Framework.NET 40.NET # Visual Basic.NET.NET Framework.NET.NET ( ) () ommon anguage untime ().NET Framework.NET Framework ( Windows Web Web ) OM OM+.NET Framework DO.NET DO.NET DO.NET DataSet (DB2 for Windows ).NET.NET onnection: ommand: SQ Dataeader: opyright BM orp. 2006,

8 Datadapter: DataSet Datadapter DataSet Microsoft 2 (OE DB.NET Data Proider ODB.NET Data Proider) OE DB.NET Data Proider DO.NET BM OE DB Proider (OM interop ) ODB.NET Data Proider DO.NET BM ODB.NET DB2 BM Data Serer Proider for.net DO.NET DB2.NET BM Data Serer Proider for.net DO.NET OE DB ODB.NET.NET (Windows).NET BM BM Data Serer Drier Package BM Data Serer Drier Package BM Data Serer untime lient.net Visual Studio.NET.NET.NET 3.NET Windows - Windows - Visual Studio -.NET Framework -.NET Framework Software Deelopment Kit - Windows -.NET Framework.NET 2 DO.NET OE DB

9 1. BM Data Serer Drier Package BM Data Serer Drier Package : BM BM Data Serer Drier Package.NET 2..NET BM Data Serer Proider for.net.net Framework Visual Studio.NET Framework.NET Framework BM Visual Studio 2008 Visual Studio 2010 Visual Studio 2012.NET Framework.NET Framework BM.NET Framework Windows.NET Framework Version 2.0.NET Framework Version 3.0.NET Framework Version 3.5.NET Framework Version 4.0.NET Framework Version BM Data Serer Package NET Framework BM Data Serer lient BM Data Serer Proider for.net BM Data Serer Proider for.net 1 DO.NET 3

10 Visual Studio DB2 BM Database dd-ns for Visual Studio Visual Studio BM BM Database dd-ns for Visual Studio BM SQ DB2 SQ SQ DB2 SQ SQ DB2 Microsoft Visual Studio ntellisense BM Database dd-ns for Visual Studio DB2 DB2 DB2 DB2 Visual Studio BM Database dd-ns for Visual Studio BM Microsoft Visual Studio BM Database dd-ns for Visual Studio : BM Database dd-ns for Visual Studio Microsoft Visual Studio Microsoft Visual Studio DB2 BM Database dd-ns for Visual Studio DB2 for inux, UNX, and Windows DB2 Web DB2 for inux, UNX, and Windows DB2 for inux, UNX, and Windows Web ( &uid=swg ) BM Data Serer Proider for.net BM Data Serer Proider for.net DO.NET BM 4 DO.NET OE DB

11 BM Data Serer Proider for.net BM Data Serer lient.net 2.NET BM Data Serer lient BM Data Serer Drier Package 2.NET ommon.net Proider DB2.NET DB2.NET DB2 nformix DB2 for z/os BM DB2 for BM i DB2 onnect Serer DB2.NET BM.Data.DB2.dll DB2 nformix ( softwareeqsforproduct.html) nformix.net : nformix.net (BM.Data.nformix.dll) DB nformix DB2.NET (BM.Data.DB2.dll) nformix.net nformix nformix.net BM.Data.nformix.dll nformix ( prodguid/1r0/clarity/softwareeqsforproduct.html) BM Data Serer Proider for.net.net Framework BM Database dd-ns for Visual Studio Microsoft Visual Studio BM.NET BM Database dd-ns for Visual Studio testconn testconn DB2.NET 1 DO.NET 5

12 testconn 2 testconn20.net Framework 2.0 DB2.NET testconn40.net Framework 4.0 DB2.NET 64 BM testconn testconn20.net Framework testconn 32 BM 32 testconn20 testconn40.net Framework testconn 32 BM 32 testconn40 testconn20_32.net Framework testconn testconn20_32 64 BM testconn40_32.net Framework testconn testconn40_32 64 BM Data Serer testconn20 conn-string testconn40 -dtc -ids -idsold -alidateextendednsightwithonnect -alidateewithonnect -maxstep step-number -specific -enumerateremotedbs -enumremotedbs -alidateextendednsight -alidatee -asp -discoer -enumeratedbs -enumdbs -enumeratelocaldbs -enumlocaldbs -dtc -ids ommon nformix.net (BM.Data.nformix.dll) : nformix.net (BM.Data.nformix.dll) DB DO.NET OE DB

13 -idsold nformix DB2.NET (BM.Data.DB2.dll) nformix SDK.NET -idsold nformix SDK -alidateextendednsight -alidatee db2dsdrier.cfg OPM E -alidateextendednsightwithonnect -alidateewithonnect db2dsdrier.cfg OPM E -maxstep step-number testconn testconn 6 -specific 1: BM.NET.NET Framework 2: db2dsdrier.cfg 3: 4: SYSBM.SYSTBES 5: GetSchema() 6: X -dtc netfxx/specific -enumerateremotedbs -enumremotedbs -asp -discoer SP.NET 1 DO.NET 7

14 DB2 (DS) DB2 -discoer BM BM BM Data Serer lient BM Data Serer untime lient -enumeratedbs -enumdbs -enumeratedbs -enumdbs BM BM BM Data Serer lient BM Data Serer untime lient -enumeratelocaldbs -enumlocaldbs conn-string -enumeratelocaldbs -enumlocaldbs BM BM BM Data Serer lient BM Data Serer untime lient testconn 6 6 -dtc testconn 6 : Program Files BM BM DT SEVE DVE bin>testconn20 -dtc "database=sampledsn;uid=username;pwd=password" adding MSDT step Step 1: Printing ersion info.net Framework ersion: X.X.XXXXX.XXXX 64-bit DB2.NET proider ersion: X.X.X.X DB2.NET file ersion: X.X.X.X apability bits: DEFNED Build: XXXXXXXX Factory for inariant name BM.Data.DB2 erified Factory for inariant name BM.Data.nformix erified DS.NET from DbFactory is ommon DS.NET VS is not installed properly Elapsed: Step 2: Validating db2dsdrier.cfg against db2dsdrier.xsd schema file : ProgramData BM DB2 BMDB1 cfg db2dsdrier.cfg against : ProgramData BM DB2 BMDB1 cfg db2dsdrier.xsd Elapsed: 0 Step 3: onnecting using "database=sampledsn;uid=username;pwd=password" Serer type and ersion: DB2/NT XX.XX.XXXX Elapsed: Step 4: Selecting rows from SYSBM.SYSTBES to alidate existence of packages 8 DO.NET OE DB

15 SEET * FOM SYSBM.SYSTBES FETH FST 5 rows only Elapsed: Step 5: alling GetSchema for tables to alidate existence of schema functions Elapsed: Step 6: reating X connection DB2TransactionScope: onnection losed. Elapsed: Test passed. -maxstep step-number 4 : Program Files BM BM DT SEVE DVE bin>testconn20 -maxstep 4 -dtc "database=sampledsn;uid=username;pwd=password" max step 4 adding MSDT step Step 1: Printing ersion info.net Framework ersion: X.X.XXXXX.XXXX 64-bit DB2.NET proider ersion: X.X.X.X DB2.NET file ersion: X.X.X.X apability bits: DEFNED Build: XXXXXXXX Factory for inariant name BM.Data.DB2 erified Factory for inariant name BM.Data.nformix erified DS.NET from DbFactory is ommon DS.NET VS is not installed properly Elapsed: Step 2: Validating db2dsdrier.cfg against db2dsdrier.xsd schema file : ProgramData BM DB2 BMDB1 cfg db2dsdrier.cfg against : ProgramData BM DB2 BMDB1 cfg db2dsdrier.xsd Elapsed: 0 Step 3: onnecting using "database=sampledsn;uid=username;pwd=password" Serer type and ersion: DB2/NT XX.XX.XXXX Elapsed: Step 4: Selecting rows from SYSBM.SYSTBES to alidate existence of packages SEET * FOM SYSBM.SYSTBES FETH FST 5 rows only Elapsed: Test passed. BM Data Serer Proider for.net BM Data Serer Proider for.net BM Data Proider for.net BM Data Proider for.net DO.NET.NET Framework System.Data.ommon.NET DO.NET # 1 DO.NET 9

16 DbProiderFactory factory = DbProiderFactories.GetFactory("BM.Data.DB2"); Dbonnection conn = factory.reateonnection(); DbonnectionStringBuilder sb = factory.reateonnectionstringbuilder(); if( sb.ontainskey( "Database" ) ) { sb.emoe( "database" ); sb.dd( "database", "SMPE" ); } conn.onnectionstring = sb.onnectionstring; conn.open(); DbProiderFactory DO.NET.NET () GetFactory "BM.Data.DB2" BM Data Serer Proider for.net DbProiderFactory BM Data Serer Proider for.net Dbonnection DB2onnection ( Dbonnection ) DB2 nformix DbonnectionStringBuilder BM Data Serer Proider for.net database SMPE BM Data Serer Proider for.net BM Data Serer Proider for.net DB2onnection 1. Serer=<ip address/localhost>:<port number>; Database=<db name>; UD=<userD>; PWD=<password>; onnect Timeout=<Timeout alue> 1: String connectstring = "Database=SMPE"; // When used, attempts to connect to the SMPE database. : D db2dsdrier.cfg 10 DO.NET OE DB

17 2: String cs = "Serer=sr:50000; Database=SMPE; UD=db2adm; PWD=ab1d;onnect Timeout=30"; // When used, attempts to connect to the SMPE database on the serer // sr through port using db2adm and ab1d as the user id and // password respectiely. f the connection attempt takes // more than thirty seconds, the attempt will be terminated and an error // will be generated. 2. connectstring DB2onnection # String connectstring = "Database=SMPE"; DB2onnection conn = new DB2onnection(connectString); conn.open(); return conn; Visual Basic.NET Dim connectstring s String = "Database=SMPE" Dim conn s DB2onnection = new DB2onnection(connectString) conn.open() eturn conn 3. DB2onnection Open connectstring BM Data Serer Proider for.net DB2 BM Data Serer Proider for.net BM Data Serer Proider for.net : Pooling=false / OM+ (MinPoolSize MaxPoolSize) (onnectionifetimenpool) BM Data Serer Proider for.net TrustedontextSystemUserD TrustedontextSystemPassword.NET 1 DO.NET 11

18 TrustedontextSystemUserD tcsuid SYSTEM UTHD TrustedontextSystemPassword tcspwd SYSYTEM UTHD TrustedontextSystemUserD TrustedontextSystemPassword nalidrgument UserD BM Data Serer Proider for.net DB2 for inux, UNX, and Windows DB2 for z/os ETE TUSTED ONTEXT ctxname1 BSED UPON ONNETON USNG SYSTEM UTHD masteruser TTBUTES ( POTOO TPP, DDESS , ENYPTON NONE ) ENBE WTH USE FO userapp1 WTH UTHENTTON, userapp2 WTH UTHENTTON; SYSTEM UTHD masteruser masterpassword / userapp1 userapp2 passapp1 passapp2 1 database=db;serer=serer1:446; UserD=userapp1;Password=passapp1; TrustedontextSystemUserD=masteruser;TrustedontextSystemPassword=masterpassword 2 database=db;serer=serer1:446; UserD=userapp2;Password=passapp2; TrustedontextSystemUserD=masteruser;TrustedontextSystemPassword=masterpassword : UserD.NET [#] DB2onnection conn = new DB2onnection(); conn.onnectionstring = "database=db;serer=serer1:446;" + "UserD=userapp1;Password=passapp1;" + "TrustedontextSystemUserD=masteruser;" + "TrustedontextSystemPassword=masterpassword;" conn.open(); 12 DO.NET OE DB

19 // Do processing as userapp1, such as querying tables conn.lose(); conn.onnectionstring = "database=db;serer=serer1:446;userd=userapp2;" + "Password=passapp2;TrustedontextSystemUserD=masteruser;" + "TrustedontextSystemPassword=masterpassword;" conn.open(); // Do processing as userapp2 conn.lose(); SQODE 0197E TrustedontextSystemUserD ( ) SQODE 0124E SQODE SQ1046N SQ30082N SQ0969N Open() : DO.NET SQ DO.NET DB2 SQ SQ BM Data Serer Proider for.net.net Framework SQ BM Data Serer Proider for.net SQ DB2ommand DB2Parameter BM.Data.DB2Types BM Data Serer Proider for.net BM.Data.DB2Types DB2 SQ SQ BM.Data.DB2Types BM Data Serer Proider for.net DB2Type DB2 nformix Microsoft.NET Framework DB2Types 1 DO.NET 13

20 1. DB2Types DB2Type DB2 nformix.net DB2Types DB2Type DB2 DB2Binary Binary H FO BT DT nformix.net Byte[] DB2Binary Binary 3 BNY Byte[] DB2Binary VarBinary 3 VBNY Byte[] DB2Binary ongvarbinary 1 ONG VH FO BT DT Byte[] DB2String har H H String DB2String Varhar VH VH String DB2String ongvarhar 1 ONG VH VH String DB2String Graphic GPH String DB2String VarGraphic VGPH String DB2String ongvargraphic 1 ONG VGPH String OB DB2lob lob OB OB, TEXT String DB2Blob Blob BOB BOB, BYTE Byte[] DB2lob Dblob DBOB String 1. DB2.NET 2. DB2 DB2Type.Xml Parameterlass.ParameterName String byte[] DB2Xml Xmleader 3. DB2 for z/os DB2 for i V61 4. DB2 for z/os 9 DB2 for inux, UNX, and Windows DO.NET OE DB

21 1. DB2Types DB2Type DB2 nformix.net () DB2Types DB2Type DB2 nformix DB2nt16 Smallnt SMNT BOOEN, SMNT DB2nt32 nteger NT NTEGE, NT, SE DB2nt64 Bignt, BigSerial BGNT BGNT, BGSE, NT8, SE8 DB2eal, DB2eal370 eal E E, SMFOT DB2Double Double DOUBE PESON DEM ( 29), DOUBE PESON DB2Double Float FOT DEM (32), FOT.NET nt16 nt32 nt64 Single Double Double DB2Decimal Decimal DEM MONEY Decimal DB2DecimalFloat DecimalFloat DEFOT Decimal (16 34) 14 DB2Decimal Numeric DEM DEM ( 29), NUME / DB2Date Date DTE DTETME ( ) DB2Time Time TME DTETME ( ) DB2TimeStamp Timestamp TMESTMP DTETME ( ) DB2TimeStamp Offset TimestampWith TimeZone TMESTMP WTH TME ZONE Decimal DateTime String 5 TimeSpan String 5 DateTime String 5 DateTimeOffset String 5 D DB2owd owd OWD Byte[] XM DB2Xml Xml 2 XM Byte[].NET SQ DB2ommand Executeeader() ExecuteNonQuery() ommandtext ommandtype Transaction SQ SQ Executeeader() DB2Dataeader SQ ExecuteNonQuery() 1 DO.NET 15

22 DB2ommand Transaction DB2Transaction DB2Transaction # UPDTE // assume a DB2onnection conn DB2ommand cmd = conn.reateommand(); DB2Transaction trans = conn.begintransaction(); cmd.transaction = trans; cmd.ommandtext = "UPDTE staff " + " SET salary = (SEET MN(salary) " + " FOM staff " + " WHEE id >= 310) " + " WHEE id = 310"; cmd.executenonquery(); Visual Basic.NET UPDTE assume a DB2onnection conn DB2ommand cmd = conn.reateommand(); DB2Transaction trans = conn.begintransaction(); cmd.transaction = trans; cmd.ommandtext = "UPDTE staff " + " SET salary = (SEET MN(salary) " + " FOM staff " + " WHEE id >= 310) " + " WHEE id = 310"; cmd.executenonquery(); # SEET // assume a DB2onnection conn DB2ommand cmd = conn.reateommand(); DB2Transaction trans = conn.begintransaction(); cmd.transaction = trans; cmd.ommandtext = "SEET deptnumb, location " + " FOM org " + " WHEE deptnumb &lt 25"; DB2Dataeader reader = cmd.executeeader(); Visual Basic.NET SEET assume a DB2onnection conn Dim cmd s DB2ommand = conn.reateommand() Dim trans s DB2Transaction = conn.begintransaction() cmd.transaction = trans cmd.ommandtext = "UPDTE staff " + " SET salary = (SEET MN(salary) " + " FOM staff " + " WHEE id >= 310) " + " WHEE id = 310"; cmd.executenonquery() DB2Transaction ommit() ollback() # 16 DO.NET OE DB

23 // assume a DB2Transaction object conn trans.ollback();... trans.ommit(); Visual Basic.NET assume a DB2Transaction object conn trans.ollback()... trans.ommit().net DB2Dataeader DB2esultSet.NET P/SQ P/SQ.NET BEGN OMPOUND BEGN # DB2Dataeader... cmd.ommandtext = begin + declare cursor1 cursor with return to client with hold for select c1 from t1; + open cursor1; + end; ; //eturns a result set by opened cursor cursor1 DB2Dataeader dr = cmd.executeeader(); while (dr.ead()) { //Process read data if required } dr.lose();... # DB2esultSet... cmd.ommandtext = begin + declare cursor1 cursor with return to client with hold for select c1 from t1; + open cursor1; + end; ; //eturns a result set by opened cursor cursor1 DB2esultSet ds = cmd.executeesultset(db2ursortype.forwardonly); while (ds.ead()) { //Process read data if required } ds.lose();... # DB2Dataeader... cmd.ommandtext = begin + declare cursor1 cursor with return to client with hold for select c1 from t1; + declare cursor2 cursor with return to client for select c2 from t2; + open cursor1; + open cursor2; + end; ; //eturns multiple result sets by opened cursors DB2Dataeader dr = cmd.executeeader(); while (dr.ead()) { 1 DO.NET 17

24 //Process read data if required from cursor1 } dr.nextesult(); //Get next result set while (dr.ead()) { //Process read data if required from cursor2 } dr.lose();... # DB2esultSet... cmd.ommandtext = begin + declare cursor1 cursor with return to client with hold for select c1 from t1; + declare cursor2 cursor with return to client for select c2 from t2; + open cursor1; + open cursor2; + end; ; //eturns multiple result sets by opened cursors DB2esultSet ds = cmd.executeesultset(db2ursortype.forwardonly); while (ds.ead()) { //Process read data if required from cursor1 } ds.nextesult(); //Get next result set while (ds.ead()) { //Process read data if required from cursor2 } ds.lose();... BM Data Serer Proider for.net BM Data Serer Proider for.net DB2Dataeader DB2Dataeader ead() GetString() Getnt32() GetDecimal() DB2Dataeader DB2Dataeader lose() # // assume a DB2Dataeader reader nt16 deptnum = 0; String location=""; // Output the results of the query while(reader.ead()) { deptnum = reader.getnt16(0); location = reader.getstring(1); onsole.writeine(" " + deptnum +"" + location); } reader.lose(); Visual Basic.NET 18 DO.NET OE DB

25 assume a DB2Dataeader reader Dim deptnum s nt16 = 0 Dim location s String "" Output the results of the query Do While (reader.ead()) deptnum = reader.getnt16(0) location = reader.getstring(1) onsole.writeine(" " & deptnum &""& location) oop reader.lose();.net.net DB2ommand BM Data Serer Proider for.net 2. DB2ommand ommandtype ommandtype.storedprocedure ommandtype.text ommandtype ommandtype.text ommandtype.text ommandtype ommandtype.storedprocedure ommandtype.storedprocedure ommandtype.text # DB2ommand cmd = conn.reateommand(); String procall = " TEST_PO (@input_param1)"; cmd.ommandtype = ommandtype.text; cmd.ommandtext = procall; : ommandtype ommandtype.text EXEUTE POEDUE ommandtype.storedprocedure # DB2ommand cmd = conn.reateommand(); String procname = "TEST_PO"; cmd.ommandtype = ommandtype.storedprocedure; cmd.ommandtext = procname; : ommandtype ommandtype.storedprocedure 3. N NOUT OUT DB2ommand.Parameters DB2Parameter dd 1 DO.NET 19

26 DB2Parameter DB2ommand.Parameters # DB2Parameter p1 = new DB2Parameter( input_param1, DB2Type.nteger); p1.value = 123; db2ommand.parameters.dd(p1); SQ # ETE POEDUE schema.my_proc ( N ar1 int, NOUT ar2 int ) NGUGE SQ BEGN -- ( ) END String procall = " my_proc (ar2=>@param2, ar1=>@param1"); BM Data Serer Proider for.net Y (N) DB2 for inux, UNX, and Windows. (NFM) DB2 for z/os 11 DB2 for i V71 Y OUT NOUT Y DB2Parameter.rrayength Y # nt32 integerrray = new nt32[] { 12, 34, 45, 67 }; DB2Parameter p1 = new DB2Parameter( input_param1, DB2Type.nteger); p1.value = integerrray; p1.rrayength = 3; db2ommand.parameters.dd(p1); USO NOUT (nputoutput) OUT (Output) ursor # DB2ommand cmd = new DB2ommand("cursor_test", conn) cmd.ommandtype = ommandtype.storedprocedure; cmd.parameters.dd("cursor1", DB2Type.ursor).Direction = ParameterDirection.Output; cmd.parameters.dd("cursor2", DB2Type.ursor).Direction = ParameterDirection.Output; cmd.executenonquery(); DB2 for z/os DB2ommand.ExecuteNonQuery() # 20 DO.NET OE DB

27 cmd.executenonquery(); OUT NOUT DB2Dataeader # DB2Dataeader DB2Dataeader droutput2 = cmd.parameters[1].value; droutput1 = cmd.parameters[0].value BM Data Serer Proider for.net OMMT (NFM) DB2 for z/os 11 BeginTransaction ommandtype.text # // assume a DB2onnection conn DB2Transaction trans = conn.begintransaction(); DB2ommand cmd = conn.reateommand(); String procname = "NOUT_PM"; String procall = " cmd.transaction = trans; cmd.ommandtype = ommandtype.text; cmd.ommandtext = procall; // egister input-output and output parameters for the DB2ommand cmd.parameters.dd( new DB2Parameter("@param1", "Value1"); cmd.parameters.dd( new DB2Parameter("@param2", "Value2"); DB2Parameter param3 = new DB2Parameter("@param3", fxtype.nteger); param3.direction = ParameterDirection.Output; cmd.parameters.dd( param3 ); // all the stored procedure onsole.writeine(" all stored procedure named " + procname); cmd.executenonquery(); ommandtype.text Visual Basic assume a DB2onnection conn Dim trans s DB2Transaction = conn.begintransaction() Dim cmd s DB2ommand = conn.reateommand() Dim procname s String = "NOUT_PM" Dim procall s String = " NOUT_PM (?,?,?)" cmd.transaction = trans cmd.ommandtype = ommandtype.text cmd.ommandtext = procall egister input-output and output parameters for the DB2ommand... all the stored procedure onsole.writeine(" all stored procedure named " & procname) cmd.executenonquery() ommandtype.storedprocedure # // assume a DB2onnection conn DB2Transaction trans = conn.begintransaction(); DB2ommand cmd = conn.reateommand(); String procname = "NOUT_PM"; cmd.transaction = trans; cmd.ommandtype = ommandtype.storedprocedure; cmd.ommandtext = procname; // egister input-output and output parameters for the DB2ommand... // all the stored procedure onsole.writeine(" all stored procedure named " + procname); cmd.executenonquery(); ommandtype.storedprocedure Visual Basic 1 DO.NET 21

28 assume a DB2onnection conn Dim trans s DB2Transaction = conn.begintransaction() Dim cmd s DB2ommand = conn.reateommand() Dim procname s String = "NOUT_PM" cmd.transaction = trans cmd.ommandtype = ommandtype.storedprocedure cmd.ommandtext = procname egister input-output and output parameters for the DB2ommand... all the stored procedure onsole.writeine(" all stored procedure named " & procname) cmd.executenonquery() Y # db2ommand.ommandtext = arrayparamprocedure ; db2ommand.ommandtype = ommandtype.storedprocedure; nt32 integerrray = new nt32[] { 12, 34, 45, 67 }; DB2Parameter p1 = new DB2Parameter( numbers_in, DB2Type.nteger); p1.value = integerrray; p1.rrayength = 3; String[] stringrray = new String[] { i think i know, but you neer know, how much i know }; DB2Parameter p2 = new DB2Parameter( archars_in, DB2Type.Varchar, 30); p2.value = stringrray; p2.rrayength = 2; db2ommand.parameters.dd(p1); db2ommand.parameters.dd(p2); db2ommand.executenonquery(); USO BM Data Serer Proider for.net DB2Type.ursor USO DB2TYPE.ursor OrderDetails 3 ETE O EPE TYPE cur S USO ETE POEDUE OrderDetails (p_startdate TMESTMP, p_enddate TMESTMP, OUT proddetails cur, OUT prodorderdetails cur, OUT custorderdetails cur) NGUGE SQ BEGN SET proddetails = USO WTH HOD FO SEET p.pid, price, quantity FOM products p, inentory i WHEE p.pid = i.pid ND p.pid N (SEET DSTNT pid FOM orders) ODE BY pid; SET prodorderdetails = USO WTH HOD FO SEET pid, OUNT(*), SUM (quantity) FOM orders WHEE date >= p_startdate ND date <= p_enddate GOUP BY pid ODE BY pid; SET custorderdetails = USO WTH HOD FO SEET pid, custd, OUNT(*), SUM(quantity) FOM orders WHEE date >= p_startdate ND date <= p_enddate GOUP BY pid, custd ODE by pid, custd; OPEN proddetails; OPEN prodorderdetails; OPEN custorderdetails; END; USO DB2Type DB2Type.ursor //# ode sample cmd.ommandtext = @custorderdetails)"; cmd.parameters.dd("@p_startdate", DateTime.Parse("1/1/2010")); cmd.parameters.dd("@p_enddate", DateTime.Parse("12/31/2010")); cmd.parameters.dd("@proddetails", DB2Type.ursor); cmd.parameters["@proddetails"].direction = ParameterDirection.Output; cmd.parameters.dd("@prodorderdetails", DB2Type.ursor); cmd.parameters["@prodorderdetails"].direction = ParameterDirection.Output; 22 DO.NET OE DB

29 DB2Type.ursor); = ParameterDirection.Output; cmd.executenonquery(); DB2Dataeader proddetailsd = (DB2Dataeader)cmd.Parameters["@prodDetails"].Value; DB2Dataeader prodorderdetailsd = (DB2Dataeader)cmd.Parameters["@prodOrderDetails"].Value; DB2Dataeader custorderdetailsd = (DB2Dataeader)cmd.Parameters["@custOrderDetails"].Value; while (prodorderdetailsd.ead()) { pid = prodorderdetailsd.getnt32(0); numorders = prodorderdetailsd.getnt32(1); totalorderquantity = prodorderdetailsd.getnt32(2); proddetailsd.ead(); price = proddetailsd.getdecimal(1); currentnentory = proddetailsd.getnt32(2); int totalustorders = 0; while (custorderdetailsd.ead()) { custd = custorderdetailsd.getnt32(1); numordersbyust = custorderdetailsd.getnt32(2); totalustorders += numordersbyust; totalorderquantitybyust = custorderdetailsd.getnt32(3); //alculate discount based on numorders, numordersbyust, // totalorderquantity, totalorderquantitybyust, price and currentnentory if (totalustorders == numorders) //done with this pid break; } } proddetailsd.lose(); prodorderdetailsd.lose(); custorderdetailsd.lose(); USO ExecuteNonQuery Value Executeeader ExecuteesultSet DB2Dataeader DB2esultSet Nextesult Value nalidoperation ExecuteNonQuery BM Data Serer Proider for.net BM Data Serer Proider for.net.net System.Diagnostics.Trace.NET public System.Diagnostics.TraceSwitch.NET 1 DO.NET 23

30 DB2NMPTE DB2NMPTE DB2.NET System.Diagnostics.TraceSwitch FXNMPTE FXNMPTE nformix.net System.Diagnostics.TraceSwitch : nformix.net (BM.Data.nformix.dll) DB nformix DB2.NET (BM.Data.DB2.dll) System.Diagnostics.TraceSwitch System.Diagnostics.Traceeel BM Data Serer Proider for.net 1..NET BM.Data.DB2 DB2.NET BM.Data.nformix nformix.net 2..NET BM.Data.DB2.NET a. set DB2NMPTE System.Diagnostics.Traceeel 0 4 set DB2NMPTE=1 b. set DB2NMPONSOE set DB2NMPONSOE=c: tmp nmptrace 24 DO.NET OE DB

31 DB2NMPTE 0 4 System.Diagnostics.Traceeel <system.diagnostics> <trace autoflush="false" indentsize="4"> <listeners> <add name="configonsoleistener" type="system.diagnostics.onsoletraceistener" /> </listeners> </trace> <switches> <add name="db2nmpte" alue="1" /> </switches> </system.diagnostics> BM.Data.nformix 2.NET a. set FXNMPTE System.Diagnostics.Traceeel 0 4 set FXNMPTE=1 b. set FXNMPONSOE set FXNMPONSOE=c: tmp nmptrace FXNMPTE 0 4 System.Diagnostics.Traceeel <system.diagnostics> <trace autoflush="false" indentsize="4"> <listeners> <add name="configonsoleistener" type="system.diagnostics.onsoletraceistener" /> </listeners> </trace> <switches> <add name="fxnmpte" alue="1" /> </switches> </system.diagnostics> * * Started tracing program * reating connection DB2onnection.DB2onnection1 api entry - database=nmpftu; DB2onnection.DB2onnection1 api exit, rc = 0 * Opening connection DB2onnection.Open api entry DB2onnection.Open api exit, rc = 0 * losing connection DB2onnection.lose api entry 1 DO.NET 25

32 DB2onnection.lose api exit, rc = 0 * Ending program DB2onnection.~DB2onnection api entry DB2onnection.~DB2onnection api exit, rc = 0 DB2onnection.Dispose api entry DB2onnection.Dispose api exit, rc = 0 (*) purequery.net.net purequery.net SQ BM Data Serer Proider for.net Microsoft Entity Framework BM Data Serer Proider for.net Microsoft Entity Framework EDM Entity SQ NQ BM Data Serer Proider for.net Microsoft Entity Framework BM Database dd-ns for Visual Studio Microsoft Visual Studio BM BM Database dd-ns for Visual Studio Microsoft Entity Framework : BM Data Serer Proider for.net Microsoft Entity Framework Microsoft Visual Studio BM BM Database dd-ns for Visual Studio Microsoft Entity Framework Microsoft.NET Framework 3.5 SP1 Microsoft Entity Data Model Entity Designer Microsoft Visual Studio 2008 BM Database dd-ns for Visual Studio Entity Framework 5.0 Microsoft.NET Framework Microsoft Visual Studio 2012 Entity Framework 5.0 Microsoft.NET Framework DO.NET OE DB

33 Entity Framework 5.0 Microsoft.NET Framework 4.5 Microsoft Entity Framework 5.0 : BM Data Serer Proider for.net Entity Framework 5.0 BM Data Serer Proider for.net BM Database dd-ns for Visual Studio Visual Studio 2012 Entity Framework 5.0 : nt16 nt32 nt64 1 : Entity Framework (EF ) 1 : : BM Data Serer Proider for.net BM 2. BM NQ DB2 for inux, UNX, and Windows DB2 for z/os DB2 for i nformix ggregate erage Bigount ount Maximum Minimum NewGuid * * * * StDe StDeP Sum Var VarP Bitwise BitWisend * * BitWiseNot * * BitWiseOr * * BitWiseXor * * Math bs eiling Floor Power ound (alue,digits) Truncate (alue,digits) 1 DO.NET 27

34 2. BM () NQ DB2 for inux, UNX, and Windows DB2 for z/os DB2 for i nformix String oncat ontains * EndsWith ndexof * eft ength Trim eplace ight Trim StartsWith Substring Toower ToUpper Trim Datetime ddnanoseconds ddmicroseconds ddmilliseconds ddseconds ddminutes ddhours dddays ddmonths ddyears reatedatetime reatedatetimeoffset urrentdatetimeoffset reatetime urrentdatetime urrentutcdatetime Day DayOfYear DiffNanoseconds * DiffMicroseconds * DiffMilliseconds * DiffSeconds * DiffMinutes * DiffHours * DiffDays * DiffMonths * DiffYears * GetTotalOffsetMinutes Hour Millisecond Minute Month Second Truncate (datetime exp) Year 28 DO.NET OE DB

35 : SQ0440N * Microsoft Entity Framework : BM Data Serer Proider for.net Microsoft Entity Framework : Entity Framework Entity Framework DB2 for z/os : E E FOT ( ) FOT nformix Dynamic Serer Entity Framework ( dociew.wss?uid=swg ) BM Data Serer Proider for.net Microsoft SQ Serer eporting Serices Microsoft SQ Serer eporting Serices (SSS) ODB OE DB DB2 (BM Data Serer Proider for.net) DB2 SSS DB2 SSS DB2 (BM Data Serer Proider for.net) DB2 ( DB2 Enterprise Serer Edition) BM Data Serer lient BM Data Serer untime lient BM Data Serer Drier Package Microsoft Visual Studio DB2 DB2 1 DO.NET 29

36 DB2 SSS 2008 SSS 2012 DB2 SSS DB2 ODB OE DB DB2 Microsoft Visual Studio BM Data Serer Proider for.net SSS DB2 SSS BM Data Serer Proider for.net BM Data Serer Proider for.net DB2 BM Data Serer DB DB2 3. BM Data Serer Proider for.net 1. BM DB2 2. DB2 DB2OPY DB2.NET Data Proider BM Data Serer Drier Package BM Data Serer Proider for.net ( DB2.NET Data Proider ) Enterprise ibrary Enterprise ibrary BM Enterprise ibrary Patchist.aspx BM (DB2 nformix U2) Enterprise ibrary readme Entib ontrib Project : 30 DO.NET OE DB

37 patterns & practices for Enterprise ibrary: Microsoft Enterprise ibrary : cc aspx BM DB2 for.net: Visual Basic.NET DB2 DB2 Visual Basic.NET bldapp.bat sqllib samples.net b 1 %1 (.b ) Dbuth bldapp.bat Visual Basic.NET Dbuth.b Dbuth bldapp Dbuth 1. Dbuth 2. 1 Dbuth <db_alias> 3. 2 D Dbuth <userid> <passwd> 4. 3 D Dbuth <db_alias> <userid> <passwd> 5. 4 D Dbuth <serer> <portnum> <userid> <passwd> 6. 5 D 1 DO.NET 31

38 Dbuth <db_alias> <serer> <portnum> <userid> <passwd> Trans Trans.b #.NET DB2 DB2 #.NET bldapp.bat sqllib samples.net cs 1 %1 (.cs ) Dbuth bldapp.bat #.NET Dbuth.cs Dbuth bldapp Dbuth 1. Dbuth 2. 1 Dbuth <db_alias> 3. 2 D Dbuth <userid> <passwd> 4. 3 D Dbuth <db_alias> <userid> <passwd> 5. 4 D Dbuth <serer> <portnum> <userid> <passwd> 6. 5 D Dbuth <db_alias> <serer> <portnum> <userid> <passwd> 32 DO.NET OE DB

39 Trans Trans.cs Visual Basic.NET Visual Basic.NET Windows Microsoft Visual Basic.NET Visual Basic.NET bldapp.bat :.NET Framework 1.1.NET Proider 9.5 bldapp VB.NET VB.NET %BDOMP% bc (Microsoft Visual Basic.NET ) /r:"%db2pth%" bin %VESON% BM.Data.DB2.dll.NET Framework DB2 %DB2PTH% %DB2PTH% DB2 %DB2PTH% BM Data Serer Drier for ODB and Data Serer Drier Package BM BM Data Serer Drier for ODB and Data Serer Drier Package %DB2PTH% %VESON%.NET Framework DB2.NET Framework %VESON% netf20 1 DO.NET 33

40 Trans (bldapp ) %BDOMP% bc (Microsoft Visual Basic.NET ) /out:ootom.dll Trans ootom ootom.b /out:subom.dll Trans SubOM SubOM.b /target:library %1.cs (ootom.b SubOM.b) /r:system.enterpriseserices.dll Microsoft Windows System EnterpriseSerices /r:"%db2pth%" bin %VESON% BM.Data.DB2.dll.NET Framework DB2 %DB2PTH% %DB2PTH% DB2 %DB2PTH% BM Data Serer Drier for ODB and Data Serer Drier Package BM BM Data Serer Drier for ODB and Data Serer Drier Package %DB2PTH% %VESON%.NET Framework DB2.NET Framework %VESON% netf20 /r:system.data.dll Microsoft Windows System Data /r:system.dll Microsoft Windows System /r:system.xml.dll Microsoft Windows System XM (SubOM.b ) 34 DO.NET OE DB

41 /r:subom.dll SubOM (ootom.b Trans.b ) /r:ootom.dll ootom (Trans.b ) #.NET #.NET Windows Microsoft # # DB2 bldapp.bat :.NET Framework 1.1.NET Proider 9.5 # (bldapp ) # %BDOMP% csc (Microsoft # ) /r:"%db2pth%" bin %VESON%BM.Data.DB2.dll.NET Framework DB2 %VESON%.NET Framework DB2.NET Framework %VESON% netf20 Trans (bldapp ) %BDOMP% csc (Microsoft # ) /out:ootom.dll Trans ootom ootom.cs 1 DO.NET 35

42 /out:subom.dll Trans SubOM SubOM.cs /target:library %1.cs (ootom.cs SubOM.cs) /r:system.enterpriseserices.dll Microsoft Windows System EnterpriseSerices /r:"%db2pth%" bin %VESON%BM.Data.DB2.dll.NET Framework DB2 %VESON%.NET Framework DB2.NET Framework %VESON% netf20 /r:system.data.dll Microsoft Windows System Data /r:system.dll Microsoft Windows System /r:system.xml.dll Microsoft Windows System XM (SubOM.cs ) /r:subom.dll SubOM (ootom.cs Trans.cs ) /r:ootom.dll ootom (Trans.cs ) OE DB.NET Data Proider OE DB.NET Data Proider onnectionstring BMDDB2 BM DB2 OE DB Drier OE DB.NET Data Proider BM OE DB Proider for DB2 BM Data Serer Proider for.net 36 DO.NET OE DB

43 OE DB.NET Data Proider BM DB2 OE DB Proider OE DB.NET Data Proider DO.NET OE DB OE DB.NET Data Proider OE DB.NET Data Proider.NET Framework DB2 Uniersal Database for S/ P ii13348 OE DB.NET Data Proider 1 3. OE DB.NET Data Proider onnectionstring POVDE BMDDB2 BM OE DB Proider for DB2 () DSN DB2 UD user D DB2 D PWD password DB2 D : onnectionstring Microsoft OleDbonnection SMPE [Visual Basic.NET] Dim con s New OleDbonnection("Proider=BMDDB2;" + "Data Source=sample;UD=userid;PWD=password;") con.open() [#] OleDbonnection con = new OleDbonnection("Proider=BMDDB2;" + "Data Source=sample;UD=userid;PWD=password;" ); con.open() OE DB.NET Data Proider OE DB.NET Data Proider BM Data Serer Proider for.net OE DB.NET Data Proider 1 DO.NET 37

44 4. OE DB.NET Data Proider S DbType.nsiString DbType.nsiStringFixedength OleDbParameters S DB2 OE DB.NET Data Proider "Specified cast is not alid" : DbType.nsiString DbType.nsiStringFixedength DbType.Binary DOecord DOecord DOecordSet Timestamp MSDN DOecordSet 1 DB2 Timestamp DOecordSet DataSet DOecordSet DataSet Timestamp : DB2 Uniersal Database for inux, UNX, and Windows MPTMESTMPDESBE = 2 Timestamp WH(26) DB2 db2 update cli cfg for section common using MPTMESTMPDESBE 2 hapters hapters OE DB.NET Data Proider Dataeader OE DB.NET Data Proider DB2 for inux, UNX, and Windows inux UNX Windows DB2 DB2 for VM/VSE DB2 for inux, UNX, and Windows DB2 db2set DB2_PM_PEFOMNE=8 DB2 OE DB OE DB Proider OleDbDatadapter.FillSchema() OleDbommand.Executeeader (ommandbehaior.schemaonly ommandbehaior.keynfo) 38 DO.NET OE DB

45 4. OE DB.NET Data Proider () SQ SQ FillSchema() SQ SQ [#] cmd.ommandtext = "NSET NTO OG(1) VUES(1000); SEET 1 FOM OG;"; da = new OleDbDatadapter(cmd); da.fillschema(ds, SchemaType.Source); DB2 OleDbommandBuilder SQ NSET SEET OleDbommandBuilder UPDTE DEETE NSET OB BOB DBOB ONG VH ONG VH FO BT DT ONG VGPH DB2 for inux, UNX, and Windows DB2 VH 1 VH FO BT DT 1 VGPH 1 E FOT DOUBE TMESTMP : VH 254 VH FO BT DT 127 VGPH DB2 for inux, UNX, and Windows DB2 OleDbommandBuilder WHEE SQ : OleDbommandBuilder UPDTE DEETE NSET DbDatadapter.Update() 1 UPDTE : SQ WHEE UPDTE DEETE NSET 1 DO.NET 39

46 4. OE DB.NET Data Proider () OleDbommandBuilder. DerieParameters OleDbommandBuilder. DerieParameters OleDbommandBuilder. DerieParameters OleDbommandBuilder. DerieParameters OleDbonnection. hangedatabase OleDbonnection. onnectionstring DerieParameters() OleDbommand.ommandText DB2 OpenSchema( OleDbSchemaGuid.Procedures ) DB2 OleDbommandBuilder.DerieParameters() OleDbParameterollection eturnvalue Sqllient BM Data Serer Proider for.net Parameterollection ParameterDirection.eturnValue OleDbommandBuilder.DerieParameters() MYPO OleDbommandBuilder.DerieParameters() DerieParameters() DerieParameters() OleDbonnection.hangeDatabase() b a O DB2 Data Source SEVE BMDDB2 nitial atalog onnect Timeout OE DB.NET Data Proider Multiple-step OE DB operation generated errors. heck each OE DB status alue, if aailable. No work was done. onnectiontimeout onnectiontimeout 40 DO.NET OE DB

47 4. OE DB.NET Data Proider () OleDbonnection. GetOleDbSchemaTable DB2 ETE TBE abc(c1 SMNT) DB2 ("B") "B" schematable = con.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, "B", "TBE" }); OleDbDatadapter DataolumnMapping : cmd.ommandtext = "create table "ase Sensitie "(c1 int)"; cmd.executenonquery(); tablename = " "ase Sensitie ""; schematable = con.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, tablename, "TBE" }); DB2 OleDbDataeader. GetSchemaTable colmap = new DataolumnMapping("EMPNO", "Employee D"); OE DB.NET Data Proider ( ) Dataeader.GetSchemaTable() seadonly DB2 for OS/390, V7 DB2 for OS/400 DB2 for VM/VSE sunique sutoncrement BaseSchemaName : BaseatalogName DB2 for OS/ OE DB.NET Data Proider ( "1" "2" "3") MSDN ("olumn1", "olumn2", "olumn3") DB2 for OS/ OE DB.NET Data Proider OE DB.NET Data Proider OE DB OE DB OE DB Proider=BMDDB2;OE DB Serices=-4;Data Source=SMPE; 1 DO.NET 41

48 OE DB DO 5. DO OE DB () "OE DB Serices = -1;" "OE DB Serices = -2;" "OE DB Serices = -4;" "OE DB Serices = -5;" "OE DB Serices = -6;" "OE DB Serices = 0;" OE DB OE DB MSDN OE DB Programmer's eference OE DB.NET Data Proider Dataecord.GetValue() OleDbDataeader.GetTimeSpan() command.ommandtext = "insert into mytable(c1) alues(? )"; c1 2 OleDbParameter.OleDbType = OleDbType.DBTime OleDbType.DBTime TimeSpan TimeSpan String DateTime TimeSpan p1.oledbtype = OleDbType.DBTime; p1.value = TimeSpan.Parse("0.11:20:30"); rowsffected = cmd.executenonquery(); MSDN TimeSpan [-]d.hh:mm:ss.ff OleDbParameter.OleDbType = OleDbType.DateTime 42 DO.NET OE DB

49 OE DB.NET Data Proider TimeSpan DateTime DateTime / 11:20:30 DateTime TimeSpan TimeSpan DateTime TimeSpan onertible p1.oledbtype = OleDbType.DBTimeStamp; p1.value = "11:20:30"; rowsffected = cmd.executenonquery(); Dataecord.GetValue() OleDbDataeader.GetTimeSpan() TimeSpan ts1 = ((OleDbDataeader)reader).GetTimeSpan( 0 ); TimeSpan ts2 = (TimeSpan) reader.getvalue( 0 ); OE DB.NET Data Proider DOecordset DOecordset DOecordset DOecordset DO addbtime.net Framework DateTime OleDbType.DBTime TimeSpan TimeSpan DOecordset Time DOecordset Time DateTime TimeSpan DOecordset Method s type signature is not nterop compatible. Time DateTime DateTime String OleDbDatadapter DataSet DOecordset DOecordset Time DataSet TimeSpan ecordsets MissingSchemaction.ddWithKey DataSet ecordset 1 DO.NET 43

50 ODB.NET Data Proider ODB.NET Data Proider DB2 ODB ODB.NET Data Proider BM Data Serer Proider for.net ODB.NET Data Proider ODB.NET Data Proider DO.NET OE DB ODB.NET Data Proider ODB.NET Data Proider.NET Framework DB2 Uniersal Database for S/400 V54 P ODB.NET Data Proider 1 6. ODB.NET Data Proider onnectionstring DSN DB2 UD user D DB2 D PWD password DB2 D : onnectionstring Microsoft Odbconnection SMPE [Visual Basic.NET] Dim con s New Odbconnection("DSN=sample;UD=userid;PWD=password;") con.open() [#] Odbconnection con = new Odbconnection("DSN=sample;UD=userid;PWD=password;"); con.open() ODB.NET Data Proider ODB.NET Data Proider BM Data Serer Proider for.net ODB.NET Data Proider 44 DO.NET OE DB

51 7. ODB.NET Data Proider S DbType.nsiString DbType.nsiStringFixedength OdbcParameters S DB2 ODB.NET Data Proider "Specified cast is not alid" ommand.prepare : DbType.nsiString DbType.nsiStringFixedength DbType.Binary ommandtext (ommand.executenonquery ommand.executeeader) Odbcommand.Prepare() Odbcommand.Prepare() ODB.NET Data Proider ommandtext [#] command.ommandtext="select OB( B ) from table1"; command.prepare(); command.executeeader(); command.ommandtext="select OB( XYZ ) from table2"; // This ends up re-executing the first statement command.executeeader(); 1 DO.NET 45

52 7. ODB.NET Data Proider () ommandbehaior. Sequentialccess ommandbehaior.sequentialccess Dataeader.Gethars() equested range extends past the end of the array. at System.untime.nteropSerices.Marshal.opy(nt32 source, har[] destination, nt32 startndex, nt32 length) at System.Data.Odbc.OdbcDataeader.Gethars(nt32 i, nt64 datandex, har[] buffer, nt32 bufferndex, nt32 length) at Oleestrict.TestGetharsndBufferSize(Dbonnection con) DB2 ETE TBE mytable(c0 int, c1 OB(10K)) SEET c1 FOM mytable; [#] cmd.ommandtext = "SEET c1 from mytable"; Dataeader reader = cmd.executeeader(ommandbehaior.sequentialccess); nt32 ihunksize = 10; nt32 ibuffersize = 10; nt32 ifieldoffset = 0; har[] buffer = new har[ ibuffersize ]; reader.ead(); reader.gethars(0, ifieldoffset, buffer, 0, ihunksize); Gethars() "equested range extends past the end of the array" Gethars() BufferSize nt32 ibuffersize = 10000; ommandbehaior. Sequentialccess ommandbehaior. Sequentialccess ibuffersize 10,000 OB c1 10K ODB.NET Data Proider OdbcDataeader.Gethars() NO_DT - no error information aailable at System.Data.Odbc.Odbconnection.HandleError( Handleef hrhandle, SQ_HNDE htype, ETODE retcode) at System.Data.Odbc.OdbcDataeader.GetData( nt32 i, SQ_ sqlctype, nt32 cb) at System.Data.Odbc.OdbcDataeader.Gethars( nt32 i, nt64 datandex, har[] buffer, nt32 bufferndex, nt32 length) OdbcDataeader.Gethars() 5000 ODB.NET Data Proider Object reference not set to an instance of an object. at System.untime.nteropSerices.Marshal.opy(nt32 source, har[] destination, nt32 startndex, nt32 length) at System.Data.Odbc.OdbcDataeader.Gethars( nt32 i, nt64 datandex, har[] buffer, nt32 bufferndex, nt32 length) at Oleestrict.TestGetharsndBufferSize(Dbonnection con) 46 DO.NET OE DB

53 7. ODB.NET Data Proider () DataolumnMapping ODB.NET Data Proider ODB Drier Manager MSDN ODB Programmer's eference MSDN U SQType Decimal OdbcParameter OdbcType.Decimal ODB.NET Data Proider OdbcType.Decimal SQ WH SQ_VH SQType [#] cmd.ommandtext = "SEET dec_col FOM MYTBE WHEE dec_col >?"; OdbcParameter p1 = cmd.reateparameter(); p1.dbtype = DbType.Decimal; p1.value = 10.0; cmd.parameters.dd(p1); Dataeader rdr = cmd.executeeader(); EO [07006] [BM][ Drier][SQDS/VM] SQ0301N The alue of input host ariable or parameter number "" cannot be used because of its data type. SQSTTE=07006 DB2 DB2 for VM/VSE : OdbcParameter ( MYSHEM.MYTBE) D ODB.NET Data Proider D ETE TBE USED2.TBE1(c1 NT NOT NU PMY KEY); [#] // onnect as user bob odbcon = new Odbconnection("DSN=sample;UD=bob;PWD=mypassword"); Odbcommand cmd = odbcon.reateommand(); // Select from table with schema USED2 cmd.ommandtext="seet * FOM USED2.TBE1"; // Fails - No key info retrieed da.fillschema(ds, SchemaType.Source); // Fails - SchemaTable has no primary key cmd.executeeader(ommandbehaior.keynfo) // Throws exception because no primary key cbuilder.getupdateommand(); 1 DO.NET 47

54 7. ODB.NET Data Proider () ODB.NET Data Proider Dataeader ODB.NET Data Proider Dataeader SQPrimaryKeys() SQStatistic() DB2 for VM/VSE Dataeader.ead() Dataeader System.Data.Odbc.OdbcException: EO [HY010] [BM][ Drier] 0125E Function sequence error. SQSTTE=HY010 DB2 DB2 for VM/VSE : [#] Odbcommand cmd = odbcon.reateommand(); OdbcDatadapter da = new OdbcDatadapter(cmd); cmd.ommandtext = "SEET * FOM MYTBE"; // Use FillSchema to retriee just the schema information da.fillschema(ds, SchemaType.Source); // Use FillSchema to retriee just the schema information da.fill(ds); SQ ( ) DB2 for OS/390 DB2 for OS/400 DB2 for VM/VSE SQ ODB.NET Data Proider SQ SQPrimaryKeys SQStatistics ODB.NET Data Proider SEET DB2 for OS/390 DB2 for OS/400 DB2 for VM/VSE 48 DO.NET OE DB

55 7. ODB.NET Data Proider () OB ODB.NET Data Proider OB DB2 SQ_OB (-99) SQ_BOB (-98) SQ_DBOB (-350) ODB.NET Data Proider "Unknown SQ type - -98" (Blob ) "Unknown SQ type - -99" (lob ) "Unknown SQ type " (Dblob ) OB : /ODB ongdataompat 1 ODB.NET Data Proider SQ_OB SQ_ONGVH SQ_BOB SQ_ONGVBNY SQ_DBOB SQ_WONGVH ongdataompat DB2 DB2 db2 update cli cfg for section common using longdatacompat 1 [#] Odbconnection con = new Odbconnection("DSN=SMPE;UD=uid;PWD=mypwd;ONGDTOMPT=1;"); DB2 Odbcommand.ancel OdbcommandBuilder /ODB DB2 UD /ODB Odbcommand.ancel "EO [24000] [Microsoft][ODB Drier Manager] nalid cursor state" OdbcommandBuilder OdbcommandBuilder SQGetnfo SQ_SEH_PTTEN_ESPE ODB.NET Data Proider ndex was outside the bounds of the array. at System.Data.Odbc.Odbconnection.get_Escapehar() at System.Data.Odbc.OdbcDataeader.GetTableNameFromommandText() at System.Data.Odbc.OdbcDataeader.BuildMetaDatanfo() at System.Data.Odbc.OdbcDataeader.GetSchemaTable() at System.Data.ommon.ommandBuilder.Buildache( Boolean closeonnection) at System.Data.Odbc.OdbcommandBuilder.GetUpdateommand() DB2 for OS/390 DBS ; DB2 for VM/VSE DBS 1 DO.NET 49

56 7. ODB.NET Data Proider () OdbcommandBuilder OdbcommandBuilder UPDTE DEETE NSET () DB2 ( ) SQ ( ) DB2 "db2 create table mytable (c1 int) " DB2 mytable MYTBE OdbcommandBuilder [#] Odbcommand cmd = odbcon.reateommand(); cmd.ommandtext = "SEET * FOM MYTBE"; OdbcDatadapter da = new OdbcDatadapter(cmd); OdbcommandBuilder cb = new OdbcommandBuilder(da); Odbcommand updatemd = cb.getupdateommand(); OdbcommandBuilder "Dynamic SQ generation for the Updateommand is not supported against a Selectommand that does not return any key column information." SEET OdbcommandBuilder E FOT DOUBE TMESTMP DB2 for OS/390 DB2 for OS/400 DB2 for VM/VSE OdbcommandBuilder. DerieParameters SEET WHEE DerieParameters() SQProcedureolumns ommandtext ommandtext ( ODB ) SQProcedureolumns ODB "{ myproc(?) }" OdbcommandBuilder. DerieParameters OdbcommandBuilder. DerieParameters DerieParameters() ommandtext ( cmd.ommandtext = "MYPO") DerieParameters() ommandtext ODB ODB.NET Data Proider eturnvalue 50 DO.NET OE DB

57 7. ODB.NET Data Proider () OdbcommandBuilder. DerieParameters OdbcommandBuilder. DerieParameters Odbconnection. hangedatabase Odbconnection. onnectionstring OdbcDataeader. GetSchemaTable DerieParameters() ommandtext = "MYSHEM.MYPO" DerieParameters() DerieParameters() SQProcedureolumns Odbconnection.hangeDatabase() Serer onnect Timeout Pooling Min Pool Size Max Pool Size onnection ifetime onnection eset ODB.NET Data Proider ( ) Dataeader.GetSchemaTable() seadonly DB2 DB2 for OS/390, 7 DB2 for OS/400 DB2 for VM/VSE sunique sutoncrement BaseSchemaName BaseatalogName ODB VH(10) MYPO [#] Odbcommand cmd = odbcon.reateommand(); cmd.ommandtype = ommandtype.text; cmd.ommandtext = "{ MYPO(?) }" OdbcParameter p1 = cmd.reateparameter(); p1.value = "Joe"; p1.odbctype = OdbcType.NVarhar; cmd.parameters.dd(p1); cmd.executenonquery(); : : ommandtype.storedprocedure ODB MSDN Odbcommand.ommandText DB2 for OS/ ODB.NET Data Proider ( "1" "2" "3") MSDN ("olumn1", "olumn2", "olumn3") ODB.NET Data Proider NU MSDN MSDN NU DB2 for OS/ DO.NET 51

58 52 DO.NET OE DB

59 2 BM OE DB Proider for DB2 BM OE DB Proider for DB2 DB2 OE DB Proider OE DB2 OE DB2 Microsoft OE DB OE/OM OE DB OE DB onsumer OE DB Proider OE DB onsumer OE DB OE DB Proider OE DB BM OE DB Proider for DB2 (Proider BMDDB2) OE DB onsumer DB2 DB2 onnect OE DB consumer DB2 for z/os DB2 Serer for VM and VSE DB2 Uniersal Database for S/400 DBMS BM OE DB Proider for DB2 OE DB Proider 0 1 Proider 1 DB2 BM OE DB Proider OE DB ( DB2 ) BM OE DB Proider for DB2 Microsoft OE DB 2.0 Microsoft Press Microsoft OE DB 2.0 Programmer's eference and Data ccess SDK BM OE DB Proider for DB2 Microsoft OE DB 2.7 Windows BM OE DB Proider for DB2 opyright BM orp. 2006,

60 BM OE DB Proider for DB2 DB2 BM Data Serer Drier for ODB and BM Data Serer Drier Package BM OE DB Proider for DB2 BM OE DB Proider for DB2 BM OE DB Proider for DB2 DO Microsoft Visual Studio ++ Microsoft Visual Basic OE DB.NET Data Proider DO.NET OE DB BMDDB2 /++ Data ccess onsumer Object T OM ppwizard T OE DB BM OE DB Proider BM OE DB Proider for DB2 1 BM OE DB Proider SequentialStream BMDDB2 (DBTYPE_UNKNOWN) SequentialStream DBBNDNG DBOBJET dwflag STGM_ED BMDDB2 SequentialStream ead SequentialStream ead Unknown 54 DO.NET OE DB

61 BM OE DB Proider DBSchemaowset NU 8. BM OE DB Proider for DB2 GUD DBSHEM _OUMN_PVEGES OUMN_NME TBE_NME TBE_SHEM OUMN_NME GNTEE GNTO S_GNTBE PVEGE_TYPE TBE_NME TBE_SHEM DBSHEM_OUMNS OUMN_NME TBE_NME TBE_SHEM HTE_MXMUM_ENGTH HTE_OTET_ENGTH OUMN_DEFUT OUMN_FGS OUMN_HSDEFUT OUMN_NME DT_TYPE DESPTON S_NUBE NUME_PESON NUME_SE ODN_POSTON TBE_NME TBE_SHEM DBSHEM_FOEGN_KEYS FK_TBE_NME FK_TBE_SHEM PK_TBE_NME PK_TBE_SHEM DEFEBTY DEETE_UE FK_OUMN_NME FK_NME FK_TBE_NME FK_TBE_SHEM ODN PK_OUMN_NME PK_NME PK_TBE_NME PK_TBE_SHEM UPDTE_UE PK_TBE_NME FK_TBE_NME 1 "%" 2 OE DB 55

62 8. BM OE DB Proider for DB2 () GUD DBSHEM_NDEXES TBE_NME TBE_SHEM DNTY USTEED OTON OUMN_NME NDEX_NME NDEX_SHEM ODN_POSTON PGES TBE_NME TBE_SHEM TYPE UNQUE DBSHEM_PMY_KEYS TBE_NME TBE_SHEM OUMN_NME ODN PK_NME TBE_NME TBE_SHEM TBE_NME "%" DBSHEM _POEDUE_PMETES PMETE_NME POEDUE_NME POEDUE_SHEM HTE_MXMUM_ENGTH HTE_OTET_ENGTH DT_TYPE DESPTON S_NUBE NUME_PESON NUME_SE ODN_POSTON PMETE_DEFUT PMETE_HSDEFUT PMETE_NME PMETE_TYPE POEDUE_NME POEDUE_SHEM TYPE_NME DBSHEM_POEDUES POEDUE_NME POEDUE_SHEM DESPTON POEDUE_NME POEDUE_SHEM POEDUE_TYPE 56 DO.NET OE DB

63 8. BM OE DB Proider for DB2 () GUD DBSHEM_POVDE_TYPES DT_TYPE BEST_MTH UTO_UNQUE_VUE BEST_MTH SE_SENSTVE ETE_PMS OUMN_SZE DT_TYPE FXED_PE_SE S_FXEDENGTH S_ONG S_NUBE TE_PEFX TE_SUFFX O_TYPE_NME MNMUM_SE MXMUM_SE SEHBE TYPE_NME UNSGNED_TTBUTE DBSHEM_STTSTS TBE_NME TBE_SHEM DNTY TBE_NME TBE_SHEM DBSHEM _TBE_PVEGES TBE_NME TBE_SHEM GNTEE GNTO S_GNTBE PVEGE_TYPE TBE_NME TBE_SHEM DBSHEM_TBES TBE_NME TBE_SHEM TBE_TYPE DESPTON TBE_NME TBE_SHEM TBE_TYPE BM OE DB Proider OE DB BM OE DB Proider for DB2 D (SD) DWOD 0xFFFFFFFF OEDB_SEVES OE DB DWOD OE DB 9. OE DB () utoenlistment DWOD 0xFFFFFFFF 0xFFFFFFF 2 OE DB 57

64 9. OE DB () DWOD 0xFFFFFFFB 0xFFFFFFF8 0x BM OE DB Proider DB2 BM OE DB Proider DB2 OE DB BM OE DB Proider for DB2 DB2 OE DB 10. DB2 OE DB DB2 OE DB OE DB DB2 SMNT DBTYPE_2 "DBTYPE_2" "SMNT" NTEGE DBTYPE_4 "DBTYPE_4" "NTEGE" "NT" BGNT DBTYPE_8 "DBTYPE_8" "BGNT" E DBTYPE_4 "DBTYPE_4" "E" FOT DBTYPE_8 "DBTYPE_8" "FOT" DOUBE DBTYPE_8 "DBTYPE_8" "DOUBE" "DOUBE PESON" DEM DBTYPE_NUME "DBTYPE_NUME" "DE" "DEM" NUME DBTYPE_NUME "DBTYPE_NUME" "NUM" "NUME" DTE DBTYPE_DBDTE "DBTYPE_DBDTE" "DTE" TME DBTYPE_DBTME "DBTYPE_DBTME" "TME" TMESTMP DBTYPE_DBTMESTMP "DBTYPE_DBTMESTMP" "TMESTMP" H DBTYPE_ST "DBTYPE_H" "H" "HTE" VH DBTYPE_ST "DBTYPE_VH" "VH" ONG VH DBTYPE_ST "DBTYPE_ONGVH" "ONG VH" 58 DO.NET OE DB

65 10. DB2 OE DB () DB2 OE DB OE DB DB2 OB "OB" DBTYPE_ST DBOUMNFGS_SONG DBPMFGS_SONG "DBTYPE_H" "DBTYPE_VH" "DBTYPE_ONGVH" DBOUMNFGS_SONG DBPMFGS_SONG GPH DBTYPE_WST "DBTYPE_WH" "GPH" VGPH DBTYPE_WST "DBTYPE_WVH" "VGPH" ONG VGPH DBOB H(n) FO BT DT VH(n) FO BT DT ONG VH FO BT DT BOB DBTYPE_WST "DBTYPE_WONGVH" "ONG VGPH" DBTYPE_WST DBOUMNFGS_SONG DBPMFGS_SONG DBTYPE_BYTES DBTYPE_BYTES DBTYPE_BYTES DBTYPE_BYTES DBOUMNFGS_SONG DBPMFGS_SONG "DBTYPE_WH" "DBTYPE_WVH" "DBTYPE_WONGVH" DBOUMNFGS_SONG DBPMFGS_SONG "DBTYPE_BNY" "DBTYPE_VBNY" "DBTYPE_ONGVBNY" "DBTYPE_BNY" "DBTYPE_VBNY" "DBTYPE_ONGVBNY" DBOUMNFGS_SONG DBPMFGS_SONG "DBOB" "BOB" OE DB DB2 BM OE DB Proider for DB2 OE DB DB2 OE DB DB2 OE DB DB2 2 OE DB 59

66 11. OE DB DB2 OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_EMPTY DBTYPE_NU DBTYPE_ESEVED DBTYPE_1 X X X X X X X X DBTYPE_2 X X X X X X X X DBTYPE_4 X X X X X X X X DBTYPE_8 X X X X X X X X DBTYPE_U1 X X X X X X X X DBTYPE_U2 X X X X X X X X DBTYPE_U4 X X X X X X X X DBTYPE_U8 X X X X X X X X DBTYPE_4 X X X X X X X X DBTYPE_8 X X X X X X X X DBTYPE_Y DBTYPE_DEM X X X X X X X X DBTYPE_NUME X X X X X X X X 60 DO.NET OE DB

67 11. OE DB DB2 () OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_DTE DBTYPE_BOO X X X X X X X X DBTYPE_BYTES X X X X X X X X X DBTYPE_BST - DBTYPE_ST X X X X X X X X X X X X X X X X X X X DBTYPE_WST X X X DBTYPE_VNT - DBTYPE_DSPTH DBTYPE_UNKNOWN X X X X X X X X X X X DBTYPE_GUD DBTYPE_EO DBTYPE_BYEF DBTYPE_Y DBTYPE_VETO DBTYPE_UDT 2 OE DB 61

68 11. OE DB DB2 () OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_DBDTE X X X X DBTYPE_DBTME X X X X DBTYPE_DBTMESTMP X X X X X DBTYPE_FETME DBTYPE_POP_VNT DBTYPE_HHPTE DBTYPE_VNUME DB2 OE DB BM OE DB Proider DB2 OE DB DB2 OE DB DB2 OE DB 62 DO.NET OE DB

69 12. DB2 OE DB OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_EMPTY DBTYPE_NU DBTYPE_ESEVED DBTYPE_1 X X X X X X X X X X X X X X X DBTYPE_2 X X X X X X X X X X X X X X X DBTYPE_4 X X X X X X X X X X X X X X X DBTYPE_8 X X X X X X X X X X X X X X X X DBTYPE_U1 X X X X X X X X X X X X X X X DBTYPE_U2 X X X X X X X X X X X X X X X DBTYPE_U4 X X X X X X X X X X X X X X X DBTYPE_U8 X X X X X X X X X X X X X X X X DBTYPE_4 X X X X X X X X X X X X X X X DBTYPE_8 X X X X X X X X X X X X X X X DBTYPE_Y X X X X X X X X X X X X X X X DBTYPE_DEM X X X X X X X X X X X X X X X DBTYPE_NUME X X X X X X X X X X X X X X X 2 OE DB 63

70 12. DB2 OE DB () OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_DTE X X X X X X X X X X X X X X DBTYPE_BOO X X X X X X X X X X X X X X X DBTYPE_BYTES X X X X X X X X X X X X X X X X X X DBTYPE_BST X X X X X X X X X X X X X X X X X X X DBTYPE_ST X X X X X X X X X X X X X X X X X X X DBTYPE_WST X X X X X X X X X X X X X X X X X X X DBTYPE_VNT X X X X X X X X X X X X X X X X X X X DBTYPE_DSPTH DBTYPE_UNKNOWN X X X X X X X X X X X X X X X X X X X X X X DBTYPE_GUD X X X X X X X X X X DBTYPE_EO DBTYPE_BYEF DBTYPE_Y DBTYPE_VETO DBTYPE_UDT DBTYPE_DBDTE X X X X X X X X X X X X X 64 DO.NET OE DB

71 12. DB2 OE DB () OE DB DB2 S M N T N T E G E B G N T E F O T D O U B E D E M N U M E D T E T M E T M E S T M P H V H O N G V H O B G P H V G P H O N G V G P H D B O B B O B D T N K H V H O N G V H DBTYPE_DBTME X X X X X X X X X X DBTYPE_DBTMESTMP X X X X X X X X X X X X X DBTYPE_FETME X X X X X X X X X X X X X X DBTYPE_POP_VNT X X X X X X X X X X X X X X X DBTYPE_HHPTE DBTYPE_VNUME : SequentialStream::ead DBTYPE_ST DBOB DBTYPE_WH BM OE DB Proider BM OE DB Proider BMDDB2 Transactionocal estartposition 2 OE DB 65

72 BMDDB2 DBD (Openowset ) OE DB onsumer BM OE DB Proider OE DB BM OE DB Proider for DB2 Microsoft OE DB Proider for ODB OE DB 13. BOB DB2 ODB Proider SequentialStream 14. DB2 ODB Proider ccessor ommand ommandpersist ommandprepare ommandproperties ommandtext ommandwithparameters olumnsnfo olumnsowset onerttype SupportErrornfo 15. DB2 ODB Proider onnectionpoint DBsynchNotify (onsumer) DBsynchStatus DBonnectionPointontainer DBreateSession DBDataSourcedmin DBnfo DBnitialize DBProperties Persist PersistFile 66 DO.NET OE DB

73 15. () DB2 ODB Proider SupportErrornfo 16. DB2 ODB Proider DBnitialize DBProperties ParseDisplayName Sourcesowset SupportErrornfo 17. DB2 ODB Proider ErrorookUp 18. DB2 ODB Proider Errornfo SQErrornfo ( ) 19. DB2 ODB Proider Multipleesults SupportErrornfo 20. DB2 ODB Proider ccessor olumnsowset olumnsnfo onerttype hapteredowset onnectionpointontainer DBsynchStatus Parentowset owset owsethange owsethaptermember owsetfind owsetdentity owsetndex 2 OE DB 67

74 20. () DB2 ODB Proider owsetnfo owsetocate owsetnotify (onsumer) owsetefresh ursor Serice omponent owsetesynch ursor Serice omponent owsetscroll 1 owsetupdate ursor Serice omponent owsetview SupportErrornfo : DB2 ODB Proider lterndex ltertable DBreateommand DBSchemaowset GetDataSource ndexdefinition Openowset SessionProperties SupportErrornfo TableDefinition TableDefinitionWithonstraints Transaction TransactionJoin Transactionocal TransactionObject TransactionOptions 22. DB2 ODB Proider Viewhapter ViewFilter Viewowset ViewSort 68 DO.NET OE DB

75 BM OE DB Proider OE DB BM OE DB Proider for DB2 OE DB 23. BM OE DB Proider for DB2 : (DBPOPSET_DTSOUE) /W DBPOP_MUTPEONNETONS VNT_FSE DBPOP_ESETDTSOUE DBPOPV_D_ESET /W 24. BM OE DB Proider for DB2 : DB2 (DBPOPSET_DB2DTSOUE) /W DB2POP_EPOTSONGFOONGTYPES VNT_FSE /W DB2POP_ETUNHSWH VNT_TUE /W DB2POP_SOTBYODN VNT_FSE /W 25. BM OE DB Proider for DB2 : (DBPOPSET_DTSOUENFO) /W 0 DBPOP_TVESESSONS DBPOP_SYNTXNBOT VNT_FSE DBPOP_SYNTXNOMMT VNT_FSE DBPOP_BYEFESSOS VNT_FSE DBPOP_OUMNDEFNTON DBPOPV_D_NOTNU DBPOP_ONTNUBEHVO DBPOPV_B_NU DBPOP_ONNETONSTTUS DBPOPV_S_NTZED DBPOP_DTSOUENME DBPOP_DTSOUEEDONY VNT_FSE DBPOP_DBMSNME DBPOP_DBMSVE DBPOP_DSOTHEDMODE DBPOPV_T_FEETHED DBPOP_GOUPBY DBPOPV_GB_ONTNS_SEET DBPOP_DENTFESE DBPOPV UPPE DBPOP_MXNDEXSZE 0 DBPOP_MXOWSZE 0 DBPOP_MXOWSZENUDESBOB VNT_TUE DBPOP_MXTBENSEET 0 DBPOP_MUTPEPMSETS VNT_FSE DBPOP_MUTPEESUTS DBPOPV_M_SUPPOTED DBPOP_MUTPESTOGEOBJETS VNT_TUE DBPOP_MUTTBEUPDTE VNT_FSE 2 OE DB 69

76 25. BM OE DB Proider for DB2 : (DBPOPSET_DTSOUENFO) () /W DBPOP_NUOTON DBPOPV_N_OW DBPOP_OEOBJETS DBPOPV_OO_BOB DBPOP_ODEBYOUMNSNSEET VNT_FSE DBPOP _OUTPUTPMETEVBTY DBPOPV_O_TEXEUTE DBPOP_PESSTENTDTYPE DBPOPV_PT_NME DBPOP_PEPEBOTBEHVO DBPOPV_B_DEETE DBPOP_POEDUETEM "STOED POEDUE" DBPOP_POVDEFENDYNME "BM OE DB Proider for DB2" DBPOP_POVDENME "BMDDB2.D" DBPOP_POVDEOEDBVE "02.7" DBPOP_POVDEVE DBPOP_QUOTEDENTFESE DBPOPV SENSTVE DBPOP _OWSETONVESONSONOMMND VNT_TUE DBPOP_SHEMTEM "SHEM" DBPOP_SHEMUSGE DBPOPV_SU_DM_STTEMENTS DBPOPV_SU_TBE_DEFNTON DBPOPV_SU_NDEX_DEFNTON DBPOPV_SU_PVEGE_DEFNTON DBPOP_SQSUPPOT DBPOPV_SQ_ODB_EXTENDED DBPOPV_SQ_ESPEUSES DBPOPV_SQ_NS92_ENTY DBPOP_SEVENME DBPOP_STUTUEDSTOGE DBPOPV_SS_SEQUENTSTEM DBPOP_SUBQUEES DBPOPV_SQ_OETEDSUBQUEES DBPOPV_SQ_OMPSON DBPOPV_SQ_EXSTS DBPOPV_SQ_N DBPOPV_SQ_QUNTFED DBPOP_SUPPOTEDTXNDD DBPOPV_T_ DBPOP_SUPPOTEDTXNSOEVES DBPOPV_T_USOSTBTY DBPOPV_T_EDOMMTTED DBPOPV_T_EDUNOMMTTED DBPOPV_T_SEZBE DBPOP_SUPPOTEDTXNSOETN DBPOPV_T_OMMT_D DBPOPV_T_BOT_NO 70 DO.NET OE DB

77 25. BM OE DB Proider for DB2 : (DBPOPSET_DTSOUENFO) () /W DBPOP_TBETEM "TBE" DBPOP_USENME 26. BM OE DB Proider for DB2 : (DBPOPSET_DBNT) /W DBPOP_UTH_PSSWOD /W DBPOP_NT_TMEOUT (1) 0 /W DBPOP_UTH_PESST _SENSTVE_UTHNFO VNT_FSE DBPOP_UTH_USED /W DBPOP_NT_DTSOUE /W DBPOP_NT_HWND /W DBPOP_NT_MODE DB_MODE_EDWTE /W DBPOP_NT_OEDBSEVES 0xFFFFFFFF /W DBPOP_NT_POMPT DBPOMPT_NOPOMPT /W DBPOP_NT_POVDESTNG /W /W 27. BM OE DB Proider for DB2 : (DBPOPSET_OWSET) /W DBPOP_BOTPESEVE VNT_FSE DBPOP_ESSODE DBPOPV_O_NDOM DBPOP_BOKNGSTOGEOBJETS VNT_FSE DBPOP_BOOKMKS VNT_FSE /W DBPOP_BOOKMKSKPPED VNT_FSE DBPOP_BOOKMKTYPE DBPOPV_BMK_NUME DBPOP_HEDEFEED VNT_FSE /W DBPOP_NFETHBKWDS VNT_FSE /W DBPOP_NHODOWS VNT_FSE DBPOP_NSOBKWDS VNT_FSE /W DBPOP_HNGENSETEDOWS VNT_FSE DBPOP_OMMTPESEVE VNT_TUE /W DBPOP_OMMNDTMEOUT 0 /W DBPOP_DEFEED VNT_FSE DBPOP_ccessor VNT_TUE DBPOP_olumnsnfo VNT_TUE DBPOP_olumnsowset VNT_TUE /W DBPOP_onertType VNT_TUE DBPOP_Multipleesults VNT_FSE /W DBPOP_owset VNT_TUE 2 OE DB 71

78 27. BM OE DB Proider for DB2 : (DBPOPSET_OWSET) () /W DBPOP_owhange VNT_FSE /W DBPOP_owsetFind VNT_FSE DBPOP_owsetdentity VNT_TUE DBPOP_owsetnfo VNT_TUE DBPOP_owsetocate VNT_FSE /W DBPOP_owsetScroll VNT_FSE /W DBPOP_owsetUpdate VNT_FSE DBPOP_SequentialStream VNT_TUE DBPOP_SupportErrornfo VNT_TUE DBPOP_TEBOOKMKS VNT_FSE DBPOP_TEDENTTY VNT_TUE DBPOP_OKMODE DBPOPV_M_SNGEOW /W DBPOP_MXOPENOWS DBPOP_MXOWS 0 /W DBPOP_NOTFTONGNUTY DBPOPV_NT_SNGEOW /W DBPOP_NOTFTON PHSES DBPOPV_NP_OKTODO DBPOPB_NP_BOUTTODO DBPOPV_NP_SYNHFTE DBPOPV_NP_FEDTODO DBPOPV_NP_DDEVENT DBPOP_NOTFYOWSETEESE DBPOP _NOTFYOWSETFETHPOSTONHNGE DBPOP_NOTFYOUMNSET DBPOP_NOTFYOWDEETE DBPOP_NOTFYOWNSET DBPOPV_NP_OKTODO DBPOPV_NP_BOUTTODO DBPOPV_NP_OKTODO DBPOPV_NP_BOUTTODO DBPOPV_NP_OKTODO DBPOPV_NP_BOUTTODO DBPOPV_NP_OKTODO DBPOPV_NP_BOUTTODO DBPOPV_NP_OKTODO DBPOPV_NP_BOUTTODO DBPOP_ODEEDBOOKMKS VNT_FSE DBPOP_OTHENSET VNT_FSE DBPOP_OTHEUPDTEDEETE VNT_FSE /W DBPOP_OWNNSET VNT_FSE DBPOP_OWNUPDTEDEETE VNT_FSE DBPOP_QUKESTT VNT_FSE /W DBPOP_EMOVEDEETED VNT_FSE /W DBPOP_OWTHEDMODE DBPOPV_T_FEETHED 72 DO.NET OE DB

79 27. BM OE DB Proider for DB2 : (DBPOPSET_OWSET) () /W DBPOP_SEVEUSO VNT_TUE DBPOP_SEVEDTONNSET VNT_FSE DBPOP_UNQUEOWS VNT_FSE /W DBPOP_UPDTBTY 0 /W 28. BM OE DB Proider for DB2 : DB2 (DBPOPSET_DB2OWSET) /W DBPOP_SONGMNENGTH /W 29. BM OE DB Proider for DB2 : (DBPOPSET_SESSON) /W DBPOP_SESS_UTOOMMTSOEVES DBPOPV_T_USOSTBTY /W : 1. TP/P TP/P sock sock BM OE DB Proider BM OE DB Proider for DB2 DB2 1: DO Visual Basic Dim db s DODB.onnection Set db = New DODB.onnection db.proider = "BMDDB2" db.ursorocation = aduselient... 2 Datanitialize Serice omponent /++ hr = oreatenstance ( SD_MSDNTZE, NU, STX_NPO_SEVE, D_Datanitialize, (oid**)&pdatanitialize); hr = pdatanitialize->reatedbnstance( SD_BMDDB2, // lassd of BMDDB2 NU, 2 OE DB 73

80 STX_NPO_SEVE, NU, D_DBnitialize, (Unknown**)&pDBnitialize); DO DO ctiex Data Objects (DO) Proider () keyword=alue (;) BM OE DB Proider for DB2 30. BM OE DB Proider for DB2 DSN DB2 UD D DB2 D PWD UD DB2 D BM OE DB Proider Visual Basic DO DB2 BM OE DB Proider DB2 BMDDB2 Proider DO DO aduselient aduseserer aduseserer BM OE DB Proider for DB2 74 DO.NET OE DB

81 DO DO DO DO Parameters.efresh DB2 Serer for VSE &VM ddnew() Fieldlist Values Update() ddnew() ddnew() Update() ddnew() ONG OB BM OE DB Proider DO 31. BM OE DB Proider DO / OE DB / BM OE DB ancel ommand reateparameter Execute 32. / OE DB / BM OE DB ctieonnection (DO ) ommand Text ommandtext ommand Timeout ommandproperties::setproperties DBPOP_OMMNDTMEOUT ommandtype (DO ) Prepared ommandprepare 2 OE DB 75

82 32. () / State OE DB / (DO ) BM OE DB 33. / OE DB / BM OE DB ommandwithparameter DBSHEM _POEDUE_PMETES Properties ommandproperties DBProperties 34. / OE DB / BM OE DB BeginTrans ommittrans ollbacktrans Transactionocal () () () Execute ommand Openowset Open DBreateSession DBnitialize OpenSchema adschemaolumnpriileges adschemaolumns adschemaforeignkeys adschemandexes adschemaprimarykeys adschemaprocedureparam adschemaprocedures adschemaproidertype adschemastatistics adschematablepriileges adschematables ancel DBSchemaowset 76 DO.NET OE DB

83 35. / OE DB / BM OE DB ttributes adxactommitetaining adxactollbacketaining Transactionocal ommandtimeout onnectionstring ommandproperties DBPOP_OMMND_TMEOUT (DO ) onnectiontimeout DBProperties DBPOP_NT_TMEOUT ursorocation: aduselient adusenone aduseserer (OE DB ursor Serice ) () DefaultDataBase DBProperties DBPOP_UENTTOG solationeel Transactionocal DBPOP_SESS _UTOOMMTSOEVES Mode admodeead admodeeadwrite admodesharedenynone admodesharedenyead admodesharedenywrite admodeshareexclusie admodeunknown admodewrite DBProperties DBPOP_NT_MODE Proider Sourceowset::GetSourceowset State (DO ) (DO ) 36. / OE DB / BM OE DB Errors Errorecords Properties DBProperties 2 OE DB 77

84 37. / Description NatieError Number Source SQState OE DB / Errorecords BM OE DB Helpontext HelpFile 38. / OE DB / BM OE DB ppendhunk Gethunk SequentialStream 39. / OE DB / BM OE DB ctual Size ccessor owset ttributes DataFormat DefinedSize Name NumericScale Precision olumnnfo OriginalValue owsetupdate () UnderlyingValue owsetefresh owsetesynch () () ccessor owset 78 DO.NET OE DB

85 40. / OE DB / BM OE DB Properties DBProperties owsetnfo 41. OE DB / / BM OE DB ppendhunk SequentialStream ttributes Direction Name NumericScale Precision Scale Size ommandwithparameter DBSHEM _POEDUE_PMETES ccessor ommand 42. / Properties OE DB / BM OE DB 43. / OE DB / BM OE DB ddnew owsethange ancel ancelbatch owsetupdate::undo () ancelupdate () lone owsetocate lose ccessor owset omparebookmarks Delete owsethange Getows ccessor owset 2 OE DB 79

86 43. () / Moe OE DB / owset owsetocate BM OE DB MoeFirst owset owsetocate MoeNext owset owsetocate Moeast owsetocate MoePreious owsetocate NextecordSet Multipleesults Open ommand Openowset equery ommand Openowset esync owsetefresh () Supports owsetnfo Update UpdateBatch owsethange owsetupdate () 44. / OE DB / BM OE DB bsolutepage owsetocate owsetscroll 1 bsoluteposition owsetocate owsetscroll 1 ctieonnection BOF DBreateSession DBnitialize (DO ) Bookmark ccessor owsetocate achesize owsetocate cows owset 80 DO.NET OE DB

87 44. () / OE DB / BM OE DB ursortype adopendynamic adopenforwardonly adopenkeyset adopenstatic ommandproperties EditMode owsetupdate () EOF (DO ) Filter owsetocate owsetview owsetupdate Viewhapter ViewFilter ocktype ommandproperties MarshallOption Maxecords ommandproperties Openowset Pageount owsetscroll 1 PageSize (DO ) Sort (DO ) Source (DO ) State (DO ) Status owsetupdate () : / OE DB / BM OE DB Fields olumnnfo Properties DBProperties owsetnfo::getproperties /++ BM OE DB Proider SD_BMDDB2 /++ ibmdadb2.h SQB include 2 OE DB 81

88 include DBNTONSTNTS /++ #define DBNTONSTNTS #include "ibmdadb2.h" BM OE DB Proider /++ /++ BM OE DB (BMDDB2) DB2 DBPromptnitialize Datanitialize oreatenstance P (OM) Datanitialize OE DB Serice omponent DBPromptnitialize Data inks omponent OM+ BM OE DB Proider Windows 2000 Microsoft omponent Serices (OM+) OE DB TransactionJoin DB2 for inux, UNX, and Windows System i OM+ BM OE DB Proider for DB2 OM+ : DB2 Windows Windows 2000 (Serice Pack 3 ) /++ OM+ ++ OM+ oreatenstance BMDDB2 JoinTransaction ++ DO OM DO.NET OE DB

89 OM+ Transactions equired equired New Supported OM+ 2 OE DB 83

90 84 DO.NET OE DB

91 3 BM Data Serer Proider for.net BM Data Serer Proider for.net BM Microsoft.NET Framework BM.Data.DB2 BM.Data.DB2Types BM.Data.nformix DB2 nformix BM nformix : nformix.net (BM.Data.nformix.dll) DB nformix DB2.NET (BM.Data.DB2.dll) BM.Data.DB2 BM.Data.DB2 DB2.NET DB2.NET BM.Data.DB2 imports using [Visual Basic] mports BM.Data.DB2 [#] using BM.Data.DB2; BM.Data.DB2.dll Visual Studio > csc bc BM.NET /r:install_dir bin netfxx BM.Data.BM.Data.DB2.dll 1 DB2onnection DB2.NET opyright BM orp. 2006,

92 DB2onnection.onnectionString DB2Datadapter DB2ommand DB2onnection DB2Dataeader DB2Bulkopy DB2BulkopyolumnMapping DB2BulkopyolumnMappingollection DB2ommand DB2ommandBuilder DB2onnection DB2onnectionStringBuilder DB2Datadapter DB2Dataeader DB2DataSourceEnumerator DB2Error DB2Errorollection DB2Exception DB2Factory DB2nfoMessageEentrgs SQ DataSet BM Data Serer Proider for.net DataSet BM DB2Datadapter BM System.Data.ommon nfomessage 86 DO.NET OE DB

93 DB2Parameter DB2Parameterollection DB2Permission DB2Permissionttribute DB2ecord DB2esultSet DB2owsopiedEentrgs DB2owUpdatedEentrgs DB2owUpdatingEentrgs DB2Transaction DB2Updatableecord DB2Xmldapter DB2ommand Dataolumn DB2ommand DataSet BM BM Data Serer Proider for.net DB2esultSet DB2owsopied owupdated owupdating SQ DB2esultSet XPathDocument DB2nfoMessageEentHandler DB2owsopiedEentHandler DB2owUpdatedEentHandler DB2owUpdatingEentHandler DB2onnection nfomessage DB2Bulkopy DB2owsopied DB2Datadapter owupdated DB2Datadapter owupdating 3 BM Data Serer Proider for.net 87

94 DB2BulkopyOptions DB2ursorType DB2esultSetOptions DB2Type DB2Bulkopy DB2BulkopyOptions DB2esultSet DB2ursorType DB2esultSet DB2esultSetOptions DB2Parameter BM.Data.DB2Types BM.Data.DB2Types.NET Framework Data Proider DB BM Data Serer Proider for.net BM Data Serer Proider for.net BM Microsoft.NET Framework BM.Data.DB2Types BM.Data.DB2Types.NET Framework Data Proider DB2 DB2Type DB2 nformix Microsoft.NET Framework DB2Types 88 DO.NET OE DB

95 46. DB2Types DB2Type DB2 nformix Numeric DB2nt16 Smallnt SMNT BOOEN, SMNT nt16 DB2nt32 nteger NT NTEGE, NT, nt32 SE DB2nt64 Bignt, BigSerial BGNT BGNT, BGSE, NT8, SE8 DB2eal, DB2eal370 eal E E, SMFOT Single DB2Double Double DOUBE PESON DEM ( 31), DOUBE PESON DB2Double Float FOT DEM (32), FOT.NET nt64 Double Double DB2Decimal Decimal DEM MONEY Decimal DB2Decimal Float DecimalFloat DEFOT (16 34) DB2Decimal Numeric DEM DEM ( 31), NUME Decimal Decimal Date/Time DB2Date Date DTE DTETME ( ) Datetime DB2Time Time TME DTETME ( ) TimeSpan DB2 TimeStamp DB2 TimeStampOffset Timestamp TMESTMP DTETME ( ) Timestamp WithTimeZone TMESTMP WTH TME ZONE DateTime DateTime Offset XM DB2Xml XmlfxType.Xml XM Byte[] DB2String har H H String DB2String Varhar VH VH String DB2String ongvarhar ONG VH VH String DB2Binary Binary H FO BT Byte[] DT DB2Binary Binary BNY Byte[] DB2Binary VarBinary VBNY Byte[] DB2Binary ong VarBinary ONG VH FO BT DT Byte[] DB2String Graphic GPH String DB2String VarGraphic VGPH String DB2String ong ONG String VarGraphic VGPH OB DB2lob lob OB OB, TEXT String DB2Blob Blob BOB BOB, BYTE Byte[] DB2lob Dblob DBOB String ow D DB2owd owd OWD Byte[] DB2oundingMode DB2DecimalFloat 6. DB2.NET 7. DB2Type.Xml DB2Parameterlass.ParameterName String byte[] DB2Xml Xmleader 8. DB2 for z/os 9 DB2 for i V61 9. DB2 for z/os 9 DB2 for inux, UNX, and Windows BM Data Serer Proider for.net 89

96 90 DO.NET OE DB

97 . DB2 DB2 DB2 BM Knowledge enter DB2 : ( ) DB2 : ( ) DB2 : PDF () PDF (DB2 PDF DVD ) : : BM Knowledge enter DB2 PDF BM Knowledge enter DB2 BM edbooks DB2 (ibm.com) DB2 nformation Management ( DB2 nformation Deelopment DB2 DB2 db2docs@ca.ibm.com E DB2 nformation Deelopment U opyright BM orp. 2006,

98 DB2 E DB2 BM DB2 ( PDF ) DB2 PDF BM Publications enter DB (PDF ) DB2 ( BM Publications enter ( publications/serlet/pbi.wss) DB2. BM Knowledge enter DB2 PDF 47. DB2 P S : S S S S S S S DO.NET OE DB

99 47. DB2 () DB2 DO.NET OE DB SQ Jaa Perl PHP Python uby on ails BM DF SQ DB2 (inux Windows ) DB2 BM 1 2 Net Search Extender S S S S S S S S S G G S G G S S S DB2 93

100 47. DB2 () S purexm S Spatial Extender S SQ : SQ 1 SQ 2 S S S Text Search S DB DB S S S XQuery S DB2 onnect DB2 onnect S DB2 onnect S SQ DB2 SQ SQSTTE SQSTTE SQ SQ SQ 94 DO.NET OE DB

101 ? sqlstate? class code sqlstate 5 SQ class code SQ 2? SQ 08003?08 08 DB2 DB2 DB2 BM Knowledge enter DB2 BM Knowledge enter ( U DB2 DB2 DB U welcome.html DB U welcome.html DB2 9.8 U welcome.html DB2 9.7 U welcome.html DB2 9.5 U welcome.html : BM Web. DB2 95

102 : BM () () : BM : BM BM BM BM : BM BM ibm.com nternational Business Machines orporation BM BM 96 DO.NET OE DB

103 B. BM BM BM BM BM BM BM BM ( ) BM BM BM Web Web Web BM Web BM opyright BM orp. 2006,

104 (i) () (ii) BM anada imited U59/ Steeles enue East Markham, Ontario 3 9Z7 ND BM BM BM BM BM BM BM : BM BM 98 DO.NET OE DB

105 BM ( ) (). BM orp. opyright BM orp.. ll rights resered. BM BM ibm.com nternational Business Machines orporation BM BM inux inus Toralds Jaa Jaa Oracle UNX The Open Group ntel ntel ntel nside ntel nside eleron ntel SpeedStep tanium Pentium ntel orporation Microsoft Windows Windows NT Windows Microsoft orporation B. 99

106 100 DO.NET OE DB

107 ,,,, BM OE DB Proider 82 DO BM OE DB Proider 54 Visual Basic 74 BM Data Serer Proider for.net 5 BM Database dd-ns for Visual Studio 4 DB2 BM Knowledge enter 95 DO 74 DO 74 BM OE DB Proider 58 BM Data Serer Proider for.net BM OE DB Proider for DB PDF BM OE DB Proider for DB2 53, 54 ODB.NET Data Proider 44 OE DB.NET Data Proider 37 BM Data Serer Proider for.net 11 OE DB.NET Data Proider 41 BM Data Serer Proider for.net 6 OE DB DB2 58 DO.NET BM.Data.DB2 85 BM.Data.nformix 85 OE DB 69 SQ 94 (OB) BM OE DB Proider 54 ctiex Data Object (DO) BM Data Serer Proider for.net 5 DO BM OE DB Proider DO 75 DOecordset 43 DO.NET 1 9 opyright BM orp. 2006,

108 OM 82 OM+ 11 #.NET (Windows) /++ BM OE DB Proider 82 D DB2 91 DB2 BM Knowledge enter 95 E Enterprise ibrary 30 BM Data Serer Proider for.net 1, 5, 9, Microsoft Entity Framework Microsoft Entity Framework 26 Microsoft SQ Serer eporting Serices 29 SQ 15 BM Database dd-ns for Visual Studio 4 BM Knowledge enter DB2 95 BM OE DB Proider 54 BM OE DB Proider () 53 58, DB2 OE DB 62 OE DB DB , DO 74, OM 82 /++ 82 OB 54 OE DB 66 OE DB 66 OE DB 57 OE DB 69 Visual Basic 74 BM.Data.DB2 85 BM.Data.fxTypesDB2Types 88 M Microsoft Entity Framework 27 BM Data Serer Proider for.net BM Data Serer Proider for.net Microsoft OE DB Proider (ODB ) OE DB 66 Microsoft SQ Serer eporting Serices 29 Microsoft Transaction Serer (MTS) OM 82 DB2 82 O ODB.NET Data Proider 1, OE DB DO.NET OE DB

109 OE DB () DB2 OE DB 62 OE DB DB2 59 DB BM OE DB Proider 66 BM OE DB Proider 69 BM OE DB Proider 73 OE DB.NET Data Proider 1, DOecordset 43 OE S.NET () # () SQ 15 Windows 32 BM Data Serer Proider System.Diagnostics.Trace 23 purequery 26 Visual Basic SQ 15 Windows 31 SMPE ODB.NET Data Proider 44 OE DB.NET Data Proider 37 SQ BM Data Serer Proider for.net T testconn 6 V Visual Basic Visual Basic.NET NET 3 2 #

110 104 DO.NET OE DB

111

112 Printed in Japan S