It is refreshing to have a new copy of the production database instance in the development database instance. It is also a bit frightening. Was anything important lost in the overwrite?
I imagine most Peoplesoft SQR programmers are also well practiced in Peopletools development. We need to set up run control tables, pages, and components for our SQR programs. Most of us customize Peopletools objects to enhance our organizations’ online operations and many of us create new fields, records, and Peoplecode programs as well.
So it’s usually worrisome when it’s time to refresh the development instance of our database. Any work in progress will be overwritten. We can migrate our projects to another instance, but only if we remember what they are.
This blog entry could be about source control systems like STAT or about programmer’s journals. I’m forced to admit that I don’t have either. Instead, it’s about building a tool to save our work.
Compare And Report
The Peoplesoft Application Designer has a Tools menu with a choice to “Compare and Report.” This tool logs into another database instance and compares the current Application Designer project in the two instances. That answers the question “What are the differences between project X in database instances A and B?” Our question is “What are the projects that have differences between database instances A and B?”
There are versions of SQR that allow a program to login to two different database instances. The Peoplesoft SQR does not. It is possible for one database instance to link to another, but we need not rely on the existence of such a link.
The call system command allows an SQR program to execute an operating system command line, and that command line can execute another SQR program. The two programs can login to different databases. The parent program can communicate with the child by adding parameters to the command line. Either program can write a disk file that the other program can read. The parent and child programs are normally different, but I hope the connection between them is clearer in this example of a program calling itself.
#define compare_file …
#define datetime_format 'mm/dd/yyyy hh:mi:ss'
#define file_number 1
#define ini_file …
#define num_projects 10000
#define program.sqr …
#define projectdefn_file …
#define record_length 100
#define sqrw.exe …
begin-setup
! The database instance, username, and password comes from the command line or the keyboard. Alternately, it could come from a run control record.
ask dbms_login
create-array name=project size={num_projects}
field=name:char=''
field=dttm:char=''
end-setup
begin-program
let $tab = chr(9)
input #phase_num
! This assumes the parent program runs in the development database and the child program runs in the production database.
! It could be rewriten to be the other way around or a general case of source and destination.
evaluate #phase_num
when = 1
do write_dev
when = 2
do compare_prod
end-evaluate
end-program
begin-procedure write_dev
open '{projectdefn_file}' as {file_number} for-writing record={record_length}:vary status=#status
if #status
show 'Cannot open {projectdefn_file}'
stop
end-if
begin-select
A.PROJECTNAME
A.LASTUPDDTTM
let $line = &A.PROJECTNAME || $tab || datetostr(&A.LASTUPDDTTM, {datetime_format})
write {file_number} from $line status=#status
if #status
show 'Cannot write {projectdefn_file}'
stop
end-if
from PSPROJECTDEFN A
order by A.PROJECTNAME
end-select
let $command = '{sqrw.exe} {program.sqr} {dbms_login} -O{compare_file} -ZIF{ini_file} 2'
call system using $command #status wait
end-procedure write_dev
begin-procedure compare_prod
open '{projectdefn_file}' as {file_number} for-reading record={record_length}:vary status=#status
if #status
show 'Cannot open {projectdefn_file}'
stop
end-if
let #project_num = 0
while #project_num < {num_projects}
read {file_number} into $line:{record_length} status=#status
if #status
show 'Cannot read {projectdefn_file}'
stop
end-if
if #end-file
break
end-if
unstring $line by $tab into $name $dttm
put $name $dttm into project(#project_num)
add 1 to #project_num
end-while
let #project_num = 0
begin-select
B.PROJECTNAME
B.LASTUPDDTTM
while 1
get $name $devdttm from project(#project_num)
if &B.PROJECTNAME < $name
show 'PROD project ' &B.PROJECTNAME ' does not exist in DEV'
break
end-if
if &B.PROJECTNAME = $name
if range(datediff(&B.LASTUPDDTTM, strtodate($devdttm, {datetime_format}), 'second'), -2, 2) = 0
let $proddttm = datetostr(&B.LASTUPDDTTM, {datetime_format})
show 'Project ' &B.PROJECTNAME ' was updated ' $proddttm ' in PROD, ' $devdttm ' in DEV'
end-if
add 1 to #project_num
break
end-if
if &B.PROJECTNAME > $name
show 'DEV project ' $name ' does not exist in PROD'
add 1 to #project_num
end-if
end-while
from PSPROJECTDEFN B
order by B.PROJECTNAME
end-select
end-procedure compare_prod
There are other uses for the SQR-calls-SQR concept. Some commands require literal (or substitution variable) parameters. We may need to calculate parameters from user input or database values and vary the size of an array, the length of disk file record, the lines in a heading, the name of an array, or even the choice of procedure to execute. The parent SQR program can write #define commands into an include file. The child SQR program can include that file and use the substitution variables to vary its constants.
Last Update Date
Another way to identify Peopletools objects to preserve is to analyze the tables that define those objects. Many objects have fields for the date and time on which the object was modified and the operator ID for the maker of the modification, Peoplesoft/Oracle Corporation (PPLSOFT) or one of the developers of our organization.
These recently custom or customized objects may have migrated to other database instances already. We can compare their last update dates in different instances the way we did with projects in the previous section
Here’s the SQL to get a list of objects that were changed in-house since the last refresh (let’s say it was on January 1, 2009).
- Fields: select FIELDNAME from PSDBFIELD where LASTUPDDTTM >= ’01-JAN-2009′ and LASTUPDOPRID <> ‘PPLSOFT’
- Translate values: select FIELDNAME, FIELDVALUE from PSXLATITEM …
- Records: select RECNAME from PSRECDEFN …
- Peoplecode Programs: select * from PSPCMPROG …
- SQL Objects: select SQLID from PSSQLDEFN …
- Pages: select PNLNAME from PSPNLDEFN …
- Components: select PNLGRPNAME from PSPNLGRPDEFN …
- Menus: select MENUNAME from PSMENUDEFN …
- Portal Objects: select PORTAL_OBJNAME from PSPRSMDEFN …
- Application Engine Programs: select AE_APPLID from PSAEAPPLDEFN …
- Application Engine Sections: select AE_APPLID, AE_SECTION from PSAESECTDEFN …
- Queries: select OPRID, QRYNAME from PSQUERYDEFN …
- Process Definitions: select PRCSNAME from PS_PRCSDEFN
- Projects: select PROJECTNAME from PSPROJECTDEFN …
For more objects, search the table of record layouts defined by Application Designer:
- select RECNAME, FIELDNAME from PSRECFIELD where (RECNAME like ‘PS%’ or RECNAME like ‘%DEFN’) and FIELDNAME like ‘LASTUPD%’ order by RECNAME, FIELDNAME
Recent Comments