This is one of the basic requirement in DataStage, we'll have to generate sequence numbers and then assign the same values to your required O/P field (e.g. 1, 2, 3, …). If you are using the import osh operator (through a stage, e.g. the Sequential File Stage) to read external data, you can use the -recordNumberField parameter.
Solution: Generate Row Number Field with DataStage Transformer Stage
There are number of different ways to solve this problem. Here I will share with you the easiest way to generate and assign sequence numbers using a DataStage Parallel Transformer stage.
@PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1
Note: This logic only works if your data is evenly balanced i.e., equal number of rows going through each partition.
The above logic uses three DataStage system variables which are listed as below;
@INROWNUM – This system variable contains the row number within the partition. For each partition this variable starts from 1: 1, 2, 3, …
@NUMPARTITIONS – This system variable contains the number of partitions (1, 2, 3, …) the stage is running on.
@PARTITIONNUM – This system variable contains the number (0, 1, 2,…) of the partition that is processing the particular row.
To understand the derivation expression better, let’s see an example.
Example-1: Generating Sequence Numbers Using 2-Node Config File
| 2 NODE CONFIG FILE | |||
| Sample Field | @INROWNUM | @PARTITIONNUM | @PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1 |
| A | 1 | 0 | 1 |
| B | 1 | 1 | 2 |
| C | 2 | 0 | 3 |
| D | 2 | 1 | 4 |
| E | 3 | 0 | 5 |
| G | 3 | 1 | 6 |
| H | 4 | 0 | 7 |
| I | 4 | 1 | 8 |
| J | 5 | 0 | 9 |
| K | 5 | 1 | 10 |
| L | 6 | 0 | 11 |
| M | 6 | 1 | 12 |
| N | 7 | 0 | 13 |
| O | 7 | 1 | 14 |
| P | 8 | 0 | 15 |
| Q | 8 | 1 | 16 |
| R | 9 | 0 | 17 |
| S | 9 | 1 | 18 |
| T | 10 | 0 | 19 |
| U | 10 | 1 | 20 |
| V | 11 | 0 | 21 |
Example-2: Generating Sequence Numbers Using 4-Node Config File
| 4 NODE CONFIG FILE | |||
| Sample Field | @INROWNUM | @PARTITIONNUM | @PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1 |
| A | 1 | 0 | 1 |
| B | 1 | 1 | 2 |
| C | 1 | 2 | 3 |
| D | 1 | 3 | 4 |
| E | 2 | 0 | 5 |
| G | 2 | 1 | 6 |
| H | 2 | 2 | 7 |
| I | 2 | 3 | 8 |
| J | 3 | 0 | 9 |
| K | 3 | 1 | 10 |
| L | 3 | 2 | 11 |
| M | 3 | 3 | 12 |
| N | 4 | 0 | 13 |
| O | 4 | 1 | 14 |
| P | 4 | 2 | 15 |
| Q | 4 | 3 | 16 |
| R | 5 | 0 | 17 |
| S | 5 | 1 | 18 |
| T | 5 | 2 | 19 |
| U | 5 | 3 | 20 |
| V | 6 | 0 | 21 |
