Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Daily file processing help

    Hello DBA's:

    I need to extract a particular file from our AS 400 system on a daily basis and do some processing on it. Also I want to do the daily processing only on those records that have been added/updated since the initial load.
    Here is the approach and possible implementation.

    Approach
    I have the DB2 source data containing 10 columns (Col1 to Col5 together form the key) and the rest are attributes. I am interested only in the key and two attributes. So I load my table with only Col1 to Col7 ( 5 for key and the two attributes). I then do my processing on this table.

    Here is the implementation given by a member of dbforums -



    You'll then have to deal with 3 potential actions,

    INSERT: New records on the file.
    DELETES: Records that don't exists.
    UPDATES: Records that are on the file, but attributes have changed.


    You had given me this code template.

    CREATE TABLE myTable99 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

    CREATE TABLE myTable00 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
    GO

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL
    SELECT '1','2','d','e','f' UNION ALL
    SELECT '1','3','g','h','i' UNION ALL
    SELECT '1','4','j','k','l'
    --DELETED

    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
    SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
    SELECT '2','3','a','b','c' --INSERT
    GO

    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO

    --DO DELETES FIRST (My comment - Before deleting, I want to copy the rows that I am going to delete on a separate table to maintain history. Then I want to delete from a). I don't get the logic. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =. why the where clause condition)
    DELETE FROM a
    FROM myTable99 a
    LEFT JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL

    -- INSERT (My comment - I don't get the logic of the where. If the rows of the old extract are not in new extract then delete them. So shouldn't it be <> instead of =)

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
    FROM myTable00 a
    LEFT JOIN myTable99 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL

    -- UPDATE

    UPDATE a
    SET Col3 = b.Col3
    , Col4 = b.Col4
    , Col5 = b.Col5
    FROM myTable99 a
    INNER JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    AND ( a.Col3 <> b.Col3
    OR a.Col4 <> b.Col4
    OR a.Col5 <> b.Col5)
    GO
    ------------

    Can anybody look at My comments and answer them or revise this code template if need be?

    Brett Kaiser - I sent you an e-mail on this. Can you respond to it when time permits.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Vivek,

    Did you test it? Run it I mean?

    I worked with a guy named Vivek once....

    First the DELETE's. You know why you want to delete first, correct?
    If yes, and you want to retain history, create an identical historical table called:

    CREATE TABLE myTable99_H (
    HIST_ADD_DT datetime DEFAULT GetDate()
    , Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)

    With no constraints..it is history after all, and is inheriting the constarints from the base table

    As far as the logic...if you look it's a LEFT OUTER JOIN between the 2 tables WHERE the key DOESN'T exist in the other (NOT NULL)

    Got it?

    Second, INSERTS...

    Same logic as to why (NOT NULL)

    Well did you cut and paste the code in to QA?

    It should run for you no problems and should be a good example...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    176

    Reponse to Brett

    Brett,

    Yes it works. Sorry about that friend. I should have tested it in QA before. Thanks. Just a one more thing - I want to do the daily processing only on those records that have been updated/inserted. My daily processing involves checking if those attribute columns Col6 and Col 7 are present in this other file. If they are present than I need to say myTable99 records is Yes - it is present.

    So I don't want to check on the attributes that have not changed. Would you suggest using a flag/status field for this purpose? Is there any other way?

    Thanks

    Vivek

    1. Instead of deleting the row first, I have to move that row to a history table and then delete it. So is this correct for Delete -

    CREATE TABLE myTable99_H (
    HIST_ADD_DT datetime DEFAULT GetDate()
    , Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)

    INSERT INTO myTable99_H(Col1,Col2,Col3,Col4,Col5)
    SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
    FROM myTable99 a
    LEFT JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL

    Then do the delete

    DELETE FROM a
    FROM myTable99 a
    LEFT JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL





    Originally posted by Brett Kaiser
    Vivek,

    Did you test it? Run it I mean?

    I worked with a guy named Vivek once....

    First the DELETE's. You know why you want to delete first, correct?
    If yes, and you want to retain history, create an identical historical table called:

    CREATE TABLE myTable99_H (
    HIST_ADD_DT datetime DEFAULT GetDate()
    , Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)

    With no constraints..it is history after all, and is inheriting the constarints from the base table

    As far as the logic...if you look it's a LEFT OUTER JOIN between the 2 tables WHERE the key DOESN'T exist in the other (NOT NULL)

    Got it?

    Second, INSERTS...

    Same logic as to why (NOT NULL)

    Well did you cut and paste the code in to QA?

    It should run for you no problems and should be a good example...

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I don't worry about it...it's like picking up sand with tweazers...

    As long as you keep history of deletes AND updates with triggers, you will always have a total view of the "life" of the data as it "grows up" (and potentially dies - DELETE).

    You can then determine with SQL what happened when and where (and if you add the right columns, by whom).

    BUT....

    You van look up the COLUMNS_UPDATED syntax in the Trigger

    I don't use it, so sorry...

    Go to BOL and look up CREATE TRIGGER in the index....

    BUT...I'm perfectly happy with my method of tracking and retaining history...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Sep 2003
    Posts
    176
    Brett,

    I agree that your method is good. I would do the same.

    My daily processing involves checking if those attribute columns Col6 and Col 7 are present in this other file. If they are present than I need to say myTable99 records is Yes - it is present.

    So I don't want to check on the attributes that have not changed. That is the records with NO CHANGE shouldn't undergo any processing. Only those that have been updated/inserted should be processed.

    Would you suggest using a flag/status field for this purpose? Is there any other way?

    Thanks

    Vivek

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, So you're saying, any rows that are identical you want to bypass?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Posts
    176
    Yes. Any rows that are identical should be bypassed. I have already done the processing on these rows. So I don't want to do it again. Only updates/inserts should be processed.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And DELETES....

    But if Col1 and 2 or Key

    and in the update I say col3 <> col3
    Or col4 <> col4

    isn't the extension of the col6 <> col6
    or col7 <> col7

    You're losing me....

    Because that would do what you're asking...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Sep 2003
    Posts
    176
    Brett,

    Let me make it simple...

    CREATE TABLE myTable99 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

    CREATE TABLE myTable00 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
    GO

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
    SELECT '1','2','d','e','f' UNION ALL --UPDATED
    SELECT '1','3','g','h','i' UNION ALL -- --NO CHANGE
    SELECT '1','4','j','k','l'
    --DELETED

    --This was my initial load...I check if Col4 or Col5 or Col6 (Name fields) are present in a Name file containing a list of names. If they are then I output the record plus the Name record from the Name file into a different table. That is my processing. I have a stored proc that does this. So now during the day this file gets updated in the source system. So the next day what I have is -

    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
    SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
    SELECT '2','3','a','b','c' --INSERT
    GO

    Select * FROM myTable00.

    I compare this with myTable99, do the deletes,updates and inserts like you said to get the appropriate contents of Table00 to Table99. Are you with me till this point. So now my Table99 next day looks like -

    1','1','a','b','c' --NO CHANGE (So today I don't have to check this record with the Name file.)
    '1','2','x','y','z' -- UPDATE ( I have to check this record with Name File)
    '1','3','g','h','i' --NO CHANGE (No check. If I check and output with Name record then duplicate row)
    '2','3','a','b','c' --INSERT (Check)

    Have I made it clear now?

    Let me know what you think. Appreciate your help.

    Vivek

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by vivek_vdc

    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL -- UPDATE (My comment - Instead of an update I want to insert a new record)
    SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
    SELECT '2','3','a','b','c' --INSERT
    GO
    Clear as mud...but I've got way too much time invested...

    You can't do that..see the bold...you're pk is col1 and col2...they can'r appear more than once like your sample suggestions...

    again, unless it's too murky in here....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •