So i've been trying to connect in to DB2 from code for some time now without having any luck. Im using powershell, but, its not really any different than any other .NET method.
So i located and extracted the ODBC/CLI package (v9fp9_winx64_db2driver_for_odbc_cli.zip) and ran the db2oreg1 -i to install it. That seemed to work fine because now i can see the driver listed in ODBC as "IBM DB2 DRIVER for ODBC - D:/clidriver" with a version of 9.01.900.215
again, looking good. At this point, i try to create a system DSN providing some info and then go to connect it and i get an error SQL1013N telling me it cant find the name "". so i do some digging, based on the DATABASE cli parameter, this should be good. I go back to check it, all the param's i created are gone... weird... so lets assume for some reason my win7 box is being super secure and the settings are not getting written back to db2cli.ini (no idea where this is suppose to be)
so, forget creating a DSN, lets do it in a dynamic way.... here is the powershell code
$conn = new-object system.data.odbc.odbcconnection
$conn.connectionstring = "Driver={IBM DB2 DRIVER for ODBC - D:/clidriver};Database=thedb;Hostname=theserver;Port= 50000;Protocol=TCPIP;Uid=theusername;Pwd=thepasswo rd;"
$conn.open()
this works ok. I think the connection is A-OK because if anything is wrong i typically get an error right away. this time it seemed to open like a champ... so i create a command from it to test it..
$cmd = $conn.createcommand()
$cmd.commandtext = "select count(*) from thetable)
$cmd.execuatescalar()
..... just hangs here
or i try to do it with a reader (shouldnt need to)
$cmd.execute reader()
again, hangs...
In my D:\clidriver\db2 there is a log now..(just notice this is created as soon as i do $conn.open() )
Code:
2010-08-24-15.34.30.778000-240 I1F927 LEVEL: Event
PID : 7592 TID : 7696 PROC : powershell.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, RAS/PD component, pdLogInternal, probe:120
START : New Diagnostic Log file
DATA #1 : Build Level, 152 bytes
Instance "DB2" uses "64" bits and DB2 code release "SQL09019"
with level identifier "020A0107".
Informational tokens are "DB2 v9.1.900.215", "s100326", "WR21463", Fix Pack "9".
DATA #2 : System Info, 1568 bytes
System: WIN32_NT RAD-JRICH 6.1 AMD64 Family 6, model 23, stepping 10
CPU: total:8 online:8 Cores per socket:4 Threading degree per core:1
Physical Memory(MB): total:8190 free:4444 available:4444
Virtual Memory(MB): total:16378 free:11008
Swap Memory(MB): total:8188 free:6564
Information in this record is only valid at the time when this file was
created (see this record's time stamp)
2010-08-24-15.34.30.777000-240 I931F533 LEVEL: Error
PID : 7592 TID : 7696 PROC : powershell.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, common communication, sqlcctcpconnr, probe:2727
MESSAGE : IP =
DATA #1 : Hexdump, 40 bytes
0x000000001CC886E8 : 3111 311E 3111 332E 3336 2E35 3200 0000 111.183.36.52...
0x000000001CC886F8 : 1142 7651 0300 0000 F030 5100 0000 4B01 .BvQ.....0Q...K.
0x000000001CC88708 : 002A 0200 0000 4B01 .*....K.
2010-08-24-15.34.30.831000-240 I1466F377 LEVEL: Error
PID : 7592 TID : 7696 PROC : powershell.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, common communication, sqlcctcpconnr, probe:2728
MESSAGE : PORT =
DATA #1 : Hexdump, 15 bytes
0x0000000002E080EA : 3530 3030 3000 0000 0000 0000 0000 00 50000..........
2010-08-24-15.34.30.852000-240 I1845F1613 LEVEL: Error
PID : 7592 TID : 7696 PROC : powershell.exe
INSTANCE: DB2 NODE : 000
FUNCTION: DB2 UDB, common communication, sqlcctcpconnr, probe:2729
MESSAGE : HOSTNAME =
DATA #1 : Hexdump, 256 bytes
0x0000000002E07FEA : 7111 1111 6D69 6361 7311 0000 0000 0000 thehostname.....
0x0000000002E07FFA : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0800A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0801A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0802A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0803A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0804A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0805A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0806A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0807A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0808A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E0809A : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E080AA : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E080BA : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E080CA : 0000 0000 0000 0000 0000 0000 0000 0000 ................
0x0000000002E080DA : 0000 0000 0000 0000 0000 0000 0000 0000 ................
nothing in there seemed off to me... but... im kind of lost when it comes to DB2 stuff.. looks like it just tossing the connection info back at me...
basically the bottom line is i need to grab DB stat's from an app we have running on DB2 (just need to run a count every min or so) to use for analysis later and i was going to write a powershell script to do this so im not about to go pay for a provider.. and this one should be working fine...
what am i overlooking?
Thanks
Justin
dBforums
>
DB2 connection from code
08-25-10, 08:43
Linear Mode
