Categories
Hints and Tips Logstash Regular Expressions Ruby

What’s in a word? (\w regexp shorthand class)

Well not just letters of the alphabet it seems.

Take the case of the logstash pattern WORD:

WORD \b\w+\b

but the shorthand character class \w matches [a-zA-Z0-9_] – notice the digits and underscore! So WORD is not really a WORD!

REALWORD \b[a-zA-Z]+\b

would be better … although I suppose things might be different in Unicode. But generally log files may be Unicode but frequently the data itself is still effectively ASCII.

Categories
Hints and Tips One-liners Oracle SQL

REGEXP Problem

A good friend asked me how to replace the lastĀ full stop (period) in a field with another character using a regular expression e.g. a comma. When I asked “what language?” it turned out he wanted to do this using REGEXP_REPLACE in Oracle SQL.

It’s a problem with a couple of solutions that I could immediately think of – one more “Oracle”, the other more “regular expression”.

First the Oracle solution:

REGEXP_REPLACE({FIELD},'\.',',',INSTR({FIELD},'.',-1))

This uses backward search capability of INSTR() to find position of the last ‘.’ in the field and return it so that the REGEXP_REPLACE only starts the replace from that point on-wards.

Now a “pure” regular expression version:

REGEXP_REPLACE({FIELD},'(\.)([^.]*$)',',\2')

This one is a bit more involved as it uses grouping and a negated character class ([^.] = “not a period”) as well as a back reference (\2).

 

Categories
Hints and Tips SQL SQL Server

CROSS/OUTER APPLY and Performance

One quite useful technique to be aware of is the use of CROSS or OUTER APPLY with an in-line SQL e.g.

 

1
2
3
4
5
6
7
8
9
INSERT INTO {TABLE}
 
SELECT A.{FIELDS},APP.{FIELDS}
 
FROM {TableA} A CROSS/OUTER APPLY (
 
SELECT {more FIELDS} FROM {TableB} B WHERE .. A.{some_field} = B.{some_field}
 
) APP

However, with CROSS APPLY, the SQL Server optimizer tends to change the logic into regular joins. Mostly this is a good thing, but not if you want to “force” the driving table to be {TableA}. This can offer a considerable performance boost in some circumstances.

What you can do to force this behaviour is to add TOP 1 to the inner SELECT – assuming you expect only one row back from the CROSS APPLY SQL. Or you ordered the SQL result set and *do* just want the first row e.g. maximum/minimum effective date.

Note: CROSS/OUTER APPLY are SQL Server specific syntax for LATERAL in Oracle and others.

Categories
Hints and Tips Windows

Powershell ISE Editor Tip

Here’s an interesting tip – if you need an editor on a Windows server that handles Unix line endings you can use Powershell ISE … it’s not installed by default, but open a Powershell prompt and enter these commands:Ā 

Import-Module ServerManager
Add-WindowsFeature PowerShell-ISE

And you will have a half-decent editor.

Categories
Hints and Tips One-liners Windows

fsutil one-liner

fsutil fsinfo ntfsinfo X:

where X: is the drive letter gives output like this:

NTFS Volume Serial Number : 0x101051a010518e1a
NTFS Version : 3.1
LFS Version : 2.0
Number Sectors : 0x0000000074498860
Total Clusters : 0x000000000e89310c
Free Clusters : 0x000000000447f222
Total Reserved : 0x00000000000016e2
Bytes Per Sector : 512
Bytes Per Physical Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000025ec0000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x0000000000000002
Mft Zone Start : 0x0000000003cfca20
Mft Zone End : 0x0000000003d00040
Max Device Trim Extent Count : 512
Max Device Trim Byte Count : 0xffffffff
Max Volume Trim Extent Count : 62
Max Volume Trim Byte Count : 0x40000000
Resource Manager Identifier : 5646BA81-xxxx-yyyy-zzzz-185E0F1F2F38

Categories
Hints and Tips Windows

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.

Categories
Hints and Tips One-liners Perl SQL Server Windows

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.

Categories
CRM Hints and Tips Peoplecode Peoplesoft

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.
Categories
Hints and Tips Peoplecode

HashTables in PeopleCode

I came across this implementation of a hashtable in PeopleCode that uses the java API:

Implementing a HashTable in PeopleCode

But it is worth noting that there is another PeopleCode only implementation already delivered in PeopleTools:

EOEW_ETLAPI:COMMON:HashTable.

Categories
Hints and Tips Peoplesoft PeopleTools SQL Server Tuning

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.