PHP, script for moving db2 data from one database to another

Question

I need to port some data from tables on a development database into identical tables on the production database, but the production already has records with primary keys that match the dev database so I can't dump the data in with primary keysbundleRenderer.renderToStream

In this case, item_id is the primary key in the parent record, which is used to relate child records to it. Doing the insert of parent records will create a new primary key, so I need child inserts to also have the newly created primary key so that the relationship is maintained on the production databasebundleRenderer.renderToStream

my script so far:

<?php
$DB2connPROD = odbc_connect("schema","user", "pass");
$DB2connDEV = odbc_connect("schema","user", "pass");


//Get itemt records from dev

$getDevitems = "
    select item_id,item_typet_id,item_identifier,expiration_timestamp 
    from development.itemt where item_typet_id in (2,3)
";

//$getDevitems will get records that have a primary key item_id which is used to get the records in the following select queries

foreach($getDevitems as $items){

    //Get all comments

    $getComments = "
    select tc.item_id, tc.comment, tc.comment_type_id from development.item_commentt tc
    inner join development.itemt t on tc.item_id = t.item_id
    where t.item_id = {item_id_from_getDevitems}
    ";

    $insertitem = "INSERT into production (item_identifier,expiration_timestamp)
    values (item_identifier,expiration_timestamp)";

    $insertComment = "INSERT into productionComment (item_id, comment, comment_type_id)
    values (item_id, comment, comment_type_id)";

}

?>

So if $getDevitems returns

item_id  |  item_typet_id  |  item_identifier  |  expiration_timestamp
----------------------------------------------------------------------------
123             1                   544                 '2020-03-01 12:00:00'

I would want it to now run the comment select with 123 as the ID in the where clause:

select tc.item_id, tc.comment, tc.comment_type_id from development.item_commentt tc
inner join development.itemt t on tc.item_id = t.item_id
where t.item_id = 123

Now for my legacy parent record I have all of the parent data and all of the relational child data. so I want to insert the new parent record into the database, creating the new ID, and inserting the child record with the newly created primary key/ID. So for the new parent record I would do:

$insertitem = "INSERT into production (item_identifier,expiration_timestamp)
values (544,'2020-03-01 12:00:00')";

Let's say that creates the new record with item_id = 43409. I want my comment insert to be:

$insertComment = "INSERT into productionComment (item_id, comment, comment_type_id)
values (43409, comment, comment_type_id)";

Bottom LIne: I need to take relational data (all based on item_id) from a development database, and insert these into a new database which creates a new primary key but I need to keep the relationship.

How can I properly finish this to do what I need and make sure I maintain the full relationship for each originally selected item?

Is it from AS-400 db2 to MySQL / PostgreSQL / etc? Great project. But the question is vague - Please improve the question so that we shall extend help. — Natarajan N Napoleon
– Natarajan N Napoleon, Commented Feb 11, 2020 at 15:11
It is from development db2 for as 400 into production db2 for as400, so the source and target are identical in system and structure. Other than that, what would be best to improve? I don't have a lot of specifics, just the general need to keep the relationship between records — Geoff_S
– Geoff_S, Commented Feb 11, 2020 at 15:26

jmarkmurphy · Accepted Answer · 2020-02-12 13:46:28Z

1

Given that your inserts are:

$insertitem = "INSERT into production (item_identifier,expiration_timestamp)
values (item_identifier,expiration_timestamp)";

$insertComment = "INSERT into productionComment (item_id, comment, comment_type_id)
values (item_id, comment, comment_type_id)";

It looks like you are using an identity column for item_id. You can retrieve the most recent generated identity value using the IDENTITY_VAL_LOCAL() function so the second insert should be:

$insertComment = "INSERT into productionComment (item_id, comment, comment_type_id)
values (IDENTITY_VAL_LOCAL(), comment, comment_type_id)";

answered Feb 12, 2020 at 13:46

jmarkmurphy

11.6k37 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nfgl · Accepted Answer · 2020-02-11 15:03:46Z

1

I cannot help with PHP but with DB2 for IBMi you have different solutions :

If i understand it correctly item_id is a GENERATED ALWAYS as IDENTITY column

You can get newly created item_id using

select item_id from final table (
  INSERT into production (item_identifier,expiration_timestamp)
  values (544,'2020-03-01 12:00:00')
)

Or you can force value of item_id with dev value or your own increment

  INSERT into production (idtem_id, item_identifier,expiration_timestamp)
  values (<your value>, 544,'2020-03-01 12:00:00')
  OVERRIDING SYSTEM VALUE

In this case you will have to set next value for item_id by issueing

alter table production alter column item_id restart with <restart value>

answered Feb 11, 2020 at 15:03

nfgl

3,2109 silver badges19 bronze badges

Collectives™ on Stack Overflow

PHP, script for moving db2 data from one database to another

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related