Remove text between two patterns

This can be done in UNIX by using SED For example: I want to remove the text between foo and bar

 

file.txt contains:

white

green

blue

foo

red

black

bar

yellow

sed '/foo/,/bar/{d;}' file.txt

Output:

white

green

blue

red

black

yellow

Hope this helps!!! Read more ...

Find and Replace from Command Line

I had a situation where I had to find and replace in files with out creating new files and renaming them back, so after some research on Google I came up with this:

find . type –f | xargs perl –pi –e 's/foo/bar/g'

Read more ...

Find text in files using find

How to list all files having particular text in UNIX, you can do it by using find and grep
For example, if we want to list all files in present working directory containing foo, the following command helps

find . -type f | xargs grep foo

Instead of period you can give the directory location.

find /usr/home/akt -type f | xargs grep -i foo

-i for ignore case Read more ...

Number of AMP's in Teradata

How to get no. of AMP's in Teradata? It's smple, just run the below query:

SELECT HASHAMP ( );

As AMP's start from 0, you have to add 1

SELECT HASHAMP ( ) + 1;

Read more ...

How to Connect MacBook Pro to Sony Bravia TV

I have recently bought Sony Bravia KDL-40EX500, and my challenge was to connect my MacBook Pro and use it as an external monitor. Is it easy? Yep, all you need is:

- Mini DVI to HDMI adapter (I bought this on monoprice.com)
- HDMI cable
- 3.5 mm stero cable male to male

Now, look for PC/HDMI slot which is behind the LCD TV and audio in which is just above the HDMI slot, see picture (Click to enlarge)

Plugin the cables, and get your MacBook Pro and connect the cables, see pictures:


Now for the TV to work as external monitor, you have to make some changes in the settings, by default when you connect the TV it is considered as second monitor by MacBook Pro. So go to System Preferences > Click on Display and select mirror option, by doing this the display on your MacBook Pro and Sony TV will be same. Now if you close the lid of MacBook Pro, it will go to sleep, to avoid this you have to connect a mouse, key board and plug-in the power adapter. Close the lid and click any key on the keyboard or click the mouse, that's it you are done. Now you can enjoy watching movies from your MacBook Pro on Sony Bravia. Hope you liked the post.
Read more ...

How to Concatnate using AWK in UNIX

I was working on a editing a file using AWK, and I want to concat a string to the output in each line, so I came up with this

filename.out contains:
abc def ;
ghi jkl ;
asd fgh ;

awk '{print $2 "="}' < filename.out

Output:
def=
jkl=
fgh=

Don't forget to use the space.
Read more ...

Spaces

Space Terminology

	Perm	Spool	Temp
Max	The maximum number of bytes available for table, index and permanent journal storage in a database or user	A value used to limit the number of bytes the system will consume to create spool files for a user	A value used to limit the number of bytes the system will use to store data for global temporary tables for a user
Current	The total number of bytes in use to store the tables, subtables, and permanent journals contained in the database or user	The number of bytes currently in use for running transactions	The number of bytes in use for global temporary tables
Peak	The largest number of bytes actually used to store data in this user since the value was last reset	The maximum number of bytes used by a transaction run for this user since the value was last reset	The maximum number of bytes used by global temporary tables for a user since the value was last reset

Space Limits:

• Perm and spool space limits are assigned at the database or user level and not at table level
• When creating users or databases, perm space limits are deducted from the available space of the immediate owner
• The spool and temp space limits should not exceed that of the immediate owner at the time of you create the object
• If you do not specify the spool or temp limit, the new object inherits the limit from its immediate owner

Example of space terminology:

Lets say we have a user HR with MaxPerm space 25 MB, MaxSpool space 50 MB and MaxTemp space 30 MB. Now HR creates two users HR01 and HR02 with 6 MB, 4 MB MaxPerm space respectively and assigning 35 MB MaxSpool space to HR01, as there is spool assigned to HR02, it spool space defaults to its immediate parent MaxSpool space i.e. 50 MB. Now HR is left with 15 MB of MaxPerm space. Later HR drops HR01, HR MaxPerm space increases to 21 MB, since it regains Perm space fro HR01. As there is no MaxTemp space assigned to HR01 and HR02 it is defaulted to their immediate parent to 30 MB.

Giving one user to another:

• When you give one object to another object in the hierarchy, all the space allocated to the object goes with it. If you drop the object the space goes to the immediate owner
• When you give database or user, all descendants (child) of the given object remain descendants (child) of the given object
• When you give an object to a new parent, the ownership of space is transferred  and limits remain the same

Read more ...

Profiles

A set of common parameters that can be applied to a group of users.

Now you have a question in your mind, what set of common parameters?

• These parameters include: Account id's, default database, spool space allocation, temporary space allocation and password attributes (expiration, minchar, etc.)
• Using profiles simplify user management, a change of a common parameter requires an update of a profile rather than updating each user
• Profiles are managed by
  • CREATE PROFILE, MODIFY PROFILE, DROP PROFILE, and SELECT PROFILE

Example: You have a group of 1000 users that are assigned same temporary space, same spool space, same account ID and same default database, if you have to change a single parameter for 1000 users is time consuming. This can be over come by creating a profile with the assigned parameters and assigning that profile to the users. This eases the user management

Implementing profiles:

• CREATE PROFILE: needed to create profiles
• DROP PROFILE: needed to modify and drop profiles

Initially only DBC has CREATE PROFILE and DROP PROFILE access rights. These can given to DBA's by:

GRANT CREATE PROFILE, DROP PROFILE TO DBA WITH GRANT OPTION;

In the following section we will see, how to create a PROFILE and assigning a user to a profile:

Create PROFILE statement:

CREATE PROFILE [profile_name] AS ACCOUNT = [account_id/null], DEFAULT DATABASE = [db_name/null], SPOOL = [bytes/null], TEMPORARY = [bytes/null], PASSWORD = [password];

CREATE PROFILE Emp AS ACCOUNT = '$H2$acct01&H', DEFAULT DATABASE = 'ABC', SPOOL = '20000000', TEMPORARY = '100000000'...;

Assigning PROFILE to the user: It can be assigned using CREATE USER and MODIFY USER

CREATE USER Emp01 AS..., PROFILE = Emp;

MODIFY USER Emp02 AS PROFILE = Emp;

To remove profile for a USER:

MODIFY USER Emp02 AS PROFILE=NULL;

Profile definitions are applied to all users, overriding specifications at system or user level. All members inherit changed profile parameters. The impact on current users is as follows:

• SPOOL and TEMP space are imposed immediately
• Password attributes take effect upon next logon
• Database and Account ID's are considered at next logon unless the user submits a SET SESSION ACCOUNT statement

Order of precedence of parameters:

• Specify database or account id's at session level
• Specified parameters in a Profile
• CREATE USER or MODIFY USER statements

Source: Teradata Manuals

Read more ...

Correlated Subquery - Performance Tuning

A correlated subquery (CS) is a subquery whose outer query results are processed a row at a time, against the subquery result. The subquery result is computed for each row processed.

• a CS eliminates the need for intermediate or temporary tables
• a CS is fully integrated with global join planning to minimize costs
• CS is significantly faster than the query using temporary tables

Example:

1. Employee with highest salary in each department

SELECT L_Na,Sal, D_No
FROM test.employee ee
WHERE Sal = (SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No);

Answer Set:

Last_Name	Salary_Amount	Dept_No
stifler	60000	50
pitt	70000	10
young	60000	50
jones	60000	30
paul	70000	70
penn	60000	60
white	75000	90
nelson	70000	80
pitt	45000	100
lucas	75000	20
phips	65000	40

2. Employees whose salary is greater than the department average salary

SELECT L_Na, Sal, D_No
FROM test.employee ee
WHERE Sal > (SELECT AVG (Sal)
FROM test.employee em
WHERE ee.D_No= em.D_No);

Answer Set:

Last_Name	Salary_Amount	Dept_No
paul	70000	70
phips	65000	40
shook	65000	10
pitt	70000	10
jones	60000	30
lucas	75000	20
wall	65000	10

Read more ...

Concatenation and Correlated Subqueries - Performance Tuning

Concatenation allows to retrieve data correlated to the MIN/MAX function in a single pass

Example:

1. Employees with highest salary in each department

This can be written as using correlated subquery

SELECT D_No, Sal, L_Na, F_Na
FROM test.employee ee
WHERE Sal IN
(SELECT MAX (Sal)
FROM test.employee em
WHERE ee.D_No=em.D_No
GROUP BY D_No)
ORDER BY D_No;

Answer Set:

Dept_No	Salary_Amount	Last_Name	First_Name
10	70000	pitt	arun
20	75000	lucas	frank
30	60000	jones	indiana
40	65000	phips	carla
50	60000	stifler	tom
50	60000	young	nikie
60	60000	penn	arun
70	70000	paul	ak
80	70000	nelson	julie
90	75000	white	sam
100	45000	pitt	mark

It can also be written as

SELECT D_No,
MAX(Sal || ' ' || L_Na || ',' ||F_Na)
FROM test.employee
GROUP BY D_No
ORDER BY D_No ;

Answer Set:

Dept_No	Maximum(((((Salary_Amount||' ')||Last_Name)||',')||First_Nam
10	70000 pitt           ,arun
20	75000 lucas          ,frank
30	60000 jones          ,indiana
40	65000 phips          ,carla
50	60000 young          ,nikie
60	60000 penn           ,arun
70	70000 paul           ,ak
80	70000 nelson         ,julie
90	75000 white          ,sam
100	45000 pitt           ,mark

Did you observe the two answer sets? Can you tell the difference and how to overcome it? Please leave your answers in comments.

Read more ...

Create User

Create user is used to add a new user to the system. As discussed earlier user is similar to database except that a user can logon with a password and run queries. Below is the create user SQL

CREATE USER [user_name] FROM [dbname] AS
PERM = [bytes]
PASSWORD = [password/null]
STARTUP = [string]
TEMPORARY = [bytes]
DEFAULT DATABASE = [dbname]
COLLATION = [collation_sequence]
ACCOUNT = [account_id]
[NO] FALLBACK
[NO/DUAL] BEFORE JOURNAL
[NO/DUAL/LOCAL/NOTLOCAL] AFTER JOURNAL
DEFAULT JOURNAL TABLE = [name].[table_name];

SQL with parameter:

CREATE USER testuser FROM DBC
AS
   PASSWORD=user1
   PERM=2000000
   SPOOL=5000000
   TEMPORARY=0
   STARTUP='SET SESSION ANSI'    
   ACCOUNT='$LUSER'
   DEFAULT DATABASE=test
   COLLATION= HOST
   NO FALLBACK
   NO BEFORE JOURNAL
   NO AFTER JOURNAL;

Source: Teradata Manuals

Read more ...