Wednesday, 24 September 2014

Unix Interview Prep Tutorial Part2- Simple unix scripts (Usage of IF, FOR Loops, While, Case, parameters, and importing variables from a file)

Unix Interview Prep Tutorial Part2- Simple unix scripts (Usage of IF, FOR Loops, While, Case, parameters, and importing variables from a file)

1) For usage for IF command in unix script, see the below sample script

if [ "$num" -gt "0" ];
  echo "$num greater than 0"
  echo "$num less than 0"

2) For usage of FOR loop to read filenames from a filelist and move those files to a target directory, see the below example: (You can edit the $SOURCEDIR and $inputfilelist to the path you need for your purpose)

starttime=`date +'%Y-%m-%d %H:%M:%S'`
for file in $(<$SOURCEDIR/$inputfilelist)
if [[ $file == "#"* ]];
echo "Moving of $file ignored"
starttime=`date +'%Y-%m-%d %H:%M:%S'`
echo "Starting moving $file at $starttime......"
mv $file /targetfiledirectory/
echo "$file moved to target directory"

3) To check number of parameters sent to a script use the below commands in a script:

if [[ $# -ne 1 ]]; then
   echo "Invalid parameters.\nUsage: $0 <filelist>"
   exit 1

4) To import the variables defined in other file you can use the below commands at the beginning of the script:

. ./file.conf   /* works on unix and import variables from file.conf */
source directory_path/file.conf  /* works on linux and imports variable names from file.conf */

5) Usage of while statement in unix is shown below:#!/usr/bin/ksh
count=5                                   # Initialise count to 5
while [ $count -gt 0 ]                     # while count is greater than 0 do
   echo "the value of count is $count"
   count=$(expr $count -1)                 # decrement count by 1

6) Usage of string array in unix is shown below:

namelist="Joe John Peter Tom Bill"
for name in $namelist
   if [ "$name" = "Joe" ]
      echo "My name is $name"
      echo "My other names are   ${name}s"
7) Usage of case statement in unix is shown below:

case $1 #value from first parameter
1) echo 'One';;
2) echo 'Two';;
*) echo 'Three';;


How to create XML parser in Informatica using XMLSpy and .dtd?

How to create XML parser in Informatica?

Let’s say you are reading an XML and want to create an XML parser transformation to parse the XML. If the XML is not very complex, then a very simple way to generate the XML parser transformation might be to use XML spy to generate the .dtd file using the XML. Steps below:

How to generate .dtd in XML spy:

1)      Go to File Menu- Open the XML file

2)      Go to DTD/Schema and click on Generate DTD/Schema. It will ask do you want to associate the dtd with the xml file? Click yes and save the dtd file.

3)      Go to XML menu- Click validate. If any issues with the DTD , it will show up when you validate the XML. For the sample xml, I was working with I had to remove one of the child item in the .dtd to make it the validation successful. You can figure it out from the message the validation process throws at you.

Once you have generated the .dtd you can go to Informatica designer-transformation menu and create xml parser transformation using the .dtd schema you just generated. If you need to make any changes to the XML view, you can use the XML Editor that you can access from the midstream XML parser tab of the XML parser.


Java program to add or substract dates

Java program to add or substract dates

Below is the java program for date addition. In example below, you are adding 3 days to date 20140101. You can change the format of the date by changing the format mentioned in the code in red below. code------------

import java.util.*;
import java.text.SimpleDateFormat;
import java.text.DateFormat;
import java.text.ParseException;
public class dateadd {
public static void main (String[] args) {
DateFormat formatter = new SimpleDateFormat("yyyymmdd");
Date dt=new Date();
 dt = formatter.parse(args[0]);
Calendar c = Calendar.getInstance();
c.add(Calendar.DATE,Integer.parseInt(args[1]) );
dt = c.getTime();
            } catch (ParseException e)

Also check out the commonly used date functions in netezza:

Monday, 8 September 2014

Commonly used netezza date functions and how to do data manipulations?

How to use date functions in Netezza?

--to select the current date and timestamp use:

select current_date
select current_timestamp 

--to add months and convert date to integer use:

select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id

select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'9999999') --to get month id

select date(current_date -  cast('1 month' as interval))

--to convert timestamp to yyyy-mm-dd format:

select TO_CHAR(TO_DATE('2011-11-24 22:42:28','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD')
select TO_CHAR(current_timestamp,'YYYY-MM-DD')

--to extract year part from the current date

select extract (year from current_date)
select date_trunc('year', current_date)
to_number(to_char(current_date,'YYYY'),'9999')  --integer format

--to extract day and month from the current date

select extract (day from current_date)

--to get first and last day of the year:

select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')

--to get first of the month given a date

select date_trunc('month', current_timestamp)

--to get last month end date

select date_trunc('month', current_timestamp)-1

--to add 7 days to current date

select date(current_date +  cast('7 days' as interval))

--to get timestamp in a specific format

select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')

--to add hours to a date

 select TO_CHAR(date((current_date +  cast('1 hour' as interval))),'YYYY-MM-DD') 

--To calculate difference in days , weeks between two dates

select datediff(week,'2012-01-01','2015-12-31') as numweeks;

select datediff(day,'2009-01-01','2015-12-31') as numdays;

Also check:

For loading and migration of data in  netezza, check:

For SQL server date functions check:

How to generate SSH keys and use it for Sftp / SSH/ SCP?

How to generate SSH keys and use it for Sftp / SSH/ SCP?

SSH keys are commonly used during sftp and scp to authourize access to a host machine. Instead of using a password, ssh keys are used to identify and authourize a machine to login to a target machine. It is more safe and hard to breakin.

In brief, the steps to set up ssh authetication would be as follows:

Step a: On any source/client machine, use the command below to generate the ssk keys:

ssh-keygen -t rsa -f <nameofthekeyfile>

Example: ssh-keygen -t rsa -f mykeys-for-targetmachine

The command will prompt for keys size, expiry date, passphase, etc. You can enter the values you deem fit for your purpose.The above command will create a private and pub key with the key name mykeys-for-targetmachine you have provided in the command. The public key file will have .pub extention.

Step b: Now open the public key file and copy the contents of the key file.


The output will look something like this:

Step c: Login to the target machine that you want to access and open the authorised_keys file. This file should be in your home directory.Paste the contents of the public file that you copied in step b to this file.

Step d: Back to the source/client machine, change the permission of the keys to make it secure.
For the above example, it would be:

chmod 600 mykeys-for-targetmachine

Step e: Now try to SSH to target machine using your new keys to test the connection: The command would be something like the below (replace the <myuser> with the user name requierd on the target machine and the <targetmachine> with the name of the target machine that you want to login i.e. the machine to with the authourized key file that you just updated in step c:

ssh -i mykeys-for-targetmachine <myuser>@<targetmachine>

it will prompt first to add the target machine to the list of known host, press yes and you should be now logged into the machine. If it prompts for a password then you haven't done something right. Check the above steps and do the required correction.

How to sftp and scp using ssh keys? Below are the commands to use:

sftp -i mykeys-for-targetmachine <myuser>@<targetmachine>
scp -i  mykeys-for-targetmachine <myuser>@<targetmachine>

More information can be found in the below link: