max sequence value --- how?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • aj
    #1

    max sequence value --- how?

    DB2 LUW 8.1 fixpak 14
    Linux Red Hat AS

    I'm trying to get the maximum value of a sequence. Because of the way
    the sequence is used (on several tables comprising a fat view), its too
    expensive to do the usual
    select max(<col>) from <view;

    Is there some way for the system catalogs or some trick to get me the
    maximum value of the sequence?

    Note that I'm on DB2 LUW 8.1 fixpak 14. There's apparently some
    sequence highwater column in v9, but I don't think I have it.

    tia

    aj
    Tags: None
    • jefftyzzer
      #2
      Re: max sequence value --- how?

      On Jul 22, 1:19 pm, aj <ron...@mcdonal ds.comwrote:
      DB2 LUW 8.1 fixpak 14
      Linux Red Hat AS
      >
      I'm trying to get the maximum value of a sequence. Because of the way
      the sequence is used (on several tables comprising a fat view), its too
      expensive to do the usual
      select max(<col>) from <view;
      >
      Is there some way for the system catalogs or some trick to get me the
      maximum value of the sequence?
      >
      Note that I'm on DB2 LUW 8.1 fixpak 14. There's apparently some
      sequence highwater column in v9, but I don't think I have it.
      >
      tia
      >
      aj
      You can see the next value (that would have been assigned), which is
      presumably one more than the present "maximum value" by running the
      following:

      VALUES NEXTVAL FOR <YOUR_SEQ_SCHEM A.YOUR_SEQ_NAME >;

      --Jeff

        Comment

        • Serge Rielau
          #3
          Re: max sequence value --- how?

          aj wrote:
          DB2 LUW 8.1 fixpak 14
          Linux Red Hat AS
          >
          I'm trying to get the maximum value of a sequence. Because of the way
          the sequence is used (on several tables comprising a fat view), its too
          expensive to do the usual
          select max(<col>) from <view;
          >
          Is there some way for the system catalogs or some trick to get me the
          maximum value of the sequence?
          >
          Note that I'm on DB2 LUW 8.1 fixpak 14. There's apparently some
          sequence highwater column in v9, but I don't think I have it.
          >
          tia
          >
          aj
          SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE
          If you don't have it on your version look in SYSIBM.SEQUENCE S

          Cheers
          Serge


          --
          Serge Rielau
          DB2 Solutions Development
          IBM Toronto Lab

            Comment

            • aj
              #4
              Re: max sequence value --- how?

              Serge: thx for reply.

              There is no SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE,
              and SYSIBM.SEQUENCE S does not exist....???

              Any other ideas?
              aj


              Serge Rielau wrote:
              aj wrote:
              >DB2 LUW 8.1 fixpak 14
              >Linux Red Hat AS
              >>
              >I'm trying to get the maximum value of a sequence. Because of the way
              >the sequence is used (on several tables comprising a fat view), its too
              >expensive to do the usual
              >select max(<col>) from <view;
              >>
              >Is there some way for the system catalogs or some trick to get me the
              >maximum value of the sequence?
              >>
              >Note that I'm on DB2 LUW 8.1 fixpak 14. There's apparently some
              >sequence highwater column in v9, but I don't think I have it.
              >>
              >tia
              >>
              >aj
              SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE
              If you don't have it on your version look in SYSIBM.SEQUENCE S
              >
              Cheers
              Serge
              >
              >

                Comment

                • Tomas
                  #5
                  Re: max sequence value --- how?

                  On Jul 23, 8:09 am, aj <ron...@mcdonal ds.comwrote:
                  Serge:  thx for reply.
                  >
                  There is no SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE,
                  and SYSIBM.SEQUENCE S does not exist....???
                  >
                  Any other ideas?
                  aj
                  >
                  >
                  >
                  Serge Rielau wrote:
                  aj wrote:
                  DB2 LUW 8.1 fixpak 14
                  Linux Red Hat AS
                  >
                  I'm trying to get the maximum value of a sequence.  Because of the way
                  the sequence is used (on several tables comprising a fat view), its too
                  expensive to do the usual
                  select max(<col>) from <view;
                  >
                  Is there some way for the system catalogs or some trick to get me the
                  maximum value of the sequence?
                  >
                  Note that I'm on DB2 LUW 8.1 fixpak 14.  There's apparently some
                  sequence highwater column in v9, but I don't think I have it.
                  >
                  tia
                  >
                  aj
                  SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE
                  If you don't have it on your version look in SYSIBM.SEQUENCE S
                  >
                  Cheers
                  Serge- Hide quoted text -
                  >
                  - Show quoted text -
                  Try SYSIBM.SYSSEQUE NCES

                  /T

                    Comment

                    • aj
                      #6
                      Re: max sequence value --- how?

                      This is just what I needed. Thanks Tomas.

                      Tomas wrote:
                      On Jul 23, 8:09 am, aj <ron...@mcdonal ds.comwrote:
                      >Serge: thx for reply.
                      >>
                      >There is no SYSCAT.SEQUENCE S.NEXTCACHFIRST VALUE,
                      >and SYSIBM.SEQUENCE S does not exist....???
                      >>
                      >Any other ideas?
                      >aj
                      >>
                      >>
                      >>
                      >Serge Rielau wrote:
                      >>aj wrote:
                      >>>DB2 LUW 8.1 fixpak 14
                      >>>Linux Red Hat AS
                      >>>I'm trying to get the maximum value of a sequence. Because of the way
                      >>>the sequence is used (on several tables comprising a fat view), its too
                      >>>expensive to do the usual
                      >>>select max(<col>) from <view;
                      >>>Is there some way for the system catalogs or some trick to get me the
                      >>>maximum value of the sequence?
                      >>>Note that I'm on DB2 LUW 8.1 fixpak 14. There's apparently some
                      >>>sequence highwater column in v9, but I don't think I have it.
                      >>>tia
                      >>>aj
                      >>SYSCAT.SEQUEN CES.NEXTCACHFIR STVALUE
                      >>If you don't have it on your version look in SYSIBM.SEQUENCE S
                      >>Cheers
                      >>Serge- Hide quoted text -
                      >- Show quoted text -
                      >
                      Try SYSIBM.SYSSEQUE NCES
                      >
                      /T

                        Comment

                        • Serge Rielau
                          #7
                          Re: max sequence value --- how?

                          Sorry.. sleep deprived.
                          Have been standing on the Newark runway half of last night :-(

                          Cheers
                          Serge

                          --
                          Serge Rielau
                          DB2 Solutions Development
                          IBM Toronto Lab

                            Comment

                            • aj
                              #8
                              Re: max sequence value --- how?

                              No problem Serge - I appreciate all the help you provide. :)
                              Long layovers are no fun...

                              Serge Rielau wrote:
                              Sorry.. sleep deprived.
                              Have been standing on the Newark runway half of last night :-(
                              >
                              Cheers
                              Serge
                              >

                                Comment

                                Previous template Next
                                Working...