Execute Child Job from Parent Job (Job order issue)

[Anonymous]

Hi,

I have two jobs that 1) copy tables to output database and 2) inject PK constraint to output database.

I want to run the whole parent job (copy DB) and then run child job (inject PK constraint to DB)

With the current job design, the child jobs (PK constraint injection) runs immediately after the first table is created in the parent job.

Without all the corresponding tables created in advance, the job freezes.

PS. Initially I wanted to triggered tRunJob component with OnSubJobOk, but there's only OnComponentOk.

PS2. When executed separately, both job works fine. Also it works fine when I first tested it with a DB with two tables.

Is there a way to fixed this? Jobs file attached

Parent Job

Child Job

Error Log

Starting job Migration_DB_Parent_Job at 19:05 24/03/2020.
[statistics] connecting to socket on port 3864
[statistics] connected
Table Name = DOM_AIR_MIN_PRICELIST
dbo.DOM_AIR_MIN_PRICELIST|Table|Primary key|PK_DOM_AIR_MIN_PRICELIST|start_point_cd, end_point_cd, airline_cd, tour_kind, yyyymm
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|yyyymm, mem_id, tour_id
dbo.DOM_AIR_SEARCH_ONEWAY_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ONEWAY_TICKETS|mem_id, tour_id
dbo.DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|Table|Primary key|PK_DOM_AIR_SEARCH_ROUNDTRIP_TICKETS|mem_id, tour_id
dbo.DOM_AIRLINE|Table|Primary key|PK_DOM_AIRLINE|airline_cd
dbo.premium_bid_for_dair|Table|Primary key|PK_premium_bid_for_dair|id
dbo.premium_entry_mem|Table|Primary key|PK_premium_entry_mem|mem_id, product_type
dbo.tbl_site_mem|Table|Primary key|PK_tbl_site_mem|site_id, mem_id
dbo.tbl_site_mem_agent|Table|Primary key|PK_tbl_site_mem_agent|site_id, mem_id
dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS 
 AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS ADD CONSTRAINT PK_DOM_AIR_MONTHLY_SEARCH_ONEWAY_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);


Job Migration_DB_Parent_Job ended at 19:06 24/03/2020. [Exit code  = 1]
Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't existTable 'dbo.tbl_site_mem' doesn't existTable 'dbo.dom_tour_for_da_roundtrip_search' doesn't existTable 'dbo.dom_air_monthly_search_roundtrip_tickets' doesn't existTable 'dbo.dom_air_search_oneway_tickets' doesn't existTable 'dbo.premium_bid_for_dair' doesn't existTable 'dbo.tbl_site_mem_agent' doesn't existTable 'dbo.dom_air_pl_monthly' doesn't existTable 'dbo.dom_airline0' doesn't exist
[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_air_monthly_search_oneway_tickets' doesn't exist
dbo.tbl_site_mem|ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);
Table Name = dbo.tbl_site_mem 
 AND the Query is = ALTER TABLE dbo.tbl_site_mem ADD CONSTRAINT PK_tbl_site_mem PRIMARY KEY (site_id, mem_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.tbl_site_mem' doesn't exist
dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH|ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);
Table Name = dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH 
 AND the Query is = ALTER TABLE dbo.DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH ADD CONSTRAINT PK_DOM_TOUR_FOR_DA_ROUNDTRIP_SEARCH PRIMARY KEY (mem_id, tour_id, money_start_date, money_end_date);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'dbo.dom_tour_for_da_roundtrip_search' doesn't exist
dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS|ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);
Table Name = dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS 
 AND the Query is = ALTER TABLE dbo.DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS ADD CONSTRAINT PK_WORK_DOM_AIR_MONTHLY_SEARCH_ROUNDTRIP_TICKETS PRIMARY KEY (yyyymm, mem_id, tour_id);

......

However when I change target db to the one with two tables, it worked,, though there are error message.

Here's the log

[statistics] connected
Table Name = tbl_inq
traveltour.tbl_inq|Table|Primary key|PK_tbl_inq|mem_id, inq_id
traveltour.tbl_mem|Table|Primary key|PK_tbl_mem|mem_id
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

Table 'traveltour.tbl_mem' doesn't existMultiple primary key defined
[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Table 'traveltour.tbl_mem' doesn't exist
traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

Table Name = tbl_mem
traveltour.tbl_inq|Table|Primary key|PK_tbl_inq|mem_id, inq_id
traveltour.tbl_mem|Table|Primary key|PK_tbl_mem|mem_id
traveltour.tbl_mem|ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);
Table Name = traveltour.tbl_mem 
 AND the Query is = ALTER TABLE traveltour.tbl_mem ADD CONSTRAINT PK_tbl_mem PRIMARY KEY (mem_id);

traveltour.tbl_inq|ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);
Table Name = traveltour.tbl_inq 
 AND the Query is = ALTER TABLE traveltour.tbl_inq ADD CONSTRAINT PK_tbl_inq PRIMARY KEY (mem_id, inq_id);

[ERROR]: local_test.primary_key_0_1.Primary_Key - tDBRow_1 - Multiple primary key defined
[statistics] disconnected

[manodwhb]

If you want to run the subjob using subjob OK then take on subjob OK from tdbinput.

[Anonymous]

@ manodwhb

But the thing is that I wanted get child job running after parent job is done (tables copied from tDBInput to tDBOutput). so I thought of using a trigger on tDBOutput,,,
since the child job will need tables in parent's job tDBOutput..

[manodwhb]

You can use the trigger of on subjob OK from tdbinputand connect to your child job.

[Anonymous]

@manodwhb 

I did as suggested but still the same result ....

[manodwhb]

As per the error your table is not created for the which you're trying to do next operation.

[Anonymous]

OnSubjobOk is only available on the first component of the sub job (the blue rectangle).

OnComponentOk is available on every component and means you trigger at the end of the flow especially at the end for the particular component.

Now it depends when you want to start your child job. Once per record or at the end of the flow. In this case why not trígger the child job just before you trigger your commit.

[Anonymous]

@manodwhb
What I mean is that I use subjob OK from tdbinput as suggested and it didn't work.
Is there way to finish runing the whole parent job before passing the operation to child job?

[Anonymous]

@lli 

Thanks for the explanation, I was a bit confuse about subjobOK and componentOK....

I tried trigger child job from tDBCommit component but still the same result....

When the operation starts, the parent job create one table and then the child job started its operation (injecting PK constraint the to tables).

For simplicity, I wanted the parent job to finish running its operation before starting the child job.

Do you know how I could fix this?

I attached the jobs file for reference

Migration_DB.zip

[manodwhb]

Are you passing the same table to child job to create primary keys?