[Openroad-users] Conversion to OpenSQL

[Chris Clark]

On 1/21/2008 7:31 PM, Colin Hay wrote:
> I've identified the following statements that will not work:
>
> · COPY <tablename>
>
> · CREATE TABLE … AS SELECT
>
> · CREATE TABLE (<column> … WITH DEFAULT)
>
> · DECLARE GLOBAL TEMPORARY TABLE … AS SELECT
>
> · DROP [TABLE|VIEW] :<tablename>
>
> · RAISE DBEVENT
>
> · REGISTER DBEVENT
>
> · SET LOCKMODE
>
> · SAVEPOINT
>
> · UPDATE <table> FROM
>

As per other comments, some of these are valid OpenSQL (and some are 
not). Some of these are not OpenSQL but are supported by EA (to specific 
back ends, e.g. dbevents are supported with EA to Oracle ONLY not DB2 
which you need). As Pete mentioned, COPY is supported (but only in 
BINARY mode).

Assuming EA 2.6 (to DB2) these are supported:

    * CREATE TABLE ... AS SELECT
    * DECLARE GLOBAL TEMPORARY TABLE ... AS SELECT


May require work:

    * COPY <tablename> -- BINARY only
    * CREATE TABLE (<column> … WITH DEFAULT)
    * SET LOCKMODE -- you can use it but it will be No-Op
    * DROP [TABLE|VIEW] :<tablename> -- I have to confess I've never
      tried using variables for this, worth trying otherwise construct
      the SQL and issue via EXECUTE IMMEDIATE


Not supported (for EA to DB2):

    * RAISE DBEVENT -- Oracle gateway only
    * REGISTER DBEVENT -- Oracle gateway only
    * UPDATE <table> FROM -- Not OpenSQL, you _may_ be able to do some
      host specific via *DIRECT* EXECUTE IMMEDIATE
    * SAVEPOINT


Chris