Monday, June 29, 2015

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)

Saturday, June 27, 2015

SQL Scenario - 1

You have a table like below :

Name Age Gender
Atul         11 F
Ram 10 F
Disha  6 M
Pavan  8 F
Mani         10 M
Lovely 10 M
Rani    9 M

Unlocking XMETA locks from jobs

How can I unlock an XMETA locked job?

The job cannot be exported and when attempting to open the job, the following error appears:

Unable to open the Job
The Job 'TestJob' is locked by user 'dsadm'
(SessionID '8F1B0326-4F7E-45A8-B8EC-EAA8C4780528')

Do you want to continue and open a read-only view of the Job?
This issue arises when a lock still remains in XMETA for the corresponding job. Information Server will lock the job to ensure that there is no indeterminancy regarding the editing and saving of a job.
The most common causes of this is when a user has closed a client window unexpectedly (e.g. Force Terminate).

Wednesday, June 24, 2015

What happen when Import Sequential File with Extra Column

Job reads sequential file and load into RDBMS table.

What happen if sequential file has extra column in the back of each record?

It depends on data type of last column defined by DS and RDBMS table.
  • If data type of last column = varchar, extra column will be included as last column.

Sunday, June 21, 2015

DataStage Installation Checklist

  • Review the system requirements.
  • learn about tiers and components.
  • Design the topology of your installation.
  • Determine whether to pre-install your database system.
  • Determine whether to pre-install IBM WebSphere Application Server or pre-configure an existing installation of WebSphere Application Server.

Tuesday, June 16, 2015

trim leading 0's from decimal in DataStage

1. Convert the decimal (or whatever it was) to String.
2. then trim '0's from left.