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

1) First difference between View and materialized view is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table. 2) Another difference between View vs materialized view is that, when we create view using any table,  rowid of view is same as original table but in case of Materialized view rowid is different. 3) One more difference between View and materialized view in database is that, In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data. 4) Performance of View is less than Materialized view. 5) View vs Materialized View is that, In case of Materialized view we need extra trigger or some automatic method so that we can keep materialized view  refreshed, this is not required for views in database. When do we go for Materialized View? View is logical view of the table, with no data stored on the disk.It will return the latest da

Informatica components i.e. mappings,session,workflows,transformation are moved from one environment to another environment say from development to SIT can be done in various ways such as --Export the xml's and again import the xml's in the respective environment --Drag and Drop objects from one repository to another repository For the above process to be done you should have required access across environments. One other way is labelling the components and then creating queries in repository manager. Using these queries components are moved across environment's We need to go to the repository manager and select the version tab and select label Then click new label and give a name say "Wf Migration" and click OK. There we  have permissions tab where we can give access to other users to use the same label. Now we need to select the workflow which needs to be promoted to higher environment.  Right click on workflow and select "view dependencies&

As we know we have pretty good number of commands with umpteen options.We will see most command commands which we use in our daily work. CAT v/s MORE: Both CAT & MORE command are primarily used to view the files the difference being that CAT command opens the complete file in a single shot however MORE command opens the file with respect to screen size i.e. if the file is huge we need to hit "space/enter" button to proceed. CAT command is also used to append,create file. cat>file name: Creates file if not present, if present then overrides the data. If we need to append the data then cat>>filename then ctrl+D to save the changes echo$0 to find the current shell ls -lrt will list all the files in a current directory with respect to timestamp ls -a will list all the hidden files ls -lrt | grep '^d' will list out all the directories in a current location gunzip filename this is used for zipping the file for example gz b2.txt will give outp

Generally, we all know that we can create a file using “touch” command in UNIX. If the given file does not exist then “touch” command will create that file, otherwise it will change the modification time of that file. Syntax: touch filename Suppose, in our project, we will get source files with the naming convention “SOURCE_FILE_YYYYMMDD.dat”.   And today we have received SOURCE_FILE_20140117.dat and tomorrow, we will get SOURCE_FILE_20140118.dat etc. Every day we will receive one file. We may have a requirement that to take all the files which are created after a specific time based on file created time from UNIX directory. Step 1: create a dummy file with specific time using TOUCH command. Syntax: touch -t 'YYYYMMDDHH24MM.SS' dummy.dat We should pass the date in YYYYMMDDHH24MM.SS format. Example: 1)       If you give touch -t '201401240134.55' dummy.dat then the dummy file will be created and created time will be Jan 24, 2014. 2)

SED command, We can do a lot of things with it but let's start with basics and let's see where we can use SED in most common places. SED means 'Stream Editor'. when we execute a SED command the output will be redirected on to the interface so in order to save the output use a file to store the redirected results. eg.  sed -n '1p' test.txt > test1.txt Here p is used to print and '1' represents line no. -n used to suppress redundancy. If we use only 'p' option then all the lines will be printed twice. --> To print the first line of the file sed -n '1p' test.txt or sed '2,$d' test.txt Here '2,$d' means deleting all the lines from from second line to the last line. 'd' means delete --->To print first and last line of the file sed  -n '1p;$p' test.txt Here we use ';' to separate two statements. we can use any line no to print and delete just by changing line no's say sed -

Sometimes we may have a requirement where might need to drop indexes/truncate table and then load the data. So in here what we can we do is we can go for pre sql in session properties and there we can write the required the sql statements such as dropping indexes or truncating table and after the loads i.e. session gets completed we can recreate the indexes in post sql command. In mapping also we can do the same but it is advisable to do at session level. We even have option in session such as Presession, Postsession command and Postsession failure command. In session go to components tab to find out these options. Upon requirement we can use the following command to run unix scripts for specific purpose. We have email option option for on success/failure scenarios too. Hope this article helps you in understanding post/pre session commands

Generally, in every project we will see at least one requirement to update target based on some conditions. Case1: When we have primary key defined on table in Database Case2:   When we do not have primary key defined on table in Database Case3: When we want to update target based on non-primary key field. Let’s discuss it in detail. Case1: When we have primary key defined on table in Database After creating table in DB, import table as target along with primary key constraints. After done with importing, we can check the primary key details by editing target in target definition. In this case, we can use update strategy to update target table. Suppose, we have a table called RETURN_CD and primary key ROW_WID defined on that table. Now, we can update target table using update strategy based on primary key. In mapping, we should connect primary key port and the ports which we want to update in target table. Please check the below screenshot. And if we edi

Friends, we knew that primary key is to identify a row in a table and surrogate key is also for same purpose like primary key. But why we need surrogate key and primary key in a table? Let’s discuss it here. Suppose I have two different sources and needs to be loaded only one target from these two sources. Assume, Source1 is Oracle table and Source2 is Flatfile. Field1 is a primary key in both the sources. Suppose you are getting values 1,2 for field1 from source1 and values 1,3 for field1 from source2. When you try to load target from both the sources, it will create duplication of primary and it will throw “primary key constraint violation” error. So we will go for surrogate key concept. I think, this is very useful information because many people face this question in interviews. If you have any questions, please feel free to write to kkrishnareddychp@gmail.com

1)       If you want to join a table using INNER JOIN, Do it as a first step in SQL. 2)       Whenever you join tables, give join conditions in below order                                 I.             Condition on Integer (number) fields                               II.             Condition on String (Character) fields                             III.             Range conditions (Like >=, <=, BETWEEN, IN etc.) 3)       If you want to join a table using LEFT OUTER JOIN, Do it after INNER JOIN in SQL. 4)       Should not use functions in join conditions (like CASE, SUBSTR, COALESCE etc.). 5)       Add filter condition (WHERE clause) to eliminate unwanted data. 6)       Use derived queries to filter data before joining with another table. 7)       Create an index on most frequently used columns on table. 8)       Collect statistics on index fields and join columns. 9)       Select only useful fields in SELECT clause. Example: SELECT (Useful fie

Requirement: Finding duplicates and removing duplicate records by retaining original record in TERADATA Suppose I am working in an office and My boss told me to enter the details of a person who entered in to office. I have below table structure. Create Table DUP_EXAMPLE ( PERSON_NAME VARCHAR2(50), PERSON_AGE INTEGER, ADDRS VARCHAR2(150), PURPOSE VARCHAR2(250), ENTERED_DATE DATE ) If a person enters more than once then I have to insert his details more than once. First time, I inserted below records. INSERT INTO DUP_EXAMPLE VALUES('Krishna reddy','25','BANGALORE','GENERAL',TO_DATE('01-JAN-2014','DD-MON-YYYY')) INSERT INTO DUP_EXAMPLE VALUES('Anirudh Allika','25','HYDERABAD','GENERAL',TO_DATE('01-JAN-2014','DD-MON-YYYY')) INSERT INTO DUP_EXAMPLE VALUES('Ashok Vunnam','25','CHENNAI','INTERVIEW',TO_DATE('01-JAN-2014',