troubleshooting Question

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

Avatar of vjmig
vjmig asked on
9 Comments4 Solutions742 ViewsLast Modified:
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
   enddate    TIMESTAMP NOT NULL
Script used to create target table:
      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

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.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 4 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros