SET

SET

The SET command is used to set database connection properties before you actually perform any activity on the database. It is very important that you understand the full capabilities of this command.

 

 SET ------------- READONLY -------- *YES ------------------------->

                                     *NO

 

               >-- MAXROWS --------- n ---------------------------->

                                     *NOMAX

 

               >-- AUTOCOMMIT ------ *YES ------------------------->

                                     *NO

 

               >-- ISOLATION ------- *NONE ------------------------>

                                     *READCOMMIT

                                     *READUNCOMMITTED

                                     *REPEATABLEREAD

                                     *SERIALIZABLE

 

               >-- PARAMETER ------- *LIST ------------------------>

                                     *SQL

                                     *CALL

                                     *MAP

                                     *NONE

 

               >-- ONERROR --------- *DISCONNECT ------------------>

                                     *CONTINUE

                                     *ROLLBACK

 

               >-- ONWARNING ------- *STOP ------------------------>

                                     *CONTINUE

 

               >-- SQLSTATE -------- *NONE ------------------------|

                                     *ERROR

                                     *WARNING

                                     *ALL

 

Keywords

READONLY	This keyword indicates if the current database connection should allow read or write access. A database connection must first be established using the CONNECT command. This keyword then applies to the current database connection. By default when a database connection is established, read and write access is permitted (depending on restrictions imposed by the database). A value of *YES indicates any changes to the database are automatically committed after executing each statement. A value of *NO indicates that changes to the database are controlled by programmed transaction boundaries. Please refer to Comments / Warnings for this command for further information.
MAXROWS	This keyword specifies the maximum number of rows to be returned by a query.
AUTOCOMMIT	This keyword indicates if information should be automatically committed to the database. A database connection must first be established using the CONNECT command to use this keyword. This keyword then applies to the current database connection. By default when a connection is established it is in auto-commit mode. A value of *YES indicates any changes to the database are automatically committed after executing each statement. A value of *NO indicates that changes to the database are controlled by programmed transaction boundaries. Please refer to Comments / Warnings for this command for further information.
ISOLATION	This keyword indicates the level of transaction isolation to be applied to the current database. A database connection must be established using the CONNECT command to use this keyword. This keyword then applies to the current database connection. The default value is *NONE otherwise you can choose from these four levels of transaction defined by the ANSI/ISO SQL standard: *READUNCOMMITTED All uncommitted data is readable from any connection. This is the same as not having any isolation (*NONE). *READCOMMITTED This prevents dirty reads but does not prevent phantoms or non-repeatable reads. Using this isolation level, only data committed before the current transaction began will be available. Any dirty data or changes made by concurrent transactions will not be available. This level is obviously more restrictive than the *READUNCOMMITTED. *REPEATABLEREAD This prevents dirty and non-repeatable reads but does not prevent phantom rows. This means the probability of other transactions having to wait for this one are increased when compared to *READUNCOMMITTED and *READCOMMITTED This is more restrictive than *READCOMMITTED. *SERIALIZABLE *SERIALIZABLE provides the highest transaction isolation. When a transaction is isolated at the *SERIALIZABLE level, only data committed before the transaction began is available. Neither dirty data nor concurrent transaction changes committed during transaction execution are available. This level emulates serial transaction execution, as transactions will effectively be executed one after another rather than concurrently. This is more restrictive than *REPEATABLEREAD. In relation to these isolation levels there are three phenomena that you need to understand before you can determine the correct isolation level to apply to your application, namely: Dirty Reads - A transaction reads data written by an uncommitted transaction. If the second transaction is rolled back, the data read by the first transaction is then invalid because the rollback undoes the changes. The first transaction won't be aware that the data it has read has become invalid. Non-repeatable Reads - A transaction re-reads data it has previously read and finds that data has been modified by another committed transaction. Phantom Read - Phantom reads occur when new records added to the database are detectable by transactions that started prior to the insert. A transaction re-executes a query and returns a set of rows satisfying a search condition only to find that additional rows satisfying the condition have been inserted by another committed transaction. The ANSI/ISO SQL standard isolation levels and the corresponding behaviors are summarized in a table in Comments / Warnings following.
PARAMETER	This keyword can be used to prepare the SQL statement prior to running the EXECUTE or READ commands. The value you specify here depends very much on the type of activity you wish to perform on the database. There are four possible values for this keyword: *LIST - you may set this value if you intend to use the UPDATE keyword of the EXECUTE command. When taking this approach the values that you intend to add to, update, or delete from the database will be specified in a list that is included in the SERVICE_LIST of this command. It is recommended that you use this option if you are going to be updating (or inserting or deleting) more than a single record. (otherwise you would have to run the EXECUTE as many times as there are records, and this would place unnecessary load on your system and network). Please refer to the following Lists and Variables and the Examples for this command. *SQL - you may set this value if to intend to prepare the SQL statement in advance of the EXECUTE command. By doing so, you place your SQL statement into a working list that is passed in the SERVICE_LIST of this command. This feature is particularly useful if you are going to be using long SQL statements. This is particularly useful for RDML applications where the longest field length can only be 256 characters. Please refer to the following Lists and Variables and Examples for this command for further information. *CALL - you may set this value if you indend to use call procedures that  have IN, OUT and INOUT parameters. *MAP - if you are expecting a large result set to be returned from a query, then you should choose to use the READ command to receive the list of values. To use the READ command you need to set this keyword to *MAP to indicate that the SERVICE_LIST of this command will contain the column and field mapping information. Please refer to the following Lists and Variables and the Examples for this command for further information. *NONE - you may use this to clear the current map, list and SQL statements.
ONERROR	The keyword is used to indicate the action you want to take if an exception is encountered during the execution of the SQL statement. There are three possible options: *ROLLBACK - if an exception is encountered during the execution of the SQL statement, a rollback will be initiated. *CONTINUE - if an exception is encountered during the execution of the SQL statement, the processing will continue. *DISCONNECT - if an exception is encountered during the execution of the SQL statement, the database connection will be closed. The default value is *DISCONNECT.
ONWARNING	This keyword is used to indicate the action you wish to take if a warning message is issued during the execution of the SQL statement. There are two possible values: *CONTINUE - if a warning is encountered during the execution of the SQL statement, the processing will continue. *STOP -  - if a warning is encountered during the execution of the SQL statement, the command will be stopped. The default value is *STOP.
SQLSTATE	By default, if an SQLException occurs then the JSMSTS field is set to ERROR, and if an SQLWarning occurs then the JSMSTS field is set to WARNING. This keyword may be used to allow the actual SQLxxxxx code to be returned in place of these default values. There are four possible values: *ALL - this will ensure that an SQLException or an SQLWarning returns the SQLxxxxx status. *ERROR - this will ensure that an SQLException returns the SQLxxxxx status while an SQLWarning returns a WARNING status. *WARNING ERROR - this will ensure that an SQLWarning returns the SQLxxxxx status while an SQLException returns an ERROR status. *NONE - this will ensure that an SQLException returns ERROR status and an SQLWarning returns WARNING status. The default value is *NONE.

Comments / Warnings

JDBC drivers have vendor defined default settings for such areas as commitment control and exception handling. As an example, autocommit is normally switched on.

The ANSI/ISO SQL standard isolation levels and the corresponding behaviors are summarized in the following table.

Isolation Level Dirty Read Non-repeatable Read Phantom Read *READUNCOMMITTED Possible Possible Possible *READCOMMITTED Not possible Possible Possible *REPEATABLEREAD Not possible Not possible Possible *SERIALIZABLE Not possible Not possible Not possible

The SQLService SET command provides you with the capability to override these settings to meet your own requirements.

A detailed explanation of commitment control and error handling is beyond the scope of this documentation. If you are unfamiliar with isolation levels, commitment control and error handling then it is strongly recommended that you research these topics through other channels. These topics are quite generic so academic books or web sites might be a good place to start. You are also recommended to review any material that the database vendor provides on these topics.

It is strongly recommended that you test your commit and rollback logic extensively.

Lists and Variables

The PARAMETER keyword provides some very useful techniques for preparing your SQL statements. These can make you code simpler and in many cases help you produce more efficient applications.

If you choose to use the PARAMETER keyword with a value of *LIST, *SQL, *MAP or *CALL, then you will need to supply a working list with the SET command. The information that the working list contains will depend upon which of the three values you specify.

The following provides some information on what each value is used for as well as the list information that needs to be passed with the command:

*LIST - this value will allow you to pass a list of records to be inserted, updated, or deleted from the remote database. This list will be sent to the remote database with a prepared SQL statement and the prepared SQL statement will be run once for each record in the list. One of the benefits of this feature is a reduction in network and system resources. The alternative would be to run the SQL statement inside a loop and send it to the server for every separate record that needs to be updated. The working list provided with this value must provide one column for each field required by the SQL statement. The order of the columns is important as is explained in the EXECUTE command.

*SQL - this value will allow you to prepare an SQL statement prior to the EXECUTE command. This is particularly useful when your SQL statement is very large (for example, the longest field type for RDML applications is 256 characters, so when you use this in your JSM_COMMAND the available space to include your SQL statement is even less than this). When you set this value your large SQL statement may be placed into a working list. If need be, the statement may be placed over many rows of the working list. (Note: only one statement per SET command). The working list passed with this option must contain a single character field, which will contain the SQL statement.

*CALL this value will allow you to pass a list of IN, OUT and INOUT stored procedure call parameters. The working list argument needs to have three columns. These columns are Type, Direction and Value. Possible values for the Type column are *CHAR, *VARCHAR, *SMALLINT, *INTEGER, *FLOAT, *DOUBLE, *DECIMAL and *NUMERIC. Possible values for the Direction column are *IN, *OUT and *INOUT. The Value column is a string value.

*MAP - if you are expecting to receive a large result set from your query of the database, then you should opt to use the READ command to obtain the values in the result set. When you take this approach, a working list will need to be passed with this SET command that contains the column field mappings. The *MAP value indicates that your list is intended for this purpose. The working list that you pass will contain two columns that will describe the column/field mappings. That is, the first column will contain the name of the field used in this function to hold the value returned from the remote table, and the second column will hold the name of the related field as it is named in the remote table. There will be as many rows in this list as there are fields to be returned from the query. Refer to the READ command for further information on this feature and where to use it.

Please note that it is quite possible that you could use a number of these options in conjunction with each other. Refer to the following examples and the SQLService Examples to see how this works. For information on how to supply a working list to service commands from RDML or RDMLX applications, refer to 5.38.5 SQLService Examples.

Examples

The SET, EXECUTE, and READ commands of the SQLService service are very tightly related to each other. As such, you are recommended to review the more extensive examples in SQLService Examples.

RDML

 

* Define the field to hold the JSM command

DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)

 

Define the field and list that will hold the SQL command

DEFINE FIELD(#COLCMD) TYPE(*CHAR) LENGTH(100)

DEF_LIST NAME(#WRKCMD) FIELDS(#COLCMD) TYPE(*WORKING)

 

* SET the commitment control settings

CHANGE FIELD(#JSMCMD) TO('SET ISOLATION(*READCOMMITTED) AUTOCOMMIT(*NO) ONERROR(*ROLLBACK) ONWARNING(*CONTINUE)')

USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)

 

* Set up the SQL statement in the list then run the SET command

CHANGE FIELD(#COLCMD) TO('SELECT ID,NAME,AGE,SALARY')

ADD_ENTRY TO_LIST(#WRKCMD)

CHANGE FIELD(#COLCMD) TO('FROM TBLNAME')

ADD_ENTRY TO_LIST(#WRKCMD)

CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*SQL) SERVICE_LIST(COLCMD)')

USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKCMD)

 

RDMLX

 

* Define the JSM Command related fields

Define Field(#JSMSTS) Type(*Char) Length(020)

Define Field(#JSMMSG) Type(*Char) Length(256)

Define Field(#JSMCMD) Type(*Char) Length(256)

Define Field(#JSMHND) Type(*Char) Length(4)

 

* Define the field and the list to hold an SQL statement

Define Field(#COLCMD) Type(*Char) Length(020)

Def_List Name(#WRKCMD) Fields(#COLCMD) Type(*WORKING)

 

* Set up the commitment control settings

#JSMCMD := 'Set Isolation(*READCOMMITTED) AutoCommit(*NO) OnError(*ROLLBACK) OnWarning(*CONTINUE)'

Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)

 

* Build an SQL statement then run the Set command

#COLCMD := 'Select ID,NAME,AGE,SALARY'

Add_Entry To_List(#WRKCMD)

#COLCMD := 'From TBLNAME'

Add_Entry To_List(#WRKCMD)

#JSMCMD := 'Set Parameter(*SQL) Service_List(COLCMD)'

Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #MAPLST)