Creation date for tables

Hi,
This is my first time posting in this forum. I am very green in DB2 admin so I would really appreciate your help.
The company had a data warehouse on DB2 7.1 on a sun solaries 8
I want to know how to retreive creation date from any table
ThanksDid you mean to post this in the SQL forum?

Regards,

hmscott|||Right church, wrong pew...

no matter...do this against your database...it should show you the definition of your tables and columns

SELECT * FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = 'your owner id'

And try here...


http://www.dbforums.com/f8/

Creating table daily

How can I create a table daily, whose name was the current date in the yy/mm/dd format plus the algarism to identify the weekday (for example Wednesday = 4), for instance if the query was run on 01/15/2003 (wednesday), the tabelname would be 3_01_15_4 ?Originally posted by cassianorsilva
How can I create a table daily, whose name was the current date in the yy/mm/dd format plus the algarism to identify the weekday (for example Wednesday = 4), for instance if the query was run on 01/15/2003 (wednesday), the tabelname would be 3_01_15_4 ?

declare @.date char(9)
set @.date = substring(convert(char(4),getdate(),121),4,1)+'_'+ substring(convert(char(7),getdate(),121),6,2)+'_'+ substring(convert(char(10),getdate(),121),9,2)
+'_'+cast(DATEPART(dw, GETDATE()) as char(1))|||Thank you !

The table creation is not included on the proceedure above, right ?|||Originally posted by cassianorsilva
Thank you !

The table creation is not included on the proceedure above, right ?

No it's not, once u have the string in the @.date variable, just concatenate that with ur tablename and use that string in ur table creation

says ur final tablename is in a variable @.tablename.

exec('Create table '+@.tablename)

Sumthin around those lines

Creating Special Relative Date Categories

I need to re-create special relative date categories in SSAS similar to the functionality offered by Cognos/Powerplay/Tranformer. My problem is that we are replacing Cognos PowerPlay/Transformer with SSAS. With Cognos, you can create relative time categories very easily. The relative time categories are part of the time dimension. When setting up these special time categories, you tell Cognos how to determine the relative time based on the current date or some other calculated date. As a result, the Cognos relative time categories like YTD, MTD, Yesterday etc. are all based off this date. Every time you refresh the cube, the relative date categories change. The big difference between SSAS and Cognos is that SSAS apparently requires you to bring the date hierarchy into a row or column. Whereas, in Cognos, the relative date categories are independent.

For example, I have reports that have several relative date categories in columns like -- Yesterday, WTD, MTD, YTD, Prior YTD etc. Under these columns, I have Sales Dollars, Sales Units, Cost, etc. In rows, I have divisions and products. Cognos knows that Yesterday was January 1, and WTD represents Sunday through Tuesday etc. The relative dates are not dependent on me placing the time hierarchy on the grid.

Is it possible to duplicate this functionality in SSAS? That is, can I create calculated “relative dates” that will change based on the current date or some lag from the current date? Thus, when I add these relative dates to the report, they will always reflect an offset from the current date. It sounds like this can be done through some MDX statement based on the current date but I'm not sure how to do this. Can anyjone provide me with some guidance on this?

Thank you.

David Greenberg

First, it is correct that you will have to feed SSAS2005 with attributes for date, month, quarter and year, but it is fairly simple to this directly in your dimension table or in the datasource view for the cube.

You can use the TSQL DATEPART(), YEAR(), QUARTER()-functions for this. Have a look in Books On Line for date-functions.

When you build the time dimension in Business Intelligence Developer Studio, you are assisted with a guide that will help you will building user hierarchies in the time dimension.

After that you can either create your MDX-time calculations yourself, in the calculation tab of the cube editor, or use the Business Intelligence wizard on the time dimension to get assistance with the MDX.

Here are some useful links

http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx (a little bit advanced)

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

http://www.databasejournal.com/features/article.php/3593466 . Look for William E. Pearson

HTH

Thomas Ivarsson

|||

Hey David,

I have the same problem with the relative dates, I am trying to migrate/re-create our cubes from Cognos to SSAS, when I got to the relative date part I got stuck and I couldn't figure out how to proceed.

I bought 2 books one of them is purely MDX Scripts and none of the books talk about a relative date!!!!!

Were you able to figure out how to incorporate the relative date in SSAS?

Thank you

John Ghannam

|||

Hi John,

I finally figured this out after several months of trying. I even purchased two MDX books myself. I tried a solution to code the MDX with the current date but it just didn't work properly. My solution doesn't use MDX but you can extend the functionality with MDX.

What I developed works very well in terms of query performance and flexibility. My solution requires a view over the date dimension table. Using T-SQL, I wrote a bunch of SQL statements that will return all of the relative dates used in PowerPlay Transformer like YTD, Last 12 Months, Last Month, MTD, WTD etc. All of these statements are based on supplying a starting date that is based off of getdate(). I needed to set the "current date" to be today's date minus one day since we want to track our shipments through the last completed day. The process of figuring this out was tedious but my custom solution works better and faster than using a calculated member because the data is stored in the cube rather than derived at query run-time. With this solution, I was able to figure out a complete process for converting our Cognos environment into SSAS.

The T-SQL statements to derive the relative dates are a bit tricky but you can write me at david@.appliedbusinessintelligence.com if you need help.

David Greenberg

Creating Special Relative Date Categories

I need to re-create special relative date categories in SSAS similar to the functionality offered by Cognos/Powerplay/Tranformer. My problem is that we are replacing Cognos PowerPlay/Transformer with SSAS. With Cognos, you can create relative time categories very easily. The relative time categories are part of the time dimension. When setting up these special time categories, you tell Cognos how to determine the relative time based on the current date or some other calculated date. As a result, the Cognos relative time categories like YTD, MTD, Yesterday etc. are all based off this date. Every time you refresh the cube, the relative date categories change. The big difference between SSAS and Cognos is that SSAS apparently requires you to bring the date hierarchy into a row or column. Whereas, in Cognos, the relative date categories are independent.

For example, I have reports that have several relative date categories in columns like -- Yesterday, WTD, MTD, YTD, Prior YTD etc. Under these columns, I have Sales Dollars, Sales Units, Cost, etc. In rows, I have divisions and products. Cognos knows that Yesterday was January 1, and WTD represents Sunday through Tuesday etc. The relative dates are not dependent on me placing the time hierarchy on the grid.

Is it possible to duplicate this functionality in SSAS? That is, can I create calculated “relative dates” that will change based on the current date or some lag from the current date? Thus, when I add these relative dates to the report, they will always reflect an offset from the current date. It sounds like this can be done through some MDX statement based on the current date but I'm not sure how to do this. Can anyjone provide me with some guidance on this?

Thank you.

David Greenberg

First, it is correct that you will have to feed SSAS2005 with attributes for date, month, quarter and year, but it is fairly simple to this directly in your dimension table or in the datasource view for the cube.

You can use the TSQL DATEPART(), YEAR(), QUARTER()-functions for this. Have a look in Books On Line for date-functions.

When you build the time dimension in Business Intelligence Developer Studio, you are assisted with a guide that will help you will building user hierarchies in the time dimension.

After that you can either create your MDX-time calculations yourself, in the calculation tab of the cube editor, or use the Business Intelligence wizard on the time dimension to get assistance with the MDX.

Here are some useful links

http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx (a little bit advanced)

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

http://www.databasejournal.com/features/article.php/3593466 . Look for William E. Pearson

HTH

Thomas Ivarsson

|||

Hey David,

I have the same problem with the relative dates, I am trying to migrate/re-create our cubes from Cognos to SSAS, when I got to the relative date part I got stuck and I couldn't figure out how to proceed.

I bought 2 books one of them is purely MDX Scripts and none of the books talk about a relative date!!!!!

Were you able to figure out how to incorporate the relative date in SSAS?

Thank you

John Ghannam

|||

Hi John,

I finally figured this out after several months of trying. I even purchased two MDX books myself. I tried a solution to code the MDX with the current date but it just didn't work properly. My solution doesn't use MDX but you can extend the functionality with MDX.

What I developed works very well in terms of query performance and flexibility. My solution requires a view over the date dimension table. Using T-SQL, I wrote a bunch of SQL statements that will return all of the relative dates used in PowerPlay Transformer like YTD, Last 12 Months, Last Month, MTD, WTD etc. All of these statements are based on supplying a starting date that is based off of getdate(). I needed to set the "current date" to be today's date minus one day since we want to track our shipments through the last completed day. The process of figuring this out was tedious but my custom solution works better and faster than using a calculated member because the data is stored in the cube rather than derived at query run-time. With this solution, I was able to figure out a complete process for converting our Cognos environment into SSAS.

The T-SQL statements to derive the relative dates are a bit tricky but you can write me at david@.appliedbusinessintelligence.com if you need help.

David Greenberg

Creating rows based on date range from another table

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:
>
PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
><...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
><...>
200602 2005-12-18 75
>
CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)
>
INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)

Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:

CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0

Given this table, we can write this query:

SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob

Erland Sommarskog wrote:

Quote:

Originally Posted by

rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD DATE DAY_NO
200602 2005-07-06 -89
200602 2005-07-07 -88
200602 2005-07-08 -87
<...>
200602 2005-10-02 -2
200602 2005-10-03 -1
200602 2005-10-04 0
200602 2005-10-05 1
<...>
200602 2005-12-18 75

CREATE TABLE "dbo"."tblDates"
("PERIOD" CHAR(6) NOT NULL,
"START_DT" DATETIME NULL,
"MID_DT" DATETIME NULL,
"END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

>
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
>
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
>
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number 0
>
Given this table, we can write this query:
>
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS

Yes, the above format could fail. There are three date formats in SQL
Server that are safe:

YYYYMMDD
YYYYMMDDTHH:MM:SS[.fff]
YYYY-MM-DDZ

Here T and Z stand for themselves.

Other formats are interpretated depending on DATEFORMAT and LANGUAGE
setting, and can fail or produced unexpected results if you don't know
what is going on.

Quote:

Originally Posted by

Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)

Yes, that dates table is essentially a table of numbers with a different
names. In fact, it appears that it has all the numbers as well!

I used a table of numbers, as numbers is the more general concept and
can be used in more places. But in fact, I added a table of dates to
our system before I added a table of numbers.

I leave it as an exercise to you how to use the dates table instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Sun, 20 Aug 2006 12:38:11 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to build a table based on values from another table.
>I need to populate a table between two dates from another table. Using
>the START_DT and END_DT, create records between those dates.
>I need a new column that is the days between the date and the MID_DT
>The data I wish to end with would look something like this:
>>
>PERIOD DATE DAY_NO
>200602 2005-07-06 -89
>200602 2005-07-07 -88
>200602 2005-07-08 -87
>><...>
>200602 2005-10-02 -2
>200602 2005-10-03 -1
>200602 2005-10-04 0
>200602 2005-10-05 1
>><...>
>200602 2005-12-18 75

[snip]

Quote:

Originally Posted by

Quote:

Originally Posted by

>INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)

Quote:

Originally Posted by

>Anyway, as I said in another newsgroup, you need a table of numbers. Here
>is a way to create such a table with a million numbers:

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

What are the pros and cons of relying on such a table vs. using a
WHILE loop? Based on Rob's context of student registrations, let's
assume we're talking about a maximum of 300 iterations per row in
the original tblDates table.

The one risk with a table of numbers is that if you run of numbers, you
will get an incorrect result. That is one reason why I'm reluctant to
use it, if there are alternative solutions. But for a case like this,
when you need to fill up a space, a table of numbers - or dates - is what
you need.

A loop is more complex to program, and easier go wrong. And as a generic
solution, you face scalability problems.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Aug 2006 08:05:49 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.sewrote:

Quote:

Originally Posted by

>Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>What are the pros and cons of relying on such a table vs. using a
>WHILE loop? Based on Rob's context of student registrations, let's
>assume we're talking about a maximum of 300 iterations per row in
>the original tblDates table.

>
>The one risk with a table of numbers is that if you run of numbers, you
>will get an incorrect result. That is one reason why I'm reluctant to
>use it, if there are alternative solutions. But for a case like this,
>when you need to fill up a space, a table of numbers - or dates - is what
>you need.
>
>A loop is more complex to program, and easier go wrong.

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:

x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while

versus

select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)

Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Quote:

Originally Posted by

And as a generic
>solution, you face scalability problems.

I kind of figured. The query seems easy to get wrong, though, if
you're not familiar with the pattern; I first wrote it as "where
dateadd(first_date, n) between first_date and last_date", but that
seems like it'd be a good bit slower.|||Ed Murphy (emurphy42@.socal.rr.com) writes:

Quote:

Originally Posted by

I disagree, but then I have somewhat more experience with imperative
than functional programming. Consider:
>
x = first_date
while x <= last_date
insert x, datediff(x, mid_date) into <table>
x = dateadd(x, 1)
end while
>
versus
>
select dateadd(first_date, n), n - datediff(mid_date, first_date)
into <table>
from numbers
where n between 0 and datediff(end_date, first_date)
>
Okay, "where n between <limits>" makes sense as an analogue to a while
loop, but that stuff in line 1 looks like the stuff of headaches.

Loops are particularly prone to two sorts of errors:

* They goes on forever, could be because of a sloppy mistake, of because the
logic is complicated.
* One-off errors because of incorrect loop conditions.

One-off errors are easy to make with set-based queries as well, but the
risk of infinite loops is nothing you have to lose sleep over.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx