SQR – Special Characters in Strings

I had a request the other day to strip out invalid characters from a series of strings.  The easiest way you can do this is to put a list of invalid characters into a string and then use the Translate function to set those characters to null.

In my code I have a Defined variable called null which = ”

Let $invalid_characters = '!!@#$%^&*()-_+=[]|\:;",.<>?''' || chr(123) || chr(125)
Let $newstring = Translate($oldstring,$invalid_characters,{null})

You will notice that there are two exclamation points in the invalid character string, and this is because one exclamation by itself denotes a comment. As well there is three end quotes which allows for a single quote to be put into the string and then end the string. The Chr function allows you to enter in a ascii specific character, which in this case 123 = { and 125 = } — If you try to put the curly bracket into the string it will try to look for a defined variable to insert and it will generate an error. You can use the ascii value to control other characters as well, here is a lookup table to reference from lookuptables.com

Ascii lookup table

Trace Application Engine

An application engine program can be traced by simply changing the trace parameters in the process scheduler configuration file, however, that requires the process scheduler to be brought down and it will trace every application engine ran through the process scheduler.  However, you can append those trace parameters to the process definition and it will trace the individual process.  Navigate to the process definition (Home>PeopleTools>Process Scheduler>Processes –> Override Options)  in the parameter list select append and enter in -TRACE xxx -TOOLSTRACEPC xxx -TOOLSTRACESQL xxx, where xxx is the bit reference based on the needs of the trace. So for example if you wanted options 1, 2, and 128 for trace, you add 1+2+128 = 131, and you enter 131 as the trace parameter: -TRACE 131.

-TRACE parameters:

Value Parameter Description
0 Disables tracing.
1 Initiates the Application Engine step trace.
2 Initiates the Application Engine SQL trace.
4 Initiates the trace for dedicated temporary table allocation to an Application Engine trace (AET) file. You can trace how the system allocates, locks, and releases temporary tables during program runs.
128 Initiates the statement timings trace to a file, which is similar to the COBOL timings trace to a file.
256 Initiates the PeopleCode detail to the file for the timings trace.
1024 Initiates the statement timings trace, but stores the results in the PS_BAT_TIMINGS_LOG and PS_BAT_TIMINGS_DTL tables.
2048 Requests a database optimizer trace file.
4096 Requests a database optimizer to be inserted in the Explain Plan table of the current database.
8192 Sets a trace for PeopleSoft Integration Broker transform programs.

-TOOLSTRACEPC parameters

Value Parameter Description
1 Trace Evaluator instructions
2 List Evaluator program
4 Show assignments to variables
8 Show fetched values
16 Show stack
64 Trace start of programs
128 Trace external function calls
256 Trace internal function calls
512 Show parameter values
1024 Show function return value
2048 Trace each statement in program


Value Parameter Description
1 SQL statements
2 SQL statement variables
4 SQL connect, disconnect, commit and rollback
8 Row Fetch (indicates that it occurred, not data)
16 All other API calls except ssb
32 Set Select Buffers (identifies the attributes of columns to be selected)
64 Database API specific calls
128 COBOL statement timings
256 Sybase Bind information
512 Sybase Fetch information
1024 SQL Informational Trace
4096 Manager information
8192 Mapcore information

Note that output from the -TRACE flag goes to the application engine trace (.AET) file. Output from the -TOOLSTRACEPC and -TOOLSTRACESQL output goes to the PeopleTools trace file (.TRC).