Alias to Sequence from Another Schema

caleysoldman · #1 (**permalink**) 07-24-07, 09:08

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.