In a prior life with prior versions of SAS and DB2 under OS/390, I had much the same problem. I was also writing some pretty convoluted queries that joined multiple tables just like in your example. They ran fine for a while, then things started getting very, very slow. In that case, the problem turned out to be some version of "DB2 was happy when the tables had X number of rows, but now that they have Y number of rows, you're out of luck."

We worked around it by having our DBA define DB2 views that did the fancy joins, then our SAS job would select from those views using some kind of key like customer ID. Not having intimate knowledge of what DB2 is doing under the hood, I find the fact that it helped dramatically somewhat counter-intuitive, but it did indeed fix the problem.

As for why Queryman works when SAS has trouble...uh...oops! Phone's ringing, gotta run!

Jeff

> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of > Sterling Price > Sent: Tuesday, January 07, 2003 3:04 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: DB2 woes > > > Greetings all, > > I have a situation where an OS/390 batch SAS program containing an SQL > pass-through query to DB2 is running for well over 24 hours without > finishing the query (after 24 hours our admins give up and > kill the job). > The query has completed successfully in the past, and last time it ran > successfully returned 16827 rows in about 2 minutes(!). > Nothing on the query > has changed since then. The same query running in Queryman (a > Windows-based > online query tool that uses ODBC) completes in only 25 > minutes. Clearly, > something isn't right. At first I just suspected that the > query needed to > be optimized or some additional indexes were needed on the > tables, but the > fact that it runs so much more quickly in another tool seems > to rule that > out. > > The query (run with the SASTRACE option and INOBS=1 option on > the Proc SQL > to show diagnostic information) is shown at the end of this > message. Has > anyone come across anything like this? So far SI support > hasn't been able > to suggest much except that it's probably a configuration > issue, but I have > no clue where to begin to look for what to look for in that regard. > > One thing I attempted was to use the libname engine to > perform the query > instead of SQL pass-through, which seemed like it would easy > enough, except > that I got this baffling error message: > > ERROR: Expression using IN has components that are of > different data types. > NOTE: The IN referred to may have been transformed from an OR > to an IN at > some point during PROC SQL where clause optimization. > > I say the message is baffling because all the values in the IN are > surrounded by single quotes, and the DDL for the table > indicates that the > column is set up as follows: > > DISC_CHARG_STAT_CD CHARACTER(1) FOR SBCS DATA > NOT NULL > > On the chance that there might be a bug with IN processing, I tried > re-coding the IN condition as a group of 5 OR conditions > instead, and got > the exact same result. Very curious. > > Anyway, I'm at the "grasping at straws" stage now - I'm > extremely grateful > for any insight into what I might try to get this job to run > in a reasonable > amount of time and/or how to get it to work using the libname > engine in the > hopes that it might circumvent the problem. > > Thank You, > > Sterling Price > > Log of Original Query Using DB2 Pass-Through (using INOBS=1 > so it completes > quickly) > > 13790 OPTIONS NOERRORABEND SASTRACE=',,,D'; > 13791 > 13792 PROC SQL INOBS=1; > 13792 ! > 13793 CONNECT TO DB2(SSID=&SSID); > TRACE: Entering d2init > TRACE: Exiting d2init > 13793 ! > 13794 CREATE TABLE FINALIZE AS > 13795 SELECT * FROM CONNECTION TO DB2( > 13796 SELECT > 13797 A.DEAL_DESC_CODE AS DEALTYPE > 13798 , A.BUYER_NAME AS BUY_NAME > 13799 , A.DEAL_ID AS DEAL_NBR > 13800 , A.VENDR_NBR AS SUPPLIER > 13801 , A.VNDR_DEAL_EFF_DATE AS EFF_DATE > 13802 , A.VNDR_DEAL_EXP_DATE AS EXP_DATE > 13803 , B.DISCOUNT_TYPE_CODE AS SUBDTYPE > 13804 , B.DCS_BUYER_USERID AS BUYER_ID > 13805 , B.PYMT_FREQ_CODE AS PYMTFREQ > 13806 , A.ACCT_DIV_NBR AS DIV_NBR > 13807 , C.STORE_DIV_NBR AS DIV_NBR1 > 13808 , C.DEPT_NBR AS DEPT_NBR > 13809 , I.DEPT_NAME AS DEPTNAME > 13810 , C.ENTRY_AMT AS NET_AMT > 13811 , E.REMIT_NAME AS SUPPNAME > 13812 , F.TAX_ITEM_CLAS_CODE AS DFLTCODE > 13813 , F.TAX_PCT AS DFLTRATE > 13814 , G.TAX_ITEM_CLAS_CODE AS ACTLCODE > 13815 , G.TAX_PERCENT AS ACTLRATE > 13816 FROM &OWNER1..VENDOR_DEAL A > 13817 , &OWNER1..DISCOUNT_CHARGE B > 13818 , &OWNER1..DAILY_RECEIVING C > 13819 , &OWNER4..VENDOR E > 13820 , &OWNER1..DDA_DISC_TYPE F > 13821 , &OWNER1..DEAL_ITEM G > 13822 , &OWNER5..AR_BILL H > 13823 , &OWNER2..DEPARTMENT I > 13824 WHERE B.DISC_CHARG_STAT_CD IN ('3','4','5','6','F') > 13825 AND A.DEAL_ID = B.DEAL_ID > 13826 AND A.DISCOUNT_TYPE_CODE = B.DISCOUNT_TYPE_CODE > 13827 AND A.PYMT_METHOD_CODE = B.PYMT_METHOD_CODE > 13828 AND A.DEAL_ID = C.DEAL_ID > 13829 AND C.BILL_NBR > 0 > 13830 AND C.BILL_NBR = H.BILL_NBR > 13831 AND H.BILLED_DATE BETWEEN &BEGYEAR AND &PROCESS > 13832 AND A.VENDR_NBR = E.VENDOR_NBR > 13833 AND B.DISCOUNT_TYPE_CODE = F.DISCOUNT_TYPE_CODE > 13834 AND A.DEAL_DESC_CODE = 3 > 13835 AND A.DEAL_ID = G.DEAL_ID > 13836 AND C.DEPT_NBR = I.DEPT_NBR > 13837 AND C.ITEM_NBR = G.ITEM_NBR > 13838 UNION ALL > 13839 SELECT > 13840 A.DEAL_DESC_CODE AS DEALTYPE > 13841 , A.BUYER_NAME AS BUY_NAME > 13842 , A.DEAL_ID AS DEAL_NBR > 13843 , A.VENDR_NBR AS SUPPLIER > 13844 , A.VNDR_DEAL_EFF_DATE AS EFF_DATE > 13845 , A.VNDR_DEAL_EXP_DATE AS EXP_DATE > 13846 , B.DISCOUNT_TYPE_CODE AS SUBDTYPE > 13847 , B.DCS_BUYER_USERID AS BUYER_ID > 13848 , B.PYMT_FREQ_CODE AS PYMTFREQ > 13849 , A.ACCT_DIV_NBR AS DIV_NBR > 13850 , C.STORE_DIV_NBR AS DIV_NBR1 > 13851 , C.DEPT_NBR AS DEPT_NBR > 13852 , I.DEPT_NAME AS DEPTNAME > 13853 , C.ENTRY_AMT AS NET_AMT > 13854 , E.REMIT_NAME AS SUPPNAME > 13855 , F.TAX_ITEM_CLAS_CODE AS DFLTCODE > 13856 , F.TAX_PCT AS DFLTRATE > 13857 , G.TAX_ITEM_CLAS_CODE AS ACTLCODE > 13858 , G.TAX_PERCENT AS ACTLRATE > 13859 FROM &OWNER1..VENDOR_DEAL A > 13860 , &OWNER1..DISCOUNT_CHARGE B > 13861 , &OWNER1..DAILY_RECEIVING C > 13862 , &OWNER4..VENDOR E > 13863 , &OWNER1..DDA_DISC_TYPE F > 13864 , &OWNER1..DEAL_ITEM G > 13865 , &OWNER5..AR_BILL H > 13866 , &OWNER2..DEPARTMENT I > 13867 WHERE B.DISC_CHARG_STAT_CD IN ('3','4','5','6','F') > 13868 AND A.DEAL_ID = B.DEAL_ID > 13869 AND A.DISCOUNT_TYPE_CODE = B.DISCOUNT_TYPE_CODE > 13870 AND A.PYMT_METHOD_CODE = B.PYMT_METHOD_CODE > 13871 AND A.DEAL_ID = C.DEAL_ID > 13872 AND C.BILL_NBR > 0 > 13873 AND C.BILL_NBR = H.BILL_NBR > 13874 AND H.BILLED_DATE BETWEEN &BEGYEAR AND &PROCESS > 13875 AND A.VENDR_NBR = E.VENDOR_NBR > 13876 AND B.DISCOUNT_TYPE_CODE = F.DISCOUNT_TYPE_CODE > 13877 AND A.DEAL_DESC_CODE IN (5,6) > 13878 AND A.DEAL_ID = G.DEAL_ID > 13879 AND C.DEPT_NBR = I.DEPT_NBR > 13880 AND C.ITEM_NBR = G.ITEM_NBR > 13881 WITH UR > 13882 ); > Prepare stmt: SELECT A.DEAL_DESC_CODE AS DEALTYPE , A.BUYER_NAME AS > BUY_NAME , A.DEAL_ID AS DEAL_NBR , A.VENDR_NBR AS SUPPLIER , > A.VNDR_DEAL_EFF_DATE AS EFF_DATE , A.VNDR_DEAL_EXP_DATE AS EXP_DATE , > B.DISCOUNT_TYPE_CODE AS SUBDTYPE , B.DCS_BUYER_USERID AS BUYER_ID , > B.PYMT_FREQ_CODE AS PYMTFREQ , A.ACCT_DIV_NBR AS DIV_NBR , > C.STORE_DIV_NBR > AS DIV_NBR1 , C.DEPT_NBR AS DEPT_NBR , I.DEPT_NAME AS > DEPTNAME , C.ENTRY_AMT > AS NET_AMT , E.REMIT_NAME AS SUPPNAME , F.TAX_ITEM_CLAS_CODE > AS DFLTCODE , > F.TAX_PCT AS DFLTRATE , G.TAX_ITEM_CLAS_CODE AS ACTLCODE , > G.TAX_PERCENT AS > ACTLRATE FROM GBDDACHG.VENDOR_DEAL A , GBDDACHG.DISCOUNT_CHARGE B , > GBDDACHG.DAILY_RECEIVING C , GBVENDOR.VENDOR E , > GBDDACHG.DDA_DISC_TYPE F , > GBDDACHG.DEAL_ITEM G , GBINTLAR.AR_BILL H ,GBDEPT00.DEPARTMENT I WHERE > B.DISC_CHARG_STAT_CD IN ('3','4','5','6','F') AND A.DEAL_ID = > B.DEAL_ID AND > A.DISCOUNT_TYPE_CODE = B.DISCOUNT_TYPE_CODE AND A.PYMT_METHOD_CODE = > B.PYMT_METHOD_CODE AND A.DEAL_ID = C.DEAL_ID AND C.BILL_NBR > 0 AND > C.BILL_NBR = H.BILL_NBR AND H.BILLED_DATE BETWEEN '2003-01-01' AND > '2003-01-07' AND A.VENDR_NBR = E.VENDOR_NBR AND B.DISCOUNT_TYPE_CODE = > F.DISCOUNT_TYPE_CODE AND A.DEAL_DESC_CODE = 3 AND A.DEAL_ID = > G.DEAL_ID AND > C.DEPT_NBR = I.DEPT_NBR AND C.ITEM_NBR = G.ITEM_NBR UNION ALL SELECT > A.DEAL_DESC_CODE AS DEALTYPE , A.BUYER_NAME AS BUY_NAME , A.DEAL_ID AS > DEAL_NBR , A.VENDR_NBR AS SUPPLIER , A.VNDR_DEAL_EFF_DATE AS > EFF_DATE , > A.VNDR_DEAL_EXP_DATE AS EXP_DATE , B.DISCOUNT_TYPE_CODE AS SUBDTYPE , > B.DCS_BUYER_USERID AS BUYER_ID , B.PYMT_FREQ_CODE AS PYMTFREQ , > A.ACCT_DIV_NBR AS DIV_NBR , C.STORE_DIV_NBR AS DIV_NBR1 , > C.DEPT_NBR AS > DEPT_NBR , I.DEPT_NAME AS DEPTNAME , C.ENTRY_AMT AS NET_AMT , > E.REMIT_NAME > AS SUPPNAME , F.TAX_ITEM_CLAS_CODE AS DFLTCODE , F.TAX_PCT AS > DFLTRATE , > G.TAX_ITEM_CLAS_CODE AS ACTLCODE , G.TAX_PERCENT AS ACTLRATE FROM > GBDDACHG.VENDOR_DEAL A , GBDDACHG.DISCOUNT_CHARGE B , > GBDDACHG.DAILY_RECEIVING C , GBVENDOR.VENDOR E , > GBDDACHG.DDA_DISC_TYPE F , > GBDDACHG.DEAL_ITEM G , GBINTLAR.AR_BILL H , > GBDEPT00.DEPARTMENT I WHERE B.DISC_CHARG_STAT_CD IN > ('3','4','5','6','F') > AND A.DEAL_ID = B.DEAL_ID AND A.DISCOUNT_TYPE_CODE = > B.DISCOUNT_TYPE_CODE > AND A.PYMT_METHOD_CODE = B.PYMT_METHOD_CODE AND A.DEAL_ID = > C.DEAL_ID AND > C.BILL_NBR > 0 AND C.BILL_NBR = H.BILL_NBR AND H.BILLED_DATE BETWEEN > '2003-01-01' AND '2003-01-07' AND A.VENDR_NBR = E.VENDOR_NBR AND > B.DISCOUNT_TYPE_CODE = F.DISCOUNT_TYPE_CODE AND > A.DEAL_DESC_CODE IN (5,6) > AND A.DEAL_ID = G.DEAL_ID AND C.DEPT_NBR = I.DEPT_NBR AND C.ITEM_NBR = > G.ITEM_NBR WITH UR > TRACE: Begin routine d2opene() > TRACE: Ending routine d2open() with success > TRACE: Begin routine d2prep() > TRACE: Ending routine d2prep() > WARNING: Only 1 records were read from the CONNECTION to DB2 > due to INOBS= > option. > NOTE: Table WORK.FINALIZE created, with 1 rows and 19 columns. > > TRACE: Begin routine d2close() > TRACE: Ending routine d2close() with success > 13882 ! > 13883 > 13884 CREATE TABLE WORK.DIVISION AS > 13885 SELECT * FROM CONNECTION TO DB2( > 13886 > 13887 SELECT DIV_NBR AS DIV_NBR, > 13888 DIV_NAME AS DIV_NAME > 13889 > 13890 FROM &OWNER3..DIVISION > 13891 ORDER BY DIV_NBR > 13892 WITH UR > 13893 ); > Prepare stmt: SELECT DIV_NBR AS DIV_NBR, DIV_NAME AS DIV_NAME FROM > GBSNS001.DIVISION ORDER BY DIV_NBR WITH UR > TRACE: Begin routine d2opene() > TRACE: Ending routine d2open() with success > TRACE: Begin routine d2prep() > TRACE: Ending routine d2prep() > WARNING: Only 1 records were read from the CONNECTION to DB2 > due to INOBS= > option. > NOTE: Table WORK.DIVISION created, with 1 rows and 2 columns. > > TRACE: Begin routine d2close() > TRACE: Ending routine d2close() with success > 13893 ! > 13894 > 13895 %PUT &SQLXMSG; * DEBUGGING AID; > > 13896 OPTIONS ERRORABEND; > 18 > The SAS System > > TRACE: Begin routine d2term > TRACE: Begin routine d2term > TRACE: End routine d2term > NOTE: The PROCEDURE SQL used 0.31 CPU seconds and 9004K. > > Log of my attempt to use the libname engine instead of SQL > pass-through > > 13793 LIBNAME OWNER1 DB2 SSID=DSN9 SCHEMA=GBDDACHG; > NOTE: Libref OWNER1 was successfully assigned as follows: > Engine: DB2 > Physical Name: DSN9 > 13793 ! > 13794 LIBNAME OWNER2 DB2 SSID=DSN9 SCHEMA=GBDEPT00; > NOTE: Libref OWNER2 was successfully assigned as follows: > Engine: DB2 > Physical Name: DSN9 > 13794 ! > 13795 LIBNAME OWNER3 DB2 SSID=DSN9 SCHEMA=GBSNS001; > NOTE: Libref OWNER3 was successfully assigned as follows: > Engine: DB2 > Physical Name: DSN9 > 13795 ! > 13796 LIBNAME OWNER4 DB2 SSID=DSN9 SCHEMA=GBVENDOR; > NOTE: Libref OWNER4 was successfully assigned as follows: > Engine: DB2 > Physical Name: DSN9 > 13796 ! > 13797 LIBNAME OWNER5 DB2 SSID=DSN9 SCHEMA=GBINTLAR; > NOTE: Libref OWNER5 was successfully assigned as follows: > Engine: DB2 > Physical Name: DSN9 > 13797 ! > 13798 > 13799 PROC SQL INOBS=1; > 13799 ! > 13800 CREATE TABLE FINALIZE AS > 13801 SELECT > 13802 A.DEAL_DESC_CODE AS DEALTYPE > 13803 , A.BUYER_NAME AS BUY_NAME > 13804 , A.DEAL_ID AS DEAL_NBR > 13805 , A.VENDR_NBR AS SUPPLIER > 13806 , A.VNDR_DEAL_EFF_DATE AS EFF_DATE > 13807 , A.VNDR_DEAL_EXP_DATE AS EXP_DATE > 6 > The SAS System > > 13808 , B.DISCOUNT_TYPE_CODE AS SUBDTYPE > 13809 , B.DCS_BUYER_USERID AS BUYER_ID > 13810 , B.PYMT_FREQ_CODE AS PYMTFREQ > 13811 , A.ACCT_DIV_NBR AS DIV_NBR > 13812 , C.STORE_DIV_NBR AS DIV_NBR1 > 13813 , C.DEPT_NBR AS DEPT_NBR > 13814 , I.DEPT_NAME AS DEPTNAME > 13815 , C.ENTRY_AMT AS NET_AMT > 13816 , E.REMIT_NAME AS SUPPNAME > 13817 , F.TAX_ITEM_CLAS_CODE AS DFLTCODE > 13818 , F.TAX_PCT AS DFLTRATE > 13819 , G.TAX_ITEM_CLAS_CODE AS ACTLCODE > 13820 , G.TAX_PERCENT AS ACTLRATE > 13821 FROM OWNER1.VENDOR_DEAL A > 13822 , OWNER1.DISCOUNT_CHARGE B > 13823 , OWNER1.DAILY_RECEIVING C > 13824 , OWNER4.VENDOR E > 13825 , OWNER1.DDA_DISC_TYPE F > 13826 , OWNER1.DEAL_ITEM G > 13827 , OWNER5.AR_BILL H > 13828 , OWNER2.DEPARTMENT I > 13829 WHERE B.DISC_CHARG_STAT_CD IN ('3','4','5','6','F') > 13830 AND A.DEAL_ID = B.DEAL_ID > 13831 AND A.DISCOUNT_TYPE_CODE = B.DISCOUNT_TYPE_CODE > 13832 AND A.PYMT_METHOD_CODE = B.PYMT_METHOD_CODE > 13833 AND A.DEAL_ID = C.DEAL_ID > 13834 AND C.BILL_NBR > 0 > 13835 AND C.BILL_NBR = H.BILL_NBR > 13836 AND H.BILLED_DATE BETWEEN &BEGYEAR AND &PROCESS > 13837 AND A.VENDR_NBR = E.VENDOR_NBR > 13838 AND B.DISCOUNT_TYPE_CODE = F.DISCOUNT_TYPE_CODE > 13839 AND A.DEAL_DESC_CODE = 3 > 13840 AND A.DEAL_ID = G.DEAL_ID > 13841 AND C.DEPT_NBR = I.DEPT_NBR > 13842 AND C.ITEM_NBR = G.ITEM_NBR > 13843 UNION ALL > 13844 SELECT > 13845 A.DEAL_DESC_CODE AS DEALTYPE > 13846 , A.BUYER_NAME AS BUY_NAME > 13847 , A.DEAL_ID AS DEAL_NBR > 13848 , A.VENDR_NBR AS SUPPLIER > 13849 , A.VNDR_DEAL_EFF_DATE AS EFF_DATE > 13850 , A.VNDR_DEAL_EXP_DATE AS EXP_DATE > 13851 , B.DISCOUNT_TYPE_CODE AS SUBDTYPE > 13852 , B.DCS_BUYER_USERID AS BUYER_ID > 13853 , B.PYMT_FREQ_CODE AS PYMTFREQ > 13854 , A.ACCT_DIV_NBR AS DIV_NBR > 13855 , C.STORE_DIV_NBR AS DIV_NBR1 > 13856 , C.DEPT_NBR AS DEPT_NBR > 13857 , I.DEPT_NAME AS DEPTNAME > 13858 , C.ENTRY_AMT AS NET_AMT > 13859 , E.REMIT_NAME AS SUPPNAME > 13860 , F.TAX_ITEM_CLAS_CODE AS DFLTCODE > > 13861 , F.TAX_PCT AS DFLTRATE > > 13862 , G.TAX_ITEM_CLAS_CODE AS ACTLCODE > > 13863 , G.TAX_PERCENT AS ACTLRATE > > 13864 FROM OWNER1.VENDOR_DEAL A > > 13865 , OWNER1.DISCOUNT_CHARGE B > > 7 > The SAS System > > > > 13866 , OWNER1.DAILY_RECEIVING C > > 13867 , OWNER4.VENDOR E > > 13868 , OWNER1.DDA_DISC_TYPE F > > 13869 , OWNER1.DEAL_ITEM G > > 13870 , OWNER5.AR_BILL H > > 13871 , OWNER2.DEPARTMENT I > > 13872 WHERE B.DISC_CHARG_STAT_CD IN ('3','4','5','6','F') > > 13873 AND A.DEAL_ID = B.DEAL_ID > > 13874 AND A.DISCOUNT_TYPE_CODE = B.DISCOUNT_TYPE_CODE > > 13875 AND A.PYMT_METHOD_CODE = B.PYMT_METHOD_CODE > > 13876 AND A.DEAL_ID = C.DEAL_ID > > 13877 AND C.BILL_NBR > 0 > > 13878 AND C.BILL_NBR = H.BILL_NBR > > 13879 AND H.BILLED_DATE BETWEEN &BEGYEAR AND &PROCESS > > 13880 AND A.VENDR_NBR = E.VENDOR_NBR > > 13881 AND B.DISCOUNT_TYPE_CODE = F.DISCOUNT_TYPE_CODE > > 13882 AND A.DEAL_DESC_CODE IN (5,6) > > 13883 AND A.DEAL_ID = G.DEAL_ID > > 13884 AND C.DEPT_NBR = I.DEPT_NBR > > 13885 AND C.ITEM_NBR = G.ITEM_NBR; > > ERROR: Expression using IN has components that are of > different data types. > > ERROR: Expression using IN has components that are of > different data types. > > ERROR: Expression using IN has components that are of > different data types. > > NOTE: The IN referred to may have been transformed from an OR > to an IN at > some point during PROC SQL where clause optimization. > ERROR: Expression using IN has components that are of > different data types. > > ERROR: Expression using IN has components that are of > different data types. > > ERROR: Expression using IN has components that are of > different data types. > > NOTE: The IN referred to may have been transformed from an OR > to an IN at > some point during PROC SQL where clause optimization. > NOTE: PROC SQL set option NOEXEC and will continue to check > the syntax of > statements. > > > 13885 ! > > 13886 > > 13887 CREATE TABLE WORK.DIVISION AS > > 13888 > > 13889 SELECT DIV_NBR AS DIV_NBR, > > 13890 DIV_NAME AS DIV_NAME > > 13891 > > 13892 FROM OWNER3.DIVISION > > 13893 ORDER BY DIV_NBR; > > NOTE: Statement not executed due to NOEXEC option. > > 13893 ! > > 13894 > > 13895 QUIT; > > NOTE: The SAS System stopped processing this step because of errors. > > NOTE: The PROCEDURE SQL used 0.05 CPU seconds and 9454K. > > > > > > Wal-Mart Confidential > > > > > ********************************************************************** > This email and any files transmitted with it are confidential > and intended solely for the individual or entity to > whom they are addressed. If you have received this email > in error destroy it immediately. > ********************************************************************** > Wal-Mart Stores, Inc. Confidential > ************************************************************** > ********