dbTalk Databases Forums  

.Net Provider for DB2 in HIS 2006

Go Back dbTalk Databases Forums Databases microsoft.public.sqlserver.olap .Net Provider for DB2 in HIS 2006

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss .Net Provider for DB2 in HIS 2006 in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
PDOlsen
 
Posts: n/a

Default .Net Provider for DB2 in HIS 2006 - 11-28-2006 , 04:36 PM





I am trying to Create a Data Source View with a Data Source using the .Net
provider that comes with Host Integration Server 2006. It is called
Microsoft.HostIntegration.MsDb2Client. When I use it with VS2005 in code it
retrieves iSeries DB2 data without any problems. I can create a Data Source
and I get a successful connection. However, when I try to create a new data
source view it does not retrieve any tables from the library and the Select
Tables and Views screen is completely empty.
I am not sure how to figure this one out. I am able to get data back with a
similar 3rd party .Net Provider from the same Database, however, I would
prefer to use the one from Microsoft.

Reply With Quote
  #2  
Old   
Ken Kwok [MS]
 
Posts: n/a

Default Re: .Net Provider for DB2 in HIS 2006 - 11-30-2006 , 05:03 PM





Nothing has been tested on this provider so I am not sure what problem here.
Are they having same connection string? and check permission as well.
You can try open the oledb connection and use connection.GetSchema("Tables")
in code and see if you get anything.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Kwok
SQL Server Analysis Services

"PDOlsen" <PDOlsen (AT) discussions (DOT) microsoft.com> wrote

Quote:
I am trying to Create a Data Source View with a Data Source using the .Net
provider that comes with Host Integration Server 2006. It is called
Microsoft.HostIntegration.MsDb2Client. When I use it with VS2005 in code
it
retrieves iSeries DB2 data without any problems. I can create a Data
Source
and I get a successful connection. However, when I try to create a new
data
source view it does not retrieve any tables from the library and the
Select
Tables and Views screen is completely empty.
I am not sure how to figure this one out. I am able to get data back with
a
similar 3rd party .Net Provider from the same Database, however, I would
prefer to use the one from Microsoft.



Reply With Quote
  #3  
Old   
Ken Kwok [MS]
 
Posts: n/a

Default Re: .Net Provider for DB2 in HIS 2006 - 12-01-2006 , 01:40 PM


I think it is related to the cartridge.
Couple Questions:
1) Is the third party provider (the one it works) is OleDB Provider? I think
it is OleDb, isn't it?
2) Can you do GetSchema("DataSourceInformation") on both connection and see
if they have same DataSourceProductName and DataSourceProductVersion? I hope
you can query and they are same.
3) Use GetSchema("Tables") on both connections and see if they return same
metadata on the data table.

Since different provider will give different metadata on data table when you
query "Tables"; therefore we need to specify the mapping class in the
cartridge. The cartridge is located at %Program Files%\Microsoft Visual
Studio
8\Common7\IDE\PrivateAssemblies\DataWarehouseDesig ner\UIRdmsCartridge\db2v0801.xsl

It is xsl file to help translate the native sql to proper sql against the
right database platform.

You will find the section below to indicate the schema class to use for
oledb provider.
<mssqlcrt:schema-class>
<mssqlcrt:managed-provider>System.Data.OleDb</mssqlcrt:managed-provider>
<mssqlcrt:type>Microsoft.DataWarehouse.Design.OleD bSchema,
Microsoft.DataWarehouse</mssqlcrt:type>
- <mssqlcrt:query-designer>
<mssqlcrt:type>Microsoft.DataWarehouse.Controls.Ol eDbQueryDesigner,
Microsoft.DataWarehouse</mssqlcrt:type>
</mssqlcrt:query-designer>
</mssqlcrt:schema-class>

so you need to put an extra entry for Microsoft.HostIntegration.MsDb2Client
and identify the mapping class
<mssqlcrt:schema-class>
<mssqlcrt:managed-provider>Microsoft.HostIntegration.MsDb2Client</mssqlcrt:managed-provider>
<mssqlcrt:type><YOUR SCHEMA CLASS HERE></mssqlcrt:type>
</mssqlcrt:schema-class>

Your schema class needs to implement the interface
Microsoft.DataWarehouse.Design.IDbSchema which can be found in
Microsoft.DataWarehouse.dll

If you decide to do that, I can give you some sample codes. But to save your
trouble, you should use the one it works now.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

Ken Kwok
SQL Server Analysis Services

"PDOlsen" <PDOlsen (AT) discussions (DOT) microsoft.com> wrote

Quote:
Ken,
Thanks for the help.
I wrote the following little Console program to get the iSeries DB2 Schema
for a Database called YUKONTEST and it gives me back all of the tables and
views as expected:

using System;
using System.Data;
using Microsoft.HostIntegration.MsDb2Client;

class Program
{
static void Main(string[] args)
{
using (MsDb2Connection connection = new MsDb2Connection("User
ID=DEVPDO;Password=XXXXXXX;Initial Catalog=YUKONTEST;Network Transport
Library=TCP;Host CCSID=37;PC Code Page=1252;Network
Address=192.168.10.14;Network Port=446;Package
Collection=YUKONTEST;Default
Schema=YUKONTEST;Process Binary as Character=False;Units of Work=RUW;DBMS
Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist
Security
Info=True;Connection Pooling=False;Derive Parameters=False;"))
{
// Connect to the database then retrieve the schema
information.
connection.Open();
DataTable table = connection.GetSchema("Tables");

// Display the contents of the table.
DisplayData(table);
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
}



private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}

==================================================
The Data Source in Analysis Services is as follows:
DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"
xsi:type="RelationalDataSource"
dwd:design-time-name="a896c042-7b9a-426e-ae30-0792ed1ccc3e"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
ID>SILVON5_YUKONTEST</ID
Name>SILVON5_YUKONTEST</Name
CreatedTimestamp>0001-01-01T05:00:00Z</CreatedTimestamp
LastSchemaUpdate>0001-01-01T05:00:00Z</LastSchemaUpdate
ConnectionString>User Id=;Password=;Initial Catalog=A10A335C;Network
Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network
Address=192.168.10.14;Network Port=446;Package
Collection=YUKONTEST;Default
Schema=YUKONTEST;Process Binary as Character=False;Time Out Value=0;Units
of
Work=RUW;DBMS Platform=DB2AS400;Use Early Metadata=True;Defer
Prepare=False;Persist Security Info=True;Derive
Parameters=False;Pooling=False;Rowset Cache Size=0</ConnectionString
ConnectionStringSecurity>PasswordRemoved</ConnectionStringSecurity
ImpersonationInfo
ImpersonationMode>ImpersonateAccount</ImpersonationMode
Account>DEVPDO</Account
ImpersonationInfoSecurity>PasswordRemoved</ImpersonationInfoSecurity
/ImpersonationInfo
Isolation>Snapshot</Isolation
ManagedProvider>Microsoft.HostIntegration.MsDb2Cli ent</ManagedProvider
Timeout>PT0S</Timeout
/DataSource

=========================
I pasted the Connection strings from the working code to the Analysis
Services code so they are basically the same with the exeption of a few
changes like stripping out the User ID and the Password in the AS code.

FYI - This same process works with a 3rd party .Net provider. It would be
great to know if any one at Microsoft could try out this new provider and
see
if they can get it to work.
Thanks for your help.
Paul

"Ken Kwok [MS]" wrote:

Nothing has been tested on this provider so I am not sure what problem
here.
Are they having same connection string? and check permission as well.
You can try open the oledb connection and use
connection.GetSchema("Tables")
in code and see if you get anything.

--
This posting is provided "AS IS" with no warranties, and confers no
rights.

Ken Kwok
SQL Server Analysis Services

"PDOlsen" <PDOlsen (AT) discussions (DOT) microsoft.com> wrote in message
news:750BED72-D238-4F43-8752-66F5E99B6E06 (AT) microsoft (DOT) com...
I am trying to Create a Data Source View with a Data Source using the
.Net
provider that comes with Host Integration Server 2006. It is called
Microsoft.HostIntegration.MsDb2Client. When I use it with VS2005 in
code
it
retrieves iSeries DB2 data without any problems. I can create a Data
Source
and I get a successful connection. However, when I try to create a new
data
source view it does not retrieve any tables from the library and the
Select
Tables and Views screen is completely empty.
I am not sure how to figure this one out. I am able to get data back
with
a
similar 3rd party .Net Provider from the same Database, however, I
would
prefer to use the one from Microsoft.



Reply With Quote
  #4  
Old   
PDOlsen
 
Posts: n/a

Default Re: .Net Provider for DB2 in HIS 2006 - 12-05-2006 , 01:37 PM


Ken,
Here are the answers to your last set of questions:
1. The third party provider that works is not an OleDB provider, but rather
a .Net 2.0 100% managed provider.
2. The GetSchema ("DataSourceInformation") for both was not the same:
a. For Microsoft.HostIntegration.MsDb2Client the values were
DataSourceProductName = Db2/400, DataSourceProductVersion = 05.04.0000
b. For the 3rd party managed provider the values were DataSourceProductName
= Db2, DataSourceProductVersion = DB2 AS400 5.4.0
3. The GetSchema("Tables") for both providers was similar. The only thing
different was that the 3rd party provider returned all tables from the
iSeries system but the Microsoft.HostIntegration.MsDb2Client only returned
tables from the Default Schema. This may be a bug in the 3rd party provider
or a missing parameter in the Connection String.
4. When I looked at the db2v0801.xsl file I did not see any entries for the
3rd party provider, nor did I see any other .xsl files provided by the 3rd
party provider.

You mentioned giving me some sample code. Do you think that still appies to
my situation?
Thanks for your help,
Paul
"Ken Kwok [MS]" <kenkwok (AT) online (DOT) microsoft.com> wrote

Quote:
I think it is related to the cartridge.
Couple Questions:
1) Is the third party provider (the one it works) is OleDB Provider? I
think it is OleDb, isn't it?
2) Can you do GetSchema("DataSourceInformation") on both connection and
see if they have same DataSourceProductName and DataSourceProductVersion?
I hope you can query and they are same.
3) Use GetSchema("Tables") on both connections and see if they return same
metadata on the data table.

Since different provider will give different metadata on data table when
you query "Tables"; therefore we need to specify the mapping class in the
cartridge. The cartridge is located at %Program Files%\Microsoft Visual
Studio
8\Common7\IDE\PrivateAssemblies\DataWarehouseDesig ner\UIRdmsCartridge\db2v0801.xsl

It is xsl file to help translate the native sql to proper sql against the
right database platform.

You will find the section below to indicate the schema class to use for
oledb provider.
mssqlcrt:schema-class
mssqlcrt:managed-provider>System.Data.OleDb</mssqlcrt:managed-provider
mssqlcrt:type>Microsoft.DataWarehouse.Design.OleDb Schema,
Microsoft.DataWarehouse</mssqlcrt:type
- <mssqlcrt:query-designer
mssqlcrt:type>Microsoft.DataWarehouse.Controls.Ole DbQueryDesigner,
Microsoft.DataWarehouse</mssqlcrt:type
/mssqlcrt:query-designer
/mssqlcrt:schema-class

so you need to put an extra entry for
Microsoft.HostIntegration.MsDb2Client and identify the mapping class
mssqlcrt:schema-class
mssqlcrt:managed-provider>Microsoft.HostIntegration.MsDb2Client</mssqlcrt:managed-provider
mssqlcrt:type><YOUR SCHEMA CLASS HERE></mssqlcrt:type
/mssqlcrt:schema-class

Your schema class needs to implement the interface
Microsoft.DataWarehouse.Design.IDbSchema which can be found in
Microsoft.DataWarehouse.dll

If you decide to do that, I can give you some sample codes. But to save
your trouble, you should use the one it works now.

--
This posting is provided "AS IS" with no warranties, and confers no
rights.

Ken Kwok
SQL Server Analysis Services

"PDOlsen" <PDOlsen (AT) discussions (DOT) microsoft.com> wrote in message
news:CF9055CF-0871-4899-8C1A-70EBF559D9EE (AT) microsoft (DOT) com...
Ken,
Thanks for the help.
I wrote the following little Console program to get the iSeries DB2
Schema
for a Database called YUKONTEST and it gives me back all of the tables
and
views as expected:

using System;
using System.Data;
using Microsoft.HostIntegration.MsDb2Client;

class Program
{
static void Main(string[] args)
{
using (MsDb2Connection connection = new MsDb2Connection("User
ID=DEVPDO;Password=XXXXXXX;Initial Catalog=YUKONTEST;Network Transport
Library=TCP;Host CCSID=37;PC Code Page=1252;Network
Address=192.168.10.14;Network Port=446;Package
Collection=YUKONTEST;Default
Schema=YUKONTEST;Process Binary as Character=False;Units of Work=RUW;DBMS
Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist
Security
Info=True;Connection Pooling=False;Derive Parameters=False;"))
{
// Connect to the database then retrieve the schema
information.
connection.Open();
DataTable table = connection.GetSchema("Tables");

// Display the contents of the table.
DisplayData(table);
Console.WriteLine("Press any key to continue.");
Console.ReadKey();
}
}



private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
}

==================================================
The Data Source in Analysis Services is as follows:
DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"
xsi:type="RelationalDataSource"
dwd:design-time-name="a896c042-7b9a-426e-ae30-0792ed1ccc3e"
xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"
ID>SILVON5_YUKONTEST</ID
Name>SILVON5_YUKONTEST</Name
CreatedTimestamp>0001-01-01T05:00:00Z</CreatedTimestamp
LastSchemaUpdate>0001-01-01T05:00:00Z</LastSchemaUpdate
ConnectionString>User Id=;Password=;Initial Catalog=A10A335C;Network
Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network
Address=192.168.10.14;Network Port=446;Package
Collection=YUKONTEST;Default
Schema=YUKONTEST;Process Binary as Character=False;Time Out Value=0;Units
of
Work=RUW;DBMS Platform=DB2AS400;Use Early Metadata=True;Defer
Prepare=False;Persist Security Info=True;Derive
Parameters=False;Pooling=False;Rowset Cache Size=0</ConnectionString
ConnectionStringSecurity>PasswordRemoved</ConnectionStringSecurity
ImpersonationInfo
ImpersonationMode>ImpersonateAccount</ImpersonationMode
Account>DEVPDO</Account
ImpersonationInfoSecurity>PasswordRemoved</ImpersonationInfoSecurity
/ImpersonationInfo
Isolation>Snapshot</Isolation
ManagedProvider>Microsoft.HostIntegration.MsDb2Cli ent</ManagedProvider
Timeout>PT0S</Timeout
/DataSource

=========================
I pasted the Connection strings from the working code to the Analysis
Services code so they are basically the same with the exeption of a few
changes like stripping out the User ID and the Password in the AS code.

FYI - This same process works with a 3rd party .Net provider. It would be
great to know if any one at Microsoft could try out this new provider and
see
if they can get it to work.
Thanks for your help.
Paul

"Ken Kwok [MS]" wrote:

Nothing has been tested on this provider so I am not sure what problem
here.
Are they having same connection string? and check permission as well.
You can try open the oledb connection and use
connection.GetSchema("Tables")
in code and see if you get anything.

--
This posting is provided "AS IS" with no warranties, and confers no
rights.

Ken Kwok
SQL Server Analysis Services

"PDOlsen" <PDOlsen (AT) discussions (DOT) microsoft.com> wrote in message
news:750BED72-D238-4F43-8752-66F5E99B6E06 (AT) microsoft (DOT) com...
I am trying to Create a Data Source View with a Data Source using the
.Net
provider that comes with Host Integration Server 2006. It is called
Microsoft.HostIntegration.MsDb2Client. When I use it with VS2005 in
code
it
retrieves iSeries DB2 data without any problems. I can create a Data
Source
and I get a successful connection. However, when I try to create a new
data
source view it does not retrieve any tables from the library and the
Select
Tables and Views screen is completely empty.
I am not sure how to figure this one out. I am able to get data back
with
a
similar 3rd party .Net Provider from the same Database, however, I
would
prefer to use the one from Microsoft.



Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top