Schema file

CA4T · Post by **CA4T** » Fri Mar 21, 2014 1:23 pm

I have a DB2 connector to a Sequential file stage. I want to be able to take a Decimal field that I get from a DB2 query and have it in the external file as string with the leftmost characters truncated.

Example: 123456.12 show as 345612 with no decimal point and right justified.

I only want to use a Schema File to achieve these results.

Here is what I have (somewhat) working... not the results I want, but no longer throwing errors!

In my query I cast the DECIMAL field (6,2) as DIGITS:
DB2 -- DIGITS(DECIMAL_FIELD)

Then in my schema:
DECIMAL_FIELD:STRING[6]{padchar='0'}

BUT... my results are 123456 - it appears to be left justifying the string and dropping the decimal values.

Does anyone know how to get the schema to right justify?
I have tried
DECIMAL_FIELD:STRING[6]{padchar='0', justification='right'};
DECIMAL_FIELD:STRING[6]{padchar='0', justification='R'}

Iget error messages like...
Unrecognized type-specific format properties: {justification=right}

ray.wurlod · Post by **ray.wurlod** » Fri Mar 21, 2014 2:43 pm

I suspect (from my understanding of the metadata model) that justification cannot be specified in metadata, but am willing to be corrected. Metadata are far more about how things are stored, rather than how they're displayed. In general.

CA4T · Post by **CA4T** » Fri Mar 21, 2014 2:49 pm

It it a valid COBOL PIC clause. I created a COBOL cfd file...

01 TRYIT.
05 TRY5 PIC x(5) JUSTIFIED RIGHT.

and imported it into Table definitions/COBOL file when I went to the Table Definitions Layout tab, parallel here is what it showed me:

record
{record_format={type=implicit}, delim=none, ebcdic, binary, fix_zero}
(
TRY50:string[5];
)

Doesn't look like it can support. Maybe I'll open an PMR with IBM.

chulett · Post by **chulett** » Fri Mar 21, 2014 3:12 pm

Just because it's valid COBOL syntax doesn't mean the stage supports it. We've seen those kind of issues before here, best to check with IBM as you noted.

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Sat Mar 22, 2014 7:59 pm

I understand you wanted to do it via a schema file, but since it doesn't work, why not do it within a transformer and send it as a varchar (or char) field to the sequential file?

CA4T · Post by **CA4T** » Mon Mar 24, 2014 7:47 am

The reason for doing it with a schema and not a transformer... is that we are attempting to write a generic job that gets any data off the mainframe DB2 table and build a schema file from DB2 tables that supply the formatting rules...

I thought about this over the weekend and I think I'll have the DB2 Select SQL cast it as a digit and apply a right justify... I'll try that.

Just my opinion, but I still think that DS should support all COBOL copybook rules if they are going to allow you to import a copybook!

IBM Analytics Champion 2009 - 2020 · Post by **asorrell** » Mon Mar 24, 2014 9:14 am

Interesting approach. I did find your example a bit odd in that it was truncating the most significant digits instead of the least, and that may be why it isn't supported, they just didn't anticipate that.

You can always call it in as an enhancement request!

CA4T · Post by **CA4T** » Mon Mar 24, 2014 9:18 am

Yes, strange request. But here is the business sense?!

We have fields defined really large and our customers want a file in the length that their systems support... they know the data will never really be that large and just don't want to make any changes on their side. So, they want us to feed the data to them with their data layout.

Weird, but that is what is happening today!

ray.wurlod · Post by **ray.wurlod** » Mon Mar 24, 2014 1:52 pm

Use a generic expression in the Transformer stage. For example:

Code: Select all

Right(Space(jpLength):InLink.TheValue, jpLength)

where jpLength is the desired length provided as a job parameter.