Is there an equivalent to the iSeries OVRDBF command in SQL?

[Legacy User]

I have a requirement in a SQL Server environment that under specific circumstances, all references to table (or view) A in a procedure actually use table (or view) B.   On the iSeries (it was still called that last time I wrote code for it)  I would have used the OVRDBF command to override references to table A with table B: OVRDBF FILE(A) TOFILE(B).   What would be the equivalent to this in SQL?   Is there one?    My goal is to end up with a procedure that is ignorant of the override.   I don't want conditional logic inside the procedure that directs processing at table B when certain conditions are met.   The vision:  Under typical circumstances:   Just invoke the procedure  Under specific alternative circumstances:   Perform the OVRDBF equivalent and then Invoke the procedure    TIAJohn

[Anon Anon]

I did this using DB Connection names.   This is documented in the Plex user guide in Chapter 11.  You specify what connection name to use in your function but you can abstract it so that the edit points and code are available in every db function and only executed when required the conditions are met.   The only tricky one is blockfetch.   you need to be aware if you are getting a new block or fetching the next block from an already existing result set.

[Legacy User]

Chris,  It seems to me that this approach would require the creation of a separate  database, one at which to direct the additional connection.   Am I understanding that correctly?    Thanks,John

[Anon Anon]

Yes, that is mostly correct. With OVRDBF, the actual "File name" does not matter.   In SQL, the "Table name" DOES.   The name is qualified with a Schema but the actual name of the table must be used in the SQL statement.
There are a few ways to skin this cat.   Runtime DB Connection names can keep your approach in your Plex model.  One approach with a "single database" is to have more than one Schema and the same table in each schema.   Then your Plex DB connections will connect to the same database but different schemas.  The short answer is, No SQL Server does not have OVRDBF.  Welcome to SQL.   Say goodbye to *LIBL and OVRDBF and all of those cozy AS/400 things you got used to.   

[Legacy User]

Ah, yes.   This:  "One approach with a "single database" is to have more than one Schema and the same table in each schema.   Then your Plex DB connections will connect to the same database but different schemas."  would be a very elegant approach.   Possibly combined with synonyms and temporary tables, you could make this extremely flexible (perhaps to a fault ;) ).  Unfortunately, I will not be able to implement it as I am too reliant on existing content that currently has no notion of multiple DB connections.   That was my primary motivation for a more OVRDBF-like approach.   If I understand the multiple DB connection approach correctly, each data access  function involved would have to be altered to be DB connection aware via the abstract content you described in your first response.  I really appreciate your input on this.John

[Srini]

Life is not that bad on the SQL, I guess you gain some and loose some :). Another option, if you are using SQL Server is Synonyms. We did implement some pretty nifty logic, to get around the LIBL problem on the SQL Server by using Synonyms. It works pretty well in our environment/requirements. Not sure if that would solve yoru issue.  Our logic is static, but your looks dynamic. See if you can create Synonym at runtime and refer to the table that you would need and then refer to the Synonym name in Plex in place of the regular table/view.  All the best.  Srini

[Legacy User]

Srini,  Thanks for the response.   I think some combination of Schema/DB Connection/Synonym manipulation might do the trick.   I can imagine using a separate DB Connection to connect as a user with their own schema.   In their schema I could create synonyms for any tables I wish to re-direct.   Note that you can not create synonyms within a schema with the same name as another object in the schema, thus the separate schema.  Also important to note is that stored procedures automatically run in the schema of their owner.   Therefore, if  I wanted to employ this technique for a stored proc, I'd have to make sure its owner used a schema in which I could create the necessary synonyms.  This opens up some interesting possibilities.   I can imagine creating all users with their own schemas so I would have a place to put this override-type stuff when I didn't want to automatically fall through to the dbo schema.   Additionally, making sure stored procs don't automatically run in the dbo schema would open up the same possiblities there.   That might allow me to get rid of the additional DB Connection altogether.   This might (or might not) be a common practice, but I've still not quite gotten used to the concept of schemas as they are now used in SQL Server (as of 2005).   Between the responses from both you and Chris, I think I'll be able to put together something that will suit my needs.    Thanks a bunch,John