Categories
Hints and Tips Java Peoplecode

Ceil/Ceiling function in PeopleCode

I just used the java Math library ceil function from PeopleCode to solve the “round to nearest 0.5” problem e.g.

1
2
3
4
5
6
7
8
9
10
11
Local JavaObject &mathclass;
Local number &number_to_round, &result;
 
/* Instantiate java Math class */
&mathclass = GetJavaClass("java.lang.Math");
 
For &number_to_round = 0.1 To 2.0 Step 0.1
/* Use ceil function from java to solve problem */
&result = &mathclass.ceil(&number_to_round * 2) / 2;
MessageBox(0, "", 0, 0, "Number to Round: " | &number_to_round | " Result: " | &result);
End-For;
Categories
Hints and Tips SQL

CRLF and “GO”

I frequently find myself writing SQL that generates SQL. On SQL Server, I like to build a string that contains the SQL statement followed by “GO”. To do that I use CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10) at the end of the string e.g.

{some_generated_SQL}+CHAR(13)+CHAR(10)+’GO’+CHAR(13)+CHAR(10)

That way, when I paste the query results into SSMS or a text editor I get:

1
2
3
4
5
{first_generated_SQL}
GO
{second_generated_SQL}
GO
...
Categories
Hints and Tips PeopleTools PUM VirtualBox Windows

EnableLinkedConnections and VirtualBox PUM Images

When you map a network drive to the Samba share of a VirtualBox PUM VM in order to install (say) the PeopleTools client, the mapped drive may be invisible to your cmd prompt running as Administrator – something you need in order to update the registry and install the client software.

To work around this on Windows 7 through 10, see this article:

https://technet.microsoft.com/en-us/library/ee844140(v=ws.10).aspx

Categories
Hints and Tips Rants

I’m not a smart-arse

Well not all of the time…

When I re-read some of my posts I wonder if I come across as an utter smart-arse. Often my posts appear to be critical of all developers in general – but in many cases my criticisms can be traced back to two fundamentals:

  • Aptitude and analytical skills.

Both of which appear (to me at least) to be “dying arts”.

Categories
App Engine Hints and Tips PeopleTools Performance

Re-Use in Application Engine

Almost without exception whenever I am asked to review the performance of a PeopleSoft system I discover issues that lead back to locally developed Application Engine processes. In particular, high database SQL parsing rates invariably originate from SQL steps that should have the re-use flag set to Yes.

What this flag does is replace any %bind() variables in the SQL step with proper bind variables and compiles the SQL only once. Without this flag, the %bind() variables are substituted as literals at run-time and executed directly. This can lead to huge parsing rates as typically the offending SQL steps are executed within a loop. Of course, this is generally contrary to what you should be doing with SQL – set processing, but all too often Application Engine is used as a direct replacement for procedural languages such as SQR.

Some metrics from a system I looked at a while ago:

Approximately 15300 SQL statements in the cache, of which over 7700 originated from a single Application Engine run just 9 times during the day. These 7700 could have been been reduced to 3 (well 2 actually) just by setting the ReUse flag to ‘Y’ on the three offending SQLs. Using set processing of course, none of them would have been needed 🙂

Categories
App Engine Hints and Tips Peoplecode Peoplesoft PeopleTools Performance Tuning

Jackson Structured Programming (JSP) – Read Ahead and PeopleCode

Jackson Structured Programming – now that brings back memories of my COBOL training at British Telecom in the late 1980’s.

What prompted this short post was a dreadful piece of hand-crafted PeopleCode to load a CSV file using a file layout. The usual “Operand of . is null” occurred unless the input file contained a “blank line” at the end.

The underlying reason for this was a failure to apply one of the fundamental techniques in JSP – the single read-ahead rule:

Single Read-ahead rule: Place the initial read immediately after opening the file, prior to any code that uses the data; place subsequent reads in the code that processes the data, immediately after the data has been processed.

In fact, this approach is exactly what you get when you drag a file layout into a PeopleCode step in Application Engine – sample code that uses the JSP single read-ahead rule.

Categories
Configuration Elasticsearch ELK Hints and Tips Peoplesoft VirtualBox

DPK VirtualBox Memory Allocation

Even though my laptop has a decent 12 Gb of RAM, I still like to minimise the RAM allocated to PeopleSoft VMs.

My experience is that the sweet spot is 3072 Mb for VMs without SES – I never bother with the beast that is SES. After all it is a dead application – Elasticsearch cannot arrive soon enough for me. You can get away with 2560 Mb of RAM but you will see some swapping in OEL – not good even if you have a fast SSD. Mine is “ok” – a Samsung 1 TB 850 EVO but allowing any swapping still makes the system slow down considerably.

Categories
Hints and Tips Peoplesoft PeopleTools PUM

psadmin.io

psadmin.io – well worth a visit. Great podcasts, discussions on DPK, the latest PeopleTools features and how to use them as well as more mundane PeopleSoft admin topics.

Recommended.

Categories
Hints and Tips SQL SQL Server

Another SQL Tip – A “DATES” Table

A common requirement is to generate a list of the dates between two dates e.g. on a source row we have a FROM and TO date but we need to expand that into a row for each date. One way to do that on SQL Server is via a CTE:
 

---
--- A generated list of dates from 01-01-2013 until 25-01-2054
--- This would be a useful utility view/table in any PS system
--- Table is preferred - CLUSTERED INDEX ON PROCESS_DT provides
--- Important statistics on distribution AND allows for a CLUSTERED INDEX SEEK
--- in execution plan.
---
 
WITH [PROCESS_DATES] AS (
 
SELECT TOP 15000 CAST(DATEADD(DAY,ROW_NUMBER() OVER ( ORDER BY RECNAME),CONVERT(DATE, '2012-12-31')) AS DATE) AS PROCESS_DT,
DATEPART(weekday,DATEADD(DAY,ROW_NUMBER() OVER ( ORDER BY RECNAME),CONVERT(DATE, '2012-12-31'))) AS PROCESS_DT_WEEKDAY
FROM PSRECFIELDDB --- ANY TABLE WITH A NUMBER OF ROWS >= THE TOP VALUE ABOVE WILL DO
 
)

That works fine, but as the comments above indicate, if you are going to do this it is better to generate the list of values into an table, Apart from anything else, why incur the CPU time recalculating this list every time it is accessed – better as a one-off table populate. Note the use of a table known to have more that 15000 rows – this could be any table – even sys.all_objects provided it has enough rows. But if you are generating rows as a one-off you could cartesian (star join) tables to themselves to get enough rows. The 15000 is arbitrary – you could go further ahead.

Categories
Hints and Tips SQL SQL Server

A SQL Tip .. In-Line UNION for previous “n” Months

Here’s an interesting requirement:

“A list of Business Units with the period (YYYYMM) and the monthname (e.g. October) against each one for the prior 3 months”.

A simple problem, but here’s my solution:

SELECT SETCNTRLVALUE AS [BUSINESS_UNIT] , 
CONVERT(VARCHAR(6),PREVIOUS_MONTHS.MYDATE,112) AS [YYYYMM],
DATENAME(MONTH,PREVIOUS_MONTHS.MYDATE) AS [MONTH_NAME]
FROM PS_SET_CNTRL_TBL S,
( SELECT DATEADD(MONTH,-1,GETDATE()) AS [MYDATE]
UNION ALL
SELECT DATEADD(MONTH,-2,GETDATE()) 
UNION ALL
SELECT DATEADD(MONTH,-3,GETDATE()) 
) PREVIOUS_MONTHS
ORDER BY 1,2

Of course, the in-line inner UNION ALL could be created as a view if it was going to be reused, but it suffices for this example. Key points to note:

  1. The use of UNION ALL to eliminate the SORT you get with UNION – we know the values are unique but the optimizer doesn’t (it probably should though)!
  2. Perform the bare functions on the date value (CONVERT,DATENAME) in the outer loop rather than create multiple columns in the in-line SQL.
  3. Yes this is a cartesian product but that is perfectly acceptable when you know your data.
  4. This example is for the prior 3 months with reference to GETDATE() – extending to prior “n” months and a different reference date is trivial. But be careful what table you use to get the reference date). Typically you might want the “n” prior months before (say) the MAX() date on some transactional table – if so make sure you have an index to make finding that value as quick as possible.
  5. This is SQL server syntax – on Oracle you would have to SELECT from DUAL etc.

Enjoy.