sequences and duals in db2

sequences and duals in db2

Post by ta.. » Thu, 16 Nov 2000 04:00:00



I was evaluating db2 for a potential migration from Oracle and wanted
to know  whether there is an equivalent of 'oracle sequence' in db2?
How do I declare and use it?

Also, is there any thing like the Oracle 'dual' in db2? ex: How do I
get the current date and time in a sql query (like i would use 'select
sysdate from dual' in Oracle)?

thanks,
Tanuj Chatterjee

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

sequences and duals in db2

Post by Mitc » Thu, 16 Nov 2000 04:00:00


UDB equivalent = IDENTITY

SELECT CURRENT TIMESTAMP FROM <tablename>

--
Richard Mitch Mitchell
Senior Database Administrator
IBM Certified Solutions Expert - DB2 UDB v6.1 Database Administration
for UNIX, Windows and OS/2

Fracta Networks



> I was evaluating db2 for a potential migration from Oracle and wanted
> to know  whether there is an equivalent of 'oracle sequence' in db2?
> How do I declare and use it?

> Also, is there any thing like the Oracle 'dual' in db2? ex: How do I
> get the current date and time in a sql query (like i would use 'select
> sysdate from dual' in Oracle)?

> thanks,
> Tanuj Chatterjee

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

sequences and duals in db2

Post by Gert van der Kooi » Thu, 16 Nov 2000 04:00:00



> I was evaluating db2 for a potential migration from Oracle and wanted
> to know  whether there is an equivalent of 'oracle sequence' in db2?
> How do I declare and use it?

> Also, is there any thing like the Oracle 'dual' in db2? ex: How do I
> get the current date and time in a sql query (like i would use 'select
> sysdate from dual' in Oracle)?

> thanks,
> Tanuj Chatterjee

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Hi,

With DB2 V7 you can use identity columns opposed to the oracle sequence.

identity-clause (copied from SQL Reference)

|---IDENTITY--+------------------------------------------------------+->
              |   .-,-----------------------------------------.      |
              |   V                 .-1----------------.      |      |
              '-(-----+-START WITH--+-numeric-constant-+---+--+---)--'
                      |               .-1----------------. |
                      +-INCREMENT BY--+-numeric-constant-+-+
                      | .-CACHE--20----------------.       |
                      '-+-NO CACHE-----------------+-------'
                        '-CACHE--integer-constant--'

To get the current date you can use special registers like 'current
date', 'current timestamp' etc.

Both are completely described in the SQL Reference.

Hope this helps.

 
 
 

sequences and duals in db2

Post by ta.. » Thu, 16 Nov 2000 04:00:00





says...
> > I was evaluating db2 for a potential migration from Oracle and
wanted
> > to know  whether there is an equivalent of 'oracle sequence' in db2?
> > How do I declare and use it?

> > Also, is there any thing like the Oracle 'dual' in db2? ex: How do I
> > get the current date and time in a sql query (like i would
use 'select
> > sysdate from dual' in Oracle)?

> > thanks,
> > Tanuj Chatterjee

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Hi,

> With DB2 V7 you can use identity columns opposed to the oracle
sequence.

> identity-clause (copied from SQL Reference)

> |---IDENTITY--+------------------------------------------------------
+->
>               |   .-,-----------------------------------------.      |
>               |   V                 .-1----------------.      |      |
>               '-(-----+-START WITH--+-numeric-constant-+---+--+---)--'
>                       |               .-1----------------. |
>                       +-INCREMENT BY--+-numeric-constant-+-+
>                       | .-CACHE--20----------------.       |
>                       '-+-NO CACHE-----------------+-------'
>                         '-CACHE--integer-constant--'

> To get the current date you can use special registers like 'current
> date', 'current timestamp' etc.

> Both are completely described in the SQL Reference.

> Hope this helps.

Thanks. That certainly helps. The problem is that IDENTITY is so
tightly attched with db2 tables (unlike Sequences in Oracle). Can I get
the next sequence id without inserting a record in the table? My
application is written such a way that I need this nubmer upfront as I
tie it with other pieces of data and validate. If everything looks
good, only then I do an insert.

Tanuj

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

sequences and duals in db2

Post by ctr.. » Thu, 16 Nov 2000 04:00:00


Tanuj,

The trick is to wrap all your inserts/updates in one transaction. You
can perform all your checks and then choose to commit/rollback.

Within the transaction (turn autocommit off)
1. insert into table with identity column
2. values(identity_val_local()) will answer the identity value
generated in step #1.
3. use this value in your other insert/update statements
4. perform checks and commit/rollback transaction.

This basically works. Has worked for me.

Alternately, you could take the not-so-elegant approach and set up a
bunch of ID table(s) and stored procedures. That will delink the
sequencing process from the tables themselves.

Hope this helps.






> says...
> > > I was evaluating db2 for a potential migration from Oracle and
> wanted
> > > to know  whether there is an equivalent of 'oracle sequence' in
db2?
> > > How do I declare and use it?

> > > Also, is there any thing like the Oracle 'dual' in db2? ex: How
do I
> > > get the current date and time in a sql query (like i would
> use 'select
> > > sysdate from dual' in Oracle)?

> > > thanks,
> > > Tanuj Chatterjee

> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.

> > Hi,

> > With DB2 V7 you can use identity columns opposed to the oracle
> sequence.

> > identity-clause (copied from SQL Reference)

> > |---IDENTITY--+-----------------------------------------------------
-
> +->
> >               |   .-,-----------------------------------------
.      |
> >               |   V                 .-1----------------.
|      |
> >               '-(-----+-START WITH--+-numeric-constant-+---+--+---)-
-'
> >                       |               .-1----------------. |
> >                       +-INCREMENT BY--+-numeric-constant-+-+
> >                       | .-CACHE--20----------------.       |
> >                       '-+-NO CACHE-----------------+-------'
> >                         '-CACHE--integer-constant--'

> > To get the current date you can use special registers like 'current
> > date', 'current timestamp' etc.

> > Both are completely described in the SQL Reference.

> > Hope this helps.

> Thanks. That certainly helps. The problem is that IDENTITY is so
> tightly attched with db2 tables (unlike Sequences in Oracle). Can I
get
> the next sequence id without inserting a record in the table? My
> application is written such a way that I need this nubmer upfront as I
> tie it with other pieces of data and validate. If everything looks
> good, only then I do an insert.

> Tanuj

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

sequences and duals in db2

Post by Sailesh Krishnamurth » Thu, 16 Nov 2000 04:00:00


    Tanuj> Also, is there any thing like the Oracle 'dual' in db2? ex:
    Tanuj> How do I get the current date and time in a sql query (like
    Tanuj> i would use 'select sysdate from dual' in Oracle)?

You can either say:

values (expr)

Or say:

select expr from sysibm.sysdummy1

The sysibm.sysdummy1 is equivalent to Oracle's dual

--
Toodle-pip
Sailesh Krishnamurthy  
IBM Almaden Research Center
Ph: (408) 927-1913

 
 
 

sequences and duals in db2

Post by tchatter.. » Thu, 16 Nov 2000 04:00:00


Thanks a ton Sailesh, that worked :-)




>     Tanuj> Also, is there any thing like the Oracle 'dual' in db2? ex:
>     Tanuj> How do I get the current date and time in a sql query (like
>     Tanuj> i would use 'select sysdate from dual' in Oracle)?

> You can either say:

> values (expr)

> Or say:

> select expr from sysibm.sysdummy1

> The sysibm.sysdummy1 is equivalent to Oracle's dual

> --
> Toodle-pip
> Sailesh Krishnamurthy
> IBM Almaden Research Center
> Ph: (408) 927-1913

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

sequences and duals in db2

Post by tchatter.. » Fri, 17 Nov 2000 12:44:36


thanks Craig... that certainly helped. Being lazy, I used the second
technique ;-)



> Tanuj,

> The trick is to wrap all your inserts/updates in one transaction. You
> can perform all your checks and then choose to commit/rollback.

> Within the transaction (turn autocommit off)
> 1. insert into table with identity column
> 2. values(identity_val_local()) will answer the identity value
> generated in step #1.
> 3. use this value in your other insert/update statements
> 4. perform checks and commit/rollback transaction.

> This basically works. Has worked for me.

> Alternately, you could take the not-so-elegant approach and set up a
> bunch of ID table(s) and stored procedures. That will delink the
> sequencing process from the tables themselves.

> Hope this helps.






> > says...
> > > > I was evaluating db2 for a potential migration from Oracle and
> > wanted
> > > > to know  whether there is an equivalent of 'oracle sequence' in
> db2?
> > > > How do I declare and use it?

> > > > Also, is there any thing like the Oracle 'dual' in db2? ex: How
> do I
> > > > get the current date and time in a sql query (like i would
> > use 'select
> > > > sysdate from dual' in Oracle)?

> > > > thanks,
> > > > Tanuj Chatterjee

> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.

> > > Hi,

> > > With DB2 V7 you can use identity columns opposed to the oracle
> > sequence.

> > > identity-clause (copied from SQL Reference)

> > > |---IDENTITY--+---------------------------------------------------
--
> -
> > +->
> > >               |   .-,-----------------------------------------
> .      |
> > >               |   V                 .-1----------------.
> |      |
> > >               '-(-----+-START WITH--+-numeric-constant-+---+--+---
)-
> -'
> > >                       |               .-1----------------. |
> > >                       +-INCREMENT BY--+-numeric-constant-+-+
> > >                       | .-CACHE--20----------------.       |
> > >                       '-+-NO CACHE-----------------+-------'
> > >                         '-CACHE--integer-constant--'

> > > To get the current date you can use special registers
like 'current
> > > date', 'current timestamp' etc.

> > > Both are completely described in the SQL Reference.

> > > Hope this helps.

> > Thanks. That certainly helps. The problem is that IDENTITY is so
> > tightly attched with db2 tables (unlike Sequences in Oracle). Can I
> get
> > the next sequence id without inserting a record in the table? My
> > application is written such a way that I need this nubmer upfront
as I
> > tie it with other pieces of data and validate. If everything looks
> > good, only then I do an insert.

> > Tanuj

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

> Sent via Deja.com http://www.deja.com/
> Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

sequences and duals in db2

Post by bdh.. » Fri, 17 Nov 2000 04:00:00


  There is no need for a dummy table to get the values of special
registers in db2, the values constructor can be used:

Example:

 db2 'values(current date, user)'

on AIX command line

Bernard Dhooghe

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

1. DB2 Sequences ?

How to implement sequences with DB2 ? I have been desperately browsing
the manuals, but there is no such a thing as sequences. Any workarounds
available ?

Regards,

Juha Vehnia
Center of Research In Electronic Commerce,
University of Texas

2. How to use recordset returned by stored procedure

3. ** SEQUENCE Emulation TRIGGER for DB2/400 **

4. Client/Server Applications

5. Alternatives to Create Sequence for DB2 on OS/390

6. API's?

7. DB2 Sequences

8. Informix 4GL Contract Opportunity

9. can not get nextval from sequence, and db2 was shutdown

10. DB2 AS 400 AND CREATE SEQUENCE

11. DB2 retrieving sequences from stored procedures

12. db2 sequence

13. Oracle like sequences on AS/400 DB2