What is EPOCH ?

EPOCH is time in seconds since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970 ( 1970/01/01 00:00:00 UTC). Or the epoch is Unix time 0 (midnight 1/1/1970), but 'epoch' is often used as a synonym for 'Unix time'. EPOCH time is widely used in every field of IT sector (specially in unix like OS ) where we have to keep difference is generation of something based on time.

Get nth Highest or Lowest data from Table

Here, we are considering a table student and we need to find out Nth highest and lowest marks of a student.

Nth Highest

select * from student s1 where N-1 = ( select count(distinct(marks)) from student s2 where s1.marks > s2.marks)

SQL Assignment08 - Delete Statement

1. Delete from customers.
2. Delete depositor of branches having number of customers between 1 and 3.
3. Delete branches havinng average deposit less than 5000.
4. Delete branches having maximum loan more than 5000.
5. Delete branches having deposit from Nagpur.

SQL Assignment07 - Update Statement

1. Give 10% interest to all depositors.
2. give 10% interest to all depositors having branch VRCE
3. Give 10% interest to all depositors living in city Nagpur.
4. Give 10% interest to all depositors having branch in city BOMBAY and living in Nagpur.
5. Add 100 Rs to deposit of Anil and assign it to Sunil.
6. Change the deposit of VRCE branch to 1000 and change the branch as VRCE_UPT
7. Assign to the deposit of Anil the maximum deposit from VRCE branch

SQL Assignment06 - Group By or Having Clause

1. List the branches having sum of deposit more than 5000.
2. List the branches having a sum of deposit more than 5000 and located in city BOMBAY.
3. List the name of Customers having deposit in the branches where the average deposit is more than 5000.
4. List the names of customers having maximum deposit.
5. List the names of customers having maximum deposit in the table CUSTOMERS living in Nagpur.
6. List the name of branch having highest number of depositors.
7. Count the number of depositors living in Nagpur.

SQL Assignment05 - Aggregate Functions

1. List total loan.
2. List total deposit.
3. List total loan taken from KAROLBAGH branch.
4. List total deposit of customers having account date later than 1-Jan-96.
5. List total deposit of customers living in city Nagpur.
6. List maximum deposit of customers living in BOMBAY.
7. List total deposit of customers having branch city DELHI

SQL Assignment04 - Operations

1. List all the customers who are depositors but not borrowers.
2. List all the customers who are both depositors and borrowers.
3. List all the customers, their amount, who are either depositors or borrowers and living in city Nagpur.
4. List all the depositors having deposit in all the branches where Sunil is having account.
5. List all the customers living in city Nagpur and having branch city BOMBAY or DELHI.

SQL Assignment03 - Join or Cartesian Product

Some more queries on SQL to work on --

1. Give names of customers having living city BOMBAY and branch city Nagpur.
2. Give Names of Customers having the same living city as their branch city.
3. Give names of customers who are borrowers as well as depositors and having living city as Nagpur.
4. Give names of customers who are depositors and having the same branch city as that of Sunil.
5. Give names of depositors having the same living city as that if Anil and having deposit amount greater than 2000.

SQL Assignment02 - Select Statement

Now, As we created some tables with data, lets do some practice with basic SQLs, Try to write queries for below statements -

-- List all data from table DEPOSIT
-- List all data from table BORROW
-- List all data from table CUSTOMERS
-- List all data from table BRANCH

SQL Assignment01 - Creating tables

Hello Guys, As we all know that SQL is "must" known for getting into DWH area, So considering this what I am trying to do here is sharing some basic SQL assignments to complete. Start with me if wanna to test your SQL knowledge :-)

First of all, we will create some tables ( Script may need to change as per DB you are using ) - 

Script to UnLock DataStage Jobs

This script will help you to unlock the all jobs, if you are a DS admin, or your datastage jobs , if a DS developer, without doing human error while executing command on Datastage server UV shell or Datastage Administrator Client.

Script is quite simple, Execute it , it will ask for Project Name and Job Name and Done if you have appropriate access on Datastage server.

May be need a modification as per your Datastage platform or if you need some fancy stuff to add on.  Happy Scripting !!

DataStage Error - Client logins fail with Error: 39125

DataStage client logins fail with error: 39125: the directory you are connecting to either is not a uv account or does not exist.

This error usually indicates that one of the 6 files required to make a UniVerse account is missing from the directory of the DataStage project which has the problem or that the user does not have permission to access these files. Those 6 files/directories are:

How to UPDATE row based on a row in same table

How you will update the Data of a table based on same table ??


update Table_DUMMY T1 
set COL_UPDT = (select T2.COL_FROM from Table_DUMMY T2 where T1.KEY=T2.KEY )  


Like the Facebook Page & join Group
https://www.facebook.com/DataStage4you
https://www.facebook.com/groups/DataStage4you
https://twitter.com/datastage4you
For WHATSAPP group , drop a msg to 91-88-00-906098

Oracle SQL Tuning Tips - 3

• Oracle automatically performs simple column type conversions(or casting) when it compares columns of different types. Depending on the type of conversion, indexes may not be used. Make sure you declare your program variables as the same type as your Oracle columns, if the type is supported in the programming language you are using.Use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = ’123′;
  HERE if emp_no indexed numeric, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_no = TO_NUMBER(’123′);
  Thus, index is used in this case.
  Don’t use:
  SELECT emp_no, emp_name, sal FROM emp WHERE emp_type = 123;
  HERE if emp_type is indexed varchar2, then after implicit conversion query will be:
  SELECT emp_no, emp_name, sal FROM emp WHERE TO_NUMBER(emp_type) = 123;
  Thus, index will not be used in this case.

Connect to Oracle DB with Linux Shell Script

In this post, we will see how to connect to Oracle DB in Linux Shell Script. There are some prerequisite for a Linux server to connect to Oracle DB. There should be oracle db driver installed on the Linux server from which you want to connect to Oracle DB. Check whether you can call/connect SQLPLUS from command line.
$ sqlplus
if you are not able to, check whether oracle path is set in .profile file. Lets assume, Oracle DB driver is installed on Server and Sqlplus path is set.

Now, there are some basic steps to connect to Oracle DB-
a) Connect to Oracle DB with UserID and Password
b) Execute the SQLs
c) Store the output in a file or send to standard output (terminal)

SQL Best Practices - Part1

Never put SQL scalar functions on columns in the WHERE predicates.
For example: WHERE YEAR(HIREDATE) = 2009 should be recoded as WHERE HIREDATE BETWEEN '2009-01-01'and '2009-12-31'.
When placing SQL scalar functions on columns in the SELECT portion of the SQL statement does incur some minimal overhead, but applying it to a column in the WHERE clause causes the predicate to become stage to non indexable.

Same applies for mathematics.
For example: WHERE HIREDATE – 7 DAYS > :HV-DATE should be recoded as WHERE HIREDATE > :HV_DATE + 7 days. Having the mathematics on the host variable is not a problem.

Check whether DataStage Job is Multi-Instance or not with Sctipt

The following script can be used to check if the given datastage job is multi instance or not.

Arguments to the Scripts:

Arg1:Datastage Project Name
Arg2:JobName

Get DataStage Job Information without using Director

With the help of this script, You can get the job no, category and other information without opening DataStage Director.

This Script need two arguments :
1. DataStage Project Name
2. DataStage Job Name

Script :

DataStage Scenario - Problem10

Goal :  Get the max salary from data file ( Seq file )

We know that max function is use to get the max value in a column of a table, but here you have to design a datastage job which get the max value from seq file.



Input Seq File :

A SQL Client Tool - TeraData Studio Express

Teradata Studio Express is a graphical Java program, developed on the Eclipse Rich Client Platform (RCP),  that will allow you to view the structure of a JDBC compliant database, browse the data in tables, issue SQL commands etc.
This is a nice tool which can connect many DBs like Aster database, DB2 for LUW, DB2 for i5/OS, DB2 for z/OS, Oracle, SQL Server, Generic JDBC connection and of course TeraData DB.