[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Dynamic SQL Candidate: Inefficient, logic-ridden SQL Statement

Hey Techies--

It has been some time since I've been in the DB2 world.  Now that I'm back, I'm taking with me some ideas from MS Sql Server.  Please review the core statement I have--which works but is inefficient. This has been placed in a stored procedure--so the variables supplied will be populated by the calling client--and yes, they could be blank individually and in combination (different meaning depending on which),  Is dynamic SQL a possibility? If it is, please instruct me---I will then be able to use this as a point of reference.  Any help would be appreciated.
CREATE PROCEDURE PRDPGMLIB.GETEMPBYJOBTYPE01(
                    IN @EMPIDIN VARCHAR(20),
                    IN @COMPANY VARCHAR(20),
                    IN @ACTIVE VARCHAR(20),
                    IN @LOCATION VARCHAR(20),
                    IN @OPERATIONALAREA VARCHAR(20),
                    IN @JOBCODE VARCHAR(20),
                    IN @JOBTYPE VARCHAR(20),
    				IN @NAME VARCHAR(20)
                    ) 
	DYNAMIC RESULT SETS 1 
	LANGUAGE SQL 
	SPECIFIC PRDPGMLIB.GETEMPBYJOBTYPE01 
	NOT DETERMINISTIC  
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	SET OPTION  ALWBLK = *READ , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DFTRDBCOL = *NONE , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *OWNER , 
	USRPRF = *OWNER,
	SRTSEQ = *HEX   

BEGIN 

DECLARE NOT_FOUND CONDITION FOR SQLSTATE '02000' ; 
DECLARE EOF_COND CHAR(1)  DEFAULT '0' ;

DECLARE FN01 VARCHAR(20);
DECLARE FN02 VARCHAR(20);
DECLARE FN03 VARCHAR(20);
DECLARE LN01 VARCHAR(20);
DECLARE LN02 VARCHAR(20);
DECLARE LN03 VARCHAR(20);
DECLARE CN01 VARCHAR(20);
DECLARE CN02 VARCHAR(20);
DECLARE CN03 VARCHAR(20);

 
DECLARE CSR1 CURSOR FOR

SELECT pren  as  empid, 
           prer  as  company, 
           prfnm as  firstname, 
           prcomn as commonname, 
           prmnm as  middlename, 
           prlnm as  lastname, 
           prl04 as  location, 
           PRDPGMLIB.FIELDORCORP(prl01) AS operationalarea,
           prtede as  termdate, 
           CASE
              WHEN prtedh = 0 THEN '1'
              ELSE '0'
            END AS isactive,
          prstat as status,
          prjob1 as jobcode, 
          prusr3 as jobtype,
          prpos   AS positioncode,
          PRDPGMLIB.POSITIONTITLE(prer, prpos) AS positiontitle, 
          prdohe  AS hiredate, 
          prrehe  AS rehiredate, 
          prpos   AS hirecode, 
          prssn   AS ssn, 
          prdobe  AS dateofbirth, 
          preth   AS ethnic,
          PRDPGMLIB.ETHNICDESC(prer,preth) AS ethnicdesc, 
          prsex   AS sex, 
          prstr1  AS addressline1,
          prstr2  AS addressline2, 
          prcty1  AS city, 
          prsta1  AS state, 
          przip1  AS zipcode,
          preml1  AS email1,
          preml2  AS email2,
          prtl1   AS telephone1,
          prtl2   AS telephone2,
          prtl3   AS telephone3,
          prtl4   AS telephone4
  FROM HRDBFA.PRPMS
 WHERE 
         ((@EMPIDIN <> ' ' AND pren=@EMPIDIN)
      OR (@EMPIDIN = ' '))
      AND 
           ((@COMPANY <> ' ' AND prer=@COMPANY)
         OR(@COMPANY = ' '))
      AND 
            ((@ACTIVE = '0' AND prtedh>0)
          OR(@ACTIVE = '1' AND prtedh=0)
          OR(@ACTIVE = ' '))
      AND 
           ((@LOCATION <> ' ' AND prL04=@LOCATION)
         OR(@LOCATION = ' '))
      AND 
           ((@OPERATIONALAREA <> ' ' AND PRDPGMLIB.FIELDORCORP(prL01)=@OPERATIONALAREA)
         OR(@OPERATIONALAREA = ' '))
      AND 
           ((@JOBCODE <> ' ' AND prjob1=@JOBCODE)
         OR(@JOBCODE = ' '))
      AND 
           ((@JOBTYPE <> ' ' AND prusr3=@JOBTYPE)
         OR(@JOBTYPE = ' '))
      AND 
         ((@NAME <> ' ' AND (prfnm LIKE FN01
					 OR prfnm LIKE FN02
					 OR prfnm LIKE FN03
                     OR prlnm LIKE LN01
					 OR prlnm LIKE LN02
					 OR prlnm LIKE LN03
			         OR prcomn LIKE CN01
					 OR prcomn LIKE CN02
					 OR prcomn LIKE CN03))
         OR(@NAME = ' '))
;



SET    @EMPIDIN = TRIM(@EMPIDIN),
       @COMPANY = TRIM(UPPER(@COMPANY)),
       @ACTIVE = TRIM(@ACTIVE),
       @LOCATION = TRIM(UPPER(@LOCATION)),
       @OPERATIONALAREA = TRIM(UPPER(@OPERATIONALAREA)),
       @JOBCODE = TRIM(UPPER(@JOBCODE)),
       @JOBTYPE = TRIM(UPPER(@JOBTYPE)),
       FN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       FN02 = '%' || TRIM(UPPER(@NAME)),
       FN03 = TRIM(UPPER(@NAME)) || '%',
       LN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       LN02 = '%' || TRIM(UPPER(@NAME)),
       LN03 = TRIM(UPPER(@NAME)) || '%',
       CN01 = '%' || TRIM(UPPER(@NAME)) || '%',
       CN02 = '%' || TRIM(UPPER(@NAME)),
       CN03 = TRIM(UPPER(@NAME)) || '%'
;



OPEN CSR1;
RETURN;
CLOSE CSR1; 

  
END }

Open in new window

0
Paula DiTallo
Asked:
Paula DiTallo
2 Solutions
 
momi_sabagCommented:
this is very inefficient
when db2 has an or in the where clause, indexes will not be used

you should use dynamic sql
basically - just like sql server
you set up a varchar variable with the statement
and then declare a cursor to use that statement
prepare it
and open the cursor
you can see an example here
http://www.dbforums.com/db2/999848-dynamic-cursor-stored-procedure.html
0
 
Kent OlsenData Warehouse Architect / DBACommented:

What Momi said.  :)   (All good advise.)

Also note a couple of other things.

  DB2 will also bypass an index if the filter/comparison calls a function.  This might seem odd, but it can be an effective tuning trick to get DB2 to use the correct index.

  The TRIM function removes blanks from the front and back of a string.  You probably want to do this before the query.  And since the strings are VARCHAR, there are no trailing strings so you can call LTRIM or TRIM equally.

  You're code always tests for blank/non-blank.  Are NULL values involved?  Any comparison of NULL to anything (even another NULL value) always returns FALSE.  It's easy to get tripped up there.

  Unless your data actually has blanks in the key fields, you can simplify the tests.  (Consider using NULL when data is missing instead of blank.)

  WHERE ((@EMPIDIN <> ' ' AND pren=@EMPIDIN) OR (@EMPIDIN = ' '))

If "pren" can not be blank, then the test shortens to:

  WHERE pren=@EMPIDIN


Oh.  And DB2 doesn't prefix variables with the '@' symbol.  :)

Good Luck,
Kent
0
 
Paula DiTalloIntegration developerAuthor Commented:
You will both be pleased to know that I have made the suggested corrections--and the sproc is now performing much better.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now