Retain the status of a job in a table

Four Stars

Retain the status of a job in a table

Hello,

I have a job that imigration of data from a green database another, in fact, I have the following architecture:

- source database that contains 2 table tab1 (id, col1, col2) and tab2 (id, col1, col2) that will migrate to a destination database that contains:
3 table tab1 (id, col1, col2), tab2 (id, col1, col2), and table_state (state_id, state, date),
the imigration does this as follows:

source database: destination database:
      tab1:
                            id ----------------------> id
                            col1 --------------------> col1
                            col2 --------------------> col2
      tab2:
                            id ----------------------> id
                            col1 --------------------> col1
                            col2 --------------------> col2
now, what I want to achieve is to feed the tabe table_etat by:
- id_state: an auto incremented identifier.
- state: yes or no, yes if the imigration is done successfully, no if not.
- date: date and time of immigration.

 

do you have any idea how to make this job run successfully ?

 

Thank's

Eight Stars

Re: Retain the status of a job in a table

Hello,

 

This is not easy to manage outside of the database. I would recommend to call SP which will check if the record was successfully inserted. See an example bellow.

The job is quite simple:


1.JPG

 

 

 

 

 

 

 

 

 

 

 

 

But you need to create a stored procedure which will manage it. Here is an example how to do it.22.JPG

 This procedure will insert no into table_etat when there will be an attempt for insert of duplicated ID.

You can change it to e.g. all errors (DECLARE EXIT HANDLER FOR SQLEXCEPTION).

 

I hope it helps you.

 

Regards

Ondrej

 

 

Four Stars

Re: Retain the status of a job in a table

Hello,

thank's for your anser, 

I thought about the stored procedure and trigger, but the trouble is i do not know the source and destination database it mysql or oracle, then for the stored procedure you have to trigger it manually, for that it is always a problem .

 

 

 

Eight Stars

Re: Retain the status of a job in a table

This was an example for MySQL.

You can do the same for Oracle, Postgres, Db2... Using the same approach, not the same code.

 

 

 

Four Stars

Re: Retain the status of a job in a table

I'm working with the General JDBC, becauce i don't know exactly what type of database well be used by my solution, so it's better to try to include the insertion in the job, this is my worry, did you get what i'm searching about !!!

 

thank you .