We had an issue with the Database Manager on our 8.3.6 Infor system where the Database Manager would start to open all of the tables but at the last table, it would hang and eventually crash the Administrator or the Architect. The tables would open correctly in SQL Management Studio so we felt certain the database was not corrupted. I have seen groups hang up the client if a recursive join is created while linking two tables so we decided to look for recursive joins.
We used the following script.
SELECT ‘Recursive joins’ as IssueType ,’JoinId: ‘ + J1.JoinID +’ FromTable.Field: ‘ + J1.FROMTABLE +’P’+ J1.FROMFIELD + ‘ TOTable.Field: ‘+ J1.TOTABLE +’.’+ J1.TOFIELD as ‘Result’
FROM
SYSDBA.JOINDATA J1, SYSDBA.JOINDATA J2
WHERE
J1.FROMTABLE = J2.TOTABLE
AND J1.TOTABLE = J2.FROMTABLE
AND J1.FROMFIELD = J2.TOFIELD
AND J1.TOFIELD = J2.FROMFIELD
Union
–Checks for bad joins
select ‘Bad join’ as IssueType , ‘JoinId: ‘+j.JoinID + ‘ From: ‘+j.FromTable+’.’+j.FromField + ‘ TO: ‘+ j.ToTable+’.’+j.ToField as Result
from sysdba.joindata j
left outer join sysobjects so1 on j.fromtable = so1.name
left outer join sysobjects so2 on j.totable = so2.name
where (so1.name is null or so2.name is null) and j.fromtable <>’*’
These joins were returned.
Recursive joins JoinId: j6UJ9A000075 FromTable.Field: LEADPLEADID TOTable.Field: LEAD_EXT.LEADID
Recursive joins JoinId: JSYST0000051 FromTable.Field: ACCOUNTPSECCODEID TOTable.Field: SECCODE.SECCODEID
Recursive joins JoinId: Q6UJ9A001WNA FromTable.Field: LEAD_EXTPLEADID TOTable.Field: LEAD.LEADID
Recursive joins JoinId: Q6UJ9A02958B FromTable.Field: SECCODEPSECCODEID TOTable.Field: ACCOUNT.SECCODEID
The first two joins appear to be system joins so we wanted to leave those, but the last two were user created and likely the source of the crash.
We used the following script to clear out the last two joins and the Database Manager opened without issues.
delete from sysdba.joindata where JOINID in (‘Q6UJ9A001WNA’,’Q6UJ9A02958B’)
