What happen when Import Sequential File with Extra Column

Job reads sequential file and load into RDBMS table.

What happen if sequential file has extra column in the back of each record?

It depends on data type of last column defined by DS and RDBMS table.

• If data type of last column = varchar, extra column will be included as last column.

Read Fixed Width Flat File with Sequential File Stage

If we have a fixed width Flat file, we can read it via Sequential File stage only, no need to use Import Stage.   For reading a Fixed Width Flat file, you should know the format of Fixed width data. How are the data splitting into two or more output columns.

Here, I demonstrate How to read a Fixed Width Flat file from Sequential File Stage

A. Input File :

Below is the Input file which we are going to use in our job. This file contains a column RollNo which is concatenation of 4 fixed width columns which we have to split and read.

DataStage Scenario - Design10 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem10 

Solution Design :

a) Job Design :  

   Below is the design which can achieve the output as we needed. In this design, we are reading the data from flat file, generating a dummy column in column generator stage, doing aggregate on that and sending the output to seq file.

DataStage Scenario - Design9 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem9

Solution Design :

a) Job Design :   
Below design will achieve the output as per our requirement.

DataStage Scenario - Design8 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem8

Solution Design :

a) Job Design : 
Below is the design which can achieve the output as we needed. Here, we are reading 2 seq file as a input, then data is passing through a Join and Filter stage to achieve the output.

DataStage Scenario - Design7- job1

DataStage Scenario Problem -->  DataStage Scenario - Problem7

Solution Design :

a) Job Design :  Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer and Peek stage to achieve the output.

DataStage Scenario - Design6 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem6

  

Solution Design :

a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through a Transformer stage to achieve the output.

DataStage Scenario - Design2 - job3

DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :

DataStage Scenario - Design2 - job2

DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :
In job design, we are using Copy, Aggregator, Filter and Join stage to get the output.

DataStage Scenario - Design 2 - job1

 DataStage Scenario Problem -->  DataStage Scenario - Problem2

Solution Design :


a) Job Design :

Below is the design which can achieve the output as we needed. Here, we are reading seq file as a input, then data is passing through Aggregator and Filter stage to achieve the output.
 

List of Environment Variables in DataStage

General Job Administration

APT_CHECKPOINT_DIR
APT_CLOBBER_OUTPUT
APT_CONFIG_FILE
APT_DISABLE_COMBINATION
APT_EXECUTION_MODE
APT_ORCHHOME
APT_STARTUP_SCRIPT
APT_NO_STARTUP_SCRIPT
APT_STARTUP_STATUS
APT_THIN_SCORE

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’?

Dummy Data Generation using Row Generator in DataStage - 2

By default the Row Generator stage runs sequentially, generating data in a single partition. You can, however, configure it to run in parallel and meaningful data.

We are using the same job design as in Dummy Data Generation using Row Generator in DataStage - 1


a) Job Design :

Dummy Data Generation using Row Generator in DataStage - 1

How to Generate Input Data for your dummy jobs n practice ??

In DataStage, There is a Stage called "Row Generator" under "Devlopment/Debug Stages" category. For Generating the dummy data,  we will use this stage.

So, We are going to create a job which generates dummy data.

14 design tips for better performance in Datastage

1) Avoid unnecessary type conversions: set the OSH_PRINT_SCHEMAS environment variable to verify that run time schemas match the job design column definitions. If you are using stage variables on a Transformer stage, ensure that their data types match the expected result types.

2) Use Transformer stages sparingly and wisely. Transformer stages can slow down your job. Do not have multiple stages where the functionality could be incorporated into a single stage, and use other stage types to perform simple transformation operations

Environment Variable for Data Stage Best Practices and Performance Tuning

DataStage provides a number of environment variables to control how jobs operate on a UNIX system.  In addition to providing required information, environment variables can be used to enable or disable various DataStage features, and to tune performance settings. 

Data Stage Environment Variable Settings for All Jobs

DataSet, FileSet and Seq File in DataStage

Seq File:

Extract/load from/to seq file max 2GB { Its depends on OS property, Now most of the OS supports greater than 2 GB }

when used as a source at the time of compilation it will be converted into native format from ASCII

does not support null values

A seg file can only be accessed on one node.