Access DB2 on AS/400 using IBM OLE DB Provider

Hi,

I am using the IBM OLE DB Provider (IBMDASQL) provided with Client Access (IBM) to access a DB2 database on an AS/400 (iSeries). Using an OLE DB Source in the data flow and Access Mode set to "OpenRowset" this works fine (even though I think it is quite slow).

However, when I change the Access Mode to "SQL Command" my data flow fails at the OLE DB Source component - not giving me any meaningful error description (only the mysterious "... the error is specific to the component..."). Using and SQL Command to access the data is important to me, since I need to put a WHERE-statement on the rows returned from the source.

/Michael

PS: I am not able to use the MS OLE DB Provider for DB2 (as far as I know it is only shipped with HIS?).

I believe you also got generic OLEDB error before seeing failures from OLEDBSrc - Did you see something like "An OLE DB error has occurred. Error code: 0x80040E00."? If yes, we talked about the same issue before in this forum. It's likely a provider issue, here is more info quoted from our dev Ted's explanation on this
"
The error returned by the provider is 0x80040E00, which is DB_E_BADACCESSORHANDLE, and means that there was a problem involving the accessor used to describe how to retrieve data from the data source. This problem is not necessarily an SSIS problem. For example, if the provider has a bug that invalidated the accessor before it is released by the client when the accessor is still needed. On our side, there isn't much we can do to diagnose this without a repro, and even with a repro, we may still need access to the provider source code to know for sure why it is returning the error.
"
Another possible solution for you, besides using HIS provider, is to create an ADO.Net:OLEDB connection for DB2 and use DataReaderSrc to pull out the data. Do you want to try that out? Let me know if any questions on using DataReaderSrc.

Thanks
Wenyang

|||

Wenyang, thanks for your reply... It does not sound too uplifting. I am going to give the ADO.NET connection a go and see if I have any luck setting up a Data Reader Source.

In the meantime: Is there any way to get a hold of the MS OLE DB Provider for DB2 without getting HIS? And if you install the trial version of HIS, is the provider also limited by the 120 day period? Also, does MS consider shipping this provider as a part of SQL Server 2005?

Thanks
Michael

|||You may want to switch to Client Access ODBC. Per IBM, the OLE DB Provider does not provide as much functionality as their ODBC driver.
It has been about 8 months since I've connected to an ISeries, but if I remember correctly the OLE DB provider does not allow you to specify a cursor mode which is why the OpenRowset is slow (the OLE provider does not support scrollable cursors).
I would try connecting to your ISeries via the .Net ODBC Driver and use the DataReader Source. That may resolve some of your issues. I only ever had one problem with the ODBC Driver and that was with PHP/ODBC where I needed to specify the cursor mode. As far as MS development packages were concerned, I never had any issues.
Larry

|||>does MS consider shipping this provider as a part of SQL Server 2005?
Yes. I was told it will be on the web as part of the feature pack of SQL Server 2005 RTM

Thanks
Wenyang|||

I am trying to connect to a DB2 database to retrieve data but always receive errors. I am using an ODBC connection using a datasource (iSeries Access ODBC Driver using SQL naming conventions) with a DataReader Source. I am attempting a simple SLQ statement - 'Select * from tablename'

The error is usually in the following format '[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - tablename in QGPL type *FILE not found.' Am I missing some setting in my DSN? Any help is appreciated.

Dan

|||It has been some time since I've used an iSeries, but the error could

because your account does not have a default library setup. I

can't remember how to setup default library though.

Try changing your SQL to "select * from library.table" and see if that works.

Larry Pope|||

Thanks alot Larry! It was the simple fix you specified. I had gotten other wierd 'QGPL' errors when I played around with the SQL and I am glad this was it!

One problem was my development environment was totally SQL Server while the target environment as AS400. But after your tip and changing the tables schema name in SQL 2005 (owner in 2000), I was able to develop/deploy.

Dan

|||

Hi,

Has anyone come up with a sensible answer on this issue?

Receiving exactly the same error using the IBM ASQL OLE DB R5V3 provider.

If Data Access Mode is:

1. Table or view - works fine

2. SQL Command - The preview button works and displays the information correctly. but fails with error below when executing.

The command is a simple "Select * from ..." a single table.

If it is a provider issue why does it work fully for the Table or View mode, and in Preview for the SQL command but not when run as an SQL Command?

[ASQL SQL Command] Error: An OLE DB error has occurred. Error code: 0x80040E00.

[DTS.Pipeline] Error: The PrimeOutput method on component "ASQL SQL Command" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

|||

Has anyone found a workaround for this problem? Table/View is not an option if you want to do a lookup or merge join, since you have to specify an order by clause, which is not possible with Table/View.

I am also having strange problems using the DTS Import/Export tool whether using Table/View or SQL Command. Table/View doesn't translate decimal data types properly and SQLCommand can't seem to map the source and destination columns (although this may be a related issue).

I've asked this on another thread, but can anyone from Microsoft speak to fixes for these issues?

Thanks

|||

The only workaround I have is to use do a two step process.

1. OLE DB Source using Client Access driver and use the Table/View option to dump the table(s) into SQL Server

2. Use an SQL OLE DB Source to run an SQL Command against the dumped table(s) - which works.....

Obviously not ideal.

|||

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

|||

I am getting a very similiar error ...

[IBM][iSeries Access ODBC Driver] DB2 UDB]SQL0204 - CYYMMDD in *LIBL TYPE *N not found.'

I can pull data just fine when I use a static search argument, but receive this error while trying to use a date function.

SELECT column1, column2
FROM library.file
WHERE column = CYYMMDD(CURRENT DATE - 1 DAYS)

I know this function exists, because I use it in queries created by a 3rd party app (ASC Sequel Viewpoint).

Any ideas?

Thank you much.

|||

KrustyDeKlown wrote:

I didn't experience quite the same problems but had inconsistant results accessing the AS400 using the OLEDB Source. I instead switched to using an ADO.Net ODBC connection and then using the DataReader Source. In there you can specify your command, you must also specify the library and the file to get it to work properly. (select * from library.file order by whatever) Hope this helps.

One thing I will say about switching to this method is that the execution time of my package was cut by almost 2/3.

-Krusty

Here, here! This worked extremely well for me.|||

HI,

I'm Facing the same Problem. Getting same error as you did mention.

what i'm trying to achieve:

Want to take Incremental data ( based on datetime stamp ) from AS400 and dump it to DB2 database using SQL Server 2005.

Have source connection to ISeries AS400 and destinaton connection to DB2 Database.

For fetching data i have two approaches:

1> Using OLEDB source connection : Not able to see the table names associated with any Library in "Table or View mode". can write a SQL query in the "SQL command" mode and see.preview of the table; but get error on execution of task: see Error # 1 below:

2> Using DataReader Source: I can use datareader source with an ADO.Net connection: I get data easily with this mode, but how do i specify the incremental data part in the query within that task.

Even if i stick to the approach 2 ( Datareader source ), i'm not able to insert data into DB2.

1> Here i'm using OLEDB destination to insert data into a DB2 database. not been able to do the same. Task fails with following error (see ERROR # 2 )

## Now my question is that how do i insert data into DB2 database ?

## and what are the alternatives available to insert data into DB2 database.

What solution did you get to your problem, mine is similar to yours....

HELP plz !!!!!!

Error # 1:

Error: 0xC0202009 at DB2 PRODUCTION, OLE DB Source [1]: An OLE DB error has occurred. Error code: 0x80040E00.
Error: 0xC0047038 at DB2 PRODUCTION, DTS.Pipeline: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DB2 PRODUCTION, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Error # 2:

Error: 0xC0202009 at PRODUCTION SERVER with data reader source TO DB2, OLE DB Destination [587]: An OLE DB error has occurred. Error code: 0x80040E53.
Error: 0xC0047022 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: The ProcessInput method on component "OLE DB Destination" (587) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at PRODUCTION SERVER with data reader source TO DB2, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0202009.

Shah