none
Query Folding not working for DB2 data source RRS feed

  • Question

  • Hi,

    The query folding didn’t work with my company ERP system database (IBM DB2). Please help to investigate if this is a bug or if I didn’t do it correctly.
    I tried using ODBC, OLEDB, DB2-Microsoft Implementation – all of them do not fold the query.
    I did a test using queries below. I checked “Applied Steps” section to see if the query folding is broken. Here is the result:

    // First Query: using ODBC
    // ODBC
    let
        Source = Odbc.DataSource("dsn=TEST", [HierarchicalNavigation=true]),
        USM3P1_Database = Source{[Name="TEST",Kind="Database"]}[Data],
        M3FDBKMN_Schema = USM3P1_Database{[Name="TEST",Kind="Schema"]}[Data],
        OCUSMA_Table = M3FDBKMN_Schema{[Name="OCUSMA",Kind="Table"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(OCUSMA_Table,{"OKSTAT", "OKCUNO", "OKCUNM"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([OKSTAT] = "10"))
    in
        #"Filtered Rows"
    // Conclusion: Query folding was broken at the “Filtered Rows” step.



    // Second Query: using OLEDB
    // OLEDB
    let
        Source = OleDb.DataSource("Provider=DB2OLEDB.1;Data Source=TEST;Initial Catalog=TEST;Network Address=TEST;Package Collection=QGPL;"),
        USM3P1_Database = Source{[Name="TEST",Kind="Database"]}[Data],
        M3FDBKMN_Schema = USM3P1_Database{[Name="TEST",Kind="Schema"]}[Data],
        OCUSMA_Table = M3FDBKMN_Schema{[Name="OCUSMA",Kind="Table"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(OCUSMA_Table,{"OKSTAT", "OKCUNO", "OKCUNM"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([OKSTAT] = "10"))
    in
        #"Filtered Rows"
    // Conclusion: Query folding was broken at the “Removed Other Columns” step.



    // Third Query: using DB2 – Microsoft implementation
    // DB2MS
    let
        Source = DB2.Database("TEST", "TEST", [CreateNavigationProperties=false, Implementation="Microsoft"]),
        M3FDBKMN_OCUSMA = Source{[Schema="TEST",Item="OCUSMA"]}[Data],
        #"Removed Other Columns" = Table.SelectColumns(M3FDBKMN_OCUSMA,{"OKSTAT", "OKCUNO", "OKCUNM"}),
        #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([OKSTAT] = "10"))
    in
        #"Filtered Rows"
    // Conclusion: Query folding was broken at the “Removed Other Columns” step.


    Wednesday, July 31, 2019 9:27 AM

Answers

  • This is absolutely not the expected behavior. The one that works best is the Db2 connector, using the Microsoft implementation. I just verified that a similar query folds just fine for me.

    Please let me know the following:

    1. What is the version number for the PowerBI Desktop installation you are using?
    2. What are the data types for the three columns in question?
    3. What kind of back-end system is running your Db2 server? (ie, is it on Windows, IBM I, Z/OS, etc)

    Thanks,

    -Wayne Steele

    PowerBI Developer


    Tuesday, August 6, 2019 12:14 AM

All replies

  • This is absolutely not the expected behavior. The one that works best is the Db2 connector, using the Microsoft implementation. I just verified that a similar query folds just fine for me.

    Please let me know the following:

    1. What is the version number for the PowerBI Desktop installation you are using?
    2. What are the data types for the three columns in question?
    3. What kind of back-end system is running your Db2 server? (ie, is it on Windows, IBM I, Z/OS, etc)

    Thanks,

    -Wayne Steele

    PowerBI Developer


    Tuesday, August 6, 2019 12:14 AM
  • Hi, Wayne,

    Sorry for the late reply. 

    I have been using latest version of PBI Desktop (updated every month). 
    Backend system is iSeries.

    Following are the 3 columns in question. 
    OKSTAT GRAPHIC(2) CCSID 13488NOTNULLDEFAULT'' ,
    OKCUNO GRAPHIC(10) CCSID 13488NOTNULLDEFAULT'' ,
    OKCUNM GRAPHIC(36) CCSID 13488 NOT NULL DEFAULT '' ,


    Wednesday, October 9, 2019 7:17 AM
  • Columns of type Graphic and Vargraphic do not fold as well as they should.

    There is an existing bug for this, but I do not have any ETA on when it will be fixed.

    • Proposed as answer by Wayne Steele Friday, October 11, 2019 7:51 PM
    Friday, October 11, 2019 7:50 PM