Extending DDDAUDIT functionality

Running the DDDAUDIT.SQR process is something every PeopleSoft administrator should do on a regular basis, because it detects flaws between objects in the database and the corresponding definitions in the PeopleSoft data dictionary.

In normal maintenance life however this process doesn’t run that often, so the output can be quite large. Also, when running a DDDAudit during upgrades, there can be a lot of records (TABLE-3 check) and views (VIEWS-2 check) that are obsolete in the new version thus have to be removed. These objects have to be removed manually, which can be a lot of work.

By adjusting the DDDAudit script a bit this work can be reduced a lot. The trick is to write a drop statement to a new file after the record or view that has to be deleted is printed to the output report.

I’ll illustrate it by adding the necessary code to the TABLE-3 check for a Microsoft SQL Server database. In the dddtable.sqc file (which is called from the dddaudit.sqr) add the following lines:

!———————————————————————-!
begin-procedure DDDTABLE-Table-in-DB-and-not-in-DD
!———————————————————————-!
move 0 to #rows
let $AuditDescr = ‘(TABLE-3) SQL Table defined in the Database ‘ || ‘and not found in the Application Designer:’

!– [Frank Kortekaas (LogicaCMG)-Send sql statements to file -Start]

let $FileName = ‘c:tempaz_drop_table3.sql’

open $FileName as 1 for-writing record=100:vary

write 1 from ‘– Frank Kortekaas (LogicaCMG)’

write 1 from ‘–Fix DDDAUDIT TABLE-3 errors by running this script’

write 1 from ‘–’

!– [Frank Kortekaas(LogicaCMG)- end sql statements to file -End]

do MSTabChk

!– [Frank Kortekaas (LogicaCMG)-Send sql statements to file-Start]

write 1 from ‘– EOF’

close 1

!– [Frank Kortekaas (LogicaCMG)-Send sql statements to file-End]

do Print-Audit-Result

do Commit-Transaction

end-procedure

!———————————————————————-!

begin-procedure MSTabChk

!———————————————————————-!

begin-Select on-Error=Rpt-SQL-Error

convert(varchar(128),A.NAME) &Table2_SQLTable

move &Table2_SQLTable to $PSTabName

LET $IsRecPrsnt = ‘N’

LET $PSTabPrefix = SUBSTR($PSTabName,1,3)

if $PSTabPrefix = ‘PS_’

LET $PSColName = SUBSTR($PSTabName, 4,30)

do FindMSTab

if $IsRecPrsnt = ‘N’

do FindMSTmpTab

if $IsRecPrsnt = ‘N’

LET $Tmp1 = substr($PSColName, 1, length(rtrim($PSColName, ‘ ‘)) – 1)

LET $PSColName = $Tmp1

do FindMSTmpTab

if $IsRecPrsnt = ‘N’

LET $Tmp1 = substr($PSColName, 1, length(rtrim($PSColName, ‘ ‘)) – 1)

LET $PSColName = $Tmp1

do FindMSTmpTab

if $IsRecPrsnt = ‘N’

do Table2-Page-Control

print &Table2_SQLTable (+1,{C_One})

!– [ Frank Kortekaas (LogicaCMG) - Send sql statements to file - Start ]

let $SQLStatement = ‘drop table ‘ || &Table2_SQLTable || ‘;’

write 1 from $SQLStatement

!– [ Frank Kortekaas (LogicaCMG) - Send sql statements to file - End ]

end-if

end-if

end-if

end-if

else

LET $PSColName = $PSTabName

do FindMSTab

if $ISRecPrsnt = ‘N’

do Table2-Page-Control

print &Table2_SQLTable (+1,{C_One})

!– [ Frank Kortekaas (LogicaCMG) - Send sql statements to file - Start ]

let $SQLStatement = ‘drop table ‘ || &Table2_SQLTable || ‘;’

write 1 from $SQLStatement

!– [ Frank Kortekaas (LogicaCMG) - Send sql statements to file - End ]

end-if

end-if

FROM SYSOBJECTS A

WHERE A.TYPE = ‘U’ and A.NAME like ‘PS%’

ORDER BY A.NAME

end-SELECT

end-procedure

In this case for every table found in the TABLE-3 check a drop statement is written to the file c:tempaz_drop_table3.sql. This file will look like this:

– Frank Kortekaas (LogicaCMG)

– Fix DDDAUDIT TABLE-3 errors by running this script

–

drop table PSRECFIELD_TMP;

drop table PSROLEDEFNLG81;

drop table PSTIMEZONELANG2;

drop table PSX_PAGEACTION;

drop table PS_ACCMOTPN_TEO1;

drop table PS_ACCMOTPN_TEO2;

drop table PS_ACCMOTPN_TEO3;

drop table PS_ACCMOTPN_TEOA;

etc…

The only thing you have to do now is run this script. You can also add code like this to the VIEWS-2 check in the dddviews.sqc file (or any check you like) to drop views defined in the database but not in de Application Designer.

 Viewed 2510 times by 998 visitors

Posted by: Frank Kortekaas (8 post(s)).