Date:         Wed, 14 Jun 2006 11:46:11 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: sql job never ending
Comments: To: Hari Nath <hari_s_nath@YAHOO.COM>
Content-Type: text/plain; charset="us-ascii"

Hari:
Two classes of problems potentially slow down queries on external
databases:
1. queries that cannot be optimized;
2. queries that require transfers of very large databases to SAS.

I suspect that the latter problem is causing the query to run for a very
long time. The query appears to subset data sufficiently to make other
optimization unnecessary.

SAS and DB2 have to play together nicely for this query to work as
expected. SAS has to substitute a DB2 function for the SAS YEAR()
function and use the DB2 form of the IN operator. SAS also has to make
sure that date comparisons work and delay application of the COMPRESS()
and PUT() functions.

Ideal SAS query optimizers and ODBC middleware would select all of the
ON and WHERE constrains that can be 'passed thru' to DB2 and let DB2
apply them with the left join. The left join would then subset what
needs to return to SAS to a small fraction of an unconstrained join of
the two DB2 datasets.

More likely SAS behind the scenes is asking DB2 to transfer both
datasets to SAS. Unless you have exceptionally quick network transfer
rates, that could take a long time.

I'd try dividing the query into two queries. In the first, select
required column variables and rows into a WORK library dataset. The
company, product, and account ON constraints should reduce the yield of
the query substantially and converting the IN operator to AND conditions
should also work OK under DB2. In a second query, apply the rest of the
constraints and compute the year function and cust_id variable.
Sig

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Hari Nath
Sent: Wednesday, June 14, 2006 10:51 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: sql job never ending


Hi all,
  I have been running the following code and i was running this for an
hour approx. and it doesnt seem to end or the tempdata.customer_x_1
still reads with 0 rows and 7 columns in my log..........and my manager
said to try running the program overnight, which i havent tried.....Iam
connecting from pc/sas to a db2 database using libname....can someone
tell whats going wrong here......am using sas 9.1.3 Thanks hari

LIBNAME db2_p odbc USER=&user_id PASSWORD=&user_password DATASRC=db2p
        SCHEMA=DWH PRESERVE_COL_NAMES=YES PRESERVE_TAB_NAMES=YES;

    proc sql;
        create table tempdata.customer_x_1 (compress=yes) as
            select
                    year(a.snapshot_date) as year,
                    a.snapshot_month as month,
                    b.appl_company as a_appl_company,
                    b.account_product_cd as a_acct_prod_cd,
                    b.account_number,
                    b.cis_branch as a_branch,
                    compress(a.cu1_appl_company||a.cu1_customer_id||put
(a.cu1_cust_tie_brkr,8.)) as cust_id
                from DB2_P.CUSTOMER_X_ACCOUNT a
                    left outer join DB2_P.ACCOUNT b
                        on a.snapshot_month = b.snapshot_month
                            and a.snapshot_date = b.snapshot_date
                            and a.ac1_appl_company = b.appl_company
                            and a.ac1_acct_prod_cd =
b.account_product_cd
                            and a.ac1_account_number = b.account_number
                where a.primary_cust_ind='Y' and
                        a.snapshot_month = &curr_month and
                        year(a.snapshot_date) = &curr_year and
                        b.account_product_cd in
("CDA", "DDA", "FEX", "REA", "SEC", "TMS")
                order by cust_id;
    quit;