Build SQL Statement with host variables

Let’s review this scenario. You just implemented a new COBOL program into production. But some users are not getting the data they expected. You finally realize an SQL statement you are running is not returning any rows. You’re sure the statement is using the correct host variables, but the query is getting a +100 SQL return code. The query is good; it must be a problem with Db2. You need a way to verify what host variables are being passed to the query.

This is a key feature of IBM db2 Query Monitor. It will display the SQL statement and the variables being passed to Db2. After you verify the variables, you soon realize your program had changed the variable and query was running with the incorrect value.

QM does a good job of capturing the query and the variables; either from TSO or from the Web Client (CAE).

My suggestion is to take it one step further. Let’s say you have a query with many variables (I’ve seen queries well over 100 and up to 500). Using the TSO interface, you can see the values. See attached file for screen shots...

RS01	 08/28	 09:40:08	  ------------------------------	 Display SQL Statement Text	

Option  ===>	 __________________________________________________________________



 DB2 SSID:	 PCA1	   Plan:	 ADBD10  	   DBRM:	 ADB2REM 	  Coll:	 ADBLD10           	

                  Cursor:	 C_VD              	  Section:	      54	

-------------------------------------------------------------------------------



     DECLARE C_VD CURSOR

     WITH HOLD FOR SELECT T1 . *

     FROM SYSVIEWDEP T1

     WHERE BCREATOR = : H AND BNAME = : H AND BTYPE = : H

=================================================================================================

RS01	 08/28	 09:41:07	  ---------------------------------	 Input Host Variables

Option  ===>	 ______________________________________________________________



 DB2 SSID:	 PCA1	   Plan:	 ADBD10  	   DBRM:	 ADB2REM 	  Coll:	 ADBLD10

                  Cursor:	 C_VD              	  Section:	      54	

---------------------------------------------------------------------------



Number  Type                Null  Length  Data

     1  VARCHAR              N         6  TS5941

                                          EEFFFF

                                          325941

     2  VARCHAR              N         3  EMP

                                          CDD

                                          547

     3  CHARACTER            N         1  T

                                          E

                                          3

This is a very simple example. You can see the 3 host variables flow into the SQL statement….

WHERE BCREATOR = : H AND BNAME = : H AND BTYPE = : H

What if you had 36 variables? You must count each one out to figure out what host variable lines up with the SQL statement. What if you wanted to run the exact query in Spufi or QMF? You need to export the query and manually add each host variable into the predicate. If you have a 100 variables, it would take some time.

Wouldn’t it be nice if you could click a button that builds the SQL statement and includes the host variables? You can export the query and have the complete SQL statement? You can then explain it with the host variables or run it in Spufi or QMF. If you had 36 variables and you needed a specific value for a column, generating the complete SQL statement would show you exactly what field lines up in the predicate. This would be great for both TSO and the Web Client (CAE).

Not only would this be good for SELECT statements, but DML as well:

INSERT INTO SYSAUTO . OBJTEMP ( SEQNO , TYPE , DBNAME , SPACENAME , PARTITION , WILDCARD , PROCESS_INDEXES , PROCESS_RI ,

PROCESS_CLONES , PROCESS_UTIL , EXCLUDE , CREATOR , PART , USERID , UPDATE_TIMESTAMP ) VALUES ( : H , : H , : H , : H , : H

, : H , : H , : H , : H , : H , : H , : H , : H , : H , : H )

Thanks, Tom