Opinionated Software

… we have opinions about everything!

Delete Too Long File Paths on Windows —

The 260 character limit on the file path makes deleting the file from applications like Weblogic problematic (especially the .patch_storage sub-folder structure). As a result the PeopleTools DPK “cleanup” command doesn’t actually clean everything up.

Use:

ROBOCOPY D:\TEMP\EMPTY .patch_storage /PURGE

or /MIR to delete the files recursively, where D:\TEMP\EMPTY is an empty folder.


Reducing SQL Server logspace usage —

Further to my post on using Elasticsearch and Kibana to visualize SQL Server logspace usage, it turned out that this data allowed me to suggest an alternative scheduling time for a large update job (to span a log backup point).

As a result of this we should be able to reduce peak logspace usage from 25% of allocated space to around 20% – thus allowing the log file to be reduced in size dramatically.


Opening UTF-16LE files in Perl —

Placeholder for useful code snippet:

open my $fh, '<:raw:perlio:encoding(UTF-16LE):crlf', $filename

which will convert CR/LF combinations to LF only. Alternatively, to keep them intact:

open my $fh, '<:raw:perlio:encoding(UTF-16LE)', $filename

Useful for reading Windows registry export files, SQL server log export files etc.


Visualizing SQL Server Logspace Usage with Elasticsearch, Kibana and Perl —

There are many ways possible ways to collect logspace usage data from SQL Server – this was a quick way using the tools I had at hand – DBCC, perl, elastic search and kibana.

All I did was capture the output of:

DBCC SQLPERF(logspace)

into a temporary table using a simple perl script using DBI. I then did a SELECT against the temporary table in the same perl script and posted the resulting data into ES using the Search::Elasticsearch CPAN module. The ES index was very simple – just 4 columns: database, logspace, logspaceused (%) and the timestamp of the capture (GETDATE()).

After that, all I had to do was visualize the data using Kibana. Here’s some sample output:

 

logspaceused graph

Logspace (%) usage over time

A great way to see the log space pressure points which can easily be tied back to specific batch processes at those times.


Display Template Debugging in PeopleSoft CRM —

A quick tip – if you want lots of debugging feedback in CRM Display Template rendering, just create a userid CSPEER (Chris Speer) and use that. Chris Speer wrote a lot (all?) of the Application Package code for Display Templates and handily left debugging (messagebox) code in place that only happens when the current logged on user is CSPEER.

Useful to know.

Note: You will need to hack the filename the debug output goes to – it still refers to a UNC path of a machine at PeopleSoft.

Of course, if you don’t want to edit the code at all you could:

  • Create a NetBIOS alias called “sclappp532” on the application server through a Windows registry entry.In HKLM\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters, just add a string value called OptionalNames with a value “sclapps532”. Personally, I would also create a matching DNS CNAME entry for completeness.
  • As the PeopleCode filename refers to a share name CR900DVL_LOGS you will also need to create that.

PeopleSoft – INCLUDE and WHERE (filtering) on SQL Server Indexes —

It is trivial to add support for INCLUDE() and WHERE() clauses on SQL Server indexes in PeopleSoft – just change the model DDL for Index creation to have the two optional clauses defaulted to blank and then override on the specific index as needed:

model_ddl_index_sql_server

Obviously, at the individual index level you will need the full syntax including the keywords e.g.

INCLUDE (col1,col2,col3 …)

WHERE (criteria)

Be aware that the criteria you can include is limited – refer to the Microsoft documentation for more details.


SQL 2014 Cardinality Estimator Trace Flags —

For reference:

  • 9481  – Use when running SQL Server 2014 with the default database compatibility level 120. Trace flag 9481 forces the query optimizer to use version 70 (the SQL Server 7.0 through SQL Server 2012) of the cardinality estimator when creating the query plan.
  • 2312 – Use when running SQL Server 2014 with database compatibility level 110, which is the compatibility level for SQL Server 2012. Trace flag 2312 forces the query optimizer to use version 120 (the SQL Server 2014 and later) of the cardinality estimator when creating the query plan.

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;