Extending DDDAUDIT functionality
来源:互联网 发布:bt下载 mac版 编辑:程序博客网 时间:2024/05/22 09:19
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 theVIEWS-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 10155 times by 2249 visitors
- Extending DDDAUDIT functionality
- Extending page
- Extending Air
- Extending R
- Extending the DataGrid Control
- Extending ColdFusion @ JDJ
- Extending and Embedding PHP
- Extending xdc.runtime
- Extending Adobe AIR
- Extending Adobe AIR
- Extending linear models
- Extending Adobe AIR(中英对照)
- Extending maya with C#
- python extending with c++
- Create Blocks ~ Extending Ardublockly
- RactiveJS-08 Extending Ractive
- Highlights of Cygwin Functionality
- function and functionality
- 为什么V8引擎这么快?
- C#图像处理(各种旋转、改变大小、柔化、锐化、雾化、底片、浮雕、黑白、滤镜效果)
- 程序员看世界之多灾多难的渔民(4)
- s3c2440的内存管理机制
- github上的版本和本地版本冲突的解决方法
- Extending DDDAUDIT functionality
- qt中显示系统时间的两种方式
- (android控件)ListView的Item中设置按钮实现
- 【Java核心】
- 程序员必须知道的几个国外IT网站
- 鸟哥的 Linux 私房菜 -- 学习 bash shell
- 你若安好,便是晴天。
- c语言小知识点
- 不要再使用Mac自带的坑爹PackageMaker打包了,使用iceberg 让你省心省力,iceberg 打包工具使用说明