I am working on a project where I need to connect some Windows 2000
(SP3) servers to an IBM AS/400 database. I'm using IBM's Client Access
OLE DB driver (V5R2 with SI08894 patch installed) on each server. The
first serve is dual-processor, running SQL Server 2000 (SP3), and I
have a DTS package that is intended to copy data from the AS/400 to
some SQL Server tables. Problems:

1. When setting up the connection in the DTS package, saving the
AS/400 connection password with the package is problematic. The Blank
Password checkbox in the connection properties won't stay unchecked,
and if I type in a password, sometimes when I close the connection
properties it is saved and sometimes it is not. Of course, if it is
not, then the DTS package fails when it is scheduled to run
automatically.

2. When I do get a DTS package to execute, it seems that IBM's OLE
driver only allows a single connection to the AS/400. I say this
because only one data transformation task will run at a time. Using a
previous AS/400 OLE driver, I would see at least two data
transfformation tasks operating in parallel. I have installed the
registry key that IBM says is needed to enable connection pooling in
V5R2, but don't know of any tools that let me see what is really going
on with OLE connections. Is there some magic registry entry (or file
to edit) that sets the maximum number of connections? I know this is
supposed to be automatic, but it appears that only one connection is
being opened at any one time.

3. If I create the DTS package on my development machine (running
Win2K SP3 and having the same version of the IBM driver installed), it
will execute fine from there. If I go to the server machine and
execute the DTS package on that machine, then the package fails. If
from the server machine I edit the DTS package, and create a new
connection (with the IBM OLE driver selected), and then change all of
the connection objects in the package to use the new connection, the
package will execute fine. In other words, there seems to be something
different about the IBM OLE driver running on my workstation and on
the server (even though they are identical) which causes a DTS package
created on one machine to not run on the other. I've never had this
problem before with other DTS packages and other drivers for the
AS/400.

My other server is single processor, running IIS, with some ASP.NET
web services accessing the AS/400 database. Everything is fine here,
except the single connection limitation seems to apply here (if
several people hit the web services at once, one of them can time out
as it is waiting on a connection to the AS/400, since another web
service is using it).

Any ideas or help that you can offer would be greatly appreciated! If
there are any tools out there that help look into a system and see
what is going on with its OLE drivers, that might help figure things
out. Thanks in advance!

Jeff Roloff

Trash the 400.
an Intel 4 way will smoke it anyway................

1) DTS steps are dependent on each other.
You have to right-click properties and allow concurrent steps to run.
I think there is a package option for this too.


2) DTS packages HATE to be moved. Best to seach the SQLDTS.COM
for the change package owner hidden stored procedure.
Maybe you can Move the Package via EM.

Finally, once you transfer the 400 data, kill the box.
Intel 4 or 8 way will be cheaper and way faster.
SQL2000 is faster than DB2 anyway..............

do you see any 400 benches here:
thought not

http://www.tpc.org/tpcc/results/tpcc...lttype=cluster

Cheers

Hi Jeff,

Issues with SQL Server and AS/400 are very complex to troubleshoot on
newsgroups. I would suggest that you open an incident with Microsoft
Product Support Services via
http://support.microsoft.com/default...EN-US;CNTACTMS, so that
we can have dedicated engineer to assist you.

Could you let me know the detailed error message when DTS package fails?
You may also enable package error logging and post the error message.


This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------
er.dts
e.de!nntp-relay.ihug.net!ihug.co.nz!nntp.cs.ubc.ca!newsfeed. stanford.edu!pos
tnews1.google.com!not-for-mail

As follow-up, I finally got the DTS package to execute. I had to log
into the SQL server machine using the login that is used to start the
MSSQLSERVER and SQLSERVERAGENT services (and which is also the owner
of the scheduled job that runs the DTS package). The box was logged in
as Administrator, so permissions were not the issue here. I then
edited the DTS package, opened the IBM OLE DB connection object, and
after several tries I got the connection password to stick. This
allowed the package to run successfully. It seems that the connection
password for IBM Client Access OLE DB connections in DTS packages is
not only server dependent but login dependent, making DTS packages
that use this driver extremely hard to transport from one machine to
another.

I still have the BIG problem on both of my servers where only one
connection is being opened to the AS/400 at one time. In answer to one
posting, my DTS package is setup to have several data transformation
occurring in parallel (the start of each transformation is not
dependent on anything), and this parallelism works fine using non-IBM
AS/400 drivers. Any ideas on how to get IBM's OLE DB provider to open
more than one connection with the AS/400 if several applications (or
processes) are requesting connections at once?

Thanks,

Jeff

Hi Jeff,

It seems that the first problem is due to security issue. Since you an
IBM-provided OLEDB provider, it is best to contact IBM for more
information, as they provide this driver. I will try my best to perform
search on my side if you could provide exact error message before and exact
provider type, so that I can check if have encountered issue with this
provider before.


This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Bill Cheng
Microsoft Support Engineer
--------------------
er.dts
m!news.maxwell.syr.edu!newsfeed.stanford.edu!postn ews1.google.com!not-for-ma
il