I learned something new!

Have you ever needed the functionality of ROWID for use with a partitioned table that you did not create using the WITH ROWID clause? Well, I recently found out that such a facility is available!

It is not compatible with the traditional ROWID, so you cannot just use it in applications that were written for ROWID use on single partition tables, but it works and it does not require any indexing or rebuilding your massive multi-partition table! The undocumented pseudo-column named ifx_row_id. This is a VARCHAR(45) type and it returns a string of the form "ppppppp:rrr" where the p's are the fragment's partnum and the r's are the row's actual rowid within the partition. Also it works exactly the same for single partition (ie non-fragmented) tables as it does for multi-partition tables.

You can select the ifx_row_id for any row in any table:

select ifx_row_id from systables where tabid = 1;

ifx_row_id                                    

5244160:769                                 


You can select data by ifx_rowid:

select * from systables where ifx_row_id = '5244160:769';

tabname          systables
owner            informix
partnum          5244160
tabid            1
rowsize          500
ncols            26
nindexes         2
nrows            307.0000000000
created          01/30/2019
version          65638
tabtype          T
locklevel        R
npused           22.00000000000
fextsize         16
nextsize         16
flags            0
site             
dbname           
type_xid         0
am_id            0
pagesize         2048
ustlowts         2019-11-03 08:44:57.35225
secpolicyid      0
protgranularity  
statchange       
statlevel         


If you do need a numeric "rowid" you could convert it to a BIGINT:

CREATE FUNCTION "art".ifx_row_id_to_rowid ( ifx lvarchar ) returning bigint;
define partnm, rw, bigrowid bigint;
define pos int;

let pos=instr( ifx, ':' );

let partnm=left( ifx, pos - 1 )::bigint;
let rw=substr( ifx, pos + 1, 10 )::bigint;
let bigrowid=(partnm * pow(2, 32 )::bigint ) + rw;

return bigrowid;
end function;