BCS filters and DB2 Stored Procedures with Parameters in SharePoint 2010

Introduction

In this tutorial we will show you how to connect to a DB2 Database using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our DB2 Stored Procedure needs to have parameters defined.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
DB2 stored procedure such as:
1. CREATE PROCEDURE GET_EMPLOYEES_FILTERED(IN P_FIRST_NAME VARCHAR(12), IN P_BONUS DECIMAL(9,2))
  DYNAMIC RESULT SETS 1
  P1: SQL SECURITY INVOKER
  BEGIN
  DECLARE v_employees CURSOR WITH RETURN for
  select * from EMPLOYEE where FIRSTNME like ‘%’ || P_FIRST_NAME || ‘%’ and BONUS > P_BONUS;
  OPEN v_employees;
  END P1

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e BCSMetaManFilteredDB2StoredProcedure and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE; Hostname=cygnus;port=50000;Protocol=TCPIP;Uid=Administrator;Pwd=12345;Trusted_Connection=false;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your DB2 data
Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”
Select the Method Type as “Finder’’
Select the first Parameter (p_first_name) and set it’s Filter Type to Comparison and the Default Value to ‘A’
Next select the second Parameter (p_bonus) and also set it’s Filter Type to Comparison and the Default Value to ‘100’
Click ‘OK’
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaManFilteredDB2StoredProcedure.FilteredEmployee’ External Content Type, click ‘OK’
Click ‘OK’ on the tool pane
Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option
Change the second Filter to be the P_BONUS and enter values for the filters and click ‘Retrieve Data’
Your DB2 data provided by Stored Procedure is now displayed in the SharePoint Web Part