daily data based on Date ?

[fahad]

Hi all,
am kind of beginner in DS and i have to write jobs that take data from AS400/db2 to Oracle on a daily basis.. the data to be pulled is that with today's date.
i tried to make a parameter variable in the select statement but this will have a manual entry. is there any way to make it auto??

note that date in the source is of format char(8)

thanks in advance
Fahad

[ogmios]

Any database has something as "current date" that you can use in a select statement. You could also pass the argument via DataStage by using @Date, Date() or any of the other "current day" functions.

Personally I would not use 'current date' directly in the SQL select, but rather pass it in via an argument (that gets retrieved once from a database table containing 1 row). Imagine something goes wrong with your ETL jobs and you have to rerun on another date than "today". This would be pretty painful if you used "current date" in SQL directly.

Ogmios

[vmcburney]

You can almost guarantee that at some point you will need to rerun a previous day. It is unlikely that your jobs will run with a 100% success rate or that your server will always be working, when you fall behind a day or two (or 10) or take the server down for maintenance or upgrade you will need to retrospectively run previous days. A simple way to do this is to have a job parameter that accepts a date, if it is left blank use the current date, if it is populated use that date.