Benefits of using JDBC components over vendor-specific (+ 1 problem)

[Anonymous]

Hi all,
I have a job that uses Oracle DB components, and am experimenting with switching to JDBC DB components, to see if this would make the job more DB independent (easier to re-target to MySQL , for example). I'm not entirely convinced it does, because you still need to specify vendor-specific details like jdbc Jar and jdbc driver class name. However, it looks like defining a JDBCConnection component first and then defining your JDBC Input/Output components to use that keeps DB-specific details out of your Input/Output components.
I'm also experiencing a problem with metadata retrieval; Retrieve metadata on JDBC connection never returns. I define a JDBC connection in Metadata -> Db connections, it connects ok, next step is retrieve metadata, but when I get to the "New Schema in connection " page, TIS seems to cycle forever in "retrieve schema" mode. Clicking "Check Connection" immediately returns ok, so the connection details are fine, and also doing the same thing with an Oracle metadata connection works fine too. (The Metadata_Retrieve_Schema screenshot shows where this occurs -- the 'Creation status' stays at Pending).
Regardless of this problem, does anyone have any stories on significant benefits of using JDBC components over vendor-specific? (reduced time when switching from one DB to another?)
Many thanks,
Colm.

[alevy]

This is something I'm also interested in for the same reasons but have not yet tried out. We did make a brief attempt using ODBC but ran into too many problems and it was far too slow.
Regarding some of your points, however:

you still need to specify vendor-specific details like jdbc Jar and jdbc driver class name

I believe you can use a context variable for the class. I think in v4.0.3 you could also use a context variable for the JAR through the Dynamic Settings while in v4.1.2 you can just include all JARs your job might need.

I'm also experiencing a problem with metadata retrieval

Presumably the schema in each database type will be equivalent so just retrieve the schema using an Oracle DB type and then you can use that schema regardless of the actual DB type to which your job is connecting.
You could also try (in TIS) using separate jobs for the dedicated DB components and joblets for the in between processing but that is not really practical for large numbers of complex jobs (using multiple tables).
Definitely interested in hearing from someone whose actually done this successfully...

[Anonymous]

It's possible to use context variables for JDBC URL, class name username and password. And putting all possible jars in Driver JAR for each component means the input/output components don't need to change for a new DB. I haven't exployed using JDBCConnection components, am tempted to because it means the only configuration that goes into input/output components is schema. I'll update on any progress.
About Metadata retrieval, I'm using Generic Schema to keep all schemas so what I posted on above isn't a serious problem.
Thanks,
Colm