• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1262
  • Last Modified:

datatype error which colum is it ?

hi,
i am running this insert statement if i insert first 100 or 500 rows its k, but if i do full load the error occur can u plz advice which column datatype should i increase as i know its datatype lenght problem but which column how can i know that ??

INSERT INTO db2admin.STG_Cust1 ( SELECT
RRR_CUST1.CUSTNO, RRR_CUST1.RECCD, RRR_CUST1.CNAME,
RRR_CUST1.CADD1, RRR_CUST1.CADD2, RRR_CUST1.CTYPRV,
RRR_CUST1.CPOSTL, RRR_CUST1.CUSER1, RRR_CUST1.CUSER2,
RRR_CUST1.CCRDT3, RRR_CUST1.CRVDT3, RRR_CUST1.CSPCON,
RRR_CUST1.FILL01, RRR_CUST1.CCTCTY, RRR_CUST1.C1STAT
FROM RRR_CUST1)
DB21034E  The command was processed as an SQL
statement because it was not a
valid Command Line Processor command.  During SQL
processing it returned:
SQL0433N  Value "US" is too long.  SQLSTATE=22001

SQL0433N  Value "US                                  
                                " is too long.

Explanation:

The value "<value>" required truncation by a system
(built-in)
cast or adjustment function, which was called to
transform the
value in some way.  The truncation is not allowed
where this
value is used.  

 The value being transformed is one of the following:

o   an argument to a user defined function (UDF)

o   an input to the SET clause of an UPDATE statement

o   a value being INSERTed into a table

o   an input to a cast or adjustment function in some
other  
    context.  

o   a recursively referenced column whose data type
and length is
    determined by the initialization part of recursion
and may    
    grow in the iterative part of the recursion.  

 

 The statement has failed.  

User Response:

If "<value>" is a literal string in the SQL statement,
it is too
long for its intended use.  

 If "<value>" is not a literal string, examine the SQL
statement
to determine where the transformation is taking place.
Either the
input to the transformation is too long, or the target
is too
short.  

 Correct the problem and rerun the statement.  

 sqlcode :  -433

 sqlstate :  22001
0
bobby2929
Asked:
bobby2929
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Bobby,

INSERT INTO db2admin.STG_Cust1 ( SELECT
RRR_CUST1.CUSTNO, RRR_CUST1.RECCD, RRR_CUST1.CNAME,
RRR_CUST1.CADD1, RRR_CUST1.CADD2, RRR_CUST1.CTYPRV,
RRR_CUST1.CPOSTL, RRR_CUST1.CUSER1, RRR_CUST1.CUSER2,
RRR_CUST1.CCRDT3, RRR_CUST1.CRVDT3, RRR_CUST1.CSPCON,
RRR_CUST1.FILL01, RRR_CUST1.CCTCTY, RRR_CUST1.C1STAT
FROM RRR_CUST1)

Compare the column definitions in STG_cust1 and RRR_CUST1.  I suspect that it's a VARCHAR truncation where one of the values in RRR_CUST1 is longer than the definition in STG_Cust1.

The solution will be to increase the size of the item in STG_CUST1, or to trim the data to fit with substr().


Good Luck,
Kent
0
 
ghp7000Commented:
yes, exactly as Kent suggests, compare the column definitions for the specified columns
two ways to do this:
if you are selecting ALL the columns in the source table, do :
method 1
db2 describe table <source_table_name>
db2 describe table <insert_table_name>
you will easily be able to see which columns do not match up
method 2
if you are selecting only a subset of columns from a table which has many, many columns, it would be easier to query the syscat.columns catalog table
db2 select colname,typename,length,scale from syscat.columsn where colname in ('col name 1', 'col name 2', etc etc) and tabname='<source_table_name>'
db2 select colname,typename,length,scale from syscat.columsn where colname in ('col name 1', 'col name 2', etc etc) and tabname='<insert_table_name>'
if you use 2nd method, list your col names in the where clause in the same order as they are selected/inserted


0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now