Sequences of Oracle

[Guest.Visitor]

Hi Any ideas how Sequences tables of Oracle can be implemented on the AS/400. I haven't found an equivalent command/statement in DB2. Thanx in advance arun

[Guest.Visitor]

Arun, There are several ways that you can replicate a sequence. You can use a User Defined Function (UDF), a trigger, or create a native program. Another option may be a timestamp, but it is possible to get a duplicate timestamp on a multi-processor machine. Back in February, Midrange Computing published an article on UDF containing a Seq() UDF. The RPG version could easily be modified with a data area to supply a unique sequence. The article is available on line at: http://www.midrangecomputing.com/mc/article.cfm?titleid=a289&md=20004 If you would like help with the modification, I could post some code. David Morris

[Guest.Visitor]

Hi Since the complete development is using only SQL, could you please give me a SQL version of the RPG code that I could use, i.e UDF in SQL programming languauge. I am working on a V4R4 machine Thanx in advance arun

[Guest.Visitor]

Arun, Here are the necessary parts: <pre> The RPG member, put in qrpglesrc, member numseq. H NoMain H OPTION(*SRCSTMT) /COPY QPROTOSRC,NumSeq ********************** * Last used sequence * ********************** DLstSeqDS DS DTAARA(LstSeq) D LstSeq 10U 0 /TITLE GetSeq - Return a sequencial number. ************************************************** ****************************** ************** * *IntSeq Return an integer numeric sequennce. * ************************************************** ****************************** ************** PGetSeq B EXPORT DGetSeq PI 10I 0 C *LOCK IN LstSeqDS C EVAL LstSeq = LstSeq + 1 C OUT LstSeqDS C RETURN LstSeq PGetSeq E The prototype member, put in qprotosrc, member numseq. ************************************************** ****************************** ************** * GetSeq Return a unique sequence. * * Return: * * Seq A sequencial 4 byte integer. * ************************************************** ****************************** ************** DGetSeq PR 10I 0 Compile with the following commands: CRTDTAARA DTAARA(objlib/LstSeq) TYPE(*CHAR) LEN(4) VALUE(X'00000000') CRTRPGMOD MODULE(objlib/NumSeq) SRCFILE(srclib/QRPGLESRC) SRCMBR(NumSeq) DBGVIEW(*SOURCE) CRTSRVPGM SRVPGM(objlib/NumSeq) EXPORT(*ALL) CREATE FUNCTION objlib/GetSeq() RETURNS INTEGER LANGUAGE RPGLE EXTERNAL NAME 'objlib/NUMSEQ(GETSEQ)' NOT DETERMINISTIC NO EXTERNAL ACTION PARAMETER STYLE GENERAL </pre> Hopefully this gives you a good start. I would have liked to have defined a bigint for the reutrn, but it wouldn't let me on V4R4. On V4R5 I would use this option, defining the return value as 20i0. David Morris