1 2 Previous Next 18 Replies Latest reply on Jan 3, 2014 1:59 PM by Gordon Young Go to original post
      • 15. Re: DB2 iSeries Connection
        Vince Shockney

        Hi Gordon,

        I could connect to the iSeries, but each connection could only be to a single table.  The multiple tables option never worked and the custom SQL option would throw an error after I clicked [OK].  I attempted to get around it by creating multiple Single Table connections, but then Tableau complained about doing relationships (joins) on the same data source.  Plus, it was incredibly slow.

         

        I haven't experienced any errors by using a linked server and OpenQuery.  After making my query a little more efficient I have been able to get my dashboard load times down from 15 seconds (which I considered acceptable for the amount of data) to under two seconds.

         

        The ease of ODBC would be preferable, but using SQL Server to access the goldmine in the iSeries appears to be my best option, for now.

        • 16. Re: DB2 iSeries Connection
          Gordon Young

          HI Vince.

           

          Thanks for the info regarding linked server and OpenQuery.  I'm trying to now to see how this performs against connecting via ODBC.

           

          Could you give me a hand with the OPENQUERY sql statement? 

           

          SELECT *

          FROM OPENQUERY(LinkedServerName, 'SELECT * FROM LinkedServerName.Database.Schema.TableName')

          • 17. Re: DB2 iSeries Connection
            Vince Shockney

            Happy to help.

             

            I'll start from the beginning or close to it.  I'm going to assume that you have created the Linked Server on your SQL Server.  Now in Tableau select the Microsoft SQL Server database connection.  In the drop down select the SQL Server and enter your credentials, then Connect.  I select any of the databases on that server.  It doesn't seem to matter.  Then Custom SQL.

             

            A basic statement is: SELECT * FROM OpenQuery([LinkedServerName], 'SELECT * FROM Schema.TableName')

             

            Something that I've learned, if you're going to do any data manipulation or filtering (JOIN, WHERE, SUM, GROUP BY, etc), do them in the OpenQuery.  It's much quicker.  Otherwise you're pulling all of the data into the SQL Server before anything gets filtered and that can take a while.

             

            Here's one that I wrote today:

            SELECT * FROM

            OpenQuery([LINKED_SERVER_NAME],

            'SELECT SUM(YRMO01) AS TRMO01, SUM(YRMO02) AS TRMO02, SUM(YRMO03) AS TRMO03, SUM(YRMO04) AS TRMO04, SUM(YRMO05) AS TRMO05, SUM(YRMO06) AS TRMO06, SUM(YRMO07) AS TRMO07, SUM(YRMO08) AS TRMO08, SUM(YRMO09) AS TRMO09, SUM(YRMO10) AS TRMO10, SUM(YRMO11) AS TRMO11, SUM(YRMO12) AS TRMO12, YRTER1, YRYEAR, AVCODE, SPSN

            FROM XXXXXX.SAM501P AS T01

            JOIN XXXXXX.AVCATS AS T03 ON T01.YRCSCL = T03.AVCCCD

            JOIN XXXXXX.TERRCRN AS T02 ON T01.YRTER1 = T02.TERR AND T03.AVCODE = T02.ACCODE

            WHERE T01.YRYEAR IN (''2012'', ''2013'') AND T03.AVCODE IN (''A'', ''C'')

            GROUP BY T01.YRTER1, T02.SPSN, T03.AVCODE, T01.YRYEAR

            ')

            • 18. Re: DB2 iSeries Connection
              Gordon Young

              Vince,

               

              I came across the following tableau discussion thread and wanted to share this with you. Using the info in the discussion thread allowed me to connect multiple tables and custom SQL using ODBC.

               

              ODBC Datasources - Single Table is OK- Custom SQL not

               

              Your Microsoft solution works great too, just wanted to share that ODBC is also an option now.

               

              Cheers,

              Gord

              1 2 Previous Next