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





            ?>

Do try and trim out endless expanses of blank lines from your code. The more compact it is, the easier it is to understand what's going on. — tadman
– tadman, Commented Feb 19, 2018 at 20:27
Right, sorry.......That was a huge oversight, I should have cleaned it up a bit more — Geoff_S
– Geoff_S, Commented Feb 19, 2018 at 20:28
Not a huge deal, but keeping things tidy means you're making an effort to keep things as small and focused as possible. — tadman
– tadman, Commented Feb 19, 2018 at 20:31

Parfait · Accepted Answer · 2018-02-20 15:11:41Z

2

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

edited Feb 20, 2018 at 15:11

answered Feb 19, 2018 at 22:58

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

10 Comments

Geoff_S Over a year ago

Wow this looks like a great answer but I am on mobile so I will have to try to apply this In a few hours. As a proof of concept for my team, would I also be able to do something similar with an array as a substitute for the csv? I prefer this method, just trying to think of methods to apply

Parfait Over a year ago

While arrays are hash tables for key/value pairing, they are not set-based, row/column tables like in databases. So you will not take advantage of index joins. The CSV is simply a medium to build a temp table to connect tables together in one database, avoiding nested for looping through all rows and checking if they are equal with other array items. Blockwise handling is usually more maintainable/robust/efficient.

Geoff_S Over a year ago

Thanks again for your help. I'm making this as a script to run regularly as a way to get new data from the old database to a new one, would it be most wise to run the first portion as a script on a schedule and make sure the 2nd portion runs after?

Geoff_S Over a year ago

Also the line while(odbc_fetch_row($result)){ is giving me error that it expects parameter 1 to be a resource

Parfait Over a year ago

Yes, all can be run regularly in sequence. I updated to show how to run queries in PHP. As for the while(...), I forgot to copy over the $result = ... line. See edit. Also, check the type of your shipdate as it is not in true date format: YYYY-MM-DD. Is it an integer or string? If so, you need to convert to actual datetime for DateAdd() function.

|

Collectives™ on Stack Overflow

Using DB2 query to join into mysql data for insert

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related