<sql> running in default schema, not specified schema (at least in DB2)

Environment: liquibase-1.9.4called via ant task, linux, DB2 Express-C 9.5

We’re trying to call a stored procedure with the tag.

This works, except when we need to call the SP in a schema other than the default schema for the DB user.

Changing: “CALL MY_PROC;”
to: “CALL MYSCHEMA.MY_PROC;”
works, except that the schema name varies.

We’ve tried – but this does not appear to modify sql defined a tag – probably b/c it is not “generated” sql.
We’ve also tried “Changelog parameter” – but these, too, do not appear to get substituted in a tag.

The only solution I can think of is to call out to sql files (and use the tag). We could use variable substitution with ant to get the schema names correct.

I was hoping for a more elegant solution.

Any ideas?

Regards,

Jonathan

CORRECTION:

I misdiagnosed the problem.  The tag is running its contents in the configured schema (not the default one). It’s just that DB2 will assume the procedure is defined in the default schema, regardless of which schema the sql is executed.

The crux of the problem is the same, however. It would be nice to have some sort of variable substitution in the contents.

Unfortunately I don’t think there isn’t a better built-in option.  Adding variable substitution to text blocks should be a feature in the upcoming 2.0, so it will be better soon.

A slightly more native option than ant substitution may be custom XML entities: http://www.xml.com/pub/a/98/08/xmlqna2.html#ENTDECL. 

Nathan

Thanks. I’ll look for it in 2.0.

For posterity, the work-around we used is to create, call and drop the procedure in a hard-coded schema. E.g.:
CREATE PROCEDURE MYSCHEMA.MY_PROC

CALL MYSCHEMA.MY_PROC;
DROP PROCEDURE MYSCHEMA.MY_PROC;

This works fine for us because:

  1. It is unnecessary in DB2 to create a schema before using it, so using any hard-coded schema will work in any environment.
  2. This SP is only used temporarily for a data migration (and subsequently is dropped), so it really doesn’t matter in which schema the SP lives.