Thanks ... I took that out and seems like it's working now ...
Seems like they should make this support the same sql ... oh well ..
thanks again ...
Peter Logan
Senior Database Administrator
Phone: 616/878-8309
From: "Gustavo Castro" <gcastro_iiug@bellsouth.net>
To: ids@iiug.org,
Date: 10/07/2013 12:36 AM
Subject: Re: Fw: archecker [31631]
Sent by: ids-bounces@iiug.org
If I remember correctly, DATE is not allowed in the fragmentation
strategy of the table. The SQL parser in archecker is a reduced version
from the one used by IDS.
You actually do not need the expression in the SOURCE table, it is
enough to say that is fragmented by round robin in all dbspaces.
The problem will be if you really need that the TARGET table maintains
the same fragmentation strategy. In that case You can create the table
before running archecker (NO fragmentation in the schema file or use
round robin) and archecker will reuse the existing table. Not sure if
this will work if you want to do logical restore, but as far as I
remember it should work for physical restore.
Gustavo
On 10/4/2013 6:12 PM, Peter_Logan@spartanstores.com wrote:
> Aix 6.1
> IDS 11.70.fc3xc
>
> I'm trying to use archecker to restore a 2 billion row table ... below
is
> the ac_schema file I'm using. The table is fragmented ... this sql is
> from a dbschema ... without the extent info ... I'm getting a syntax
> error at line 26 column 24, which is the first line of the fragment
> expression ... anybody got any clues ... Thanks ...
>
> Peter Logan
> Senior Database Administrator
> Phone: 616/878-8309
> ----- Forwarded by Peter Logan/Corporate/Spartan on 10/04/2013 06:10 PM
> -----
>
> From: plisad0 (spartan02) <plisad0@spartan02.spartanstore.com>
> To: Peter_Logan@spartanstores.com,
> Date: 10/04/2013 06:09 PM
> Subject: archecker
>
> database eisdw01@dss_prd_tcp;
>
> create table "whmgr".rxrecon_dl_scr_arc
> (
>
> sales_date date not null ,
>
> sales_line_id char(10) not null ,
>
> script_nbr integer not null ,
>
> refill_nbr integer not null ,
>
> fill_date date,
>
> reconciled_flg varchar(20),
>
> total_cash_amt decimal(9,2),
>
> total_pos_cash_amt decimal(9,2),
>
> pos_cash_date date,
>
> total_dspnsd_qty integer,
>
> total_charge_amt decimal(9,2),
>
> total_pay_plan_amt decimal(9,2),
>
> ttl_cstmry_price_amt decimal(9,2),
>
> ext_acqsn_cost_amt decimal(9,2),
>
> update_tmsp datetime year to second,
>
> load_batch_id integer,
>
> origin_id char(10),
>
> archive_tmsp datetime year to second
> )
> fragment by expression
>
> ((sales_date >= DATE ('08/15/2010' ) ) AND (sales_date <=
>
> DATE ('09/11/2010' ) ) ) in eis_fragt_01,
>
> ((sales_date >= DATE ('09/12/2010' ) ) AND (sales_date <=
>
> DATE ('10/09/2010' ) ) ) in eis_fragt_02,
>
> ((sales_date >= DATE ('10/10/2010' ) ) AND (sales_date <=
>
> DATE ('11/06/2010' ) ) ) in eis_fragt_03,
>
> ((sales_date >= DATE ('11/07/2010' ) ) AND (sales_date <=
>
> DATE ('12/04/2010' ) ) ) in eis_fragt_04,
>
> ((sales_date >= DATE ('12/05/2010' ) ) AND (sales_date <=
>
> DATE ('01/01/2011' ) ) ) in eis_fragt_05,
>
> ((sales_date >= DATE ('01/02/2011' ) ) AND (sales_date <=
>
> DATE ('01/29/2011' ) ) ) in eis_fragt_06,
>
> ((sales_date >= DATE ('01/30/2011' ) ) AND (sales_date <=
>
> DATE ('02/26/2011' ) ) ) in eis_fragt_07,
>
> ((sales_date >= DATE ('02/27/2011' ) ) AND (sales_date <=
>
> DATE ('03/26/2011' ) ) ) in eis_fragt_08,
>
> ((sales_date >= DATE ('03/27/2011' ) ) AND (sales_date <=
>
> DATE ('04/23/2011' ) ) ) in eis_fragt_09,
>
> ((sales_date >= DATE ('04/24/2011' ) ) AND (sales_date <=
>
> DATE ('05/21/2011' ) ) ) in eis_fragt_10,
>
> ((sales_date >= DATE ('05/22/2011' ) ) AND (sales_date <=
>
> DATE ('06/18/2011' ) ) ) in eis_fragt_11,
>
> ((sales_date >= DATE ('06/19/2011' ) ) AND (sales_date <=
>
> DATE ('07/16/2011' ) ) ) in eis_fragt_12,
>
> ((sales_date >= DATE ('07/17/2011' ) ) AND (sales_date <=
>
> DATE ('08/13/2011' ) ) ) in eis_fragt_13,
>
> ((sales_date >= DATE ('08/14/2011' ) ) AND (sales_date <=
>
> DATE ('09/10/2011' ) ) ) in eis_fragt_14,
>
> ((sales_date >= DATE ('09/11/2011' ) ) AND (sales_date <=
>
> DATE ('10/08/2011' ) ) ) in eis_fragt_15,
>
> ((sales_date >= DATE ('10/09/2011' ) ) AND (sales_date <=
>
> DATE ('11/05/2011' ) ) ) in eis_fragt_16,
>
> ((sales_date >= DATE ('11/06/2011' ) ) AND (sales_date <=
>
> DATE ('12/03/2011' ) ) ) in eis_fragt_17,
>
> ((sales_date >= DATE ('12/04/2011' ) ) AND (sales_date <=
>
> DATE ('12/31/2011' ) ) ) in eis_fragt_18,
>
> ((sales_date >= DATE ('01/01/2012' ) ) AND (sales_date <=
>
> DATE ('01/28/2012' ) ) ) in eis_fragt_19,
>
> ((sales_date >= DATE ('01/29/2012' ) ) AND (sales_date <=
>
> DATE ('02/25/2012' ) ) ) in eis_fragt_20,
>
> ((sales_date >= DATE ('02/26/2012' ) ) AND (sales_date <=
>
> DATE ('03/31/2012' ) ) ) in eis_fragt_21,
>
> ((sales_date >= DATE ('04/01/2012' ) ) AND (sales_date <=
>
> DATE ('04/28/2012' ) ) ) in eis_fragt_22,
>
> ((sales_date >= DATE ('04/29/2012' ) ) AND (sales_date <=
>
> DATE ('05/26/2012' ) ) ) in eis_fragt_23,
>
> ((sales_date >= DATE ('05/27/2012' ) ) AND (sales_date <=
>
> DATE ('06/23/2012' ) ) ) in eis_fragt_24,
>
> ((sales_date >= DATE ('06/24/2012' ) ) AND (sales_date <=
>
> DATE ('07/21/2012' ) ) ) in eis_fragt_25,
>
> ((sales_date >= DATE ('07/22/2012' ) ) AND (sales_date <=
>
> DATE ('08/18/2012' ) ) ) in dss_fragt_26,
>
> ((sales_date >= DATE ('08/19/2012' ) ) AND (sales_date <=
>
> DATE ('09/15/2012' ) ) ) in dss_fragt_27,
>
> ((sales_date >= DATE ('09/16/2012' ) ) AND (sales_date <=
>
> DATE ('10/13/2012' ) ) ) in dss_fragt_28,
>
> ((sales_date >= DATE ('10/14/2012' ) ) AND (sales_date <=
>
> DATE ('11/10/2012' ) ) ) in dss_fragt_29,
>
> ((sales_date >= DATE ('11/11/2012' ) ) AND (sales_date <=
>
> DATE ('12/08/2012' ) ) ) in dss_fragt_30,
>
> ((sales_date >= DATE ('12/09/2012' ) ) AND (sales_date <=
>
> DATE ('01/05/2013' ) ) ) in dss_fragt_31,
>
> ((sales_date >= DATE ('01/06/2013' ) ) AND (sales_date <=
>
> DATE ('02/02/2013' ) ) ) in dss_fragt_32,
>
> ((sales_date >= DATE ('02/03/2013' ) ) AND (sales_date <=
>
> DATE ('03/02/2013' ) ) ) in dss_fragt_33,
>
> ((sales_date >= DATE ('03/03/2013' ) ) AND (sales_date <=
>
> DATE ('03/30/2013' ) ) ) in dss_fragt_34,
>
> ((sales_date >= DATE ('03/31/2013' ) ) AND (sales_date <=
>
> DATE ('04/27/2013' ) ) ) in dss_fragt_35,
>
> ((sales_date >= DATE ('04/28/2013' ) ) AND (sales_date <=
>
> DATE ('05/25/2013' ) ) ) in dss_fragt_36,
>
> ((sales_date >= DATE ('05/26/2013' ) ) AND (sales_date <=
>
> DATE ('06/22/2013' ) ) ) in dss_fragt_37,
>
> ((sales_date >= DATE ('06/23/2013' ) ) AND (sales_date <=
>
> DATE ('07/20/2013' ) ) ) in dss_fragt_38,
>
> ((sales_date >= DATE ('07/21/2013' ) ) AND (sales_date <=
>
> DATE ('08/17/2013' ) ) ) in dss_fragt_39,
>
> ((sales_date >= DATE ('08/18/2013' ) ) AND (sales_date <=
>
> DATE ('09/14/2013' ) ) ) in dss_fragt_40,
>
> ((sales_date >= DATE ('09/15/2013' ) ) AND (sales_date <=
>
> DATE ('10/12/2013' ) ) ) in dss_fragt_41,
>
> ((sales_date >= DATE ('10/13/2013' ) ) AND (sales_date <=
>
> DATE ('11/09/2013' ) ) ) in dss_fragt_42,
>
> ((sales_date >= DATE ('11/10/2013' ) ) AND (sales_date <=
>
> DATE ('12/07/2013' ) ) ) in dss_fragt_43,
>
> ((sales_date >= DATE ('12/08/2013' ) ) AND (sales_date <=
>
> DATE ('01/04/2014' ) ) ) in dss_fragt_44;
>
> create table "whmgr".pdl_rxrecon_dl_scr_arc
> (
>
> sales_date date not null ,
>
> sales_line_id char(10) not null ,
>
> script_nbr integer not null ,
>
> refill_nbr integer not null ,
>
> fill_date date,
>
> reconciled_flg varchar(20),
>
> total_cash_amt decimal(9,2),
>
> total_pos_cash_amt decimal(9,2),
>
> pos_cash_date date,
>
> total_dspnsd_qty integer,
>
> total_charge_amt decimal(9,2),
>
> total_pay_plan_amt decimal(9,2),
>
> ttl_cstmry_price_amt decimal(9,2),
>
> ext_acqsn_cost_amt decimal(9,2),
>
> update_tmsp datetime year to second,
>
> load_batch_id integer,
>
> origin_id char(10),
>
> archive_tmsp datetime year to second
> )
> fragment by expression
>
> ((sales_date >= DATE ('08/15/2010' ) ) AND (sales_date <=
>
> DATE ('09/11/2010' ) ) ) in eis_fragt_01,
>
> ((sales_date >= DATE ('09/12/2010' ) ) AND (sales_date <=
>
> DATE ('10/09/2010' ) ) ) in eis_fragt_02,
>
> ((sales_date >= DATE ('10/10/2010' ) ) AND (sales_date <=
>
> DATE ('11/06/2010' ) ) ) in eis_fragt_03,
>
> ((sales_date >= DATE ('11/07/2010' ) ) AND (sales_date <=
>
> DATE ('12/04/2010' ) ) ) in eis_fragt_04,
>
> ((sales_date >= DATE ('12/05/2010' ) ) AND (sales_date <=
>
> DATE ('01/01/2011' ) ) ) in eis_fragt_05,
>
> ((sales_date >= DATE ('01/02/2011' ) ) AND (sales_date <=
>
> DATE ('01/29/2011' ) ) ) in eis_fragt_06,
>
> ((sales_date >= DATE ('01/30/2011' ) ) AND (sales_date <=
>
> DATE ('02/26/2011' ) ) ) in eis_fragt_07,
>
> ((sales_date >= DATE ('02/27/2011' ) ) AND (sales_date <=
>
> DATE ('03/26/2011' ) ) ) in eis_fragt_08,
>
> ((sales_date >= DATE ('03/27/2011' ) ) AND (sales_date <=
>
> DATE ('04/23/2011' ) ) ) in eis_fragt_09,
>
> ((sales_date >= DATE ('04/24/2011' ) ) AND (sales_date <=
>
> DATE ('05/21/2011' ) ) ) in eis_fragt_10,
>
> ((sales_date >= DATE ('05/22/2011' ) ) AND (sales_date <=
>
> DATE ('06/18/2011' ) ) ) in eis_fragt_11,
>
> ((sales_date >= DATE ('06/19/2011' ) ) AND (sales_date <=
>
> DATE ('07/16/2011' ) ) ) in eis_fragt_12,
>
> ((sales_date >= DATE ('07/17/2011' ) ) AND (sales_date <=
>
> DATE ('08/13/2011' ) ) ) in eis_fragt_13,
>
> ((sales_date >= DATE ('08/14/2011' ) ) AND (sales_date <=
>
> DATE ('09/10/2011' ) ) ) in eis_fragt_14,
>
> ((sales_date >= DATE ('09/11/2011' ) ) AND (sales_date <=
>
> DATE ('10/08/2011' ) ) ) in eis_fragt_15,
>
> ((sales_date >= DATE ('10/09/2011' ) ) AND (sales_date <=
>
> DATE ('11/05/2011' ) ) ) in eis_fragt_16,
>
> ((sales_date >= DATE ('11/06/2011' ) ) AND (sales_date <=
>
> DATE ('12/03/2011' ) ) ) in eis_fragt_17,
>
> ((sales_date >= DATE ('12/04/2011' ) ) AND (sales_date <=
>
> DATE ('12/31/2011' ) ) ) in eis_fragt_18,
>
> ((sales_date >= DATE ('01/01/2012' ) ) AND (sales_date <=
>
> DATE ('01/28/2012' ) ) ) in eis_fragt_19,
>
> ((sales_date >= DATE ('01/29/2012' ) ) AND (sales_date <=
>
> DATE ('02/25/2012' ) ) ) in eis_fragt_20,
>
> ((sales_date >= DATE ('02/26/2012' ) ) AND (sales_date <=
>
> DATE ('03/31/2012' ) ) ) in eis_fragt_21,
>
> ((sales_date >= DATE ('04/01/2012' ) ) AND (sales_date <=
>
> DATE ('04/28/2012' ) ) ) in eis_fragt_22,
>
> ((sales_date >= DATE ('04/29/2012' ) ) AND (sales_date <=
>
> DATE ('05/26/2012' ) ) ) in eis_fragt_23,
>
> ((sales_date >= DATE ('05/27/2012' ) ) AND (sales_date <=
>
> DATE ('06/23/2012' ) ) ) in eis_fragt_24,
>
> ((sales_date >= DATE ('06/24/2012' ) ) AND (sales_date <=
>
> DATE ('07/21/2012' ) ) ) in eis_fragt_25,
>
> ((sales_date >= DATE ('07/22/2012' ) ) AND (sales_date <=
>
> DATE ('08/18/2012' ) ) ) in dss_fragt_26,
>
> ((sales_date >= DATE ('08/19/2012' ) ) AND (sales_date <=
>
> DATE ('09/15/2012' ) ) ) in dss_fragt_27,
>
> ((sales_date >= DATE ('09/16/2012' ) ) AND (sales_date <=
>
> DATE ('10/13/2012' ) ) ) in dss_fragt_28,
>
> ((sales_date >= DATE ('10/14/2012' ) ) AND (sales_date <=
>
> DATE ('11/10/2012' ) ) ) in dss_fragt_29,
>
> ((sales_date >= DATE ('11/11/2012' ) ) AND (sales_date <=
>
> DATE ('12/08/2012' ) ) ) in dss_fragt_30,
>
> ((sales_date >= DATE ('12/09/2012' ) ) AND (sales_date <=
>
> DATE ('01/05/2013' ) ) ) in dss_fragt_31,
>
> ((sales_date >= DATE ('01/06/2013' ) ) AND (sales_date <=
>
> DATE ('02/02/2013' ) ) ) in dss_fragt_32,
>
> ((sales_date >= DATE ('02/03/2013' ) ) AND (sales_date <=
>
> DATE ('03/02/2013' ) ) ) in dss_fragt_33,
>
> ((sales_date >= DATE ('03/03/2013' ) ) AND (sales_date <=
>
> DATE ('03/30/2013' ) ) ) in dss_fragt_34,
>
> ((sales_date >= DATE ('03/31/2013' ) ) AND (sales_date <=
>
> DATE ('04/27/2013' ) ) ) in dss_fragt_35,
>
> ((sales_date >= DATE ('04/28/2013' ) ) AND (sales_date <=
>
> DATE ('05/25/2013' ) ) ) in dss_fragt_36,
>
> ((sales_date >= DATE ('05/26/2013' ) ) AND (sales_date <=
>
> DATE ('06/22/2013' ) ) ) in dss_fragt_37,
>
> ((sales_date >= DATE ('06/23/2013' ) ) AND (sales_date <=
>
> DATE ('07/20/2013' ) ) ) in dss_fragt_38,
>
> ((sales_date >= DATE ('07/21/2013' ) ) AND (sales_date <=
>
> DATE ('08/17/2013' ) ) ) in dss_fragt_39,
>
> ((sales_date >= DATE ('08/18/2013' ) ) AND (sales_date <=
>
> DATE ('09/14/2013' ) ) ) in dss_fragt_40,
>
> ((sales_date >= DATE ('09/15/2013' ) ) AND (sales_date <=
>
> DATE ('10/12/2013' ) ) ) in dss_fragt_41,
>
> ((sales_date >= DATE ('10/13/2013' ) ) AND (sales_date <=
>
> DATE ('11/09/2013' ) ) ) in dss_fragt_42,
>
> ((sales_date >= DATE ('11/10/2013' ) ) AND (sales_date <=
>
> DATE ('12/07/2013' ) ) ) in dss_fragt_43,
>
> ((sales_date >= DATE ('12/08/2013' ) ) AND (sales_date <=
>
> DATE ('01/04/2014' ) ) ) in dss_fragt_44;
> insert into pdl_rsal_dl_scr_arc
> select * from rxrecon_dl_scr_arc;
>
> restore to '2013-10-04 12:00:00';
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.