What is the relationship between SQR and Application Engine? Is one better or are they complementary? They are both frameworks for batch processing in the Peoplesoft environment. As such, they must have overlapping functionality. Like Star Trek and Star Wars, they each have fans and detractors, with not always rational reasons for their preferences.
History Of Peoplesoft Batch
SQ Software created SQR in 1988. D&N Systems and Sybase marketed it, and merged in 1990. Sybase bought SQR from SQ Software in 1992. Peoplesoft Inc. adopted SQR to add reports to version 5 in 1995. MITI bought SQR from Sybase 1996 and merged with Brio Technology in 1999. Brio released SQR version 6 in 2000 and licensed it to Peoplesoft. Brio was acquired by Hyperion Solutions in 2003, which was acquired by Oracle Corporation in 2007.
Gray Sparling Solutions (GSS) blogged about Application Engine on February 03, 2007. Application Engine was created by Owen O’Neill in the 1990s as a replacement for COBOL in the Peoplesoft AR module. It became part of Peopletools in Peoplesoft version 8 in 2000.
Various presenters from Peoplesoft Inc. gave mixed messages about the destinies of SQR and Application Engine. Some gave the impression that Application Engine was the tool of the future and that SQR would be phased out. Others reassured us that our investment in SQR would not be lost. The latter seems to have borne out. Nine years later, I’m still writing SQR programs for tasks that cannot be done with Application Engine or BI Publisher.
The Necessary Similarities
Application Engine (PSAE) and the SQR runtime processor (PSSQR) are tools for performing certain types of work. PSSQR can perform some jobs that PSAE cannot, such as printing reports. PSAE can offer some features of operation more easily than PSSQR, such as parallel execution and restartability. However, there is a large overlap in purpose that makes it unsurprising that the two tools share some characteristics.
Both PSAE and PSSQR use SQL to process data on the database server. Both of them use proprietary but similar syntax (Peoplecode in PSAE, SQR in PSSQR) to process data internally. Both have means to transfer data between database tables and internal storage.
The SQR language is oriented towards handling one value at a time (although it can also execute SQL commands of unlimited complexity for set processing). The let command returns one value. The print command outputs one value. Certainly, the begin-select command can read multiple columns from multiple tables at once, but in the form of one row at a time.
Application Engine is oriented towards SQL set processing of an indeterminate number of rows at a time (although it can also execute Peoplecode, which will handle one value at a time).
PSSQR is primarily a reporting tool. It can print text, images, charts, and barcodes. Application Engine cannot print at all. GSS’s blog tells of several projects to add printing to Peoplecode. It seems they were all abandoned for lack of priority. If we want to integrate the programming power of Application Engine and the printing power of BI Publisher, we need SQR.
Application Engine is primarily a framework for moving data among tables. SQR can do that too, but it tempts us with seamless integration of set-oriented, non-procedural SQL and scalar-oriented, procedural SQR. Is it an advantage that we can choose either methodology or a disadvantage that when we mix them, we lose the power of set processing entirely? Yes.
What Is A Program?
An SQR program is an ASCII text file. It may have #include commands to insert another file into the source code – then it is multiple ASCII text files.
An Application Engine program consists of the following objects. This is relevant when we have to migrate it to another database. You won’t like it if you overlook one of these objects.
- The program itself.
- One or more sections.
- One or more steps.
- Zero or more SQL objects.
- Zero or more state tables.
- Zero or more temporary tables.
Both types of programs need a process definition. They need a run control page, run control component, and portal object, although they might share these with other programs. They both usually need a run control record and table.
Peoplesoft SQR is delivered without a development environment, unless we count the bundled copy of Kedit. We can buy a development environment:
- SQR Runner from CON+DEV Consulting & Development.
- SP Debugger for SQR from SparkPath Technologies Inc.
- SQR Express from Business Computing Solutions LLC.
- SQRIntegrator from SQRIntegrator.
- SQRPlus from SRI Technologies.
These products each have a variety of features, including color coding, record layouts, code generation, and debugging tools.
Application Engine uses the Application Designer as its development environment. It has some marvelous features and some major frustrations. The Peoplecode editor has color coding and syntax checking. The SQL editor expands meta-SQL. The rest of Application Designer is there to let us view and change record layouts. On the other hand, the SQL editor insists on reformatting our SQL statements for the worse and does no validation.
In addition to the textual code, Application Engine allows detailed configuration. Unfortunately, the high impact options (whether to execute the code, when to perform a commit, etc.) are displayed in small edit fields and checkboxes that aren’t visible at the same time as the code. It is as if an SQR editor allowed us to view the let commands OR the program flow commands (evaluate, if, while), but not all at once.
Application Designer allows us to have one edit window open at a time. We can either view one SQL statement, or one Peoplecode program, or the Application Engine structure, but only one. It’s like programming through a keyhole.
Strength Or Compensation?
Application Engine has a temporary table management system to allow multiple instances of a program to run simultaneously. This speeds up processing because the database management system can manipulate data in temporary tables faster than permanent tables because they’re smaller, containing only the relevant rows of data. Also, multiple instances of a program take advantage of multiple processors in the file server.
On the other hand, well written SQR programs usually perform more of their data processing in memory. They don’t write every interim result out to disk via the slow process of inserting rows in tables. We could run multiple instances of an SQR program, each with its own internal memory heap. We could even write a temporary table management system for SQR. However, SQR may be too fast to ever require that.
Application Engine programs can be made restartable. Then, if a program fails, we can try to correct a SQL statement or its data and restart the program at the point it left off.
That would be rather difficult in SQR. However, the SQR compiler validates all the SQL statements in the program before the program executes (except those with dynamic variables). Also, SQR rolls back the database when the program fails (unless we explicitly perform a commit), which allows us to rerun the program from the beginning. If the SQR program is faster than its Application Engine equivalent, we might still be ahead.
Application Engine has meta-SQL. It enables us to use temporary tables with %Table. We can do the same in SQR with dynamic variables. My favorite meta-SQL construct is %InsertSelect. This is a lifesaver for copying data from table to table when the two tables have many of the same field names. It automatically matches the fields unless we state otherwise.
On the other hand, SQR’s global variables greatly reduce the need to copy table to table. Application Engine forces us into that style of programming but compensates for it by reducing the required typing.
Send me an e-mail at firstname.lastname@example.org if you’re interested in the Northern California Regional Peoplesoft & J.D. Edwards Users Group. We will meet at the Oracle Headquarters in Redwood Shores, California on December 10, 2009. There will be a “discussion table” for SQR/SQL developers. I hope to meet you there.