How to convert fixed width file to delimeted file in UNIX

Ex: awk -v OFS=, '{ print substr($0, 1, 1), substr($0, 2, 10), substr($0, 12, 4), substr($0, 16, 2), substr($0, 18, 2) }' FIX_COMMA.txt > CSV.txt

Find the Missing Values from Expected Values - Oracle/SQL

SELECT STATE
FROM
(
SELECT 'KS' AS STATE FROM DUAL UNION
SELECT 'WA' FROM DUAL UNION
SELECT 'OH' FROM DUAL UNION
SELECT 'MI' FROM DUAL UNION
SELECT 'IN' FROM DUAL UNION
SELECT 'AZ' FROM DUAL UNION
SELECT 'CO' FROM DUAL UNION
SELECT 'MO' FROM DUAL UNION
SELECT 'IA' FROM DUAL UNION
SELECT 'IL' FROM DUAL UNION
SELECT 'ND' FROM DUAL UNION
SELECT 'TX' FROM DUAL UNION
SELECT 'WI' FROM DUAL UNION
SELECT 'MN' FROM DUAL UNION
SELECT 'OR' FROM DUAL
) A
where STATE not in ( select STATE from SCHEMA.TABLE )

or You can Create table with Above rows and select the State from the that table.

Extract a String from a file name in unix

echo "BW-CDR-20160712000000-2-005056896A77-210486.csv" | cut -f 1 -d '.' | awk -F '-' '{print $6}'

Result ; 210486

echo "MCLD2BUL.T60718IL02.rdy" | awk -F '.' '{print $2}' | cut -c 7-8

Result ;  IL

Setup ODBC Connection for DataStage to Access on Windows Server

To Access any database from DataStage you will need a ODBC connection.


To Create a ODBC Connection in Windows Server 2008 R2 , follow the below steps.

For 32 Bit OS :

• 
  
• Navigate to Folder - C:\Windows\System32
• Double Click odbcad32.exe
• ODBC Administrator window opens
• Click System DSN Tab
• Click Add
• Then Follow Steps , when done click Finish

For 64 bit OS:

• Navigate to Folder - C:\Windows\SysWOW64
• Double Click odbcad32.exe ( Yes , its odbcad32.exe - its not a typo )
• ODBC Administrator window opens
• Click System DSN Tab
• Click Add
• Then Follow Steps , when done click Finish

How to map DataStage user Credentials to Operating system Credentials

If The DataStage User is not mapped to the Operating system user where the engine tier components are installed , DataStage user will have lot of problems accessing Remote Servers or Company Network Drives.

Example Situaltion : When i was trying to copy some files from the Network Drive using the script that is running from DataStage Parallel Job , it failed with an below error message.

Error Message :  \\network\drive : Logon failure: unknown user name or bad password. 

But when i run the same script from command prompt it ran fine.

Then i figured user credentials are not mapped correctly or not mapped at all. I followed instruction from the below IBM links and it worked fine.

https://www.ibm.com/support/knowledgecenter/SSPT3X_2.1.2/com.ibm.swg.im.iis.found.moz.wc.admin.doc/topics/wsisinst_config_user_mappings.html

1. Log in to the IBM InfoSphere Information Server Web console by using Administrator credentials.
2. On the Administration tab, expand the Domain Management section and click Engine Credentials.
3. Select the InfoSphere Information Server engine for which you want to map user credentials.
4. Click Open User Credentials.
5. Click Browse to search for suite users.
6. Optional: Specify additional search criteria, and click Filter to display a list of users.
7. From the search results, select the suite users that you want to map to the engine tier operating system local credentials and click OK.
8. On the Map User Credentials pane, select one or more users to map to the credentials. If you want to map some suite users to one user and map other suite users to a different user, select one subset of users and continue.
9. In the Assign User Credentials pane, specify the local operating system user credentials. The user name and password that you provide must be a valid user name and password for the operating system where the engine tier components are installed. If you want to preserve credential mappings that users have already configured, select the Apply Only to Users without Credentials check box.
10. Click Apply.

How to remove extra delimiters using DataStage

Input Sample Data:

Order_Number
----------------
C67330672,
C97652762,C67330672,
C67330672,C67330672,C67330672,
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445

To Eliminate extra delimiters [commas ","] in above first 3 rows used below process.

Defined Transformer Stage Variables:

svDCOUNT =  Dcount(Order_Number,",")
     svFIELD =  Field(Order_Number,",", svDCOUNT)
  svISNULL =  If Trim(svFIELD)='' Then 0 Else 1
svLENGTH =  Len(Order_Number)-1


Then in Derivation :

If svISNULL=0 Then Order_Number[1, svLENGTH] Else Order_Number


Output:
----------------------

Order_Number
----------------
C67330672
C97652762,C67330672
C67330672,C67330672,C67330672
C38750605
C43446335,C21659445
C21659445,C21659445,C21659445

Difference between Oracle connector and Oracle Enterprise Stage

-Oracle Enterprise stage runs in sequential mode when used as source but connector can be run in parallel using different parallel read options. 

-Also connector is said to be faster than Enterprise stage. 

-Connection can be saved and can be reused. 

-Error/rejects can be captured with an error code and error text based on certain conditions in connector when used in target.

Assigning a Particular Node Or Resource Pool to a Stage

Specify node map or node pool or resource pool constraints. The configuration file allows you to set up pools of related nodes or resources. The Advanced tab allows you to limit execution of a stage to a particular node or resource pool. You can also use a map to specify a group of nodes that execution will be limited to just in this stage. Supply details as follows:

Node pool and resource constraints. Specify constraints in the grid. Select Node pool or Resource pool from the Constraint drop-down list. Select a Type for a resource pool and, finally, select the name of the pool you are limiting execution to. You can select multiple node or resource pools. This is only enabled if you have defined multiple pools in the configuration file.

Node map constraints. Select the option box and type in the nodes to which execution will be limited in the text box. You can also browse through the available nodes to add to the text box. Using this feature conceptually sets up an additional node pool which does not appear in the configuration file.

The lists of available nodes, available node pools, and available resource pools are derived from the configuration file.

Source: IBM

ORA-01502 Oracle

Error Message :

The OCI function OCIStmtExecute returned status 1. Error code: 1502, Error message: ORA-01502: index 'INDEX_NAME' or partition of such index is in unusable state.

You will get above error messages in Datastage job or toad/sql developer when you are trying insert duplicate records into a table.

Solution :

1) check the index status in table by going to Index tab.
2) If it is in "UNUSABLE" state then you need to rebuild the index using below alter statement :

ALTER INDEX INDEX_NAME REBUILD;

Then run your job or insert sql.

Debug in Datastage 8.7

Step 1 : Right click on the link where you want to create a break point       

  Step 2 : Then click on Toggle Breakpoint                                                          

Step 4 :  Then From the Menu bar click "Debug" then click "Go".

Then Job Run window and Debug window will appear, then provide required parameter values and click ok on Jon Run window.

It will show like below when Debug is running

Next it will show up the first row values like below.We can change number of rows you want to see by clicking "Edit Break Point" from the "Menu > Debug " or by right clicking on the "Break Point" that we created in Step 1.

How to search only for numbers in a column in Oracle : rtrim

 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE not rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating the numbers:

1gnec16z15j129947
abcdefghijklmnop
jtmbk31v076017323

*************************************************
 with test as
(select '1gnec16z15j129947' vin from dual union all
 SELECT '99999999999999999' vin FROM DUAL UNION ALL
 SELECT '00000000000000000' vin FROM DUAL UNION ALL
 SELECT '11111111111111111' vin FROM DUAL UNION ALL
  select 'abcdefghijklmnop' vin from dual union all
 select 'jtmbk31v076017323' vin from dual
 )
 SELECT  * FROM TEST WHERE rtrim(vin, '0123456789') is null


OUTPUT :

below is the output of the above query which is eliminating rows other than numbers.

99999999999999999
00000000000000000
11111111111111111

What is UNICODE ??

Unicode Provides unique number for every character  no matter what the language,program and platform.

Difference between Unicode and UTF : 

vi editor commands

To locate instances of particular sets of characters (or strings), use the following commands.

/string       search forward for occurrence of string in text
?string      search backward for occurrence of string in text
n               move to next occurrence of search string
N              move to next occurrence of search string in opposite direction
u               undo
U              undo all the changes

Replace
------------
:%s/oldword/newword/g
:%s/^M//g                           ----  to replace control-m characters (press ctrl-v& ctrl-m )

look for special characters:
---------------------------------
:set list

Unix ksh won't execute and gives error 127

If you get the error : 127 when you execute the shell script.

There might be one of these 2 issues :

1) shebong line is wrong
2) you might have copied the file from windows to unix - that will create a control/special characters at end of the line.

SOLUTION :
execute the below command

head script.ksh | cat -vet | head -1

If you see "^M" at the end of the output - then you need to remove that special character by either using "dos2unix" command or using cat command like below :

1)  cp padaddwip.ksh padaddwip.ksh.bak
     dos2unix padaddwip.ksh.bak > padaddwip.ksh

2)  cat script.ksh.bak | tr -d "\r" > script.ksh

SET SYSTEM VARIABLES - ORACLE/SQL PLUS

SET DEF[INE] {& | c | ON | OFF}

Sets the charavter used to prefix substitution variables to c.

ON or OFF controls whether SQL * Plus will scan commands for substitution variables and replace them with their values.

Example :

sql > SET DEFINE ON

        SELECT 'HELLO & ?' FROM DUAL;

       ? = WORLD   (IT WILL ASK FOR USER INPUT,HERE THE 'WORLD' IS A USER INPUT)

       OUTPUT : "HELLO WORLD"


sql > SET DEFINE OFF
        SELECT 'HELLO & ?' FROM DUAL;

        (IT WON'T ASK FOR USER INPUT AS THE DEFINE IS OFF)

        OUTPUT : "HELLO & ?"

Difference Between Normal Lookup and Sparse Lookup

• Normal Lookup data needs to be in memory
• Normal might provide poor performance if the reference data is huge as it has to put all the data in memory.
• Normal Lookup can have more than one reference link.
• Normal lookup can be used with any database.

• Sparse Lookup directly hits the database.
• If the input stream data is less and reference data is more like 1:100 or more in such cases sparse lookup is better.
• Sparse Lookup,we can only have one reference link.
• Sparse lookup,we can only use for Oracle and DB2.
• Sparse lookup sends individual sql statements for every incoming row.(Imagine if the reference data is  huge).

This Lookup type option can be found in Oracle or DB2 stages.Default is Normal.

When do you use Snowflake Schema Implementation?

Ralph Kimball, the data warehousing guru, proposes three cases where snowflake implementation is not only acceptable but is also the key to a successful design:

• Large customer dimensions where, for example, 80 percent of the fact table measurements involve anonymous visitors about whom you collect little detail, and 20 percent involve reliably registered customers about whom you collect much detailed data by tracking many dimensions.

• Financial product dimensions for banks, brokerage houses, and insurance companies, because each of the individual products has a host of special attributes not shared by other products.

• Multi enterprise calendar dimensions because each organization has idiosyncratic fiscal periods,seasons, and holidays.

Ralph Kimball recommends that in most of the other cases, star schemas are a better solution. Although redundancy is reduced in a normalized snowflake, more joins are required. Kimball usually advises that it is not a good idea to expose end users to a physical snowflake design, because it almost always compromises understandability and performance.

Difference Between Master Data Management(MDM) and Data warehouse

Master Data Management and Data Warehousing have a lot in common. For example, the effort of data transformation and cleansing is very similar to an ETL process in data warehousing, and in fact they can use the same ETL tools. In the real world, it is not uncommon to see MDM and data warehousing fall into the same project. On the other hand, it is important to call out the main differences between the two:


1) Different Goals

The main purpose of a data warehouse is to analyze data in a multidimensional fashion, while the main purpose of MDM is to create and maintain a single source of truth for a particular dimension within the organization. In addition, MDM requires solving the root cause of the inconsistent metadata, because master data needs to be propagated back to the source system in some way. In data warehousing, solving the root cause is not always needed, as it may be enough just to have a consistent view at the data warehousing level rather than having to ensure consistency at the data source level.


2) Different Types of Data

Master Data Management is only applied to entities and not transactional data, while a data warehouse includes data that are both transactional and non-transactional in nature. The easiest way to think about this is that MDM only affects data that exists in dimensional tables and not in fact tables, while in a data warehousing environment includes both dimensional tables and fact tables.


3) Different Reporting Needs

In data warehousing, it is important to deliver to end users the proper types of reports using the proper type of reporting tool to facilitate analysis. In MDM, the reporting needs are very different -- it is far more important to be able to provide reports on data governance, data quality, and compliance, rather than reports based on analytical needs.


4) Where Data Is Used

In a data warehouse, usually the only usage of this "single source of truth" is for applications that access the data warehouse directly, or applications that access systems that source their data straight from the data warehouse. Most of the time, the original data sources are not affected. In master data management, on the other hand, we often need to have a strategy to get a copy of the master data back to the source system. This poses challenges that do not exist in a data warehousing environment. For example, how do we sync the data back with the original source? Once a day? Once an hour? How do we handle cases where the data was modified as it went through the cleansing process? And how much modification do we need make do to the source system so it can use the master data? These questions represent some of the challenges MDM faces. Unfortunately, there is no easy answer to those questions, as the solution depends on a variety of factors specific to the organization, such as how many source systems there are, how easy / costly it is to modify the source system, and even how internal politics play out.

Oracle SQL Hints

/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */

Difference Between UNION and UNION ALL

The Main Difference is

UNION returns the data without duplicates
UNION ALL returns the data with duplicates if there any.

UNION is costly as it has to check for redundancy
UNION ALL is faster.

Difference Between Delete,Truncate and Drop

Delete :

• Delete is a DML command.
• Delete command uses undo table space for roll back.
• deletes can be undo by using Rollback.
• Delete command can be used to delete few rows from table or all rows from table.
• After the Delete command need to use the  Commit or Rollback command to permanent the transaction.

Truncate ;

• Truncate is a DDL command.
• Truncate removes all rows from a table.
• truncate wont use any table space and triggers will be filred.
• It cannot be Rolled Back.
• It's faster than Delete.

Drop :

• Drop removes the data and the table structure from the Database.
• Operation cannot be rolled back.
• No DML trigger will be fired.

How to run the UNIX Processes in background

using "&" symbol (ampersand) at the end of the command we can run the processes in the background.

Ex :  sleep 100 &

it will give the PID and leave the terminal for other work.we can see the background process by using the "ps" command.

Copy Stage - Force Option

Copy Stage :  Stage->Properties have option called Force

Force : true or False.

True to specify that DataStage should not try to optimize the job by removing the Copy operation.

False to specify that Datastage should try to optimize the job (it might remove the copy operator or might not).

Conductor Node in Datastage

Below is the sample APT CONFIG FILE ,see in bold to mention conductor node.


{
node "node0"
{
fastname "server1"
pools "conductor"
resource disk "/datastage/Ascential/DataStage/Datasets/node0" {pools "conductor"}
resource scratchdisk "/datastage/Ascential/DataStage/Scratch/node0" {pools ""}
}
node "node1"
{
fastname "server2"
pools ""
resource disk "/datastage/Ascential/DataStage/Datasets/node1" {pools ""}
resource scratchdisk "/datastage/Ascential/DataStage/Scratch/node1" {pools ""}
}
node "node2"
{
fastname "server2"
pools ""
resource disk "/datastage/Ascential/DataStage/Datasets/node2" {pools ""}
resource scratchdisk "/datastage/Ascential/DataStage/Scratch/node2" {pools ""}
}
}

Please find the below different answers :
------
For every job that starts there will be one (1) conductor process (started on the conductor node), there will be one (1) section leader for each node in the configuration file and there will be one (1) player process (may or may not be true) for each stage in your job for each node. So if you have a job that uses a two (2) node configuration file and has 3 stages then your job will have

1 conductor
2 section leaders (2 nodes * 1 section leader per node)
6 player processes (3 stages * 2 nodes)

Your dump score may show that your job will run 9 processes on 2 nodes.

This kind of information is very helpful when determining the impact that a particular job or process will have on the underlying operating system and system resources.

-----
Conductor Node :
It is a main process to

1.  Start up jobs
2.  Resource assignments
3.  Responsible to create Section leader (used to create & manage player player process which perform actual job execution).
4.  Single coordinator for status and error messages.
5.  manages orderly shutdown when processing completes in the event of fatal error.

-----
Jobs developed with DataStage EE and QualityStage are independent of the actual hardware and degree of parallelism used to run the job. The parallel Configuration File provides a mapping at runtime between the job and the actual runtime infrastructure and resources by defining logical processing nodes.


To facilitate scalability across the boundaries of a single server, and to maintain platform independence, the parallel framework uses a multi-process architecture.

The runtime architecture of the parallel framework uses a process-based architecture that enables scalability beyond server boundaries while avoiding platform-dependent threading calls. The actual runtime deployment for a given job design is composed of a hierarchical relationship of operating system processes, running on one or more physical servers

• Conductor Node (one per job): the main process used to startup jobs, determine resource assignments, and create Section Leader processes on one or more processing nodes. Acts as a single coordinator for status and error messages, manages orderly shutdown when processing completes or in the event of a fatal error. The conductor node is run from the primary server
• Section Leaders (one per logical processing node): used to create and manage player processes which perform the actual job execution. The Section Leaders also manage communication between the individual player processes and the master Conductor Node.
• Players: one or more logical groups of processes used to execute the data flow logic. All players are created as groups on the same server as their managing Section Leader process.

-----

When the job is initiated the primary process (called the “conductor”) reads the job design, which is a generated Orchestrate shell (osh) script. The conductor also reads the parallel execution configuration file specified by the current setting of the APT_CONFIG_FILE environment variable.

Once the execution nodes are known (from the configuration file) the conductor causes a coordinating process called a “section leader” to be
started on each; by forking a child process if the node is on the same machine as the conductor or by remote shell execution if the node is on a
different machine from the conductor (things are a little more dynamic in a grid configuration, but essentially this is what happens). Each section
leader process is passed the score and executes it on its own node, and is visible as a process running osh. Section leaders’ stdout and stderr are
redirected to the conductor, which is solely responsible for logging entries from the job.


The score contains a number of Orchestrate operators. Each of these runs in a separate process, called a “player” (the metaphor clearly is one of an
orchestra). Player processes’ stdout and stderr are redirected to their parent section leader. Player processes also run the osh executable.

Communication between the conductor, section leaders and player processes in a parallel job is effected via TCP.

Difference between scratch disk and resource scratch disk

The Only difference is :

• Scratch Disk is for Temporary storage (Like RAM in our PC)

Ex : Files created during the process between the source and targtet such as Sort,Remove duplicate,Aggregator etc..

• Resource Scratch Disk is for Permanent storage (like a Hard Drice in our PC)

Ex : Data sets,files sets, Loookup file sets etc..

Common Errors,warnings in Datastage

• Warning ; A sequential operator cannot preserve the partitioning of input data set on input port 0

          Sol : Clear the partitoning

• Warning : Agg_stg: When checking operator: When binding input interface field “column1” to field “ column1 ”: Implicit conversion from source type “string[5]” to result type “dfloat”: Converting string to number.

          Sol: use data type conversion

• Warning ; oci_oracle_source: When checking operator: When binding output interface field “column1” to field “column1”: Converting a nullable source to a non-nullable result;

         Sol : Use Null functions

Initial Load and Delta Load

Difference Between Initial Load and Delta Load :

Initial Load :

Ø       Occurs Once

Ø       Large amount of Data

Delta Load :

If the data service has the capability to return the data modified only after a specified date and time, the ETL process will load only the data modified after the last successful load. This is called delta load

Ø       Occurs regularly

Ø       Adjustments to Initial load

Ø       Small amount of data

Information Analyzer Introduction

sed unix

awk unix

NR = Number or Records read so far
NF = Number of Fields in current record
FS = the Field Separator
RS = the Record Separator
BEGIN = a pattern that's only true before processing any input
END = a pattern that's only true after processing all input.