The first section of this appendix covers some of the common errors and performance concerns that users have. The second section is a list of hints and tips provided to help you diagnose a problem.
This is not intended to be a comprehensive discussion on either performance or diagnosis, because RXSQL is merely an interface between REXX and the database manager. There are many factors that can contribute to the performance of your programs, or to errors in your programs. This discussion just highlights some of the more common pitfalls.
If you have a hint or a tip that you feel should be in this appendix please send it in to IBM on the Readers' Comment Form at the back of the manual. IBM is committed to providing you with quality documentation, and welcomes any suggestions you have. IBM may use or distribute whatever information you supply in any way it believes appropriate without incurring any obligation to you.
You can turn off the setting and resetting of a group of RXSQL variables to improve an application's performance, after you finish testing it. Before setting the LASTING GLOBALV variable (GLOBALV) described here, make sure that the application contains no programmed dependencies that will become incorrect with the changed settings of the RXSQL variables described in this section.
Use a GLOBALV variable to improve an application's performance. The GLOBALV variable, $$RXSQL32$$ in group SQL/DS,
is retrieved at the first invocation of a RXSQL command in an EXEC. If the variable exists and the first character of its value is Y or y, several RXSQL variables will not be set or reset at each RXSQL or EXECSQL invocation. Changing the value of $$RXSQL32$$ after the first RXSQL request in an EXEC has no effect until control is returned to CMS.
The GLOBALV variable can be set with the following CMS command:
GLOBALV SELECT SQL/DS SETP $$RXSQL32$$ Y
When the above command is issued before the first RXSQL request in the EXEC, the affected RXSQL variables are:
SQLWARN, and SQLERRD.n are not reset after each RXSQL request. If the request results in a database manager call, these variables are set with the corresponding values returned from the database manager.
Setting $$RXSQL32$$ as a GLOBALV variable continues to affect the execution of RXSQL applications because the variable remains in the logon session after the application terminates, unless it is explicitly cleared. To stop the effect of this variable on an EXEC, clear the GLOBALV variable using an appropriate GLOBALV CMS command before the first RXSQL request in the EXEC.
There are a few basic rules to follow when coding your programs for optimum performance.
As an application designer you often have to weigh the pros and cons of many design strategies and many factors including system considerations, database considerations, and object design. Subjects that will help you make these decisions include: Locking, normalization, DASD balancing, indexes, and access authorities. Both the DB2 Server for VSE & VM Application Programming manual and the DB2 Server for VSE & VM Database Administration manual will provide you with more information on these and other topics.
In the following discussion, all SQLCODES and return codes mentioned are for DB2 Server for VM.
Common Error conditions:
This DB2 Server for VM error condition means that the data type of a rexx-host-variable and the target data column are not compatible. It usually occurs when the database expects a character value, but DB2 RXSQL infers that the data type of the rexx-host-variable is numeric.
To ensure that DB2 RXSQL passes a character value to the database, use variable-qualifiers, or enclose the value of the rexx-host-variable in single quotation marks ('). DB2 RXSQL removes the leading and trailing quotation marks and passes the value to the database as a character string.
For example, typing:
charvar = "'"charvar"'"
before a CALL, EXECUTE, OPEN or PUT statement ensures that charvar will be interpreted as a character string.
If you are using variable-qualifiers, do not surround your input values with quotes.
When specifying an output-rexx-host-variable-list on FETCH or an input-rexx-host-variable-list on CALL, EXECUTE, OPEN or PUT, ensure that the rexx-host-variable-list is enclosed within quotation marks so that REXX does not resolve the variables before the statement is passed to DB2 RXSQL.
Don't rely on an implicit COMMIT to occur when an EXEC finishes processing. There are VM timing dependencies when the IUCV communications connection to the database is terminated. Either a COMMIT or a ROLLBACK can occur. Make sure you use the COMMIT statement explicitly to save the changes you made to the database before the EXEC finishes processing.
These DB2 RXSQL error messages tell you that a host variable value is too long. If the variable value does not appear to be too long, check that it does not have any leading or trailing blanks as part of its value. DB2 RXSQL does not strip leading or trailing blanks from the value in host variables when retrieved from REXX. For example:
package_id = ' USERAAA.MYPACK'
In this example, the authorization-name appears to DB2 RXSQL as if it is 10 characters long, but authorization-name is limited to 8 characters in length.
Enclose each DB2 RXSQL statement in single quotes to prevent CMS from folding it to uppercase.
If you use DB2 RXSQL in the XEDIT environment, as for example from XEDIT macros, XEDIT truncates all DB2 RXSQL commands that it finds (even if implicitly passed to CMS when IMPCMSCP is ON).
When you use Address COMMAND or the DB2 RXSQL subcommand environment, the DB2 RXSQL statement bypasses XEDIT handling of the command.
Address COMMAND 'RXSQL ... '
This is usually the result of
Miscellaneous Tips
Type:
SQLHX
A return code of -914 (user cancel) is issued from the database manager, and a ROLLBACK is issued for your current logical unit of work. You receive a return code of 8 from DB2 RXSQL, and an SQLCODE of -914 is returned to your EXEC.
The next DB2 RXSQL statement reconnects to the database manager and continues processing.
After a severe error has terminated your connection to the application server, any request other than a CONNECT request results in an SQLCODE of -900 and an SQLSTATE of 51018 in the SQLCA. You must issue a CONNECT request to reconnect to the application server when the error has been fixed.
On DB2 Server for VM releases prior to Version 3 Release 4, after a severe error had occurred on a previous request, the DB2 Server for VM system would accept only the CONNECT request, and would abend on any other request. To avoid abends from the DB2 Server for VM system, any RXSQL release prior to Version 3 Release 4 did the following after it detected a severe error:
Starting with RXSQL Version 3 Release 4, RXSQL submits SQL requests to the application server without checking for severe errors. If a severe error had occurred previously, the RXSQL user will get an SQLCODE of -900 and an SQLSTATE of 51018, issued by SQL/DS system Version 3 Release 4 or later.
| Note: | If RXSQL Version 3 Release 4 or later submits requests to an DB2 Server for VM system on a release prior to Version 3 Release 4, users might encounter abends after a severe error has occurred. |
If you are switching between application servers that have different versions of the RXSQL package, you must:
before you issue the CONNECT statement.