Topic
  • 2 replies
  • Latest Post - ‏2014-07-25T11:09:55Z by SamueleSadocco
SamueleSadocco
SamueleSadocco
9 Posts

Pinned topic Alias for schema name

‏2014-07-02T15:57:25Z |

Hi,

we have about 20 LPAR where we have installed the same version of our custom software and in some of them we have some shared program library. The data library have different name, even if they have the same data model.

Using PF/LF is easy for us to install object, using a data area in QTEMP containing the data library names.

For new objects we are using SQL interface for create tables, views, and Stored Procedures, but we have some trouble when:

* a view is refered to tables that are in different libraries/schemas. In this case, when we save an environment and restore to an other, the view is still refered to an incorrect schemas (even if we create the view without referencing the schema name!)

* a SQL stored procedure or function have some SELECT/INSERT etc statement with unqualified object reference: in this case the SQL compiler, at compile time, changes the statemente and use a qualified object reference.

This is very different from "usual" (and more intuitive) manner of static SQL in RPG/COBOL, where the unqualified objects reference are resolved at runtime.

If It could be possible (in future) to create (at login/connection time) a global environment variable and USE this variable in SQL statement (create table, select, etc.) for reference a schema or to define a Schema alias, I think that many of these trouble could be resolved.

Regards

Samuele 

  • B.Hauser
    B.Hauser
    283 Posts

    Re: Alias for schema name

    ‏2014-07-03T05:29:18Z  

    What naming conventions are you using when creating your SQL/Database objects?

    System or SQL Naming conventions? I assume you are using SQL Naming conventions. Moving to System Naming conventions may resolve the problems with unqualified access in SQL Routines (Stored Procedure or User Defined Functions).

    But for views the schema of the tables or views that are accessed is resolved at creation. Even if you do not specify a schema.

    For more information about the different behaviours between System and SQL Naming conventions you may read the following articles:

    System versus SQL Naming Conventions - Part 1

    System versus SQL Naming Conventions - Part 2

    Birgitta

  • SamueleSadocco
    SamueleSadocco
    9 Posts

    Re: Alias for schema name

    ‏2014-07-25T11:09:55Z  
    • B.Hauser
    • ‏2014-07-03T05:29:18Z

    What naming conventions are you using when creating your SQL/Database objects?

    System or SQL Naming conventions? I assume you are using SQL Naming conventions. Moving to System Naming conventions may resolve the problems with unqualified access in SQL Routines (Stored Procedure or User Defined Functions).

    But for views the schema of the tables or views that are accessed is resolved at creation. Even if you do not specify a schema.

    For more information about the different behaviours between System and SQL Naming conventions you may read the following articles:

    System versus SQL Naming Conventions - Part 1

    System versus SQL Naming Conventions - Part 2

    Birgitta

    Thank you for your reply.