Flat table to normalized table in DB2

Question

This is for DB2 9.7

--OLDPHONE TABLE
CREATE TABLE OLDPHONE (
    ID        BIGINT  NOT NULL ,
    PHONE1    VARCHAR(30) ,
    PHONE2    VARCHAR(30) ,
    PHONE3    VARCHAR(30) ,
    PHONE4    VARCHAR(30) ,
    PHONE5    VARCHAR(30));

--NEWPHONE TABLE
CREATE TABLE NEWPHONE (
    ID       BIGINT  NOT NULL ,
    INDEX    SMALLINT  NOT NULL ,
    PHONE  VARCHAR(30)  NOT NULL);

--SAMPLE DATA
INSERT INTO OLDPHONE (ID, PHONE1, PHONE2, PHONE3, PHONE4, PHONE5) VALUES
(1,'78948418',NULL, NULL, NULL, NULL),
(2,'78948418','78948418', NULL, NULL, NULL),
(3,'78948418','78948418', '78948418', NULL, NULL),
(4,'78948418',NULL, NULL, '78948418', NULL),
(5,'78948418',NULL, '78948418', '78948418', NULL);

Currently a customer can have up to 5 phone numbers using the OLDPHONE table method. I want to be able to have the customer have unspecified number of phone numbers stored. So to manipulate the data I created the NEWPHONE table to merge the existing data into it.

I use this merge statement to get results I need and it work perfectly but it's costly. Is there a more efficient way of doing this? This merge is performed on a regular basis to update the NEWPHONE table. So merge is needed.

MERGE INTO NEWPHONE P1 USING (
    SELECT T1.ID, T2.INDEX, T2.PHONE 
        FROM OLDPHONE AS T1,   
            TABLE(VALUES(1, T1.PHONE1),
                        (2, T1.PHONE2),     
                        (3, T1.PHONE3),
                        (4, T1.PHONE4),
                        (5, T1.PHONE5))
            AS T2(INDEX, PHONE)
    WHERE T2.PHONE IS NOT NULL) P2
    ON P1.ID = P2.ID AND P1.INDEX = P2.INDEX AND P1.PHONE = P2.PHONE
WHEN NOT MATCHED THEN
   INSERT (ID, INDEX, PHONE) 
   VALUES (P2.ID, P2.INDEX, P2.PHONE);

Answer 1

Is a merge really necessary, or can you just insert? It isn't clear from your question.

insert into newphone 
    select id, 1, phone1 from oldphone where phone1 is not null
       union all
    select id, 2, phone2 from oldphone where phone2 is not null
       union all
    select id, 3, phone3 from oldphone where phone3 is not null
       union all
    select id, 4, phone4 from oldphone where phone4 is not null
       union all
    select id, 5, phone5 from oldphone where phone5 is not null

Things that can speed this up, if the insert is not performant:

• Disable logging on the destination table using alter table newphone activate not logged initially within the transaction.
• If that doesn't work, use LOAD FROM <cursorname>, which is almost certainly going to be the most efficient.

If you do need to merge, then try disabling logging as suggested above. Also, make sure there is an index on each column used in the on clause of the merge.