Is it possible to create a view that is aware of current schema/library name?

Question

Background: iSeries version of DB2. In every environment, there is a table containing positional column information about other tables. As the data in this table is static and has to be re-generated every time a table is altered, problems can occur if it is out of step.

All the positional data exists in QSYS2.SYSTABLES and QSYS2.SYSCOLUMNS, so it should be possible to create a view that contains the same information but is guaranteed to be correct. Old programs should then be able to use this view.

The only issue is that the view would need to be aware of the current schema (data library in iSeries speak), to retrieve the right information from the QSYS2 tables, as they contain data for all schemas/libraries.

Any ideas on whether this is possible and if so how?

EDIT: Re: Ryan Guill

Effectively I want to the view to select rows in QSYS2.SYSCOLUMNS using the current library name in its criteria. If I have table T in multiple libraries, then SYSCOLUMNS will have data for instances of the T in each library.

An inelegant solution is that I could store the library/schema name in each library, and the view would use this value in the selection...

Answer 1

You are saying that you want to create a view and stick it in a library that shows only it's library's contents? I'm guessing you are accessing this stuff using a library list and not specifying a specific library?

I don't think it is possible using sql for the view to know it's current library when using a library list. If you aren't using a library list then you should just be able to run your queries using the library that you know.

Or am I misunderstanding?

Update

If you want to have a view that dynamically knows what schema it is in and uses that as part of the sql statement, I don't think it is possible. If you hardcode the query for the view to say where schema = 'myLib' to the current library then it could work, but you would have to make a different version to put in every library. And it would not be obvious if it was ever wrong or if it was switched, etc.

Answer 2

I think you're idea of storing the schema & table names in a table will work. Your view gets created in that schema by using an inner join of that schema & table to syscolumns to create the view.

Optionally you might want to consider using DDS instead of SQL to create a logical file (view) over syscolumns for each library. Not sure how many libraries you are dealing with but if they are just a few this might be workable.

To use DDS with Selection:

SYSCOLUMNS is already a view so to create a DDS LF for Select/Omit you have to create it over the underlying QADBIFLD physical file (QADBXSFLD is also involved but I don't think you need any fields from that file for this application):

 A          R QDBIFLD                   PFILE(QADBIFLD)                      
 A          S DBILB2                    COMP(EQ 'SCHEMANAME')                
 A          S DBILFI                    COMP(EQ 'TABLENAME')

You could use DBILIB & DBIFIL if you're schema and table names are 10 or under. If you need the SYSCOLUMN names you have to do some field renaming.

Ryan, I'm wrong as you could also do this with a SQL view:

    CREATE VIEW MYSCHEMA/MYSYSCOLUMN AS
     SELECT *
       FROM SYSCOLUMNS
      WHERE SYSCOLUMNS.DBNAME = 'SCHEMANAME'
        AND SYSCOLUMNS.TBNAME = 'TABLENAME'

To use a join:

CREATE TABLE MYSCHEMA/MYTABLESELECT
           ( MYSCHEMA VARCHAR (128),
             MYTABLE VARCHAR (128) );
INSERT INTO MYTABLESELECT VALUES( 'SCHEMANAME', 'TABLENAME' );
CREATE VIEW MYSCHEMA/MYSYSCOLUMN AS
     SELECT SYSCOLUMNS.*
       FROM SYSCOLUMNS, MYTABLESELECT
      WHERE SYSCOLUMNS.DBNAME = MYTABLESELECT.MYSCHEMA
        AND SYSCOLUMNS.TBNAME = MYTABLESELECT.MYTABLE;

No ordering is being done on either the DDS or the View.

Answer 3

Have you tried using the special register CURRENT_SCHEMA?

ex: Select CURRENT_SCHEMA From QSQPTABL

(note: QSQPTABL is the same as SysIBM/SysDummy1; dummy table for playing around)

Select * From SysViews
Where System_View_Schema = CURRENT_SCHEMA

'course that only works if you're using *SQL naming and not *SYS naming, but that's another story :)