Unable to connect to IBM DB2 from PBI Desktop

[monaraya]

Issue:

Unable to connect to IBM DB2 from PBI Desktop

Error:

• IBM DB2: ERROR [42704] [IBM][DB2]SQL1234 "SYSCAT.SCHEMATA: is an undefined name."
• The package corresponding to an SQL statement execution request was not found.
• Left screenshot for IBM DB2 provider. Right screenshot for MS provider.

**Please note, the resolution for this issue should be carried out by DBA who would have full understanding of the action items.

Resolution: -

1. Please try using the Microsoft provider instead of IBM DB2 and you would get error “SQLSTATE=51002 SQLCODE=-805"
2. You would need to specify "Package Collection" for the connection. This needs to be done by the DBA.
   • The error usually means 1) you are connecting to a Mainframe, and 2) you need to specify a "Package Collection" in the connection dialog
   • You should contact the DBA, and find out what package collection to use (the driver will create the packages at connection time, but the user needs to have enough authority to the appropriate "collection" to do so.

3. Execute this step if step 2 fails: -

• If you receive error even after step 2, you may well be using the correct package name, but lack the authority on the mainframe to create and bind the needed packages.
• IBMs description of DB2 packages: https://www.ibm.com/developerworks/data/library/techarticle/dm-0606chun/index.html
• You will need to get the DBA to grant you this authority so the packages can be created and bound, and then (presumably) remove the authority after this has been done.Here are specific commands to do so for z/OS:

1.Grant authority to bind a new package to user:

a.GRANT BINDADD ON SYSTEM TO <authorization_name>

           Or

b.GRANT PACKADM ON <collection_name> TO <authorization_name>

2. Using consumer (e.g. Power BI Desktop), connect to DB2 and retrieve a list of schemas, tables, and views:

           The Microsoft Connector for DB2 will auto-create the package NULLID.MSCS001, and then grant execute on package to public.

3. Revoke authority to bind a new package to user.

a.REVOKE BINDADD FROM <authorization_name>

           Or

b.REVOKE PACKADM ON <collection_name> FROM <authorization_name>

Author: Srishti Sharma

Reviewer: Mounika Narayana Reddy