Internal inconsistency, Type D, detected. when storing to qvd.

[#1]

I'm running out of virtual memory on the following load for about 26,000,000 records.  Is there a way to restructure this load so that it requires fewer resources?  (The idea is to pull a running 13 months of data up to the last completed month.)

Inspn:

  LOAD [%Ins Id],

  [%Veh Id],

  [%Inr Id],

  [%Sit Id],

  [%Loc Id],

  date(DayStart([Ins Date])) as [Ins Date],

  [Ins Railcar],

  [Ins Position],

  [Ins Damage Count];

  SQL SELECT dat_inspection.ins_id             AS "%Ins Id"

        , dat_inspection.veh_id           AS "%Veh Id"

        , dat_inspection.inr_id           AS "%Inr Id"

       , dat_inspection.sit_id           AS "%Sit Id"

        , dat_inspection.loc_id           AS "%Loc Id"

        , trunc(dat_inspection.ins_date)         AS "Ins Date"

        , dat_inspection.ins_railcar      AS "Ins Railcar"

        , dat_inspection.ins_position     AS "Ins Position"

        , dat_inspection.ins_damage_count AS "Ins Damage Count"

        FROM   avis_app.dat_inspection

  where  dat_inspection.ins_date <

                                      to_date(to_char(sysdate,'YYYYMM')||'01','YYYYMMDD')

                                      and dat_inspection.ins_date >=

                                      to_date(to_char(sysdate,'MM')||'/01/'||to_char(sysdate-365,'YYYY'), 'MM/DD/YYYY');

  STORE Inspn into CurrYearInspn.qvd (qvd);

[Anonymous]

Brian

A random suggestion maybe, but how about extracting the data one month at a time and saving into monthly QVD's ?

Best Regards,     Bill

[Clever_Anjos · Accepted solution]

As Bill suggested, try to split your qvd´s

Let MonthsToProcess=36

For i = 0 to $(MonthsToProcess) -1

    Let p1 =date(monthstart(addmonths(today(),$(i))),'YYYY-MM-DD hh:mm:ss');

    Let p2 =date(monthend(addmonths(today(),$(i))),'YYYY-MM-DD hh:mm:ss');

Inspn:

  LOAD [%Ins Id],

  [%Veh Id],

  [%Inr Id],

  [%Sit Id],

  [%Loc Id],

  date(DayStart([Ins Date])) as [Ins Date],

  [Ins Railcar],

  [Ins Position],

  [Ins Damage Count];

  SQL SELECT dat_inspection.ins_id             AS "%Ins Id"

        , dat_inspection.veh_id           AS "%Veh Id"

        , dat_inspection.inr_id           AS "%Inr Id"

       , dat_inspection.sit_id           AS "%Sit Id"

        , dat_inspection.loc_id           AS "%Loc Id"

        , trunc(dat_inspection.ins_date)         AS "Ins Date"

        , dat_inspection.ins_railcar      AS "Ins Railcar"

        , dat_inspection.ins_position     AS "Ins Position"

        , dat_inspection.ins_damage_count AS "Ins Damage Count"

        FROM   avis_app.dat_inspection

  where  dat_inspection.ins_date <

                                      to_date(to_char($(p2),'YYYYMM')||'01','YYYYMMDD')

                                      and dat_inspection.ins_date >=

                                      to_date(to_char($(p1),'MM')||'/01/'||to_char($(p1),'YYYY'), 'MM/DD/YYYY');

     let QvdName='QVD'&date(addmonths(today(),$(i)),'YYYYMM');

     Store Inspn into $(QvdName)(qvd);

     drop table Inspn;

next

[#4]

Thank you, Bill Markhan and Clever Anjos.  That makes a lot of sense and gives me a great platform for additional flexibility in pulling data at different intervals depending on how old it is.  Thank you!