All Forums Tools
Satish Mee 10 posts Joined 04/05
29 Jan 2008
COMP causing Mload faliure

Hi Gurus,As we know we have different ways to Compress the Decimal data Like COMP-3,COMP-4,COMP-5 and COMPFor 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

30 Jan 2008

hello satya try thisfor a field that is define in copy book as s9(04)v99 comp-3the layout is as follows (when you do cols and hex)0060070Cand this is defined as decimal(7,2)another exampledecimal(11,2) in the table was represented as follows00000000000Cin your case you are looking for s9(13)v99 compso it will be represented as0000000000000000000000000Cso the field should be decimal(25,2)in the layout say .fieldX decimal(25,2)please let me know if that worksi am not an expert in Mainframes but i used it a few time and it worked.

idg_tddevex 18 posts Joined 10/06
31 Jan 2008

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 as000D0070 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.

Fred 1096 posts Joined 08/04
31 Jan 2008

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.

Satish Mee 10 posts Joined 04/05
05 Feb 2008

[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

Fred 1096 posts Joined 08/04
05 Feb 2008

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" */

Satish Mee 10 posts Joined 04/05
08 Feb 2008

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))/100In 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

Fred 1096 posts Joined 08/04
08 Feb 2008

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].

pvsk 2 posts Joined 10/09
09 Oct 2009

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?

You must sign in to leave a comment.