topic Re: Skipping tables which have been failed to migrate in Talend Studio

Skipping tables which have been failed to migrate

StreetTalender — Fri, 15 Nov 2024 22:09:46 GMT

Hi,

I have created a migration job that migrates a whole schema including all its tables.

However, there are some errors in the job, so I want to skip those tables which have errors, and output which tables have failed to migrate, so that I can create jobs for those tables individually.

Can anyone help me figuring out how to do it?

In my job, I use tDBTableList, in which it stores table names in a global variable to pass to tOracleInput.

Errors I have:

in tDBinput, ORA-00942: table or view does not exist
in tDBoutput, ORA-01723: ORA-01723: zero-length columns are not allowed

Job design:

----------------------------------------

tOracleConnection1-oncomponentok-tOracleConnection2

|onsubjobok

tOracleTableList--iterate--tOracleInput--main--tOracleOutput--oncomponentok--tOracleCommit

|onsubjobok

tOracleClose1--oncomponentok--tOracleClose2

----------------------------------------

Thanks in advance.

Re: Skipping tables which have been failed to migrate

Anonymous — Fri, 27 Jan 2023 16:05:09 GMT

Hi,

Thank you for reaching out!

regarding this error message: ORA-00942: table or view does not exist

This could happen for one of these reasons:

- Referencing a table or view that does not exist

- Using an unauthorized synonym

- Using an expression of view where a table is required

- Attempting to use a table without proper permission or privilege

Please verify these points using this article:

https://www.tekstream.com/resource-center/ora-00942-table-or-view-does-not-exist/#:~:text=You%20may%20be%20seeing%20the,table%20by%20the%20schema%20name.

As for ORA-01723: ORA-01723: zero-length columns are not allowed

Please try the below workaround to increase the dynamic schema length to more than 100 (default max is 100) and let me know your observations:

- In the Talend Studio, click the Window button on top menu bar.

- select Show View and then scroll down to Navigator and then double click it

- go to Navigator --> Java --> src --> main --> resources --> xmlMappings then edit the mapping .xml file

- look for type="VARCHAR" & defaultLengh="200" then change it to type="VARCHAR" ignoreLen="true"

- then save it then run the job, it will insert data has length more than 100 to the database.

Best,

Re: Skipping tables which have been failed to migrate

StreetTalender — Sat, 28 Jan 2023 07:50:45 GMT

Hi @Yussef Saleh ,

Thanks for your reply!

Regarding ORA-00942: table or view does not exist,

the execution log from TMC does not show any info to figure out which table is the problem. as I am using dynamic schema which handles thousands of tables, I cannot go through all tables to find out which table is the problem.

Could you let me know a way to find that out?

As for ORA-01723: ORA-01723: zero-length columns are not allowed,

Im my Talend studio, I could not find “Java“ folder under Navigator. (Also Navigator was marked as “depricated“, if that matters)

Could Java folder be anywhere else?

Best,

Re: Skipping tables which have been failed to migrate

Anonymous — Sat, 28 Jan 2023 08:56:47 GMT

Go to the the Day of the tip page and read the tip of Sep 27, 2022, this tip shows you how to catch the exception, output the current table name if there is an error occurs, and continue to execute the next table.

Regards

Shong

Re: Skipping tables which have been failed to migrate

Anonymous — Sun, 29 Jan 2023 14:56:43 GMT

I'd consider creating 2 levels.

MASTER job would list the tables you want to migrate.

WORKER would do the actual lift & shift.

Inbetween the 2 you can use Iterate link + have the ability to configure the number of threads to be used. (To make things faster.)

Then it's only letting the child job die/crash, and OnSubjobError on the parent to trigger a tFixedFlowInput (tablename) -> tFileOutputDelimited (failures).

Or raising a FATAL log message that you can redirect to a dedicated log4j appender for logging errors.

I think both of your errors are related to quotes.

SELECT * FROM FOO; SELECT * FROM foo; SELECT * FROM FoO; --> equivalent

But if you have a table defined as: FoO yuo can only query it by:

SELECT * FROM "FoO"; (so quoting is necesary)

Same is true for the columns:

If I have 2 columns: "HI," and "HOLA"

SELECT HI, , HOLA FROM TABLE -> 0 length column.

SELECT "HI," , "HOLA" FROM "TABLE" is what you want.

Maybe enable debug logs to see the queries executed by Talend against your database.

There should be no need to create custom jobs. I migrated a million different tables using Dynamic schema between different database types.

Re: Skipping tables which have been failed to migrate

Anonymous — Sun, 29 Jan 2023 14:57:21 GMT

Worth adding a tWarn / tJobLog to dump the tablename you're working with/on.

Re: Skipping tables which have been failed to migrate

StreetTalender — Wed, 01 Feb 2023 02:57:55 GMT

Hi shong and bgunics,

Thanks for your suggestions!

I will try those and let you know how they go.

Best,