Sean,
We currently use ProData's RdbConnect product to read/write to a MS Sql Server DB
on our network and we use sql to read/write to iSeries DB2 at the same time. All
of our current code is embedded SQLRPGLE.
To answer your question about using a result set from a query against DB2 as the
source for an insert to the remote DB (MS SQL in your case), there may be a way
to do this with other solutions, but with RdbConnect my current approach is to
create an array DS which is loaded from multi-row Fetch against DB2, then a loop
against the array DS to insert to the remote db.
I have no experience that compares with your .3M to 1M rows, but my guess is you
will need to do serious performance testing.
As Alan suggested, Scott Klement's JDBC api is maybe the easiest way to get started and
I think it will meet your needs for the project you describe.
We looked at ArdGate which is open source available on SourceForge.net - I found a lot to
like: in addition to SQL procedures and embedded SQL, ArdGate supports STRSQL and QM Query,
but ultimately my company went with ProData due to local issues I don't control.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Porterfield, Sean
Sent: Thursday, February 21, 2013 2:08 AM
To: Midrange Systems Technical Discussion
Subject: RE: Microsoft Integration Services password
Stored procedure on the i? I'm having trouble envisioning the process. (Also have to figure out how a stored procedure fits into our change management...)
I can comprehend accessing a database, running a query, processing results. (Select * from branches; read result set; do something for each branch.)
What I'm trying to do is dump a table from one database to another. Maybe 300,000 records today; maybe a million next month.
Can I use a result set from a query against the i database as the source of an insert into MS SQL?
I browsed through Scott's
http://www.scottklement.com/presentations/External%20Databases%20from%20RPG.pdf document which was not confusing, and I do even see an example referencing localhost. I'm not sure if two different databases can be accessed at the same time, though it should be fairly easy to find out tomorrow after I get everything setup (assuming I can steal the time to do it.) If our system weren't down for backup, I'd do it now, since I can't sleep anyway. In the meantime, I'll read through the other docs on the site.
--
Sean Porterfield
________________________________________
From: midrange-l-bounces@xxxxxxxxxxxx [midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin [alan0307d@xxxxxxxxx]
Sent: Wednesday, February 20, 2013 19:41
To: Midrange Systems Technical Discussion
Subject: Re: Microsoft Integration Services password
I would definitely recommend using Scott JDBC api to call stored procedures to extract information or update. It is running on the i and you don't have all the intermittent stuff running with potential to fail.
On Wed, Feb 20, 2013 at 5:17 PM, Porterfield, Sean < SPorterfield@xxxxxxxxxxxxxxxxxxxxxxx> wrote:
I've been using DTS for years on SQL Server 2005 to pull data from our
IBM i (or whatever it was called then ;) )
Now I have a new project on SQL Server 2008, so I used Integration
Services to pull the data. I automate it from a batch job when the
data is ready, calling RUNRMTCMD to DTEXEC on the Windows box.
Whenever the Windows box is rebooted, I have to log on as the user
listed in the RUNRMTCMD, start the data transfer process, and key the
IBM i user password in the normal System i Access password dialog. It
then works fine, even after logging off, until the system is rebooted again.
Does anyone know a way to make it keep the password? I typed it in
the ODBC properties when I created the IS process, but it doesn't store it.
If not, (or regardless) what would others recommend for a data
transfer process? At this time, I don't think I'm actually changing
any of the data, just using TRUNCATE TABLE and pulling in an entire *PF to replace it.
I was thinking it's probably possible and not horribly difficult to do
in PHP, but I've only ever done that for a single record update. JDBC
comes to mind as another option, though I've never installed or used
it. It seems more logical to run the entire process from the i
instead of having to bounce back and forth.
--
Sean Porterfield
This email is confidential, intended only for the named recipient(s)
above and may contain information that is privileged. If you have
received this message in error or are not the named recipient(s),
please notify the sender immediately and delete this email message
from your computer as any and all unauthorized distribution or use of
this message is strictly prohibited. Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
This email is confidential, intended only for the named recipient(s) above and may contain information that is privileged. If you have received this message in error or are not the named recipient(s), please notify the sender immediately and delete this email message from your computer as any and all unauthorized distribution or use of this message is strictly prohibited. Thank you.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
