The rowid column does not exist in the user table.

3. RE: The rowid column does not exist in the user table.

Like

SangGyu Jeong

Posted Thu January 16, 2020 04:51 AM

Oops ... I was mistaken because AUTOLOCATE was set to 3 and created as a fragment table. Thanks for the reply.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message

Original Message:
Sent: Thu January 16, 2020 03:40 AM
From: Andreas Legner
Subject: The rowid column does not exist in the user table.

onstat -g cfg full AUTOLOCATE

You might inadvertently have got a (signle-fragment round-robin) fragmented table
-> check 'oncheck -pt' or query systables/sysfragments for table in question.

BR,
Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Thu January 16, 2020 02:26 AM
From: SangGyu Jeong
Subject: The rowid column does not exist in the user table.

Hi,
I noticed that since the 12.10.xc13 version, no rowid columns are created for user-created tables.
When I try to query the rowid column I get an 857 error.
So if you need a rowid column, you should use ifx_row_id.

Catalog tables such as systables had a rowid column.
Of course, you can add a rowid column to the fragment table with the 'alter table ... add rowids' statement.

Interestingly, in 14.10.xc3, there was a rowid column in the table I created.

$ echo "select dbinfo('version','full') from sysmaster:sysdual" | dbaccess sysmasterDatabase selected.(constant)IBM Informix Dynamic Server Version 12.10.FC13WE1 row(s) retrieved.Database closed.$ echo "create table test as select * from customer" | dbaccess stores_demoDatabase selected.28 row(s) retrieved into table.Database closed.$ echo "select first 1 rowid from test" | dbaccess stores_demoDatabase selected.  857: Rowids do not exist on table.Error in line 1Near character position 30Database closed.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix

5. RE: The rowid column does not exist in the user table.

Like

SangGyu Jeong

Posted Thu January 16, 2020 06:14 AM

Hi Art,
Yes. The AUTOLOCATE setting was set to 3. In the knowledge center documentation, rowid is described as a deprecated feature so I thought the rowid column was gone.
Thanks for your reply.

https://www.ibm.com/support/knowledgecenter/cs/SSGU8G_11.70.0/com.ibm.sqls.doc/ids_sqs_0544.htm

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message

Original Message:
Sent: Thu January 16, 2020 05:52 AM
From: Art Kagel
Subject: The rowid column does not exist in the user table.

Do you have AUTOLOCATE set to a value greater than 1? If so then tables created without an IN or other FRAGMENT IN clause are created partitioned ROUND ROBIN with the specified number of partitions. Partitioned tables do not have a ROWID unless the table was created by including the WITH ROWID clause!

You can use the undocumented universal rowid pseudo-column which is supported even for partitioned tables: ifx_row_id:

$ myschema -d art -t fragtest -q

CREATE TABLE "art".fragtest (
one INTEGER,
two CHAR(100)
) FRAGMENT BY ROUND ROBIN
PARTITION datadbs IN datadbs,
PARTITION rootdbs IN datadbs
EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;
...

art@Elbereth:~/GoogleDrive/iiug/utils2_ak$ dbaccess art -

Database selected.

> select ifx_row_id from fragtest;

ifx_row_id

5242935:257
5242935:258
5242935:259
5242935:260
5242935:261
5242935:262
5242935:263
5242935:264
5242935:265
5242936:257
5242936:258
5242936:259
5242936:260
5242936:261
5242936:262

15 row(s) retrieved.

------------------------------
Art Kagel

Original Message:
Sent: Thu January 16, 2020 02:26 AM
From: SangGyu Jeong
Subject: The rowid column does not exist in the user table.

Hi,
I noticed that since the 12.10.xc13 version, no rowid columns are created for user-created tables.
When I try to query the rowid column I get an 857 error.
So if you need a rowid column, you should use ifx_row_id.

Catalog tables such as systables had a rowid column.
Of course, you can add a rowid column to the fragment table with the 'alter table ... add rowids' statement.

Interestingly, in 14.10.xc3, there was a rowid column in the table I created.

$ echo "select dbinfo('version','full') from sysmaster:sysdual" | dbaccess sysmasterDatabase selected.(constant)IBM Informix Dynamic Server Version 12.10.FC13WE1 row(s) retrieved.Database closed.$ echo "create table test as select * from customer" | dbaccess stores_demoDatabase selected.28 row(s) retrieved into table.Database closed.$ echo "select first 1 rowid from test" | dbaccess stores_demoDatabase selected.  857: Rowids do not exist on table.Error in line 1Near character position 30Database closed.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix

6. RE: The rowid column does not exist in the user table.

Like

Jacob Salomon

Posted Thu January 16, 2020 12:16 PM

Hi Art.

Here my $0.02 (US) on this matter.

Once the ifmx_row_id genie is out of the bottle, NOBODY should ever create their partitioned tables WITH ROWID. Last time I did that I ended up with an unremovable index that just added clutter to a table already cluttered with too many indexes. Thank you for rubbing that lamp for us!

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
------------------------------

Original Message

Original Message:
Sent: Thu January 16, 2020 05:52 AM
From: Art Kagel
Subject: The rowid column does not exist in the user table.

Do you have AUTOLOCATE set to a value greater than 1? If so then tables created without an IN or other FRAGMENT IN clause are created partitioned ROUND ROBIN with the specified number of partitions. Partitioned tables do not have a ROWID unless the table was created by including the WITH ROWID clause!

You can use the undocumented universal rowid pseudo-column which is supported even for partitioned tables: ifx_row_id:

$ myschema -d art -t fragtest -q

CREATE TABLE "art".fragtest (
one INTEGER,
two CHAR(100)
) FRAGMENT BY ROUND ROBIN
PARTITION datadbs IN datadbs,
PARTITION rootdbs IN datadbs
EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE PAGE;
...

art@Elbereth:~/GoogleDrive/iiug/utils2_ak$ dbaccess art -

Database selected.

> select ifx_row_id from fragtest;

ifx_row_id

5242935:257
5242935:258
5242935:259
5242935:260
5242935:261
5242935:262
5242935:263
5242935:264
5242935:265
5242936:257
5242936:258
5242936:259
5242936:260
5242936:261
5242936:262

15 row(s) retrieved.

------------------------------
Art Kagel

Original Message:
Sent: Thu January 16, 2020 02:26 AM
From: SangGyu Jeong
Subject: The rowid column does not exist in the user table.

Hi,
I noticed that since the 12.10.xc13 version, no rowid columns are created for user-created tables.
When I try to query the rowid column I get an 857 error.
So if you need a rowid column, you should use ifx_row_id.

Catalog tables such as systables had a rowid column.
Of course, you can add a rowid column to the fragment table with the 'alter table ... add rowids' statement.

Interestingly, in 14.10.xc3, there was a rowid column in the table I created.

$ echo "select dbinfo('version','full') from sysmaster:sysdual" | dbaccess sysmasterDatabase selected.(constant)IBM Informix Dynamic Server Version 12.10.FC13WE1 row(s) retrieved.Database closed.$ echo "create table test as select * from customer" | dbaccess stores_demoDatabase selected.28 row(s) retrieved into table.Database closed.$ echo "select first 1 rowid from test" | dbaccess stores_demoDatabase selected.  857: Rowids do not exist on table.Error in line 1Near character position 30Database closed.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix

9. RE: The rowid column does not exist in the user table.

Like

SangGyu Jeong

Posted Thu January 16, 2020 09:24 AM
Edited by System Admin Fri January 20, 2023 04:16 PM

Yes Paul. The table is fragmented by AUTOLOCATE feature.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Original Message

Original Message:
Sent: Thu January 16, 2020 09:10 AM
From: Paul Watson
Subject: The rowid column does not exist in the user table.

Fragmented ?

Cheers

Paul

Original Message------

Hi,
I noticed that since the 12.10.xc13 version, no rowid columns are created for user-created tables.
When I try to query the rowid column I get an 857 error.
So if you need a rowid column, you should use ifx_row_id.

Catalog tables such as systables had a rowid column.
Of course, you can add a rowid column to the fragment table with the 'alter table ... add rowids' statement.

Interestingly, in 14.10.xc3, there was a rowid column in the table I created.

$ echo "select dbinfo('version','full') from sysmaster:sysdual" | dbaccess sysmasterDatabase selected.(constant)IBM Informix Dynamic Server Version 12.10.FC13WE1 row(s) retrieved.Database closed.$ echo "create table test as select * from customer" | dbaccess stores_demoDatabase selected.28 row(s) retrieved into table.Database closed.$ echo "select first 1 rowid from test" | dbaccess stores_demoDatabase selected.  857: Rowids do not exist on table.Error in line 1Near character position 30Database closed.

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
#Informix

Informix

Informix