Linked Server to AS/400

Question

Post reply

Linked Server to AS/400

bbron

SSC Eights!

Points: 936
More actions
January 19, 2004 at 9:16 pm

#161093

Hi,
hehehe, just discovered that I can post! Thought that only certain "members" could post questions
Anyway, I'm using SQL 2000 but our primary source of data is still from IBM AS/400 or DB2 (is it the same thing anyway). Downloading data is quite alright as I'm using DTS packages (thought the naming convention is a bit funny). However, I've problems with uploading information. I could not use DTS. The funny thing is that I can see that it has created the relevant table/file in the relevant library on AS400 but I could not insert records.
I tried to do a linked server but no success either. So, my current method is to create a DTS that export the relevant data into a text file and use a DOS utility, using the programs provided by the Client Access Express For windows (an emulator I presume?) to do the actual uploading. The problem with that, other than it's not really Straight Through is that the password to AS400 is revealed in plain text (required to connect to AS400).
Any idea?

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

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

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

You can using Vortex.

http://www.trifox.com/vortex/index.html

I used their drivers for Adabas > SQL which worked well.

They have for DB2 as well.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 2

Is this a new table that you are trying to load into or an existing one. I use linked servers all of the time to an as400 and as long as the 400 file is journaled I have no problem.

Tom

bbron SSC Eights! Points: 936 More actions · Answer 3

Tried the website but not much help there it seems? And it seems I'd have to pay something...

Tom, I supposed you are refer to when I upload data into AS400. Well, it can be either. Of course, it's preferable if I could simply create a table there with the appropriate structure (just like when I download from AS400). However, I'd say that 90% of the time it would be to upload to an existing table.

Journaled? Here's what I've done:

1. Linked Server: The AS400 IP Address

2. Provide Name: IBM AS400 OLE DB Provider

3. Product Name: Any name

4. Data Source: A created one which works perfectly when downloading

I could see [Tables] and [view] but when I click on them the following appears:

"Error 7302: Could not create an instance of OLE DB Provider 'IBMDAS400'

OLE DB Error trace[Non-interface error: CoCreate of DSO for IBMDAS400 returned

0x80040154]"

Thanks in advance

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 4

Try to create an odbc connection to the as400 on the sql machine and then create a linked server for odbc using that dsn. I never got the ibm oledb provider to work right.

Tom

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

Not specific to your "error"....

We create a linked server on an ODBC entry using Client Access.

CA version must match AS400 O/S etc...

OLEDB not so kind as the ODBC ???

Just thoughts, hope it helps

Once you understand the BITs, all the pieces come together

bbron SSC Eights! Points: 936 More actions · Answer 6

Man!!! It works!!! Well, now I can see those tables on AS400

Any "examples" you might have worked on to "Select" and "insert" data into that newly linked server? (As a matter of fact, I need to find out how to use such linked servers....)

By any chance, did you manage to use DTS to upload data to AS400?

ThomasH SSCertifiable Points: 7437 More actions · Answer 7

Use just like a regular table in SQL2K...

select * from OpenQuery(AS400, 'select * from "F.M.AS400Table" ') AnAS400TableAlias

Insert into OpenQuery(AS400, 'select fld1 from "F.M.AS400Table" ') AnAS400TableAlias

Select QSL2kFld1 From SQLTable

Notice AS400 query was DoubleQuotes around the AS400 table since smoe of your tables on AS400 side might be name/qualified that way.

Did not use DTS.

Once you understand the BITs, all the pieces come together

bbron SSC Eights! Points: 936 More actions · Answer 8

Ahhhh.... yep, that works...

select * from OpenQuery(IBMAS400, 'select * from "SHEAU"."AA_CHEQUE" ')

BUT BUT.... hum.... the number of records returned is kind of restricted..... getting only say 189 records form a table with 10K plus.... etc

Additionally, when I tried the INSERT, I get the errors

Insert into OpenQuery(IBMAS400, 'Select * from "SHEAU"."AA_CHEQUE" ')

Select 11111, 'vvv', 'test name', 999, 1, 'AA'

Server: Msg 7399, Level 16, State 1, Line 5

OLE DB provider 'MSDASQL' reported an error.

[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7008 - AA_CHEQUE in SHEAU not valid for operation.]

OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].

I wonder whether it's due to the AS400 side being closed or something??!!!

ThomasH SSCertifiable Points: 7437 More actions · Answer 9

I do not know if "SHEAU"."AA_CHEQUE" is identicle to "SHEAU.AA_CHEQUE" ????

Seems I came across "not getting all the rows" thing... Will look into...

On the INSERT side, try enumerating the individual column names. also you may want to limit the rows returned in the AS400 query part , when performing the insert... use WHERE 1=2 or something providing zero rows from AS400.... maybe WHERE fld1 = ''xxxxx'' when fld1 has an index on the AS400 side. It's quicker, because the actual SQL query is being performed.

Also, make a simple test table on AS400, get your SELECT, DELETE, INSERT working on it, then move to "real" tables. Updating records on the AS400 side may require journaling, and various ODBC "update" configuration stuff.

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 10

The "not getting all the rows" thing...

also ODBC config settings.

Sorry no details, shoot I don't even have an AS400 here /

Play with some of them, remember, use simple test on test table 1st.

Once you understand the BITs, all the pieces come together

bbron SSC Eights! Points: 936 More actions · Answer 11

"SHEAU.AA_CHEQUE" won't work....

I'll try the rest in a couple of hours after getting some sleep.

Talking about test table etc... Should I be worried that such "link" is permanent and could somehow affect the AS400 side?

My AS400 DBA will kill me if anything happens to his "programs"... Can you imagine this, I've no access to the table until all his "programs" have been completed. When asked to simply copy the relevant tables/files, it's pointed out (no less by IBM support as claimed) such "copy/duplicate" process is not 100% safe. Gosh, if eveything needs to be 100% fail-proof, the world would not be interesting anymore!!!

Alright... think the mind is already shutting down.....

Thanks again for the help.

ThomasH SSCertifiable Points: 7437 More actions · Answer 12

Here is what I say:

"The Client Access ODBC driver has many configuration options that affect the way the Windows PC and AS400 interact. These options are not staightforward, and the setting values may vary depending on the AS400 O/S version, Client Access version, Windows Serverpacks etc. so they are not listed anywhere in a "best settings for.... list". In order to effectively provide you with the most fail-proof code , I request the use of, and access to a couple or few AS400 "test tables" with enough data to simulate the data conditions I might encounter during production. This will allow me to configure the Server-to-Server data connection without affecting any production databases on the chance/likelyhood that some configuration options may produce incorrect results. This is especially ture, since I am not only querying, but also updating data on the AS400."

Also, KNOW your data, so you can suggest the appropriate test data.

hope this helps.

Once you understand the BITs, all the pieces come together

ThomasH SSCertifiable Points: 7437 More actions · Answer 13

One other thing, sometimes when perfoming a query in Q/A to the AS400, a "Client Access provided Login" dialog box may appear under certain configurations. If this is the case, remember that if such a dialog box "appears" while SQL Server Agent, or SQL Server service itself is doing a task, these are "screen less" services, and the dialog box appears in computer never-never land, and can not be filled out. If you see a query that is "Hanging forever" this is most likely the case. Stop-Start of SQL Server service is required to get rid of the connection "attempt" and the SPID. This may even happen in Q/A ??? but ODBC confuguration can be "tweaked" to have this not happen. It is usually on the 1st attempt to access the AS400, or upon the 1st access after a reboot of the AS400. So test your stuff having your SQL Server PC rebooted from scratch, then test this "1st time" access. Once it's good, should be OK 2nd, 3rd.... attempt onward. This might (???) be for each different security context.

sorry, was more than "one other thing...."

Once you understand the BITs, all the pieces come together

bbron SSC Eights! Points: 936 More actions · Answer 14

Thanks for all the help... I tried a few times but can't seem to achieve anything. Not too sure where to tweak the ODBC configuration. I'm just hoping someone has done such thing and might help me a little bit on that.

Otherwise, I'll stick to DTS for downloading and using DOS batch for uploading, not the prettiest and easiest but that will work fine for the time being.