Linking AS/400 with SQL

Hello,
Can anyone tell me where I can find info on how to do
this?
Thanks.BOL has some information such as the topic "OLE DB Provider for DB2". You
can also find lots of general information on DTS, linked servers, and
replication (DB2 can be a subscriber to SQL2K).
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi there Lina,
Here are my experiences and tips from the field...
1) On your SQL server create a System DSN for the AS400 you are trying
to connect to. It MUST be a System DSN, I couldn't work out why, and
quite frankly I don't care, just accept it, its the way the world is.
The DSN for this example will be called "MYAS"
2) When you set up your System DSN I changed the following:-
Under TAB Server, I changed the Default Libraries to the name of the
library I was working on.
Under TAB Package(s) I changed the Default Package Library to the one
I was working on.
Under TAB Other, set Scrollable Cursor to Allways scrollable.
3) On your SQL server, create a linked server. Use the Microsoft OLE
DB for ODBC Provider. Under Provider Options, set the following to
True:-
Allow InProcess
Non transacted Updates
Linked Server Name is "MYAS" (this is what you will refer to it as in
code)
Product Name is "MYAS" (from Above)
Data Source is "MYAS" (from Above)
Under Server Options set the RPC and RPC Out ticks to ON
5) Set your security to how you want it.
That is it for the Setup
From ISQLW query analyzer:-
select * from
OPENQUERY(MYAS, 'Select * from library.file')
insert into
OPENQUERY (MYAS, 'Select * from library.file')
select '1', '2', 3, 'Test123', 1, '', '', 23
delete from
OPENQUERY(MYAS, 'SELECT * FROM LIBRARY.FILE')
where REQ = 23
Tips for above... If you want to Insert/Update/Delete you MUST turn on
Journalling. There is an option in the ODBC driver setup COMMIT =
*NONE which is meant to override this, but it doesn't work.
The delete statement is case sensative, and the insert/select is not -
go figure!!! So you will note above that I have used uppercase in the
delete statement.
I hope this helps you out, I didn't run an update, and god willing I
won't have to. This has been THE MOST PAINFUL experience in my life,
and with any luck I will not have anything to do with it again from
this point on.
"Lina" <anonymous@.discussions.microsoft.com> wrote in message news:<1503701c415ac$25dfd200$
a301280a@.phx.gbl>...
> Hello,
> Can anyone tell me where I can find info on how to do
> this?
> Thanks.|||Hello,
StarQuest has a product called StarQuest Data Replicator, quite easy
to setup and very fast.
Bob
"Lina" <anonymous@.discussions.microsoft.com> wrote in message news:<1503701c415ac$25dfd200$
a301280a@.phx.gbl>...
> Hello,
> Can anyone tell me where I can find info on how to do
> this?
> Thanks.|||Hi,
I followed your guideline on how to create the linked server, but when I try
to execute the openrowset in ISQLW, it tells me "Executing query batch" and
seems to hang-on. I waited for over 30 minutes but it did not receive any
response from AS and I could not cancel the execution of the query: I had to
stop ISQLW with Task Manager.
Where did I go wrong?
Thanks
<jpo@.bigpond.net.au> wrote in message
news:f30cd675.0403291705.48f37980@.posting.google.com...
> Hi there Lina,
> Here are my experiences and tips from the field...
> 1) On your SQL server create a System DSN for the AS400 you are trying
> to connect to. It MUST be a System DSN, I couldn't work out why, and
> quite frankly I don't care, just accept it, its the way the world is.
> The DSN for this example will be called "MYAS"
> 2) When you set up your System DSN I changed the following:-
> Under TAB Server, I changed the Default Libraries to the name of the
> library I was working on.
> Under TAB Package(s) I changed the Default Package Library to the one
> I was working on.
> Under TAB Other, set Scrollable Cursor to Allways scrollable.
> 3) On your SQL server, create a linked server. Use the Microsoft OLE
> DB for ODBC Provider. Under Provider Options, set the following to
> True:-
> Allow InProcess
> Non transacted Updates
> Linked Server Name is "MYAS" (this is what you will refer to it as in
> code)
> Product Name is "MYAS" (from Above)
> Data Source is "MYAS" (from Above)
> Under Server Options set the RPC and RPC Out ticks to ON
> 5) Set your security to how you want it.
> That is it for the Setup
> From ISQLW query analyzer:-
> select * from
> OPENQUERY(MYAS, 'Select * from library.file')
> insert into
> OPENQUERY (MYAS, 'Select * from library.file')
> select '1', '2', 3, 'Test123', 1, '', '', 23
> delete from
> OPENQUERY(MYAS, 'SELECT * FROM LIBRARY.FILE')
> where REQ = 23
>
> Tips for above... If you want to Insert/Update/Delete you MUST turn on
> Journalling. There is an option in the ODBC driver setup COMMIT =
> *NONE which is meant to override this, but it doesn't work.
> The delete statement is case sensative, and the insert/select is not -
> go figure!!! So you will note above that I have used uppercase in the
> delete statement.
> I hope this helps you out, I didn't run an update, and god willing I
> won't have to. This has been THE MOST PAINFUL experience in my life,
> and with any luck I will not have anything to do with it again from
> this point on.
> "Lina" <anonymous@.discussions.microsoft.com> wrote in message
news:<1503701c415ac$25dfd200$a301280a@.phx.gbl>...
>|||"Fabio Benedini" <f.benedini@.magconsulting.it> wrote in message news:<kFRcc.112608$Kc3.3732
630@.twister2.libero.it>...
> Hi,
> I followed your guideline on how to create the linked server, but when I t
ry
> to execute the openrowset in ISQLW, it tells me "Executing query batch" an
d
> seems to hang-on. I waited for over 30 minutes but it did not receive any
> response from AS and I could not cancel the execution of the query: I had
to
> stop ISQLW with Task Manager.
> Where did I go wrong?
> Thanks
>
Follow ups from our Production migration....
1) the Account that SQL Server is running MUST be a user account. The
AS400 ODBC configuration is by user, even though it is a system DSN -
you can't argue, its the way it is.
2) Log on as the Account you have configured to use as your SQL
account and set up your DSN settings while logged on as that user.
3) Go and make these changes to the registry as per the URL bellow, it
is so that you don't have to logon every time the client interface
starts up.
http://www.faqts.com/knowledge_base...aid/7054/fid/14
Good luck, you may need it.