Using DB2 query to join into mysql data for insert

Question

I'm trying to get a script together in php that takes a query from DB2, using sql, and then uses that result set by equating it to data in a mysql table in anther connection and inserts results based on that into another.

Basically, My db2 query returns several fields that are needed to determine the ID of a table in My SQL

Below I have an example:

I'm selecting from here: DB2 Query Results

Dealer | Style | Frame | Cover | Color | Placements | shipdate
---------------------------------------------------------------
123        1      1234    12       1         2         20180219
123        2      1235    12       1         2         20180219
123        3      1236    12       1         2         20180219

I need to join the former data, in a sense, to the following data in the SKU Table (db2.style = sku.groupID, db2.frame = sku.frame, db2.cover = sku.cover, and db2.color = sku.color) in order to get the correct ID

ID | Frame | GroupID | cover | color 
------------------------------------
15    1234      1        12      1
16    1235      2        12      1
17    1236      3        12      1

Then below, I need to do an insert of the previously determined ID, as well as some of the data from the original DB2 query (insert style into groupID, dealer into dealerID, shipdate into startdate, placements into placements)

INSERT would result in: (skuplacement table)

sku_id | groupID | dealerID | startDate | expirationDate          | placements
------------------------------------------------------------------------------
15          1       123        20180226    (shipdate + 127 days)       2
16          2       123        20180226    (shipdate + 127 days)       2
17          3       123        20180226    (shipdate + 127 days)       2

I hope this makes sense.

I've included my full script as of right now, but My INSERT/SELECT/JOIN for MYSQL is missing. I'm not sure how to go about this right now, but I have both connections working. It's just a matter of creating the correct query.

I'm happy to answer any questions to clear up confusion.

            <?php


            //Establilsh MySql Connection
            $mysqlConn = new mysqli($mysqlServer, $mysqlUser, $mysqlPass);

            //Check MySQL connection
            if($mysqlConn->connect_error){
              die("Connection Failed: " .$mysqlConn->connect_error);
            }
            echo "Connected Succssfully to Mysql";

            //Establish DB2 connection
            $DB2Conn = odbc_connect();

            //Check DB2 Connection
            if(!$DB2Conn){
              die("Could not connect");
            }else{
                echo"Connected to DB2";
            }



            $plcQueryDB2 = "

                   select
                        xcstno as dealer,
                        p.style as Style,
                        trim(p.framfmt) as Frame,
                        p.cover1 as Cover,
                        p.color1 as Color,
                        sum(skunoc) as placements,
                        p.extd1d as shipdate
                    from pieces p
                    where left(shipdate, 4) >= '2016'
                    group by xcstno, xslsno, sup, f.style, f.grpnam, f.framfmt, f.cover1, f.color1, f.coldss,a.extd1d
                    order by left(shipdate, 4) ASC, REP
                ";

            $prep = odbc_prepare($DB2Conn, $plcQueryDB2);
            $exec = odbc_execute($prep);

            $result = odbc_exec($DB2Conn, $plcQueryDB2);

            if(!$prep){
                die("Could Not Run Query");
            }else{
                echo "DB2 Query Successful";
            }


            while(odbc_fetch_row($result)){
                     for($i=1;$i<=odbc_num_fields($result);$i++){
                    echo "Result is ".odbc_result($result,$i);
                }
            }


            /*Need to get an INSERT created here*/

            $InsertSKUS = "

                    INSERT INTO skuPlacement
                    values (?,?,?,?,?,?)     
            ";

            /* This is my problem. I need to select from another table called skus and insert into skuPlacement based on equating fields from my previous DB2 query and the skus table*/


            ////*CLOSE CONNECTIONS*////


            if (mysqli_close($mysqlConn)){
                echo "MySQL Closed";
            }

            //$CloseDB2 =  odbc_close( $DB2Conn);

            if(odbc_close( $DB2Conn)){
                echo "DB2 Closed";
            }





            ?>

Answer 1

Consider saving your DB2 query results to csv file. Then import the csv file into MySQL with LOAD DATA INFILE (a fast bulk facility) and run needed join append query all inside MySQL:

DB2 Query CSV

try {
    $DB2Conn = odbc_connect();

    $plcQueryDB2 = "...";
    $prep = odbc_prepare($DB2Conn, $plcQueryDB2);
    $exec = odbc_execute($prep);
    $result = odbc_exec($DB2Conn, $plcQueryDB2);
}
catch(Exception $e) {  
    echo $e->getMessage();  
} 

// Writing column headers
$columns = array('Dealer', 'Style', 'Frame', 'Cover', 'Color', 'Placements', 'shipdate');
$fs = fopen('DB2_Query.csv', 'w');
fputcsv($fs, $columns);      
fclose($fs);

// Writing data rows
while($arr = odbc_fetch_array($result)) {
    $fs = fopen('DB2_Query.csv', 'a');
      fputcsv($fs, $arr);
    fclose($fs);
}

odbc_close($DB2Conn);
$DB2Conn = null;

MySQL Queries (run separately in PHP or console)

$mysqlConn->query("CREATE TABLE IF NOT EXISTS db2Temp (
                       dealer INTEGER,
                       style INTEGER,
                       frame INTEGER,
                       cover INTEGER,
                       color INTEGER,
                       placements INTEGER,
                       shipdate DATE
                  )");

$mysqlConn->query("DELETE FROM db2Temp");

$mysqlConn->query("LOAD DATA LOCAL INFILE /path/to/DB2Query.csv
                   INTO TABLE db2temp
                   FIELDS TERMINATED BY ','
                   OPTIONALLY ENCLOSED BY '\"'
                   LINES TERMINATED BY '\n'
                   IGNORE 1 LINES");

$mysqlConn->query("INSERT INTO skuPlacement (sku_id, groupID, dealerID, startDate,
                                             expirationDate, placements)
                   SELECT sku.ID, sku.Group_ID, db2.dealer, db2.shipDate,
                          DATE_ADD(
                               DATE_FORMAT(CONVERT(db2.shipDate, CHAR(8)), '%Y-%m-%d'), 
                               INTERVAL 127 DAY) as expirationDate,
                          db2.placements
                   FROM sku
                   INNER JOIN db2temp db2
                   ON db2.style = sku.groupID
                   AND db2.frame = sku.frame
                   AND db2.cover = sku.cover
                   AND db2.color = sku.color
                   WHERE NOT EXISTS                  
                      (SELECT 1 FROM skuPlacement p
                       WHERE sku.ID = p.sku_ID)");