ksh version

what `which ksh` | grep Version

wireless access on OpenBSD 4.6

source : http://www.daemonforums.org/showthread.php?p=25477

as per "man iwn"

wget http://damien.bergamini.free.fr/packages/openbsd/iwn-firmware-5.3.tgz

then as root
pkg_add -v iwn-firmware-5.3.tgz

then create files as follows.



cat /etc/hostname.iwn0
dhcp NONE NONE NONE nwid reiki wpa wpapsk \
0x0280836cc9b8f6d8524f36001e970f133a054e2e664604604d4df20ac1716384


cat /etc/mygate
192.168.1.1

CentOS vs Ubuntu

This is my personal experience. I had an issue with wireless networking on my laptop when i installed CentOS.

Basically Wireless works fine with Ubuntu right of the bat. CentOS requires some configuration. I did not try very hard to fix the problem with CentOS though.

As of now I prefer ubuntu for this reason and also I have more experience with Ubuntu. CentOS was my first install. I like it but wireless not working out of the box was a turnoff.

I would prefer to install CentOS on a server with wired networking.

concatenating multiple rows into a single string

--String Aggregation
--source : http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

1* select wm_concat(ename) employees from emp
SQL> /

EMPLOYEES
----------------------------------------------------------------------------------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER


SQL> select collect(ename) from emp;

COLLECT(ENAME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYSTPhHx4WvmCf/fgQAB/AQFNoQ==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

Oracle Hints

select /*+ first_rows(10) */ customer_id,c.first_name,last_name,city,country,order_total
from customer c
natural join orders o
natural join order_items;


returns immediately. There are millions of rows in orders and order_items tables.

SQL> select count(*) from orders;

COUNT(*)
----------
107508853

SQL> select count(*) from order_items;

COUNT(*)
----------
1028941975

SQL> select count(*) from customer;

COUNT(*)
----------
601

Ansi Joins

SQL> desc customer
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
CUSTOMER_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(40)
LAST_NAME VARCHAR2(40)
EMAIL VARCHAR2(55)
DOB DATE
CITY VARCHAR2(50)
COUNTRY VARCHAR2(50)
ZIP VARCHAR2(10)

SQL> desc orders
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE DATE
ORDER_MODE VARCHAR2(8)
CUSTOMER_ID NUMBER(6)
ORDER_TOTAL NUMBER(8,2)

SQL> desc order_items
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ORDER_ID NUMBER(12)
LINE_ITEM_ID NUMBER(3)
PRODUCT_ID NUMBER(6)
UNIT_PRICE NUMBER(8,2)
QUANTITY NUMBER(8)


natural joins

select c.customer_id,c.first_name,last_name,city,country,order_total
from customer c
natural join orders o;

returns errors as the common column "customer_id" is aliased.

ORA-25155: column used in NATURAL join cannot have qualifier
25155. 00000 - "column used in NATURAL join cannot have qualifier"
*Cause: Columns that are used for a named-join (either a NATURAL join
or a join with a USING clause) cannot have an explicit qualifier.
*Action: Remove the qualifier.
Error at Line: 1 Column: 7

remove the c.customer_id alias and it works fine :)

explain plan's

explain plan
SET statement_id = 'orders' INTO plan_table FOR SELECT * FROM customer NATURAL JOIN orders;


SELECT RTRIM (LPAD (' ', 2 * LEVEL)
|| RTRIM (operation)
|| ' '
|| RTRIM (OPTIONS)
|| ' '
|| object_name) query_plan,
cost,
cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0 ;


QUERY_PLAN COST CARDINALITY
SELECT STATEMENT 80358215 60983502947
MERGE JOIN CARTESIAN 80358215 60983502947
TABLE ACCESS FULL CUSTOMER 5 599
BUFFER SORT 80358210 101808853
TABLE ACCESS FULL ORDERS 134154 101808853

Dual Boot OpenBSD/Ubuntu/Vista using GAG

GAG is a really nice boot manager. Very easy to install and very small in size.

My configuration is as follows
- I installed Vista first then installed OpenBSD 4.6 , then I installed GAG and everything works fine.

Second configuration
- Installed Ubuntu by replacing above Vista , and then later installed GAG but GAG does not work

The trick is when u install Ubuntu , Install grub on its own root partition such as /dev/sda2 and not on the disks MBR (/dev/sda)

Then when you install GAG things will work fine. The reason for this is GAG replaces whatever boot manager (grub) in the earlier case if grub is installed on the MBR. So to keep Ubuntu intact and make it work with GAG install grub on the linux partition.

/dev/sda1 - ubuntu (/ as ext4) -- This was previously Vista
/dev/sda2 - OpenBSD
/dev/sda5 - swap

GAG works with ext4

executing sql file though perl

The code is simple , no big deal . just documenting for possible reuse next time.
The blog just does not let me put the lessthan greaterthan symbol.

#!/usr/bin/perl

use Getopt::Std;

%options=();
getopts("s:",\%options);

&usage if (! defined( $options{s} ) );

my $sqlfile = $options{s};

my $login_file = "/home/reiki/.dev_login";
open (IP,"$login_file") || die "Cannot open file $login_file $!";
my $user_pass = ;
close IP;


open ORA, "| /oracle/reikidev/app/product/10.2.0/bin/sqlplus -s $user_pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on echo on pagesize 500 linesize 20000 colsep ' '\n";

open (SF,"$sqlfile") || die "Cannot open file $sqlfile $!";
while (my $line = ) {
print ORA "$line";
}

print ORA "exit\n";
close ORA;
close SF;


sub usage {
print "usage: ./ut_reiki_exec_sql.pl -s sqlfile\n";
exit;
}

ksh 93

Ultimate Ksh book/reference by the creator of ksh himself.

Book title is
The New Kornshell - command and programming language.


print vs echo
echo is system dependent and provided for compatiblity with Bourne shell. print is used in the book instead of echo.

global database name

select * from global_name;


show parameter db_domain


select name,value from v$parameter where name = 'db_domain';

byobu and screen

byobu is a nice wrapper to screen (gnu screen)

From the man pages

NAME
byobu - wrapper script for seeding a user's byobu configuration and launching screen

DESCRIPTION
byobu is a script that launches GNU screen in the byobu configuration. This enables the display of system information and status notifications within two lines
at the bottom of the screen session. It also enables multiple tabbed terminal sessions, accessible through simple keystrokes.

Calling sqlplus from shell,perl

#!/usr/bin/perl

open ORA, "| /u01/app/oracle/product/11.1.0/db_1/bin/sqlplus -s $user/$pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on pagesize 500 linesize 200 colsep ' '\n";
print ORA "spool output.txt\n";
print ORA "select * from tab;\n";
print ORA "spool off\n";
print ORA "exit\n";
close ORA;


Calling from Korn shell

dblogin="`cat $HOME/.db_login`"

sqlplus -s $dblogin LESSTHANlessthanEOF | \
awk '{if(NR==1) printf("%s ", $1); \
if(NR==2) printf("%s ", $1); \
if(NR==3) printf("%s ", $1); \
if(NR==4) printf("%s ", $1); \
if(NR==5) printf("%s ", $1); \
if(NR==6) printf("%s ", $1); \
if(NR==7) printf("%s ", $1);} \
' | read t1_count \
t2_count \
t3_count \
t4_count \
t5_count \
t6_count \
t7_count

set head off pagesize 0 echo off verify off feedback off

select count(*) from t1
/
select count(*) from t2
/
select count(*) from t3
/
select count(*) from t4
/
select count(*) from t5
/
select count(*) from t6
/
select count(*) from t7
/
set head on echo on verify on feedback on
set lines 90
set pages 999
EOF

echo Table t1 has $t1_count rows
echo Table t2 has $t2_count rows

Pipes in Perl

#!/usr/bin/perl

open ORA, "| /u01/app/oracle/product/11.1.0/db_1/bin/sqlplus -s $user/$pass" or die "Can't pipe to sqlplus: $!";
print ORA "set trimspool on pagesize 500 linesize 200 colsep ' '\n";
print ORA "spool output.txt\n";
print ORA "select * from tab;\n";
print ORA "spool off\n";
print ORA "exit\n";
close ORA;

#-- list the processes running on your system
open(PS,"ps -e -o pid,stime,args |") || die "Failed: $!\n";
while ( )
{
print $_;
}