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 sysmaster Database selected. (constant) IBM Informix Dynamic Server Version 12.10.FC13WE 1 row(s) retrieved. Database closed. $ echo "create table test as select * from customer" | dbaccess stores_demo Database selected. 28 row(s) retrieved into table. Database closed. $ echo "select first 1 rowid from test" | dbaccess stores_demo Database selected. 857: Rowids do not exist on table. Error in line 1Near character position 30 Database closed.​ ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------ #Informix

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 -qCREATE 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 ------------------------------

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 ------------------------------

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 sysmaster Database selected. (constant) IBM Informix Dynamic Server Version 12.10.FC13WE 1 row(s) retrieved. Database closed. $ echo "create table test as select * from customer" | dbaccess stores_demo Database selected. 28 row(s) retrieved into table. Database closed. $ echo "select first 1 rowid from test" | dbaccess stores_demo Database selected. 857: Rowids do not exist on table. Error in line 1Near character position 30 Database closed.​ ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------ #Informix

Yes Paul. The table is fragmented by AUTOLOCATE feature. ------------------------------ SangGyu Jeong Software Engineer Infrasoft Seoul Korea, Republic of ------------------------------

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 ------------------------------

You are welcome Jacob. But I let that genie out back in November in my Blog post! Art 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. ------Original Message------ 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 ------------------------------ #Informix