Mid-Life Crisis Continues

I wrote earlier about how I was keen on getting a motorbike after two decades of not having ridden one. Well, haven't got there yet for one reason or another. So in the meantime my age riddled brain has been pondering other stuff I used to enjoy in much much much younger days. The result is quite pathetic, but enjoyable to me just the same ....
My New Passion

Windows Services

Reference I find useful, as some servers at my workplace have hidden Control Panel options. To invoke Control Panel > Administrative Tools > Services, run:

c:\windows\system32\services.msc

UNION [ALL], INTERSECT, MINUS Operators

The UNION operator is useful when you want to draw information from two or more tables that all have the same structure.

Take for example two tables AUSTRALIA_CRICKET containing a few players from the Australian Cricket Team and KOLKATA_CRICKET containing a few players from the IPL Kolkata Cricket Team.

SQL> SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee

5 rows selected.

SQL> SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Sourav               Ganguly
Shoaib               Akhtar
Ricky                Ponting
Brendon              McCullum
Chris                Gayle

5 rows selected.

You can see from the results that Ricky Ponting is a player common to both tables.

To return data from both these tables, you could write:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  UNION
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME                     LAST_NAME
------------------------------ -----------------------
Brad                           Haddin
Brendon                        McCullum
Brett                          Lee
Chris                          Gayle
Michael                        Clarke
Ricky                          Ponting
Shoaib                         Akhtar
Sourav                         Ganguly
Stuart                         Clark

9 rows selected.

You'll note Ricky Ponting was returned only once using the UNION syntax.

This is what happens if you change the UNION to UNION ALL:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  UNION ALL
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting
Michael              Clarke
Stuart               Clark
Brad                 Haddin
Brett                Lee
Sourav               Ganguly
Shoaib               Akhtar
Ricky                Ponting
Brendon              McCullum
Chris                Gayle

10 rows selected.

This time Ricky Ponting appears twice. Once for each table.

If you want only rows that appear in all tables, you would use the INTERSECT operation:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  INTERSECT
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Ricky                Ponting

1 row selected.

To round things off, if you wish to return data from the first table only, excluding data common between the two tables, you would use the MINUS operation:

SQL> SELECT * FROM AUSTRALIA_CRICKET
  2  MINUS
  3  SELECT * FROM KOLKATA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Brad                 Haddin
Brett                Lee
Michael              Clarke
Stuart               Clark

4 rows selected.

Or ... in Reverse:

SQL> SELECT * FROM KOLKATA_CRICKET
  2  MINUS
  3  SELECT * FROM AUSTRALIA_CRICKET;

FIRST_NAME           LAST_NAME
-------------------- --------------------
Brendon              McCullum
Chris                Gayle
Shoaib               Akhtar
Sourav               Ganguly

4 rows selected.

PeopleSoft Scheduled Jobsets

A useful query to lookup "Active" scheduled jobsets.

select SCHEDULENAME
     , JOBNAMESRC
     , TO_CHAR(STARTDATETIME,'HH24:MI') TM
     , A.RECURNAME
     , RUNMONDAY
     , RUNTUESDAY
     , RUNWEDNESDAY
     , RUNTHURSDAY
     , RUNFRIDAY
     , RUNSATURDAY
     , RUNSUNDAY
  from ps_schdldefn a
     , psxlatitem b
     , ps_prcsrecur c
 where a.schedulestatus=b.fieldvalue
   and a.recurname = c.recurname
   and b.fieldname='SCHEDULESTATUS'
   and b.xlatshortname='Active'
order by TM;

The numeric values in the RUN{DAY} columns indicates what days of the week the jobs are scheduled to run.

Oracle Process Details

A useful query to check database processes:

select s.sid
     , p.pid
     , p.spid
     , s.process
     , s.status
     , s.username
     , s.osuser
     , s.program
     , a.name
     , s.client_info
     , s.module
     , to_char(s.logon_time,'yyyy-mm-dd hh24:mi:ss')
     , to_char(sysdate - (s.last_call_et /86400),'yyyy-mm-dd hh24:mi:ss')
  from v$session s
     , v$process p
     , audit_actions a
 where s.paddr = p.addr
   and s.command = a.action
 order by s.status;

Field Key

Oracle Session ID

Oracle Process ID

OS Process ID

OS Client Process ID

Status of Session

Oracle Username

OS Username

OS Program Name

Command in Progress

Client Info from DBMS_APPLICATION_INFO.SET_CLIENT_INFO proc

Module Info from DBMS_APPLICATION_INFO.SET_MODULE proc

Time of Logon

Last Call

Accessing TSM through java client on UNIX

Thanks to a tip from my mate Ken W.

Invoke TSM Java client on AIX 5.2:

Start up a x-terminal program such as x-win

Set the display using syntax export DISPLAY={ipaddress}:0.0

Set Java Path e.g. export PATH=$PATH:/{java directory path}

Start TSM dsmj