PowerShell version 2.0 has been out for a while. Microsoft doesn't make it all that obvious, but it seems you can't just get PowerShell by itself.
Also, for Windows XP, you have to be on Service Pack 3, which, alas, I am not. My PeopleSoft servers are running Windows Server 2003, so I can get PowerShell v2 for them.
Anyway, you can download it here. It wasn't all that easy to find. I looked at the MSFT PowerShell Blog, and saw a "click here to download PowerShell" link right at the top. Good! So I follow the link, and does it get me to the download page? Nope. It's a list of links, none of which say anything about the final release of PowerShell 2.0. There are earlier versions, and some test releases of 2.0. There's one link labeled "lastest release," but that's to Windows Management Framework, not PowerShell. Little did I know, WMF includes PS. On a whim, I followed that link, and finally found myself on a download page.
Even on then, you have to scroll about 2/3 of the way down. Hey, Microsoft! If you want people to use this stuff, make it less confusing. Granted, PowerShell is not exactly aimed at the easily-confused, but still.
Friday, December 4, 2009
Oracle index compression and PeopleTools
[blog note: PowerShell has dropped to the bottom of my priority list for now. I hope to get back to it eventually. In the meantime, here's a PeopleSoft-related post that has nothing to do with PowerShell.]
I've been having great success with using Oracle's index compression with our PeopleSoft database. It saves a lot of space, and speeds up index range scans. The problem is that it's not supported by PeopleTools.
It's not appropriate for every index, but that's beyond the scope of this discussion. For some background on Oracle index compression, Jonathan Lewis describes the basics here, and Richard Foote goes into detail here, here, here, and here.
When an index is built or rebuilt with the PeopleTools App Designer, it uses the DDL [data definition language] model for your particular platform. In the case of Oracle, you can have default parameters like initial and max extents that you can override per index. Compression isn't one of those parameters, so I've been compressing selected indexes outside of PeopleTools. Usually, I'll just do an "alter index ps_some_huge_table rebuild compress X" with X being some number of columns.
This is not at all optimal. It's not reflected in the PeopleTools metadata, so if anybody does a build with PeopleTools, the compression is lost. In the case of an application upgrade, this is likely to undo compression on a large number of indexes.
What I'd like to do is modify the DDL model SQL, have the default be no compression, and override the compression for those indexes that I've already compressed. For future index compression, I can just use PeopleTools from the get-go.
You can change the DDL model SQL in PeopleTools/Utilities/Administration/DDL Model Defaults.
Here's the problem I've run into: the delivered DDL Model SQL for a create index command for Oracle looks like this, with anything in double-asterisks being a parameter that's either defaulted or overridden:
I've modified it to look like this:
and set up an additional parameter COMPRESS with a default value of 0. I then go into a record definition for some table with an index I want to compress and override that default value with 3. I generate a CREATE INDEX script, and everything looks good -- the index with the overridden COMPRESS parameter says COMPRESS 3, and the other indexes say COMPRESS 0.
Alas, COMPRESS 0 is not legal syntax, at least in Oracle 10.2. The syntax for no compression is either NOCOMPRESS or just no compress clause at all. I can't have the default be blank, since COMPRESS on its own with no integer means compress every column in a non-unique index and compress all but the last in a unique index.
As I'm typing this, another idea occurs to me. Back in a bit... [type type type test test test...]
OK! That's better. Instead of using the parameter just as the number of columns, with the default being 0, I made the default value "NOCOMPRESS" and the override now has to be "COMPRESS X" with X being some positive integer. I no longer include the keyword "COMPRESS" in the model DDL; I just include the parameter value itself. So now the model SQL is this:
One oddity: I had to exit and restart the app designer to get it to use the new default value.
I have it only in a development database. I'll have to think, read, and tinker a bit before I put it in production. Upgrades are by far my biggest worry.
UPDATE: I heard from a couple of PeopleSoft experts, by email and in the comments, and there's an even simpler way to achieve this. It's based on the fact that PeopleTools does no validation on the model SQL or parameters. One can just piggyback the compress syntax onto one of the existing parameters. Says David Kurtz:
UPDATE: David Kurtz discusses this topic on his own blog.
I've been having great success with using Oracle's index compression with our PeopleSoft database. It saves a lot of space, and speeds up index range scans. The problem is that it's not supported by PeopleTools.
It's not appropriate for every index, but that's beyond the scope of this discussion. For some background on Oracle index compression, Jonathan Lewis describes the basics here, and Richard Foote goes into detail here, here, here, and here.
When an index is built or rebuilt with the PeopleTools App Designer, it uses the DDL [data definition language] model for your particular platform. In the case of Oracle, you can have default parameters like initial and max extents that you can override per index. Compression isn't one of those parameters, so I've been compressing selected indexes outside of PeopleTools. Usually, I'll just do an "alter index ps_some_huge_table rebuild compress X" with X being some number of columns.
This is not at all optimal. It's not reflected in the PeopleTools metadata, so if anybody does a build with PeopleTools, the compression is lost. In the case of an application upgrade, this is likely to undo compression on a large number of indexes.
What I'd like to do is modify the DDL model SQL, have the default be no compression, and override the compression for those indexes that I've already compressed. For future index compression, I can just use PeopleTools from the get-go.
You can change the DDL model SQL in PeopleTools/Utilities/Administration/DDL Model Defaults.
Here's the problem I've run into: the delivered DDL Model SQL for a create index command for Oracle looks like this, with anything in double-asterisks being a parameter that's either defaulted or overridden:
CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING;
I've modified it to look like this:
CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING COMPRESS **COMPRESS**;
and set up an additional parameter COMPRESS with a default value of 0. I then go into a record definition for some table with an index I want to compress and override that default value with 3. I generate a CREATE INDEX script, and everything looks good -- the index with the overridden COMPRESS parameter says COMPRESS 3, and the other indexes say COMPRESS 0.
Alas, COMPRESS 0 is not legal syntax, at least in Oracle 10.2. The syntax for no compression is either NOCOMPRESS or just no compress clause at all. I can't have the default be blank, since COMPRESS on its own with no integer means compress every column in a non-unique index and compress all but the last in a unique index.
As I'm typing this, another idea occurs to me. Back in a bit... [type type type test test test...]
OK! That's better. Instead of using the parameter just as the number of columns, with the default being 0, I made the default value "NOCOMPRESS" and the override now has to be "COMPRESS X" with X being some positive integer. I no longer include the keyword "COMPRESS" in the model DDL; I just include the parameter value itself. So now the model SQL is this:
CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING **COMPRESS**;
One oddity: I had to exit and restart the app designer to get it to use the new default value.
I have it only in a development database. I'll have to think, read, and tinker a bit before I put it in production. Upgrades are by far my biggest worry.
UPDATE: I heard from a couple of PeopleSoft experts, by email and in the comments, and there's an even simpler way to achieve this. It's based on the fact that PeopleTools does no validation on the model SQL or parameters. One can just piggyback the compress syntax onto one of the existing parameters. Says David Kurtz:
I tend to put the COMPRESS keyword and value into the PCTFREE DDL override. These are just strings that are used to build up the DDL command. The PCTFREE override defaults to "10". I might, for example, set it to "10 COMPRESS 1".The advantage is that when upgrade time comes, and the DDL script runs (in this case, DDLORA.DMS), nothing is lost. I don't have to remember to redo the custom model SQL.
UPDATE: David Kurtz discusses this topic on his own blog.
Thursday, May 28, 2009
Zombie Crystal Reports, part 2
See part 1.
Yes, I have a lot of Chrome tabs open. You don't know the half of it...
It goes on and on and on from there, eventually showing us this:
Here we can see some App Engine servers, various report server processes like distribution servers, and an SQR. No Crystal Reports at all. I was hoping for a zombie so we could kill it. Another day.
I am hunting for long-running Crystal Reports on a PeopleSoft report server. Obviously there are a number of ways to see these, e.g., the PeopleSoft process monitor or Oracle data dictionary v$ views. I need a way to see these at the Windows process level, so I can kill them.
The PowerShell way to get process information is get-process. How did I learn this? Well, I googled it. That's a limitation of the command line: it's not all that discoverable. In contrast to a GUI, it's often hard to just poke around the tool itself to find stuff out. This page at Microsoft shows a number of things you can do with get-process.
Aside: get-process is a cmdlet (pronounced commandlet), which are the basic building-blocks in PowerShell.
By default, it shows all processes, and eight columns of attributes, none of which has anything to do with start time:
PS C:\temp\ps1> get-process Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName ------- ------ ----- ----- ----- ------ -- ----------- 88 3 2124 4300 39 5.39 308 Avconsol 86 3 2248 4096 44 25.66 1280 Avsynmgr 89 3 1916 3552 421 1.58 3468 bash 65 4 19216 18972 74 4.23 200 chrome 713 15 42024 56864 138 481.77 1372 chrome 61 3 8948 2448 60 0.80 1700 chrome 61 5 17168 3148 73 4.50 1916 chrome 61 3 11548 4684 65 10.64 2056 chrome 62 3 54004 15736 121 13.83 2228 chrome ...
Yes, I have a lot of Chrome tabs open. You don't know the half of it...
To find out what other attributes get-process can report, pipe it to cmdlet get-member:
PS C:\temp\ps1> get-process | get-member TypeName: System.Diagnostics.Process Name MemberType Definition ---- ---------- ---------- Handles AliasProperty Handles = Handlecount Name AliasProperty Name = ProcessName NPM AliasProperty NPM = NonpagedSystemMemorySize PM AliasProperty PM = PagedMemorySize VM AliasProperty VM = VirtualMemorySize WS AliasProperty WS = WorkingSet add_Disposed Method System.Void add_Disposed(EventHandler value) ...
StartTime Property System.DateTime StartTime {get;}
Get used to "get-member." I know I'll be using it all the time.
To see that property, pipe it to the cmdlet Select-Object. We'll also send it a process name with a wildcard to just see PeopleSoft-related processes:
PS C:\temp\ps1> get-process ps* | select-object Id, ProcessName, StartTime Id ProcessName StartTime -- ----------- --------- 3484 psadmin 3/26/2009 8:48:36 AM 2024 psaesrv 5/27/2009 5:41:20 PM 2328 psaesrv 5/26/2009 5:58:13 PM 3116 psaesrv 5/19/2009 12:23:18 PM 3344 psdstsrv 5/28/2009 3:32:49 AM 3472 psdstsrv 4/2/2009 6:05:18 AM 2344 psmonitorsrv 5/28/2009 6:05:17 AM 3132 psprcsrv 4/2/2009 6:05:19 AM 3388 pssqr 5/28/2009 1:13:50 PM
Here we can see some App Engine servers, various report server processes like distribution servers, and an SQR. No Crystal Reports at all. I was hoping for a zombie so we could kill it. Another day.
So we can report on the start time, but how do we filter on it? Let's say we want to see everthing started in the last two hours:
PS C:\temp\ps1> $now = Get-Date
PS C:\temp\ps1> $then = $now.AddSeconds(-7200)
PS C:\temp\ps1> get-process | where-object {$_.StartTime -gt $then}
Handles NPM(K) PM(K) WS(K) VM(M) CPU(s) Id ProcessName
------- ------ ----- ----- ----- ------ -- -----------
31 1 1888 2608 12 0.03 2480 pssqr
79 4 5220 10056 44 0.06 1624 sqlplus
78 5 12816 16828 66 0.20 2576 sqrw
Grab the current time with Get-Date; calculate a time two hours ago (7200 seconds = 2 hours), and, via cmdlet Where-Object, show all processes started after that. Here we see that the report server has recently launched an SQR and a SQL*Plus script.
I could easily have asked for just the Crystal Reports, though since there weren't any running, it's a good thing I didn't.
In part three I may finally get around to killing the zombies.
Wednesday, May 27, 2009
Zombie Crystal Reports, part 1
I've struggled with Crystal Reports and the PeopleSoft report server over the years. Back when we were on PeopleTools 8.42, it took me several weeks to get them running smoothly. I had to carefully limit the number that would run simultaneously on any server, or they'd lock up on us. It took a lot of experimentation to find the right limit that would minimize zombie reports, yet still give us the throughput we required. I ended up adding another server just to handle the load.
Another thing I did, which was an ugly but effective hack, was to run a VBscript that would find and kill any Crystal that had been running for more than an hour. I know nothing about VBScript, but I found a generic process-killing script somewhere, and modified it for my purposes. I scheduled it to run periodically on each report server, and it did what it was supposed to do.
On our last PeopleSoft upgrade, we deployed all new Windows servers, including the report servers. I didn't put those VBScripts on the new servers, and so far on PeopleTools 8.49, I haven't seen many Crystal zombies.
On Tuesday of this week, I found a zombie that had been running over the three-day holiday weekend. I'd received complaints about Crystals sitting in queue for a long time. Part if it was just a tremendous number of report requests, but that one stuck report was apparently taking up a slot in the report server queue, so we were running one less report at a time than we normally do.
The PeopleSoft process request table (PSPRCSRQST) and the Oracle V$SESSION table showed me the thing had been running for a few days, but the Windows task manager showed three pscrrun process. The task manager doesn't show a start time. Which was the zombie?
Well, one clue was that one of them was using a crazy amount of memory -- I believe it was close to 1 GB. I was fairly sure that was the culprit, but to be extra sure, I put that report server in "suspend" mode, which completes any current processes, but doesn't start any new ones. Sure enough, the other two Crystals finished within a minute. I killed the zombie using the Windows task manager, and un-suspended the report server.
Had I scheduled that VBScript, that Crystal would have been long gone. I considered doing just that, but it seemed like a good opportunity to learn a little PowerShell.
Next, in part 2: finding long-running reports.
Tuesday, May 26, 2009
Getting started
Your Windows-based PeopleSoft servers and your Windows desktop machine probably don't have PowerShell installed. As of today, PowerShell is an optional feature on Windows Server 2008, is built in to Windows 7, and is installable for Windows XP, Vista, and Windows Server 2003.
PowerShell version 1 requires .NET framework version 2.0 or higher, which you also may not have. There's a preview of PowerShell version 2, which I have not tried. I believe when Windows 7 ships it will have PowerShell 2.
You can get the .NET framework 3.5 SP1 here, and PowerShell here. The installations are pretty straightforward. I'm lucky enough to have free reign over my servers (well, within reason...), but depending on your workplace policies, you may have to convince your nework or server admin to allow these on to the servers.
From what I gather, PowerShell works quite well over the network, so you may not have to install it everywhere. I'll be exploring that idea on a later date.
PowerShell version 1 requires .NET framework version 2.0 or higher, which you also may not have. There's a preview of PowerShell version 2, which I have not tried. I believe when Windows 7 ships it will have PowerShell 2.
You can get the .NET framework 3.5 SP1 here, and PowerShell here. The installations are pretty straightforward. I'm lucky enough to have free reign over my servers (well, within reason...), but depending on your workplace policies, you may have to convince your nework or server admin to allow these on to the servers.
From what I gather, PowerShell works quite well over the network, so you may not have to install it everywhere. I'll be exploring that idea on a later date.
Introduction
Here begins a very narrowly-focused blog describing one PeopleSoft administrator's use of PowerShell.
If you're here, you probably are looking for something quite specific, but I feel obliged to make some general introductory remarks. What is this? Who am I? What do I hope to accomplish here? Just what am I talking about, anyway?
PeopleSoft is a bit of an ambiguous term. It was the name of a company, and also the name of the product suite that they sold. The company is now owned by Oracle Corporation, which continues to sell, support, and develop the products. PeopleSoft made their bones back in the 1990s mostly with their HR/Payroll applications, and over the years added numerous other applications, like financials, distribution, manufacturing, and campus solutions. All of these are built on a proprietary development platform called PeopleTools, which started out as a client-server system. Several years ago PeopleTools migrated to a web-based design, known as PIA -- the PeopleSoft Internet Architecture. This blog will be mainly concerned with administration of the various servers that run a PeopleSoft application.
PowerShell is Microsoft's relatively new command-line shell and scripting language for Windows. It's been around since 2006. Windows has historically been a pointy-clicky sort of system, in stark contrast with Unix, which has a very long history of doing most things from the command line. As time goes on, there's a lot of blurring of that line, but the difference is still there. I work with both Windows and Unix, and I can't count the number of times I've said to myself "I wish Windows would do x" where x is some really simple Unix shell command. There's something to be said for the Windows GUI way of doing things -- you can often just point-and-click your way around and find what you're looking for. But what if you want that same thing to happen 100 times in a row, or every Monday at 4 a.m.? Command line to the rescue!
OK, now, who am I? I've been working with PeopleSoft since 1998. Since day one, I've used both Unix (specifically IBM's AIX) and Windows to run these applications. I've always had an Oracle database back-end running on Unix, and other associated servers on Windows. Back in the PS version 7.5 days, this included application servers and process schedulers (aka report servers), and since version 8, web servers as well.
I'm a complete newbie when it comes to PowerShell. I quite literally have not done a single useful thing with it yet. I've fiddled with it just long enough to see that it's very powerful, and if I can just learn it, it can become very useful in my daily work. I'm sure I'll often be doing things the hard way, so I encourage any PowerShell users out there to chime in with any advice you may have. And any PeopleSoft admins, as well -- if there's one thing I've learned in the last decade, it's that a decade of PeopleSoft admin experience isn't enough!
If you're here, you probably are looking for something quite specific, but I feel obliged to make some general introductory remarks. What is this? Who am I? What do I hope to accomplish here? Just what am I talking about, anyway?
PeopleSoft is a bit of an ambiguous term. It was the name of a company, and also the name of the product suite that they sold. The company is now owned by Oracle Corporation, which continues to sell, support, and develop the products. PeopleSoft made their bones back in the 1990s mostly with their HR/Payroll applications, and over the years added numerous other applications, like financials, distribution, manufacturing, and campus solutions. All of these are built on a proprietary development platform called PeopleTools, which started out as a client-server system. Several years ago PeopleTools migrated to a web-based design, known as PIA -- the PeopleSoft Internet Architecture. This blog will be mainly concerned with administration of the various servers that run a PeopleSoft application.
PowerShell is Microsoft's relatively new command-line shell and scripting language for Windows. It's been around since 2006. Windows has historically been a pointy-clicky sort of system, in stark contrast with Unix, which has a very long history of doing most things from the command line. As time goes on, there's a lot of blurring of that line, but the difference is still there. I work with both Windows and Unix, and I can't count the number of times I've said to myself "I wish Windows would do x" where x is some really simple Unix shell command. There's something to be said for the Windows GUI way of doing things -- you can often just point-and-click your way around and find what you're looking for. But what if you want that same thing to happen 100 times in a row, or every Monday at 4 a.m.? Command line to the rescue!
OK, now, who am I? I've been working with PeopleSoft since 1998. Since day one, I've used both Unix (specifically IBM's AIX) and Windows to run these applications. I've always had an Oracle database back-end running on Unix, and other associated servers on Windows. Back in the PS version 7.5 days, this included application servers and process schedulers (aka report servers), and since version 8, web servers as well.
I'm a complete newbie when it comes to PowerShell. I quite literally have not done a single useful thing with it yet. I've fiddled with it just long enough to see that it's very powerful, and if I can just learn it, it can become very useful in my daily work. I'm sure I'll often be doing things the hard way, so I encourage any PowerShell users out there to chime in with any advice you may have. And any PeopleSoft admins, as well -- if there's one thing I've learned in the last decade, it's that a decade of PeopleSoft admin experience isn't enough!
Subscribe to:
Posts (Atom)