talexb has asked for the wisdom of the Perl Monks concerning the following question:

I'm going through some old Perl code that accesses a DB2 database, and in many of the queries and commands, the schema is specified using a variable that's interpolated into the query or command string.

This is something that goes against the grain. I know that if I'm dealing with a single schema, I can just set schema foo and move on. However, in some cases there are two schemas in a single query or command. And, of course, a schema's not something that can be specified with a placeholder, as far as I know.

So am I stuck using variables to specify the multiple schemas in a query or command? Comments? Thoughts? Novel ideas?

Thanks all. :)

Alex / talexb / Toronto

"Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Replies are listed 'Best First'.
Re: DB2 / Multiple schemas in a query or command
by Tanktalus (Canon) on Dec 15, 2008 at 23:03 UTC

    Specifying a schema with a placeholder is kind of like specifying a table with a placeholder. It'll throw all the DB's precise access planning out the window if it changes. As long as the prepare is being re-done anyway, it may as well have you do it.

    In other words, the schema really is part of the table name. So treat it as such. Just think of it that there is no schema, but every table name must have a dot in it. There is a default prefix available, but it's still there, even if you don't say it.

    So, one solution is: 

    my $tbl_foo = 'FOO'; # rely on current schema, or
my $tbl_foo = 'BAZ.FOO'; # explicit, or

my $sch = 'BAZ'; my $tbl_foo = "$sch.FOO"; # make it easier to change 
+a bunch of schemas at the same time

    [download]
    This latter solution is analogous to what I do with DB2::db where I have a base class that returns its schema, and the table classes simply derive off the appropriate base class, making it pretty easy to both change the name of a schema and to change which schema a particular table is in. Not that I need it very often.

[reply]
[d/l]
Re: DB2 / Multiple schemas in a query or command
by kennethk (Abbot) on Dec 15, 2008 at 22:40 UTC
    For Re: Perl DBI + Access bind issues, I verified that you can use placeholders for column names for DBD::ODBC on Access and DBD::Oracle. It undermines some of the speed and integrity checks in prepare (according to mr_mischief), but it might be worth a shot checking since it would likely be a much faster fix than some of the other options.
[reply]
Re: DB2 / Multiple schemas in a query or command
by runrig (Abbot) on Dec 16, 2008 at 00:26 UTC
    If the variable is coming from a trusted source, and not from some random user on the web, then go ahead and interpolate it in the sql.
[reply]

      Thanks, putting the schema(s) into the query is what's happening right now -- I just wanted to find out if there was a better way (more DB2-ish). It appears not.

      I'll just put that idea aside for now. My plan is to set the schema when the database connection is made, so that, for most part, I don't have to have the schema in the query or command.

      Is that making things too tidy?

      Alex / talexb / Toronto

      "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

[reply]
        talexb:

        I don't know if DB2 allows insertable views or not. But if so, you could make some views from the most-used schema to the objects in the lesser-used ones, bypassing the issue...

        use food

create view food.orchard
  as select * from trees.orangeTrees

select A.apples, B.oranges
from fruitSalad A
join orchard B on A.compareKey = B.compareKey

        [download]
        ...roboticus
[reply]
[d/l]

Back to Seekers of Perl Wisdom