Re: DB2 Query Help

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

    Re: DB2 Query Help

    I am just getting my feet wet here in DB2 and Quest. Would it be quicker to
    create a temp table with just the client_cd I need and then query my count
    and sum results off of that. Is that possible to do and if quicker how would
    I approach it?

    As far as the table properties I am not the admin so I cannot change any
    settings or indexes. Is there anything in particular I should look for so I
    can tell the admin what to change?

    --
    Message posted via DBMonster.com
    http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200806/1


    Tags: None
    • Serge Rielau
      #2
      Re: DB2 Query Help

      nlburgess via DBMonster.com wrote:
      I am just getting my feet wet here in DB2 and Quest. Would it be quicker to
      create a temp table with just the client_cd I need and then query my count
      and sum results off of that. Is that possible to do and if quicker how would
      I approach it?
      >
      As far as the table properties I am not the admin so I cannot change any
      settings or indexes. Is there anything in particular I should look for so I
      can tell the admin what to change?
      >
      As pointed out by others the query you posted doesn't look like DB2 SQL.
      DATEPART(YEAR,C LAIM_THRU_DT) does not make sense in DB2, even as a UDF
      Would you mind posting the real query so we can better help you?
      It woudl also help to get a rundown of the column types.
      (describe table)

      Cheers
      Serge


      --
      Serge Rielau
      DB2 Solutions Development
      IBM Toronto Lab

        Comment

        • DA Morgan
          #3
          Re: DB2 Query Help

          Serge Rielau wrote:
          nlburgess via DBMonster.com wrote:
          >I am just getting my feet wet here in DB2 and Quest. Would it be
          >quicker to
          >create a temp table with just the client_cd I need and then query my
          >count
          >and sum results off of that. Is that possible to do and if quicker
          >how would
          >I approach it?
          >>
          >As far as the table properties I am not the admin so I cannot change any
          >settings or indexes. Is there anything in particular I should look
          >for so I
          >can tell the admin what to change?
          >>
          As pointed out by others the query you posted doesn't look like DB2 SQL.
          DATEPART(YEAR,C LAIM_THRU_DT) does not make sense in DB2, even as a UDF
          Would you mind posting the real query so we can better help you?
          It woudl also help to get a rundown of the column types.
          (describe table)
          >
          Cheers
          Serge
          DATEPART is SQL Server lineage.
          --
          Daniel A. Morgan
          Oracle Ace Director & Instructor
          University of Washington
          damorgan@x.wash ington.edu (replace x with u to respond)
          Puget Sound Oracle Users Group
          PSOUG.org - Oracle PL/SQL Database Code Library and Resources | Examples | Syntax | DBMS Packages | PL/SQL | SQL |
          https://www.psoug.org
          Oracle PL/SQL examples, syntax, DBMS packages, string, timestamp, substring, PHP code, and Javascript Code Reference Library (formerly known as Morgan's Library)

            Comment

            • Tonkuma
              #4
              Re: DB2 Query Help

              >Would it be quicker to create a temp table with just the client_cd I need and then query my count and sum results off of that. <<
              If you can specify the client_cd you need, I think that it would be
              better to add WHERE clause with that conditions for your query, before
              try to create a temp table.

              Another thought.
              If you don't need the output rows both RXPCFS and MEDPCFS are zero,
              you can add WHERE clause.
              WHERE CLAIM_TYPE_CD
              IN ('12','01','02' ,'03','04','06' ,'07','09','18' ,'21')
              I'm not sure it would get better performance.
              It may be no difference in performance.
              But, it would be worth to try.

                Comment

                • nlburgess via DBMonster.com
                  #5
                  Re: DB2 Query Help

                  Other than tweaking the database I have not found any other way to speed it
                  up which is something I will just have to deal with. It runs in 536.97
                  seconds on my laptop which is expected. Our db admin said he could change a
                  couple of things on his side to speed it up. Thanks for everyone's input.

                  --
                  Message posted via http://www.dbmonster.com

                    Comment

                    • Serge Rielau
                      #6
                      Re: DB2 Query Help

                      nlburgess via DBMonster.com wrote:
                      Other than tweaking the database I have not found any other way to speed it
                      up which is something I will just have to deal with. It runs in 536.97
                      seconds on my laptop which is expected. Our db admin said he could change a
                      couple of things on his side to speed it up. Thanks for everyone's input.
                      >
                      Great. And index on the column in the where clause column would be a start.

                      Cheers
                      Serge


                      --
                      Serge Rielau
                      DB2 Solutions Development
                      IBM Toronto Lab

                        Comment

                        Previous template Next
                        Working...