cdr define shardCollection
Frank's 'cdr define shardCollection' (12.10.FC4) failed two ways: a hash-strategy run errored with 'Server -versionCol=version is not listed ... in sqlhosts', and an expression-strategy run gave 'sharding expression required for node REMAINDER'. Madison Pruet noted the option needs two dashes (--versionCol); retyping the dashes (an apparent bad/invisible character) got the command much further, creating the shard tables. It then still ended with 'invalid select syntax (39)' whenever REMAINDER was used; substituting an ordinary IN (...) expression for the last node worked. How to write a working REMAINDER clause is left unanswered in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hey,
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10 days
22:37:40 -- 11778640 Kbytes
I tried to create some Shard with ER. Copied an example of
informix online_doc and modified it as needed,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=expression
-versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
It complained,
sharding expression required for node REMAINDER
Then, I tried another simple one with hash strategy,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
g_nsofops g_ncdcops g_ngdcops
It complained with more confused/messy error,
connect to noaa@-versionCol=version failed
Server -versionCol=version is not listed as a dbserver name in sqlhosts.
(-25555)
command failed -- unable to connect to server specified (5)
Any comments ?
Thanks
Frank
--001a11376d64d02dd80523e39b00
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px
#715FFA solid !important; padding-left:1ex !important; background-color:white
!important; } Two -, not one
Sent from Yahoo Mail for iPad
On Friday, November 6, 2015, 12:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
Hey,
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10 days
22:37:40 -- 11778640 Kbytes
I tried to create some Shard with ER. Copied an example of
informix online_doc and modified it as needed,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=expression
-versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
It complained,
sharding expression required for node REMAINDER
Then, I tried another simple one with hash strategy,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
g_nsofops g_ncdcops g_ngdcops
It complained with more confused/messy error,
connect to noaa@-versionCol=version failed
Server -versionCol=version is not listed as a dbserver name in sqlhosts.
(-25555)
command failed -- unable to connect to server specified (5)
Any comments ?
Thanks
Frank
--001a11376d64d02dd80523e39b00
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px
#715FFA solid !important; padding-left:1ex !important; background-color:white
!important; } --versionCol
Sent from Yahoo Mail for iPad
On Friday, November 6, 2015, 1:26 PM, Madison Pruet <madison_pruet@yahoo.com>
wrote:
blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px
#715FFA solid !important; padding-left:1ex !important; background-color:white
!important; } Two -, not one
Sent from Yahoo Mail for iPad
On Friday, November 6, 2015, 12:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
Hey,
IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10 days
22:37:40 -- 11778640 Kbytes
I tried to create some Shard with ER. Copied an example of
informix online_doc and modified it as needed,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=expression
-versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
It complained,
sharding expression required for node REMAINDER
Then, I tried another simple one with hash strategy,
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
g_nsofops g_ncdcops g_ngdcops
It complained with more confused/messy error,
connect to noaa@-versionCol=version failed
Server -versionCol=version is not listed as a dbserver name in sqlhosts.
(-25555)
command failed -- unable to connect to server specified (5)
Any comments ?
Thanks
Frank
--001a11376d64d02dd80523e39b00
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Not sure what happened in last email.
I verified it was/is -- and run both again, same error ( see following
screen shot),
Thanks
Frank
[informix@maggie shard_er]$ more cdr_ds_head_shard_hash
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
g_nsofops g_ncdcops g_ngdcops
[informix@maggie shard_er]$ cdr_ds_head_shard_hash
connect to noaa@-versionCol=version failed
Server -versionCol=version is not listed as a dbserver name in sqlhosts.
(-25555)
command failed -- unable to connect to server specified (5)
[informix@maggie shard_er]$ more cdr_ds_head_shard
cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
--type=delete --key=datatype_name --strategy=expression
-versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
[informix@maggie shard_er]$ cdr_ds_head_shard
sharding expression required for node REMAINDER
[informix@maggie shard_er]$
On Fri, Nov 6, 2015 at 2:27 PM, Madison Pruet <madison_pruet@yahoo.com>
wrote:
> --versionCol
>
>
> Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
>
> On Friday, November 6, 2015, 1:26 PM, Madison Pruet <
> madison_pruet@yahoo.com> wrote:
>
> Two -, not one
>
>
> Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
>
> On Friday, November 6, 2015, 12:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> Hey,
>
> IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10 days
> 22:37:40 -- 11778640 Kbytes>
> I tried to create some Shard with ER. Copied an example of
> informix online_doc and modified it as needed,
>
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=expression
> -versionCol=version \\\\
> g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> g_ngdcops REMAINDER
>
> It complained,
>
> sharding expression required for node REMAINDER
>
> Then, I tried another simple one with hash strategy,
>
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
> g_nsofops g_ncdcops g_ngdcops
>
> It complained with more confused/messy error,
>
> connect to noaa@-versionCol=version failed
> Server -versionCol=version is not listed as a dbserver name in sqlhosts.
> (-25555)
> command failed -- unable to connect to server specified (5)
>
> Any comments ?
>
> Thanks
> Frank
>
> --001a11376d64d02dd80523e39b00
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a114584da4b88e20523e47a4a
Must be a ghost letter there !
I deleted the two "--" and type two new ones, it is sort of working ....
Thanks Madison!
Frank
[informix@maggie shard_er]$ cdr_ds_head_shard
create table informix.ds_head_sha_errors (
shard_target integer,
shard_key integer,
shard_txcode integer,
shard_dscode integer,
shard_sqlerror integer,
shard_isamerror integer,
inventory_id serial,
dataset_name varchar(255,44),
dataset_size_bytes int8,
datatype_name char(10),
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second,
orig_data_filenm varchar(255,44),
distribution_site char(1),
data_source char(10),
has_visual_file char(1),
restriction_level smallint,
accessible char(1),
uuid char(40),
primary key (shard_target, shard_key)
) lock mode row
Verification of noaa@g_nsofops:informix.ds_head_shard started
Verification of noaa@g_nsofops:informix.ds_head_shard is successful
Verification of noaa@g_ncdcops:informix.ds_head_shard started
Creating table...
create table informix.ds_head_shard (
inventory_id serial not null,
dataset_name varchar(255,44) not null,
dataset_size_bytes int8,
datatype_name char(10) not null,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null,
orig_data_filenm varchar(255,44) not null,
distribution_site char(1) not null,
data_source char(10),
has_visual_file char(1),
restriction_level smallint,
accessible char(1),
uuid char(40),
primary key (inventory_id)) lock mode row;
create index 'informix'.w_ds_head_shard_idx4 on 'informix'.ds_head_shard
(ingest_dt btree_ops) using btree
create index 'informix'.w_ds_head_shard_idx3 on 'informix'.ds_head_shard
(datatype_version btree_ops) using btree
create index 'informix'.w_ds_head_shard_idx2 on 'informix'.ds_head_shard
(datatype_name btree_ops) using btree
create index 'informix'.ds_head_shard_idx1 on 'informix'.ds_head_shard
(uuid btree_ops) using btree
create unique index 'informix'.ds_head_shard_idx0 on
'informix'.ds_head_shard (dataset_name btree_ops) using btree
Verification of noaa@g_ncdcops:informix.ds_head_shard is successful
Verification of noaa@g_ngdcops:informix.ds_head_shard started
Creating table...
create table informix.ds_head_shard (
inventory_id serial not null,
dataset_name varchar(255,44) not null,
dataset_size_bytes int8,
datatype_name char(10) not null,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null,
orig_data_filenm varchar(255,44) not null,
distribution_site char(1) not null,
data_source char(10),
has_visual_file char(1),
restriction_level smallint,
accessible char(1),
uuid char(40),
primary key (inventory_id)) lock mode row;
create index 'informix'.w_ds_head_shard_idx4 on 'informix'.ds_head_shard
(ingest_dt btree_ops) using btree
create index 'informix'.w_ds_head_shard_idx3 on 'informix'.ds_head_shard
(datatype_version btree_ops) using btree
create index 'informix'.w_ds_head_shard_idx2 on 'informix'.ds_head_shard
(datatype_name btree_ops) using btree
create index 'informix'.ds_head_shard_idx1 on 'informix'.ds_head_shard
(uuid btree_ops) using btree
create unique index 'informix'.ds_head_shard_idx0 on
'informix'.ds_head_shard (dataset_name btree_ops) using btree
Verification of noaa@g_ngdcops:informix.ds_head_shard is successful
Deleteing replicate ifxshard_ds_head_sha_g_ncdcops_0
Deleteing replicate ifxshard_ds_head_sha_g_nsofops_0
command failed -- invalid select syntax (39)
[informix@maggie shard_er]$
On Fri, Nov 6, 2015 at 2:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Not sure what happened in last email.
>
> I verified it was/is -- and run both again, same error ( see following
> screen shot),
>
> Thanks
> Frank
>
> [informix@maggie shard_er]$ more cdr_ds_head_shard_hash
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
> g_nsofops g_ncdcops g_ngdcops
> [informix@maggie shard_er]$ cdr_ds_head_shard_hash
> connect to noaa@-versionCol=version failed
> Server -versionCol=version is not listed as a dbserver name in sqlhosts.
> (-25555)
> command failed -- unable to connect to server specified (5)
> [informix@maggie shard_er]$ more cdr_ds_head_shard
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=expression
> -versionCol=version \\\\
> g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> g_ngdcops REMAINDER
>
> [informix@maggie shard_er]$ cdr_ds_head_shard
> sharding expression required for node REMAINDER
> [informix@maggie shard_er]$
>
> On Fri, Nov 6, 2015 at 2:27 PM, Madison Pruet <madison_pruet@yahoo.com>
> wrote:
>
> > --versionCol
> >
> >
> > Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
> >
> > On Friday, November 6, 2015, 1:26 PM, Madison Pruet <
> > madison_pruet@yahoo.com> wrote:
> >
> > Two -, not one
> >
> >
> > Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
> >
> > On Friday, November 6, 2015, 12:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > Hey,
> >
> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10> days
> > 22:37:40 -- 11778640 Kbytes
> >
> > I tried to create some Shard with ER. Copied an example of
> > informix online_doc and modified it as needed,
> >
> > cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> > --type=delete --key=datatype_name --strategy=expression
> > -versionCol=version \\\\
> > g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> > g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> > g_ngdcops REMAINDER
> >
> > It complained,
> >
> > sharding expression required for node REMAINDER
> >
> > Then, I tried another simple one with hash strategy,
> >
> > cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> > --type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
> > g_nsofops g_ncdcops g_ngdcops
> >
> > It complained with more confused/messy error,
> >
> > connect to noaa@-versionCol=version failed
> > Server -versionCol=version is not listed as a dbserver name in sqlhosts.
> > (-25555)
> > command failed -- unable to connect to server specified (5)
> >
> > Any comments ?
> >
> > Thanks
> > Frank
> >
> > --001a11376d64d02dd80523e39b00
> >
> >
> >
>
>
*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a114584da4b88e20523e47a4a
>
>
>
>
*******************************************************************************@@N
Screen shots and other attachments do not work in the forums.
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 Fri, Nov 6, 2015 at 2:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Not sure what happened in last email.
>
> I verified it was/is -- and run both again, same error ( see following
> screen shot),
>
> Thanks
> Frank
>
> [informix@maggie shard_er]$ more cdr_ds_head_shard_hash
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
> g_nsofops g_ncdcops g_ngdcops
> [informix@maggie shard_er]$ cdr_ds_head_shard_hash
> connect to noaa@-versionCol=version failed
> Server -versionCol=version is not listed as a dbserver name in sqlhosts.
> (-25555)
> command failed -- unable to connect to server specified (5)
> [informix@maggie shard_er]$ more cdr_ds_head_shard
> cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> --type=delete --key=datatype_name --strategy=expression
> -versionCol=version \\\\
> g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> g_ngdcops REMAINDER
>
> [informix@maggie shard_er]$ cdr_ds_head_shard
> sharding expression required for node REMAINDER
> [informix@maggie shard_er]$
>
> On Fri, Nov 6, 2015 at 2:27 PM, Madison Pruet <madison_pruet@yahoo.com>
> wrote:
>
> > --versionCol
> >
> >
> > Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
> >
> > On Friday, November 6, 2015, 1:26 PM, Madison Pruet <
> > madison_pruet@yahoo.com> wrote:
> >
> > Two -, not one
> >
> >
> > Sent from Yahoo Mail for iPad <https://yho.com/footer0>;
> >
> > On Friday, November 6, 2015, 12:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
> >
> > Hey,
> >
> > IBM Informix Dynamic Server Version 12.10.FC4W1XU -- On-Line -- Up 10> days
> > 22:37:40 -- 11778640 Kbytes
> >
> > I tried to create some Shard with ER. Copied an example of
> > informix online_doc and modified it as needed,
> >
> > cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> > --type=delete --key=datatype_name --strategy=expression
> > -versionCol=version \\\\
> > g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> > g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> > g_ngdcops REMAINDER
> >
> > It complained,
> >
> > sharding expression required for node REMAINDER
> >
> > Then, I tried another simple one with hash strategy,
> >
> > cdr define shardCollection ds_head_sha noaa:informix.ds_head_shard \\\\
> > --type=delete --key=datatype_name --strategy=hash -versionCol=version \\\\
> > g_nsofops g_ncdcops g_ngdcops
> >
> > It complained with more confused/messy error,
> >
> > connect to noaa@-versionCol=version failed
> > Server -versionCol=version is not listed as a dbserver name in sqlhosts.
> > (-25555)
> > command failed -- unable to connect to server specified (5)
> >
> > Any comments ?
> >
> > Thanks
> > Frank
> >
> > --001a11376d64d02dd80523e39b00
> >
> >
> >
>
>
*******************************************************************************
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a114584da4b88e20523e47a4a
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a113ff82686632b0523e4ea6b
Seems another bump,
1) simple table definition:
create table "informix".ds_head_shard_easy
(
inventory_id serial not null ,
dataset_name varchar(255,44) not null ,
dataset_size_bytes int8,
datatype_name char(10) not null ,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null ,
orig_data_filenm varchar(255,44) not null ,
primary key (inventory_id)
) extent size 16 next size 16 lock mode row;
2) run the command:
cdr define shardCollection ds_head_sha_easy
noaa:informix.ds_head_shard_easy \\\\
--type=delete --key=datatype_name --strategy=expression
--versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
3) output:
create table informix.ds_head_sha_easy_errors (
shard_target integer,
shard_key integer,
shard_txcode integer,
shard_dscode integer,
shard_sqlerror integer,
shard_isamerror integer,
inventory_id serial,
dataset_name varchar(255,44),
dataset_size_bytes int8,
datatype_name char(10),
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second,
orig_data_filenm varchar(255,44),
primary key (shard_target, shard_key)
) lock mode row
Verification of noaa@g_nsofops:informix.ds_head_shard_easy started
Verification of noaa@g_nsofops:informix.ds_head_shard_easy is successful
Verification of noaa@g_ncdcops:informix.ds_head_shard_easy started
Creating table...
create table informix.ds_head_shard_easy (
inventory_id serial not null,
dataset_name varchar(255,44) not null,
dataset_size_bytes int8,
datatype_name char(10) not null,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null,
orig_data_filenm varchar(255,44) not null,
primary key (inventory_id)) lock mode row;
Verification of noaa@g_ncdcops:informix.ds_head_shard_easy is successful
Verification of noaa@g_ngdcops:informix.ds_head_shard_easy started
Creating table...
create table informix.ds_head_shard_easy (
inventory_id serial not null,
dataset_name varchar(255,44) not null,
dataset_size_bytes int8,
datatype_name char(10) not null,
datatype_version char(10),
ingest_status char(10),
ingest_dt datetime year to second not null,
orig_data_filenm varchar(255,44) not null,
primary key (inventory_id)) lock mode row;
Verification of noaa@g_ngdcops:informix.ds_head_shard_easy is successful
Deleteing replicate ifxshard_ds_head_sha_easy_g_ncdcops_0
Deleteing replicate ifxshard_ds_head_sha_easy_g_nsofops_0
command failed -- invalid select syntax (39)
What is the last error "invalid select syntax"?
Thanks
Frank
On Fri, Nov 6, 2015 at 2:53 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Must be a ghost letter there !
> I deleted the two "--" and type two new ones, it is sort of working ....
>
> Thanks Madison!
>
> Frank
>
> [informix@maggie shard_er]$ cdr_ds_head_shard
> create table informix.ds_head_sha_errors (
> shard_target integer,
> shard_key integer,
> shard_txcode integer,
> shard_dscode integer,
> shard_sqlerror integer,
> shard_isamerror integer,>
> inventory_id serial,
>
> dataset_name varchar(255,44),
>
> dataset_size_bytes int8,
>
> datatype_name char(10),
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second,
>
> orig_data_filenm varchar(255,44),
>
> distribution_site char(1),
>
> data_source char(10),
>
> has_visual_file char(1),
>
> restriction_level smallint,
>
> accessible char(1),
>
> uuid char(40),
> primary key (shard_target, shard_key)
>
> ) lock mode row
> Verification of noaa@g_nsofops:informix.ds_head_shard started
> Verification of noaa@g_nsofops:informix.ds_head_shard is successful
> Verification of noaa@g_ncdcops:informix.ds_head_shard started
> Creating table...
> create table informix.ds_head_shard (>
> inventory_id serial not null,
>
> dataset_name varchar(255,44) not null,
>
> dataset_size_bytes int8,
>
> datatype_name char(10) not null,
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second not null,
>
> orig_data_filenm varchar(255,44) not null,
>
> distribution_site char(1) not null,
>
> data_source char(10),
>
> has_visual_file char(1),
>
> restriction_level smallint,
>
> accessible char(1),
>
> uuid char(40),
>
> primary key (inventory_id)) lock mode row;
> create index 'informix'.w_ds_head_shard_idx4 on 'informix'.ds_head_shard
> (ingest_dt btree_ops) using btree
> create index 'informix'.w_ds_head_shard_idx3 on 'informix'.ds_head_shard
> (datatype_version btree_ops) using btree
> create index 'informix'.w_ds_head_shard_idx2 on 'informix'.ds_head_shard
> (datatype_name btree_ops) using btree
> create index 'informix'.ds_head_shard_idx1 on 'informix'.ds_head_shard
> (uuid btree_ops) using btree
> create unique index 'informix'.ds_head_shard_idx0 on
> 'informix'.ds_head_shard (dataset_name btree_ops) using btree
> Verification of noaa@g_ncdcops:informix.ds_head_shard is successful
> Verification of noaa@g_ngdcops:informix.ds_head_shard started
> Creating table...
> create table informix.ds_head_shard (>
> inventory_id serial not null,
>
> dataset_name varchar(255,44) not null,
>
> dataset_size_bytes int8,
>
> datatype_name char(10) not null,
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second not null,
>
> orig_data_filenm varchar(255,44) not null,
>
> distribution_site char(1) not null,
>
> data_source char(10),
>
> has_visual_file char(1),
>
> restriction_level smallint,
>
> accessible char(1),
>
> uuid char(40),
>
> primary key (inventory_id)) lock mode row;
> create index 'informix'.w_ds_head_shard_idx4 on 'informix'.ds_head_shard
> (ingest_dt btree_ops) using btree
> create index 'informix'.w_ds_head_shard_idx3 on 'informix'.ds_head_shard
> (datatype_version btree_ops) using btree
> create index 'informix'.w_ds_head_shard_idx2 on 'informix'.ds_head_shard
> (datatype_name btree_ops) using btree
> create index 'informix'.ds_head_shard_idx1 on 'informix'.ds_head_shard
> (uuid btree_ops) using btree
> create unique index 'informix'.ds_head_shard_idx0 on
> 'informix'.ds_head_shard (dataset_name btree_ops) using btree
> Verification of noaa@g_ngdcops:informix.ds_head_shard is successful
> Deleteing replicate ifxshard_ds_head_sha_g_ncdcops_0
> Deleteing replicate ifxshard_ds_head_sha_g_nsofops_0
> command failed -- invalid select syntax (39)
> [informix@maggie shard_er]$
>
> On Fri, Nov 6, 2015 at 2:45 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Not sure what happened in last email.@@NL@
After I replaced the REMAINDER of the following command,
cdr define shardCollection ds_head_sha_easy
noaa:informix.ds_head_shard_easy \\\\
--type=delete --key=datatype_name --strategy=expression
--versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops REMAINDER
with a regular " IN ('haha') ",
cdr define shardCollection ds_head_sha_easy
noaa:informix.ds_head_shard_easy \\\\
--type=delete --key=datatype_name --strategy=expression
--versionCol=version \\\\
g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
g_ngdcops " IN ('haha') "
It worked fine with no complained Syntax error!
Hmmm...... ....
Just Curious how to write a right syntax to handle Remainder clause
there ...
Thanks
Frank
On Fri, Nov 6, 2015 at 4:05 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Seems another bump,
>
> 1) simple table definition:
>
> create table "informix".ds_head_shard_easy
> (
>
> inventory_id serial not null ,
>
> dataset_name varchar(255,44) not null ,
>
> dataset_size_bytes int8,
>
> datatype_name char(10) not null ,
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second not null ,
>
> orig_data_filenm varchar(255,44) not null ,
>
> primary key (inventory_id)
> ) extent size 16 next size 16 lock mode row;
>
> 2) run the command:
>
> cdr define shardCollection ds_head_sha_easy
> noaa:informix.ds_head_shard_easy \\\\
> --type=delete --key=datatype_name --strategy=expression
> --versionCol=version \\\\
> g_nsofops "IN ('CORGNSS','CORDLYOBS','GOME')" \\\\
> g_ncdcops "IN ('ASCAT','GVAR_IMG','IASI')" \\\\
> g_ngdcops REMAINDER
>
> 3) output:
>
> create table informix.ds_head_sha_easy_errors (
> shard_target integer,
> shard_key integer,
> shard_txcode integer,
> shard_dscode integer,
> shard_sqlerror integer,
> shard_isamerror integer,>
> inventory_id serial,
>
> dataset_name varchar(255,44),
>
> dataset_size_bytes int8,
>
> datatype_name char(10),
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second,
>
> orig_data_filenm varchar(255,44),
> primary key (shard_target, shard_key)
>
> ) lock mode row
> Verification of noaa@g_nsofops:informix.ds_head_shard_easy started
> Verification of noaa@g_nsofops:informix.ds_head_shard_easy is successful
> Verification of noaa@g_ncdcops:informix.ds_head_shard_easy started
> Creating table...
> create table informix.ds_head_shard_easy (>
> inventory_id serial not null,
>
> dataset_name varchar(255,44) not null,
>
> dataset_size_bytes int8,
>
> datatype_name char(10) not null,
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second not null,
>
> orig_data_filenm varchar(255,44) not null,
>
> primary key (inventory_id)) lock mode row;
> Verification of noaa@g_ncdcops:informix.ds_head_shard_easy is successful
> Verification of noaa@g_ngdcops:informix.ds_head_shard_easy started
> Creating table...
> create table informix.ds_head_shard_easy (>
> inventory_id serial not null,
>
> dataset_name varchar(255,44) not null,
>
> dataset_size_bytes int8,
>
> datatype_name char(10) not null,
>
> datatype_version char(10),
>
> ingest_status char(10),
>
> ingest_dt datetime year to second not null,
>
> orig_data_filenm varchar(255,44) not null,
>
> primary key (inventory_id)) lock mode row;
> Verification of noaa@g_ngdcops:informix.ds_head_shard_easy is successful
> Deleteing replicate ifxshard_ds_head_sha_easy_g_ncdcops_0
> Deleteing replicate ifxshard_ds_head_sha_easy_g_nsofops_0
> command failed -- invalid select syntax (39)
>
> What is the last error "invalid select syntax"?
>
> Thanks
> Frank
>
> On Fri, Nov 6, 2015 at 2:53 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Must be a ghost letter there !
> > I deleted the two "--" and type two new ones, it is sort of working ....
> >
> > Thanks Madison!
> >
> > Frank
> >
> > [informix@maggie shard_er]$ cdr_ds_head_shard
> > create table informix.ds_head_sha_errors (
> > shard_target integer,
> > shard_key integer,
> > shard_txcode integer,
> > shard_dscode integer,
> > shard_sqlerror integer,
> > shard_isamerror integer,> >
> > inventory_id serial,
> >
> > dataset_name varchar(255,44),
> >
> > dataset_size_bytes int8,
> >
> > datatype_name char(10),
> >
> > datatype_version char(10),
> >
> > ingest_status char(10),
> >
> > ingest_dt datetime year to second,
> >
> > orig_data_filenm varchar(255,44),
> >
> > distribution_site char(1),
> >
> > data_source char(10),
> >
> > has_visual_file char(1),
> >
> > restriction_level smallint,
> >
> > accessible char(1),
> >
> > uuid char(40),
> > primary key (shard_target, shard_key)
> >
> > ) lock mode row
> > Verification of noaa@g_nsofops:informix.ds_head_shard started
> > Verification of noaa@g_nsofops:informix.ds_head_shard is successful
> > Verification of noaa@g_ncdcops:informix.ds_head_shard started
> > Creating table...
> > create table informix.ds_head_shard (> >
> > inventory_id serial not null,
> >
> > dataset_name varchar(255,44) not null,
> >
> > dataset_size_bytes int8,
> >
> > datatype_name char(10) not null,
> >
> > datatype_version char(10),
> >
> > ingest_status char(10),
> >
> > ingest_dt datetime year to second not null,
> >
> > orig_data_filenm varchar(255,44) not null,
> >
> > distribution_site char(1) not null,
> >
> > data_source char(10),
> >
> > has_visual_file char(1),
> >
> > restriction_level smallint,
> >
> > accessible char(1),
> >
> > uuid char(40),
> >
> > primary key (inventory_id)) lock mode row;
> > create index 'informix'.w_ds_head_shard_idx4 on 'informix'.ds_head_shard
> > (ingest_dt btree_ops) using btree
> > create index 'informix'.w_ds_head_shard_idx3 on 'informix'.ds_head_shard
> > (datatype_version btree_ops) using btree
> > create index 'informix'.w_ds_head_shard_idx2 on 'informix'.ds_head_shard
> > (datatype_name btree_ops) using btree
> > create index 'informix'.ds_head_shard_idx1 on 'informix'.ds_head_shard
> > (uuid btree_ops) using btree
> > create unique index 'informix'.ds_head_shard_idx0 on
> > 'informix'.ds_head_shard (dataset_name btree_ops) using btree
> > Verification of noaa@g_ncdcops:informix.ds_head_shard is successful
> > Verification of noaa@g_ngdcops:informix.ds_head_shard started
> > Creating table...
> > create table informix.ds_head_shard (> >
> > inventory_id serial not null,
> >
> > dataset_name varchar(255,44) not null,
> >
> > dataset_size_bytes int8,
> >
> > datatype_name char(10) not null,
> >
> > dat