Is it possible to create a view that knows the current schema / library name?

Question

Reference: DB2 for iSeries version. Each environment has a table containing positional column information about other tables. Since the data in this table is static and must be generated each time the table is modified, problems can occur if it is outside the step.

All positional data exists in QSYS2.SYSTABLESand QSYS2.SYSCOLUMNS, therefore, it should be possible to create a view that contains the same information, but is guaranteed to be correct. Then older programs should be able to use this view.

The only problem is that the view needs to know about the current schema (data library in iSeries) in order to get the necessary information from QSYS2 tables, since they contain data for all the schema / library.

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

EDIT: Re: Ryan Gill

In fact, I want the view to select rows in QSYS2.SYSCOLUMNS, using the current library name in its criteria. If I have a table T in several libraries, then I SYSCOLUMNSwill have data for instances of T in each library.

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

Answer 1

CURRENT_SCHEMA?

ex: Select CURRENT_SCHEMA From QSQPTABL

(: QSQPTABL SysIBM/SysDummy1 )

Select * From SysViews
Where System_View_Schema = CURRENT_SCHEMA

', , * SQL, * SYS, :)

Answer 2

, , . , syscolumns .

, DDS SQL () syscolumns . , , , .

DDS :

SYSCOLUMNS , DDS LF Select/Omit QADBIFLD (QADBXSFLD , , - ):

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

DBILIB DBIFIL, 10 . SYSCOLUMN, .

, , SQL:

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

:

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;

DDS, .

Answer 3

, , ? , ?

, sql , . , , .

?

, , , sql, , . , , schema = 'myLib' , , . , - ..

Answer 4

If you create your libraries in the form of diagrams (i.e. use CREATE SCHEMA, not CRTLIB), then all the necessary information will already be in the diagram. That is, you do not need to query QSYS2.SYSTABLESand QSYS2.SYSCOLUMNS, because you can request myschema.SYSTABLESboth myschema.SYSCOLUMNS, or simply unqualified SYSTABLES and SYSCOLUMNS, if your CURRENT_SCHEMA is set to 'myschema'.