none
Access DB2 on AS/400 using IBM OLE DB Provider

    Question

  • 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?).

    Tuesday, October 11, 2005 7:04 PM

Answers

  • Could you try to select .NET provider for ODBC in the wizard and then pass it the DSN you created?

     

    HTH.

    Wednesday, June 06, 2007 4:59 AM

All replies

  • 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

    Tuesday, October 11, 2005 10:14 PM
  • Wenyang, thanks for your reply... It does not sound too uplifting. Sad 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

    • Proposed as answer by deepakjg Wednesday, October 05, 2011 5:35 PM
    Wednesday, October 12, 2005 4:34 AM
  • 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




    Wednesday, October 12, 2005 2:55 PM
  • >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
    Wednesday, October 12, 2005 4:31 PM
  • 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

    Thursday, January 26, 2006 5:42 PM
  • 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
    Thursday, January 26, 2006 6:27 PM
  • 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

    Friday, January 27, 2006 5:43 PM
  • 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.

    Thursday, February 09, 2006 1:10 PM
  • 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

    Thursday, February 16, 2006 9:34 PM
  • 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.

     

    Friday, February 17, 2006 4:41 PM
  • 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

    Friday, February 17, 2006 9:17 PM
  • 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.

    Tuesday, February 28, 2006 8:51 PM
  •  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.
    Friday, September 29, 2006 6:37 PM
  • 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

    Monday, January 01, 2007 12:44 PM
  • Hi!

    I have same situation with OLE DB for DB2. For incremental read from iSeries I have to use time parameters. How much I know only possibility is useing OLE DB Source (Data Reader Source has not parameter support?). But OLE DB give me follow error message. Foolish thing is, that this construction has work week ago, but today not .

    I will  appreciate any idea!

    Ain

    • [OLE DB Source [1]] Error: An OLE DB error has occurred. Error code: 0x80040E00.
    • [DTS.Pipeline] Error: 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.
    • [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
    • [DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
    • [DTS.Pipeline] Error: 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.
    • [DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
    • [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.

     

     

    Wednesday, January 24, 2007 9:48 AM
  • Why dont you try ODBC driver for iSeries... for me it is working perfectly fine for last 7 months... just have a go....

    regards,

    Anas

    Wednesday, January 24, 2007 10:12 AM
  •  Zadoras wrote:

    Why dont you try ODBC driver for iSeries... for me it is working perfectly fine for last 7 months... just have a go....

    regards,

    Anas

    I am using the ODBC drivers for iSeries too for over a year and everything works well...

    You might also try a combination of Linked Server and Stored procedure, i use this to compare timestamp on AS/400 for our SQL Server DW. (if using a linked server don't forget to check the "allow inprocess" in the provider properties (i use IBM DB2 UDB for iSeries IBMDA400 OLE DB provider))

     

     

     

    Wednesday, January 24, 2007 1:46 PM
  • Hi Anas!

    I use ODBC for transport data from iSeries to SQL2000 long time. In SQL2000 DTS using parameters was very simple. How I can do it with ODBC Data Source (In SQL2005 Integration service)? Did you try it? (My problem is incremental data load)

    Ain 

     

    Wednesday, January 24, 2007 1:58 PM
  •  AinT wrote:

    Hi Anas!

    I use ODBC for transport data from iSeries to SQL2000 long time. In SQL2000 DTS using parameters was very simple. How I can do it with ODBC Data Source (In SQL2005 Integration service)? Did you try it? (My problem is incremental data load)

    Ain 

     

     

    I am also loading only the data which have been modified since my last extract.... if i m have understood ur scenario correctly, it might be achived by the following steps

    1. Create Data Flow ITem and create ODBC connection for iSeries

    2. Db click the data flow item and create and inside data flow item create Datareader Source component and set connection to ODBC ( which created in Step 1)

    3. now come back to Data flow component and click properties.

    4. Open the expression tab from properties and select property <Datareader Source name>.SQL Command

    5. now you can set up the dynamic SQL by using expresion, may be setting up the time value in some Package Variables

    Note: set the variable to datatype String otherwise u might have error

    i m not good at explanation..... if u dont understand please write back to me , i will try to clarify

    Wednesday, January 24, 2007 2:17 PM
  • Good Morning and thanks for you replay!

    I tried your suggestion.

    I can write in property “<Datareader Source name>.SQL Command” for example:

     "SELECT * FROM i5LIB.DATATABLE WHERE CrTime >= ?"

    But how I make mapping between my SQL parameters and package variables? I didn’t find this place. Do you know any trick?

    Ain

    Thursday, January 25, 2007 8:10 AM
  •  AinT wrote:

    Good Morning and thanks for you replay!

    I tried your suggestion.

    I can write in property “<Datareader Source name>.SQL Command” for example:

     "SELECT * FROM i5LIB.DATATABLE WHERE CrTime >= ?"

    But how I make mapping between my SQL parameters and package variables? I didn’t find this place. Do you know any trick?

    Ain

    Hi Ain

    i didnt get u 100%.... what do u want to do exatly... why u need variable.. can tell me bit more about it.... i might be able to suggest you..

    regards,

    Anas

    Thursday, January 25, 2007 3:50 PM
  • Hi

    I have found that the ODBC/data reader solution is the way to go. The data reader does not support parameter substitution  directly so we must use another method.

    My solution is to use dynamic sql. In the data reader I set the sql from an expression that references a package variable. I insert a simple script component before the data reader to assemble my sql statement which is then used to populate my package variable.

    It is necessary to set the DelayValidation property of the dataflow task to true to make this work.

     

    Lance

    Thursday, January 25, 2007 8:48 PM
  • Hi!

    I fought create one new table in my source system where insert necessary values and one static “SQL View” witch will join my data with these “parameter table”.  But I think your idea is better. I try it. Thank you Lance!

     

    Ain

     

    Friday, January 26, 2007 6:42 AM
  • I have been using the Client Access driver on my SQL 2000 server and importing data from our AS400 via DTS for years.  We now have a SQL2005 server.  I have tried using the IBM DB2 UDB for iSeries OLE DB Provider but cannot get it configured to connect to our AS400 without errors.  I've installed Client Access (V5R2) on the SQL 2005 server but the Client Access ODBC drivers do not display as an option in the Import Wizard (SSIS?).  I can create a system DSN using the Client Access ODBC driver but I don't know how to tell the Import Wizard to use it.

     

    I've been tyring to understand the other posts with regarding this issue but I'm clearly not understanding what I need to do.  To work around this issue I am importing the AS400 file to my SQL 2000 server, then importing from it to my database on SQL 2005.  I pretty much bring the AS400 file over as is to a dummy file, then I do conversion on it in the next transfer to the actual file I want the data to go into.  I'm expecting I'll have to do this two-step approach but I should be able to just do it all on the same server.  Are there some very basic instructions for importing from an AS400 to SQL 2005 using the Import wizard anywhere?  This shouldn't be that complicated.

    Monday, June 04, 2007 10:49 PM
  • Could you try to select .NET provider for ODBC in the wizard and then pass it the DSN you created?

     

    HTH.

    Wednesday, June 06, 2007 4:59 AM
  •  

    Hello

     

    Just wanted to share some info I found after a long research. Many people seem to have this issue and no-one has been willing to share a solution.

    It looks like IBM has accepted the problem and created a PTF (APAR SE27941) to fix it.

     

    I have not tried it, but it really describes the problem.

     

    As we are behind loading PTF's on our system, maybe with this post someone will beat me to it and can let us know if it really works.

    This is the link to the APAR description ..

    http://www-912.ibm.com/n_dir/nas4apar.NSF/c79815e083182fec862564c00079d117/a802b6eb29d32cac8625726c0041efc9?OpenDocument

     

    If you search in the IBM PTF database using the APAR number you will get the details of the cumulative PTF.

     

    Good luck.. and let us know if it works...

    LEO

     

     

     

    Thursday, June 07, 2007 8:43 AM
  • I was able to get the .Net provider for ODBC with my DSN using the Client Acess ODBC driver passed to it to work - AFTER we reinstalled Client Access selecting All components and installed the Service Pack as well.

     

    Thank you!

     

    I still can't get the IBM DB2 UDB Provider to work.

    Monday, June 11, 2007 7:38 PM
  • Hello Dan,

    Am having an asp application which connects to AS 400 using the ODBC connection using a datasource.I would like to know the following- as i do not have the application code,

    I have the connection propery set to a default User ID (Say the User ID is TEST) in the datasource- but i would like to know where will the password to this AS 400 User ID (TEST) be provided?

    Could it be possibly from the application?

    Could there be any other way by which the application is passing the password for the AS 400 login (TEST)?As i check the application error logs, i get the below errors,Microsoft OLE DB Provider for ODBC Drivers:[IBM][iSeries Access ODBC Driver]Communication link failure.
     comm rc=8011 - CWBSY0011
     Microsoft OLE DB Provider for ODBC Drivers: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 -GAUT in MPRDTA type *FILE not found

    Wednesday, July 03, 2013 12:59 PM