-1

I am new to DB2. I tried creating a table with the following query:

CREATE TABLE CLP.legal_bill_charge(
client_id VARCHAR(11),
client_division_id VARCHAR(11),
client_office_id VARCHAR(11),
matter_id VARCHAR(11),
legal_bill_charge_id VARCHAR(13) NOT NULL,
legal_bill_id VARCHAR(11),
lawfirm_charge_number decimal(10,4),
timekeeper_id VARCHAR(11),
timekeeper_name varchar(256),
timekeeper_billed_level varchar(20),
charge_type char(10),
charge_text varchar(300),
long_charge_text varchar(8000),
charge_date TIMESTAMP   ,
original_units decimal(8,2),
original_unit_price decimal(23,6),
original_amount decimal(23,6),
current_units decimal(19,13),
current_unit_price decimal(23,6),
current_adjustment_amount decimal(23,6),
lf_adjustment_amount decimal(23,6),
lbc_tax_type varchar(6),
lbc_tax_rate decimal(16,15),
tax_amount decimal(65,36),
recommended_expense_amount decimal(23,6),
recommended_fee_amount decimal(23,6),
activity_code varchar(255),
activity_desc varchar(255),
task_code varchar(255),
task_desc varchar(255),
phase_code_id varchar(255),
phase_code_desc varchar(255))
in FAC032TS
organize by row;

However I got the below error: DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0604N The length, precision, or scale attribute for column, distinct type, structured type, array type, attribute of structured type, routine, cast target type, type mapping, or global variable "decimal(65,36)" is not valid.
SQLSTATE=42611

SQL0604N The length, precision, or scale attribute for column, distinct type, structured type, array type, attribute of structured type, routine, cast target type, type mapping, or global variable "decimal(65,36)

Could someone plz point out what mistake am I doin? Thanks in advance.

mao
  • 11,321
  • 2
  • 13
  • 29
user9419188
  • 27
  • 4
  • 14
  • What operating-system runs your Db2-Server and which Version is your Db2? You have to know these things with Db2 always. The maximum precision of a DECIMAL datatype in Db2 LUW is 31. – mao Mar 01 '18 at 15:25
  • If you use datatype DECFLOAT the maximum precision is 34, but you have to ensure your applications are compatible with that. – mao Mar 01 '18 at 15:32
  • linux os and version is 10.5.9 – user9419188 Mar 01 '18 at 15:53
  • Datatype has given by my client to create a table in this format.could you please tell me any alternative solution for the problem – user9419188 Mar 01 '18 at 16:07

1 Answers1

1

You got this error because Db2 on Linux/Unix/Windows offers the DECIMAL data type with a maximum precision of 31. If you use DECFLOAT you get precision 34 but your applications must be compatible with that data type.

Refer to the documentation page for the supported data types and their characteristics.

You can approximate larger numbers with FLOAT or DOUBLE, but only if the applications and SQL are properly coded to compensate.

Verify the requirement carefully.

mao
  • 11,321
  • 2
  • 13
  • 29