Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2012
    Posts
    5

    Unanswered: Connecting to DB2 on zOS in an SSIS Package

    Hi,

    I'm trying to connect to a DB2 data source on zOS in a SQL Server 2008 R2 SSIS Package.

    I define the data source as an OLEDB data source and when I execute the package I get the following error:


    TITLE: Connection Manager
    ------------------------------

    Test connection failed because of an error in initializing provider. ERROR [ ] [IBM][CLI Driver] SQL1460N The environment variable "SOCKS_NS" required for SOCKS server name resolution is not defined or not valid.

    ERROR [HY000] [IBM][CLI Driver] SQL1460N The environment variable "SOCKS_NS" required for SOCKS server name resolution is not defined or not valid.

    What could be the problem? Please advise.

    Thanks,
    SG

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    do you have DB2 Connect installed?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2012
    Posts
    5
    Brett,

    Yes, I have DB2 Connect installed on my machine.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    DB2 Connect provides an ODBC driver, but no OLEDB driver.

    If you can connect via a Linked Server and you can install Microsoft's OLEDB Driver for DB2 on your server, that is the preferred solution especially in a 64 bit environment.

    You definitely can connect SSIS to DB2 using IBM DB2 Connect, but it is trickier than using the Microsoft OLEDB Driver for DB2.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Apr 2012
    Posts
    5

    Connecting to DB2 on zOS in an SSIS Package

    Pat,

    I followed your suggestion and I'm in the process of downloading DB2OLEDB.EXE from the Microsoft site.

    Once it is downloaded and installed, what are the next steps?

    Thanks for all your help.

    SG

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    There should be a read me...

    I deal with DB2 z/OS for...well most of my life...

    I remember once that it has to look at the entire subsystem catalog...and it took forever

    My prefered meythod is to create a DB2 Unload of the Data I wanted, cyberfusion it down and bcp the data in

    Exactly what are you trying to accimplish

    And please don't say a join
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2012
    Posts
    5

    Connecting to DB2 on zOS in an SSIS Package

    Brett,

    I'm using DB2 tables as a source for a SQL Server Data Mart. I can take the approach suggested by you however I prefer something more direct, where I can connect to the source data table directly.

    Thanks,
    SG

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...it all comes down to performance right?

    Unless it's a batch thing, then if it is, why would you care

    What I told you would be the fastest method I'm guessing...

    If you want selective data, then write a COBOL sproc and call that

    Otherwise download the data

    And if not, good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by DB2SSIS_USER View Post
    I followed your suggestion and I'm in the process of downloading DB2OLEDB.EXE from the Microsoft site.

    Once it is downloaded and installed, what are the next steps?
    There's a thread with good discussion of Microsoft DB2 OLEDB drivers in general, and using them with SSIS in specific at SSIS and Microsoft OLE DB Provider for DB2 that gives good information. Pay special attention to Charles Ezzell, he actually works on the team that produces the code and manages problems with it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Read it...still think my solution is the correct answer

    Simpler the better
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2012
    Posts
    5
    Thanks Pat. I'll look it up.

    Brett, your solution is not simple but rather 'simplistic' . Incidentally I thought of that in the beginning itself.

    I have dozen's of tables with 100,000+ rows in my source database. I don't think BCP-ing those and creating parallel SQL table will help me much. Also, the source tables are part of a relational database, which means I have to get the most current data. Your method, will increase moving parts and overhead in the system.

    I still appreciate your taking the time and sharing though.

    Thanks,
    SG

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No problem...as not be close to your problem it's kinda of difficult to see what you want to accomplish

    That said, your last post gives us more info

    I would then especially go with creating sprocs (or VIEWS) that encapsulate what you need, then calling that

    I don't recall if you said you need to do joins, but you could still accomodate that by passing xml, or storing results in a temp table afte the call of a DB2 sproc

    In any case...much joy, good luck and happiness your way

    Were you ever able to connect?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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