f there's a way to automate an unpleasant task, I'm always the first to do it. So when I ended up in a position where I had to do PeopleSoft migrations, it didn't take long before I had a migration batch script up and running. I thought I'd share my script, and maybe you can find a way to improve on it.
Script to copy and build a project
As you probably know, Application Designer has a command-line interface (see https://financial.gmis.in.gov/fs_pbooks/eng/psbooks/tapd/book.htm) that allows you to migrate and build projects via the command line. You can also compare databases and import and export projects as files via the command line, but that's beyond what we're trying to accomplish here.
Here's the script: rem Filename: MigrateProject.cmd
setlocal
set DBTYPE=ORACLE
set SOURCEUSERID=BRENT
set SOURCEPWD=mysecretpassword
set SOURCEDB=HDEV
set TARGETUSERID=BRENT
set TARGETPWD=mysecretpassword
set TARGETDB=HTST
set PROJECTNAME=%1
rem Copy Project
N:\HR8_9\bin\client\winx86\PSIDE.EXE -HIDE -PJC %PROJECTNAME% -CT %DBTYPE% -CD %SOURCEDB% -CO %SOURCEUSERID% -CP %SOURCEPWD% -TD %TARGETDB% -TO %TARGETUSERID% -TP %TARGETPWD% -QUIET -LF C:\TEMP\COPY.LOG -CL 150 -AF 1 -DDL 1
rem Build Project
regedit -s "C:\Documents and Settings\bmartin1\My Documents\Scripts\RDMBuildSettings.reg" N:\HR8_9\bin\client\winx86\PSIDE.EXE -CT %DBTYPE% -CD %TARGETDB% -CO %TARGETUSERID% -CP %TARGETPWD% -PJB %PROJECTNAME% -HIDE -QUIET
- END
endlocal
And here's the contents of the registry entry file (RDMBuildSettings.reg) that it imports: Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\PeopleSoft\PeopleTools\Release8.40]
[HKEY_CURRENT_USER\Software\PeopleSoft\PeopleTools\Release8.40\RDM Build Settings]
"CreateTables"=dword:00000001
"CreateIndexes"=dword:00000001
"CreateViews"=dword:00000001
"CreateTrigger"=dword:00000001
"AlterTables"=dword:00000001
"ExecuteOption"=dword:00000002
"LogErrors"=dword:00000002
"LogToScript"=dword:00000001
"LogToWindow"=dword:00000000
"LogSettings"=dword:00000000
"AlterDropOption"=dword:00000001
"AlterTruncateOption"=dword:00000001
"TableOption"=dword:00000002
"ViewOption"=dword:00000001
"IndexOption"=dword:00000003
"AlterAdds"=dword:00000001
"AlterChanges"=dword:00000001
"AlterRenames"=dword:00000001
"AlterDeletes"=dword:00000001
"OutputToSingleFile"=dword:00000000
"AlwaysOverwrite"=dword:00000001
"LogComments"=dword:00000000
"UnicodeScript"=dword:00000000
"AlterByTableRename"=dword:00000001
"ForceAlterOption"=dword:00000000
"LogFilename"="c:\\temp\\psbuild.log"
"OutputToSingleFilename"="c:\\temp\\PSBUILD.SQL"
"CreateTableFilename"="c:\\temp\\CreateTables.sql"
"CreateIndexFilename"="c:\\temp\\CreateIndexes.sql"
"CreateViewFilename"="c:\\temp\\CreateViews.sql"
"AlterTableFilename"="c:\\temp\\AlterTables.sql"
"CreateTriggerFilename"="c:\\temp\\CreateTriggers.sql"
740207732898698169365288
How the script works
rem Filename: MigrateProject.cmd setlocal set DBTYPE=ORACLE set SOURCEUSERID=BRENT set SOURCEPWD=mysecretpassword set SOURCEDB=HDEV set TARGETUSERID=BRENT set TARGETPWD=mysecretpassword set TARGETDB=HTST set PROJECTNAME=%1 This code essentially sets your database type, source database connection info, and your target database connection info. Notice the setlocal command on the first line. This will keep environment variable changes local to the script, so you don't leave them hanging around after the script completes.
rem Copy Project N:\HR8_9\bin\client\winx86\PSIDE.EXE -HIDE -PJC %PROJECTNAME% -CT %DBTYPE% -CD %SOURCEDB% -CO %SOURCEUSERID% -CP %SOURCEPWD% -TD %TARGETDB% -TO %TARGETUSERID% -TP %TARGETPWD% -QUIET -LF C:\TEMP\COPY.LOG -CL 150 -AF 1 -DDL 1 This copies the project from the source database to the target database. The -HIDE and -QUIET flags are required for running app designer on the command line, otherwise app designer starts normally. CL sets the commit level to 150 objects. AF 1 directs app designer to retain the target audit flags, and DDL 1 directs app designer to retain the target DDL settings.
rem Build Project
regedit -s "RDMBuildSettings.reg"
N:\HR8_9\bin\client\winx86\PSIDE.EXE -CT %DBTYPE% -CD HQA -CO %TARGETUSERID% -CP %TARGETPWD% -PJB %PROJECTNAME% -HIDE -QUIET
endlocal
- END
This code segment creates the DDL necessary to build the objects. Since you can't really specify build settings on the command line, I use regedit to import the registry settings for the build options I want. In this case, it only creates SQL files (it doesn't execute them); it creates separate files for create table, alter table, create trigger, create index, and create view SQL; and puts it all in the C:\temp directory. I'm not crazy about this step because the correct registry entries can change between tools releases and it creates the potential to corrupt your registry. Unfortunately I don't know a better way to do it, so import registry entries at your own risk! Next the script calls App Designer again with the directives to build the project. When it's complete, you should find your SQL scripts in the C:\temp directory. After reviewing, I'll either run them or send them to the DBA to run depending on the migration process.
I'll leave the scripting of batch objects like SQR's and Crystal to your imagination. I'm sure you can find plenty of internet resources on how to copy files from one directory to another, and how to write a script to FTP files to your UNIX server.
TrackBack URI for this entry
Subscribe to this comment's feed
| < Prev |
|---|