|
|
|
Junior Member
 
Group: Forum Members
Last Login: 8/18/2009 3:47:17 PM
Posts: 11,
Visits: 42
|
|
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
Cheers,
S.A.T.I.S.H ,
Teradata Certified Master
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/26/2009 6:42:55 PM
Posts: 116,
Visits: 27
|
|
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.
Feroz Shaik
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 4/3/2009 1:21:58 AM
Posts: 14,
Visits: 31
|
|
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.
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
|
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.
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/18/2009 3:47:17 PM
Posts: 11,
Visits: 42
|
|
[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
Cheers,
S.A.T.I.S.H ,
Teradata Certified Master
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
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" */
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/18/2009 3:47:17 PM
Posts: 11,
Visits: 42
|
|
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
Cheers,
S.A.T.I.S.H ,
Teradata Certified Master
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: 9/9/2009 4:34:06 PM
Posts: 407,
Visits: 889
|
|
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].
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/12/2009 10:11:46 AM
Posts: 2,
Visits: 6
|
|
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?
|
|
|
|