What is Derby's equivalent of DB2's "NOT NULL WITH DEFAULT"

Question

I'm investigating how feasible it is to change some unit tests (for an application running on DB2) to use Derby. Having found this question where the answer claims that DB2 and Derby are very compatible, it seemed like a possibility to take DDL out of the DB2 database and run it on a Derby database. But I seem to have found a case where Derby doesn't work with Derby DDL.

So I have DDL written for DB2 9.1 that looks like this:

CREATE TABLE FOO (
  FOO_ID CHAR(17) NOT NULL,
  FOO_SOMESTUFF CHAR(17) NOT NULL WITH DEFAULT ,
  FOO_MORESTUFF CHAR(1) NOT NULL WITH DEFAULT ,
  FOO_DT DATE NOT NULL WITH DEFAULT 
);

Running this DDL in ij doesn't work, I get this syntax error:

ij> run 'c:/derby/db-derby-10.6.1.0-bin/foo.sql';
ij> CREATE TABLE FOO (
            FOO_ID CHAR(17) NOT NULL,
            FOO_SOMESTUFF CHAR(17) NOT NULL WITH DEFAULT ,
            FOO_MORESTUFF CHAR(1) NOT NULL WITH DEFAULT ,
            FOO_DT DATE NOT NULL WITH DEFAULT
    );
ERROR 42X01: Syntax error: Encountered "," at line 3, column 62.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed dir
ectly.
Consult your DBMS server reference documentation for details of the SQL syntax s
upported by your server.

What is the DDL for Derby that will result in the same default values as the DB2 DDL? I get that I may need to change the DDL, it's full of strange stuff like AUDIT NONE and DATA CAPTURE and CCSID EBCDIC that I'm going to have to get rid of for Derby to work. But defaulting stuff to all-blanks is a big deal for this codebase, if there is a different in-memory database that would do better than Derby at this I'm open to suggestions.

Answer 1

I didn't actually try this in Derby. But my guess is that it will work if you explicitly state what the defaults are, like this:

CREATE TABLE FOO (
  FOO_ID CHAR(17) NOT NULL,
  FOO_SOMESTUFF CHAR(17) NOT NULL WITH DEFAULT ' ' ,
  FOO_MORESTUFF CHAR(1) NOT NULL WITH DEFAULT ' ',
  FOO_DT DATE NOT NULL WITH DEFAULT CURRENT DATE
);