IT Pandora (ETL,DB,UNIX,AWS,NodeJS,Github etc.)

Sometimes we need to find the indexes present for a table or the table structure or db links present etc. To find these details we have some standard DB queries which gives us the required results. The following queries are applicable to only Oracle database. SELECT * FROM ALL_TABLES   The following query lists out all the tables present in the database. If you want search a particular table we can use it in the following way SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE '%emp%' SELECT * FROM ALL_TAB_COLUMNS This query list out all the columns. If we want to know a particular column is present in which all tables we can frame the query in the following way SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME='ID'; SELECT * FROM ALL_DB_LINKS This query will return the list of db links present in the database SELECT * FROM ALL_INDEXES This query will list out all the indexes present in the database.If we want to find the indexes present for a particular t...

Its been some time since the last post, but now i am back with a pretty interesting article Performance tuning is a very vital aspect in any project. The faster and better interface is always appreciated and wins laurels When the file size/ table which act as our sources are huge in size and the throughput is very low then we can go for partitioning. In partitioning we can split the data into various threads so data will be processed faster  If the informatica we are using is a standard edition then partitioning feature will not be available so now what we can do is  increase DTM buffer size. It will be present in session properties under performance section. By default it will be in auto mode we can change it to say 220mb,1gb etc depending on size of the source data.This will increase the throughput If the lookup transformation is taking is long time than we can go for joiner transformation to perform the same operation. As joiner will be better in performance...

One of the primary reasons why TERADATA is considered such a valuable product in market because of the utilities that are provided by teradata database. They are five teradata utilities available They are a)BTEQ b)FAST EXPORT c)MLOAD d)FLOAD e)TPUMP Let me give an overview about these utilities a)BTEQ - It stands for basic teradata query. In BTEQ we can perform various DDL and DML operations i.e. create,insert,merger,update,delete.  It can be run in two modea i.e. interactive and batch mode.  In here we can use BT & ET functions these represent begin transaction and end transaction respectively.  We can write multiple sql statement with in these two and commit action will be performed upon successful execution of all statements  If any of the sql statements fails then the data will be roll-backed. b)FAST EXPORT - FastExport ,the name itself is spells to exports data from Teradata to a Flat file. But BTEQ also does the same thing.The main difference i...

All the functions mentioned in the above title are mainly used in evaluation of data whether it is null or not. . Lets see in details how these functions work and what are the differences between them NVL Syntax:  NVL ( expr1 , expr2 ) If the expr1 is null then expr2 value is returned, if expr1 value is not null then expr1 value is returned. NVL2 Syntax: NVL2(expr1,expr2,expr3) if the expr1 is null then expr3 value is returned, if expr1 value is not null then expr2 value is returned. COALESCE Syntax: COALESCE(expr1,expr2,expr3...) it returns first non null expression value in the list. DECODE Decode is enhanced version of case statement let me explain you with a sample query select initcap(substr(pub_name,1,20)) publisher_name, decode(book_type,'computer',1,0) media from publisher  Here decode statement works like if the column book_type has computer value then it is returned as 1 else 0  Similar to case statement case when book_type...

Fact and Dimension table what does it mean? How do we classify which is fact and dimension table? Where do we use these. These are some of the most common questions that pop up when we hear dimension and fact table. Going by book, Fact and Dimension table in technical perspective is defined as follows The fact table contains business facts (or measures), and foreign keys which refer to candidate keys (normally primary keys ) in the dimension tables. A dimension table is a table in a star schema of a data warehouse. A dimension table stores attributes, or dimensions, that describe the objects in a fact table. Not let’s dig into this we have basically two schemas Star and Snow Flake schema In Star schema   a fact table is surrounded by dimension tables. In Snow flake schema we have again Fact table in the center of schema   surrounded by dimension tables, however in here the dimension tables can have their own child tables. facts correspond to events, dim...

We will be having a dilemma that when should we go for  dynamic lookup so let us discuss about that first. When we create a lookup on a target table and we are sure that there will be no duplicates coming from source data on PK Combination we go for a static cache lookup. But when there are duplicates coming from source end and we need to pick a latest value from the set then we go for dynamic look up. let me brief you with an example data Name   Location AAA    IND BBB     USA CCC     EUR AAA    EUR suppose we have data in the above following manner and only latest records should be pulled into the target so that the target data should look like in the following manner Name Location AAA  EUR BBB   USA CCC   EUR When we select dynamic cache in look up properties a new column named as NewLookupRow gets added in ports section of look up. Then we n...

After a bit of gap, i am back here.So instead discussing about a specific topic, let me tell you about some tricks/tips that can be used while working in informatica Worklet as we know is a set of reusable tasks.We cannot start a single task individually in worklet from workflow manager. we need to run the worklet in order the start the individual tasks assigned to it.Although after a run in completed from workflow monitor we can start tasks individually from monitor. If a worklfow which has schedule assigned to it gets failed, then it gets unscheduled so we need to reschedule it again by doing right click in workflow manger and select schedule workflow. If the following error appears while trying logging in informatica   Then try restarting services by doing the following steps go to run and type services.msc, there search for informatica sever then right click select restart.   Joiner vs lookup - It is advisable to choose joiner transformation when t...

Lets see about some more loop statements which can be used in unix shell scripting while: while followed by condition then set of statements to be executed do,done act as flower braces Syntax: while [condition/statement]              do              statement 1              statement 2              :              :               statement n               done  eg: #!/bin/sh a=0 while [ $a -lt 10 ] do    echo $a    a=`expr $a + 1` done Output will be as follows 0 1 2 3 4 5 6 7 8 9 Until: This command functionality is viceversa of while command. In while the loops continues to execute when condition is true , but in until the loops continues to execute until the condition is false. Syn...

There might be a scenario where we need a same statement  be executed for some 'n' number of times in those cases we usually go for loop commands. We have used these in c,c++,java.The same can be used even in unix shell scripting too Let's see about some loop commands which can be used in shell scripting. If,For,While,Until. IF-   If  keyword followed by condition then set of statements that should be executed upon satisfying the condition and then if loop will be ended with fi keyword syntax:        if [expression/condition]         then         statements to be executed         fi eg:    #!/bin/sh a=10 b=20 if [ $a == $b ] then    echo "a is equal to b" fi  for if-else it will be as follows #!/bin/sh a=10 b=20 if [ $a == $b ] then    echo "a is equal to b" else   echo "a is not equal to b" ...

We have many relational databases available in market, teradata is one such RDBMS it is usually preferred when we are dealing data with tera bytes in size and when we need a very fast quick response for retrieval data. Teradata main advantage is its parallelism architecture and utilities.Lets get to know more about it in a deeper way we have Parsing engine which has 4 components a)Session control- It checks for user/login credentials before processing the query b)Parser-It checks for the SQL syntax and user rights to access various database objects referred in the SQL query submitted by user c)Optimizer-It generates the query execution plan d)Dispatcher-It passes execution plan to bynet and receives all the responses and sends back to user Next we have Bynet it is the communication layer between PE (parsing engine) and AMP's ( access module processors).There are two bynets available Bynet-0 and Bynet-1 which help in continuous communication between AMP's and P...

Continuing the unix commands left at part II, lets get to know some more unix commands Paste- This command is used to merge lines of file of a single file or multiple files.for example if we give paste dummy.txt. It will display contents of file similar to cat command. $ paste dummy.txt aaa bbb  ccc $paste -s dummy.txt aaa bbb ccc  -s joins all lines of file since no delimiter is specified, default delimiter tab is being used $paste -d, -s dummt.txt aaa,bbb,ccc -d is it the delimiter option $ paste file1 file2  In here it merges both the files parallellely merged nslookup - This command is used to find the ip address if you know the domain name and vice versa $nslookup abc.com Name: abc.com Address: 209.132.183.181 $nslookup  209.132.183.181 Name: abc.com finger- This command is used to know the information of a user i.e name,home directory, last logged info etc. finger dvader [mentor.cc.purdue.edu]   Login name: dvade...

In here we will see how to generate files dynamically. The requirement is, Consider i have an employee table with the following fields Name,Salary,Department,ID The files should be generated with employee name as file name and that particular file should have the details of that respective employee only, if the employee has more than one record then both the records should be pulled into the file. We need to create a port in the target instance named as file name we need to create a port present in the right end corner it will be named as "Add FileName column to this table" Create the mapping in the following way In the expression, give the conditions in the following way In transaction control information give the condition as So by this for each new ename a file would be created, the duplicate values for the same ename also would be saved in the same file. Hope this article helps you in understanding dynamic file creation

In Data warehousing, irrespective of schema (snow flake schema or star schema) we are using, we should join dimension and fact tables to analyze the business. Below are the frequently used joins: Inner join Left outer Join Right outer Join Cross join Inner Join: Inner join will give you the matching rows from both the tables. If the join condition is not matching then zero records will return. We should use ON keyword to give join condition. Example: Table1: ID Name 1 Krishna 2 Anirudh 4 Ashok Table2: ID Location 1 Bangalore 3 Chennai 4 Chennai We can join above two tables using inner join based on key column ID. SELECT T1.ID, T1.Name, T2.Location FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ID = T2.ID     If we are using inner join, it will give us matching rows from both the table. Here in this example, we have 2 matching rows i.e. ID 1 and 4. Below will be the result set for the above exa...