× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I have a python script that runs on a Windows server in our computer room. The script connects to the i database using ODBC and connects to the MySQL database running on an Ubuntu server using the Python MySQLdb connector.

The script does an SQL SELECT on the i database and then loops over the result set issuing SQL INSERTs on the MySQL database. Here is an overly simplified script to show how it works:

-----------------------------
import pyodbc, MySQLdb

if __name__ == "__main__":

print 'Connecting to the ERP database...'
iConn = pyodbc.connect('dsn=odbcdatasourcename;uid=username;pwd=password')
iC = iConn.cursor()

print 'Connecting to the MySQL database...'
mConn = MySQLdb.connect(host=mysqlservername, user=username, passwd=password, db=databasename)
mC=mConn.cursor()

iSql = 'SELECT pcpcls, pcpcds FROM libraryname.tablename'
print 'Fetching records...'
iC.execute(iSql)

print 'Loading data...'
for pcpcls, pcpcds in iC.fetchall():
pcpcds = pcpcds.strip()
mSql = 'INSERT INTO productClass (productClass, name, tracksTons) VALUES (\'%s\', \'%s\', 0)' % (pcpcls, pcpcds)
mC.execute(mSql)

print 'Closing IBM i connection...'
iC.close()
iConn.close()

print 'Committing and closing MySQL connection...'
mConn.commit()
mC.close()
mConn.close()

print 'Processing complete.'
-----------------------------

Hope this helps. Let me know if you're unclear on how any of this works. Actual production code would obviously have more error checking in it but this is the basic flow.

-Jim

Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jack Kingsley
Sent: Tuesday, October 05, 2010 1:35 PM
To: Midrange Systems Technical Discussion
Subject: Re: Which data warehouse use?

Ok, maybe I am missing something, then how are you getting the data from
python/windows script from the I to ubuntu?? Need clarification, thx.

2010/10/5 jim@xxxxxxx <jim@xxxxxxx>

I am currently running them from a Windows system. ODBC access to an i
from Ubuntu is just way too complex. I know I can do it, but it is
drop-dead simple under Windows so why bother. I see an opportunity for
someone...

-Jim

Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jack Kingsley
Sent: Tuesday, October 05, 2010 12:30 PM
To: Midrange Systems Technical Discussion
Subject: Re: Which data warehouse use?

How are you running the scripts on the ubuntu/python side or on the I side.

2010/10/5 jim@xxxxxxx <jim@xxxxxxx>

Can't say I've tried it with a db of that size, but would be curious to
see
how it performed. Are you talking about just adding 30-40k per day or
are
you also potentially updating the other 20-30 million? 30-40k of inserts
wouldn't be an issue.

I just ran a little test where I'm inserting about 400,000 records. It
took about 15 minutes to transfer the data.

Here are the some other things that I'm doing that would slow the process
down.
- I'm running this on my development workstation, with about a dozen
other
apps running
- I'm connected to the AS/400 and MySQL db over 100meg Ethernet, our
servers are all 1G connections
- I'm dropping all indices before and recreating after the transfer
- At the time of the transfer I'm also flattening out the database,
pulling
in data from a lot of lookup tables

But, that should give you a general idea of the performance. Not great,
but acceptable in my environment.

-Jim

Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Tuesday, October 05, 2010 10:44 AM
To: Midrange Systems Technical Discussion
Subject: RE: Which data warehouse use?

Can this setup handle a largish database? Say, 20 to 30 million records
with 30K to 40K new records a day?

I'd surmise ODBC would be inadequate to handle it.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Steil
Sent: Tuesday, October 05, 2010 10:09 AM
To: Midrange Systems Technical Discussion
Subject: Re: Which data warehouse use?

I write Python scripts to handle it all. Python has a great database
API
that is really easy to implement. I connect to the AS/400 database using
ODBC and to the MySQL db with the Python MySQL driver. The programs are
quite simple. If you haven't used Python before, don't be intimidated by
it. It is really easy to get started with. And, if you are already
familiar with SQL then writing these scripts would be really easy.

I'm happy to help as much as possible to get you going if you're
interested. I really like using Python and enjoy getting others
hooked...

-Jim

On 10/5/2010 9:32 AM, Jack Kingsley wrote:
Jim, what are you using to move the data between the I and your MYSQL
Server.

On Fri, Oct 1, 2010 at 4:17 PM, Jim Steil<jim@xxxxxxx> wrote:

I take the opposite approach. In my mind the data warehousing data
is all reproducible from my production data. Therefore, I don't need
the robustness (and extra cost) the i offers. I take all of my data
to MySQL running on commodity hardware with Ubuntu. Low cost and
really very fast. Users can hit this database with their reporting
tool of choice all they want with no ill effects on the production
systems.

As for data warehousing software, we don't do anything fancy. We
write programs to generate all necessary standard reports to excel or
pdf. Ad hoc is done using MS Access (yuck) by our end users or using
SQL and programming as necessary when it gets too complicated for the
end users.

Just my $.02

-Jim

On 10/1/2010 2:27 PM, Nathan Andelin wrote:
I think that managing disparate systems is the biggest challenge in
IT,
and the
biggest waste of time. If it were up to me, I'd migrate MS SQL
Server
and
PostgreSQL server data and applications to IBM i. And not
surprisingly,
I'd do
data warehousing under IBM i, too. I can't recommend any separate
data warehouse system. Just create SQL views of IBM i tables, and
run queries against them.

-Nathan



----- Original Message ----
From: Tomasz Skorża<t.skorza@xxxxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Fri, October 1, 2010 11:53:49 AM
Subject: Which data warehouse use?

Hi guys

I have one database on DB2-400, one on MS SQL Server and one on
PostrgreSQL.
Which dedicated software for data warehouse you propose to use -
best if
open
source - and which

database use for warehouse?
Other DB2, MS SQL, Postgre or maybe mySQL?

I will be gracefull for any ideas and prompting

Regards

Tomek

--
Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell
--
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.



--
Jim Steil
VP of Information Technology
Quality Liquid Feeds, Inc.
608.935.2345 office
608.341.9896 cell
--
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 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 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.





As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.