Live Database Reverse Engineering Physical Options

During reverse engineering, physical options are concatenated in a single string statement. However, when the system tables of a database are partitioned (like in Oracle) or fragmented (like in Informix), the partitions/fragments share the same logical attributes but their physical properties like storage specifications, are stored in each partition/fragment of the database. The columns in the partitions/fragments have a data type (LONG) that allows storing larger amount of unstructured binary information.

Since physical options in these columns cannot be concatenated in the string statement during reverse engineering, SqlOptsQuery (Tables category in the DBMS) contains a call to a user-defined query that will evaluate these physical options.

In Informix SQL 9, SqlOptsQuery is delivered by default with the following user-defined queries and variables (the following is a subset of SqlOptsQuery):

select
 t.owner,
 t.tabname,
 '%SqlFragQuery.FragSprt'||f.evalpos||'% %FragExpr'||f.evalpos||'% in %FragDbsp'||f.evalpos||'% ',
 f.evalpos
from
 informix.systables t,
 informix.sysfragments f
where 
 t.partnum = 0
 and t.tabid=f.tabid
[  and t.owner = '%SCHEMA%']
[  and t.tabname='%TABLE%']

After the execution of SqlOptsQuery, the user-defined query SqlFragQuery is executed to evaluate FragDbsp n, FragExpr n, and FragSprt n. n stands for evalpos which defines fragment position in the fragmentation list. n allows to assign unique names to variables, whatever the number of fragment defined in the table.

FragDbsp n, FragExpr n, and FragSprt n are user-defined variables that will be evaluated to recover information concerning the physical options of fragments in the database:

SqlFragQuery is defined as follows:

{A, a(E="expression", R="round robin", H="hash"), B, b, C, c, D, d(0="", *=",")}
select
 'FragDbsp'||f.evalpos, f.dbspace,
 'FragExpr'||f.evalpos, f.exprtext,
 'FragSprt'||f.evalpos, f.evalpos
from
 informix.systables t,
 informix.sysfragments f
where 
 t.partnum = 0
 and f.fragtype='T'
 and t.tabid=f.tabid
[  and t.owner = '%SCHEMA%']
[  and t.tabname='%TABLE%']

The header of SqlFragQuery contains the following variable names.

{A, a(E="expression", R="round robin", H="hash"), B, b, C, c, D, d(0="", *=",")}

Only the translation rules defined between brackets will be used during string concatenation: "FragSprt0", which contains 0 (f.evalpos), will be replaced by " ", and "FragSprt1", which contains 1, will be replaced by ","

SqlFragQuery generates a numbered resultset containing as many pairs of user-defined variable name (without %) and variable value as needed, if there are many variables to evaluate.

The user-defined variable names are replaced by their values in the string statement for the physical options of fragments in the database.