Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: making query more efficient

    I am hitting a DB2 to get info out to go into a SQL table. This query takes a long time to run. Is there anyway you can help me rewrite it to make it run faster?


    SELECT DISTINCT AL1.CLMS,
    AL1.COS,
    AL2.JURIS_MVT_SEQ_NUM,
    RIGHT(AL2.ORG_ID,3)AS ORG_ID,
    AL2.JURIS_MVT_TYP

    FROM MT2.CLAIM AL1, MT2.JURISMVT AL2

    WHERE (AL1.CLMS=AL2.CLMS AND AL1.BICNUM=AL2.BICNUM
    AND AL1.REF_NUM=AL2.REF_NUM AND
    AL1.TYP_OF_ACTN=AL2.TYP_OF_ACTN AND
    AL1.APP_STDT=AL2.APP_STDT AND
    AL1.ACTV_IND_CD=AL2.ACTV_IND_CD)
    AND ((AL1.ACTV_IND_CD='A' AND AL1.ADJU_LVL='I' AND AL1.WKLD='04' AND
    (NOT AL1.APP_RCPDT IS NULL)))

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... This query takes a long time to run. ...
    a) Compared with what?
    b) How long did it take actualy?

    Read this topic
    Performance overview - IBM DB2 9.7 for Linux, UNIX, and Windows
    especialy, Performance information that users can provide

    Some more questions(not complete):
    c) What DB2 version/release and OS are you using?
    d) What are your difinitions(CREATE statements) of tables/indexes ?
    e) How many rows in each tables?
    f) How many rows returned(or expected) in the query, before and after applying DISTINCT?
    g) Did you checked result of explain(access path)?
    h) Is it used indexes?
    ...

  3. #3
    Join Date
    Sep 2011
    Posts
    3

    DB2 Error

    I have four queries getting info from a DB2.

    When I run the one I'm trying to make more efficient I get:

    [DB2] SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014
    (IBM OLE DB Provider for DB2)

    Do you know what's causing this error?

  4. #4
    Join Date
    Sep 2011
    Posts
    3

    Just ran it and got this error:

    I had the user put their password in:

    Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "IBM OLE DB Provider for DB2" Hresult: 0x80040E4D Description: " SQL30082N Security processing failed with reason "19" ("USERID DISABLED or RESTRICTED"). SQLSTATE=08001 ". End Error Error: 2011-09-27 11:27:03.02 Code: 0xC020801C Source: Adding new PSC info into T2PendJuris Table OLE DB Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER . The AcquireConnection method call to the connection manager "DBP8.0758" failed with error code 0xC0202009. There may be error messages posted before this with more ... The package execution fa... The step failed

    Any suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •