Tools & Utilities

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-29-2008
04:36 AM

01-29-2008
04:36 AM

Hi Gurus,

As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMP

For example if we have COLUMN given as SALARY S9(13)V99 Comp-3 , we create Teradata table as SALARY DECIMAL(15,2)

and LAYOUT as .FIELD SALARY * DECIMAL(15,2) and Mload works fine.

The alternate way is ,we can define the layout as .FIELD SALARY * CHAR(8) (we are computing it as 13+2/2 = 8( rounding 7.5) )

the mention CAST(:SALARY AS DECIMAL(15,2) FORMAT '9(13)V9(2)S') in the DML phase (Insert phase) then Mload works fine.

Problem we are facing is with COMP format .

we dont know the relevent teradata datatype for COMP.I got a COPY BOOK which has a COLUMN as BALANCE S9(13)V99 COMP.

I defined it as BALANCE DECIMAL(15,2) and tried defining the layout in above mentioned 2 ways ,But Mload is failing with error-2679.

I knew the data doesnt has any Bad Chars , it contains 0.00 values.

What is the relevant teradata data type for S9(13)V99 COMP ?

Can you please give me solution for this..?

Hope someone who worked on surely gives me a Answer ..

Thanks In Advance

As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMP

For example if we have COLUMN given as SALARY S9(13)V99 Comp-3 , we create Teradata table as SALARY DECIMAL(15,2)

and LAYOUT as .FIELD SALARY * DECIMAL(15,2) and Mload works fine.

The alternate way is ,we can define the layout as .FIELD SALARY * CHAR(8) (we are computing it as 13+2/2 = 8( rounding 7.5) )

the mention CAST(:SALARY AS DECIMAL(15,2) FORMAT '9(13)V9(2)S') in the DML phase (Insert phase) then Mload works fine.

Problem we are facing is with COMP format .

we dont know the relevent teradata datatype for COMP.I got a COPY BOOK which has a COLUMN as BALANCE S9(13)V99 COMP.

I defined it as BALANCE DECIMAL(15,2) and tried defining the layout in above mentioned 2 ways ,But Mload is failing with error-2679.

I knew the data doesnt has any Bad Chars , it contains 0.00 values.

What is the relevant teradata data type for S9(13)V99 COMP ?

Can you please give me solution for this..?

Hope someone who worked on surely gives me a Answer ..

Thanks In Advance

8 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-30-2008
07:59 PM

01-30-2008
07:59 PM

hello satya try this

for a field that is define in copy book as s9(04)v99 comp-3

the layout is as follows (when you do cols and hex)

0060

070C

and this is defined as decimal(7,2)

another example

decimal(11,2) in the table was represented as follows

000000

00000C

in your case you are looking for s9(13)v99 comp

so it will be represented as

0000000000000

000000000000C

so the field should be decimal(25,2)

in the layout say .fieldX decimal(25,2)

please let me know if that works

i am not an expert in Mainframes but i used it a few time and it worked.

for a field that is define in copy book as s9(04)v99 comp-3

the layout is as follows (when you do cols and hex)

0060

070C

and this is defined as decimal(7,2)

another example

decimal(11,2) in the table was represented as follows

000000

00000C

in your case you are looking for s9(13)v99 comp

so it will be represented as

0000000000000

000000000000C

so the field should be decimal(25,2)

in the layout say .fieldX decimal(25,2)

please let me know if that works

i am not an expert in Mainframes but i used it a few time and it worked.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-31-2008
03:14 AM

01-31-2008
03:14 AM

Hi Satya,

I did also have the same file layout once. I suppose you cannot load a COMP value in Teradata.

For e.g : a value defined in copybook as S9(9) will be represented as

000D

0070

i.e it will occupy 4 bytes and you cannot define it as DECIMAL(9,0) in your TD table as it will then expect a 5 byte record. Moreover defining the field as DECIMAL(8,0) also is wrong since its a S9(9).

So the best way is to request the source system to change the filed layout to COMP-3 and then you can load it.

But again if there is any way to load the COMP values I am not aware.

I did also have the same file layout once. I suppose you cannot load a COMP value in Teradata.

For e.g : a value defined in copybook as S9(9) will be represented as

000D

0070

i.e it will occupy 4 bytes and you cannot define it as DECIMAL(9,0) in your TD table as it will then expect a 5 byte record. Moreover defining the field as DECIMAL(8,0) also is wrong since its a S9(9).

So the best way is to request the source system to change the filed layout to COMP-3 and then you can load it.

But again if there is any way to load the COMP values I am not aware.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

01-31-2008
05:33 PM

01-31-2008
05:33 PM

COBOL COMP fields correspond to Teradata BYTEINT, SMALLINT, INTEGER, or BIGINT depending on the number of digits (1-2,3-4,5-9, or 10-18). If you have an implied decimal point in the COBOL PICture, you will need to "scale" the value, e.g. :bigintval/100 and convert it to DECIMAL.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2008
02:58 AM

02-05-2008
02:58 AM

[font=Tahoma][/font]

Hi shaik.feroz,

Thanks for your responce.

i tried in 2ways.

Way1-In the Layout i mentioned BALANCE FIELD decimal(25,2) and didnt change the table which has BALANCE DECIMAL(13,2)AND KEPT :BALANCE (DECIMAL(15,2),FORMAT '9(13)V9(02)S') IN THE INSERT PHASE

Way2-In the Layout i mentioned BALANCE FIELD decimal(25,2) and changed the table which has BALANCE DECIMAL(25,2)

in this 2 ways all rows going to ET tables with the same error 2679..

PLEASE LET ME KNOW IF U GET ANY HINT.

THANKS

Hi shaik.feroz,

Thanks for your responce.

i tried in 2ways.

Way1-In the Layout i mentioned BALANCE FIELD decimal(25,2) and didnt change the table which has BALANCE DECIMAL(13,2)AND KEPT :BALANCE (DECIMAL(15,2),FORMAT '9(13)V9(02)S') IN THE INSERT PHASE

Way2-In the Layout i mentioned BALANCE FIELD decimal(25,2) and changed the table which has BALANCE DECIMAL(25,2)

in this 2 ways all rows going to ET tables with the same error 2679..

PLEASE LET ME KNOW IF U GET ANY HINT.

THANKS

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-05-2008
01:00 PM

02-05-2008
01:00 PM

Mainframe COBOL PIC S9(13)V99 COMP would be stored as an 8-byte binary integer with implied decimal.

So in MLOAD .LAYOUT use

.FIELD BALANCEIN * BIGINT;

and in the DML use

CAST(:BALANCEIN AS DECIMAL(17,2))/100

/* 17 = 15 digits total in input field + 2 digits to the right of decimal after division*/

or

:BALANCEIN/100.00

/* As many zeros to both left and right of decimal as digits after "V" */

So in MLOAD .LAYOUT use

.FIELD BALANCEIN * BIGINT;

and in the DML use

CAST(:BALANCEIN AS DECIMAL(17,2))/100

/* 17 = 15 digits total in input field + 2 digits to the right of decimal after division*/

or

:BALANCEIN/100.00

/* As many zeros to both left and right of decimal as digits after "V" */

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-08-2008
06:32 AM

02-08-2008
06:32 AM

Hi Fred Pluebell,

Thanks for you reply.

As you said i gave .FIELD BALANCEIN * BIGINT in the Layout.

and in the DML gave as CAST(:BALANCEIN AS DECIMAL(17,2))/100. then changes DDL as the Column BALANCEIN as DECIMAL(17,2)).

When i run the MLOAD , all rows are going to UV with error code-2616 which says:

Numeric overflow occurred during computation.

Then i changed it (as Feroz Shaik said in his last post):

COLUMN in Table as DECIMAL(25, 2), Layout as .FIELD BALANCEIN * BIGINT & cast as CAST(:BALANCEIN AS DECIMAL(25,2))/100

In this case all rows going to Target table But with a rounding. Suppose if we have

56883112469940758.88 in the Flat file the value loading to Table is : 56883112469940760.00.

Note : am not sure on the data(i gave above) Bcz the data am looking through File-aid is in COMP format ,so we cant assume as it visible.

Thanks

Satya

Thanks for you reply.

As you said i gave .FIELD BALANCEIN * BIGINT in the Layout.

and in the DML gave as CAST(:BALANCEIN AS DECIMAL(17,2))/100. then changes DDL as the Column BALANCEIN as DECIMAL(17,2)).

When i run the MLOAD , all rows are going to UV with error code-2616 which says:

Numeric overflow occurred during computation.

Then i changed it (as Feroz Shaik said in his last post):

COLUMN in Table as DECIMAL(25, 2), Layout as .FIELD BALANCEIN * BIGINT & cast as CAST(:BALANCEIN AS DECIMAL(25,2))/100

In this case all rows going to Target table But with a rounding. Suppose if we have

56883112469940758.88 in the Flat file the value loading to Table is : 56883112469940760.00.

Note : am not sure on the data(i gave above) Bcz the data am looking through File-aid is in COMP format ,so we cant assume as it visible.

Thanks

Satya

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-08-2008
12:16 PM

02-08-2008
12:16 PM

Note that your example data has more digits than the COBOL PIC clause at the top of this thread declares. (That may be allowed depending on your compiler options for "numeric truncation".)

It's possible that the "correct" value is being stored but you are not retrieving it correctly (e.g. due to ODBC limitations on numeric precision). Try SELECT CAST(BALANCE AS VARCHAR(27)) to display the full value [that's 25 digits plus decimal point and optional minus sign].

It's possible that the "correct" value is being stored but you are not retrieving it correctly (e.g. due to ODBC limitations on numeric precision). Try SELECT CAST(BALANCE AS VARCHAR(27)) to display the full value [that's 25 digits plus decimal point and optional minus sign].

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

10-09-2009
05:18 PM

10-09-2009
05:18 PM

Hello

I have data type 9(6)V(9)2 COMP-3 field on mainframe file and trying load this data using the tpt script.

I want to covert this filed to char(5) in teradata. How can I achieve this?

I have data type 9(6)V(9)2 COMP-3 field on mainframe file and trying load this data using the tpt script.

I want to covert this filed to char(5) in teradata. How can I achieve this?