By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

checksum validation

P: n/a
I need to write some code that will validate data. I have a checksum,
that I need to calculate in a rather complicated way, so a simple
CHECK is not enough. Which is the best way to do that in DB2?

Ewa.
Dec 17 '07 #1
Share this Question
Share on Google+
17 Replies


P: n/a
On Dec 17, 9:30 am, E.Bartosiew...@gmail.com wrote:
I need to write some code that will validate data. I have a checksum,
that I need to calculate in a rather complicated way, so a simple
CHECK is not enough. Which is the best way to do that in DB2?
If the validation involves other tables, IMO a before trigger is your
best option:

CREATE TRIGGER ...
NO CASCADE BEFORE INSERT ON ...
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN ( <condition) SIGNAL SQLSTATE ....

and a similar one for update of involved columns.

If rather complicated means complicated but concern only this table, I
would still aim for check constraint
My 2 skr

/Lennart

Dec 17 '07 #2

P: n/a
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)

Ewa

Dec 17 '07 #3

P: n/a
E.************@gmail.com wrote:
>If rather complicated means complicated but concern only this table, I
would still aim for check constraint

Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)
You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Dec 17 '07 #4

P: n/a
On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint

Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)
Can you be more specific on what you are trying to do, and why you
need to loop to validate a checksum?
/Lennart

Dec 17 '07 #5

P: n/a
On 17 Gru, 12:18, Serge Rielau <srie...@ca.ibm.comwrote:
E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)

You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)
Thanks for your help!

So as I thought, I need a function. I still don't know how to write
one though... It's easy when you need a sequence of sql commends, but
I can't imagine how to do operations on text data and loops. Can you
recommend some tutorial?

Ewa
Dec 17 '07 #6

P: n/a
E.************@gmail.com wrote:
On 17 Gru, 12:18, Serge Rielau <srie...@ca.ibm.comwrote:
>E.Bartosiew...@gmail.com wrote:
>If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)

You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)

Thanks for your help!

So as I thought, I need a function. I still don't know how to write
one though... It's easy when you need a sequence of sql commends, but
I can't imagine how to do operations on text data and loops. Can you
recommend some tutorial?
Have a look at the samples provided with DB2 in the sqllib/samples/c/
or /sqllib/samples/java/ directories for external UDFs.

If you register your function as being DETERMINISTIC (which it hopefully is)
and NO EXTERNAL ACTION (which is also should be), then you can embed the
function in a CHECK constraint and don't have to worry about triggers.

In case you need further help, just let us know...

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 17 '07 #7

P: n/a
On 17 Gru, 13:29, Knut Stolze <sto...@de.ibm.comwrote:
E.Bartosiew...@gmail.com wrote:
On 17 Gru, 12:18, Serge Rielau <srie...@ca.ibm.comwrote:
E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)
You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)
Thanks for your help!
So as I thought, I need a function. I still don't know how to write
one though... It's easy when you need a sequence of sql commends, but
I can't imagine how to do operations on text data and loops. Can you
recommend some tutorial?

Have a look at the samples provided with DB2 in the sqllib/samples/c/
or /sqllib/samples/java/ directories for external UDFs.

If you register your function as being DETERMINISTIC (which it hopefully is)
and NO EXTERNAL ACTION (which is also should be), then you can embed the
function in a CHECK constraint and don't have to worry about triggers.

In case you need further help, just let us know...
Ok, so just one last question :)

I also thought of writing the function in c or java as an external
function, which would make things much simpler, but I was asked to
write the check in sql. Is there a chance of doing that and not using
external UDFs?

Ewa
Dec 17 '07 #8

P: n/a
On 17 Gru, 12:43, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)

Can you be more specific on what you are trying to do, and why you
need to loop to validate a checksum?
I have a string type of data which consists of numbers and letters (an
ISIN -http://en.wikipedia.org/wiki/Interna...ifying_Number).
I need to change every letter into a numeric equivalent and then do
some arithmetic calculations to get the checksum.

Ewa
Dec 17 '07 #9

P: n/a
On Dec 17, 2:15 pm, E.Bartosiew...@gmail.com wrote:
On 17 Gru, 12:43, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)
Can you be more specific on what you are trying to do, and why you
need to loop to validate a checksum?

I have a string type of data which consists of numbers and letters (an
ISIN ->http://en.wikipedia.org/wiki/Interna...ifying_Number).
I need to change every letter into a numeric equivalent and then do
some arithmetic calculations to get the checksum.
I see. IMO it should be possible to do this without a loop. I don't
have the time at the moment (I can see what you think here :-), but I
can provide a solution for a similar problem, which might give you an
idea:

http://en.wikipedia.org/wiki/Nationa..._number#Sweden

Since the Y2K 12 positions is used in all system that I know of. The
12 position is a check digit that is calculated as:

2*first position + 1 * second position + 2*third pos + etc

In addition the 9:th pos can contain letters and those are treated as
1. It is used for temporary personal'ids. A check constraint that
validates such number may look like:

ALTER TABLE T ADD CONSTRAINT X
coalesce(nullif(10 -
mod(
2*int(substr(person_id,3,1)) -
case when 2*int(substr(person_id,3,1)) 9
then 9 else 0 end +
int(substr(person_id,4,1)) +
2*int(substr(person_id,5,1)) -
case when 2*int(substr(person_id,5,1)) 9
then 9 else 0 end +
int(substr(person_id,6,1)) +
2*int(substr(person_id,7,1)) -
case when 2*int(substr(person_id,7,1)) 9
then 9 else 0 end +
int(substr(person_id,8,1)) +
case when substr(person_id,9,1) not between '0' and '9'
then 2
else 2*int(substr(person_id,9,1)) -
case when 2*int(substr(person_id,9,1)) 9
then 9 else 0
end
end +
int(substr(person_id,10,1)) +
2*int(substr(person_id,11,1)) -
case when 2*int(substr(person_id,11,1)) 9
then 9 else 0 end,
10),10),0) = int(substr(person_id,12,1))
);

HTH
/Lennart

Dec 17 '07 #10

P: n/a
On Dec 17, 2:46 pm, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Dec 17, 2:15 pm, E.Bartosiew...@gmail.com wrote:
On 17 Gru, 12:43, Lennart <Erik.Lennart.Jons...@gmail.comwrote:
On Dec 17, 10:44 am, E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this table, I
would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give me
some hints (or link to man) on how to make a check constraint, that
consists of loops and some operations on the data? I never wrote
checks more complicated than CONSTRAINT year CHECK (YEAR(birthday) >=
1986)
Can you be more specific on what you are trying to do, and why you
need to loop to validate a checksum?
I have a string type of data which consists of numbers and letters (an
ISIN ->http://en.wikipedia.org/wiki/Interna...ifying_Number).
I need to change every letter into a numeric equivalent and then do
some arithmetic calculations to get the checksum.

I see. IMO it should be possible to do this without a loop. I don't
have the time at the moment (I can see what you think here :-), but I
can provide a solution for a similar problem, which might give you an
idea:

http://en.wikipedia.org/wiki/Nationa..._number#Sweden

Since the Y2K 12 positions is used in all system that I know of. The
12 position is a check digit that is calculated as:

2*first position + 1 * second position + 2*third pos + etc

In addition the 9:th pos can contain letters and those are treated as
1. It is used for temporary personal'ids. A check constraint that
validates such number may look like:

ALTER TABLE T ADD CONSTRAINT X
coalesce(nullif(10 -
mod(
2*int(substr(person_id,3,1)) -
case when 2*int(substr(person_id,3,1)) 9
then 9 else 0 end +
int(substr(person_id,4,1)) +
2*int(substr(person_id,5,1)) -
case when 2*int(substr(person_id,5,1)) 9
then 9 else 0 end +
int(substr(person_id,6,1)) +
2*int(substr(person_id,7,1)) -
case when 2*int(substr(person_id,7,1)) 9
then 9 else 0 end +
int(substr(person_id,8,1)) +
case when substr(person_id,9,1) not between '0' and '9'
then 2
else 2*int(substr(person_id,9,1)) -
case when 2*int(substr(person_id,9,1)) 9
then 9 else 0
end
end +
int(substr(person_id,10,1)) +
2*int(substr(person_id,11,1)) -
case when 2*int(substr(person_id,11,1)) 9
then 9 else 0 end,
10),10),0) = int(substr(person_id,12,1))
);

HTH
/Lennart
I only had a quick glance at the algorithm, but I assume you would
like a function like:

create function letter2digit (l varchar(1)) returns char(2) return
char(ascii(l) - 55)

which you can apply at your 2 first chars. Your resulting string would
be:

with T (str) as (values 'US0378331005') select letter2digit(substr(str,
1,1)) || letter2digit(substr(str,2,1)) || substr(str,3) from T"

1
----------------
30280378331005

1 record(s) selected.

From there you should be able to use a similar technique as in my
previous example
HTH
/Lennart

Dec 17 '07 #11

P: n/a
E.************@gmail.com wrote:
On 17 Gru, 13:29, Knut Stolze <sto...@de.ibm.comwrote:
>E.Bartosiew...@gmail.com wrote:
On 17 Gru, 12:18, Serge Rielau <srie...@ca.ibm.comwrote:
E.Bartosiew...@gmail.com wrote:
If rather complicated means complicated but concern only this
table, I would still aim for check constraint
Yes, I forgot to mention that it does concern only one table. I just
need to check the data before I put it in the table. Could you give
me some hints (or link to man) on how to make a check constraint,
that consists of loops and some operations on the data? I never
wrote checks more complicated than CONSTRAINT year CHECK
(YEAR(birthday) >= 1986)
>You have to write a function.
If it's as complex as you say an C UDF (eventually unfenced) woudl be
the way to go.
If it's just a couple of IFs and a loop, just do a SQL UDF.
My thumb rule is that SQL UDFs shouldn't be longer than a page.
(25 lines)
Thanks for your help!
So as I thought, I need a function. I still don't know how to write
one though... It's easy when you need a sequence of sql commends, but
I can't imagine how to do operations on text data and loops. Can you
recommend some tutorial?

Have a look at the samples provided with DB2 in the sqllib/samples/c/
or /sqllib/samples/java/ directories for external UDFs.

If you register your function as being DETERMINISTIC (which it hopefully
is) and NO EXTERNAL ACTION (which is also should be), then you can embed
the function in a CHECK constraint and don't have to worry about
triggers.

In case you need further help, just let us know...

Ok, so just one last question :)

I also thought of writing the function in c or java as an external
function, which would make things much simpler, but I was asked to
write the check in sql. Is there a chance of doing that and not using
external UDFs?
Yes, of course. SQL is computationally complete, so you can basically do
everything with it. Based on your description and Lennart's help, I think
you should quickly be able to work out the remaining details.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 17 '07 #12

P: n/a
On Dec 18, 5:23 am, Knut Stolze <sto...@de.ibm.comwrote:
>
Yes, of course. SQL is computationally complete, so you can basically do
everything with it. Based on your description and Lennart's help, I think
you should quickly be able to work out the remaining details.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany- Hide quoted text -
I feel that it is not so easy. The main reasons are followings.
(1) Alphabetical characters would appear not only in left 2 characters
but also other positions
(See second example of ISIN "TREASURY CORP VICTORIA 5 3/4% 2005-2016:
ISIN AU0000XVGZA3").
So, the digits to multply 2 are shifted by the alphabetical characters
right to the digit.
(2) SQL UDFs cannot be used in CHECK condition(I saw DB2 Version 9 SQL
Reference Volume 2 ---CREATE TABLE).

My trial expression got very complex....
------------------------- Commands Entered -------------------------
SELECT isin
,CAST(MOD(10-MOD(
LOCATE(SUBSTR(isin, 1,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 2,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 3,
9),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 3,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 4,
8),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 4,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 5,
7),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 5,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 6,
6),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 6,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 7,
5),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 7,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 8,
4),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 8,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 9,
3),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin, 9,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,10,
2),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin,10,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4
*(1-MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,11,
1),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2))
+LOCATE(SUBSTR(isin,11,1),' 5A 1FK 6BPU2GLZ7CQV3HM 8DRW4IN 9ESXJO
TY')/4

+LOCATE(SUBSTR(isin, 1,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 2,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 3,
9),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 3,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 4,
8),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 4,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 5,
7),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 5,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 6,
6),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 6,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 7,
5),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 7,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 8,
4),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 8,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin, 9,
3),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin, 9,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,10,
2),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
+LOCATE(SUBSTR(isin,10,1),' 1 2A 3B 4CK 5DL 6EMU7FNV8GOW9HPXIQY
JRZ S T')/4
*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,11,
1),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ','')),2)
,10),10) AS CHAR(1) )
FROM (VALUES 'US037833100', 'AU0000XVGZA', 'GB000263494') S(isin);
--------------------------------------------------------------------

ISIN 2
----------- -
US037833100 5
AU0000XVGZA 3
GB000263494 6

3 record(s) selected.

Test data were extracted from the reference in Ewa Date: Mon, 17 Dec
2007 05:15:04 -0800 (PST).
Apple Inc.: ISIN US0378331005,
TREASURY CORP VICTORIA 5 3/4% 2005-2016: ISIN AU0000XVGZA3
BAE Systems: ISIN GB0002634946,
Dec 18 '07 #13

P: n/a
Tonkuma wrote:
On Dec 18, 5:23 am, Knut Stolze <sto...@de.ibm.comwrote:
>>
Yes, of course. SQL is computationally complete, so you can basically do
everything with it. Based on your description and Lennart's help, I
think you should quickly be able to work out the remaining details.

I feel that it is not so easy. The main reasons are followings.

(2) SQL UDFs cannot be used in CHECK condition(I saw DB2 Version 9 SQL
Reference Volume 2 ---CREATE TABLE).
I have used external UDFs in CHECK constraints before. That's why I assumed
that SQL UDFs can be used in the same way. But apparently, I was mistake
and there is indeed such a restriction. So triggers are the way to go
here.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 18 '07 #14

P: n/a
Lennart wrote:
The search-condition cannot contain any of the following (SQLSTATE
42621):
[...]
* Column functions
* Functions that are not deterministic
* Functions defined to have an external action
* User-defined functions defined with either CONTAINS SQL or READS
SQL DATA
[...]

How come:

create table T (c1 char(12) not null);
alter table T add constraint X check ( substr(c1,1,1) = 'A' );

is valid? Isn't substr a column function?
No, SUBSTR is a scalar function. Column functions are aggregate functions
like MIN, MAX, AVG, STDDEV, etc.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
Dec 18 '07 #15

P: n/a
On Dec 18, 3:10 pm, Knut Stolze <sto...@de.ibm.comwrote:
[...]
>
No, SUBSTR is a scalar function. Column functions are aggregate functions
like MIN, MAX, AVG, STDDEV, etc.
Ah, yes of course. Thanks
/Lennart

Dec 18 '07 #16

P: n/a
No, SUBSTR is a scalar function. Column functions are aggregate functions
like MIN, MAX, AVG, STDDEV, etc.
Thanks, everyone, for the discussion and lots of useful info :)

Ewa
Dec 20 '07 #17

P: n/a
A little shorter solution than my previous answer.
------------------------------ Commands Entered
------------------------------
SELECT isin
,CAST(MOD(10-MOD(
LOCATE(SUBSTR(isin,1,1)
,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO **TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ *S***T'
,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
2,10),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,2,1)
,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQ Y*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESX JO**TY'
,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
3,9),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,3,1)
,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO **TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ *S***T'
,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
4,8),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,4,1)
,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQ Y*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESX JO**TY'
,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
5,7),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,5,1)
,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO **TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ *S***T'
,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
6,6),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,6,1)
,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQ Y*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESX JO**TY'
,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
7,5),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,7,1)
,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO **TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ *S***T'
,1+45*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
8,4),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,8,1)
,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQ Y*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESX JO**TY'
,1+52*MOD(LENGTH(REPLACE(TRANSLATE(SUBSTR(isin,
9,3),'','0123456789'),' ','')),2)) )/4
+LOCATE(SUBSTR(isin,9,1)
,SUBSTR('0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO **TY0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQY*JRZ *S***T'
,1+45*MOD(LENGTH(LTRIM(RTRIM(TRANSLATE(SUBSTR(isin ,
10,2),'','0123456789')))),2)) )/4
+LOCATE(SUBSTR(isin,10,1)
,SUBSTR('0**1***2A**3B**4CK*5DL*6EMU7FNV8GOW9HPXIQ Y*JRZ*S***T0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESX JO**TY'
,CASE WHEN SUBSTR(isin,11,1) BETWEEN '0' AND '9' THEN 1
ELSE 53 END) )/4
+LOCATE(SUBSTR(isin,
11,1),'0**5A**1FK*6BPU2GLZ7CQV3HM*8DRW4IN*9ESXJO** TY')/4
,10),10) AS CHAR(1) )
FROM (VALUES 'US0378331005', 'AU0000XVGZA3', 'GB0002634946') S(isin)
;
------------------------------------------------------------------------------

ISIN 2
------------ -
US0378331005 5
AU0000XVGZA3 3
GB0002634946 6

3 record(s) selected.

Dec 20 '07 #18

This discussion thread is closed

Replies have been disabled for this discussion.