1

Need your advice on the below scenario.

An old COBOL program has SELECT SQL where it has multiple WHERE clauses. But these WHERE clauses don't act together. Depending on variable values a single WHERE condition is switched on and others are ignored.

SQL Query:

SELECT EMP_ID, EMP_SSN, EMP_PH, EMP_SALARY, EMP_EMAIL
INTO <:DCLGEN variable1, DCLGEN variable2...variable5>
FROM <Table name>
WHERE ((EMP_ID = <:HOST Var1> OR 'N' = :HOST switch1)
             AND
             (EMP_SSN = <:HOST Var2> OR 'N' = :HOST switch2)
             AND
             (EMP_PH = <:HOST Var3> OR 'N' = :HOST switch3)
             AND
             (EMP_EMAIL = <:HOST Var4> OR 'N' = :HOST switch4))

Now in the above query as you can see there are 4 WHERE conditions connected with AND.

In the program, when we want to run the first condition (EMP_ID) under WHERE clause we are passing SPACE to HOST switch1 and 'N' to rest all three. Similarly when we want to activate the 3rd condition (EMP_PH) then SPACE to HOST switch3 and 'N' to rest all three and so on.

Now we are facing a performance issue with this query. The reason is, though we have 4 indexes (all 4 columns in WHERE condition) but because of these HOST Switch variables DB2 is doing TS space instead of Index scan. So we need to rewrite the query and we can not use this kind of HOST Switch variables in the query.

  1. One solution is to remove all HOST switch variables and break the query into 4 diff queries. But that program will be lengthy and lots of code changes needed. So we are trying to avoid this.

Could someone please share suggestions on how we can achieve this within a single SQL query without using the above kind of Host Switch Variables?

NOTE- Due to business logic we need to use specific WHERE conditions depending on certain variable values.

I broke the query into 4 parts (each query with single WHERE condition) and removed all HOST switch variables. Depending on the business condition I am calling each SQL query. But that we are trying to avoid because the whole program needs to be changed to accomodate this new approach.

Subrata
  • 11
  • 1
  • Try `SELECT ... FROM (SELECT ... FROM ... WHERE COLx = :VARx AND 'N' <> :SWITCHx UNION ALL ...) T` if only one `'N' <> :SWITCHx` is true every time. Use dynamic queries otherwise. – Mark Barinstein Apr 25 '23 at 05:55
  • Hi Mark.. thanks for ur reply.. I will try that to see how it comes.. but using the Host Switch variable again inside the subquery won't affect the performance? I mean again DB2 will use TS scan right, instead of an Index scan ? As our objective to make DB2 use Index scan – Subrata Apr 25 '23 at 06:07
  • Sorry, but I didn't get the question. You have 4 different "Host Switch" variables, and the query proposed uses the same number of parameters with the same names. You may find, that the optimizer doesn't want to use the corresponding indexes even in this case which means, that you should use other optimization techniques. – Mark Barinstein Apr 25 '23 at 14:13
  • The query uses 4 different Table columns with 4 different values and 4 different Switches.. any single point we are activating only one Where condition using Host Switch. What I have found is because of those Switch variables DB2 optimizer is not opting for Index scan (not sure but maybe DB2 is assuming those Host Switches as the table's column) but the moment I remove those switches it is doing what we are expecting. – Subrata Apr 25 '23 at 16:07
  • The problem is that an access plan is built **before** the actual parameter values are known by default. You can try to bind you plan with the [REOPT](https://www.ibm.com/docs/en/db2-for-zos/11?topic=services-reopt-bind-option) **ALWAYS** bind option to check, if db2 is smart enough to eliminate the whole `AND (COLx = :PARx OR 'N' = 'N'`) completely. – Mark Barinstein Apr 25 '23 at 16:44
  • Hi Mark, thanks for your reply. For now I have used Dynamic query (had to redesign the complete program) to fix this issue. But the one you have mentioned above I need to check. I will check and will get back to you. – Subrata Apr 26 '23 at 10:48
  • If you are using Host-Switch to simulate dynamic query, then don't do that. Using an OR will probably make the plan not include the index. Instead, write different queries without OR and the program logic determine which one to execute based on passed switch values. This way, the index will be used if it is defined. – NoChance May 03 '23 at 23:36

1 Answers1

0

Please try this approach

SELECT *
FROM your_table
WHERE
    (variable = 'value1' AND condition1) OR
    (variable = 'value2' AND condition2) OR
    (variable = 'value3' AND condition3)

The variable represented by variable in this illustration is the one whose value determines which WHERE condition to use. Values 1, 2, and 3 represent the variables' potential values, while Conditions 1, 2, and 3 represent the particular requirements that must be met in each situation.

Without using host switch variables, you can combine multiple conditions and switch between them based on the value of the variable by using parentheses and the OR operator.

Gemunu R Wickremasinghe
  • 472
  • 4
  • 7