Topic
  • 7 replies
  • Latest Post - ‏2012-12-15T10:05:42Z by Mauri_Claudio
Mauri_Claudio
Mauri_Claudio
20 Posts

Pinned topic Db2 for i - Using ALIAS to bypass library list in SQL

‏2012-12-13T07:05:57Z |
Good morning,

since in SQL stored proc is not possible to use something similar to library list concept (i.e, identifying the correct schema for a certain user), I thought to use CREATE ALIAS statement to create in QTEMP an ALIAS for a table X in a certain schema Y. A create alias statement may be easily issued using dynamic sql in a stored proc.

Using the alias instead of the 'real' table should let you to avoid using dynamic sql every time you need to work with the table.

My question is: are there any contraindications in using aliases ?

Regards,
Mauri Claudio.
Updated on 2012-12-15T10:05:42Z at 2012-12-15T10:05:42Z by Mauri_Claudio
  • B.Hauser
    B.Hauser
    264 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-13T08:21:20Z  
    > Mauri_Claudio wrote:
    > Good morning,
    >
    > since in SQL stored proc is not possible to use something similar to library list concept

    This is not true, whether a library list or a single schema is accessed depends on the naming convention (System or SQL) used when creating the procedure, default schema set when creating the procedure and whether static of dynamic SQL is used.

    For more information please refer the following articles
    System versus SQL Naming - PartII
    System versus SQL Naming - PartI

    Birgitta
  • B.Hauser
    B.Hauser
    264 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-13T08:23:57Z  
    • B.Hauser
    • ‏2012-12-13T08:21:20Z
    > Mauri_Claudio wrote:
    > Good morning,
    >
    > since in SQL stored proc is not possible to use something similar to library list concept

    This is not true, whether a library list or a single schema is accessed depends on the naming convention (System or SQL) used when creating the procedure, default schema set when creating the procedure and whether static of dynamic SQL is used.

    For more information please refer the following articles
    System versus SQL Naming - PartII
    System versus SQL Naming - PartI

    Birgitta
    Sorry the link did not work. Here are the links:

    http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
    http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-13T09:01:23Z  
    • B.Hauser
    • ‏2012-12-13T08:23:57Z
    Sorry the link did not work. Here are the links:

    http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
    http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html
    Thanks for your answer. Unluckyly, I need to use only SQL naming conventions.
  • B.Hauser
    B.Hauser
    264 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-14T06:14:26Z  
    Thanks for your answer. Unluckyly, I need to use only SQL naming conventions.
    If there is only a single schema to be accessed, you may check if executing an SET SCHEMA statement will help to modify or check the schema.

    Birgitta
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-14T08:55:44Z  
    • B.Hauser
    • ‏2012-12-14T06:14:26Z
    If there is only a single schema to be accessed, you may check if executing an SET SCHEMA statement will help to modify or check the schema.

    Birgitta
    I've tried SET SCHEMA, with no luck. Anyway, what I'm asking is if aliases must be used carefully and, if yes, why.

    Thanks for your answers.
  • krmilligan
    krmilligan
    450 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-14T16:15:09Z  
    I've tried SET SCHEMA, with no luck. Anyway, what I'm asking is if aliases must be used carefully and, if yes, why.

    Thanks for your answers.
    There would be some overhead to creating the alias object prior to running the SQL statement.
  • Mauri_Claudio
    Mauri_Claudio
    20 Posts

    Re: Db2 for i - Using ALIAS to bypass library list in SQL

    ‏2012-12-15T10:05:42Z  
    There would be some overhead to creating the alias object prior to running the SQL statement.
    Well, that's may be acceptable, since even using prepared statements one would introduce an overhead.On the other hand, one may gain in terms of readability of code.

    Thank you for your answer.