QVC Components incremental load not working

[sifatnabil]

Hi all,

I'm querying an Oracle DB where the UpdateColumn is a field called MSG_DATE_TIME which has format TIMESTAMP(6) WITH LOCAL TIME ZONE. I've used the below parameters from the QVC components template:

// The directory for the QVD file

SET Qvc.Loader.v.QvdDirectory='QVDs';

// How far back to load if First Load

LET Qvc.Loader.v.BaseValue = num(MakeDate(2016,05,23));

// Datetime literal format for Excel - Shipper exercise

SET Qvc.Loader.v.DatabaseDatetimeMask = 'DD-MMM-YY hh:mm:ss[.fffffffff]';

// Will the max Modfield value be stored with the QVD?

// The recommended value is True. This will greatly speed up subsequent loads.

SET Qvc.Loader.v.StoreMaxModFieldValue = -1;

CALL Qvc.Log('>>>  Incremental Load with inserts & updates');

// Calling parameters are Tablename, UpdateColumn, QVD_PrimaryKey.

CALL Qvc.IncrementalSetup ('Orders', 'MSG_DATE_TIME', 'O_MSG_CRC'); 

The initial load works but the incremental load loads 0 rows every time. Any ideas why this would happen? The MSG_DATE_TIME field in SQL Developer looks like this: 23-MAY-16 13.25.42.672526000

[swuehl]

Have you tried changing the mask to

SET Qvc.Loader.v.DatabaseDatetimeMask = 'DD-MMM-YY hh.mm.ss[.fffffffff]';

(BTW, I think only up to three fff are relavant for the format code indicating milliseconds, might this be a problem?)

[sifatnabil]

Hi,

I've changed to 3 fffs and also hh:mm:ss to hh.mm.ss, but still showing 0 rows updated even though there are new rows after the max timestamp value. Here are the logs:

00001 23/05/2016 13:45:29; >>> Incremental Load with inserts & updates

00002 23/05/2016 13:45:29; QVDs\Orders.qvd exists, rows=13,846

00003 23/05/2016 13:45:30; Loading rows where MSG_DATE_TIME >='23-May-16 13.43.38'

00004 23/05/2016 13:45:32; Orders loaded, rows=0

00005 23/05/2016 13:45:32; QVDs\Orders.qvd updated, rows=13,846

[swuehl]

Maybe rwunderlich‌, the creator and maintainer of the lib, can easily spot a potential cause for this.

[sifatnabil · Accepted solution]

The issue was more on my side with the Oracle DB. When querying I need to alter session to America/New_York (due to a bug in a join - DBAs working to fix it). However in the output, timestamps are returned in GMT/BST. Hence the max updatevalue appeared in GMT/BST. On incremental load, it used the GMT/BST timestamp on the America/New_York session, and since US is 5 hours behind, no data was returned. The way I worked around this is to alter session to Europe/London if QVD exists, else America/New_York if initial load:

if QvdCreateTime('QVDs\Orders.qvd') then

sql ALTER SESSION SET TIME_ZONE = 'Europe/London';

else

sql ALTER SESSION SET TIME_ZONE = 'America/New_York';

end if;

The other issue is that the query using where and 'DD-MMM-YY hh.mm.ss[.fffffffff]' is very slow. I need to use this timestamp format for quick speed: to_timestamp('$(startdate)' || ' 00:00:00', 'dd/mm/yyyy hh24:mi:ss')

To get the format changed I changed the following in the QVC script:

// Specify the Database product name. Possible values are SQLSERVER | ORACLE | SFDC.

SET Qvc.Loader.v.Database=ORACLE;

SET _Qvc.Loader.v.Predicate.ORACLE.Datetime = '>= TIMESTAMP ' & chr(39) & timestamp($1, 'YYYY-MM-DD hh:mm:ss') & chr(39);

to

SET _Qvc.Loader.v.Predicate.ORACLE.Datetime = '>= to_timestamp(' & chr(39) & '$1' & chr(39) & ' || ' & chr(39) & ' 16:00:00' & chr(39) & ',' & chr(39) & 'dd/mm/yyyy hh24:mi:ss' & chr(39) & ')';

LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & $(_tempLoaderPredicate($(_vDate.Max)));

to

LET Qvc.Loader.v.IncrementalExpression = '$(_vSqlModField) ' & '>= to_timestamp(' & chr(39) & text(date($(_vDate.Max),'DD/MM/YYYY')) & chr(39) & ' || ' & chr(39) & ' ' & text(timestamp($(_vDate.Max),'hh:mm:ss')) & chr(39) & ',' & chr(39) & 'dd/mm/yyyy hh24:mi:ss' & chr(39) & ')';

New log file:

00001 23/05/2016 16:27:10; >>> Incremental Load with inserts & updates

00002 23/05/2016 16:27:10; QVDs\Orders.qvd exists, rows=11,938

00003 23/05/2016 16:27:11; Loading rows where O_MSG_DATE_TIME >= to_timestamp('23/05/2016' || ' 16:26:53','dd/mm/yyyy hh24:mi:ss')

00004 23/05/2016 16:27:13; Orders loaded, rows=10

00005 23/05/2016 16:27:13; QVDs\Orders.qvd updated, rows=11,945

[swuehl]

Great, glad that you've worked it out and shared your results.

If your request is resolved, then please close this thread (either by flagging your own answer as correct (should be ok in this specific case) or setting the completet thread to 'Assumed Answered').