indices on all the tables

[J_J]

Hello All,

Is there a ube or process by which we can verify that all the index which are defined in JDE actually exist in the RDBMS. I have been on client where they have done the instalaltion and for some reason index were not generated at all / partially. I would like to know if there is a process by which we can confirm if all the indices exist...

Thanks in advance for all your help

JJ

 

[kastanek]

You can try R9698713 in PROOF mode. We had table and index issues and this
told us how out of whack our tables were. It compared the table structure
to the source to verify if the tables and indices were correct. I'm not
sure if it'll tell you whether or not they exist.

I found this UBE by searching in BV for Object=R9* and Description=*ndex*
(QBE is case sensitive so I always leave of the first letter).

Good luck.

Gerald.





J_J
<[email protected] To: [email protected]
om> cc:
Sent by: Subject: indices on all the tables
jdelist-bounces@j
delist.com


06/10/2004 10:08
AM
Please respond to
JDELIST One World
/ XE Discussions





Hello All,Is there a ube or process by which we can verify that all the
index which are defined in JDE actually exist in the RDBMS. I have been on
client where they have done the instalaltion and for some reason index were
not generated at all / partially. I would like to know if there is a
process by which we can confirm if all the indices exist...Thanks in
advance for all your helpJJ
--------------------------
To view this thread, go to:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=73739

This is the JDELIST One World / XE Mailing List. To stop receiving these
messages, login to http://www.jdelist.com/forums, click Control Panel,
then click Edit by "Subscribe / Unsubscribe from receiving board posts by
email, change message notifications, etc." and adjust your subscription
preferences. JDEList is not affiliated with JDEdwards®

 

[jim_smith]

Double J,

I have done this before using a SQL statement. It was for an AS/400 installation. All you have to do is simply compare the indexes from the database system table (qsys2/sysindexes for AS/400 or ..sysindexes for SQL Server, not sure about Oracle) with those found in the index definitions table (F98712) in OW. The F98712 in found in your Central Objects library/database.

It would be easier to provide you with a sample SQL script if you would provide a proper signature telling the list about your platform specifics.

Good luck,

 

[DRGJDE]

Jim
If you would be so kind, I'd like to see your sample script. We are running JDE on SQL 2000.
our CRP database is JDE_CRP and the central tables are in JDE_CRPB733. I've often wondered if all tables have the indexes that are defined for them.
Could you email the script to me?

thanks

 

[rhunt]

Jim,

Do you know a similar easy way to find out if a column is indexed? For instance, I am trying to find all/any indexes that include MCRP24 from F0006...built through OneWorld or the DBMS.

This is for AS400.

Thanks

Ryan Hunt

 

[jim_smith]

Ryan,

since we are no longer on the 400 platform, the following statement is from memory:

SELECT

*

FROM qsys2.syskeys INNER JOIN qsys2.sysindexes ON qsys2.sysindexes.index_name = qsys2.syskeys.index_name

WHERE qsys2.sysindexes.table_name = 'F0006'
AND qsys2.syskeys.index_schema = 'PRODDTA'
AND qsys2.syskeys.column_name = 'MCRP24'

Hope this helps,

 

[rhunt]

Jim, that's a pretty good memory you've got there.

Thanks

Ryan

 

[jim_smith]

David,

the following script will only work if all your databases (crp and central objects) reside on the same sever, which is my scenario. If not, then you will have to make it a two step process involving global temporary tables. Note: you will have to modify the script to fit your setup (change the CRP database to match yours and change the central objects database to match yours).

Good luck to you,

--
-- File : Find missing indexes.sql
-- Purpose: To find indexes in OneWorld that are not generated in the database
-- Author : Jim Smith ([email protected])
--

--
-- Select database
--

USE jde_crp
GO

SET NOCOUNT ON

DECLARE @OW_Table_Name VARCHAR(20)
DECLARE @OW_Index_Name VARCHAR(20)
DECLARE @OW_Index_ID NUMERIC
DECLARE @filler VARCHAR(20)

--
-- Create temporary table to store user defined table names
--

--CREATE TABLE #TempTable (name VARCHAR(255) COLLATE Latin1_General_CI_AI PRIMARY KEY)

--
-- NOTE: Notice that there is a collation attribute specified in the above statement
-- If your Central Objects database has the same collation as your target database
-- then simply use the statement below. However, if for some reason there is a
-- collation mismatch between both databases, then modify the above statement to
-- match your target database.
--

CREATE TABLE #TempTable (name VARCHAR(255) PRIMARY KEY)

INSERT INTO #temptable SELECT name FROM ..sysobjects WHERE xtype = 'U'

--
-- For each OneWorld index defined in the Primary Index Header table (F98712),
-- verify if it has been generated in the database selected in the beginning of the
-- script.
--

DECLARE OW_Index_Csr CURSOR FORWARD_ONLY FOR
SELECT tpobnm, tpinid FROM jde_dv7333.dv7333.f98712 WHERE tpobnm IN (SELECT name FROM #TempTable) ORDER BY tpobnm
FOR READ ONLY

OPEN OW_Index_Csr
FETCH NEXT FROM OW_Index_Csr INTO @OW_Table_Name, @OW_Index_ID
WHILE (@@FETCH_STATUS = 0) BEGIN
IF(@OW_Index_ID = 1)
SET @OW_Index_Name = RTRIM(@OW_Table_Name) + '_PK'
ELSE
SET @OW_Index_Name = RTRIM(@OW_Table_Name) + '_' + CAST(@OW_Index_ID AS VARCHAR(3))
SET @filler = ''
SELECT @filler = name FROM jde_crp..sysindexes WHERE name = @OW_Index_Name
IF (@filler = '')
PRINT @OW_Index_Name

FETCH NEXT FROM OW_Index_Csr INTO @OW_Table_Name, @OW_Index_ID
END
CLOSE OW_Index_Csr
DEALLOCATE OW_Index_Csr
DROP TABLE #TempTable

GO

 

[DRGJDE]

Thank you Jim
It turns out that there were a couple of indexes defined but not in the database. I did have to modify one statement as we had the same (custom) table name with different owners
(INSERT INTO #temptable SELECT distinct name FROM ..sysobjects WHERE xtype = 'U' )
.
I tip my hat to you.

dave

 

[bknowles]

I made a slight change to your script as tpprmf is what
determines if an index is a primary key which isn't always tpinid = 1.

-- File : Find missing indexes.sql
-- Purpose: To find indexes in OneWorld that are not generated in the database
-- Author : Jim Smith ([email protected])
--

--
-- Select database
--

USE jde_crp
GO

SET NOCOUNT ON

DECLARE @OW_Table_Name VARCHAR(20)
DECLARE @OW_Index_Name VARCHAR(20)
DECLARE @OW_Index_ID NUMERIC
DECLARE @OW_Primary_Key CHAR(1)
DECLARE @filler VARCHAR(20)

--
-- Create temporary table to store user defined table names
--

--CREATE TABLE #TempTable (name VARCHAR(255) COLLATE Latin1_General_CI_AI PRIMARY KEY)

--
-- NOTE: Notice that there is a collation attribute specified in the above statement
-- If your Central Objects database has the same collation as your target database
-- then simply use the statement below. However, if for some reason there is a
-- collation mismatch between both databases, then modify the above statement to
-- match your target database.
--

CREATE TABLE #TempTable (name VARCHAR(255) PRIMARY KEY)

INSERT INTO #temptable SELECT DISTINCT name FROM ..sysobjects WHERE xtype = 'U'

--
-- For each OneWorld index defined in the Primary Index Header table (F98712),
-- verify if it has been generated in the database selected in the beginning of the
-- script.
--

DECLARE OW_Index_Csr CURSOR FORWARD_ONLY FOR
SELECT tpobnm, tpinid, tpprmf FROM jde_py7334.py7334.f98712 WHERE tpobnm IN (SELECT name FROM #TempTable) ORDER BY tpobnm
FOR READ ONLY

OPEN OW_Index_Csr
FETCH NEXT FROM OW_Index_Csr INTO @OW_Table_Name, @OW_Index_ID, @OW_Primary_Key
WHILE (@@FETCH_STATUS = 0) BEGIN
--IF(@OW_Index_ID = 1)
IF(@OW_Primary_Key = '1')
SET @OW_Index_Name = RTRIM(@OW_Table_Name) + '_PK'
ELSE
SET @OW_Index_Name = RTRIM(@OW_Table_Name) + '_' + CAST(@OW_Index_ID AS VARCHAR(3))
SET @filler = ''
SELECT @filler = name FROM jde_crp..sysindexes WHERE name = @OW_Index_Name
IF (@filler = '')
PRINT @OW_Index_Name

FETCH NEXT FROM OW_Index_Csr INTO @OW_Table_Name, @OW_Index_ID, @OW_Primary_Key
END
CLOSE OW_Index_Csr
DEALLOCATE OW_Index_Csr
DROP TABLE #TempTable