Connecting to AS400

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    In need of some assistance, I am trying to connect to AS400 machine to pull data from it, as a linked server. I am still getting error when testing the connection.

    I created a ODBC in the ODBC Data source Administrator

    I went through the Linked Server setup steps. Chose Microsoft OLE DB Provider for ODBC Drivers.  set the product name, dtatsource location and provider string I left blank, filled in the catalog. This is the error I'm getting.

     

     

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    The linked server has been created but failed a connection test. Do you want to keep the linked server?

    ------------------------------

    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "TESTAS400".

    OLE DB provider "MSDASQL" for linked server "TESTAS400" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2070&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

     

     

  • Sue_H

    SSC Guru

    Points: 90673

    Try installing AS/400 Client Access software on the server and using that provider to setup your linked server.

    Sue

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    So install it on the AS400 machine? I am running SQL from my local machine, as a development box until I show proof of concept, So I am trying to connect to the AS400 from my machine, I have the IBM I Access Client solution installed on my machine, I can get to it from that, schemas and Tables, but this locks up when trying to copy large number of rows, That's why I would like to get the AS400 as a Linked Server to pull directly from SQL, any good examples of setting this up, I have looked and I have tried them with no success.

  • Sue_H

    SSC Guru

    Points: 90673

    If you want to create a linked server, you would install the Client Access on the SQL Server as well.

    IBM has an older documentation that gives the general idea of how to set these up.

    Configuring an OLE DB Provider on an SQL Server

    Make sure to select Allow inprocess for the provider properties.

    Sue

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    Awesome, Thank you I got a successful connection, now if you don't mind I am trying to bring the raw data into SQL, task>import data, what is a good way to do this? I am essentially trying to bring all the data into SQL from AS400.

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    I GOT IT, I figured it out, Thank you very much, I am very appreciative for your assistance.

  • Sue_H

    SSC Guru

    Points: 90673

    You are very welcome, glad I could be of assistance. And thanks a lot for posting back as that really helps when people provide updates like you have.

    Sue

     

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    I'm not understanding this, Is the SQL table only at length of 3? I have increased all of the sizes on the SQL side so not sure why I am getting this on all fields.

    Truncation may occur due to inserting data from data flow column "BLRITM" with a length of 255 to database column "BLRITM" with a length of 3.

  • Sue_H

    SSC Guru

    Points: 90673

    My first guess is that it's a mapping issue. And mapping with an AS400 data source and using the import/export wizard is likely to be a headache. This article might give you some ideas around handling those issue:

    Import Export Wizard Mapping Files

    FWIW....I did something similar before and personally I think the most painless way is to do an export of the tables from the AS400 side - you can export the data from the tables out as CSV files. And then import the CSV files into SQL Server.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 996640

    cbrammer1219 wrote:

    I GOT IT, I figured it out, Thank you very much, I am very appreciative for your assistance.

    That's nice.  Would you mind sharing what you did... maybe even as some code?  I'll also state that if you didn't use OPENQUERY to do it, you might have just built yourself one hell of a performance issue.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • cbrammer1219

    SSCarpal Tunnel

    Points: 4457

    I followed the advice Sue had given, I read the document she provided the link to and setup the link server, I can query the as400 tables as I would a sql table. I am trying to setup some jobs to extract the data from as400, running into a little bit of issues with data truncation as mentioned in previous post, so trying to work through that. if I have to I will select from as400 and insert into table within a stored procedure, IF I HAVE TO.... I am doing this because the company I just started working for is using QlikSense and using excel as datasources which has horrible performance, so I want to get Qlik setup using SQL.

  • Sue_H

    SSC Guru

    Points: 90673

    One thing to check on with whoever is managing the AS400 is to checking on the indexing to see if you can use Index As Access Path which is a setting you can enable on the provider. Whoever the DBA is for the AS400 would know the data you are hitting and if this is something you can use. When we had one other thing to query regularly on the AS400 (after getting the csv dumps) having that enabled was very fast, pretty much like a local table. But the indexing has to be set a certain way or something (can't remember the details) on the AS400 side for it to work. If it's not and you enable that setting, queries won't work. Worth checking into though.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 996640

    cbrammer1219 wrote:

    I followed the advice Sue had given, I read the document she provided the link to and setup the link server, I can query the as400 tables as I would a sql table. I am trying to setup some jobs to extract the data from as400, running into a little bit of issues with data truncation as mentioned in previous post, so trying to work through that. if I have to I will select from as400 and insert into table within a stored procedure, IF I HAVE TO.... I am doing this because the company I just started working for is using QlikSense and using excel as datasources which has horrible performance, so I want to get Qlik setup using SQL.

    Awesome feedback!  Thanks for laying out what you actually did.

    Shifting gears a bit, while it's tempting to access the tables across the linked server the same way you would with local SQL Server tables, you might want to consider using OPENQUERY instead.  It passes the query you want (which must be written in the same language as what the AS400 understands... most likely DB2) back to the AS400 and, in the presence of the correct WHERE clause and if there are the equivalent to what we know as a index on our side (IIRC, they're referred to as "Journaling" on the DB2 side)  is present on the "file" (DB2 uses "files" rather than "tables" as we know them), you can get quite the performance improvement.

    I helped some folks do just that yesterday and it cut their report generation time down from a full 2 hours to just 17 seconds.  It was all thanks to OPENQUERY.  And example of OPENQUERY is also contained in the awesome link that Sue posted.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply