Replicate data from production to dev

[paolinosal]

HI all,

I want to replicate some table from our production to dev system, until now we use a clp that do a clrpfm of some table in dev and than a cpyf from a remote ddm to production, obioulsy this is not very efficent, I'm looking for some sql that help me to make the upsert of the record new/different from production table, but with merge this don't let me use to access to a remote database, I think about create view, but it's the same.

Does anyone have some idea?

Many thanks

[inigo.redin]

I think one sollution could be the combination of ALIAS (to access the remote table) and the command MERGE (to insert, update rows in the target system).

[paolinosal]

Hi Inigo,

I try, but when I make the merge the sql always give me the same error:

in Italian:
Messaggio: [SQL0512] L'istruzione fa riferimento ad oggetti in più database.

For the moment I user this solutions:

Create table lib/tablelocal as ( (Select *
From remotedb.remotelib.remotetable
where only what I want)
With Data;

And so it let me use the merge:

Merge Into mylib/mytable loc Using (
Select *
From lib/tablelocal
) As rem
On rem.codcli = loc.codcli

When Matched And loc.updatedate < rem.updatedate Then Update Set (my fields) =
(rem.myfields)

When Not Matched Then Insert (my fields) Values (rrem.myfields);

If someone have a better solution, please let me know.

Many thanks.

Bye

[inigo.redin]

Yes, this is a common message.
But I could understand it if you get the message when you create the table, as it is acessing to 2 databases. I know that CREATE TABLE works.

It's strange it happens when you run the merge.
The merge affects just one database. All data is placed in the same host.
Are you running bot setences in the target database?

[Dennis See]

My idea... just the concept here, but you need to understand the details in order to implement. Journal the file, and remote journal it from Prod to Dev. In Dev, you can extract the data from the journal receiver. For delete records, you can delete the corresponding records. For update and inserts changes, you can delete the equivalent in Dev and then add them back.

[paolinosal]

Yes, this is a common message.
But I could understand it if you get the message when you create the table, as it is acessing to 2 databases. I know that CREATE TABLE works.

It's strange it happens when you run the merge.
The merge affects just one database. All data is placed in the same host.
Are you running bot setences in the target database?

Hi Inigo,

the error happen when I invoke the merge statement, and the two statement (create alias and merge) run on the same system.

[paolinosal]

My idea... just the concept here, but you need to understand the details in order to implement. Journal the file, and remote journal it from Prod to Dev. In Dev, you can extract the data from the journal receiver. For delete records, you can delete the corresponding records. For update and inserts changes, you can delete the equivalent in Dev and then add them back.

Hi Dennis,

this is a bit onerous for this need.

Many thanks

[Dennis See]

It actually won't take a lot of time if the data file updates are simple. It would take less than 10 minutes to do CRTJRNRCV, CRTJRN, STRJRNPF, ADDRMTJRN & CHGRMTJRN, and this is all it takes to start the transmission of data changes to a second server/partition. On the DEV side, SELECT the ENTRY_DATA where the Entry_Types are PT, PX and UP (assuming no deletion). These records, assuming are unique changes per key, just need to be added to the DEV side after deleting them (no need to delete, if using Merge statement). And it is done ! Anyway, just an idea to KIV. I have just done a proof-of-concept test successfully in less than an hour.

[paolinosal]

Hi,

About merge could I simply tells to sql to update entire row without specifying the list of columns?

something like this:

Merge Into mylib/mytable loc
   Using ( Select * From lib/tablelocal ) As rem
   On rem.codcli = loc.codcli
  When Matched Then Update row
  When Not Matched Then Insert  row;

[TedHolt]

Have you considered save and restore?