Hard-coding PSL, Literally!
We all use variables in our code. Variables make life easier because, well, they are variable. We can declare the variable XYZ and use it multiple times with different values and, perhaps, for different purposes.
Variables are powerful, but sometimes they can be extraneous and their use can create inefficient code. For example, consider a data extract procedure where the data is tab-delimited and requires a carriage return and linefeed at the end of each row.
You could initialize your variables like this:
type String CRLF = (13, 10).char(), TAB = 9.char()
You would use them like this:
do file.write (acn.cid_TAB_acn.lnm_TAB_acn.bal_TAB_acn.irn_CRLF)
And your M code would look like this:
In this example, each time your procedure needs to reference TAB or CRLF (perhaps millions of times in a big extract!) GT.M needs to look up the variables in the symbol table and translate them every time they are referenced. Efficient? No.
For better performance, you could use the actual literal values without using variables:
do file.write(acn.cid_9.char()_acn.lnm_9.char()_acn.bal_9.char()_acn.irn_(13, 10).char())
While this method provides better performance, it can be hard to read and, therefore, hard to understand and support. For example, do you know the ASCII character for 126.char()?
PSL Literal Scope Variables
A smart alternative to either of the above-mentioned methods is to use literal scope variables. PSL supports the ability to define variables as literals. You work with them in your PSL code the same as you would with any non-literal variable. For example, the above TYPE declaration would be changed subtly to add the “literal” designation and place each variable on a separate line:
type literal String CRLF = (13, 10).char()
type literal String TAB = 9.char()
You would still use them in the following manner:
But compilation is where PSL works its magic. The compiled M code looks like this:
D write^UCIO(file,vop1_$C(9)_$P(acn,$C(124),6)_$C(9)_$P(vop2,$C(124),1)_ $C(9)_$P(vop3,$C(124),1)_$C(13,10))
Notice that PSL has translated the variables and replaced them in the M code with their literal values. So, with literal scope variables, the PSL code is easier to read and the M code operates more efficiently.
PSL Global Scope Literal Variables
PSL supports global scope literal variables, of which presently only the columns in CUVAR qualify because of CUVAR’s non-keyed structure.
When you reference uppercase CUVAR in PSL:
type Number INT = CUVAR.INTPOS, X
set X = INT * 2
The M code generated with the value from CUVAR would look like this:
N INT S INT=1 N X
You can simplify your code and make it easier to read and support by using global scope literals in place of variables. Below is a slightly simpler version of the code eliminating the need for the variable INT:
type Number X
set X = CUVAR.INTPOS * 2
The M code generated with the value from CUVAR would look like this:
So, using global scope literal references makes your code easier to read and understand, plus it compiles into better performing code.
Two side notes:
- Only use global scope literal references for data that will rarely, if ever, change. Changes in the table require a recompile of every procedure, trigger or batch that refers to that data.
- If you need to reference CUVAR not as a literal, instantiate the CUVAR object in lower case. The references will not be interpreted by PSL as literals. So, CUVAR.INTPOS will compile as a literal but cuvar.intpos will not.
If you need to reference tables that rarely change, you could look them up from disk each time, or you could place them in the system cache (DBTBL1.CACHED = 2) which, depending on the cache size, could require additional memory use.
PSL supports the ability to compile literal tables directly into M code using a literal Db.select and the #WHILE and #END compiler directives. The structure for declaring a literal table is:
- Declare as a literal the ResultSet used to look up data from the table. For example:
type literal ResultSet rs = Db.select("IDP", "STBLIDP")
- Add the #WHILE command, followed by the rs.next()method.
- Add the lines of code that will compile the results of the select into your procedure’s M code.
- Place an #END command to close the code block.
The PSL code would look something like this:
public IDP(String IDP())
type literal ResultSet rs = Db.select("IDP, DESC", "STBLIDP")
set IDP(rs.getCol(1)) = rs.getCol(2)
And the M code generated would look like this:
S IDP("0")="Calculated at Billing"
S IDP("1")="Calculated at Payment"
There are two important points that must always be considered when working with literal tables:
- Tables that change frequently are generally going to be poor candidates unless you coordinate table changes with recompiles of the code that refers to the table. Theoretically, an after-update or after-insert trigger on the table could force a recompile. This might not be an optimal choice, however, if there are changes to multiple records in the same session because updating each record would cause a recompile.
- Very large tables are also poor candidates. There is no firm rule on table sizes, but a table too large could negatively impact performance. Tables with less than 50 entries will probably benefit from literal tables. However, for best results, you should benchmark different coding techniques when deciding which method to use for larger tables.
Put It All Together
Case Study – Which Customers Reside in High-Risk Countries?
The Financial Action Task Force (FATF) is an inter-governmental body established in 1989 by ministers of its member jurisdictions. This organization determines which countries pose a high risk of money laundering and terrorist funding. Presently, there are 64 countries identified as high-risk by FATF. Financial institutions must take precautions when dealing with customers who reside in these countries.
Periodically, countries are added and removed from this list, but changes are infrequent, which makes this list perfect for a literal table.
For this case study, we need to provide a new logical computed data item in the CIF table that would specify whether the country of the customer’s permanent address is high-risk. We must also ignore any customers from the United Kingdom (country code GB), which, for purposes of this project, is the bank’s home country. The computed data item will be used by the bank’s risk management people to identify high-risk customers for monitoring. For brevity’s sake, our example will only use six of the 64 countries. Those countries are:
1. Create a new user table to hold the high-risk countries.
a. Table HRSKCNTRY contains the following columns:
i. CNTRYCD – The two character mnemonic of the country. This is the primary key to the table.
ii. DESC – The name of the country.
b. Populate the table with the six countries above.
2. Create a new function in the procedure ABC to provide a list of the high-risk countries.
type String HRSK = ","
type literal ResultSet rs = Db.select("CNTRYCD", "HRSKCNTRY")
set HRSK = HRSK_rs.getCol(1)_","
Note: We may be tempted to refer the column name and code HRSK_rs.getCol(1) as HRSK_rs.getCol("CNTRYCD"), but PSL will not correctly handle a column name, only a column number.
The M code looks like this:
N HRSK S HRSK=","
If we write $$HRSK^ABC we receive the string “,DZ,EC,ET,MM,PK,TR,”
3. Create the code for the computed data item in a separate procedure.
(PSL does not allow the code that creates the literal table and the code that refers to the literal table to reside in the same procedure.)
public HRSKCUS(Number ACN)
type literal String LIST = $$HRSK^ABC
type RecordCIF cif = Db.getRecord("CIF", "ACN = :ACN", 1)
// Ignore customers from the bank's country
if cif.pcntry = CUVAR.CCNTRY return 0
if (LIST).contains(","_cif.pcntry_",") return 1
The M code looks like this:
N cif,vop1,vop2 S vop1=ACN,cif=$$vRCgetRecord1Opt^RecordCIF(ACN,0,"")
I $P(vop2,$C(124),4)="GB" Q 0
I (",DZ,EC,ET,MM,PK,TR,")[(","_$P(vop2,$C(124),4)_",") Q 1
4. Create the computed data item.
HRSKCUS Customer Resides in High-Risk Country1 L Y Computed: $$HRSKCUS^PROCCIF(ACN)
Case Study Conclusion
In this case study:
1. We created a function ($$HRSK^ABC) that creates a result string based on a literal table. We saw the compiled M code only contains the literal values of the table data.
2. We created a second function ($$HRSKCUS^PROCCIF) that did the following:
a. Declares the literal string LIST based upon the result of $$HRSK^ABC. In the M code, we saw that not only did LIST get created from the result of $$HRSK^ABC, but references to LIST were replaced with the result of $$HRSK^ABC by PSL.
b. Checks the customer’s permanent address country against the value of CUVAR.CCNTRY, which is “GB.” We saw in the M code that CUVAR.CCNTRY was translated as the literal “GB.”
3. We created a new computed logical data item (CIF.HRDKCUS) that tells us if a customer has a permanent address in a high-risk country.
* * *
PSL provides several options to use literal scope variables. Using literal scope variables, where appropriate, can improve the performance, readability, and supportability of your code. And, perhaps the most important benefit of using literal scope variables is that they reduce runtime CPU cycles and disk accesses for every execution and transfer all of that activity into a one-time event during code compilation. All are worthy reasons to hard code PSL, literally.
The developers and analysts of Mozaic Group Partners are among the most expert and experienced Profile people in the world when it comes to coding, fixing performance issues and providing brilliant solutions for Profile client institutions. If you have questions about your system’s performance or think your team could benefit from a Profile code review session or code workshop, contact us. We are happy to help.
If you found this article helpful, please share it with someone who might also benefit from its content.