Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I don't need table names in child job so I didn't pass the table name to child job as you can see in the child job's tDBInput component below,
**In the child job, tDBConnection_1 is the target db(MSSQL) for loading PK constraint and tDBConnection_2(MySQL) is the output database for injecting PK constraint.
Child Job
The parent job copy tables from target database (MSSQL) to the output database(MySQL).
Then the child job read the target database(MSSQL)'s constraint and inject it into the output database(MySQL).
So basically I need the parent job to finish copying the tables(MSSQL->MySQL) before starting the child job.
(As the child job couldn't operate with a blank database).
That's how it work when operating the parent and child job separately,, would appreciate if you could guide me how I can make it work when starting the operation from parent job.
Here I tried to pass the tables from parent job to child job, then run parent job,
but still the result is the same
Parent job's result
Parent's job context param
Child's job context
Calling using the context in tDBRow Component (since DBInput doesn't need the parent's job output)
If this is not the right way, please let me know...
Here I use context variable on Table name in tDBInput component and also in the output tDBRow component.
But still the result is the same. . .
Are there any ways to fixed this?
Run Log
I tried trigger tSubJobOk from tRunJob to tDBInput and it partially work.
But somehow, the last table's didn't have PK constraint injected. (in the output database, there's a table and its data, but no PK constraint)
When I check the log, it seems like the tables are created and its PK are loaded, but PK constraint are not inserted into the table.
Run Log
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);
In the other job, I have experience a similar problem where the last table's data is not inserted to the output database.
At that time, I changed tDBOutput repository from using existing tDBConnection to manual setting and it worked.
So I tried to do the same for tDBRow's, but it didn't work, still the last table didn't have its PK constraint inserted.
And also below I also tried connecting tDBConnection with tDBCommit using OnComponentOK, but still no difference.
do you have any ideas?