9 Replies Latest reply: Jun 11, 2012 10:07 AM by hannesse RSS

    Incremental load - scripting problems



      I have som problem writing my script that should be used for incremental load of data. As always when using incremental load I just want to load the posts that is new by looking at the field called CREATE_TIMESTAMP.

      The problem is that i have to tables that i want to perform incremental load on, lets call it table1 and table2, in an Oracle database. It works fine when i am loading only from table1, only the new posts is loaded in to my qvd-file each time. When this is done i just want to load the data in table2 that is connected to the loaded posts in table1 since the other data already should be loaded. Here's the catch, the CREATE_TIMESTAMP is only in table1 and table2 is associated with table1 with an key (table1.ID = table2.ID2). I ONLY want to load the posts from table2 that is associated via the key on the loaded posts in table1.


      Hope you understand my problem.


      Thank you.


        • Incremental load - scripting problems
          Shantanu Sardar

          Hello Hannesse,


          I am not so very clear. If u provide me the script I may understand. And then I may provide you the solution.





            • Incremental load - scripting problems

              shantanu73, heres the script:


              /* Setup for incremental load. */

              SET vQvdFile='File.qvd';

              SET vPKID='ID1';


              // Set a variable indicating if the QVD exists or not. -1 is True, 0 is False

              LET vQvdExists = if(FileSize('$(vQvdFile)') > 0, -1, 0);


              IF $(vQvdExists) THEN                    // QVD exists, we will do an incremental reload


                        // Get the max date from this QVD for use in incremental SELECT

                        LOAD max(CREATE_TIMESTAMP) as maxdate

                        FROM $(vQvdFile) (qvd);

                LET vIncrementalExpression = 'WHERE CREATE_TIMESTAMP >= ' & chr(39) & timestamp(peek('maxdate')) & chr(39);

                        DROP table maxdateTab;


              ELSE                                                            // QVD does not exist

                        LET vIncrementalExpression = '';           // No QVD. Force full reload

              END IF


              ODBC CONNECT TO [database;DBQ=DATABASE ] (XUserId is XXXXX, XPassword is YYYY);








                  ID as "ID1",                                        //Primary Key



              FROM PROD.TABLE1; 


              //Heres the problem, i only want to load the posts that is associated with the newly loaded posts from table1

              //Maybe i need to store old posts from table2 in another qvd-file as well and concatenate the new posts to that?


              SQL SELECT "DATE",



                  ID1 as "Table2_ID"    //NOTE THAT ID2 is the field that should be associated with ID1 in table1

              FROM PROD.TABLE2;




              Update the QVD with changes.




              IF $(vQvdExists) THEN

                        // Use CONCATENATE in case we've added any new fields.

                        CONCATENATE (Table1) LOAD * FROM $(vQvdFile) (qvd)

                        WHERE NOT exists($(vPKID))          // Load only QVD rows that were not already loaded in the data load.


              END IF


              //Overwrite the QVD with the QV datatable. 


              STORE Table1 INTO $(vQvdFile);








                • Incremental load - scripting problems
                  Shantanu Sardar

                  Hello Hannes,


                  Sorry of Late Answer,


                  Check the following Script. If you get some idea.


                  //Start from Here:


                  Let ThisExecTime = ReloadTime();

                  SQL SELECT PrimaryKey, X, Y FROM DB_TABLE
                  WHERE ModificationTime >= #$(LastExecTime)#
                         AND ModificationTime < #$(ThisExecTime)#;

                  Concatenate LOAD PrimaryKey, X, Y FROM File.QVD
                  WHERE NOT EXISTS(PrimaryKey);

                  Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

                  If ScriptErrorCount = 0 then
                  STORE QV_Table INTO File.QVD;
                  Let LastExecTime = ThisExecTime;
                  End If

                  //End from Here:





              • Incremental load - scripting problems

                You Chek this One.



                                              LOAD *;

                                              SQL  SELECT  * FROM Table_Name

                                                        where TO_CHAR(to_DATE($(vModificationTime),'YYYY/MM/DD HH24:MI:SS'),'YYYYMMDDHH24MISS')>='$(T)';


                                              Concatenate ([Table_Name])




                                              LOAD * from Table_Name .qvd (qvd)

                                              WHERE NOT Exists ($(Y));


                                              inner join ([Table_Name])



                                              sql select Pk  FROM Table_Name;



                                    store Table_Name into Table_Name.qvd ;




                                    LET T= (DATE(NOW(),'YYYYMMDD')  & ((hour(NOW()))*10000)  + (MINUTE(NOW())*100)  + SECOND(NOW()));