DB2 UDB for Windows needs to emulate a DB2 UDB for z/OS function

[JimS]

In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

[Lennart]

JimS wrote:

In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

All I can think of is a trigger that check uniquenes when column is not
null. If the value is not unique, signal sql exception.
/Lennart

[JimS]

Lennart wrote:

JimS wrote:

In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

All I can think of is a trigger that check uniquenes when column is not
null. If the value is not unique, signal sql exception.
/Lennart

Thanks for the reply. Using a trigger in this manner seems plausible.
Do you know if there is a user exit in DB2 UDB for Windows that can be
tweaked to emulate the "WHERE NOT NULL" parameter on a unique index in
DB2 UDB for z/OS? If not, do you know if DB2 UDB for Windows supports
Sparse Indexes?

[Serge Rielau]

JimS wrote:

Lennart wrote:

>JimS wrote:

>>In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

All I can think of is a trigger that check uniquenes when column is not
null. If the value is not unique, signal sql exception.
/Lennart

Thanks for the reply. Using a trigger in this manner seems plausible.
Do you know if there is a user exit in DB2 UDB for Windows that can be
tweaked to emulate the "WHERE NOT NULL" parameter on a unique index in
DB2 UDB for z/OS? If not, do you know if DB2 UDB for Windows supports
Sparse Indexes?

There is no such exit.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Tonkuma]

JimS wrote:

In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

How about this way?
1) Add a generated column(call it uniq_sfx) Like this
uniq_sfx data-type
GENERATED ALWAYS AS
(CASE
WHEN unique_col IS NULL THEN
<primary-keyor <unique-column>
ELSE NULL
END
)

2) CREATE UNIQUE INDEX ON <table(unique_col, uniq_sfx)

For example:
(mgrno is a unique column which is allowed multiple nulls)
------------------------------ Commands Entered
------------------------------
CREATE TABLE Dept_Uniq_NULLs
(deptno CHAR(3) NOT NULL PRIMARY KEY
,deptname VARCHAR(29) NOT NULL
,mgrno CHAR(6)
,admrdept CHAR(3) NOT NULL
,uniq_sfx CHAR(3)
GENERATED ALWAYS AS
(CASE
WHEN mgrno IS NULL THEN
deptno
ELSE NULL
END
)
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
CREATE UNIQUE INDEX Dept_UNs_mgrno ON Dept_Uniq_NULLs
(mgrno, uniq_sfx);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, mgrno, admrdept)
SELECT deptno, deptname, mgrno, admrdept
FROM Department;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Dept_Uniq_NULLs;
------------------------------------------------------------------------------

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
B01 PLANNING 000020 A00 -
C01 INFORMATION CENTER 000030 A00 -
D01 DEVELOPMENT CENTER - A00 D01
D11 MANUFACTURING SYSTEMS 000060 D01 -
D21 ADMINISTRATION SYSTEMS 000070 D01 -
E01 SUPPORT SERVICES 000050 A00 -
E11 OPERATIONS 000090 E01 -
E21 SOFTWARE SUPPORT 000100 E01 -

9 record(s) selected.
------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D02', 'New DEVELOPMENT CENTER', 'A00');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, mgrno, admrdept)
VALUES ('D03', '3rd DEVELOPMENT CENTER', '000030', 'A00');
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or
foreign key update caused by a DELETE statement are not valid because
the
primary key, unique constraint or unique index identified by "2"
constrains
table "DB2ADMIN.DEPT_UNIQ_NULLS" from having duplicate rows for those
columns.
SQLSTATE=23505

------------------------------ Commands Entered
------------------------------
SELECT * FROM Dept_Uniq_NULLs;
------------------------------------------------------------------------------

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
B01 PLANNING 000020 A00 -
C01 INFORMATION CENTER 000030 A00 -
D01 DEVELOPMENT CENTER - A00 D01
D11 MANUFACTURING SYSTEMS 000060 D01 -
D21 ADMINISTRATION SYSTEMS 000070 D01 -
E01 SUPPORT SERVICES 000050 A00 -
E11 OPERATIONS 000090 E01 -
E21 SOFTWARE SUPPORT 000100 E01 -
D02 New DEVELOPMENT CENTER - A00 D02

10 record(s) selected.

[Tonkuma]

If you want hide uniq_sfx column, you can CREATE View.

For example:
------------------------------ Commands Entered
------------------------------
CREATE VIEW Dept_Uniq_NULLsV AS
SELECT deptno, deptname, mgrno, admrdept
FROM Dept_Uniq_NULLs;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLsV
VALUES ('D04', '4th DEVELOPMENT CENTER', NULL, 'A00');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered
------------------------------
SELECT * FROM Dept_Uniq_NULLsV;
------------------------------------------------------------------------------

DEPTNO DEPTNAME MGRNO ADMRDEPT
------ ----------------------------- ------ --------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00
B01 PLANNING 000020 A00
C01 INFORMATION CENTER 000030 A00
D01 DEVELOPMENT CENTER - A00
D11 MANUFACTURING SYSTEMS 000060 D01
D21 ADMINISTRATION SYSTEMS 000070 D01
E01 SUPPORT SERVICES 000050 A00
E11 OPERATIONS 000090 E01
E21 SOFTWARE SUPPORT 000100 E01
D02 New DEVELOPMENT CENTER - A00
D04 4th DEVELOPMENT CENTER - A00

11 record(s) selected.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLsV
VALUES ('D05', '5th DEVELOPMENT CENTER', '000050', 'A00');
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or
foreign key update caused by a DELETE statement are not valid because
the
primary key, unique constraint or unique index identified by "2"
constrains
table "DB2ADMIN.DEPT_UNIQ_NULLS" from having duplicate rows for those
columns.
SQLSTATE=23505

[Lennart]

Tonkuma wrote:

JimS wrote:

In my environment we are using DB2 UDB for Windows for development and
DB2 UDB for z/OS for production. In DB2 UDB for z/OS uniqueness of
columns that permit nulls may be enforced with a unique index created
with the "WHERE NOT NULL" parameter; this has the effect of ensuring
that not null columns are unique while not considering null columns to
be duplicates. Does anyone know of a way to duplicate this behavior
with DB2 UDB for Windows?

How about this way?
1) Add a generated column(call it uniq_sfx) Like this
uniq_sfx data-type
GENERATED ALWAYS AS
(CASE
WHEN unique_col IS NULL THEN
<primary-keyor <unique-column>
ELSE NULL
END
)

2) CREATE UNIQUE INDEX ON <table(unique_col, uniq_sfx)

For example:
(mgrno is a unique column which is allowed multiple nulls)
------------------------------ Commands Entered
------------------------------
CREATE TABLE Dept_Uniq_NULLs
(deptno CHAR(3) NOT NULL PRIMARY KEY
,deptname VARCHAR(29) NOT NULL
,mgrno CHAR(6)
,admrdept CHAR(3) NOT NULL
,uniq_sfx CHAR(3)
GENERATED ALWAYS AS
(CASE
WHEN mgrno IS NULL THEN
deptno
ELSE NULL
END
)
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
CREATE UNIQUE INDEX Dept_UNs_mgrno ON Dept_Uniq_NULLs
(mgrno, uniq_sfx);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, mgrno, admrdept)
SELECT deptno, deptname, mgrno, admrdept
FROM Department;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Dept_Uniq_NULLs;
------------------------------------------------------------------------------

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
B01 PLANNING 000020 A00 -
C01 INFORMATION CENTER 000030 A00 -
D01 DEVELOPMENT CENTER - A00 D01
D11 MANUFACTURING SYSTEMS 000060 D01 -
D21 ADMINISTRATION SYSTEMS 000070 D01 -
E01 SUPPORT SERVICES 000050 A00 -
E11 OPERATIONS 000090 E01 -
E21 SOFTWARE SUPPORT 000100 E01 -

9 record(s) selected.
------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D02', 'New DEVELOPMENT CENTER', 'A00');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, mgrno, admrdept)
VALUES ('D03', '3rd DEVELOPMENT CENTER', '000030', 'A00');
------------------------------------------------------------------------------
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement,
or
foreign key update caused by a DELETE statement are not valid because
the
primary key, unique constraint or unique index identified by "2"
constrains
table "DB2ADMIN.DEPT_UNIQ_NULLS" from having duplicate rows for those
columns.
SQLSTATE=23505

------------------------------ Commands Entered
------------------------------
SELECT * FROM Dept_Uniq_NULLs;
------------------------------------------------------------------------------

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 -
B01 PLANNING 000020 A00 -
C01 INFORMATION CENTER 000030 A00 -
D01 DEVELOPMENT CENTER - A00 D01
D11 MANUFACTURING SYSTEMS 000060 D01 -
D21 ADMINISTRATION SYSTEMS 000070 D01 -
E01 SUPPORT SERVICES 000050 A00 -
E11 OPERATIONS 000090 E01 -
E21 SOFTWARE SUPPORT 000100 E01 -
D02 New DEVELOPMENT CENTER - A00 D02

10 record(s) selected.

Ah, thats clever. Heres a slightly different version (which I think
will work as well):

DROP TABLE Dept_Uniq_NULLs;
CREATE TABLE Dept_Uniq_NULLs
(deptno CHAR(3) NOT NULL PRIMARY KEY
,deptname VARCHAR(29) NOT NULL
,mgrno CHAR(6)
,admrdept CHAR(3) NOT NULL
,uniq_sfx CHAR(3)
GENERATED ALWAYS AS
(COALESCE(mgrno,deptno))
);

CREATE UNIQUE INDEX Dept_UNs_mgrno ON Dept_Uniq_NULLs
(uniq_sfx);

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D02', 'New DEVELOPMENT CENTER', 'A00');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D03', 'New DEVELOPMENT CENTER', 'A00');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D04', 'New DEVELOPMENT CENTER', 'A00', 'AAA');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D05', 'New DEVELOPMENT CENTER', 'A00', 'BBB');

[ltjn@lelles ~]$ db2 "select * from Dept_Uniq_NULLs"

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
D02 New DEVELOPMENT CENTER - A00 D02
D03 New DEVELOPMENT CENTER - A00 D03
D04 New DEVELOPMENT CENTER AAA A00 AAA
D05 New DEVELOPMENT CENTER BBB A00 BBB

4 record(s) selected.

[Tonkuma]

Lennart wrote:

Ah, thats clever. Heres a slightly different version (which I think
will work as well):

DROP TABLE Dept_Uniq_NULLs;
CREATE TABLE Dept_Uniq_NULLs
(deptno CHAR(3) NOT NULL PRIMARY KEY
,deptname VARCHAR(29) NOT NULL
,mgrno CHAR(6)
,admrdept CHAR(3) NOT NULL
,uniq_sfx CHAR(3)
GENERATED ALWAYS AS
(COALESCE(mgrno,deptno))
);

CREATE UNIQUE INDEX Dept_UNs_mgrno ON Dept_Uniq_NULLs
(uniq_sfx);

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D02', 'New DEVELOPMENT CENTER', 'A00');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept)
VALUES ('D03', 'New DEVELOPMENT CENTER', 'A00');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D04', 'New DEVELOPMENT CENTER', 'A00', 'AAA');

INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D05', 'New DEVELOPMENT CENTER', 'A00', 'BBB');

[ltjn@lelles ~]$ db2 "select * from Dept_Uniq_NULLs"

DEPTNO DEPTNAME MGRNO ADMRDEPT UNIQ_SFX
------ ----------------------------- ------ -------- --------
D02 New DEVELOPMENT CENTER - A00 D02
D03 New DEVELOPMENT CENTER - A00 D03
D04 New DEVELOPMENT CENTER AAA A00 AAA
D05 New DEVELOPMENT CENTER BBB A00 BBB

4 record(s) selected.

You can't INSERT a row with MGRNO = 'D02'.
Following INSERT will fail.
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D06', 'New DEVELOPMENT CENTER', 'A00', 'D02');

[Lennart]

Tonkuma wrote:
[...]

You can't INSERT a row with MGRNO = 'D02'.
Following INSERT will fail.
INSERT INTO Dept_Uniq_NULLs
(deptno, deptname, admrdept, mgrno)
VALUES ('D06', 'New DEVELOPMENT CENTER', 'A00', 'D02');

I stand corrected :-)

/Lennart