Select Needed to Retrieve SuperDescriptor and its column components from CONNX

, , , , ,
1

Our developers need the ability to identify the columns that are components of a superdescriptor. They want to be able to do a select against the connx schema to list all the superdescriptors of a table together with all of the components columns that made up each of the super.

I see table_indexes and syscnxStatistics but none would allow me to provide the information that the developers need.

2

This information is available in the data dictionary viewer utility (not to be confused with the data dictionary administrator). Please have the developer use this to view the superdescriptors and all the columns that make up each superdescriptor.

3

Larry,

Is it possible to address this via SQL? We have 100+ developers and we rather not have them each of them install the CDD viewer tool if possible.

Thanks,
Min

4

Hi!

Maybe this is a first step to solve your request:

The information_schema holds two tables:

  • INFORMATION_SCHEMA.TABLE_INDEXES
  • INFORMATION_SCHEMA.KEY_COLUMN_USAGE

I tried to get the information for this SPD:

DEFINITION-VIW:FD-BELEG-(0,125) -------------
COMMAND===>
T L DB Name
1 AO D-BETRAG

  •    -------- SOURCE FIELD(S) -------

  •    KUNDENNUMMER(1-4)

  •    TEILNEHMER(1-4)

  •    BETRAG(1-6)

  •    BUCHUNGSJAHR(1-4)

  •    BELEGKREIS(1-4)

  •    BELEGNUMMER-EXT(1-4)

  •    FAKTURASTELLENNUMMER(1-3)

using this sql

SELECT

TIDX.*

, KCUS.CONSTRAINT_CATALOG
, KCUS.CONSTRAINT_SCHEMA
, KCUS.CONSTRAINT_NAME
, KCUS.TABLE_CATALOG
, KCUS.TABLE_SCHEMA
, KCUS.TABLE_NAME
, KCUS.ORDINAL_POSITION
, KCUS.COLUMN_NAME

FROM
INFORMATION_SCHEMA.TABLE_INDEXES TIDX
,INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCUS

WHERE
TIDX.TABLE_CATALOG = ‘ADA201’
AND TIDX.TABLE_SCHEMA = ‘FD’
AND TIDX.TABLE_NAME = ‘BELEG’
AND TIDX.SHORT_NAME = ‘AO’
*
AND KCUS.CONSTRAINT_CATALOG = TIDX.TABLE_CATALOG
AND KCUS.CONSTRAINT_SCHEMA = TIDX.TABLE_SCHEMA
AND KCUS.TABLE_NAME = TIDX.TABLE_NAME

ORDER BY ORDINAL_POSITION

I was able to get all involved columns, but I get more records than expected.
Maybe the problem is that we do not import SPDs as named columns in the CDD.
I guess this is the reason that the column KCUS.CONSTRAINT_NAME holds no values in our environment.

Larry may give an advice whether the retrieval of the information_schema is an alternative approach.

cu Christian

5

This query returns all of the key columns

SELECT *
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

It does not display the super descriptor name however, the only way to see that is in the data dictionary viewer.