Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
cibin_louis
New Member

Help on using IBM DB2 Z/OS Mainframe stored procedure result set (data) in PowerBI report

Hi All,

 

I am trying to create a report by executing a DB2 stored procedure on the mainframe (Z/OS). The stored procedure returns a single result set. Stored procedure works fine when tested but when it’s invoked from powerBI, I am seeing empty result set. Please help me with the syntax to invoke the DB2 stored procedure if what I have used is not correct and suggest some alternatives for SP invocation. Please note that I am able to connect to the database, create reports by running query on tables etc. successfully. I am also able to invoke the stored procedures from DB2 Connect (ie, there are no issues with DB2 connection or procedure). I suspect that either PowerBI doesn’t support DB2 store procedure call or syntax is wrong. I am using PowerBI desktop version 2.40.4554.463.

 

let

    Source = Odbc.Query(“dsn=DB2EMPA”,”CALL EMPLOYEE.SUMMARY1(‘MATHS’, ‘5’)”)

in

    Source

3 REPLIES 3
v-huizhn-msft
Microsoft Employee
Microsoft Employee

Hi @cibin_louis,

You didn't receive any errors beside empty table? I have made some research and found some tricks. Please try and check if it works fine.

  1. Click "Edit Queries" in the "Home" tab, a "Query Editor" window pops up.
  2. Click "New Source" ->"SQL Server" and fill the "server","database","Sql Statement" with "testServer","testdb","exec p4test 'goodday'", click ok.
  3. Click on that query on the left panel and click "Advanced Editor" in "Home" tab, and paste the below code.


    let
        Source = DB2.Database("server", "darabase",[Odbc.Query(“dsn=DB2EMPA”,”CALL EMPLOYEE.SUMMARY1(‘MATHS’, ‘5’)”])
    in
        Source
    

Best Regards,
Angelia

Can you please confirm if this suggestion will work for IBM DB2 ?.  SQL Server is mentioned as the New Source in your response.

umarsalaluddin
New Member

OleDb.DataSource("provider=IBMDADB2.DB2COPY1; data source=DB2EMPA", [Query="CALL EMPLOYEE.SUMMARY1('MATHS', '5');"])

 

Resolved. Using OLEDB instead of ODBC helped to get the returned result set from SP.

 

Note: IBMDADB2.DB2COPY1 is the installation default IBM DB2 OLE DB provider.

 

ODBC provides access only to relational databases
OLE DB provides the following features
Access to data regardless of its format or location
Full access to ODBC data sources and ODBC drivers

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.