Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: Alias to Sequence from Another Schema


    hi db2 users...

    is it possible to create an alias or synonym to a sequence in another schema? if so, please post the sql syntax.

    i create the table, index, and sequence. i can select the nextval from the sequence. but when i try to create a synonym to the sequence from another schema, i get an sql error that the object does not exist even though the synonym is created.

    thanks,
    bill

    DB2 v8.1.0.64
    FixPak 7
    Type ESE

    Steps:

    set current schema SCHEMA_A

    CREATE TABLE TABA ( COLA DECIMAL(18, 0) NOT NULL ) IN DATA INDEX IN INDEX LONG IN BLOB
    DB20000I The SQL command completed successfully.

    CREATE UNIQUE INDEX COLA_PK ON TABA (COLA) CLUSTER PCTFREE 15 MINPCTUSED 5
    DB20000I The SQL command completed successfully.

    CREATE SEQUENCE TABA_PK_SEQ START WITH 100 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE NO CACHE NO ORDER
    DB20000I The SQL command completed successfully.

    INSERT INTO TABA (COLA) VALUES (NEXTVAL FOR TABA_PK_SEQ)
    DB20000I The SQL command completed successfully.

    db2 "select nextval for SCHEMA_A.taba_pk_seq from SCHEMA_A.taba"

    1
    -----------
    101

    1 record(s) selected.

    set current schema SCHEMA_B


    create synonym SCHEMA_B.TABA for SCHEMA_A.TABA
    DB20000I The SQL command completed successfully.

    create synonym SCHEMA_B.TABA_PK_SEQ for SCHEMA_A.TABA_PK_SEQ
    SQL0403W The newly defined alias " SCHEMA_B.TABA_PK_SEQ" resolved to the object
    " SCHEMA_A.TABA_PK_SEQ" which is currently undefined. SQLSTATE=01522

    db2 "select tabschema,tabname,definer from syscat.tables where tabname='TABA_PK_SEQ'"

    SCHEMA_B TABA_PK_SEQ DB2INST1

    db2 "select nextval for SCHEMA_B.taba_pk_seq from SCHEMA_B.taba"
    SQL0204N "B.TABA_PK_SEQ" is an undefined name. SQLSTATE=42704

    db2 "select nextval for SCHEMA_B.taba_pk_seq from SCHEMA_A.taba"
    SQL0204N "ECORE.TABA_PK_SEQ" is an undefined name. SQLSTATE=42704


    db2 "select nextval for SCHEMA_A.taba_pk_seq from SCHEMA_B.taba"

    1
    -----------
    102

    1 record(s) selected.

    db2 "select nextval for SCHEMA_A.taba_pk_seq from SCHEMA_A.taba"

    1
    -----------
    103

    1 record(s) selected.


    db2 "drop synonym SCHEMA_B.taba_pk_seq"
    DB20000I The SQL command completed successfully.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Aliases are on tables only. (Tables are views, base tables, ...)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2007
    Posts
    16
    thanks stolze for confirming what i suspected to be true...

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The manual actually states this as well:
    The CREATE ALIAS statement defines an alias for a table, view, nickname, or another alias.
    http://publib.boulder.ibm.com/infoce...c/r0000910.htm
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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