Last Table's Data is not migrated (tDBOutput)

[Anonymous]

Hi,

Using the following job and settings, I was able to dynamically migrate a whole database from MSSQL to MySQL. However, I noticed that the last table's data is blank (only the table structure and column is migrated).

**The table and its schema is migrated, but no data inside the table. // I'm using a dynamic schema.

Job Design and DBInput

dbinput

DBOutput

DBOutput

When I tried to logged each component, I found that the last table's data is loaded and pass on from tDBInput to tMap and also from tMap to tDBOutput, as the last table's data showed up in the log.

However, when I check the actual output database, only the last table contains no data at all.

Output DB's 1st Table - Have data

2nd (last) table - No Data

[nivedhitha]

@crotmn ,

I have 2 suggestions for you

1. Can you try connecting to tDbInput_1 from tJava_1 on 'Iterate' link and run the job again?

Now that tDbInput is connected on 'On Component Ok', it is triggered only after all the iteration are done. But you want tne tDbInput subjob to also be iterated.

If this solution doesnt work then try the 2nd option

2. Create a subjob and move the tDbInput subjob to the child job and call this on 'Iterate' link.

Let me know if neither of these work

. 

[Anonymous]

@nivedhitha 

Thanks for your answer.

1. Can you try connecting to tDbInput_1 from tJava_1 on 'Iterate' link and run the job again?

I tried that but the following error comes up from tDBInput_1.

"A component that is not a subprocess start cant have link on component link."

so I tried the 2nd suggestion.

2. Create a subjob and move the tDbInput subjob to the child job and call this on 'Iterate' link.

Here's is the job design for the parent and child job.

Parent-job

Parent-job

test04_1(Child-job)

Child-job

It seems that the child job is not called with the current job design.

Can you help point up what I can improve?

Thank you

[nivedhitha]

@crotmn , 

I'm pretty sure the job is being called. It is just that the inputs(table name) the child job needs are not being passed here.

You will not be able to access tableList's global variable in the child job and hence they must be passed as context variables.

Create a context variable in the child job first say 'tableName' and then pass the value for this in the tRunJob of the parent job under 'context param'. When you try to add entries, the context variables in the child automically comes up in the drop down and assign value for it using the global variable.

And then update the tDbInput component in the child job to use the context variable rather than the global variable.

With this design, for very iteration, a different table name is passed to the child.

Let me know if it doesnt work

[Anonymous]

@nivedhitha 

Thanks for getting back to me.

I see what you mean, but I'm not confident about context variables, so please let me confirm my understanding here.

I tried to fixed my job design as in the screenshot below and I have an error message as well.

1) Create a context variable in the child job first say 'tableName' 

Child's Job

child's Job context

I bet it's this one, but I'm not sure how I should set the value.

Is this the right way?? (the actual one, I left the value blank)

or should I set this one too?

context

2) then pass the value for this in 'tRunJob' of the parent job under 'context param'. 

3) When you try to add entries, the context variables in the child automatically comes up in the drop down and assign value for it using the global variable.

Parent's Job

Parent Job's Context Params

Here I select 'tableName' from the dropdown list and use global variable as the value.

4.  Update the tDbInput component in the child job to use the context variable rather than the global variable.

<child's Job>

tDBInput's Table Name: 

((String)globalMap.get("tDBTableList_1_CURRENT_TABLE"))  change to

context.tableName

basically, change global variable to context variable.

I tried running the job with current setting and here's the error message.

Exception in thread "main" java.lang.Error: Unresolved compilation problems: 
	org.talend.job cannot be resolved to a type
	org.talend.designer cannot be resolved to a type
	org.talend.designer.components.util.mssql.MSSqlUtilFactory cannot be resolved to a type
	org.talend.logging cannot be resolved to a type
	org.talend.job.audit.JobEventAuditLoggerFactory cannot be resolved to a type
	at local_test.test04_1_0_1.test04_1.<init>(test04_1.java:162)
	at local_test.test04_0_1.test04.tDBTableList_1Process(test04.java:1032)
	at local_test.test04_0_1.test04.runJobInTOS(test04.java:1751)
	at local_test.test04_0_1.test04.main(test04.java:1538)

[nivedhitha]

@crotmn ,

You are passing the value right. The value for the context variable in the child job should be empty as it get its value dynamically from the parent job.

Also can you make a connection between tDbConnection_2 and tDbInput_1 in the child job? As there is no preJob component , the connection and the input components will be executed at the same time.

Where are you getting the target table name from? If that is also from the table in parent job, then it has to be passed to the child job too 

[Anonymous]

@nivedhitha 

Thanks for your prompt response.

"Also can you make a connection between tDbConnection_2 and tDbInput_1 in the child job? As there is no preJob component , the connection and the input components will be executed at the same time."

> For the connection you mentioned here, is 'OnSubJobOk' good for this?

or should I add a preJob component? I had it in the parent job already, so I'm not sure if I should use it again here.

Child Job

Child Job

"Where are you getting the target table name from? If that is also from the table in parent job, then it has to be passed to the child job too "

> You mean in the child job right? If yes, I use a context variable you mentioned in the earlier thread as a table name. If there's any changes needed please let me know.

By the way, I set up a connection component (input&output) in both parent and child job, is it needed for both job? or having in it either job would work fine too?

[nivedhitha]

@crotmn ,

For your first question, Yeah 'On Subjob Ok' should be fine.

> You mean in the child job right? If yes, I use a context variable you mentioned in the earlier thread as a table name. If there's any changes needed please let me know.

- Yeah i see you using in the tDbInput component. So it is the source table. Is the table name in the tDbOutput component dynamic too? If so you will need another context variable similar to the source table and pass value for it as well.

> By the way, I set up a connection component (input&output) in both parent and child job, is it needed for both job? or having in it either job would work fine too?

- It doesnt matter if you create them in both the parent and the child. But there is an option to reuse the connection created in parent job in the child job. 

If the current design works, leave it like that.

If you still have problems getting it to work, please export your job and attach it here or DM it to me 

[Anonymous]

@nivedhitha 

Thanks for getting to back to me.

Yeah i see you using in the tDbInput component. So it is the source table. Is the table name in the tDbOutput component dynamic too? If so you will need another context variable similar to the source table and pass value for it as well.

> Yes, the table name in tDBOutput is 'context.tableName'. Can I use the same one with tDBInput??

With the current job design, the same error still occurred.

Exception in thread "main" java.lang.Error: Unresolved compilation problems: 
	org.talend.job cannot be resolved to a type
	org.talend.designer cannot be resolved to a type
	org.talend.designer.components.util.mssql.MSSqlUtilFactory cannot be resolved to a type
	org.talend.logging cannot be resolved to a type
	org.talend.job.audit.JobContextBuilder cannot be resolved to a type
	JobAuditLogger cannot be resolved to a type
	org.talend.job.audit.JobEventAuditLoggerFactory cannot be resolved to a type
	at local_test.test04_1_0_1.child_job.<init>(child_job.java:162)
	at local_test.test04_0_1.parent_job.tDBTableList_1Process(parent_job.java:1032)
	at local_test.test04_0_1.parent_job.runJobInTOS(parent_job.java:1751)
	at local_test.test04_0_1.parent_job.main(parent_job.java:1538)

PS. DbConnection2 is actually for output, and DbConnection1 is for input. Isn't it better to connect DbConnection1  with DBInput1? and vice versa? 

I feel like there's something needed to be fixed in the child job.

Thanks!

[nivedhitha]

@crotmn , if the input and output tables are the same the its fine to have one context and use it both in DbInput and DbOutput.

Its okay if one is an input connection and the other is output. You can leave the design unchanged as both the connection has to be made in order for the job to execute. That is why the connections are made before the actual processing takes place.

To see what the error could be, can you run just the child job directly? It says compilation error so it will show you the component with error.

Also it would be better if you can attach an exported copy of the job with all your credentials removed