Results 1 to 5 of 5
  1. #1
    Join Date
    May 2008
    Posts
    39

    Unanswered: Parameterizing schema name

    Hi
    I am trying to parameterize shema name:

    CREATE PROCEDURE Test1"
    (IN "SCH_NM" VARCHAR(20)
    )

    BEGIN
    insert into SCH_NM."test" (x)
    values(234555,)
    END;

    But I get an error:
    [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N "SCH_NM.test" is an undefined name. LINE NUMBER=... SQLSTATE=42704

    Please help
    Last edited by usDB2; 07-17-08 at 09:18.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You will have to do it as dynamic SQL not static.

    Andy

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    the problem in this case is not only you'll have to create the schema but you'll have to create the table inside that schema as well. Absence of any one of those will always result in an error.

    IBM Information Center states
    Code:
    CREATE SCHEMA statement
    Invocation
    This statement can be embedded in an application program or issued through the use of dynamic SQL statements.
    So dynamic SQL is the way out for you.

    for further reference
    http://publib.boulder.ibm.com/infoce...n/r0000925.htm

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Schema creation doesn't have anything to do with the problem.

    Schemas are created implicitly when you create a table and have the necessary privileges (e.g. IMPLICIT_SCHEMA if I'm not mistaken).
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    point noted and thanks for updating me on the same.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •