1

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these different databases.

It is difficult to maintain these multiple set of DDLs/DMLs and moreover in future our application might support few more database type.

What will be the best approach if we want to maintain a single set of DDLs/DMLs?

  • Check out Liquibase – a_horse_with_no_name Jan 18 '15 at 14:55
  • 1
    I'm biased here, but this is the price to pay if you want to support different types. I would take a very long think, choose one, and don't look back. None of them is perfect, so don't try to cover that fact by not choosing any, and causing a lot of problems coping with differences. But, maybe, and very likely, that is not your choice. – tvCa Jan 18 '15 at 15:38
  • @a_horse_with_no_name Thanks. I will check it out. – Somnath Musib Jan 25 '15 at 12:44
4

The best way to support multiple database types is to optimize the sql statements for each on of them. Otherwise you will one day end up with huge performance problems on one or the other database types.

Additionally to that it's unfair for your customers to tell them "I support Oracle, DB2, MSSQL, etc.) and not optimizing the SQL for each one of them. Why? Because your customer pays a lot for the database and your application does not use the advantages and features the database provides.

  • I agree. I can scratch my head for all day long but I can't find 2 dbms that can add 3 days to a date with the same query. – ypercubeᵀᴹ Jan 19 '15 at 16:32
  • @user56901 Thanks for the response and I am agree with both of your points. – Somnath Musib Jan 25 '15 at 12:49
  • @ypercube: I can actually find 5 DBs ;) The following works on Postgres, Oracle, DB2, Vertica and HSQLDB: current_date + interval '3' day But in general I totally agree that writing DBMS independent SQL simply means it will be equally slow on all databases (if it can be achieved at all) – a_horse_with_no_name Jan 25 '15 at 18:10
1

I'm going to list a different option here, having come from a development background. At one point, we had developed a client/server application, where we wanted the DDL (for the most part) to be the same between the client and the server. The challenge was the client and the server used different database engines.

So what we did was use XML. We defined all our tables (and even upgrades within XML). And then used differing XSL stylesheets to create the specific creation (or upgrade) scripts (with the correct DB engine DDL dialect) to run against the client or the server. So they went from XML to SQL. And we didn't have to maintain differing SQL definitions. Just one XML file.

Now, I know this option might turn a few heads, but it worked. And I am not saying you should pursue this option. Just that it is an option.

  • I'm sure that it worked for you but not everything that works is also performing well. If high performance is not needed you can do whatever you want. – user56901 Jan 19 '15 at 14:18
  • Agreed. Don't disagree with you there. – Chris Aldrich Jan 19 '15 at 15:03
  • @user56901 Chris' answer mentions only DDL, not DML, so performance is not a major issue (at least when creating tables, for altering them it is.) As for DML, having the same queries for different DBMS is not going to gain you any efficiency, on the contrary. – ypercubeᵀᴹ Jan 19 '15 at 16:31
  • 1
    Both DDL and DML need optimization for their DBMS. Here are some oracle examples: partitioning, index partitioning, the storage clause. There are no things like that in other dbms. With Oracle DML you can for example use a with clause in an complex update statement. There was quite a long time when Oracle did not have a pivot function and others had. Does this mean you should not use it for other databases just because one does not support it? So I definitely think that a software which does not have optimized SQL for each supported DBMS is a bad software! – user56901 Jan 19 '15 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.