FILE COMPARISON
Produced: 10/26/2009 9:47:30 AM
   
Mode:  All Lines  
   
Left file: C:\pQ_workspaces\release_docs\com.ibm.datatools.javatool.runtime.overview.doc\lit_sub.2.2.txt  
Right file: C:\pQ_workspaces\release_docs\com.ibm.datatools.javatool.runtime.overview.doc\lit_sub.2.2.0.1.txt  
1 Developing and optimizing data applications and routines > Developing high-performance Java data access applications with pureQuery > Running SQL statements statically > Running SQL statements statically from non-pureQuery API applications > Properties that determine the actions of client optimization > Descriptions of properties for client optimization = 1 Developing and optimizing data applications and routines > Developing high-performance Java data access applications with pureQuery > Running SQL statements statically > Running SQL statements statically from non-pureQuery API applications > Properties that determine the actions of client optimization > Descriptions of properties for client optimization
2 IBM Optim Development Studio, Version 2.2   2 IBM Optim Development Studio, Version 2.2
3 sqlLiteralSubstitution property for client optimization   3 sqlLiteralSubstitution property for client optimization
    <> 4  
4 If your application runs many SQL statements that share the same syntax and differ only in the literal values that they contain, you can capture and consolidate those statements by having pureQuery substitute parameter markers for the literal values. Your application can then run those SQL statements statically and benefit from the security and speed of static SQL. = 5 If your application runs many SQL statements that share the same syntax and differ only in the literal values that they contain, you can capture and consolidate those statements by having pureQuery substitute parameter markers for the literal values. Your application can then run those SQL statements statically and benefit from the security and speed of static SQL.
5     6  
6 Your application might, for example, generate INSERT statements that are identical syntactically, but that include literal values that are provided by users in the fields of a form.   7 Your application might, for example, generate INSERT statements that are identical syntactically, but that include literal values that are provided by users in the fields of a form.
7     8  
8 Normally, you would not be able to run these statements statically because they are generated at runtime. However, pureQuery's client optimization feature can capture such statements by replacing the literal values with parameter markers and recognizing when the syntax of an SQL statement is identical to that of a statement that it already captured. You can therefore bind these SQL statements into DB2® packages. <> 9 Normally, you would not be able to run these statements statically because they are generated at run time. However, pureQuery's client optimization feature can capture such statements by replacing the literal values with parameter markers and recognizing when the syntax of an SQL statement is identical to that of a statement that it already captured. You can therefore bind these SQL statements into DB2® packages.
9   = 10  
10 When you run your application with the client optimization property executionMode set to STATIC, pureQuery can match SQL statements to the parameterized SQL statements that it captured. Matching statements run statically.   11 When you run your application with the client optimization property executionMode set to STATIC, pureQuery can match SQL statements to the parameterized SQL statements that it captured. Matching statements run statically.
    <> 12 Sections in this topic
      13  
      14     * Example
      15     * Setting the values of the sqlLiteralSubstitution property
      16     * Implicit casting
      17     * Restrictions
      18  
11 Example = 19 Example
12     20  
13 When you set the client optimization properties before capturing SQL statements, set the property sqlLiteralSubstitution to ENABLE, as in this example:   21 When you set the client optimization properties before capturing SQL statements, set the property sqlLiteralSubstitution to ENABLE, as in this example:
14     22  
15 pdqProperties=captureMode (ON), executionMode (DYNAMIC),   23 pdqProperties=captureMode (ON), executionMode (DYNAMIC),
16 pureQueryXml    (C:/workspace/capture_file.pdqxml),   24 pureQueryXml    (C:/workspace/capture_file.pdqxml),
17 sqlLiteralSubstitution (ENABLE)   25 sqlLiteralSubstitution (ENABLE)
18     26  
19 When your application runs an INSERT statement like the first statement in the next example, pureQuery captures it in the form of the second statement:   27 When your application runs an INSERT statement like the first statement in the next example, pureQuery captures it in the form of the second statement:
20     28  
21 insert into autoGeneratedKy(name, salary, deptno) values('Margaret Dong', 60000, 12 ) <> 29 insert into EMPLOYEES/*inserting new row into EMPLOYEES table*/values('Dong','Margaret',NULL,60000,12)
22   = 30  
23 insert into autoGeneratedKy(name, salary, deptno) values(?,?,?) <> 31 insert into EMPLOYEES values(?,?,?,?,?)
24   = 32  
25 After you finishing capturing statements, you can run the Configure utility on capture_file.pdqxml, and then run the StaticBinder utility to bind the statements into DB2 packages. <> 33 After you finish capturing statements, you can run the Configure utility on capture_file.pdqxml, and then run the StaticBinder utility to bind the statements into DB2 packages.
26   = 34  
27 When you run the application with executionMode set to STATIC and the application issues the statement insert into autoGeneratedKy(name, salary, deptno) values('ABC', 50000, 11 ), pureQuery matches the statement to the parameterized version in the pureQueryXML file and runs it statically. <> 35 When you run the application with executionMode set to STATIC and the application issues the statement insert into EMPLOYEES values('Hinkis','Tali','R',68000,20)), pureQuery matches the statement to the parameterized version in the pureQueryXML file and runs it statically.
28   = 36  
29 However, you can tell pureQuery not to match SQL statements with parameterized statements in capture_file.pdqxml. Before you run the application, when you set executionMode to STATIC you can also set sqlLiteralSubstitution to DISABLE. When you run the application and it issues the INSERT statement in the previous paragraph, whether pureQuery runs the statement depends on the values of the client optimization properties capturedOnly and allowDynamicSQL.   37 However, you can tell pureQuery not to match SQL statements with parameterized statements in capture_file.pdqxml. Before you run the application, when you set executionMode to STATIC you can also set sqlLiteralSubstitution to DISABLE. When you run the application and it issues the INSERT statement in the previous paragraph, whether pureQuery runs the statement depends on the values of the client optimization properties capturedOnly and allowDynamicSQL.
30     38  
31     * If the value of capturedOnly is TRUE, pureQuery throws an SQLException and does not run the statement because the statement is not in the pureQueryXML file.   39     * If the value of capturedOnly is TRUE, pureQuery throws an SQLException and does not run the statement because the statement is not in the pureQueryXML file.
32     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is TRUE, pureQuery tries to run the statement dynamically.   40     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is TRUE, pureQuery tries to run the statement dynamically.
33     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is FALSE, pureQuery issues an error message and does not run the statement. <> 41     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is FALSE, pureQuery throws an SQLException and does not run the statement.
34   = 42  
35 Setting the values of the sqlLiteralSubstitution property   43 Setting the values of the sqlLiteralSubstitution property
36     44  
37 The following table lists the three values of the sqlLiteralSubstitution property and their effects. The table also shows what happens when you do not set a value for this property.   45 The following table lists the three values of the sqlLiteralSubstitution property and their effects. The table also shows what happens when you do not set a value for this property.
38 Table 1. The effects of setting or not setting a value for the sqlLiteralSubstitution propertyValue     Effects when you capture statements for the first time  Effects when you incrementally capture  Effects when you run the application in either STATIC or DYNAMIC mode   46 Table 1. The effects of setting or not setting a value for the sqlLiteralSubstitution propertyValue     Effects when you capture statements for the first time  Effects when you incrementally capture  Effects when you run the application in either STATIC or DYNAMIC mode
39 ENABLE  pureQuery replaces literal values with parameter markers.   47 ENABLE  pureQuery replaces literal values with parameter markers.
40     48  
41 pureQuery also captures the stack trace for the original SQL statements. If you want pureQuery to capture stack traces for all of the original SQL statements, you might need to increase the value of the property maxStackTracesCaptured.   49 pureQuery also captures the stack trace for the original SQL statements. If you want pureQuery to capture stack traces for all of the original SQL statements, you might need to increase the value of the property maxStackTracesCaptured.
42     50  
43 pureQuery does not count the original SQL statements against the value of the property maxNonParmSQL.   51 pureQuery does not count the original SQL statements against the value of the property maxNonParmSQL.
44     52  
45 If pureQuery cannot parameterize an SQL statement, it logs a warning message.   The effects are the same as when you capture SQL statements for the first time.         pureQuery tries to match SQL statements to parameterized SQL statements in a pureQueryXML file. <> 53 If pureQuery cannot parameterize an SQL statement, it logs a warning message.   The effects are the same as when you capture SQL statements for the first time.         pureQuery tries to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file.
46 DISABLE         pureQuery does not replace literal values with parameter markers.       pureQuery does not replace literal values with parameter markers.       pureQuery does not try to match SQL statements to parameterized SQL statements in a pureQueryXML file.   54 DISABLE         pureQuery does not replace literal values with parameter markers.       pureQuery does not replace literal values with parameter markers.       pureQuery does not try to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file.
47        
48     * If the value of capturedOnly is TRUE, pureQuery throws an SQLException and does not run the statement because the statement is not in the pureQueryXML file.      
49     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is TRUE, pureQuery tries to run the statement.      
50     * If the value of capturedOnly is FALSE and the value of allowDynamicSQL is FALSE, pureQuery throws an SQLException and does not run the statement.      
51        
52 NOT_SET (or not specified)      pureQuery does not replace literal values with parameter markers.       If the value was ENABLE when pureQuery last captured statements in the specified pureQueryXML file, pureQuery replaces literal values with parameter markers. = 55 NOT_SET (or not specified)      pureQuery does not replace literal values with parameter markers.       If the value was ENABLE when pureQuery last captured statements in the specified pureQueryXML file, pureQuery replaces literal values with parameter markers.
53     56  
54 If the value was DISABLE, pureQuery does not replace literal values with parameter markers.     If the value was ENABLE when pureQuery last captured statements in the specified pureQueryXML file, pureQuery replaces literal values with parameter markers. <> 57 If the value was DISABLE, pureQuery does not replace literal values with parameter markers.     If the value was ENABLE when pureQuery last captured statements in the specified pureQueryXML file, pureQuery tries to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file.
55   = 58  
56 If the value was DISABLE, pureQuery does not replace literal values with parameter markers. <> 59 If the value was DISABLE, pureQuery does not try to match SQL statements that the application attempts to run to parameterized SQL statements in a pureQueryXML file.
      60 Casting
      61  
      62 Beginning with version 2.2.0.1, pureQuery supports two types of casting when replacing literal values with parameter markers.
      63  
      64 CAST function
      65     When an SQL statement uses the CAST function to cast a literal value to a data type, pureQuery replaces that value with a parameter marker.
      66  
      67     For example, if a statement contains the clause WHERE mySMALLINTcolumn=CAST(32767999 as INTEGER), the value 32767999 becomes a parameter marker when the statement is captured.
      68  
      69     To take another example, if you use the CAST function CAST(6 as DECIMAL(3,3)), only the 6 becomes a parameter marker when the statement is captured.
      70  
      71     User-defined external scalar functions that are named CAST are not supported.
      72 Implicit casting
      73     pureQuery can perform implicit casting of literal values. For example, pureQuery can recognize that the literal value in the statement select DEPTNAME from DEPARTMENT WHERE DEPTNO = '1' is a numeric type. When you run your application, pureQuery can replace the string value '1' with the numeric value 1 before the application runs the statement against a database.
      74  
      75     Because the conversion takes place on database clients, you can run such SQL statements on database management systems that do not support implicit casting.
      76  
      77     pureQuery supports the following types of implicit casting:
      78  
      79     Converting strings to numeric types
      80  
      81         select DEPTNAME from DEPARTMENT WHERE DEPTNO = '1'
      82         select DEPTNAME from DEPARTMENT WHERE DEPTNO BETWEEN '20' AND '30'
      83  
      84     Converting numeric types to strings
      85  
      86         select DEPTNO from DEPARTMENT WHERE DEPTNAME = 123456
      87  
      88     Converting strings to datetime constants
      89  
      90         INSERT INTO DEPARTMENT VALUES(1,5,56,'000010','A00','aa','2008-09-09')
      91  
57 Restrictions = 92 Restrictions
58     93  
59     * In most cases, the data type of a literal value must match the data type of the corresponding column in the data source. If the data type does not match when you try to capture the statement, pureQuery might not be able to replace the literal value with a parameter marker when it adds the statement to a pureQueryXML file. If the data type does not match when you run an application in STATIC mode, pureQuery runs the statement that the application passes to it; however, the data source might throw an exception.   94     * In most cases, the data type of a literal value must match the data type of the corresponding column in the data source. If the data type does not match when you try to capture the statement, pureQuery might not be able to replace the literal value with a parameter marker when it adds the statement to a pureQueryXML file. If the data type does not match when you run an application in STATIC mode, pureQuery runs the statement that the application passes to it; however, the data source might throw an exception.
60       There are two cases where this restriction does not apply:   95       There are two cases where this restriction does not apply:
61           o You can assign a character string to a parameter of the type DATETIME.   96           o You can assign a character string to a parameter of the type DATETIME.
62           o You can assign a numeric literal value to a parameter of a different numeric type if the assignment does not result in a loss of precision.   97           o You can assign a numeric literal value to a parameter of a different numeric type if the assignment does not result in a loss of precision.
63     * If the DBMS is unable to determine the data type to associate with a parameter marker, pureQuery does not replace the literal value with a parameter marker. <> 98     * If the DBMS is unable to determine the data type to associate with a parameter marker, pureQuery does not substitute parameter markers for any of the literal values in the statement.
64     * pureQuery does not support replacing literal values with parameter markers in the following places: = 99     * pureQuery does not support replacing literal values with parameter markers in the following places:
    -+ 100           o CALL statements
      101           o SQL DDL statements
65           o SQL statements that already contain parameter markers, whether those markers are named or unnamed; for example, pureQuery does not replace the literal values in this statement: = 102           o SQL statements that already contain parameter markers, whether those markers are named or unnamed; for example, pureQuery does not replace the literal values in this statement:
66     103  
67             insert into sales values (5, ?, 9);   104             insert into sales values (5, ?, 9);
68     105  
69           o All constants of the form prefix'string' or prefix 'string'. Such constants include: <>    
70                 + Datetime constants: DATE 'string', TIME 'string', or TIMESTAMP 'string'. For example, pureQuery would not support this literal value: DATE '10/12/2008'      
71                 + Binary-string constants, which are of the form BX'string' or X'string'.   106           o Binary-string constants, which are of the form BX'string' or X'string'.
72                 + Graphic string constants, which are of the form UX'string' or GX'string'.   107           o Graphic string constants, which are of the form UX'string' or GX'string'.
73           o SQL statements that are used in batch operations = 108           o SQL statements that are used in batch operations
74           o SQL DDL statements <> 109           o User-defined external scalar functions that have the name CAST
75           o CALL statements      
76           o Values with the XML data type = 110           o Values with the XML data type
77           o XQuery expressions   111           o XQuery expressions
78     * An SQLException object that pureQuery returns because of a problem with an SQL statement might differ from an SQLException object that JDBC would return for the same problem in the same statement. Therefore, applications cannot depend on SQLException objects being the same with client optimization as they would be without client optimization.   112     * An SQLException object that pureQuery returns because of a problem with an SQL statement might differ from an SQLException object that JDBC would return for the same problem in the same statement. Therefore, applications cannot depend on SQLException objects being the same with client optimization as they would be without client optimization.
79     * Applications must accommodate changes in the data type of an SQL expression. However, numeric expressions will remain Numeric objects (though the precision and scale might change), and string expressions will remain String objects.   113     * Applications must accommodate changes in the data type of an SQL expression. However, numeric expressions will remain Numeric objects (though the precision and scale might change), and string expressions will remain String objects.
80     114  
    -+ 115 Parent topic: Descriptions of properties for client optimization
81 Related reference = 116 Related reference
82 allowDynamicSQL property for client optimization   117 allowDynamicSQL property for client optimization
83 capturedOnly property for client optimization   118 capturedOnly property for client optimization
84 captureMode property for client optimization   119 captureMode property for client optimization
85 captureStatementBatchSQL property for client optimization   120 captureStatementBatchSQL property for client optimization
86 enableDynamicSQLReplacement property for client optimization   121 enableDynamicSQLReplacement property for client optimization
87 executionMode property for client optimization   122 executionMode property for client optimization
88 maxNonParmSQL property for client optimization   123 maxNonParmSQL property for client optimization
89 maxStackTracesCaptured property for client optimization   124 maxStackTracesCaptured property for client optimization
90 outputPureQueryXml property for client optimization   125 outputPureQueryXml property for client optimization
91 packagePrefixExclusions property for client optimization   126 packagePrefixExclusions property for client optimization
92 pureQueryXML property for client optimization   127 pureQueryXML property for client optimization
93 stackTraceDepth property for client optimization   128 stackTraceDepth property for client optimization
94     129  
95 Feedback   130 Feedback
96     131