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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BD - 07 Apr 2008 17:39 GMT

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...

Reply to this Message

Dave Hughes - 07 Apr 2008 18:45 GMT

> Hi, all..
>
[quoted text clipped - 20 lines]
>
> Thanks all, for any suggestions...

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

+--DEFAULT--+------------------+-+
| +-constant---------+ |
| +-USER-------------+ |
| +-CURRENT SQLID----+ |
| +-NULL-------------+ |
. . . .
. .

Would seem to imply that one can only use a /constant/ with DEFAULT (or
USER, CURRENT SQLID, or NULL). However, CURRENT DATE is not a constant,
it's a special register [2]. Compare with the same section from the DB2
for LUW CREATE TABLE reference [3]:

default-clause

|--DEFAULT--+--------------------+--|

'-| default-values |-'

default-values

|--+-constant--------------------+--|

+-datetime-special-register---+
+-user-special-register-------+
+-CURRENT SCHEMA--------------+
+-NULL------------------------+
. .
. .

Which explicitly includes special registers distinctly from constants.
I assume there must be some way to emulate this functionality in DB2
for z/OS though ... perhaps a BEFORE trigger that re-writes NULL for
the column in question to CURRENT DATE ?

[1]
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2
.doc.sqlref/rctbl.htm#rctbl

[2]
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2
.doc.sqlref/rc2curt.htm#rc2curt

[3]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.l
uw.sql.ref.doc/doc/r0000927.html

Cheers,

Dave.

Reply to this Message

The Boss - 07 Apr 2008 19:30 GMT

>> Hi, all..
>>
[quoted text clipped - 56 lines]
> for z/OS though ... perhaps a BEFORE trigger that re-writes NULL for
> the column in question to CURRENT DATE ?

It has been quite a while since I did these kind of things on zOS, but iirc
you can just define the column including a "WITH DEFAULT" clause without
specifying a specific value for the default, like this:
"MY_DATE_COLUMN DATE NOT NULL WITH DEFAULT"
DB2 will know by means of the column data type that it has to use the
contents of the CURRENT_DATE special register for the default value.

HTH.

Cheers!

Signature

Jeroen

Reply to this Message

Dave Hughes - 07 Apr 2008 21:23 GMT

> > > Hi, all..
> > >
[quoted text clipped - 19 lines]
> > > difference in syntax requirements between LUW and z/OS as regards
> > > this particular instance.

[snip]

> > Which explicitly includes special registers distinctly from
> > constants. I assume there must be some way to emulate this
[quoted text clipped - 7 lines]
> know by means of the column data type that it has to use the contents
> of the CURRENT_DATE special register for the default value.

You are absolutely correct - I had wondered what DEFAULT with no
specification meant but didn't bother to look further down the page
where I would've found the following:

If a value is not specified after DEFAULT, the default value depends on
the data type of the column, as follows:

Data Type Default Value
Numeric 0
Fixed-length string Blanks
Varying-length string A string of length 0
Date CURRENT DATE
Time CURRENT TIME
Timestamp CURRENT TIMESTAMP
Distinct type The default of the source data type

Cheers,

Dave.

Reply to this Message

BD - 07 Apr 2008 22:28 GMT

> >> Hi, all..
>
[quoted text clipped - 72 lines]
>
> - Show quoted text -

It does indeed. I think that will do it.

Thanks kindly!

Reply to this Message

BD - 07 Apr 2008 22:32 GMT

> 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.

...

Reply to this Message

Dave Hughes - 07 Apr 2008 22:54 GMT

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

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.

Reply to this Message

BD - 07 Apr 2008 23:05 GMT

> > > Looking at the CREATE TABLE syntax diagram [1] I don't think this is
> > > possible. Specifically, this bit:
[quoted text clipped - 27 lines]
>
> - 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

Reply to this Message

Serge Rielau - 07 Apr 2008 23:07 GMT

>>> Looking at the CREATE TABLE syntax diagram [1] I don't think this is
>>> possible. Specifically, this bit:
[quoted text clipped - 20 lines]
> 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/db2/library/techarticle/0206sqlref/0206sqlref.html

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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message