473,320 Members | 1,600 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

checksum validation

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
17 4426
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Mercuro | last post by:
Hello i'm looking for a simple way to checksum my data. The data is 70 bytes long per record, so a 32 byte hex md5sum would increase the size of my mysql db a lot. I'm looking for something...
2
by: pradeep | last post by:
I have 2 data files, DATA1 and DATA2 , both same. My task is to: Open DATA1, compute the checksum and put it in the end of the file(don't bother about boundary conditions).close DATA1 Open...
4
by: Abby | last post by:
I have an array which contain Hex no. in each position. For examples, unsigned char data; data = 0x00; data = 0x01; data = 0x02; data = 0xE; data = 0xEF; --> This is the checksum value
2
by: Abby | last post by:
I need to do 8 bits 2's complement checksum, so I wrote code in order to see if the checksum calculation is correct. ===========================================================================...
6
by: Kevin | last post by:
I'm on Sun 0S 5.8 and need to calculate the checksum of certain sections in an ELF binary file. Specifically .text to .rodata. I'm able to parse through to the program header table and then find...
6
by: Astroman | last post by:
Hi guys and girls. This is my first time posting here so go easy :) . I was wondering if someone could please interpret how this csum() function works in the following C code. I know that the...
3
by: Andrus | last post by:
Device connected to serial port accepts data packets in the form 02 0x57 ll ll 00 00 00 00 dd..dd cc cc 02 (1 byte ) is message prefix 0x57 (1 byte) is message type (W=Write) ll ll ( 2 bytes)...
1
by: Terry | last post by:
I'm trying to calculate the checksum for UDP packet. The algorithm itself is not difficult (lots of examples out there), but what I'm having the most trouble with is determining the byte order...
4
by: Rain | last post by:
hi, need help here, does anyone know how to use or does any one have the code for checksum? I want to checksum a string to be sent using udp and checksum it again when received.. does anyone...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.