YYYYMM "between" selection on date column?

[Bernd Hohmann]

Hi there,

we're currently convert an old ISAM application to Java and DB2.

Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".

Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.

This isn't a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.

Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.

But I'm curious if this can be solved in a query without consuming too
much load on the database server.

A "select ... where order_date >= '2004-10' and order_date <='2005-10'
didn't work, >='2004-10-*' neither :-(

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Serge Rielau]

Bernd Hohmann wrote:

Hi there,

we're currently convert an old ISAM application to Java and DB2.

Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".

Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.

This isn't a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.

Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.

But I'm curious if this can be solved in a query without consuming too
much load on the database server.

A "select ... where order_date >= '2004-10' and order_date <='2005-10'
didn't work, >='2004-10-*' neither :-(

Bernd

Bernd, why do you believe there is load on the system? when doing the
math you propose. Depending on your fixpack you will either get the
constant expressions involved executed exactly once at runtime, or if
you pass true constants and are on a late fixpack DB2 may compute the
whole beast at compile-time (sonething we call "constant folding").

Cheers
Serge

PS: Welcome back from whichever stone you have been hiding under ;-)
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Serge Rielau wrote:

This isn't a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.

Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.

[...] Bernd, why do you believe there is load on the system? when doing the
math you propose. Depending on your fixpack you will either get the
constant expressions involved executed exactly once at runtime, or if
you pass true constants and are on a late fixpack DB2 may compute the
whole beast at compile-time (sonething we call "constant folding").
The problem isn't really the CPU load but the problem of additional
code. Additional code means problems in maintenance sooner or later.

So I have to decide betwen pest and cholera. The old ISAM was ok for
this task: "select ... where o_date between 200410 and 200510*".
PS: Welcome back from whichever stone you have been hiding under ;-)

Thanks a lot. I think I'll participate more in the next weeks to refresh
my knowledge.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Bob Stearns]

Bernd Hohmann wrote:

Hi there,

we're currently convert an old ISAM application to Java and DB2.

Among the load of problems we found the reports which have user
selections like "select all data between mm/yyyy and mm/yyyy".

Means: if the user enters "10/2004 - 10/2005" everything between
"2004-10-01" and "2004-10-31" is selected.

This isn't a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.

Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.

But I'm curious if this can be solved in a query without consuming too
much load on the database server.

A "select ... where order_date >= '2004-10' and order_date <='2005-10'
didn't work, >='2004-10-*' neither :-(

Bernd

It is ugly looking but
YEAR(date_col)||'/'||MONTH(date_col) BETWEEN 'yyyy/mm' AND 'yyyy/mm'
should work, with the proviso that if it is the only WHERE clause, a
sequential scan of your table may be necessitated

[Serge Rielau]

Bernd Hohmann wrote:

Serge Rielau wrote:

This isn't a real problem because I can expand the lower and upper
MMYYYY date selection to a DB2 date by calculating the first and last
day of a month and stuff it into the query parameters.

Or calculating the Julian date and substract 1 day from the upper
selection value and use this as parameter.

[...]

Bernd, why do you believe there is load on the system? when doing the
math you propose. Depending on your fixpack you will either get the
constant expressions involved executed exactly once at runtime, or if
you pass true constants and are on a late fixpack DB2 may compute the
whole beast at compile-time (sonething we call "constant folding").

The problem isn't really the CPU load but the problem of additional
code. Additional code means problems in maintenance sooner or later.

So I have to decide betwen pest and cholera. The old ISAM was ok for
this task: "select ... where o_date between 200410 and 200510*".

Well, you can alwasy use pandora's box:
CREATE FUNCTION my_date(arg INT)
RETURNS DATE DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURN <pestandcholera>

Would be fun to see the plan If all goes well: *poof* Out comes a constant.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Bob Stearns wrote:

It is ugly looking but
YEAR(date_col)||'/'||MONTH(date_col) BETWEEN 'yyyy/mm' AND 'yyyy/mm'
should work, with the proviso that if it is the only WHERE clause, a
sequential scan of your table may be necessitated

Well, if its doing the job I'll try it <g>

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Serge Rielau]

Bernd Hohmann wrote:

Bob Stearns wrote:

It is ugly looking but
YEAR(date_col)||'/'||MONTH(date_col) BETWEEN 'yyyy/mm' AND 'yyyy/mm'
should work, with the proviso that if it is the only WHERE clause, a
sequential scan of your table may be necessitated

Well, if its doing the job I'll try it <g>

Bernd

*grmbl* Don't complain if it's slow....

DROP FUNCTION date_begin;
CREATE FUNCTION date_begin(arg VARCHAR(7))
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURNS DATE
RETURN DATE(SUBSTR(arg, 1, 3) || '01/' || SUBSTR(arg, 4, 4));

DROP FUNCTION date_end;
CREATE FUNCTION date_end(arg VARCHAR(7))
DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL
RETURNS DATE
RETURN DATE(SUBSTR(arg, 1, 3) || '01/' || SUBSTR(arg, 4, 4)) + 1 MONTH
-1 DAY;

DROP TABLE T;
CREATE TABLE T (dt DATE);
INSERT INTO T VALUES
('10/01/2000'),
('09/30/2001'),
('08/15/2002'),
('07/12/2001');

SELECT * FROM T WHERE dt BETWEEN date_begin('09/2000') AND
date_end('08/2002');

Original Statement:
------------------
SELECT *
FROM T
WHERE dt BETWEEN date_begin('09/2000') AND date_end('08/2002')
Optimized Statement:
-------------------
SELECT Q1.DT AS "DT"
FROM SRIELAU.T AS Q1
WHERE (Q1.DT <= -(+(DATE(('08/' || '01/' || '2002')), 1, 2), 1, 3)) AND
(DATE(('09/' || '01/' || '2000')) <= Q1.DT)

Access Plan:
-----------
Total Cost: 12.875
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
0.4
TBSCAN
( 2)
12.875
1
|
4
TABLE: SRIELAU
T
Give this puppy an index on DT and it will fly with a start-stop key

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Serge Rielau wrote:

Give this puppy an index on DT and it will fly with a start-stop key

Works fine, but now the trouble with functions begin.

The user connected is "benutzer", the function is created with schema "cmm".

"...date_begin('09/2004')" doesn't work but
"...cmm.date_begin('09/2004')" does.

How can I add "cmm" permanent to the function path?

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Knut Stolze]

Bernd Hohmann wrote:

Serge Rielau wrote:

Give this puppy an index on DT and it will fly with a start-stop key

Works fine, but now the trouble with functions begin.

The user connected is "benutzer", the function is created with schema
"cmm".

"...date_begin('09/2004')" doesn't work but
"...cmm.date_begin('09/2004')" does.

How can I add "cmm" permanent to the function path?

Modify the CURRENT FUNCTION PATH special register. I believe if your
application uses JDBC/CLI, then you can set the function path also in the
cli config.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Bernd Hohmann wrote:

Serge Rielau wrote:

Give this puppy an index on DT and it will fly with a start-stop key

Works fine, but now the trouble with functions begin.

The user connected is "benutzer", the function is created with schema
"cmm".

"...date_begin('09/2004')" doesn't work but
"...cmm.date_begin('09/2004')" does.

How can I add "cmm" permanent to the function path?

Bernd

SET PATH = CURRENT PATH, CMM
Permatent for the session. You can also add it to your client connectuon
properties, like the cli.ini file...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Serge Rielau wrote:

SET PATH = CURRENT PATH, CMM
Permatent for the session. You can also add it to your client connectuon
properties, like the cli.ini file...

Ok, I set it at the beginning of the session.

Unfortunately I found again the reason why I refused to work with UDFs
in the past: problems with prepared statements. "some_udf(?)" has no
type during precompile so SQL0418N is thrown.

Gna... back to dynamic sql and howngrown query preprocessors too.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Knut Stolze]

Bernd Hohmann wrote:

Serge Rielau wrote:

SET PATH = CURRENT PATH, CMM
Permatent for the session. You can also add it to your client connectuon
properties, like the cli.ini file...
Ok, I set it at the beginning of the session.

Unfortunately I found again the reason why I refused to work with UDFs
in the past: problems with prepared statements. "some_udf(?)" has no
type during precompile so SQL0418N is thrown.

That's because DB2 doesn't know the type of '?'. Thus, it can't resolve the
(potentially overloaded) function. Use this instead:

some_udf(CAST(? AS <type>))

p.s: Personally, I prefer to always use explicitly the fully qualified name
(including schema name) for tables, views, functions and the like. It
simply avoids confusion and possibly problems down the road.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Bernd Hohmann wrote:

Serge Rielau wrote:

SET PATH = CURRENT PATH, CMM
Permatent for the session. You can also add it to your client connectuon
properties, like the cli.ini file...

Ok, I set it at the beginning of the session.

Unfortunately I found again the reason why I refused to work with UDFs
in the past: problems with prepared statements. "some_udf(?)" has no
type during precompile so SQL0418N is thrown.

That's because DB2 doesn't know the type of '?'. Thus, it can't resolve the
(potentially overloaded) function. Use this instead:

some_udf(CAST(? AS <type>))

p.s: Personally, I prefer to always use explicitly the fully qualified name
(including schema name) for tables, views, functions and the like. It
simply avoids confusion and possibly problems down the road.

Until you try to move the schema around ;-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Serge Rielau wrote:

p.s: Personally, I prefer to always use explicitly the fully qualifiedname
(including schema name) for tables, views, functions and the like. It
simply avoids confusion and possibly problems down the road.

Until you try to move the schema around ;-)

So much I prefer the style of Knut I see the problems.

After working with other SQL databases the last years I found out that
DB2 is a wonderful but harsh mistress.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Serge Rielau]

Bernd Hohmann wrote:

Serge Rielau wrote:

p.s: Personally, I prefer to always use explicitly the fully
qualified name
(including schema name) for tables, views, functions and the like. It
simply avoids confusion and possibly problems down the road.

Until you try to move the schema around ;-)

So much I prefer the style of Knut I see the problems.

After working with other SQL databases the last years I found out that
DB2 is a wonderful but harsh mistress.

Bernd

Hehe, we Canadians live in an unforgiving environment, what do you
expect to come out of it? ;-)

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernd Hohmann]

Serge Rielau wrote:

After working with other SQL databases the last years I found out that
DB2 is a wonderful but harsh mistress.

Hehe, we Canadians live in an unforgiving environment, what do you
expect to come out of it? ;-)

You're right. Tribes living in unforgiving environments are not very
known for inventing tools for a pleasant life.

The ancient romans for example invented the steam bath, central heating
and underfloor heating even though the're lived in a warm environment.

Contrariwise the Germans lived very unintimated and needed about 2000
years to regain the lost time.

Ok, I'll wait 2000 years for a "DB2 for the easygoing peoples" edition :-)

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin

[Serge Rielau]

Bernd Hohmann wrote:

Ok, I'll wait 2000 years for a "DB2 for the easygoing peoples" edition :-)

Watch out for DB2 code named "Malibu" from Jamaica Lab

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab