NLS settings to Trademark symbol in Datastage

Changes made in the job:

Step 1:

Added parameter NLS_LANG with default value as ‘AMERICAN_AMERICA.AL32UTF8’

Step 2:

Changed the NLS from project default(ISO_8859) to ‘ASCL_MS1252’

Step 3:

Changed the column metadata to ‘Unicode’

Target:

 Should be able to see TM symbols

Datastage Debugging

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 theTools 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.

2. Check the DataStage job log for warnings or abort messages. These may indicate anunderlying logic problem or unexpected data type conversion. Check all the messages. The PX jobs almost all the times, generate a lot of warnings in addition to the problem area.

3. Run the job with the message handling (both job level and project level) disabled to find out if there are any warning that are unnecessarily converted to information messages or dropped from logs.

4. Enable the APT_DUMP_SCORE using which you would be able see how different stages are combined. Some errors/logs mentioned the error is in APT_CombinedOperatorController stages. The stages that form the part of the APT_CombinedOperatorController can be found using the dump score created after enabling this env variable.
   This environment variable causes the DataStage to add one log entry which tells how stages are combined in operators and what virtual datasets are used. It also tells how the operators are partitioned and how many no. of partitions are created.

5. One can also enable APT_RECORD_COUNTS environment variables. Also enable OSH_PRINT_SCHEMAS to ensure that a runtime schema of a job matches the design-time schema that was expected.

6. Sometimes the underlying data contains the special characters (like null characters) in database or files and this can also cause the trouble in theexecution. If the data is in table or dataset, then export it to a sequential file (using DS job). Then use the command “cat –tev” or “od –xc” to find out the special characters.

7. Once can also use “wc -lc filename”, displays the number of lines and characters in the specified ASCII text file. Sometime this is also useful.

8. Modular approach: If the job is very bulky with many stages in it and you are unable to locate the error, the one option is to go for modular approach. In this approach, one has to do the execution step by step. E.g. If a job has 10 stages, then create a copy of the job. Just keep say first 3 stages and run the job. Check the result and if the result is fine, then add some more stages (may be one or two) and again run the job. This has to be done till one is unable to locate the error.

9. Partitioned approach with data: This approach is very useful if the job is running fine for some set of data and failing for other set of data, or failing for large no. of rows. In this approach, one has to run the jobs on selected no .of rows and/or partitions using the DataStage @INROWNUM (and @PARTITIONNUM in Px). E.g. a job when run with 10K rows works fine and is failing with 1M rows. Now one can use @INROWNUM and run the job for say first 0.25 million rows. If the first 0.25 million are fine, then from 0.26 million to 0.5 million and so on.
   Please note, if the job parallel job then one also has to consider the no. of partitions in the job.

10. Other option in such case is – run the job only one node (may be by setting using APT_EXECUTION_MODE to sequential or using the config file with one node.

11. Execution mode: Sometime if the partitions are confusing, then one can run the job in sequential mode. There are two ways to achieve this:
1. Use the environment variable APT_EXECUTION_MODE and set it to sequential mode.
2. Use a configuration file with only one node.

12. A parallel Job fails and error do not tell which row it has failed for: In this case, if this job is simple we should try to build the server job and run it. The server jobs can report the errors along with the rows which are in error. This is very useful in case when DB errors like primary/unique violation or any other DB error is reported by PX job.

13. Sometimes when dealing when DB and if the rows are not getting loaded as expected, adding the reject links to the DB stages can help us locating the rows with issues.

14. In a big job, adding some intermediate datastes/peek stages to find out the data values at certain levels can help. E.g. if there 10 stages and after that it is going to dataset. Now there may be different operations done at different stages. After 2/3 stages, add peek stages or send data to datasets using copy stages. Check the values after at these intermediate 

InfoSphere DataStage Jobstatus returned Codes from dsjob

Equ DSJS.RUNNING To		0		This is the only status that means the job is actually running
Equ DSJS.RUNOK To		1		Job finished a normal run with no warnings
Equ DSJS.RUNWARN To		2		Job finished a normal run with warnings
Equ DSJS.RUNFAILED To		3		Job finished a normal run with a fatal error
Equ DSJS.QUEUED To		4		Job queued waiting for resource allocation
Equ DSJS.VALOK To		11		Job finished a validation run with no warnings
Equ DSJS.VALWARN To		12		Job finished a validation run with warnings
Equ DSJS.VALFAILED To		13		Job failed a validation run
Equ DSJS.RESET To		21		Job finished a reset run
Equ DSJS.CRASHED To		96		Job has crashed
Equ DSJS.STOPPED To		97		Job was stopped by operator intervention (can't tell run type)
Equ DSJS.NOTRUNNABLE To		98		Job has not been compiled
Equ DSJS.NOTRUNNING To		99		Any other status

Warning : Ignoring duplicate entry at table record no further warnings will be issued for this table

This warning is seen when there are multiple records with the same key column is present in the reference table from which lookup is done. Lookup, by default, will fetch the first record which it gets as match and will throw the warning

since it doesn’t know which value is the correct one to be returned from the reference.

To solve this problem you can either one of the reference links from  “Multiple rows returned from link” dropdown, in Lookup constraints. In this case Lookup will return multiple rows for each row that is matched.

Else use some method to eradicate duplicate multiple rows with same key columns according to the business requirements.

How to replace ^M character in VI editor/sed?

^M is DOS line break character which shows up in UNIX files when uploaded from a windows file system in ascii format.

To remove this, open your file in vi editor and type

:%s/(ctrl-v)(ctrl-m)//g

and press Enter key.

Important!! – press (Ctrl-v) (Ctrl-m) combination to enter ^M character, dont use “^” and M.

If anything goes wrong exit with q!.

Also,

Your substitution command may catch more ^M then necessary. Your file may contain valid ^M in the middle of a line of code for example. Use the following command instead to remove only those at the very end of lines:

:%s/(ctrl-v)(ctrl-m)*$//g

Using sed:

sed -e "s/^M//g" old_file_name > new_file_name

How to convert a single row into multiple rows ?

Below is a screenshot of our input data

City	State	Name1	Name2	Name3
xy	FGH	Sam	Dean	Winchester

We are going to read the above data from a sequential file and transform it to look like this

City	State	Name
xy	FGH	Sam
xy	FGH	Dean
xy	FGH	Winchester

So lets get to the job design

Step 1: Read the input data

Step 2: Logic for Looping in Transformer

In the adjacent image you can see a new box called Loop Condition. This where we are going to control the loop variables.

Below is the screenshot when we expand the Loop Condition box

The Loop While constraint is used to implement a functionality similar to “WHILE” statement in programming. So, similar to a while statement need to have a condition to identify how many times the loop is supposed to be executed.

To achieve this @ITERATION system variable was introduced. In our example we need to loop the data 3 times to get the column data onto subsequent rows.

So lets have @ITERATION <=3

Now create a new Loop variable with the name LoopName 

The derivation for this loop variable should be

If @ITERATION=1 Then DSLink2.Name1 Else If @ITERATION=2 Then DSLink2.Name2 Else DSLink2.Name3

Below is a screenshot illustrating the same

Now all we have to do is map this Loop variable LoopName to our output column Name

Lets map the output to a sequential file stage and see if the output is a desired.

After running the job, we did a view data on the output stage and here is the data as desired.

Making some tweaks to the above design we can implement things like

• Adding new rows to existing rows
• Splitting data in a single column to multiple rows and many more such stuff..

How to perform aggregation using a Transformer

 Input:Below is the sample data of three students, their marks in two subjects, the corresponding grades and the dates on which they were graded.

Output:Our requirement is to sum the marks obtained by each student in a subject and display it in the output

Step 1: Once we have read the data from the source we have to sort data on our key field. In our example the key field is the student name

Once the data is sorted we have to implement the looping function in transformer to calculate the aggregate value

Before we get into the details, we need to know a couple of functions

• SaveInputRecord(): This function saves the entire record in cache and returns the number of records that are currently stored in cache
• LastRowInGroup(input-column): When a input key column is passed to this function it will return 1 when the last row for that column is found and in all other cases it will return 0
  To give an example, lets say our input is

  Student	Code
  ABC	1
  ABC	2
  ABC	3
  DEF	2

  For the first two records the function will return 0 but for the last record ABC,3 it will return 1 indicating that it is the last record for the group where student name is “ABC”
• GetSavedInputRecord(): This function returns the record that was stored in cache by the function SaveInputRecord()

Back to the task at hand, we need 7 stage variables to perform the aggregation operation successfully.

1. LoopNumber: Holds the value of number of records stored in cache for a student
2. LoopBreak: This is to identify the last record for a particular student
3. SumSub1: This variable will hold the final sum of marks for each student in subject 1
4. IntermediateSumSub1: This variable will hold the sum of marks until the final record is evaluated for a student (subject 1)
5. SumSub2: Similar to SumSub1 (for subject 2)
6. IntermediateSumSub2: Similar to IntermediateSumSub1 (for subject 2)
7. LoopBreakNum: Holds the value for the number of times the loop has to run
Below is the screenshot of the stage variables

We also need to define the Loop Variables so that the loop will execute for a student until his final record is identified

To explain the above use of variables -

When the first record comes to stage variables, it is saved in the cache using the function SaveInputRecord() in first stage variableLoopNumber

The second stage variable checks if it is the last record for this particular student, if it is it stores 1 else 0

The third SumSub1 is executed only if the record is the last record

The fourth IntermediateSumSum1 is executed when the input record is not the last record, thereby storing the intermediate sum of the subject for a student

Fifth and sixth are the same as 3 and 4 stage variables

Seven will have the first value as 1 and for the second record also if the same student is fetched it will change to 2 and so on

The loop variable will be executed until the final record for a student is identified and the GetSavedInputRecord() function will make sure the current record is processed before the next record is brought for processing.

What the above logic does is for each and every record it will send the sum of marks scored by each student to the output. But our requirement is to have only one record per student in the output.

So we simply add a remove duplicates stage and add the student name as a primary key

Run the job and the output will be according to our initial expectation

We have successfully implemented AGGREGATION using TRANSFORMER Stage