EXPLAIN Facility and Predictive Analysis › Predicting Database Performance › Methods to Generate Statements

Methods to Generate Statements

Just as data can be generated, so can statements. You can write SQL statements that generate statements. For example, you might need to generate single select statements against the EMP table to test a possible application process or scenario. You can write a statement such as the following to generate those statements:

SELECT 'SELECT LASTNAME, FIRSTNME ' CONCAT
       'FROM EMP WHERE EMPNO = ''' CONCAT
       EMPNO CONCAT ''';'
FROM   SUSAN.EMP
WHERE  WORKDEPT IN ('C01', 'E11')
AND    RAND() < .33

The previously noted query generates SELECT statements for approximately 33 percent of the employees in departments C01 and E01. The output appears similar to the following:

1                                                        
----------------------------------------------------------
SELECT LASTNAME, FIRSTNME FROM EMP WHERE EMPNO = '000030';
SELECT LASTNAME, FIRSTNME FROM EMP WHERE EMPNO = '000130';
SELECT LASTNAME, FIRSTNME FROM EMP WHERE EMPNO = '200310';

You can also use recursive SQL statements to generate statements. The following statement was used during the testing of high performance INSERTs to an account history table. The following statement generated 50,000 random insert statements:

WITH GENDATA (ACCT_ID, HIST_EFF_DTE, ORDERVAL) AS
(VALUES (CAST(2 AS DEC(11,0)), CAST('2003-02-01' AS DATE), CAST(1 AS FLOAT))
UNION ALL
SELECT ACCT_ID + 5, HIST_EFF_DTE, RAND()
FROM   GENDATA
WHERE  ACCT_ID < 249997)
SELECT 'INSERT INTO YLA.ACCT_HIST (ACCT_ID, HIST_EFF_DTE)' CONCAT
' VALUES(' CONCAT CHAR(ACCT_ID) CONCAT ',' CONCAT ''''
CONCAT CHAR(HIST_EFF_DTE,ISO) CONCAT '''' CONCAT ');'
 FROM GENDATA ORDER BY ORDERVAL;