The DB2 UDB table column data glossary query returns the names of DB2 UDB tables which contain a column which matches the data characteristics specified in the WHERE clause.
A column in a DB2 UDB table may use a Shareable Data Element to specify its data characteristics, the characteristics may be specified locally or it may inherit the data characteristics of the matching column in a parent table if the column participates in a foreign key.
In order to locate all DB2 UDB tables which contain a column with certain characteristics, the query must include the attributes which define the column using a Shareable Data Element, defined locally or via the parent table.
View type: DAQDB2csTable
Modifications:
Change the search criteria of the data characteristic attributes in the SQL WHERE clause. The view type contains two sets of attributes which describe the data characteristics of the column.
For example, either e.typeCode or c.localDataTypeCode will contain the data type code for the column depending upon whether the column uses a Shareable Data Element or is locally defined. Each of the following attributes may be referenced either as shareable or local:
--typeCode
Possible values are:
0 Fixed binary
1 Packed decimal
2 Zoned decimal
3 Binary floating point
4 Decimal (or external) floating point
5 Bit string
6 Character string
7 DBCS (or graphic) string
8 Mixed SBCS/DBCS string
9 Date
10 Time
11 Timestamp
12 Index
13 Undefined
14 Rowid (Oracle)
15 MLSLable (Oracle)
--stringLength
The data length for string data types: bit string in bits, character
string in bytes, DBCS or graphic string in double bytes, and mixed
SBCS/DBCS string in bytes.
--numericPrecision
The data length for numeric data types: fixed binary in bits, packed
decimal in digits, zoned decimal in digits, binary floating point in
bits, and decimal floating point in digits of the mantissa.
--scale
The position of the decimal point for numeric data types in digits.
--asBitData
A boolean value indicating whether the character string is treated
as bit (binary) data.
--stringVaryingCode
A value indicating whether the string is of fixed length (1) or
variable length (2).
--isLOB
A boolean value indicating whether the character string is a large object.
#MODIFIED#
#VERSION#
#SQL STATEMENT#
--DA_DB2udbTableColumnData
SELECT
-- column uses Shareable Data Element
t.db2csTable,
td.tableDefinition,
td.columnName,
'Data Element' as sourceType,
e.dataElement as dataSource,
e.typeCode as typeCode,
e.stringLength as stringLength,
e.stringVaryingCode as stringVaryingCode,
e.asBitData as asBitData,
e.isLOB as isLOB,
e.numericPrecision as numericPrecision,
e.scale as numericScale
FROM
DAQDB2csTable t,
(t.usesTableDefinition) td,
(td.usesDataElement) e
WHERE
e.typeCode=6 AND
e.stringLength<20 AND
td.columnName <> ''
UNION
SELECT
-- column is defined by primary table column
-- primary table column uses a Shareable Data Element