Inserting Multiple DATE values into a column.

[Tim Davidge]

Hi folks, been a while since I have posted a plea for help and I think I
have forgotten everything I learnt from the helpful contributors to this
newsgroup, that said however :

I'm trying to insert a range of dates for the past say two years into the
first column of say a two column table. I've been trying to use 'current
date' and working backwards from there but for the life of me I cannot get
the syntax right. I can produce a single value, but cannot get a list that
would be all WEEKDAYS in date format for the last two years. Using VALUES
instead of a SELECT statement, how do I get a list instead of a single
value. VALUES doesnt sem to like BETWEEN and I do not know how to use
recursive statements.

SELECT would imply I already have the values in a table, where VALUES as a
function should somehow be able to calculate these for me. Any help would be
greatly appreciated.

TD

[PM \(pm3iinc-nospam\)]

WITH
-- GENERATE DATES
TYEARS(DT, CNT, WD) AS
(
-- SEED
VALUES( DATE('2002-01-01'), 1, DAYOFWEEK_ISO(DATE('2002-01-01')) )
UNION ALL
SELECT TYEARS.DT + 1 DAY, CNT + 1, DAYOFWEEK_ISO(DATE(TYEARS.DT) + 1 DAY)
FROM TYEARS
-- LIMIT DATA GENERATION TO A CERTAIN TIME LIMIT
WHERE TYEARS.CNT < (366*2)
)
-- DATES WITHOUT THE WEEKENDS
-- (a)
SELECT
RCALC1.DT
FROM
(
SELECT DT FROM TYEARS
WHERE WD NOT IN (6, 7)
) AS RCALC1(DT)
-- WHERE DT BETWEEN ... OR YEAR() ... OR ...
ORDER BY DT
;
You can use an insert instead of the select (a) if you want. (insert into x
select * from ...)
I did not bother about performance...
The statement could be more compact but i did not bother either.

PM

[AK]

Tim,

try this:

CREATE TABLE DATE_SEQ(SOME_DATE DATE NOT NULL);
INSERT INTO DATE_SEQ SELECT DATE('01/01/2003') + (ROW_NUMBER() OVER()
- 1) DAYS FROM SYSCAT.COLUMNS;

[Jeremy Rickard]

"Tim Davidge" <tdavidge@no_spam.hotmail.com> wrote in message news:<jq********************@comcast.com>...

I'm trying to insert a range of dates for the past say two years into the
first column of say a two column table.

Recursive query would be the most obvious solution. You'll need to
plug something like the following into an appropriate INSERT (e.g.
maybe joining back to your table to avoid duplicate inserts):

WITH
dates(date) AS
(
VALUES CURRENT DATE - 2 YEARS
UNION ALL
SELECT
date + 1 DAY
FROM
dates
WHERE
date <= CURRENT DATE
)
SELECT date FROM dates WHERE dayofweek_iso(date) < 6
Jeremy Rickard

[Tim Davidge]

Guys,

That is perfect. Many thanks for the help.

Tim
"Jeremy Rickard" <jr******@unisystems.biz> wrote in message
news:d3**************************@posting.google.c om...

"Tim Davidge" <tdavidge@no_spam.hotmail.com> wrote in message news:<jq********************@comcast.com>...

I'm trying to insert a range of dates for the past say two years into the first column of say a two column table.

Recursive query would be the most obvious solution. You'll need to
plug something like the following into an appropriate INSERT (e.g.
maybe joining back to your table to avoid duplicate inserts):

WITH
dates(date) AS
(
VALUES CURRENT DATE - 2 YEARS
UNION ALL
SELECT
date + 1 DAY
FROM
dates
WHERE
date <= CURRENT DATE
)
SELECT date FROM dates WHERE dayofweek_iso(date) < 6
Jeremy Rickard

[AK]

Jeremy,

I think recursion won't run as fast as my solution.
What do you think?

[PM \(pm3iinc-nospam\)]

.... but at least, we, the common table expressioners, will never have to
worry about having enough columns to generate the data we need... ;-)

db2batch -d SAMPLE -f GENDATES.DBB -a USER/USING -i COMPLETE
--#SET PERF_DETAIL 2
--#SET ROWS_FETCH -1
--#SET ROWS_OUT 0

Statement number: 1

SELECT * FROM (SELECT (DATE('01/01/2003') + (ROW_NUMBER() OVER() - 1) DAYS)
R FROM SYSCAT.COLUMNS ) AS R2(DT) WHERE DT <= DATE('01/01/2003') + (3137)
DAYS O
RDER BY DT

DT
------------
Number of rows retrieved is: 3138
Number of rows sent to output is: 0

Prepare Time is: 0.000 seconds
Execute Time is: 0.062 seconds
Fetch Time is: 0.047 seconds
Elapsed Time is: 0.109 seconds

---------------------------------------------

Statement number: 2

WITH
TYEARS(DT, CNT, WD) AS
(
VALUES( DATE('2003-01-01'), 1, DAYOFWEEK_ISO(DATE('2003-01-01')) )
UNION ALL
SELECT TYEARS.DT + 1 DAY, CNT + 1, DAYOFWEEK_ISO(DATE(TYEARS.DT) + 1 DAY)
FROM TYEARS
WHERE TYEARS.CNT < (366*12)
)
SELECT
RCALC1.DT
FROM
(
SELECT DT FROM TYEARS
WHERE WD NOT IN (6, 7)
) AS RCALC1(DT)
ORDER BY DT
DT
------------
Number of rows retrieved is: 3138
Number of rows sent to output is: 0

Prepare Time is: 0.000 seconds
Execute Time is: 0.407 seconds
Fetch Time is: 0.046 seconds
Elapsed Time is: 0.453 seconds

---------------------------------------------

Summary of Results
==================
Elapsed Agent CPU Rows Rows
Statement # Time (s) Time (s) Fetched Printed
1 0.109 0.063 3138 0
2 0.453 0.406 3138 0

Arith. mean 0.281 0.234
Geom. mean 0.222 0.159

PM

"AK" <ak************@yahoo.com> a écrit dans le message de
news:46**************************@posting.google.c om...

Jeremy,

I think recursion won't run as fast as my solution.
What do you think?

[Jeremy Rickard]

ak************@yahoo.com (AK) wrote in message news:<46**************************@posting.google. com>...

Jeremy,

I think recursion won't run as fast as my solution.
What do you think?

Slightly slower, yes, but on my P3 laptop the recursive solution can
still generate almost 200 years of weekdays in a second (measured with
db2batch). I don't think the OP was planning to populate his table
more than once per day.

Surely more important, the recursive solutions didn't rely on having
an existing table with sufficient rows.
Jeremy

[AK]

> Surely more important, the recursive solutions didn't rely on having

an existing table with sufficient rows.

I see and respect your point,
but even when you have just created a new database, it already has
system tables. Enough columns for most practical cases.

[Knut Stolze]

AK <ak************@yahoo.com> wrote:

Surely more important, the recursive solutions didn't rely on having
an existing table with sufficient rows.

I see and respect your point,
but even when you have just created a new database, it already has
system tables. Enough columns for most practical cases.

That depends on your "practical case", I'd say.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

Knut,

the original poster has specified: "I'm trying to insert a range of
dates for the past say two years into the"

two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
database

Regarding "what if more rows are needed" concerns, I have an
impression that performance of recursion might degrade exponencially
when number of rows increases.

Please correct me if I'm wrong

[PM \(pm3iinc-nospam\)]

Looks linear to me in this case.
I did the recursion for 1-100 years and 1000 years.
STMT NO (nb years), elapse, agent, rows fetch, elapse/year, agent/year,
fetch/year

1 0.077 0.063 262 0.08 0.06 262.00

2 0.095 0.109 523 0.05 0.05 261.50

3 0.14 0.125 784 0.05 0.04 261.33

4 0.156 0.141 1046 0.04 0.04 261.50

5 0.202 0.188 1308 0.04 0.04 261.60

6 0.234 0.203 1568 0.04 0.03 261.33

7 0.281 0.25 1830 0.04 0.04 261.43

8 0.313 0.281 2092 0.04 0.04 261.50

9 0.359 0.328 2353 0.04 0.04 261.44

10 0.39 0.344 2614 0.04 0.03 261.40

11 0.438 0.391 2876 0.04 0.04 261.45

12 0.454 0.406 3138 0.04 0.03 261.50

13 0.484 0.438 3398 0.04 0.03 261.38

14 0.547 0.5 3660 0.04 0.04 261.43

15 0.579 0.531 3922 0.04 0.04 261.47

16 0.609 0.547 4183 0.04 0.03 261.44

17 0.656 0.594 4444 0.04 0.03 261.41

18 0.687 0.609 4706 0.04 0.03 261.44

19 0.766 0.656 4968 0.04 0.03 261.47

20 0.766 0.688 5228 0.04 0.03 261.40

21 0.829 0.75 5490 0.04 0.04 261.43

22 0.843 0.75 5752 0.04 0.03 261.45

23 0.891 0.813 6013 0.04 0.04 261.43

24 0.907 0.828 6274 0.04 0.03 261.42

25 0.969 0.859 6536 0.04 0.03 261.44

26 1 0.891 6798 0.04 0.03 261.46

27 1.015 0.922 7058 0.04 0.03 261.41

28 1.079 0.984 7320 0.04 0.04 261.43

29 1.126 1 7582 0.04 0.03 261.45

30 1.157 1.031 7843 0.04 0.03 261.43

31 1.172 1.063 8104 0.04 0.03 261.42

32 1.219 1.125 8366 0.04 0.04 261.44

33 1.234 1.141 8628 0.04 0.03 261.45

34 1.282 1.141 8888 0.04 0.03 261.41

35 1.297 1.156 9150 0.04 0.03 261.43

36 1.374 1.219 9412 0.04 0.03 261.44

37 1.47 1.328 9673 0.04 0.04 261.43

38 1.485 1.297 9934 0.04 0.03 261.42

39 1.485 1.313 10196 0.04 0.03 261.44

40 1.562 1.391 10458 0.04 0.03 261.45

41 1.578 1.391 10718 0.04 0.03 261.41

42 1.64 1.438 10980 0.04 0.03 261.43

43 1.656 1.469 11242 0.04 0.03 261.44

44 1.672 1.484 11503 0.04 0.03 261.43

45 1.704 1.5 11764 0.04 0.03 261.42

46 1.735 1.547 12026 0.04 0.03 261.43

47 1.75 1.594 12288 0.04 0.03 261.45

48 1.797 1.609 12548 0.04 0.03 261.42

49 1.843 1.656 12810 0.04 0.03 261.43

50 1.875 1.672 13072 0.04 0.03 261.44

51 1.937 1.703 13333 0.04 0.03 261.43

52 1.969 1.75 13594 0.04 0.03 261.42

53 1.984 1.781 13856 0.04 0.03 261.43

54 2.078 1.828 14118 0.04 0.03 261.44

55 2.062 1.844 14378 0.04 0.03 261.42

56 2.141 1.906 14640 0.04 0.03 261.43

57 2.172 1.953 14902 0.04 0.03 261.44

58 2.171 1.953 15163 0.04 0.03 261.43

59 2.251 2 15424 0.04 0.03 261.42

60 2.265 2.016 15686 0.04 0.03 261.43

61 2.282 2.031 15948 0.04 0.03 261.44

62 2.344 2.109 16208 0.04 0.03 261.42

63 2.407 2.156 16470 0.04 0.03 261.43

64 2.39 2.156 16732 0.04 0.03 261.44

65 2.375 2.125 16993 0.04 0.03 261.43

66 2.468 2.172 17254 0.04 0.03 261.42

67 2.5 2.234 17516 0.04 0.03 261.43

68 2.563 2.281 17778 0.04 0.03 261.44

69 2.579 2.297 18038 0.04 0.03 261.42

70 2.672 2.344 18300 0.04 0.03 261.43

71 2.719 2.391 18562 0.04 0.03 261.44

72 2.719 2.406 18823 0.04 0.03 261.43

73 2.782 2.469 19084 0.04 0.03 261.42

74 2.796 2.469 19346 0.04 0.03 261.43

75 2.797 2.484 19608 0.04 0.03 261.44

76 2.843 2.547 19868 0.04 0.03 261.42

77 2.922 2.563 20130 0.04 0.03 261.43

78 3 2.688 20392 0.04 0.03 261.44

79 2.953 2.656 20653 0.04 0.03 261.43

80 2.969 2.641 20914 0.04 0.03 261.43

81 3.047 2.719 21176 0.04 0.03 261.43

82 3.094 2.766 21438 0.04 0.03 261.44

83 3.156 2.813 21698 0.04 0.03 261.42

84 3.141 2.813 21960 0.04 0.03 261.43

85 3.156 2.766 22222 0.04 0.03 261.44

86 3.266 2.906 22483 0.04 0.03 261.43

87 3.234 2.875 22744 0.04 0.03 261.43

88 3.328 2.953 23006 0.04 0.03 261.43

89 3.391 3 23268 0.04 0.03 261.44

90 3.437 3.063 23528 0.04 0.03 261.42

91 3.438 3.047 23790 0.04 0.03 261.43

92 3.531 3.125 24052 0.04 0.03 261.43

93 3.579 3.188 24313 0.04 0.03 261.43

94 3.532 3.203 24574 0.04 0.03 261.43

95 3.61 3.172 24836 0.04 0.03 261.43

96 3.735 3.266 25098 0.04 0.03 261.44

97 3.719 3.313 25358 0.04 0.03 261.42

98 3.781 3.344 25620 0.04 0.03 261.43

99 3.781 3.344 25882 0.04 0.03 261.43

100 3.922 3.469 26143 0.04 0.03 261.43

1000 37.53 33.125 261428 0.04 0.03 261.43

PM

"AK" <ak************@yahoo.com> a écrit dans le message de
news:46**************************@posting.google.c om...

Knut,

the original poster has specified: "I'm trying to insert a range of
dates for the past say two years into the"

two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
database

Regarding "what if more rows are needed" concerns, I have an
impression that performance of recursion might degrade exponencially
when number of rows increases.

Please correct me if I'm wrong

[PM \(pm3iinc-nospam\)]

Testing table format

STMT NO
elapse
agent
rows fetch
elapse/year
agent/year
fetch/year

1
0.077
0.063
262
0.08
0.06
262.00

2
0.095
0.109
523
0.05
0.05
261.50

3
0.14
0.125
784
0.05
0.04
261.33

4
0.156
0.141
1046
0.04
0.04
261.50

5
0.202
0.188
1308
0.04
0.04
261.60

6
0.234
0.203
1568
0.04
0.03
261.33

7
0.281
0.25
1830
0.04
0.04
261.43

8
0.313
0.281
2092
0.04
0.04
261.50

9
0.359
0.328
2353
0.04
0.04
261.44

10
0.39
0.344
2614
0.04
0.03
261.40

11
0.438
0.391
2876
0.04
0.04
261.45

12
0.454
0.406
3138
0.04
0.03
261.50

13
0.484
0.438
3398
0.04
0.03
261.38

14
0.547
0.5
3660
0.04
0.04
261.43

15
0.579
0.531
3922
0.04
0.04
261.47

16
0.609
0.547
4183
0.04
0.03
261.44

17
0.656
0.594
4444
0.04
0.03
261.41

18
0.687
0.609
4706
0.04
0.03
261.44

19
0.766
0.656
4968
0.04
0.03
261.47

20
0.766
0.688
5228
0.04
0.03
261.40

21
0.829
0.75
5490
0.04
0.04
261.43

22
0.843
0.75
5752
0.04
0.03
261.45

23
0.891
0.813
6013
0.04
0.04
261.43

24
0.907
0.828
6274
0.04
0.03
261.42

25
0.969
0.859
6536
0.04
0.03
261.44

26
1
0.891
6798
0.04
0.03
261.46

27
1.015
0.922
7058
0.04
0.03
261.41

28
1.079
0.984
7320
0.04
0.04
261.43

29
1.126
1
7582
0.04
0.03
261.45

30
1.157
1.031
7843
0.04
0.03
261.43

31
1.172
1.063
8104
0.04
0.03
261.42

32
1.219
1.125
8366
0.04
0.04
261.44

33
1.234
1.141
8628
0.04
0.03
261.45

34
1.282
1.141
8888
0.04
0.03
261.41

35
1.297
1.156
9150
0.04
0.03
261.43

36
1.374
1.219
9412
0.04
0.03
261.44

37
1.47
1.328
9673
0.04
0.04
261.43

38
1.485
1.297
9934
0.04
0.03
261.42

39
1.485
1.313
10196
0.04
0.03
261.44

40
1.562
1.391
10458
0.04
0.03
261.45

41
1.578
1.391
10718
0.04
0.03
261.41

42
1.64
1.438
10980
0.04
0.03
261.43

43
1.656
1.469
11242
0.04
0.03
261.44

44
1.672
1.484
11503
0.04
0.03
261.43

45
1.704
1.5
11764
0.04
0.03
261.42

46
1.735
1.547
12026
0.04
0.03
261.43

47
1.75
1.594
12288
0.04
0.03
261.45

48
1.797
1.609
12548
0.04
0.03
261.42

49
1.843
1.656
12810
0.04
0.03
261.43

50
1.875
1.672
13072
0.04
0.03
261.44

51
1.937
1.703
13333
0.04
0.03
261.43

52
1.969
1.75
13594
0.04
0.03
261.42

53
1.984
1.781
13856
0.04
0.03
261.43

54
2.078
1.828
14118
0.04
0.03
261.44

55
2.062
1.844
14378
0.04
0.03
261.42

56
2.141
1.906
14640
0.04
0.03
261.43

57
2.172
1.953
14902
0.04
0.03
261.44

58
2.171
1.953
15163
0.04
0.03
261.43

59
2.251
2
15424
0.04
0.03
261.42

60
2.265
2.016
15686
0.04
0.03
261.43

61
2.282
2.031
15948
0.04
0.03
261.44

62
2.344
2.109
16208
0.04
0.03
261.42

63
2.407
2.156
16470
0.04
0.03
261.43

64
2.39
2.156
16732
0.04
0.03
261.44

65
2.375
2.125
16993
0.04
0.03
261.43

66
2.468
2.172
17254
0.04
0.03
261.42

67
2.5
2.234
17516
0.04
0.03
261.43

68
2.563
2.281
17778
0.04
0.03
261.44

69
2.579
2.297
18038
0.04
0.03
261.42

70
2.672
2.344
18300
0.04
0.03
261.43

71
2.719
2.391
18562
0.04
0.03
261.44

72
2.719
2.406
18823
0.04
0.03
261.43

73
2.782
2.469
19084
0.04
0.03
261.42

74
2.796
2.469
19346
0.04
0.03
261.43

75
2.797
2.484
19608
0.04
0.03
261.44

76
2.843
2.547
19868
0.04
0.03
261.42

77
2.922
2.563
20130
0.04
0.03
261.43

78
3
2.688
20392
0.04
0.03
261.44

79
2.953
2.656
20653
0.04
0.03
261.43

80
2.969
2.641
20914
0.04
0.03
261.43

81
3.047
2.719
21176
0.04
0.03
261.43

82
3.094
2.766
21438
0.04
0.03
261.44

83
3.156
2.813
21698
0.04
0.03
261.42

84
3.141
2.813
21960
0.04
0.03
261.43

85
3.156
2.766
22222
0.04
0.03
261.44

86
3.266
2.906
22483
0.04
0.03
261.43

87
3.234
2.875
22744
0.04
0.03
261.43

88
3.328
2.953
23006
0.04
0.03
261.43

89
3.391
3
23268
0.04
0.03
261.44

90
3.437
3.063
23528
0.04
0.03
261.42

91
3.438
3.047
23790
0.04
0.03
261.43

92
3.531
3.125
24052
0.04
0.03
261.43

93
3.579
3.188
24313
0.04
0.03
261.43

94
3.532
3.203
24574
0.04
0.03
261.43

95
3.61
3.172
24836
0.04
0.03
261.43

96
3.735
3.266
25098
0.04
0.03
261.44

97
3.719
3.313
25358
0.04
0.03
261.42

98
3.781
3.344
25620
0.04
0.03
261.43

99
3.781
3.344
25882
0.04
0.03
261.43

100
3.922
3.469
26143
0.04
0.03
261.43

1000
37.53
33.125
261428
0.04
0.03
261.43

PM

[AK]

PM,

thank you for the correction

[Knut Stolze]

AK <ak************@yahoo.com> wrote:

Knut,

the original poster has specified: "I'm trying to insert a range of
dates for the past say two years into the"

two years < 1K days < number of rows in SYSCAT.COLUMNS in an empty
database
I don't argue against that.
But still I would personally not rely on it. Maybe some change in the
application logic requires now 4 years and then IBM might change something
in the DB2 catalog (e.g. maybe not externalizing the SYSIBM tables - who
knows) and the scheme doesn't work any longer in a predictable way.
Regarding "what if more rows are needed" concerns, I have an
impression that performance of recursion might degrade exponencially
when number of rows increases.

Possibly not, as PM demonstrated.

Besides, you could write your own table function that takes a start and end
date and calculates all the dates in between. Its trivial to implement and
has a linear run time. For my personal taste, this would be much safer.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[AK]

Knut,

Besides, you could write your own table function that takes a start and end
date and calculates all the dates in between. Its trivial to implement and
has a linear run time. For my personal taste, this would be much safer.

I think this is a matter of personal preferences. I have created a
permanent calendar table and populated it just once, then I added
indexes on it. It's being used all the time for frequently running
queries like "7 business days from now" or "how many people weren't
doing anything last Monday" and so on. I was considering a table UDF,
but that seemed to be re-doing one and the same work over and over
again many times every hour.

What do you think?

[Knut Stolze]

AK <ak************@yahoo.com> wrote:

Knut,

Besides, you could write your own table function that takes a start and
end
date and calculates all the dates in between. Its trivial to implement
and
has a linear run time. For my personal taste, this would be much safer.

I think this is a matter of personal preferences. I have created a
permanent calendar table and populated it just once, then I added
indexes on it. It's being used all the time for frequently running
queries like "7 business days from now" or "how many people weren't
doing anything last Monday" and so on. I was considering a table UDF,
but that seemed to be re-doing one and the same work over and over
again many times every hour.

If you have static data, then I would (most probably) also materialize it in
a table. It's much better if DB2 knows the kind of data it has to deal
with in such a case.

My thoughts on the table function just for (or rather against) the
"performance of recursion might degrade exponencially".

Also, my considerations on the stable mechanism to generate those date
values applies primarily for the case where the values need to be generated
more often. For a one time shot, I might not bother with something more
complex either, and then check if anything is missing or wrong and clean it
up manually.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena