DB2 for z/OS – Using Named Parameter Markers

db2db2-zos

I have a query where I will be using the same value twice for one of the parameters. This query will be run under IBM Data Studio to see the results. Below is a sample query:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN 
    CONCAT(?, '-12.00.00.000000') AND 
    CONCAT(?, '-22.00.00.000000');

When run in Data Studio or any similar graphical query building environment I am prompted to enter values for both parameters, even though the values will always be the same for both.

Screenshot from IBM Data Studio showing prompts for both markers

I believe it is possible to used named parameter markers in other RDBMSes to indicate that the value should only be given once. Example:

SELECT *
FROM SYSADM.STATISTICAL_TABLE S
WHERE S.END_RECORD_TIMESTAMP BETWEEN 
    CONCAT(?:queryDate, '-12.00.00.000000') AND 
    CONCAT(?:queryDate, '-22.00.00.000000');

Is there a way to use named markers in DB2 for z/OS to indicate that the value will be the same so graphical tools only prompt for one input? On queries that have 20+ inputs, this can be time consuming.

Is there a better way that I should be doing this?

Best Answer

First of all, the syntax for a named parameter marker is CONCAT(:queryDate, '-12.00.00.000000'), that is, it begins with a colon, not a question mark.

Processing of parameter markers is performed on the client side, not on the server, and it's irrelevant if your server happens to be DB2 for z/OS or not. The IBM Data Server Client (the common driver for IBM database servers) supports named parameter markers.

Whether Data Studio SQL editor can properly parse named parameter markers and pass them to the driver is a different question, to which I'm afraid I don't know the answer.

Related Question