archecker Table Level Restore Error
A user on IDS 12.10.FC5W1 rebuilding after a catastrophic failure used archecker table-level restore, but the schema command file failed with "syntax error ... near token month" because archecker's parser wouldn't accept a FRAGMENT BY EXPRESSION clause using MONTH(). Art Kagel suggested restoring into plain/separate tables and then using ALTER FRAGMENT (INIT) to move the data into the desired fragmentation, which works and drops the original copy. John Miller gave the simpler fix: pre-create the target fragmented table before running archecker, so the command file needs only the column list and archecker appends the data; he also recommended switching to interval (RANGE) fragmentation for speed. The poster confirmed both approaches worked.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I am on IBM Informix Dynamic Server Version 12.10.FC5W1.
After a catastrophic failure and being unable to restore from my latest
backup, I have re-installed Informix and re-created all dbspaces, tables,
indexes, etc. Now I am doing Table Level Restores on critical data using
archecker. So far so good. Until I try to restore a partitioned table. This
particular table has 100 Million + records so definitely need to be
partitioned.
I get the following error and archecker will not run. I've tried rewriting
this several different ways that mean the same thing and archecker will not
recognize MONTH. Is there a way to partition/fragment like this in archecker
TLR?
STATUS: Temporary workspace has been set
ERROR: syntax error at line 376 column 10, near token "month"
ERROR: Error parsing "wmcfg_schema.txt"
CRITICAL ERROR: Unable to initialize extraction
STATUS: archecker completed Physical Restore pid = 4878 exit code: 3
Below is my archecker schema file:
set workspace to archive_dbs;
database ibmposdb;
create table "informix".wmcfg
(
store_number integer not null ,
report_date date not null ,
. . . .
) ;
create table "informix".wmcfg_new
(
store_number integer not null ,
report_date date not null ,
. . . .
)
fragment by expression
(MONTH (report_date ) = 1 ) in wmcfg_tbl_dbs1,
(MONTH (report_date ) = 2 ) in wmcfg_tbl_dbs2,
(MONTH (report_date ) = 3 ) in wmcfg_tbl_dbs3,
(MONTH (report_date ) = 4 ) in wmcfg_tbl_dbs4,
(MONTH (report_date ) = 5 ) in wmcfg_tbl_dbs5,
(MONTH (report_date ) = 6 ) in wmcfg_tbl_dbs6,
(MONTH (report_date ) = 7 ) in wmcfg_tbl_dbs7,
(MONTH (report_date ) = 8 ) in wmcfg_tbl_dbs8,
(MONTH (report_date ) = 9 ) in wmcfg_tbl_dbs9,
(MONTH (report_date ) = 10 ) in wmcfg_tbl_dbs10,
(MONTH (report_date ) = 11 ) in wmcfg_tbl_dbs11,
(MONTH (report_date ) = 12 ) in wmcfg_tbl_dbs12
extent size 350000 next size 34992 lock mode page;
insert into wmcfg_new
select * from wmcfg
where report_date >= '02/01/2016';
restore to current with no log;
Don't know why that's not working. However, in order to get the job done,
try this:
Instead of fragmenting the output table, use WHERE filters to load data for
each month to a separate table placed where you want the final fragment to
reside. Once the data is all restored, and before creating indexes, use
ALTER FRAGMENT to merge all the tables together into a single tableaccording to the correct fragmentation scheme.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, May 9, 2017 at 4:36 PM, LARA KIZZIA <lancaste21@gmail.com> wrote:
> Hi,
> I am on IBM Informix Dynamic Server Version 12.10.FC5W1.
>
> After a catastrophic failure and being unable to restore from my latest
> backup, I have re-installed Informix and re-created all dbspaces, tables,
> indexes, etc. Now I am doing Table Level Restores on critical data using
> archecker. So far so good. Until I try to restore a partitioned table. This
> particular table has 100 Million + records so definitely need to be
> partitioned.
>
> I get the following error and archecker will not run. I've tried rewriting
> this several different ways that mean the same thing and archecker will not
> recognize MONTH. Is there a way to partition/fragment like this in
> archecker
> TLR?
>
> STATUS: Temporary workspace has been set
> ERROR: syntax error at line 376 column 10, near token "month"
> ERROR: Error parsing "wmcfg_schema.txt"
> CRITICAL ERROR: Unable to initialize extraction
> STATUS: archecker completed Physical Restore pid = 4878 exit code: 3
>
> Below is my archecker schema file:
>
> set workspace to archive_dbs;
> database ibmposdb;>
> create table "informix".wmcfg
> (
>
> store_number integer not null ,
>
> report_date date not null ,
> .. . . .
> ) ;
>
> create table "informix".wmcfg_new
> (
>
> store_number integer not null ,
>
> report_date date not null ,
> .. . . .
> )
> fragment by expression
>
> (MONTH (report_date ) = 1 ) in wmcfg_tbl_dbs1,
>
> (MONTH (report_date ) = 2 ) in wmcfg_tbl_dbs2,
>
> (MONTH (report_date ) = 3 ) in wmcfg_tbl_dbs3,
>
> (MONTH (report_date ) = 4 ) in wmcfg_tbl_dbs4,
>
> (MONTH (report_date ) = 5 ) in wmcfg_tbl_dbs5,
>
> (MONTH (report_date ) = 6 ) in wmcfg_tbl_dbs6,
>
> (MONTH (report_date ) = 7 ) in wmcfg_tbl_dbs7,
>
> (MONTH (report_date ) = 8 ) in wmcfg_tbl_dbs8,
>
> (MONTH (report_date ) = 9 ) in wmcfg_tbl_dbs9,
>
> (MONTH (report_date ) = 10 ) in wmcfg_tbl_dbs10,
>
> (MONTH (report_date ) = 11 ) in wmcfg_tbl_dbs11,
>
> (MONTH (report_date ) = 12 ) in wmcfg_tbl_dbs12
> extent size 350000 next size 34992 lock mode page;
>
> insert into wmcfg_new
> select * from wmcfg
> where report_date >= '02/01/2016';>
> restore to current with no log;
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c1be850790ca0054f1ea4fd
Art,
Thank you so much for responding. For my first table, I went ahead and created
a very large "archive" dbspace and restored the entire table there. If I use
ALTER FRAGMENT INIT, will that move everything where I want it or will therestill be lingering sys data in my archive space?
ALTER FRAGMENT ON TABLE wmcfgx_new INIT
FRAGMENT BY EXPRESSION
(MONTH (report_date ) = 1 ) in wmcfg_tbl_dbs1,
(MONTH (report_date ) = 2 ) in wmcfg_tbl_dbs2,
(MONTH (report_date ) = 3 ) in wmcfg_tbl_dbs3,
(MONTH (report_date ) = 4 ) in wmcfg_tbl_dbs4,
(MONTH (report_date ) = 5 ) in wmcfg_tbl_dbs5,
(MONTH (report_date ) = 6 ) in wmcfg_tbl_dbs6,
(MONTH (report_date ) = 7 ) in wmcfg_tbl_dbs7,
(MONTH (report_date ) = 8 ) in wmcfg_tbl_dbs8,
(MONTH (report_date ) = 9 ) in wmcfg_tbl_dbs9,
(MONTH (report_date ) = 10 ) in wmcfg_tbl_dbs10,
(MONTH (report_date ) = 11 ) in wmcfg_tbl_dbs11,
(MONTH (report_date ) = 12 ) in wmcfg_tbl_dbs12;
Lara:
It will move everything and drop the original copy of the table from the
archive space.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, May 9, 2017 at 7:00 PM, LARA KIZZIA <lancaste21@gmail.com> wrote:
> Art,
> Thank you so much for responding. For my first table, I went ahead and
> created
> a very large "archive" dbspace and restored the entire table there. If I
> use
> ALTER FRAGMENT INIT, will that move everything where I want it or will> there
> still be lingering sys data in my archive space?
>
> ALTER FRAGMENT ON TABLE wmcfgx_new INIT
> FRAGMENT BY EXPRESSION>
> (MONTH (report_date ) = 1 ) in wmcfg_tbl_dbs1,
>
> (MONTH (report_date ) = 2 ) in wmcfg_tbl_dbs2,
>
> (MONTH (report_date ) = 3 ) in wmcfg_tbl_dbs3,
>
> (MONTH (report_date ) = 4 ) in wmcfg_tbl_dbs4,
>
> (MONTH (report_date ) = 5 ) in wmcfg_tbl_dbs5,
>
> (MONTH (report_date ) = 6 ) in wmcfg_tbl_dbs6,
>
> (MONTH (report_date ) = 7 ) in wmcfg_tbl_dbs7,
>
> (MONTH (report_date ) = 8 ) in wmcfg_tbl_dbs8,
>
> (MONTH (report_date ) = 9 ) in wmcfg_tbl_dbs9,
>
> (MONTH (report_date ) = 10 ) in wmcfg_tbl_dbs10,
>
> (MONTH (report_date ) = 11 ) in wmcfg_tbl_dbs11,
>
> (MONTH (report_date ) = 12 ) in wmcfg_tbl_dbs12;
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--94eb2c13edd67c6888054f202762
There is a simple work around that will solve your problem.
Create the "new" table before running the archecker table level restore.
If the table already exists archecker will append the data and then the
command file only requires the table name and the associated columns
portion of the schema and not the fragmentation clause. This should work
and I have used it many times.
Since you are one version 12, I would strongly suggest you update your
fragmentation to "interval" fragmentation. It has many benefits over
expression based fragmentation. It is faster in evaluation as it only does
a single evaluation. In the fragmentation below you will evaluate until
you find a a fit, on average 6 expressions.
CREATE TABLE wmcfg (report=5Fdate DATE)
FRAGMENT BY RANGE report=5Fdate
INTERVAL (NUMTOYMINTERVAL (1,'MONTH')) STORE IN (dbs1, dbs2, dbs3)
PARTITION p2 VALUES < "1/1/2000" IN dbs1
John F. Miller III
miller3@us.ibm.com
503-747-1366
From: "LARA KIZZIA" <lancaste21@gmail.com>
To: ids@iiug.org
Date: 05/09/2017 02:37 PM
Subject: archecker Table Level Restore Error [39152]
Sent by: ids-bounces@iiug.org
Hi,
I am on IBM Informix Dynamic Server Version 12.10.FC5W1.
After a catastrophic failure and being unable to restore from my latest
backup, I have re-installed Informix and re-created all dbspaces, tables,
indexes, etc. Now I am doing Table Level Restores on critical data using
archecker. So far so good. Until I try to restore a partitioned table. This
particular table has 100 Million + records so definitely need to be
partitioned.
I get the following error and archecker will not run. I've tried rewriting
this several different ways that mean the same thing and archecker will not
recognize MONTH. Is there a way to partition/fragment like this in
archecker
TLR?
STATUS: Temporary workspace has been set
ERROR: syntax error at line 376 column 10, near token "month"
ERROR: Error parsing "wmcfg=5Fschema.txt"
CRITICAL ERROR: Unable to initialize extraction
STATUS: archecker completed Physical Restore pid =3D 4878 exit code: 3
Below is my archecker schema file:
set workspace to archive=5Fdbs;
database ibmposdb;
create table "informix".wmcfg
(
store=5Fnumber integer not null ,
report=5Fdate date not null ,
.. . . .
) ;
create table "informix".wmcfg=5Fnew
(
store=5Fnumber integer not null ,
report=5Fdate date not null ,
.. . . .
)
fragment by expression
(MONTH (report=5Fdate ) =3D 1 ) in wmcfg=5Ftbl=5Fdbs1,
(MONTH (report=5Fdate ) =3D 2 ) in wmcfg=5Ftbl=5Fdbs2,
(MONTH (report=5Fdate ) =3D 3 ) in wmcfg=5Ftbl=5Fdbs3,
(MONTH (report=5Fdate ) =3D 4 ) in wmcfg=5Ftbl=5Fdbs4,
(MONTH (report=5Fdate ) =3D 5 ) in wmcfg=5Ftbl=5Fdbs5,
(MONTH (report=5Fdate ) =3D 6 ) in wmcfg=5Ftbl=5Fdbs6,
(MONTH (report=5Fdate ) =3D 7 ) in wmcfg=5Ftbl=5Fdbs7,
(MONTH (report=5Fdate ) =3D 8 ) in wmcfg=5Ftbl=5Fdbs8,
(MONTH (report=5Fdate ) =3D 9 ) in wmcfg=5Ftbl=5Fdbs9,
(MONTH (report=5Fdate ) =3D 10 ) in wmcfg=5Ftbl=5Fdbs10,
(MONTH (report=5Fdate ) =3D 11 ) in wmcfg=5Ftbl=5Fdbs11,
(MONTH (report=5Fdate ) =3D 12 ) in wmcfg=5Ftbl=5Fdbs12
extent size 350000 next size 34992 lock mode page;
insert into wmcfg=5Fnew
select * from wmcfg
where report=5Fdate >=3D '02/01/2016';
restore to current with no log;
***************************************************************************=
****
Forum Note: Use "Reply" to post a response in the discussion forum.
Thank you so much. This worked!
Thank You!! This worked as well and saved some time re-organizing after the restore to get things into the correct dbspaces.