# Overview

It is often necessary or useful to analyze a specific table’s columns and their associated data types when constructing queries for CUCM, CUCxN, UCCX etc. However, if its an unfamiliar area of the db, a review of tables, columns and even their data types may be warranted. The following queries are useful for informix database analyses.

# Querying informix db

## Finding Tables

Listing all tables is achieved with the below. `tabid > 100`

filters out the informix system catalog:

SELECT tabname FROM "informix".systables WHERE tabid >= 100

Output:

tabname ================================ dblcnqueue dblschemaorder aarneighborhood aardialprefixmatrix alarmusertext availdialplan axlchangenotifyclient carrierselectprofile ccaprofile ccdhosteddngroup ccdhosteddn cdrmconfig cmcinfo codeclist confidentialaccesslevel confidentialaccesslevelmatrix corsdomain credentialpolicy devicemobilitygroup ... etc.

To inspect indivivdual tables, we use a join on `"informix".syscolumns`

. The below samples assume the CUCM db `device`

table is under inspection.

## Inspecting Individual Tables

SELECT tabname, colname FROM "informix".systables t, "informix".syscolumns c WHERE t.tabname = 'device' AND t.tabtype = 'T' AND t.tabid = c.tabid

Output:

tabname colname ======= ===================================== device pkid device name device description device tkmodel device tkdeviceprotocol device tkprotocolside device specialloadinformation device fkdevicepool device fkphonetemplate device fkcallingsearchspace device ctiid device tkclass device fkprocessnode device defaultdtmfcapability device fklocation device tkproduct device dialplanwizardgenid ... etc.

Wanting to review the column types is a more complex undertaking:

SELECT TRIM(t.tabname) table, TRIM(c.colname) table_dot_column, CASE WHEN MOD(coltype,256)=0 THEN 'CHAR' WHEN MOD(coltype,256)=1 THEN 'SMALLINT' WHEN MOD(coltype,256)=2 THEN 'INTEGER' WHEN MOD(coltype,256)=3 THEN 'FLOAT' WHEN MOD(coltype,256)=4 THEN 'SMALLFLOAT' WHEN MOD(coltype,256)=5 THEN 'DECIMAL' WHEN MOD(coltype,256)=6 THEN 'SERIAL' WHEN MOD(coltype,256)=7 THEN 'DATE' WHEN MOD(coltype,256)=8 THEN 'MONEY' WHEN MOD(coltype,256)=9 THEN 'NULL' WHEN MOD(coltype,256)=10 THEN 'DATETIME' WHEN MOD(coltype,256)=11 THEN 'BYTE' WHEN MOD(coltype,256)=12 THEN 'TEXT' WHEN MOD(coltype,256)=13 THEN 'VARCHAR' WHEN MOD(coltype,256)=14 THEN 'INTERVAL' WHEN MOD(coltype,256)=15 THEN 'NCHAR' WHEN MOD(coltype,256)=16 THEN 'NVARCHAR' WHEN MOD(coltype,256)=17 THEN 'INT8' WHEN MOD(coltype,256)=18 THEN 'SERIAL8' WHEN MOD(coltype,256)=19 THEN 'SET' WHEN MOD(coltype,256)=20 THEN 'MULTISET' WHEN MOD(coltype,256)=21 THEN 'LIST' WHEN MOD(coltype,256)=22 THEN 'ROW (unnamed)' WHEN MOD(coltype,256)=23 THEN 'COLLECTION' WHEN MOD(coltype,256)=40 THEN 'LVARCHAR fixed-length opaque types' WHEN MOD(coltype,256)=41 THEN 'BLOB, BOOLEAN, CLOB variable-length opaque types' WHEN MOD(coltype,256)=43 THEN 'LVARCHAR (client-side only)' WHEN MOD(coltype,256)=45 THEN 'BOOLEAN' WHEN MOD(coltype,256)=52 THEN 'BIGINT' WHEN MOD(coltype,256)=53 THEN 'BIGSERIAL' WHEN MOD(coltype,256)=2061 THEN 'IDSSECURITYLABEL' WHEN MOD(coltype,256)=4118 THEN 'ROW (named)' ELSE TO_CHAR(coltype) END AS Type, BITAND(coltype,256)=256 AS NotNull FROM "informix".systables AS t JOIN "informix".syscolumns AS c ON t.tabid = c.tabid WHERE t.tabtype = 'T' AND t.tabid >= 100 AND t.tabname = 'device' ORDER BY t.tabname, c.colno

Output:

table table_dot_column type notnull ====== ===================================== ================================================ ======= device pkid CHAR t device name VARCHAR t device description LVARCHAR fixed-length opaque types t device tkmodel INTEGER t device tkdeviceprotocol INTEGER t device tkprotocolside INTEGER t device specialloadinformation VARCHAR f device fkdevicepool CHAR f device fkphonetemplate CHAR f ... etc.

The complexity in the latter query is due to informix using bitwise arithmetic to determine nullability. < 256 are nullable, with 256 added to indicate "Not Null". This is accounted for in the above query.

# References

https://stackoverflow.com/questions/1380782/informix-sql-list-all-fields-tables

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.70.0/com.ibm.sqlr.doc/ids_sqr_025.htm