Difference in default constraint syntax between LUW and z/OS?

[BD]

Hi, all..

I'm creating a table with one column of type DATE, non nullable, with
a default of the current date.

The column syntax I'm using is "THIS_DATE_1 DATE NOT NULL DEFAULT
CURRENT DATE"

On LUW, the constraint compiles - I insert a row and the date value is
inserted with the correct default.

The same syntax appears to be failing on zOS:

IBM][CLI Driver][DB2] SQL0104N An unexpected token "DATE" was found
following "". Expected tokens may include: "SQLID". SQLSTATE=42601

I've done some digging on the SQL0104N error, and it's generic enough
to be less than helpful.

I'm looking through the online docs to see if there's a difference in
syntax requirements between LUW and z/OS as regards this particular
instance.

Thanks all, for any suggestions...

[BD]

Looking at the CREATE TABLE syntax diagram [1] I don't think this is
possible. Specifically, this bit:

Well, here's my session log...

db2 =CREATE TABLE MYSCHEMA.DATETEST ( TEST_INT INTEGER ,
TEST_DATE DATE NOT
NULL DEFAULT CURRENT DATE)
DB20000I The SQL command completed successfully.

db2 =INSERT INTO MYSCHEMA.DATETEST (TEST_INT) values (1)
DB20000I The SQL command completed successfully.

db2 =SELECT * FROM MYSCHEMA.DATETEST

TEST_INT TEST_DATE
----------- ----------
1 04/07/2008

1 record(s) selected.
....

[Dave Hughes]

BD wrote:

>

Looking at the CREATE TABLE syntax diagram [1] I don't think this is
possible. Specifically, this bit:

Well, here's my session log...

db2 =CREATE TABLE MYSCHEMA.DATETEST ( TEST_INT INTEGER ,
TEST_DATE DATE NOT
NULL DEFAULT CURRENT DATE)
DB20000I The SQL command completed successfully.

db2 =INSERT INTO MYSCHEMA.DATETEST (TEST_INT) values (1)
DB20000I The SQL command completed successfully.

db2 =SELECT * FROM MYSCHEMA.DATETEST

TEST_INT TEST_DATE
----------- ----------
1 04/07/2008

1 record(s) selected.
...

Sorry, I should've clarified that: looking at the CREATE TABLE syntax
diagam for DB2 for z/OS..., naturally it works fine on DB2 for LUW
Cheers,

Dave.

[BD]

On Apr 7, 2:54*pm, "Dave Hughes" <[e-mail]:

BD wrote:

Looking at the CREATE TABLE syntax diagram [1] I don't think this is
possible. Specifically, this bit:

Well, here's my session log...

db2 =CREATE TABLE MYSCHEMA.DATETEST ( *TEST_INT *INTEGER *,
TEST_DATE *DATE *NOT
*NULL *DEFAULT CURRENT DATE)
DB20000I *The SQL command completed successfully.

db2 =INSERT INTO MYSCHEMA.DATETEST (TEST_INT) values (1)
DB20000I *The SQL command completed successfully.

db2 =SELECT * FROM MYSCHEMA.DATETEST

TEST_INT * *TEST_DATE
----------- ----------
* * * * * 1 04/07/2008

* 1 record(s) selected.

...

Sorry, I should've clarified that: looking at the CREATE TABLE syntax
diagam for DB2 for z/OS..., naturally it works fine on DB2 for LUW

Cheers,

Dave.- Hide quoted text -

- Show quoted text -

Mmm... yes, and that was what I was in the process of digging up when
I posted the question. The SQL guides which I'm using are not platform-
specific, from what I can see. I must say, it's a little frustrating,
developing under LUW for an application which runs on z/OS. The
discrepancies I've seen are not many, but they are subtle enough to
cause some confusion.

I should probably get my hands on a better set of syntax docs.

Thanks for the feedback!

BD

[Serge Rielau]

Dave Hughes wrote:

BD wrote:

>>Looking at the CREATE TABLE syntax diagram [1] I don't think this is
possible. Specifically, this bit:

Well, here's my session log...

db2 =CREATE TABLE MYSCHEMA.DATETEST ( TEST_INT INTEGER ,
TEST_DATE DATE NOT
NULL DEFAULT CURRENT DATE)
DB20000I The SQL command completed successfully.

db2 =INSERT INTO MYSCHEMA.DATETEST (TEST_INT) values (1)
DB20000I The SQL command completed successfully.

db2 =SELECT * FROM MYSCHEMA.DATETEST

TEST_INT TEST_DATE
----------- ----------
1 04/07/2008

1 record(s) selected.
...

Sorry, I should've clarified that: looking at the CREATE TABLE syntax
diagam for DB2 for z/OS..., naturally it works fine on DB2 for LUW

http://www.ibm.com/developerworks/db...206sqlref.html

Perhaps I should post a topic on this....

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab