Planning and Architecting Solutions Using Data Clients

 

Microsoft data clients enable enterprise developers to deliver new applications faster with less custom coding through improved productivity using Microsoft Visual Studio 2012 and Microsoft .NET Framework 4, without requiring knowledge of host systems and infrastructure, compared to IBM host development tools and technologies. Microsoft data clients empower knowledge workers and decision-makers to analyze and report vital information faster, by directly accessing host data sources from business intelligence tools within Microsoft Office SharePoint and SQL Server 2012, without needing to schedule host developers to write programs to extract and convert host data structures.

DI_DataClients

Host Integration Server 2013 data clients connect new solutions based on Microsoft Windows operating systems with existing information stored in IBM DB2 and Informix database, IBM mainframe and midrange host file systems.

The following table describes the supported platforms and versions.

Data SourcePlatform and Version
DB2IBM DB2 for z/OS, 9.1 and 10

IBM DB2 for i5/OS V5R4, V6R1 and V7R1

IBM DB2 UDB for Windows, AIX, HP-UX, Solaris, Linux V9.7 and V10
InformixIBM Informix IDS V11
Host FilesIBM DFSMS DFM z/OS V1.11, V1.12 and V1.13

IBM i5/OS V5R4, V6R1 and V7R1

The Host Integration Server 2013 data client integration technologies and tools use IBM and industry-standard protocols and formats to help you connect to the back end data sources. The common IBM architecture is Distributed Data Management (DDM), which IBM has built into DB2 and Informix servers, mainframe z/OS and midrange i5/OS file systems.

When accessing remote IBM DB2 and Informix database servers, Microsoft technologies operate as industry-standard Distributed Relational Database Architecture (DRDA) Application Requester (AR) clients. To connect to IBM file systems (Host Files), Microsoft technologies operate as IBM-standard Distributed Data Management (DDM) Record-Level Input/Output (RLIO) clients.

Data SourceProtocol Client
DB2Microsoft Client for DRDA (msdrda.dll)
InformixMicrosoft Client for DRDA (msixdrda.dll)
Host FilesMicrosoft DDM Client (included in MsHostFileClient provider)

On top of these network clients, Host Integration Server 2013 offers a set of data provider features for you to use. The following table describes providers, architecture and consumers.

ProviderArchitectureConsumers
ADO.NET Provider for DB2ADO.NETWin Forms, Web Forms, Web Services, SQL Server Integration Services
Entity Provider for DB2Entity FrameworkEntity Data Model, WCF Data Services
BizTalk Adapter for DB2BizTalk MessagingBizTalk Server
OLE DB Provider for DB2COM OLE DBOffice Excel, SharePoint, SQL Server (Integration Services, Analysis Services, Reporting Services, Replication Services, and Query Processor), SQL Server PowerPivot for Excel, SQL Server PowerPivot for SharePoint
ODBC Driver for DB2Open Database ConnectivityODBC consumers and custom ODBC applications
OLE DB Provider for InformixCOM OLE DBOLE DB consumers and ADO.NET Provider for OLE DB applications
ADO.NET Provider for Host FilesADO.NETWin Forms, Web Forms, Web Services, SQL Server Integration Services
BizTalk Adapter for Host FilesBizTalk MessagingBizTalk Server

When you want to define and manage connections, configure or develop applications, Microsoft offers the set of technologies described in the following table.

ProductToolData SourceDescription
HISData Access Tool with Data Source WizardInformix Host, filesHIS Data Access Tool with Data Source Wizard guides the knowledge worker, IT professional or enterprise developer to define and manage connections to DB2, Informix and host files.
HISData Access LibraryDB2, Informix, Host FilesHIS Data Access Library offers a set of .NET Framework 4 components to automate common data administration tasks, such as defining connections and creating static SQL packages for DB2.
HISVS Designer plug-inHost FilesThe HIS client for host files relies on an HIS Host Files Designer for Visual Studio plug-in, allowing enterprise developers to import COBOL and RGP with which to define local metadata maps that are required for encoding/decoding record layouts.
Visual StudioVS Server Explorer, Query and View Designer, DataSet Designer with TableAdapter WizardDB2VS Server Explorer, Query and View Designer, DataSet Designer with TableAdapter Wizard assist the enterprise developer to develop Windows Form, XML Web Service and Web Form applications with less ADO.NET provider coding required.
SharePointData Sources in SharePoint DesignerDB2SharePoint Designer enables IT professionals to integrate host data with collaboration and business intelligence Web sites
SQL ServerSQL Server Management Studio and Business Intelligence Development StudioDB2, InformixSQL Server Management Studio and Business Intelligence Development Studio enable the IT professional and enterprise developer to deliver data.
Excel and SharePointSQL Server PowerPivot Add-in for Excel and SQL Server Reporting Services Report BuilderDB2, InformixSQL Server PowerPivot Add-in for Excel and SQL Server Reporting Services Report Builder enable self-serve business intelligence for streamlining the integration of data from multiple sources.
BizTalk ServerBizTalk Administrator and BizTalk ExplorerDB2, Host FilesBizTalk Adapters are based on the Microsoft ADO.NET Data Providers for DB2 and Host Files, offering intuitive wizards to configure the static solicit and response send ports solutions that efficiently integrate DB2 databases without writing code.
Visual StudioEntity DesignerDB2VS Entity Designer, based on the Entity Framework and Entity Data Model, enables enterprise developers to connect DB2 data through Language Integrated Query (LINQ) and ADO.NET Data Services.

The Data Access Library (DAL) offers .NET Framework 4 components and interfaces to automate common administrative tasks, such as defining connections, changing passwords, creating standard and customer packages.

  • Creating Connections for DB2.

  • Creating Connections for Host Files.

  • Creating Standard Packages for DB2.

  • Creating Custom Packages for DB2.

  • Changing Passwords for DB2.

Additionally, the Host Integration Server 2013 Data Access Tool and Data Source Wizard utilize the Data Access Library as underlying technology, to connect these tools to the Microsoft network clients and data providers at runtime. For example, when using the Data Source Wizard to test a connection, the Data Source Wizard connects via the Data Access Library to the Microsoft network client for DB2.

For more information, see Using the Data Access Library in Data Integration (Configuration) and the Microsoft.HostIntegration.DataAccessLibrary Namespace documentation.

SQL Server 2012 provides a rich array of tools that you can use to create DB2 solutions with SQL Server consumers.

Business Intelligence Development Studio

Business Intelligence Development Studio is the primary development environment that you can use for creating business solutions using Analysis Services, Integration Services, and Reporting Services. Business Intelligence Development Studio provides templates, designers, tools, and wizards that you can use which are specific to each consumer. For more information, see Introducing Business Intelligence Development Studio (http://go.microsoft.com/fwlink/?LinkID=180755).

SQL Server Management Studio

SQL Server Management Studio is an integrated environment that you can use for accessing, configuring, managing, administering, and developing all components of SQL Server. You can use the graphical tools and script editors in SQL Server Management Studio to work with DB2 data and SQL Server data. In addition, SQL Server Management Studio works with all components of SQL Server such as Reporting Services and Integration Services. For more information, see Using SQL Server Management Studio (http://go.microsoft.com/fwlink/?LinkID=180759).

DI_OLEDB_Provider

HIS data client for DB2 supports an OLE DB Provider for DB2 that can be consumed by all SQL Server technologies.

Integration Services

You can use the Integration Services project type in Business Intelligence Development Studio to create data extraction, transformation, and loading (ETL) applications. It contains templates for packages, data sources, and data source views, and provides the tools for working with these objects. For more information, see Integration Services in Business Intelligence Development Studio (http://go.microsoft.com/fwlink/?LinkID=180757). You can also use the Namespaces in the http://go.microsoft.com/fwlink/?LinkID=180760 (http://go.microsoft.com/fwlink/?LinkID=180760) to programmatically create and manage packages. For more information about how to create Integration Services solutions, see the Integration Services Developer InfoCenter (http://go.microsoft.com/fwlink/?LinkID=180761). For more information about SQL Server Integration Services, see SQL Server Integration Services (http://go.microsoft.com/fwlink/?LinkID=180424).

Analysis Services

You can use the Business Intelligence Development Studio to develop Online Analytical Processing (OLAP) cubes and data mining models in SQL Server Analysis Services. This project type includes templates for cubes, dimensions, mining structures, data sources, data source views, and roles, and provides the tools for working with these objects. For more information, see Analysis Services in Business Intelligence Development Studio (http://go.microsoft.com/fwlink/?LinkID=180756). For the Analysis Services documentation, see SQL Server Analysis Services - Multidimensional Data (http://go.microsoft.com/fwlink/?LinkID=180426) and SQL Server Analysis Services - Data Mining (http://go.microsoft.com/fwlink/?LinkID=180427).

Reporting Services

You can use the Report Model and Report Server projects in Business Intelligence Development Studio for developing Reporting Services solutions that access DB2 data. The Report Model project type includes templates for report models, data sources, and data source views for you to use, and provides the tools you need for working with these objects. The Report Server project includes the templates you can use for working with reports and shared data sources. For more information, see Reporting Services in Business Intelligence Development Studio (http://go.microsoft.com/fwlink/?LinkID=180758). For the Reporting Services documentation, see SQL Server Reporting Services (http://go.microsoft.com/fwlink/?LinkID=180428).

Replication

Administrators can move data from SQL Server to DB2 by using Replication wizards in SQL Server Management Studio, as part of either snapshot or transactional replication operations. For Replication, SQL Server uses linked servers for connectivity and Integration Services for synchronizing data with DB2. For the SQL Server Replication documentation, see SQL Server Replication (http://go.microsoft.com/fwlink/?LinkID=180425).

Query Processor

Administrators and developers can use distributed queries to access data from multiple heterogeneous data sources including DB2. For more information about how to configure DB2 data sources, see Connectivity and Data Access. For more information about SQL Server distributed queries, see Distributed Queries (http://go.microsoft.com/fwlink/?LinkID=180429).

Microsoft data providers for host files support multiple access methods, data set types, and record types, depending on the back end data source platform.

PlatformAccess MethodData Set Type
Mainframe (z/OS)Sequential Access Method (SAM)Basic Sequential Access Method (BSAM) data sets
Queued Sequential Access Method (QSAM) data sets
Virtual Storage Access Method (VSAM)Entry-Sequenced Data Sets (ESDSs)
Key-Sequenced Data Sets (KSDSs)
Fixed-length Relative Record Data Sets (RRDSs)
Variable-length Relative Record Data Sets (VRRDSs)
VSAM Alternate Indexes to ESDSs or KSDSs
Basic Partitioned Access MethodPartitioned Data Sets (PDS) and Partitioned Data Set Extended (PDSE) directories and members
Midrange (i5/OS)Sequential and Keyed AccessSingle and multiple member Physical files (PF) and Keyed physical files (KPF)
Logical files (LF) over a PF or KPF

DI_Midrange

HIS data client for host files can access midrange files and submit a remote command.

Host Integration Server 2013 data providers support a set of access methods and command syntax, depending on the data source and provider architecture. The following table describes the supported providers, command types and command syntax.

ProviderCommand TypesCommand Syntax
ADO.NET Provider for DB2Dynamic SQL, Static SQL, Stored ProceduresANSI SQL 92 Entry-level syntax supported by IBM DB2 servers
Entity Provider for DB2Dynamic SQL, Stored ProceduresANSI SQL 92 Entry-level syntax supported by IBM DB2 servers
BizTalk Adapter for DB2Dynamic SQL, Stored ProceduresSubset of ANSI SQL 92 Entry-level syntax, specific to HIS data provider (SELECT, INSERT, UPDATE, DELETE, CALL)
OLE DB Provider for DB2Dynamic SQL, Static SQL, Stored ProceduresANSI SQL 92 Entry-level syntax supported by IBM DB2 servers
ODBC Driver for DB2Dynamic SQL, Static SQL, Stored ProceduresANSI SQL 92 Entry-level syntax supported by IBM DB2 servers
OLE DB Provider for InformixDynamic SQL, Stored ProceduresANSI SQL 92 Entry-level syntax supported by IBM Informix servers
ADO.NET Provider for Host FilesSequential, Keyed, Relative RecordSubset of ANSI SQL 92 Entry-level syntax, specific to HIS data provider (SELECT, INSERT, UPDATE, DELETE)
BizTalk Adapter for Host FilesSequential, Keyed, Relative RecordSubset of ANSI SQL 92 Entry-level syntax, specific to HIS data provider (SELECT, INSERT, UPDATE, DELETE)

Host Integration Server data integration technologies allow you to develop custom applications using Microsoft data access architectures and development tools. The following table lists the data sources, providers, architectures and tools available for custom application development.

Data SourceData ProviderData Provider Namespace or Program NameData Access ArchitectureProgramming Languages
DB2Microsoft Entity Provider for DB2Microsoft.HostIntegration.MsDb2ClientADO.NET Entity FrameworkMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++
DB2Microsoft ADO.NET Data Provider for DB2Microsoft.HostIntegration.MsDb2ClientADO.NETMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++
DB2Microsoft OLE DB Provider for DB2DB2OLEDBOLE DBADO.NETMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++
DB2ODBC Driver for DB2MSEIDB2DODBCOLE DBADO.NETMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++
InformixMicrosoft OLE DB Provider for InformixMSINFORMIXOLE DBADO.NETMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++
Host FilesMicrosoft ADO.NET Data Provider for Host FilesMicrosoft.HostIntegration.MsHostFileClientADO.NETMicrosoft Visual Basic.NET, Microsoft C#, Microsoft Visual C++

DI_DataClient1

HIS data client for host files can access mainframe sequential, VSAM and partitioned data sets.

You should use ADO.NET and the .NET Framework to develop all new custom applications to integrate important information stored in IBM DB2 databases and host file systems. ADO.NET and the .NET Framework offer many advantages over OLE. These include the ability to develop XML and Web-aware applications, efficient deployment of Web Services and Windows Communication Foundation Services in addition to making it easy to develop Windows and Web form-based applications.

DI_DataClient2

HIS data client for Informix supports an OLE DB Provider for Informix that can be consumed from ADO.NET.

Entity Data Model

Enterprise developers need to extend existing data storage systems, integrating valuable information more efficiently in new applications, without requiring changes to the underlying data store. Microsoft ADO.NET Data Services and Entity Data Model (EDM) provide the data architecture components and tools to enable enterprise developers to deliver new solutions faster, while retaining the data integrity and security of enterprise data stores. Data services provide an isolation layer between the physical data in the data store and the logical data used by applications. Modeling provides a way to work on the information with context associated with a given data-aware application or set of applications.

DI_DataClient3

HIS data client for DB2 supports a broad set of data providers, including an Entity Framework Provider for DB2.

With the ADO.NET Entity Framework and Microsoft Entity Provider for DB2, which sits on top of the Microsoft ADO.NET Provider for DB2, enterprise developers can create data access applications by programming against a conceptual application model instead of programming directly against the DB2 storage schema. Using EDM with the Entity Provider for DB2, enterprise developers can deliver solutions faster, while decreasing the amount of code and maintenance required for data-aware applications.

Show: