Category: Hints and Tips

Categories
Hints and Tips Peoplesoft PeopleTools Process Scheduler

Process Scheduler and nVision – UseExcelAutomation!

Remember that to get correct formatting in server generated nVision reports you need to ensure the process scheduler config (psprcs.cfg) specifies UseExcelAutomation=1 in PeopleTools 8.5 versions. Without this setting, the output file will be OpenXML format – that will not run the nVision macros. As a result you will see the correct query data in the Excel output but it will lack most formatting.

Ref: Doc ID 1317246.1 on Oracle support details this, but the support document confusingly mentions 8.51 the in title even though the issue applies through to 8.53.

Categories
Hints and Tips SQL SQL Server

GETDATE()-1

Ever noticed some “odd” predicates in execution plans using GETDATE()-n where “n” is the number of days? Things like:

SOME_DATETIME_FIELD >= GETDATE()-‘1900-01-02 00:00:00.000’

It may look a bit odd but it does make sense where you think about the data type precedence rules in SQL Server. In this case, as GETDATE() is a DATETIME, there is an implicit conversion of the “n” into a DATETIME. So, if we run:

SELECT CAST(1 AS DATETIME)

what do we get? ‘1900-01-02 00:00:00.000’

And yes, “2” gives ‘1900-01-03 00:00:00.000’ as you might expect.

As the subtraction of the two dates is actually just using decimal subtraction, the GETDATE()-1 does work to give you “yesterday”. It’s just not quite as clear (in my view) as using:

DATEADD(day,-1,GETDATE())

Categories
Hints and Tips Security

Cisco AnyConnect – Disable ICS

If you are having issues using Cisco AnyConnect on Windows and are getting errors such as:

“Connection attempt has failed”

or

“AnyConnect was not able to establish a connection to the specified secure gateway”

then be sure to confirm you have stopped or disabled the Windows Internet Connection Sharing (ICS) service.

ICS

Also, make 100% sure you haven’t shared the wireless device itself.

Related Post: Lenovo laptops have a HotSpot feature that requires ICS (see Lenovo Support Article).

Categories
App Engine Hints and Tips Oracle PeopleTools PSVERSION Tools VERSION

VERSION 3.0 Application Engine!

If you are running a pre-8.53 version of PeopleTools you will (hopefully) be aware of the issues and requirements of using the VERSION Application Engine to correct problems with object versioning in PeopleSoft.

To summarise, the older VERSION App Engine just resets all the counter to 1! This is known as “Classic Mode” in the later release of the VERSION App Engine and is to be avoided at all costs really. You need everyone out of PeopleSoft, have to take all Application and Process Schedulers down, clear cache and run the App Engine from the command line to be 100% safe using it. Ugh.

Oracle recommends downloading and installing the most current VERSION Application Engine on all systems running PeopleTools >= 8.44 up to the latest release. Why? Because the later release is safer and better in all respects.

It has three execution modes:

  • Report Only Mode – Prefix your Run Control Id with the string “REPORTONLY” and you will get a report of the problems you have (if any) with versioning.
  • Classic Mode – Prefix your Run Control Id with the string “RESETVERSIONS” and the App Engine will run the “old way” – with all of it’s limitations (servers down, everybody out, cache cleared and run from command line). DO NOT USE THIS!
  • Enhanced Mode – Use and other Run Control prefix and the App Engine will just fix the versions that are wrong. It won’t reset back to 1 – just make everything line up correctly. You can even run this via Process Scheduler and with the servers up. What’s not to like about that?

Oracle Support Document 611565.1 (E-AS: Instructions Regarding the Use of the VERSION Application Engine Program) can be found at: Oracle Support Document 611565.1

Categories
Hints and Tips Oracle PeopleTools

PeopleSoft Date Fields Tip

Remember that you can put the letter t (or T) into a date field in PeopleSoft and the system will replace it with (t)oday’s date when you tab out of the field.

Actually, back in pre-web/pre-PIA versions of PeopleTools (when the application was a Windows 32-bit executable), there were other useful values you could use in a date field – from memory I believe “y”/”Y” gave you yesterday’s date. There may have been others too but as far as I am aware only “t”/”T” still exists in the web versions of PeopleTools.

Categories
Hints and Tips Oracle SQL Server Tuning

Oracle vs SQL Server Terminology

Full Table Scan == Clustered Index Scan

In Oracle we are generally taught that a Full Table Scan is a bad thing. Whilst this isn’t always true (it depends on so many factors), the terminology used in Oracle makes it sound bad. With SQL server we have the concept of a Clustered Index (which is essentially just the table itself) but the equivalent terminology we see in the query plan is “Clustered Index Scan”. This sounds almost like a good thing to those from an Oracle background. But it isn’t. It’s a full table scan. 🙂

Categories
Hints and Tips One-liners SQL

List of Alphabetic Values in SQL Server

I really like this use of the undocumented but widely used master.dbo.spt_values table to give a list of characters e.g. a-z or A-Z:

SELECT CHAR(NUMBER) FROM master.dbo.spt_values
WHERE NUMBER BETWEEN 97 AND 122 --- Lowercase a-z 
--WHERE number between 65 and 90 --- Uppercase A-Z

Other ranges such as “number between 48 and 57” are also useful (that gives 0 through 9).

There are of course other techniques to address this requirement such as creating a permanent table of the numbers/letters required. I’m not advocating the above over others – I just like the “neatness” of the above approach. Perhaps using sys.all_objects or some other documented table/view would be safer – although I doubt microsoft will remove master.dbo.spt_values any time soon. Here’s another approach you might prefer:

SELECT TOP 26 
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+64) AS upper_case,
CHAR(ROW_NUMBER() OVER (ORDER BY object_id)+96) AS lower_case
FROM sys.all_objects
Categories
Hints and Tips Performance

“Do it in the Database” Anti-pattern

Reuven M Lerner’s article titled “Use Your Database” in the August 2015 Linux Journal re-iterates the point I make in my post about moving logic to the database to improve performance.

Don’t forget how fast your DBMS is nor how much processing power you almost certainly have sitting idle on your DB server(s). Use it!

Categories
Hints and Tips SQL

Underscore matching in LIKE clauses

Speaking with an old colleague last week made me realize that some developers do not appreciate the significance of the underscore (_) in LIKE clauses.

A somewhat artificial example from PeopleSoft:

A developer wants to find all record definitions where the override SQL table name starts with ‘PS_’ followed by the string ‘DEFN’. They try this:

SELECT * FROM PSRECDEFN WHERE SQLTABLENAME LIKE 'PS_%DEFN%'

But that gives them too many rows because the underscore matches any character in that position. So they craft a better solution:

Continue reading “Underscore matching in LIKE clauses”
Categories
Hints and Tips SQL

Character Classes in SQL Server LIKE

Although SQL Server does not support regular expressions natively, character classes in LIKE clauses offer a subset of the flexibility that is often forgotten. Some examples:

For example:

SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS LIKE '[ALPS]'

Is equivalent to:

SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS IN ('A','L','P','S')

But this is NOT the same as this because the character class check is normally case sensitive:

SELECT TOP 10 * FROM PS_JOB WHERE EMPL_STATUS LIKE '[aLPS]'

You can also mix ranges and lists of values. For example, to find all OPRIDs in PSOPRDEFN starting with a lowercase letter or numerics 1,2,6,7 or 8:

Continue reading “Character Classes in SQL Server LIKE”