Incremental Load using qvd files Incremental Load Differential

Incremental Load using qvd files

Incremental Load • Differential Load • Delta Load

Incremental Load only the new or changed records from the database. The rest should already be available, one way or another.

Comments on Buffer Load • Buffer (Incremental) Load … is a solution only for Log files, but not for DBs. • Buffer (Stale after 7 days) Select … is not a solution. It needs a full Load after 7 days.

Incremental Load • Load new data from Database table (slow, but few records) • Load old data from QVD file (many records, but fast) • Create new QVD file • Procedure must be repeated for each table

Different DB-changes If source allows … 1) 2) 3) 4) Append only. (Logfiles) Insert only. (No Update or Delete) Insert and Update. (No Delete) Insert, Update and Delete.

1) Append only • Must be Log file • Loads records added in the end of the file

1) Append only Buffer (Incremental) Load * From Log. File. txt (ansi, txt, delimiter is 't', embedded labels);

2) Insert only • Can be any DB • Loads INSERTed records • Needs the field Modification. Date

2) Insert only QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#;

2) Insert only QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD;

2) Insert only QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD; STORE QV_Table INTO File. QVD;

2) Insert only QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)# AND Modification. Time < #$(Beginning. This. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD; STORE QV_Table INTO File. QVD;

3) Insert and Update • Can be any DB • Loads INSERTed and UPDATEd records • Needs the fields Modification. Date and Primary. Key

3) Insert and Update QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD WHERE NOT Exists(Primary. Key); STORE QV_Table INTO File. QVD;

4) Insert, Update and Delete • Can be any DB • Loads INSERTed and UPDATEd records • Removes DELETEd records • Needs the fields Modification. Date and Primary. Key • Tricky to implement

4) Insert, Update and Delete QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD WHERE NOT EXISTS(Primary. Key); Inner Join SQL SELECT Primary. Key FROM DB_TABLE; STORE QV_Table INTO File. QVD;

4) Insert, Update and Delete List. Of. Deleted. Entries: SQL SELECT Primary. Key AS Deleted FROM DB_TABLE WHERE Deletion. Flag = 1; QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD WHERE NOT Exists(Primary. Key) AND NOT Exists(Deleted, Primary. Key); Drop Table List. Of. Deleted. Entries; STORE QV_Table INTO File. QVD;

Last. Execution. Time & Error handling Let This. Exec. Time = Reload. Time(); { Load sequence } If Script. Error. Count = 0 then Let Last. Exec. Time = This. Exec. Time; End If

Final Script Let This. Exec. Time = Reload. Time(); QV_Table: SQL SELECT Primary. Key, X, Y FROM DB_TABLE WHERE Modification. Time >= #$(Last. Exec. Time)# AND Modification. Time < #$(This. Exec. Time)#; Concatenate LOAD Primary. Key, X, Y FROM File. QVD WHERE NOT EXISTS(Primary. Key); Inner Join SQL SELECT Primary. Key FROM DB_TABLE; If Script. Error. Count = 0 then STORE QV_Table INTO File. QVD; Let Last. Exec. Time = This. Exec. Time; End If

Summary 1 • Incremental Load possible for… • • Append only. (Logfiles) Insert only. (No Update or Delete) Insert and Update. (No Delete) Insert, Update and Delete.

Summary 2 • „Incremental Load“ normally not equivalent to Buffer (Incremental) Load …