Is this temp table indexed? Have you done runstats?
SESSION.BTT_CON_PREP#1

Cheers
Serge


"senthil1" <db2udbdba-ezmlmshield-x58188192.[Email address protected] wrote
on 03/22/2006 09:06:13 AM:

>
> Hi All,
>
> Require your help in sql tuning
>
> The scenario is, I am joining temporary table with a permanent table on a

> field.
>
> The permanent table is indexed on the predicate key (CLNT_ID) used for
> this query.
> Runstats has been run periodically for this table.
> Temporary table is also index on the predicate key (CLNT_ID)
>
> Temporary table has some 10,000 records
> Permanent table has 5639299 records .
>
> While joining these two tables, it takes around 2 to 2.5 hours. (it is a

> data warehousing application, and it is written in BULK SQLs on DB2 UDB
> V8.1 on AIX.
> BULK SQLs are CRUD. (Create,Read,Update,Delete logic))
>
> Help me to tune this query.
>
>
> NSERT INTO SESSION.CLIENTTEMP0 (
> SELECT A.CLNT_ID
> , A.ADMIN_DATA_SRC_CD
> , A.CONTR_ID
> , A.AGT_ID
> , A.CMF_SEL_IND
> , A.CPS_CLNT_ID
> , A.MNT_TYP_CD
> , A.DEL_IND
> , B.TXPYR_ID
> , B.MRTL_CD
> , B.LAST_NM
> , CASE
> WHEN
> SUBSTR(RTRIM(LTRIM(B.FIRST_NM)),1,POSSTR(RTRIM(LTRIM(B.FIRST_NM)),'
'))='
> '
> THEN
> RTRIM(LTRIM(B.FIRST_NM))
> ELSE
> SUBSTR(RTRIM(LTRIM(B.FIRST_NM)),1,POSSTR(RTRIM(LTRIM(B.FIRST_NM)),' '))
> END
> , SUBSTR(B.LAST_NM,1,4) ||
> CASE
> WHEN
> SUBSTR(RTRIM(LTRIM(B.FIRST_NM)),1,POSSTR(RTRIM(LTRIM(B.FIRST_NM)),'
'))='
> '
> THEN
> RTRIM(LTRIM(B.FIRST_NM))
> ELSE
> SUBSTR(RTRIM(LTRIM(B.FIRST_NM)),1,POSSTR(RTRIM(LTRIM(B.FIRST_NM)),' '))
> END
> , B.BIRTH_DT
> , B.MDL_NM
> , B.SFX_NM
> , (DAYS(CURRENT DATE)-DAYS(COALESCE(B.BIRTH_DT,'01/01/0001')))/365
> , B.GNDR_CD
> , B.PHN_AREA_CD
> , B.PHN_NUM
> , A.FIRST_NM
> , A.LAST_NM
> FROM
> BTCIPRD.BTT_CIS_ADMIN07 B
> , SESSION.BTT_CON_PREP#1 A
> WHERE A.CLNT_ID = B.CLNT_ID )
>
> Your help is appreciated
>
> Regards
> Senthil S.
> Tata Consultancy Services Limited
> Mailto: senthil1.[Email address protected]
> Website: http://www.tcs.com
>
> Notice: The information contained in this e-mail message and/or
> attachments to it may contain confidential or privileged
> information. If you are not the intended recipient, any
> dissemination, use, review, distribution, printing or copying of the
> information contained in this e-mail message and/or attachments to
> it are strictly prohibited. If you have received this
> communication in error, please notify us by reply e-mail or
> telephone and immediately and permanently delete the message and any
> attachments. Thank you
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


DB2 & UDB email list listserv db2-l LazyDBA home page