Note: Technical Talk being moved to:
www.nazimcricket.com/wiki


Saturday, 20 September 2008

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

Wednesday, 17 September 2008

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.

Tuesday, 16 September 2008

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.

Tuesday, 9 September 2008

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
  • Monday, 8 September 2008

    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