February 10, 2020 at 5:41 pm
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
February 10, 2020 at 6:48 pm
Try installing AS/400 Client Access software on the server and using that provider to setup your linked server.
Sue
February 13, 2020 at 5:01 pm
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.
February 13, 2020 at 5:25 pm
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
February 13, 2020 at 6:47 pm
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.
February 13, 2020 at 6:59 pm
I GOT IT, I figured it out, Thank you very much, I am very appreciative for your assistance.
February 13, 2020 at 7:14 pm
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
February 13, 2020 at 7:52 pm
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.
February 13, 2020 at 8:13 pm
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
February 13, 2020 at 9:55 pm
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
Change is inevitable... Change for the better is not.
February 13, 2020 at 10:05 pm
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.
February 13, 2020 at 10:59 pm
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
February 14, 2020 at 12:38 am
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
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply