Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

    ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
    ** This thread discusses the Content article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements0

  • #2
    DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

    ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
    This is a most interesting development. It should be interesting to see what happens when a DB2 feature is being used that is not supported by MS. For instance would this still work if the file contained an embedded dot in the file name, or if the file contained more than a billion records? Dave

    Comment


    • #3
      DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

      ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
      Thanks a lot for a good article! I've seen the Linked Servers option before, but hadn't been able to make it work. One question for you - do you know how to return data that's CCSID 65537? Normally, in my Connection String, I'll add "Force Translate=37", but I'm not sure how to do that here. I tried adding in @provStr='Force Translate=37;', but no good. Any suggestions? Cliffe

      Comment


      • #4
        DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

        ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
        Hi David You are correct -- there are some problems between DB2 and SQL Server that you have to watch for. If you exceed the max rows allows in SQL Server then there's nothing you can do about that. However, DB2 on the iSeries allows you to work around some special character problems (such as an embedded dot) by using double quotes. So the following query will work from SQL Server: Select * From OpenQuery(DB2400,'Select * From DATALIB."DOT.TEST"') A Also, if you're using four part table names, this will work as well: Select * From DB2400.S107X248.DATALIB."DOT.TEST" Another issue I've come across is the date data type -- DB2 can hold a much broader range of dates in its ISO data type (starting from year 1, whereas SQL Server starts in the 1700s if I remember right.) For these types of issues you just have to program around them in your query.

        Comment


        • #5
          DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

          ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
          Hi Cliffe You're on the right track. In your provider string, simply specify: TRANSLATE=1 or FORCETRANSLATATION=1 This switch is either turns translation on or off, you can't specify a from CCSID.

          Comment


          • #6
            DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

            ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
            Thanks for the tip, but I'm still getting an error: Cannot initialize the data source object of OLE DB provider "IBMDA400" for linked server "MYISERIES" The statement to create the linked server (using Client Access' IBMDA400 OLEDB driver) was this: EXEC master.dbo.sp_addlinkedserver @server = N'MYISERIES', @srvproduct=N'IBMDA400', @provider=N'IBMDA400', @datasrc=N'MYISERIES', @catalog=N'S7809323', @provstr='FORCETRANSLATE=1' If I don't put the @provstr argument in, it works, but I get gibberish from queries. Thanks again, Cliffe

            Comment


            • #7
              DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

              ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
              Sorry - I gave you a bum steer (I just presumed you were using the ODBC driver). When using the OLE DB provider IBMDA400, you should specify: FORCE TRANSLATE=37 However, you need to be at V5R1 or higher. I tried it on a V5R4 system (using iSeries Access V5R4) and it worked fine.

              Comment


              • #8
                DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                Thanks again for your help. I still got the same error, even using "FORCE TRANSLATE=37". Strange. I'm on CA/400 V5R2. However, I used an ODBC DSN, and was able to make it work, so that's the main thing. Thanks again, and I look forward to Part II of this series and beyond. Cliffe

                Comment


                • #9
                  DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                  ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                  Hi, I read your article regarding linked server in SQL 2005. It worked perfect when I am running myself, but under the job it failed with the following error Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274). The step failed. My linked server is using this secruity settings "Be made using this security context" AS400UserID, AS400Password on the security tab on the linked server. I have even tried to run this job (Run AS ) my user but still the same error coming. Please help.

                  Comment


                  • #10
                    DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                    ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                    Thank you for your article! but I found when I tried to create my linked server using the odbc that the 4 part name was case sensitive. I ONLY COULD GET MY LINKED SERVERS TO WORK IF I USED UPPERCASE.

                    Comment


                    • #11
                      DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                      ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                      Thank you for the article, but I could get it to work using ODBC DSN when executing this SQL: SELECT * From OpenQuery(DB2400, 'SELECT * FROM QIWS.QCUSTCDT') I'm getting this error: "[IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=11004 - CWBCO1011 - Remote port could not be resolved". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DB2400". PLEASE HELP.

                      Comment


                      • #12
                        DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                        ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                        Just a thought, but it sounds like there's a problem connecting to your iSeries. Try using "CWBPING servername" from a command-line, using the same servername you put in your ODBC DSN. Cliffe

                        Comment


                        • #13
                          DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                          ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                          I tried CWBPING and I got this: C:>CWBPING 192.168.118.5 IBM iSeries Access for Windows Version 5 Release 3 Level 0 Connection Verification Program (C) Copyright IBM Corporation and Others 1984, 2003. All rights reserved. U.S. Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Licensed Materials - Property of IBM To cancel the CWBPING request, press CTRL-C or CTRL-BREAK I - Verifying connection to system 192.168.118.5... I - Successfully connected to server application: Central Client I - Successfully connected to server application: Network File I - Successfully connected to server application: Network Print I - Successfully connected to server application: Data Access I - Successfully connected to server application: Data Queues I - Successfully connected to server application: Remote Command I - Successfully connected to server application: Security I - Successfully connected to server application: DDM I - Successfully connected to server application: Telnet I - Connection verified to system 192.168.118.5

                          Comment


                          • #14
                            DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                            ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                            Could it be that the iseries port for odbc is closed or blocked for security reasons? A number that comes to mind for the odbc port for iseries is 50000. Tom.

                            Comment


                            • #15
                              DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements

                              ** This thread discusses the article: DB2 Integration with SQL Server 2005, Part I: Linked Server Enhancements **
                              How do you check if a port is blocked or not? And how do you unblock it? Thanks.

                              Comment

                              Working...
                              X