Scheduling

[ksmurthys]

Hi ALL,

I am directly extracting data from AS/400 DB2 Database.(Usually somebody will extract the data from DB2 n give it to us weekly or daily or monthly basis).Here i need to set up all the jobs on weekly basis,how do i design that job?

Thanks,
Ram.

[bryan]

I dont think you were precise in what you needed.

Since you are able to extract your data from AS400, you design the job and SCHEDULE your job to run on weekly basis either using Windows Scheduler or Cron in Unix.

Thanks
Bryan

[ksmurthys]

Brain,
I was connecting to DB2 using ODBC DSN.
IT people load data every week(simply they are inserting rows into their system as is).I loaded history data till 09/30/2004, now i want to load only data between the 10/01/2004(eventough source system contains all the previous data) n would like to schedule that job monthly or weekly.
Could u tell me how to pass parameters for this kind of jobs?

Ram.

[bryan]

dude, you still not giving us enough details

you say, you want to schedule your job but you dont tell us whats your scheduling tool is or is it just basic cron or windows

you extracting data from DB2 and where are you loading this table into?

At the source side while you extract data you can leverage database to give you the records by using WHERE Tab in your stage properties and give your date range in the query.

At the target, if you not maintaining any history or not doing any update,
select your update action as "Insert rows without clearing". So, you will have the previous data as it is and your new data will be loaded.

[ksmurthys]

Brian,
I need to schedule this jon on WINDOWS.
I tried update action "insert rows without clearing",its simply inserting all the records into target i.e i am getting dupliacte rows in staging table.

[ogmios]

Brian,
I need to schedule this jon on WINDOWS.
I tried update action "insert rows without clearing",its simply inserting all the records into target i.e i am getting dupliacte rows in staging table.

insert rows without clearing is ok, but put a where clause on the input statement (the SQL you use to extract the data), so that you only select rows that are not in your target table.

Ogmios

[ksmurthys]

Hi,

The source columns are Custno,year,month,dol1,....dol31 n take for example i loaded data today.I need to load data every week ...could u guide where do i put WHERE CLAUSE ??? n on which column??

[ogmios]

Hi,

The source columns are Custno,year,month,dol1,....dol31 n take for example i loaded data today.I need to load data every week ...could u guide where do i put WHERE CLAUSE ??? n on which column??

On year and month... but there are some other things involved. What I would do:
- write a BASIC scheduling job that selects the highest year || month from the target table. and gives this as arguments to your real job.
- In your real job put 2 extra parameters in there: YEAR and MONTH.
- In the SQL in your input stage do something as "select col1, col2, ... from sourectable where year > #YEAR# or (year = #YEAR# and month >= #MONTH#). And then use update or insert in your target table putting as key columns "custno, year, month" provided they are unique. In this way you will always re-update the rows of the last month as well.

If you won't do this and suppose your job fails and you rerun you would not finish loading the current month as the month would already be in the target (and so using > on month would not load the rest of the data).

Several other methods can be devised, this is just an example of how it could be done.

Ogmios