CODEWITHSUNDEEP
phpsqldb2-400
Geoff_S
Geoff_S

Reputation: 5107

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

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?

Upvotes: 0

Views: 92

Answers (2)

jmarkmurphy
jmarkmurphy

Reputation: 11473

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)";

Upvotes: 1

nfgl
nfgl

Reputation: 3202

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>

Upvotes: 1

Related Questions