Pinned topic CAST

I'm not clear why you need a cast at all. When instering a *ZERO into any numeric field it is automatically converted to the expected format.

Insert into MyTable Values(0,  'ABC');

Birgitta

• B.Hauser 1000007U1D
• ‏2013-05-15T07:34:44Z

I'm not clear why you need a cast at all. When instering a *ZERO into any numeric field it is automatically converted to the expected format.

Insert into MyTable Values(0,  'ABC');

Birgitta

 

More...

What SQLSTATE value were you getting on that INSERT that caused you to start using the CAST function?

Could you eliminate the specification of blanks and zeroes by just omitting the columns and letting DB2 assign the default values with a statement like the following?  

   INSERT into target_table (colA, colC, colE) SELECT col1, col3, col5 FROM source_table

• krmilligan 120000MDWP
• ‏2013-05-15T18:15:21Z

What SQLSTATE value were you getting on that INSERT that caused you to start using the CAST function?

Could you eliminate the specification of blanks and zeroes by just omitting the columns and letting DB2 assign the default values with a statement like the following?  

   INSERT into target_table (colA, colC, colE) SELECT col1, col3, col5 FROM source_table

 

 

More...

What I am trying to do is to insert records into a table from another table. The original cast I did where I was casting a zero to ALL unused fields, whether alpha or numeric worked fine. However, when I looked at the joblog, there was a message that said that led me to believe I'd get better performance if I used CAST. So then I changed the zeros to CAST as necessary, but could never get a clean execution of the SQL. There was always the SQLSTATE I listed above that came back from SQL. Finally, I gave up [I was under time constraints], and created the workfile to have only the fields I needed in it, and everything worked out. I wanted to use the CREATE TABLE function here, where I can create one table like another, which worked. But since this INSERT is being made from a HUGE table, which already takes awhile to process, I thought using the cast, like the message said, might save some time. Let me know if you need me to research what the JOBLOG message was, as I may still have that joblog on the spool.

Thank you,

Doug

• DougTX 270006BMBK
• ‏2013-05-17T03:18:29Z

What I am trying to do is to insert records into a table from another table. The original cast I did where I was casting a zero to ALL unused fields, whether alpha or numeric worked fine. However, when I looked at the joblog, there was a message that said that led me to believe I'd get better performance if I used CAST. So then I changed the zeros to CAST as necessary, but could never get a clean execution of the SQL. There was always the SQLSTATE I listed above that came back from SQL. Finally, I gave up [I was under time constraints], and created the workfile to have only the fields I needed in it, and everything worked out. I wanted to use the CREATE TABLE function here, where I can create one table like another, which worked. But since this INSERT is being made from a HUGE table, which already takes awhile to process, I thought using the cast, like the message said, might save some time. Let me know if you need me to research what the JOBLOG message was, as I may still have that joblog on the spool.

Thank you,

Doug

More...

I'm not sure what message you're referring to, so it would be helpful to share the Message ID. 

I'm guessing it was supplying a value of 0 to the character fields causing the message.  DB2 can convert 0 to a character string, but it would be faster to use a value of '0' instead.

• krmilligan 120000MDWP
• ‏2013-05-17T14:51:13Z

I'm not sure what message you're referring to, so it would be helpful to share the Message ID. 

I'm guessing it was supplying a value of 0 to the character fields causing the message.  DB2 can convert 0 to a character string, but it would be faster to use a value of '0' instead.

More...

Here is the message I received:

SQL7939    Information        00   05/13/13  14:54:09.584440  QSQRUN3      QSYS        *STMT    QSQROUTE  
  From module . . . . . . . . :   QSQINS                                    
  From procedure  . . . . . . :   CLEANUP                                   
  Statement . . . . . . . . . :   29834                                     
  To module . . . . . . . . . :   QSQROUTE                                  
  To procedure  . . . . . . . :   QSQROUTE                                  
  Statement . . . . . . . . . :   12900                                     
  Message . . . . :   Data conversion required on INSERT or UPDATE.         
  Cause . . . . . :   The INSERT or UPDATE values can not be directly moved to
    the columns because the data type or length of a value is different than one
    of the columns. The INSERT or UPDATE statement ran correctly.  Performance,
    however, would be improved if no data conversion was required. The reason
    data conversion is required is 2. -- Reason 1 is that the INSERT or UPDATE
    value is a character or graphic string of a different length than column
    RCSEQ5. -- Reason 2 is that the INSERT or UPDATE value is a numeric type
    that is different than the type of column RCSEQ5. -- Reason 3 is that the
    INSERT or UPDATE value is a variable length string and column RCSEQ5 is not.
    -- Reason 4 is that the INSERT or UPDATE value is not a variable length 
    string and column RCSEQ5 is. -- Reason 5 is that the INSERT or UPDATE value
    is a variable length string whose maximum length is different that the  
    maximum length of column RCSEQ5. -- Reason 6 is that a data conversion was
    required on the mapping of the INSERT or UPDATE value to column RCSEQ5, such
    as a CCSID conversion. -- Reason 7 is that the INSERT or UPDATE value is a
    character string and column RCSEQ5 is of type DATE, TIME, or TIMESTAMP. --
    Reason 8 is that the target table of the INSERT is not a SQL table. Recovery
     . . . :   To get better performance, try to use values of the same type and
    length as their corresponding columns.

• DougTX 270006BMBK
• ‏2013-05-17T16:06:10Z

Here is the message I received:

SQL7939    Information        00   05/13/13  14:54:09.584440  QSQRUN3      QSYS        *STMT    QSQROUTE  
  From module . . . . . . . . :   QSQINS                                    
  From procedure  . . . . . . :   CLEANUP                                   
  Statement . . . . . . . . . :   29834                                     
  To module . . . . . . . . . :   QSQROUTE                                  
  To procedure  . . . . . . . :   QSQROUTE                                  
  Statement . . . . . . . . . :   12900                                     
  Message . . . . :   Data conversion required on INSERT or UPDATE.         
  Cause . . . . . :   The INSERT or UPDATE values can not be directly moved to
    the columns because the data type or length of a value is different than one
    of the columns. The INSERT or UPDATE statement ran correctly.  Performance,
    however, would be improved if no data conversion was required. The reason
    data conversion is required is 2. -- Reason 1 is that the INSERT or UPDATE
    value is a character or graphic string of a different length than column
    RCSEQ5. -- Reason 2 is that the INSERT or UPDATE value is a numeric type
    that is different than the type of column RCSEQ5. -- Reason 3 is that the
    INSERT or UPDATE value is a variable length string and column RCSEQ5 is not.
    -- Reason 4 is that the INSERT or UPDATE value is not a variable length 
    string and column RCSEQ5 is. -- Reason 5 is that the INSERT or UPDATE value
    is a variable length string whose maximum length is different that the  
    maximum length of column RCSEQ5. -- Reason 6 is that a data conversion was
    required on the mapping of the INSERT or UPDATE value to column RCSEQ5, such
    as a CCSID conversion. -- Reason 7 is that the INSERT or UPDATE value is a
    character string and column RCSEQ5 is of type DATE, TIME, or TIMESTAMP. --
    Reason 8 is that the target table of the INSERT is not a SQL table. Recovery
     . . . :   To get better performance, try to use values of the same type and
    length as their corresponding columns.

 

 

More...

What is type and length of RCSEQ5?

I am not understanding why you wouldn't just subset the columns specified on the Insert?