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
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
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]
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;