Leading Zero in RCP Job

[neeraj]

Hello,

I have created a RCP Job which is reading the Oracle table and generating the files at runtime.

The Job design is:-

Oracle connector --> Modify --> Seq

Modify stage is used to drop the unwanted columns.

The issue we have defined the column as Integer which is read as Number(38,0) and causing an issue with the File.

The input in the Oracle is as below
101 Test
102 Test
103 Test1

The output is coming as
00000000000000000000000000000000000101.
00000000000000000000000000000000000102.
00000000000000000000000000000000000103.

36 Zeros and . is added to the existing data.

How can I handle such issue in the RCP job where Metadata is not defied? The scheme file is also not used.

Regards
Neeraj

[kumar_s]

Why is the interger read as Numeric?
Integer in Oracle could be Numeric in Datastage.
But covert it to interger in Datastage to get rid of padded zeros.

[neeraj]

It is a RCP JOB.. How can I apply the conversion??

[ray.wurlod]

I should like to point out that the zeroes are perfectly correct; the correct decimal value will be stored in Oracle, irrespective of how it is displayed using View Data.

[sachinshankrath]

The problem is not just with how the data looks in view data but how it appears in the seq file being created. All numeric data whether integers or decimal numbers are appearing in the file with unnecessary leading and trailing zeroes. One way to handle that would be to use DecimalToString(column_name, "suppress_zero") to transform the columns prior to writing to file. But since the original poster says he is using RCP to hide the metadata so that he/she could use the same job to generate multiple files, the metadata is not exposed and hence columns cannot be transformed. The question therefore is, how to handle this type of situation without resorting to explicitly defining the metadata and using transformations? Is abandoning RCP and explicitly defining metadata/using transformations the only solution?

[ray.wurlod]

They may be unnecessary (to you) but they're not wrong. What exactly is the problem?

DataStage puts them there driven by the precision and scale settings of the Decimal field.

[ray.wurlod]

It may not look correct, but it IS correct.

[eostic]

Leading zeros shouldn't be a problem for the receiving tool. They are perfectly valid, as Ray noted, and the data is correct. With the data being pipe delimited, one would expect any tooling to be able to parse this just fine, whether leading zeros are there or not.

Ernie

[chulett]

So what if it isn't fine? What are the options for someone in this situation?

[ray.wurlod]

Something clever in sed or awk, or a simple job/routine to parse the entire line, or abandonment of the requirement that everything has to be generic and RCP driven.

[eostic]

I'd probably write a server job to parse the whole line and clean it up as I need.

[neeraj]

Hi,

We are planning to write a stored procedure instead of DataStage job which will create the Pipe delimeted files.

SED/AWK can be an option but It can impact the valid Zeros as for e.g. Date field which may start with Zero..

Regards
Neeraj

[qt_ky]

Go for a Server job.

[neeraj]

The client is using Talend and during load of the file, the Job aborted stating that can't load float into Integer..

That means it is not going to work and we need another solution.

[ray.wurlod]

If it has to be integer, then you need to strip out the ".". The leading zeroes are not a problem.