Data Lake
3. Importar ou Exportar
3.3. Extração metadados Informix
select TABLE_INTERNALNAME,
TABLE_NAME,
TABLE_COMMENT,
TABLE_TAGS,
COLUMN_INTERNALNAME,
COLUMN_NAME,
COLUMN_COMMENT,
COLUMN_TAGS,
COLUMN_DATATYPE_ID,
CASE WHEN COLUMN_DATATYPE_ID = 4 THEN TRUNC(COLUMN_LENGTH/256)
WHEN COLUMN_DATATYPE_ID = 1 THEN COLUMN_LENGTH
ELSE 0 END AS COLUMN_LENGTH,
CASE WHEN COLUMN_DATATYPE_ID = 4 AND MOD(COLUMN_LENGTH,256) <> 255 THEN MOD(COLUMN_LENGTH,256)
ELSE 0 END AS COLUMN_PRECISION,
COLUMN_NULLABLE
from (select 'IFX_0000_SIS_V1_' || UPPER(tabname) AS TABLE_INTERNALNAME
,'IFX_0000_SIS_V1_' || UPPER(tabname) AS TABLE_NAME
,'' AS TABLE_COMMENT
,'' AS TABLE_TAGS
,colname as COLUMN_INTERNALNAME
,colname as COLUMN_NAME
,'' as COLUMN_COMMENT
,'' AS COLUMN_TAGS
,CASE WHEN MOD(coltype,256)=0 THEN 1
WHEN MOD(coltype,256)=1 THEN 2
WHEN MOD(coltype,256)=2 THEN 2
WHEN MOD(coltype,256)=3 THEN 4
WHEN MOD(coltype,256)=4 THEN 4
WHEN MOD(coltype,256)=5 THEN 4
WHEN MOD(coltype,256)=6 THEN 2
WHEN MOD(coltype,256)=7 THEN 7
WHEN MOD(coltype,256)=8 THEN 4
WHEN MOD(coltype,256)=10 THEN 3
WHEN MOD(coltype,256)=11 THEN 2
WHEN MOD(coltype,256)=12 THEN 1
WHEN MOD(coltype,256)=13 THEN 1
WHEN MOD(coltype,256)=14 THEN 1
WHEN MOD(coltype,256)=15 THEN 1
WHEN MOD(coltype,256)=16 THEN 1
WHEN MOD(coltype,256)=17 THEN 2
WHEN MOD(coltype,256)=40 THEN 1
WHEN MOD(coltype,256)=41 THEN 5
WHEN MOD(coltype,256)=45 THEN 5
WHEN MOD(coltype,256)=52 THEN 9
WHEN MOD(coltype,256)=53 THEN 1
END AS COLUMN_DATATYPE_ID
,collength AS COLUMN_LENGTH
,CASE WHEN coltype >= 256 THEN 0
ELSE 1 END AS COLUMN_NULLABLE
from informix.syscolumns as c join informix.systables as t on t.tabid = c.tabid
WHERE t.tabname in ('nf_etiq_emitidas'));