[JDE] Data Dictionary – Searching For Similar Data Types

Purpose

Two queries for extracting information from the JDE Data Dictionary.  One is used to export an entire JDE Data Dictionary with all of the useful properties by Item.  The second is used to search the Data Dictionary for an Item and retrieve a list of all other Items that are fully compatible.  It’s very useful when using Table Designer.

About

The Data Dictionary is one of the most basic building blocks of JDE development.  It is a collection of well-structured data types that define all sorts of behavior, from underlying data types & precision (Numeric, Date, String, etc) to default values, display formatting & advanced data validation rules.

One problem with the Data Dictionary is much like the rest of JDE – the information is well-structured, but it’s scattered across multiple screens that are difficult to search. There is no single unified view of critical information built into the system, so I wrote a query to extract it.  I also exported these results to Excel, which you can download here.

Another common problem occurs during Table Design, where you can only use each Data Dictionary Item once.  Designers frequently need to find compatible Data Dictionary Items, so I wrote a query to quickly (and accurately) find suitable substitutes.

Query 1 – Export All Data Dictionary Items & Properties [Download results in Excel format]
WITH DD AS (
	SELECT
		FROWDI as Name,
			
		FRDTAI as Alias,
			
		FROWTP as Type,
		UDCOWTP.DRDL01 as [TypeDesc],
		FRCLAS as Class,
			
		FRDTAS as Size,                        
			
		FRDTAD as FileDecimals,
		CASE WHEN FRCDEC = '' THEN 0 ELSE FRCDEC END as DisplayDecimals,
		FRNSY as NextNumber,
		CASE WHEN FRNSY = '' THEN '' ELSE CAST(FRNNIX AS VARCHAR(2)) END as [NNIndex],
			
		FROWER as EditRule,
		FROER1 as Rule1,
		FROER2 as Rule2,
			
		FROWDR as DisplayRuleType,
		COALESCE(UDCOWDR.DRDL01,'') as [DisplayRuleTypeDesc],

	FRODR1 as [CodeDisplayRule],
	CASE WHEN FRODR1 = '' THEN '' ELSE UDCODR1.DRDL01 END AS DispRuleDesc,
	
	FRDVAL as DefaultValue,
	CASE WHEN FRUPER = '' THEN 'N' ELSE FRUPER END as UpperCaseOnly,
	CASE WHEN FRALBK = '' THEN 'N' ELSE FRALBK END as AllowBlanks,
	CASE WHEN FRAUIN = '' THEN 'N' ELSE FRAUIN END as AutoInclude,
	CASE WHEN FRPLFG = '' THEN 'N' ELSE FRPLFG END as DoNotTotal
	
	FROM JDE910.DD910.F9210
		LEFT JOIN TESTCTL.F0005 UDCODR1 ON FRODR1 = RTRIM(LTRIM(UDCODR1.DRKY)) AND UDCODR1.DRSY = '98' AND UDCODR1.DRRT = 'EC'
		LEFT JOIN TESTCTL.F0005 UDCOWTP ON FROWTP = RTRIM(LTRIM(UDCOWTP.DRKY)) AND UDCOWTP.DRSY = 'H98' AND UDCOWTP.DRRT = 'DT'
		LEFT JOIN TESTCTL.F0005 UDCOWDR ON FROWDR = RTRIM(LTRIM(UDCOWDR.DRKY)) AND UDCOWDR.DRSY = 'H98' AND UDCOWDR.DRRT = 'DR'
)
SELECT
*
FROM DD
Query 2 – Search for “fully compatible” Data Dictionary Items
DECLARE @SearchAlias NVARCHAR(10) = 'TRDJ'; -- Set to Data Item to search by

With DataTypes AS (
	SELECT
		FROWDI as Name,
			
		FRDTAI as Alias,
			
		FROWTP as Type,
		UDCOWTP.DRDL01 as [TypeDesc],
		FRCLAS as Class,
			
		FRDTAS as Size,                        
			
		FRDTAD as FileDecimals,
		CASE WHEN FRCDEC = '' THEN 0 ELSE FRCDEC END as DisplayDecimals,
		FRNSY as NextNumber,
		CASE WHEN FRNSY = '' THEN '' ELSE CAST(FRNNIX AS VARCHAR(2)) END as [NNIndex],
			
		FROWER as EditRule,
		FROER1 as Rule1,
		FROER2 as Rule2,
			
		FROWDR as DisplayRuleType,
		UDCOWDR.DRDL01 as [DisplayRuleTypeDesc],

	
		FRODR1 as [CodeDisplayRule],
		CASE WHEN FRODR1 = '' THEN '' ELSE UDCODR1.DRDL01 END AS DispRuleDesc,
			
		FRDVAL as DefaultValue,
		CASE WHEN FRUPER = '' THEN 'N' ELSE FRUPER END as UpperCaseOnly,
		CASE WHEN FRALBK = '' THEN 'N' ELSE FRALBK END as AllowBlanks,
		CASE WHEN FRAUIN = '' THEN 'N' ELSE FRAUIN END as AutoInclude,
		CASE WHEN FRPLFG = '' THEN 'N' ELSE FRPLFG END as DoNotTotal
		
	FROM JDE910.DD910.F9210
		LEFT JOIN TESTCTL.F0005 UDCODR1 ON FRODR1 = RTRIM(LTRIM(UDCODR1.DRKY)) AND UDCODR1.DRSY = '98' AND UDCODR1.DRRT = 'EC'
		LEFT JOIN TESTCTL.F0005 UDCOWTP ON FROWTP = RTRIM(LTRIM(UDCOWTP.DRKY)) AND UDCOWTP.DRSY = 'H98' AND UDCOWTP.DRRT = 'DT'
		LEFT JOIN TESTCTL.F0005 UDCOWDR ON FROWDR = RTRIM(LTRIM(UDCOWDR.DRKY)) AND UDCOWDR.DRSY = 'H98' AND UDCOWDR.DRRT = 'DR'
)
SELECT Two.* 
FROM DataTypes One LEFT JOIN DataTypes Two 
	ON        One.Type = Two.Type 
	AND One.Size <= Two.Size 
	AND One.Class = Two.Class 
	AND One.DisplayDecimals = Two.DisplayDecimals 
	AND One.FileDecimals = Two.FileDecimals
	AND One.NextNumber = Two.NextNumber
	AND One.NNIndex = Two.NNIndex
	AND One.EditRule = Two.EditRule 
	AND One.Rule1 = Two.Rule1 
	AND One.Rule2 = Two.Rule2 
	AND One.DisplayRuleType = Two.DisplayRuleType
	AND One.[CodeDisplayRule] = Two.[CodeDisplayRule]
	AND One.DefaultValue = Two.DefaultValue
	AND One.UpperCaseOnly = Two.UpperCaseOnly
	AND One.AllowBlanks = Two.AllowBlanks
	AND One.AutoInclude = Two.AutoInclude
	AND One.DoNotTotal = Two.DoNotTotal
WHERE One.Alias = @SearchAlias
ORDER BY Two.Alias, Two.Size

Comments 3

  • Query 3: Data Dictionary Dump (improved June 2018 edition)

    Includes Display Name, Localized Display Name, UDC Header Description (when appropriate), Alias and Table Count, which lets you know how many times an Alias is used in any of your JDE tables.


    WITH DD AS (
    SELECT
    DDDesc.FRDSCA as [Display Name],

    FROWDI as Name,

    COALESCE(RTRIM(DTDL01),'') as [UDC Desc],

    DD.FRDTAI as Alias,

    FROWTP as Type,
    UDCOWTP.DRDL01 as [TypeDesc],
    FRCLAS as Class,

    FRDTAS as Size,

    FRDTAD as FileDecimals,
    CASE WHEN FRCDEC = '' THEN 0 ELSE FRCDEC END as DisplayDecimals,
    FRNSY as NextNumber,
    CASE WHEN FRNSY = '' THEN '' ELSE CAST(FRNNIX AS VARCHAR(2)) END as [NNIndex],

    FROWER as EditRule,
    FROER1 as Rule1,
    FROER2 as Rule2,

    FROWDR as DisplayRuleType,
    COALESCE(UDCOWDR.DRDL01,'') as [DisplayRuleTypeDesc],

    FRODR1 as [CodeDisplayRule],
    CASE WHEN FRODR1 = '' THEN '' ELSE UDCODR1.DRDL01 END AS DispRuleDesc,

    FRDVAL as DefaultValue,
    CASE WHEN FRUPER = '' THEN 'N' ELSE FRUPER END as UpperCaseOnly,
    CASE WHEN FRALBK = '' THEN 'N' ELSE FRALBK END as AllowBlanks,
    CASE WHEN FRAUIN = '' THEN 'N' ELSE FRAUIN END as AutoInclude,
    CASE WHEN FRPLFG = '' THEN 'N' ELSE FRPLFG END as DoNotTotal

    FROM JDE910.DD910.F9210 DD -- FR
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCODR1 ON FRODR1 = RTRIM(LTRIM(UDCODR1.DRKY)) AND UDCODR1.DRSY = '98' AND UDCODR1.DRRT = 'EC'
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCOWTP ON FROWTP = RTRIM(LTRIM(UDCOWTP.DRKY)) AND UDCOWTP.DRSY = 'H98' AND UDCOWTP.DRRT = 'DT'
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCOWDR ON FROWDR = RTRIM(LTRIM(UDCOWDR.DRKY)) AND UDCOWDR.DRSY = 'H98' AND UDCOWDR.DRRT = 'DR'
    LEFT JOIN JDE910.DD910.F9203 DDDesc on DD.FRDTAI = DDDesc.FRDTAI AND DDDesc.FRLNGP = '' AND DDDesc.FRSYR = '' AND DDDesc.FRSCRN = ''
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0004 UDCH ON FROER1 = DTSY AND FROER2 = DTRT
    ),Usage as (
    SELECT TDOBND, COUNT(*) as [Count] FROM JDE_PD910.PD910.F98711 GROUP BY TDOBND
    )
    SELECT
    DD.*, COALESCE(Usage.[Count], 0) as [Table Count]
    FROM DD LEFT JOIN Usage ON Alias = Usage.TDOBND

    Reply

  • Query 4: Dump All JDE Tables and Column Definitions

    Used to create an “inventory” of all Database Tables and Columns for a Business Intelligence project.

    Lists every column in every table, including Database, Schema, Table + Description, Primary Key + 2nd & 3rd Unique Index Keys, Column Description and Data Dictionary information (Data Type, Precision, Edit Rules, UDC Header Info, etc)


    WITH
    Prefixes as (
    SELECT DISTINCT TDOBNM, SUBSTRING(TDSQLC,1,2) as [Prefix] FROM JDE_PD910.PD910.F98711
    -- BETTER: Use JDE910.OL910.F9860 PFX column
    ),
    DefaultOCM as (
    select
    rtrim(omdatb2) as [Database],
    rtrim(omoown) as [Schema],
    rtrim(omobnm) as [Table]
    from
    jde910.sy910.f986101 ocm left join jde910.sy910.f98611 ds on ocm.omdatp = ds.omdatp
    where
    ocm.omenhv = 'PD910'
    and OMSTSO = 'AV'
    and OMUGRP = '*PUBLIC'
    and OMFUNO = 'TBLE'
    and omobnm = 'DEFAULT'
    ),
    OCMs as (
    select
    CASE WHEN omdatb2 IS NULL THEN DefaultOCM.[Database] ELSE rtrim(omdatb2) END as [Database],
    CASE WHEN omoown IS NULL THEN DefaultOCM.[Schema] ELSE rtrim(omoown) END as [Schema],
    CASE WHEN omobnm IS NULL THEN Prefixes.TDOBNM ELSE rtrim(omobnm) END as [Table],
    Prefixes.Prefix
    from
    Prefixes left join jde910.sy910.f986101 ocm
    on Prefixes.TDOBNM = ocm.omobnm
    and ocm.omenhv = 'PD910'
    and OMSTSO = 'AV'
    and OMUGRP = '*PUBLIC'
    and OMFUNO = 'TBLE'
    left join jde910.sy910.f98611 ds on ocm.omdatp = ds.omdatp
    cross apply DefaultOCM
    ),
    DD AS (
    SELECT
    DDDesc.FRDSCA as [Display Name],

    FROWDI as Name,

    RTRIM(DTDL01) as [UDC Desc],

    DD.FRDTAI as Alias,

    FROWTP as Type,
    UDCOWTP.DRDL01 as [TypeDesc],
    FRCLAS as Class,

    FRDTAS as Size,

    FRDTAD as FileDecimals,
    CASE WHEN FRCDEC = '' THEN 0 ELSE FRCDEC END as DisplayDecimals,
    FRNSY as NextNumber,
    CASE WHEN FRNSY = '' THEN '' ELSE CAST(FRNNIX AS VARCHAR(2)) END as [NNIndex],

    FROWER as EditRule,
    FROER1 as Rule1,
    FROER2 as Rule2,

    FROWDR as DisplayRuleType,
    COALESCE(UDCOWDR.DRDL01,'') as [DisplayRuleTypeDesc],

    FRODR1 as [CodeDisplayRule],
    CASE WHEN FRODR1 = '' THEN '' ELSE UDCODR1.DRDL01 END AS DispRuleDesc,

    FRDVAL as DefaultValue,
    CASE WHEN FRUPER = '' THEN 'N' ELSE FRUPER END as UpperCaseOnly,
    CASE WHEN FRALBK = '' THEN 'N' ELSE FRALBK END as AllowBlanks,
    CASE WHEN FRAUIN = '' THEN 'N' ELSE FRAUIN END as AutoInclude,
    CASE WHEN FRPLFG = '' THEN 'N' ELSE FRPLFG END as DoNotTotal

    FROM JDE910.DD910.F9210 DD -- FR
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCODR1 ON FRODR1 = RTRIM(LTRIM(UDCODR1.DRKY)) AND UDCODR1.DRSY = '98' AND UDCODR1.DRRT = 'EC'
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCOWTP ON FROWTP = RTRIM(LTRIM(UDCOWTP.DRKY)) AND UDCOWTP.DRSY = 'H98' AND UDCOWTP.DRRT = 'DT'
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0005 UDCOWDR ON FROWDR = RTRIM(LTRIM(UDCOWDR.DRKY)) AND UDCOWDR.DRSY = 'H98' AND UDCOWDR.DRRT = 'DR'
    LEFT JOIN JDE910.DD910.F9203 DDDesc on DD.FRDTAI = DDDesc.FRDTAI AND DDDesc.FRLNGP = '' AND DDDesc.FRSYR = '' AND DDDesc.FRSCRN = ''
    LEFT JOIN JDE_PRODUCTION.PRODCTL.F0004 UDCH ON FROER1 = DTSY AND FROER2 = DTRT
    ),
    Indexes as (
    SELECT TPOBNM as iTable, TLOBND as iAlias, DENSE_RANK() OVER (PARTITION BY TPOBNM ORDER BY TPINID) as indexNum
    FROM JDE_PD910.PD910.F98712 TP LEFT JOIN JDE_PD910.PD910.F98713 TL ON TPOBNM = TLOBNM AND TPINID = TLINID
    WHERE TPPRMF = 1 OR TPUNIQ = 1
    )
    SELECT
    [Database], [Schema], [Table],
    RTRIM(SIMD) as [Table Desc],
    RTRIM(TDSQLC) as [Column Name],
    CASE WHEN PK.indexNum IS NOT NULL THEN 'Y' ELSE '' END as [PK],
    CASE WHEN UK2.indexNum IS NOT NULL THEN 'Y' ELSE '' END as [UK2],
    CASE WHEN UK3.indexNum IS NOT NULL THEN 'Y' ELSE '' END as [UK3],
    RTRIM([TDOBND]) as [Alias],
    DD.[Display Name] as [Column Name],
    DD.[TypeDesc] as [Type],
    DD.[Size] as [Length],
    DD.FileDecimals,
    DD.DisplayDecimals,
    DD.EditRule,
    DD.Rule1,
    DD.Rule2,
    DD.[UDC Desc] as [UDC Name]
    FROM OCMs LEFT JOIN JDE_PD910.PD910.F98711 TD ON [Table] = TDOBNM
    LEFT JOIN JDE910.OL910.F9860 SI ON [Table] = SIOBNM
    LEFT JOIN DD ON DD.[alias] = [TDOBND]
    LEFT JOIN Indexes PK on [Table] = PK.iTable and [TDOBND] = PK.iAlias AND PK.indexNum = 1
    LEFT JOIN Indexes UK2 on [Table] = UK2.iTable and [TDOBND] = UK2.iAlias AND UK2.indexNum = 2
    LEFT JOIN Indexes UK3 on [Table] = UK3.iTable and [TDOBND] = UK3.iAlias AND UK3.indexNum = 3
    ORDER BY
    [Database],
    [Schema],
    [Table],
    CASE WHEN PK.indexNum IS NOT NULL THEN 1 ELSE 2 END,
    CASE WHEN UK2.indexNum IS NOT NULL THEN 1 ELSE 2 END,
    CASE WHEN UK3.indexNum IS NOT NULL THEN 1 ELSE 2 END,
    [TDPSEQ]

    Reply

  • Query 5: Dump JDE Tables + Primary/Unique Keys + Row Width + Record Count

    Caution: This is an intense query that will perform a SELECT COUNT(*) over every one of your JDE tables. It will return information useful for DBA’s and other people who are trying to “size up” your data set. This only works on SQL Server due to its use of sys.columns.


    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    CREATE TABLE #TableStats (
    [Database] NVARCHAR(20),
    [Schema] NVARCHAR(10),
    [Table] NVARCHAR(10),
    [Index] FLOAT,
    [Table Desc] NVARCHAR(60),
    [Prefix] NVARCHAR(4),
    [Key Fields] NVARCHAR(MAX),
    RowLength FLOAT,
    [RowCount] FLOAT,
    [SQLSelect] NVARCHAR(MAX),
    PRIMARY KEY ([Database],[Schema],[Table],[Index])
    );

    WITH UniqueIndexes as (
    SELECT
    RTRIM(TPOBNM) as [Table],
    TPINID as [Index],
    RTRIM(SIMD) as [Table Desc],
    RTRIM(SIPFX) as [Prefix]
    FROM
    JDE_PD910.PD910.F98712 TP -- Index Master
    LEFT JOIN JDE910.OL910.F9860 SI -- Object Master
    ON TPOBNM = SIOBNM
    WHERE TPPRMF = 1 OR TPUNIQ = 1
    ),
    DefaultOCM as (
    select
    rtrim(omdatb2) as [Database],
    rtrim(omoown) as [Schema],
    rtrim(omobnm) as [Table]
    from
    jde910.sy910.f986101 ocm left join jde910.sy910.f98611 ds on ocm.omdatp = ds.omdatp
    where
    ocm.omenhv = 'PD910'
    and OMSTSO = 'AV'
    and OMUGRP = '*PUBLIC'
    and OMFUNO = 'TBLE'
    and omobnm = 'DEFAULT'
    ),
    OCMs as (
    select
    CASE WHEN omdatb2 IS NULL THEN DefaultOCM.[Database] ELSE rtrim(omdatb2) END as [Database],
    CASE WHEN omoown IS NULL THEN DefaultOCM.[Schema] ELSE rtrim(omoown) END as [Schema],
    CASE WHEN omobnm IS NULL THEN UniqueIndexes.[Table] ELSE rtrim(omobnm) END as [Table],
    UniqueIndexes.[Table Desc],
    UniqueIndexes.[Index],
    UniqueIndexes.[Prefix]
    from
    UniqueIndexes left join jde910.sy910.f986101 ocm
    on UniqueIndexes.[Table] = ocm.omobnm
    and ocm.omenhv = 'PD910'
    and OMSTSO = 'AV'
    and OMUGRP = '*PUBLIC'
    and OMFUNO = 'TBLE'
    left join jde910.sy910.f98611 ds on ocm.omdatp = ds.omdatp
    cross apply DefaultOCM
    ),
    RowWidth as (
    SELECT
    object_name(object_id) as [Table],
    sum(max_length) as [RowLength],
    count(*) as [ColumnCount]
    FROM sys.columns
    group by object_id
    )
    INSERT INTO #TableStats ([Database],[Schema],[Table],[Index],[Table Desc],[Prefix],[Key Fields],[RowLength])
    SELECT
    OCMs.[Database],
    OCMs.[Schema],
    OCMs.[Table],
    OCMs.[Index],
    OCMs.[Table Desc],
    OCMs.[Prefix],
    STUFF(
    (SELECT ',' + Prefix + RTRIM(Sub.TLOBND) FROM JDE_PD910.PD910.F98713 Sub WHERE Sub.TLOBNM = OCMs.[Table] AND Sub.TLINID = OCMs.[Index] ORDER BY TLCMPI FOR XML PATH (''))
    ,1 ,1, ''
    ) as [Key Fields],
    RowWidth.RowLength
    FROM OCMs LEFT JOIN RowWidth on OCMs.[Table] = RowWidth.[Table];

    DECLARE @Database NVARCHAR(20);
    DECLARE @Schema NVARCHAR(10);
    DECLARE @Table NVARCHAR(10);

    DECLARE @ObjectName NVARCHAR(42);
    DECLARE @sqlCommand NVARCHAR(200);

    DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
    SELECT [Database], [Schema], [Table] FROM #TableStats;

    OPEN tableCursor;

    FETCH NEXT FROM tableCursor INTO @Database, @Schema, @Table;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SELECT @sqlCommand = N'
    UPDATE #TableStats SET [RowCount] = (SELECT COUNT(*) FROM ' + @Database + '.' + @Schema + '.' + @Table + ')
    WHERE [Database] = ''' + @Database + ''' AND [Schema] = ''' + @Schema + ''' AND [Table] = ''' + @Table + ''';'

    exec sp_executesql @sqlCommand;

    FETCH NEXT FROM tableCursor INTO @Database, @Schema, @Table;

    END

    CLOSE tableCursor;

    DEALLOCATE tableCursor;

    SELECT * FROM #TableStats;

    DROP TABLE #TableStats;

    Reply

Leave a Reply to brandon Cancel reply

Your email address will not be published. Required fields are marked *

➤