CreateView and schema

General Discussion
1

Hey guys (and possible girls :slight_smile:

I have found an interesting “issue” when using LiquiBase to create database views.
My app is running on WebSphere and using JNDI data source that has no scheme defined (as it is used by more then one application and each application specifies schema in its own configuration).

In Hibernate I can specify schema using :

  1. ${jdbc.schema}
In LiquiBase I can do the same using Spring's integration :
  2.        
  3.        
  4.        
  5.    
Now imagine that I have a view to be created :
  2.         replaceIfExists is not allowed on db2
  3.        
  4.             SELECT ROWNUMBER() OVER() as ID, MAD_SERVER_ID as MAD_SERVER_ID, SUBSTR( xmlserialize
  5.             (xmlagg

    (xmltext (concat (’,’, MAD_SERVER_PROPERTY.NAME))) AS VARCHAR(255)), 2)

  6.             as PROPERTIES FROM mad_server INNER JOIN MAD_SERVER_TO_PROPERTY ON MAD_SERVER_TO_PROPERTY.MAD_SERVER_ID

  7.             =

    mad_server.ID INNER JOIN MAD_SERVER_PROPERTY ON MAD_SERVER_TO_PROPERTY.PROPERTY_ID

  8.             = MAD_SERVER_PROPERTY.ID GROUP BY MAD_SERVER_ID
  9.        
  10.    
When this changeSet gets executed it will look for table ‘mad_server’ (please don’t laugh) in default schema (which is same as DB user) rather than in schema I provided via Spring.And since the table is in schema I provided this SQL fails - obviously.
I could put SCHEMA.mad_server into the SQL but that would loose the portability.

Any idea how this could be done? Or is it database (or DB2) related “issue”/feature.

All this happens on DB2 9.7.

Thanks !

PS : If I use local data source with schema specified it work fine !

2

If you are wanting to inject variables into the body of the view, the best approach is probably to use changelog parameters. http://www.liquibase.org/documentation/changelog_parameters.html 

That will allow you to “hard code” a schema-identified table in the changelog file but change what that value is at runtime.

Nathan