Online Data Transfer from One DB to Another

[ajaysheth]

Hello Progress Experts:

I need help to transfer data from Source DB to Target DB using data migration programs. The name of the Source DB and Target DB will be accepted at Runtime.

The Functionality of the data migration program will basically be to extract data from Source DB and populate the new created Target DB.

I tried to connect both the DB's in my code. I am able to connect both the DB's. My problem is when I need to extract data from Source, I need to generically prefix the name of the source DB such that when I load the data into the same table in Target DB, it should allow me.

For example:
1. Moving all records from MFG/PRO customer master, "cm_mstr" from source (old version of DB) to target (OpenEdge 10.1A) using progress code.

Psuedo code would be something like;

for each source.cm_mstr where source.cm_mstr.cm_addr begins "S" :
create target.cm_mstr.
assign target.cm_mstr.cm_addr = source.cm_mstr.cm_addr.
<other fields>
end.

Since I am dynamically accepting the source and target DB names, how can I use above logic. Please help. If time is a constraint, kindly give references of examples available in other sites/URL's.

Thanks in Advance.
Ajay.

 

[ChrisJRutter]

Hello Progress Experts:

I need help to transfer data from Source DB to Target DB using data migration programs. The name of the Source DB and Target DB will be accepted at Runtime.

The Functionality of the data migration program will basically be to extract data from Source DB and populate the new created Target DB.

I tried to connect both the DB's in my code. I am able to connect both the DB's. My problem is when I need to extract data from Source, I need to generically prefix the name of the source DB such that when I load the data into the same table in Target DB, it should allow me.

For example:
1. Moving all records from MFG/PRO customer master, "cm_mstr" from source (old version of DB) to target (OpenEdge 10.1A) using progress code.

Psuedo code would be something like;

for each source.cm_mstr where source.cm_mstr.cm_addr begins "S" :
create target.cm_mstr.
assign target.cm_mstr.cm_addr = source.cm_mstr.cm_addr.
<other fields>
end.

Since I am dynamically accepting the source and target DB names, how can I use above logic. Please help. If time is a constraint, kindly give references of examples available in other sites/URL's.

Thanks in Advance.
Ajay.

Easiest option would be to have a compile-at-run-time procedure and pass it the table name.

e.g.

RUN copy_records.p <source> <target>

/* copy_records.p */
FOR EACH {1}.cm_mstr where {1}.cm_mstr.cm_addr begins "S" :
create {2}.cm_mstr.
assign {2}.cm_mstr.cm_addr = {1}.cm_mstr.cm_addr.
<other fields>
end.

You could further extend this to pass the table name and the where clause e.g.

RUN copy_records2.p <source> <target> <table name> <where>

/* copy_records2.p */
FOR EACH {1}.{3} where {1}.{3} BEGINS "{4}" :
create {2}.{3}.
buffer-copy {1}.{3} to {2}.{3}
end.

HTH

 

[joey.jeremiah]

How about a dump & load ?


Theres a dump and load chapter in the Database Administration doc.

Have a look at some of the common and essential optmizations techniques, no crash protection (-i), deactivating and idxbuild etc.

You'll find tons of posts on the subject here and @peg.com


Tom Bascom wrote an excellent paper on parallel dump and loads @greenfieldtech.com.

Also checkut Dan Foremans Pro D&L util @bravepoint.com.

Good luck

 

[TomBascom]

You are, essentially, describing what my highly parallel dump & load talk at Exchange was all about. The (updated for 2007) presentation should be available later this week on my website (see below).

From you description you are having trouble establishing logical database names.

In my case I simply started the source session with -ld src and the target session with -lb dst. I then (psuedo-)coded like so:

define query q for src.{2}.
query q:forward-only = yes.
open query q for each src.{2} no-lock {3}.

outer_loop: do for dst.{2} while true transaction:

  inner_loop: do while true:

    get next q no-lock.
    if not available src.{2} then leave outer_loop.

    create dst.{2}.
    buffer-copy src.{2} to dst.{2} no-error.

    d = d + 1.
    if d modulo 100 = 0 then next outer_loop.

  end.

  leave outer_loop.

end.

{2} = table name, {3} = optional WHERE clause

If you don't want to use the -ld parameter you an also create aliases "src" and "dst".

 

[ajaysheth]

Thanks All for your valuable inputs. I have completed the work using -ld and executing the called programs using the "run" command and passing the logical names of the source and target DB.

I am working on to put such extraction programs in a batch to execute as a one-time script. Wish me good luck ;-)

Thanks,
Ajay.

 

[joey.jeremiah]

How big is the database (in gigabytes) ?

Oh, and good luck !