This chapter describes the use of the general-purpose programs supplied with DB2 RXSQL and the DB2 RXSQL online help that is invoked using the CMS HELP command. The programs and online help can be invoked from the command line, or from within your own programs.
There are two different kinds of online help provided with RXSQL. You can look up information about DB2 Server for VM and RXSQL error messages using the RXSQLHLP EXEC. The other way to find help information is by using the CMS HELP command. CMS HELP for RXSQL provides you with information on RXSQL-supplied programs, the syntax of the RXSQL requests and the RXSQL statement equivalents for each SQL statement.
Two of the RXSQL-supplied programs (RXSELECT and RXSQLEX) can be used to execute simple queries or perform simple data manipulations. These operations must be simple because you can not prepare an SQL statement for later execution, or use a cursor.
If you are writing an application that requires more complexity than these programs provide, see Chapter 7, Concepts. You can also refer to Appendix F, Sample Programs with Examples of RXSQL Requests which illustrates how to use RXSQL requests in REXX programs. Chapter 10, RXSQL Request Descriptions, provides you with a detailed description of each RXSQL request.
>>-EXEC RXCASE----+-String-+-----------------------------------><
'-Upper--'
|
The RXCASE program sets a global variable indicating the case setting to be used by the RXSELECT EXEC and the RXSQLEX EXEC. If RXCASE is Upper, the SQL statement is folded to upper case by the RXSELECT EXEC and the RXSQLEX EXEC.
If RXCASE is String, the SQL statement remains as originally typed.
The following two examples show two different forms of output from RXSELECT when the two options for RXCASE are used.
RXSELECT folds the SQL statement to uppercase.
RXCASE UPPER RXSELECT EMPNO, LASTNAME, JOB, SALARY FROM RXEMP WHERE JOB = 'manager'
The following output is displayed in the temporary file S$Q$L S$T$M$T.
+--------------------------------------------------------------------------------+ |SELECT EMPNO, LASTNAME, JOB, SALARY FROM RXEMP WHERE JOB='MANAGER' | |EMPNO LASTNAME JOB SALARY | |------ -------- -------- -------- | |002130 SAMS MANAGER 41700.00 | |003326 GOODBAR MANAGER 40360.00 | |ELO2121I ******** End-of-Data ********** | +--------------------------------------------------------------------------------+
RXSELECT leaves the SQL statement as typed. No data is selected from the table because the data is stored in uppercase and does not fit the search specification.
RXCASE STRING RXSELECT EMPNO, LASTNAME, JOB, SALARY FROM RXEMP WHERE JOB = 'manager'
The following output is displayed in the temporary file S$Q$L S$T$M$T.
+--------------------------------------------------------------------------------+ |Select EMPNO, LASTNAME, JOB, SALARY FROM RXEMP WHERE JOB='manager' | |EMPNO LASTNAME JOB SALARY | |----- -------- --- ------ | |ELO2121I ******** End-of-Data ********** | +--------------------------------------------------------------------------------+
>>-EXEC RXSELECT select_statement------------------------------>< |
| Note: | The FOR UPDATE OF clause is not supported by the RXSELECT EXEC. If you want to use a Positioned Update or Positioned Delete statement you must write a DB2 RXSQL application. |
See the DB2 Server for VSE & VM SQL Reference manual for more information about SELECT statements.
RXSELECT retrieves the first 100 rows from the database and displays them, using XEDIT, in a temporary file called S$Q$L S$T$M$T. If there are more than 100 rows in the result, type:
MORE
or,
MORE n
where n is the number of rows you want to display, from within XEDIT.
While you are in XEDIT, you can query the database again. Type:
RXSELECT new_select_statement
on the XEDIT command line, where new_select_statement is a new SQL SELECT statement. The new query overrides the previous query.
The RXSELECT EXEC folds the entire SELECT statement to uppercase unless you have issued the RXCASE EXEC with the String option.
When the RXSELECT EXEC returns data from a GRAPHIC, VARGRAPHIC, or LONG VARGRAPHIC column, and the DBCS option from the LASTING GLOBALV file for the DB2 Server for VM user is set to 'YES', RXSELECT concatenates the DBCS shift-out delimiter in front of the data and appends the DBCS shift-in character following the data.
Select the columns EMPNO, LASTNAME, WORKDEPT, JOB, and SALARY from the table RXEMP.
RXSELECT EMPNO, LASTNAME, WORKDEPT, JOB, SALARY FROM RXEMP
The following output is displayed in the temporary file S$Q$L S$T$M$T.
+--------------------------------------------------------------------------------+ |SELECT EMPNO, LASTNAME, WORKDEPT, JOB, SALARY FROM RXEMP | |EMPNO LASTNAME WORKDEPT JOB SALARY | |------ -------- -------- -------- -------- | |002130 SAMS B12 MANAGER 41700.00 | |002300 HEDGLEY B09 ANALYST 37900.00 | |001010 LOWRY D14 ANALYST 38240.00 | |000990 SCHENKER A07 OPERATOR 30190.00 | |002020 RAINEY D11 DESIGNER 32560.00 | |001840 CORDON B09 FILEREP 28090.00 | |002330 FABER A10 CLERK 27800.00 | |009236 DOBSON D08 WRITER 37600.00 | |002574 MERCIER A11 WRITER 33400.00 | |003567 SCHMIDT C04 CLERK 25790.00 | |002419 ATWALA A07 OPERATOR 37940.00 | |003326 GOODBAR B12 MANAGER 40360.00 | |003589 GOULD D07 WRITER 39250.00 | |ELO2121I ******** End-of-Data ********** | +--------------------------------------------------------------------------------+
>>-EXEC RXSQLANG----+-----------------------------+------------->
+-RXlang(language_identifier)-+
+-RXlang(langid)--------------+
'-RXlang( )-------------------'
>-----+-----------------------------+--------------------------><
+-SQlang(language_identifier)-+
+-SQlang(langid)--------------+
'-SQlang( )-------------------'
|
RXSQLANG provides a means to override the system defaults for the languages in which HELP information is displayed by DB2 RXSQL. During installation of both the database manager and DB2 RXSQL, a system default language is established. Using RXSQLANG you can specify your own default language for either or both DB2 RXSQL and DB2 Server for VM HELP information. The RXSQLHLP EXEC uses the default languages specified to display HELP information.
RXSQLANG verifies that the parameters you supply specify national languages that have been installed on your system. To determine which DB2 RXSQL national languages have been installed check the SQLDBA.ELOLANGUAGE table. This table is provided by DB2 RXSQL. To check which DB2 Server for VM languages have been installed check the SQLDBA.SYSLANGUAGE catalog table.
The national languages that you choose do not affect any other users or change the system defaults. The selection remains in effect until you issue RXSQLANG again with new parameters.
Set the default language for RXSQL HELP information to American English using the langid.
RXSQLANG RX(AMENG)
Set the default language for DB2 Server for VM HELP information to American English using the language_identifier.
RXSQLANG SQ(ENGLISH)
>>-EXEC RXSQLEX statement-------------------------------------->< |
DB2 RXSQL passes statement to the database manager where it is processed as an EXECUTE IMMEDIATE statement.
If no error occurs after the statement is processed, a COMMIT WORK statement is executed. If an error does occur a ROLLBACK statement is issued.
If the SQL statement is an INSERT, an UPDATE, or a DELETE, and it affects more than one row in the database, the following prompt appears:
Enter ROLLBACK or CANCEL to cancel the changes.
Cancel your changes by typing ROLLBACK or CANCEL, or accept your changes by pressing enter.
The SQL statement is folded to uppercase by DB2 RXSQL unless you select the String option for RXCASE.
Grant select authority on the RXEMP table to all users.
RXSQLEX GRANT SELECT ON RXEMP TO PUBLIC
>>-EXEC RXSQLHLP topic----------------------------------------->< |
ELOnnnns
where nnnn is the message identifier number and s is the error severity
RXSQLHLP can display both DB2 RXSQL and DB2 Server for VM HELP information if they are installed on your system.
The RXSQLHLP program uses the default language installed on your system, or the one you specified using the RXSQLANG EXEC. See RXSQLANG EXEC for more information.
After the results are displayed in an XEDIT file, you can view additional topics by typing:
SQLHELP newtopic
on the XEDIT command line, where newtopic is any HELP topic as described above.
Display the HELP information for the topic '+100'.
RXSQLHLP +100
The following output is displayed in the temporary file S$Q$L H$E$L$P.
+--------------------------------------------------------------------------------+ | | | | |=========================================================== | |DB2 RXSQL HELP +100 | |=========================================================== | |ELO2112I DB2 RXSQL HELP text is not available | |for topic '+100' for language S001. | |=========================================================== | |DB2 for VM HELP '+100' | |=========================================================== | |TOPIC NAME: +100 | | | |+100 There are no (or no more) rows that satisfy the | | condition. | | | |EXPLANATION: For a query that uses a cursor, the cursor is | |empty or all rows have been selected. For a query that does | |not use a cursor, no row was found that satisfied the WHERE | |condition. An INSERT via SELECT statement may return this | |SQLCODE if the SELECT statement does not retrieve any rows. | | | |SQLSTATE 02000. | | | |SYSTEM ACTION: Normal completion. | | | |USER RESPONSE: Take suitable action based on the SQLCODE | |descriptive text. | | | | | | | +--------------------------------------------------------------------------------+
>>-EXEC RXSQLOP operator_command------------------------------->< |
SHOW and COUNTER monitor the DB2 Server for VM system. See the DB2 Server for VSE & VM Operation manual for more information on operator commands.
Using XEDIT, RXSQLOP displays the results in the file S$Q$L O$P.
To process additional commands, type:
SQLOP newcommand
on the XEDIT command line, where newcommand is a DB2 Server for VM SHOW or COUNTER operator command.
This program will issue an error message if invoked from within a logical unit of work because the DB2 Server for VM database manager does not allow operator commands to be issued from within a logical unit of work.
Issue a DB2 Server for VM operator command.
RXSQLOP COUNTER *
Output similar to the following is displayed in the temporary file S$Q$L O$P.
+--------------------------------------------------------------------------------+ |=========================================================== | |SQLOP COUNTER * | |=========================================================== | |Counter values at DATE='05-30-91' TIME='11:18:01' | |Calls to RDS RDSCALL : 1219 | |Calls to DBSS DBSSCALL: 6862 | |LUWs started BEGINLUW: 268 | |LUWs rolled back ROLLBACK: 69 | |System checkpoints taken CHKPOINT: 1 | |Maximum locks exceeded LOCKLMT : 0 | |Lock escalations ESCALATE: 0 | |Waits for lock WAITLOCK: 0 | |Deadlocks detected DEADLCK : 0 | |Looks in page buffer LPAGBUFF: 13576 | |DBSPACE page reads PAGEREAD: 3117 | |DBSPACE page writes PAGWRITE: 20 | |Looks in directory buffer LDIRBUFF: 1092 | |Directory block reads DIRREAD : 414 | |Directory block writes DIRWRITE: 99 | |Log page reads LOGREAD : 3 | |Log page writes LOGWRITE: 13 | |Total DASD reads DASDREAD: 3534 | |Total DASD writes DASDWRIT: 132 | |Total DASD I/O DASDIO : 3666 | |ARI0065I Operator command processing is complete. | +--------------------------------------------------------------------------------+
>>-EXEC RXSQLVL----+--------+----------------------------------><
+-RELMOD-+
'-LIFO---'
|
The RXSQLVL program may be executed to determine the release level of DB2 RXSQL that is installed.
If you do not specify a parameter, a message is displayed on the console indicating the version, release, and modification level of the installed DB2 RXSQL system.
Determine the release level of RXSQL that is installed.
RXSQLVL
The following output is displayed on the screen.
|+--------------------------------------------------------------------------------+ ||ELO2102I *** DB2 RXSQL Version 7 Release 1 Modification 0 *** | |+--------------------------------------------------------------------------------+
This is not a program, but online HELP provided by DB2 RXSQL that can be displayed using the CMS HELP command. It is invoked from the command line like the DB2 RXSQL-supplied programs. The syntax is as follows:
>>-HELP RXSQL MENU--------------------------------------------->< |
HELP RXSQL MENU displays a HELP panel with all the DB2 RXSQL HELP topics. You can find out
The topics you can choose from are as follows:
Display the RXSQL help menu.
HELP RXSQL MENU
The menu will look like this:
+--------------------------------------------------------------------------------+
| DB2 RXSQL MENU Menu Help Information line 1 of 11 |
| (c) Copyrighted IBM Corporation |2000. |
| |
| You can select a file for viewing by placing the cursor under any |
| character of the file wanted and pressing the PF1 key. If you are |
| using a terminal that does not have a CURSOR or PF KEYS, you MUST TYPE |
| the COMPLETE HELP COMMAND with operands and options. For a |
| description of the operands and options, type HELP HELP. |
| |
| |
| RXCASE RXSELECT RXSQLEX RXSQLHLP RXSQLOP RXSQLVL RXUSAGE |
| RXCMDS RXSQLANG |
|* * * End of File * * * |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| PF1= Help 2= Top 3= Quit 4= Return 5= Clocate 6= ? |
| PF7= Backward 8= Forward 9= PFkeys 10= 11= 12= Cursor |
| |
|====> |
| Macro-read 1 file |
+--------------------------------------------------------------------------------+