Activity Code – Teradata Stored Procedure

ACTIVITY_COUNT
          The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application.
Example :
SELECT MBR_ID INTO V_MBR_ID FROM MEMEBER WHERE YEAR(ENROLLMENT_DATE ) = 2016
IF ACTIVITY_COUNT > 0       then   <<<<  our statement >>>>>>>>
/*  If Activity_code > 0  then, i.e.,  We are having valid values from the above select query  */

Create stored procedure in Teradata

Here is the example of simple stored procedure with only one output variable.

Create procedure Test (out SubID Varchar(100))

Begin

Select top 1 Sub_ID into SubID from Member;

End ;

Test – Stored Procedure Name

Out – To specify the output parameter.

To Execute the Stored procedure in Teradata,

CALL Test(SubID);

Create MDC Tables in DB2

We can create a Multi Dimensional clustering table in DB2. Mostly the tables are used in the data warehousing.  If a table contain the large amount of data then we can convert the table in to the MDC Table. If we convert it then it will respond quickly.

Syntax

CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE)
   ORGANIZE BY DIMENSIONS (c1, c3, c4)

In the above example the clustering created based on the three dimensions, c1,c3,c4.

We can create the clustering based on the combination of number of columns.

Syntax.

CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE)

   ORGANIZE BY DIMENSIONS (c1, (c3, c4))

In the above example, two clustering was created one is on dimension c1, and another one is on c3 and c4. Combination of c3 and c4 creates a single clustering.

Operations Behind Alter Table in DB2

We are frequently using alter table queries in our daily operations. The the list of operations running behind the single alter query.

  1. Alter Table Procedure called “SYSPROC.ALTOBJ” .  This procedure called from DB2 Design Advisor.
  2. Drop all dependent objects of the table.
  3. Rename the table
  4. Create the table using the new definition
  5. Recreate the all dependent objects of the table.
  6. Transform the existing table data  into the new table.   If any of the transformations are required while moving, the transformation logics are implemented in the data moving process.  The transformations are required for the data type changes.

DB2 Table Types

For easier table design and faster query performance we have some types of tables in DB2.  Choosing the right table design we can optimize the query performance.  

  1. Regular Tables.
  2. Append Mode tables
  3. Multidimensional Clustering tables.
  4. Range cluster Tables.
  5. Partitioned tables.

Regular Tables.

            This kind of table is normally used in DB2.  These kind of tables we can use indexes.

Append Mode Tables.

            This kind of tables are used when the tables will involve in more number of transactions per day.  The table support faster  insert and update operations.

Multidimensional Clustering tables.

            This kind of tables support number of cluster indexes.  We can use number of dimensions for cluster indexing.   Mostly this kind of design will be used in the Data warehousing. The larger amount of data handling will be easier when we uses this table design.

Partitioned tables.

            This tables design will be used when the number of indexes are tightly coupled with the table.

Show List of Tables in DB2

Here I share the query which used to get the list of tables from DB2.

select * from syscat.tables where type = ‘T’

Here you can change the type of the table. The type would be ‘T’, ‘S’,’V’. 

The above query will give the entire details about the table.

Informatica Interview questions

Which transformation should we use to normalize the COBOL and relational sources?

Normalizer Transformation. Normalizer Transformation.

Normalizer Transformation.When we drag the COBOL source in to the mapping Designer workspace,the normalizer transformation automatically appears,creating input and output ports for every column in the source.

Difference between static cache and dynamic cache?

In case of Dynamic catche when you are inserting a new row it looks at the lookup catche to see if the row existing or not,If not it inserts in the target and catche as well in case of Static catche when you are inserting a new row it checks the catche and writes to the target but not catche

If you cache the lookup table, you can choose to use a dynamic or static cache. By default, the lookup cache remains static and does not change during the session. With a dynamic cache, the Informatica Server inserts or updates rows in the cache during the session. When you cache the target table as the lookup, you can look up values in the target and insert them if they do not exist, or update them if they do.

The information you are posting should be related to java and ORACLE technology. Not political.