linked server with the as/400

[swoodring]

I'm trying to set up a linked server to our as/400 with no luck. the script I uses creates the link server but every time I try to access it it just hangs. In other words when I try to access either through a simple query through query analyzer or when I try to access through enterprise manager under linked server and clikc on the tables icon for the linked server it just hangs. I end up having to kill the processes manually. I did notice under the process info it has either miscellaneous or pageiolatch_sh under the wait type. Not sure what this means or if this is the reason its not working. Here is the code I used to ceate the linked server. I've even tried when creating the linked server using data source parameter instead of the connections string. That did the same thing. I can connect using the DSN in a dts when I set up a connection. So the dsn is set up properly.

exec sp_addlinkedserver
@server = 'TEST4',
@provider = 'MSDASQL',
@srvproduct = 'Microsoft OLE Any DB Provider for ODBC',
@provstr = "ODBC;DSN = AS400; UID= HART; Password = whittman"

Any ideas would be appreiciated. I'd really like to get this set up as a linked server.

Stacy

 

[TomSark]

I have had a similar problem in the past with this as well, but blew it off because there were corrupted files on my server that prevented it from working...

I have some questions, but can't guarantee an answer...

Has this ever worked?

Are you using SNA server or Host Integration server, or client access express?

FYI... pageiolatch_sh is a lock that is created by SQL Server then the storage engine is transferring data to memory to be used by the database engine when the query is being processed...

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[swoodring]

No it never has worked. We had a consulting company that came and set everything up and did the initial work. I'm taking over for them. Looks like they tried to set up linked servers with no sucess. instead they have a file on the 400 that they transfer between the server and the 400 every day.
We use client access to access the 400 when we sign in. I think thats what your getting at here. Not sure what you mean by SNA server or Host Integration server. I'm fairly new to SQL server so bare with me.

Stacy

 

[TomSark]

The reason why I asked is that if you are using OLEDB to connect to the 400, you should be using SNA server or the newer Host Integration server to connect to the 400... This information came to me directly from Microsoft.

I don't know if IBM has produced an OLEDB provider that will work (now) without you having to use SNA server yet, or not...

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[swoodring]

maybe thats my problem I'll have to check with our network people and find out for sure if we have this. thanks for the info.
I've also been told about this web site fro as/400 ole db providers and see if they can be of any help. Tought it might help you out as well.

http://www.hitsw.com/

 

[swoodring]

But I am able to connect to the 400 in a dts package if i set up a connection in the package using an odbc source and another one for sql server and do a tranformation between the 2.

Stacy

 

[TomSark]

You'll notice on the AS/400 link on this web site that SNA server is involved in the communication process...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[swoodring]

I've been doing some research on SNA. It looks like there is some option in client access for SNA but doesn't look like we have a license for it. So after we get SNA server from Microsoft. Would I then just set up a linked server but not with an odbc connection? I'm assuming there would then be another type of driver in the list of provider names. Any info you could send me on SNA and what I would have to do after getting SNA to set up a linked server would be greatly appreciated.

 

[TomSark]

You can start with:

http://www.microsoft.com/sna

I simply know that if you are using OLEDB, which is required for linked servers, you need it...

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[SpeedOLight]

Hi there...

We use Client Access Express, connecting via TCP/IP (NOT SNA) and have successfully setup a linked server.

Tom - Stacey isn't using IBM's OLEDB Provider. She's using the Microsoft OLEDB provider for ODBC and then using Client Access for ODBC.

I'm assuming you're accessing the AS/400 via TCP/IP...

If so, check that you can ping the AS/400 from a command line prompt from the SQL Server box.

If that's OK, then check that you can ODBCPING the AS/400 using your ODBC DSN (your DSN based on your 1st email is AS400) from the SQL Server Box

These two steps insure that you have connectivity. If either of these two don't work, the problem exists outside of SQL Server.

Finally, the configuration you've described looks fine to me for using the linked server.

If you have further problems, feel free to post them and I'll help when I have time.

Regards...Marc
Independent Software Consultant

 

[SpeedOLight]

Oh...and another thing...

You may want to see if you can setup/access the Linked Server outside of code. Set it up via SQL Server's Enterprise Manager rather than through Transact SQL.

Good Luck...

Regards...Marc
Independent Software Consultant

 

[swoodring]

I tried to ping the 400 and odbcping and they both came back fine. I've already tried setting up the linked server using enterprise manager and had the same results were it just hung. I'm new to Sql sever so please bare with me and what might seem like stupid questions to you.

I am able to have a package and create a connection and a tranformation and that works fine. Its just the linked server I can't get set up and it just hangs. Any other suggestions would be greatly appreciated. Thanks in advance.

Stacy

 

[SpeedOLight]

I just want to double check something here, since people sometimes get confused with this. Bear with me if you already know this.

1. The Client Access software and the ODBC Data source name are both setup on the SQL Server box, not the client PC. The query to the AS/400 is ACTUALLY EXECUTED from the SQL Server itself, not the client machine. Hence, all ODBC settings need to be on the SQL Server box.

2. Also, when you issued the PING and the ODBCPING, you were sitting at the SQL Server's console, not a client machine.

3. You are running SQL Server 7.0, correct? You're not running 7.0 emulating SQL Server 6.5, correct?

4. Settings for linked server are pretty basic once all this is setup. The following are the settings we use (looking at it from the Enterprise Manager):
On the General Tab
Provider = Microsoft OLE DB Provider for ODBC
Data Source = YourODBCDataSourceName
Data Access is checked
On the Security Tab
"They will be mapped to" is selected
Remote User = AS400UserAccount
With Password = AS400UserAccountPassword


That should allow you access from SQL Server. One other note: I'm assuming you can access the AS400 via ODBC from other applications not using the Linked Server. If not, the problem may actually reside on the AS/400 not having the correct communication jobs setup for Client Access.

Good Luck...

Regards...Marc
Independent Software Consultant

 

[swoodring]

in regard to your questions.
I have the data source name as400 set up on the server its also set up on my machine - client.

when I tried the ping and odbc ping I was on the server. I actually tried it both from my laptop - client machine and from the server. They both worked.

We are running SQL 7.0. We never had 6.5 we started with 7.0.

The setting for the linked server are set up like you have them. I just tried creating another one again through enterprise manager with the above setting and it did the same just hung when I clicked on tables.

yes I can access the 400 from other applications not using the linked server. I have dts packages in which I use odbc connections and a sql server connection and am able to get the data from the 400 to the sql server by doing a transformation between the. Just can't seem to get the linked server to work.

 

[SpeedOLight]

Hmmm...that certainly sounds correct. Maybe it has to do with the software versions you are running...

We're using Client Access Express for Windows
Version 4, Release 4, Modification Level 0,
Service Level None

Our AS/400 is running V4R3 (and we're pretty current on our PTFs, but I don't know exactly which ones are applied...sorry, my expertise is more on the client side )

Good Luck...

Regards...Marc
Independent Software Consultant

 

[swoodring]

our as/400 is running v4r4 and we are running the regular client access version 3 release 2 modification level 0

Maybe I'll have to look into upgrading client access to express. At least thats whats on my machine. I'll have to check the server not sure whats on there.

Thanks for trying to help. I appreciate you taking the time.

 

[SpeedOLight]

No Problem...

If Client Access Express solves the issue, would you be sure to let this forum know? Thanks...

Good Luck...

Regards...Marc
Independent Software Consultant

 

[swoodring]

Actually since I only had client access running and an older version I checked the server. I noticed the server had client access express version 4 release 4 running so i tried creating the link server from there. And then tried accessing it from there but still the same problem it just hung there.

I have a work around not using a linked server but eventually I'd like to use the linked server instead of having to send the data to the 400 and then accessing it from there.

 

[SpeedOLight]

A few final thoughts...

1. You said that some contractors worked on the original setup. Is it possible that it worked initially for them and then at a later time it stopped working? Is the SQL Server a machine that you can reboot? If so, try this and immediately upon it restarting attempt to access the link server through SQL Server Enterprise Administrator.

Ok...now I know I sound like a Help Desk when I tell you to reboot, but here's the reason why. I've seen an AS/400 Administrator set up a batch job to kill jobs without any CPU activity. It is possible it may have killed the communication job utilized by your SQL Server Linked Server. This would cause the hanging problem you've described - SQL Server is waiting for a response from the communicatino job. By rebooting the SQL Server, it forces SQL Server to start a new communication job on the AS/400. This is a bit of a long shot, but I have seen it occur in the past.

2. Is it possible the version of Client Access installed on the AS/400 isn't compatable with Client Access Express on the SQL Server? Do any of your working applications use Client Access Express, or do they all use Client Access?

3. If you'd like, you can also list the ODBC settings (without the IP address or the UserID/Password), and I'll compare it to our settings. I doubt that's it, since you can access it from other machines.

That's about all I can think of...

Regards...Marc
Independent Software Consultant

 

[swoodring]

I've actually already tried suggestion #1 a few time rebooting and that didn't do anything. I noticed after my jobs hung and I manually killed them they were still out in the current activity. Under a SPID and even when I said kill process they didn't seem to go away so I did a reboot.

I don't beleive the consultants ever got it to work. I thinkt he 2 out there are there attempts to get it to work. Because in the jobs they have a file that they send from the sql server to 400.

As to express not being compatable with whats on 400 not sure. We are not using it anywhere else. All our workstations have regular client access. How do I tell what verion is installed on the 400.

Stacy

Our odbc settings (system dsn)
under general tab
data source name: as400
Descripion: Client Access ODBC source
System: S001.performanceinc.com

Under the server
default library: retail
commit mode: commit immediate none

Under packages
enable extended dynamic package support is checked off.
default package library: Qgpl

Under performance:
enable lazy close support is checked off.
enable pre-fetch during execute is checked off.
record blocking type:block except if for update of specified.
size: 512
os400 libary view: default library list

Under Language
sorty type:sort based on hex values

Under other
Connection type:read/write all sql statments allowed
object description type: os400
scrollabe cursor: scrollabe unless rowset size is 1

Under translation
translation: do not translate CCSID

Under format
Naming convention: sql naming covention
decimal seperator .period

Time format: *HMS
seperator:colon

Date Format: *ISo