Determining scope

DDL commands that affect temporary objects, which lack a permanent object ID, are local. This includes these object types:

• Local temporary tables

• Local procedures

• Temporary options

DDL commands that create, alter, or drop a permanent object ID in the ISYSOBJECT table are global. This includes these object types:

• Table – includes SA base tables, IQ base tables, and global temporary tables

• View

• Materialized view (SA tables only)

• Column

• Index

• Procedure

• Event

• User

• Publication

• Remote type

• Login mapping

• JAR

• Java class

• Service

• Database options (minus locally scoped option exceptions)

You cannot run global DDL commands if the coordinator node is not running. Attempting to do so results in the error SQLCODE: -1004011, SQLSTATE QIA11: Coordinator node not responding.

When creating objects that are global, make sure that they do not depend on objects that are local. For example, if you created this temporary function or stored procedure:

CREATE TEMPORARY FUNCTION f1() RETURNS INT
BEGIN
RETURN 1;
END

Next, if you tried to create a view that is dependent on the temporary function:

CREATE VIEW v1 AS SELECT * FROM f1()

You would receive the error Procedure 'f1' not found since it is not a permanent procedure. Sybase IQ does not allow such operations in a multiplex environment.

Consider a second example where you create a global object with a dependency on a local object. Assume you create the lineitem temporary table on a secondary node:

DECLARE LOCAL TEMPORARY TABLE #lineitem (
  l_orderkey       integer,
  l_partkey        integer     iq unique(20000000),
  l_suppkey        integer     iq unique(20000000),
  l_linenumber     integer,
  l_quantity       integer     iq unique(50),
  l_extendedprice  double,
  l_discount       double      iq unique(11),
  l_tax            double      iq unique(9),
  l_returnflag     char(1)     iq unique(3),
  l_linestatus     char(1)     iq unique(2),
  l_shipdate       date        iq unique(270),
  l_commitdate     date        iq unique(256),
  l_receiptdate    date        iq unique(300),
  l_shipinstruct   char(25),
  l_shipmode       char(10)    iq unique(7),
  l_comment        char(44)
)

Next, you create indexes—which are global objects—on the columns of the lineitem temporary table using the BEGIN PARALLEL IQ command:

BEGIN PARALLEL IQ
CREATE LF INDEX LFIQ_IDX_TXXX_CXX_L_PK on #lineitem (l_partkey);
CREATE LF INDEX LFIQ_IDX_TXXX_CXX_L_OK on #lineitem (l_orderkey);
END PARALLEL IQ

Sybase IQ returns the error Table 'lineitem' not found because the BEGIN PARALLEL IQ command is a global command sent to the coordinator node, but the lineitem table is a local temporary table on the secondary node.