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://cloverhound.wordpress.com/2015/04/21/finding-all-the-tables-and-columns-on-an-cisco-informix-db/

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

https://community.cisco.com/t5/collaboration-voice-and-video/shallow-dive-into-call-manager-sql-database/ta-p/3165901