Read Sequential File with SCHEMA file

In Datastage, Schemas are an alternative way for you to specify column definitions for the data used by parallel jobs. You do not need to define any column and data format for reading a file. We are using this method when we needed a generic job for scenario like reading multiple files having different metadata defined.

Here, I am going to show you how to read the seq file with help of schema

A. Design

I am using below design to demonstrate this functionality, I am reading the data and dumping it to another file with different format.

Auto Generate Table Schema in DataStage

Few days ago, I got a request to explain how to generate the SCHEMA file of a table ? So here we goes.....

a. Design :

We can generate the SCHEMA file with this job. As we can see there are only two stages. One is DB connector stage ( it can be any DB) and a seq file stage which will hold the SCHEMA definition.

Interview Questions : DataWareHouse - Part6

What is BUS Schema?
BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.

What are the methodologies of Data Warehousing?
Every company has methodology of their own. However, to name a few SDLC Methodology, AIM methodology is standard used.

Interview Questions : DataWareHouse - Part 5

How Many different schemas or DW Models can be used in Siebel Analytics. I know Only STAR and SNOW FLAKE and any other model that can be used?
Integrated schema design is also used to define an integrated schema design we have to define the following concepts

► Fact constellation
► Act less fact table
► Onformed dimension

A: A fact constellation is the process of joining two or more fact tables

B: A fact table with out any facts is known as fact less fact table

C:A dimension which is re useful and fixed is known as conformed dimensionA dimension, which is, shared with multiple fact tables known as conformed dimension

What is an error log table in Informatica occurs and how to maintain it in mapping?
Error Log in Informatica is a one of output file created by Informatica Server while running the session for error messages. It is created in Informatica home directory.

DataStage Parallel job: Retrieve sql codes on a failed upsert

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

Interview Questions : DataWareHouse - Part 4

What are the types of Synonyms?
There are two types of Synonyms Private and Public

What is a Redo Log?
The set of Redo Log files YSDATE, UID, USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What is an Index Segment?
Each Index has an Index segment that stores all of its data.

Explain the relationship among Database, Table space and Data file?
Each databases logically divided into one or more table spaces one or more data files are explicitly created for each table space.

Interview Questions : DataStage - self-3

100    If 1st and 8th record is duplicate then which will be skipped? Can you configure it?
101    How do you import and export datastage jobs? What is the file extension? (See each component while importing and exporting).
102    How do you rate yourself in DataStage?
103    Explain DataStage Architecture?
104    What is repository? What are the repository items?
105    What is difference between routine and transform?
106    When you write the routines?

Interview Questions : DataStage - self-2

48    Why can’t we use sequential file as a lookup?
49    What is data warehouse?
50    What is ‘Star-Schema’?
51    What is ‘Snowflake-Schema’?
52    What is difference between Star-Schema and Snowflake-Schema?
53    What is mean by surrogate key?
54    What is ‘Conformed Dimension’?

Interview Questions : DataWareHouse - Part 2

For more : Visit HERE




What is real time data-warehousing?

Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly.

What are conformed dimensions?

Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. They are common to the cubes.

What is conformed fact?

Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly.

Oracle Interview Questions - Part-3

51. What is a database instance? Explain.
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users. The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

52. What is Parallel Server?
Multiple instances accessing the same database (only in multi-CPU environments)

Schema File in Datastage

Schema files and partial Schemas:

You can also specify the meta data for a stage in a plain text file known as a schema file. This is not stored in the Repository but you could, for example, keep it in a document management or source code control system, or publish it on an intranet site.

List the tables in TeraData

For listing all the tables, views and macro , execute any sql from below as per need


select * from dbc.tables;

List the tables in DB2 LUW & z/OS

List the tables in DB2 LUW

list tables for all

list tables for schema <schema_name>

or

select * from syscat.tables ;

Oracle Interview Questions - Part-2

26. What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

27. When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

list of tables in an Oracle database

To list the tables in the schema of the current user:

SELECT table_name FROM user_tables;
or
select * from tab ;

Oracle Interview Questions - Part-1

1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.

2. What are the components of logical database structure of Oracle database?
There are tablespaces and database’s schema objects.

3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

Optimzie your DataStage Job Performance with relevant Environment Variables

DataStage has many parameters which can be tweaked and used to optimize the performance of various DataStage Jobs. Even many available to collect more information during the event of crash to get more traces.

For any DataStage Job if you run into problem or want to get more details need to check following variables.

Snowflake Schema

The snowflake schema is an extension of the star schema, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. 

Tips & Tricks for debugging a DataStage job

The article talks about DataStage debugging techniques. This can be applied to a job which

• is not producing proper output data or
• to a job that is aborting or generating warnings

1. Use the Data Set Management utility, which is available in the Tools menu of the DataStage Designer or the DataStage Manager, to examine the schema, look at row counts, and delete a Parallel Data Set. You can also view the data itself.
   

star schema

 
In the star schema design, a single object (the fact table) sits in the middle and is radially connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a forieng key in the fact table.