Topic
7 replies Latest Post - ‏2012-12-15T10:05:42Z by Mauri_Claudio
Mauri_Claudio
Mauri_Claudio
16 Posts
ACCEPTED ANSWER

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
    241 Posts
    ACCEPTED ANSWER

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

    ‏2012-12-13T08:21:20Z  in response to Mauri_Claudio
    > 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
      241 Posts
      ACCEPTED ANSWER

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

      ‏2012-12-13T08:23:57Z  in response to B.Hauser
      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
        16 Posts
        ACCEPTED ANSWER

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

        ‏2012-12-13T09:01:23Z  in response to B.Hauser
        Thanks for your answer. Unluckyly, I need to use only SQL naming conventions.
        • B.Hauser
          B.Hauser
          241 Posts
          ACCEPTED ANSWER

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

          ‏2012-12-14T06:14:26Z  in response to Mauri_Claudio
          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
            16 Posts
            ACCEPTED ANSWER

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

            ‏2012-12-14T08:55:44Z  in response to B.Hauser
            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
              433 Posts
              ACCEPTED ANSWER

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

              ‏2012-12-14T16:15:09Z  in response to Mauri_Claudio
              There would be some overhead to creating the alias object prior to running the SQL statement.
              • Mauri_Claudio
                Mauri_Claudio
                16 Posts
                ACCEPTED ANSWER

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

                ‏2012-12-15T10:05:42Z  in response to krmilligan
                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.