Solved

DB2 to_nchar() functions modifies the behaviour of coulmn from NULL to NOT NULL

Posted on 2012-03-18
11
581 Views
Last Modified: 2013-08-01
We are migrating the Sybase  database to DB2 database. As the data has DBCS characters, we are changing the char/varchar datatypes to nchar/nvarchar types. As part of this, the function TO_CHAR is changed to TO_NCHAR() assuming that the behaviour of both is same. But it is observed that while creating a table with TO_NCHAR() gives the column as NOT NULL whereas creating a table with TO_CHAR() gives the column as NULLABLE.

Source table:
 
CREATE TABLE tt_source
   (
   StartDate  TIMESTAMP NULL,
   enddate    TIMESTAMP NOT NULL
   )
 
Script used to create target table:
 
CREATE TABLE tt_target AS
   (
      SELECT
     
      TO_CHAR(Startdate,'mm/dd/yyyy') StartDate_char
     ,TO_CHAR(EndDate,'mm/dd/yyyy') EndDate_char
      ,TO_NCHAR(Startdate,'mm/dd/yyyy') StartDate_Nchar
     ,TO_NCHAR(EndDate,'mm/dd/yyyy') EndDate_Nchar
      FROM tt_source
   ) DEFINITION ONLY ;

The output is
Column      Type      Remarks
StartDate_char      Null      
EndDate_char      NOT NULL      
StartDate_nchar      NOT NULL      "This should have been NULLABLE,
                                                                  but to_nchar() changed to NOT NULL column"
EndDate_nchar      NOT NULL      


Is there a way to retain the nullability of the column while using TO_NCHAR() function.
0
Comment
Question by:vjmig
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37737138
i don't understand
are you generating those statements or write them manually?
if you write them manually, why not just write a regular create table (instead of create as with the only definition clause)

did you try
cast (colname as nchar) ?
0
 

Author Comment

by:vjmig
ID: 37737279
We are not writing them manullally. We are migrating 2500 stored procedures using some third party tool that has converted to use TO_CHAR() to similate sybase behaviour. To handle the DBCS characters, we have  convetered to_char() to to_nchar().

My question is why to_char() and to_nchar() is behaving differently with respect to nulability.
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37737477
Hi vj,

I believe that that behavior is a function of your conversion processor.  DB2 isn't creating the DDL to define the table.

Can you modify the table after it's created and before the data is loaded?  If so, you should be able to recreate the table to meet your needs, or ALTER any column definition that you need.


Good Luck,
Kent
0
 

Author Comment

by:vjmig
ID: 37737545
The tt_target table definetion is used to declare global temporary table. This way many temporary tables are declared in our Stored procedures.

CREATE TABLE tt_target AS
   (
      SELECT
     
      TO_CHAR(Startdate,'mm/dd/yyyy') StartDate_char
     ,TO_CHAR(EndDate,'mm/dd/yyyy') EndDate_char
      ,TO_NCHAR(Startdate,'mm/dd/yyyy') StartDate_Nchar
     ,TO_NCHAR(EndDate,'mm/dd/yyyy') EndDate_Nchar
      FROM tt_source
   ) DEFINITION ONLY ;
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 37737651
Sorry, I misunderstood your question.

But my question is still valid -- Can you modify the table after it's created and before the data is loaded?  If so, you should be able to recreate the table to meet your needs, or ALTER any column definition that you need.


Kent
0
 

Author Comment

by:vjmig
ID: 37737695
Yes, we can do that. but that will impact all the stored procedures and all of them around 4000 will need to be modified. I want to trying to avoid this if there is any easy way of doing this.
Also, I did not find any documention stating that to_char() and to_nchar() behave differently apart from character representaion.
0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 250 total points
ID: 37738699
Hi vj,

Yeah -- this came as something of a surprise to me, too.  :(

Do you have a need to store both an empty string and NULL in any of the effected columns?  Could you store an empty string in the NOT NULL column.  Then when the load is complete, ALTER the table to make the column nullable and update the table to set the blank items to NULL.


Just trying to help find a workaround,
Kent
0
 
LVL 8

Assisted Solution

by:mustaccio
mustaccio earned 125 total points
ID: 37739162
Looks like it may be a bug - consider opening a PMR with the IBM support.
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 125 total points
ID: 37740412
how about trying the following solution:
create your own function which is called to_nchar2 and in that function have an if that checks something that can never happen, and returns null, otherwise returns to_nchar
for example

create my_function(input nchar)
begin
if (input == 'ThisValueWillNeverBeInTheInput')
 then return cast(null as nchar)
else
 return to_nchar(input)
end

now, use some text editor and replace all the to_nchar with to_nchar2
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now