Row size in dbschema
I developed a stored procedure that returns the size in bytes of a given
column.
I used all the information given in the "Informix Guide to SQL - Reference -
V 4.1" to know how to calculate the size based on syscolumns.coltype and
syscolumns.collength. I'm running Informix Dynamic Server Version 7.31.UC2
over SCO Release = 3.2v5.0.4
But when I run my sp, the number returned is not equal to the number
returned using dbschema.
Could be the difference between the docs version and the software? Or it is
my mistake?
Thanks in advance !!!
The table structure is:
{ TABLE "tecsys".aaa_mads row size = 187 number of columns = 16 index size =
12 }
create table "tecsys".aaa_mads
(
a serial not null ,
b integer,
c smallint,
d decimal(16),
e decimal(16,2),
f decimal(5,3),
g smallfloat,
h float,
i date,
j money(16,2),
k money(5,3),
l datetime year to fraction(5),
m interval year(2) to month,
n interval day(4) to fraction(5),
o varchar(50,1),
p varchar(50,10)
);
create unique index "tecsys".ix952_1 on "tecsys".aaa_mads (a);
The stored procedure is:
-- ######################################################################
create procedure sp_colbytes (v_coltype smallint,
v_collength smallint)
returning smallint;-- ######################################################################
-- recibiendo como parametros:
-- v_coltype : tipo de la col (syscolumns.coltype)
-- v_collength : "largo" de la col (syscolumns.collength)
-- retorna la cantidad de bytes que la misma utiliza
define v_smint smallint; -- auxiliar para calculos
define v_bytes smallint; -- numero de bytes
if v_coltype > 256
then
-- la columna es not null... pero no viene al caso
-- averiguo el tipo de datos quitandole el not null
let v_coltype = v_coltype - 256;
end if;
let v_bytes = 0;
if v_coltype in (0, -- char
1, -- smallint (2 bytes)
2, -- integer (4 bytes)
3, -- float (8 bytes generalmente)
4, -- smallfloat (4 bytes generalmente)
6, -- serial (4 bytes)
7) -- date (4 bytes)
then
let v_bytes = v_collength;
elif v_coltype in (5, -- decimal
8, -- money
10, -- datetime
14) -- interval
then
-- dec & money: bytes = (precision / 2 ) + 1
-- datetime & interval: bytes = (numero total de digitos / 2 ) + 1
let v_smint = v_collength / 256; -- tomo solo la parte entera
let v_bytes = (v_smint / 2) + 1;
elif v_coltype in (9, -- No existe el 9 en la documentacion !!!
11, -- byte - tama~o desconocido
12) -- text - tama~o desconocido
then
let v_bytes = 0;
elif v_coltype = 13 -- varchar
then
-- busco el largo maximo, no me importa el espacio reservado
-- Acotacion: si quieres el largo reservado usa v_collength / 256
-- Lamentablemente el SPL de Informix es, en cuanto a instrucciones,
-- igual a Tatu, o sea, recortado. Por eso el operador MOD no existe.
-- Asi que resuelvo con un select
-- let v_bytes = v_collength mod 256;
select mod(v_collength, 256)
into v_bytes
from systables
where tabid = 1;
end if;
return v_bytes;
end procedure -- sp_colbytes()
And I'm executing the sp with the following instructions:
select t.tabname, c.colno, c.colname, c.coltype, c.collength,
sp_colbytes(c.coltype, c.collength)
from systables t, syscolumns c
where t.tabid = c.tabid
and t.tabname matches "*aaa_mads*"
order by 1, 2;
select t.tabname, sum(sp_colbytes(c.coltype, c.collength))
from systables t, syscolumns c
where t.tabid = c.tabid
and t.tabname matches "*aaa_mads*"
group by 1;
--
Manuel A. Daponte Santiago
Systems Consultant, ICP
"Manuel A. Daponte Santiago" wrote:
> I developed a stored procedure that returns the size in bytes of a given
> column.
>
> I used all the information given in the "Informix Guide to SQL - Reference -
> V 4.1" to know how to calculate the size based on syscolumns.coltype and
> syscolumns.collength. I'm running Informix Dynamic Server Version 7.31.UC2
> over SCO Release = 3.2v5.0.4
>
> But when I run my sp, the number returned is not equal to the number
> returned using dbschema.
>
> Could be the difference between the docs version and the software? Or it is
> my mistake?
Looks like it. Download the latest documentation from the Informix Web-site.
Your SP's problems appear to be in the following datatypes
1. Decimal : ROUND((((collength/256) - MOD(collength,256))/2),0)
+ ROUND(MOD(collength,256)/2,0) + 1
2. Money : same as Decimal
3. Datetime : ROUND((collength/512)+1,0)
4. Interval : same as Datetime
4. Varchar :MOD(collength,256) + 1
Rudy