Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    14

    Unanswered: DB2 connection from code

    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

  2. #2
    Join Date
    Nov 2007
    Posts
    14
    One other thing im noticing... im using win7x64 with a 64bit driver which is hanging. I also have Toad installed, which has installed a 32bit driver. I can use my code in 32bit powershell (specifying the 32bit toad driver) and am able to get the results ok. One thing to note is im connecting to a 32bit DB2 system. i am FAIRLY sure that shouldnt make a difference. I suspect that something im doing with the install is whats screwing it up. I did find after using procmon and auditing that the files were marked as read only (default state after an unzip) and after changing that i was able to get the ODBC DSN to save its settings and to say it connected successfully. But the problem of it hanging is still an issue. Why would a query to the DB from what appears to be a successful connection hang like that?

    Thanks

  3. #3
    Join Date
    Nov 2007
    Posts
    14
    I've managed to get the 32bit provider installed and working but not the 64bit. from what i've seen a 64 bit provider should work fine with a 32bit DB. I was able to find doc's that indicated this was ok in previous versions... v7 and v8, so i assume this is ok with v9.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •