IBM SC34-5764-01 286

ADDRESS EXECSQL

"statement"

SQL can exist on more than one line. Each part of the statement is enclosed in quotes and a comma

delimits additional statement text as follows:

ADDRESS EXECSQL

"SQL text",

"additional text",

"final text"

The following rules apply to embedded SQL:

vYou can pass the following SQL directly to the EXECSQLcommand environment:

ALTER

CREATE

COMMENT ON

DELETE

DROP

EXPLAIN

GRANT

INSERT

LABEL ON

LOCK

REVOKE

SELECT

SET CURRENT SQLID

UPDATE.

vYou cannot use the following SQL:

BEGIN DECLARE SECTION

COMMIT

CONNECT

DECLARE CURSOR

DECLARE STATEMENT

DECLARE TABLE

DESCRIBE

END DECLARE SECTION

EXECUTE

EXECUTE IMMEDIATE

FETCH

INCLUDE

OPEN

PREPARE

ROLLBACK

SET CURRENT PACKAGESET

SET HOST VARIABLE

WHENEVER.

DB2 Interface

286 CICS TS for VSE/ESA: REXX Guide

Contents

Main Page Page Page Contents Part 1. User's Guide..............................1 iv Part 2. Reference ..............................101 vi Page viii Page x Page xii Page xiv Part 3. Appendixes .............................377 xvi Page Page Preface What this book is about Who this book is for What you need to know to understand this book Prerequisites Page Page Page Chapter 1. Introduction Components of REXX 4 Chapter 2. Writing and Running a REXX Program What you need to run a REXX Program? What is a REXX Program? Syntax of REXX Instructions The Format of REXX Instructions 6 The Letter Case of REXX Instructions Using Quotation Marks in an Instruction Ending an instruction Continuing an instruction Continuing a literal string without adding a space 8 Types of REXX Clauses Keyword Instructions Assignment Label Null Clause Programs Using Double-Byte Character Set Names Typing in a Program Running a Program Interpreting Error Messages 12 How to Prevent Translation to Uppercase Characters within a program Characters Input to a program Exercises - Running and Modifying the Example Programs 14 Passing Information to a program Getting Information from the Program Stack or Terminal Input Device Specifying Values When Calling a program Specifying Too Few Values Specifying Too Many Values Preventing Translation of Input to Uppercase Exercises - Using the ARG Instruction 16 Passing Arguments Using the CALL Instruction or a REXX Function Call Page Chapter 3. Using Variables and Expressions Program Variables Using Variables Variable Names Variable Values Exercises - Identifying Valid Variable Names 20 Using Expressions Arithmetic Operators Division Order of Evaluation 22 Using Arithmetic Expressions ExercisesCalculating Arithmetic Expressions Comparison Operators The Strictly Equal and Equal Operators Using Comparison Expressions 24 Exercises - Using Comparison Expressions Logical (Boolean) Operators Using Logical Expressions 26 Exercises - Using Logical Expressions Concatenation Operators Using Concatenation Operators Priority of Operators 28 Exercises - Priority of Operators Tracing Expressions with the TRACE Instruction Tracing Operations Tracing Results Exercises - Using the TRACE Instruction 30 When you run this program, it produces: Page Chapter 4. Controlling the Flow within a program Conditional, Looping, and Interrupt Instructions Using Conditional Instructions IF...THEN...ELSE Instructions instruction instruction Nested IF...THEN...ELSE Instructions 34 Page Page SELECT WHEN...OTHERWISE...END Instruction Exercises - Using SELECT WHEN...OTHERWISE...END 38 Using Looping Instructions Repetitive Loops Infinite Loops DO FOREVER Loops 40 LEAVE Instruction This example results in a list of numbers from 1 to 10 with the exception of number 8. ITERATE Instruction Exercises - Using Loops Conditional Loops 42 DO WHILE Loops instruction(s) DO WHILE END Exercise - Using a DO WHILE Loop DO UNTIL Loops DO UNTIL loops in a flowchart appear as follows: 44 DO UNTIL instruction(s) Exercise - Using a DO UNTIL Loop ANSWER Combining Types of Loops 46 The result of this example is as follows: Nested DO Loops Exercises - Combining Loops Using Interrupt Instructions EXIT Instruction 48 CALL and RETURN Instructions SIGNAL Instruction Chapter 5. Using Functions What is a Function? Example of a Function Built-In Functions 52 Arithmetic Functions Comparison Functions Conversion Functions Formatting Functions String Manipulating Functions 54 Miscellaneous Functions Testing Input with Built-In Functions Exercise - Writing a program with Built-In Functions 56 Chapter 6. Writing Subroutines and Functions What are Subroutines and Functions? When to Write Subroutines Rather Than Functions Writing Subroutines and Functions Page When to Use Internal Versus External Subroutines or Functions Passing Information Page 62 The next two examples are the same, except they use functions rather than subroutines. The next example is the same except PROCEDURE EXPOSE is in a function instead of a subroutine. For more information about the PROCEDURE instruction, see section PROCEDURE on page 155. Passing Information by Using Arguments 64 Page Receiving Information from a Subroutine or Function Exercise - Writing an Internal and an External Subroutine Exercise - Writing a Function 68 Page Subroutines and FunctionsSimilarities and Differences The following tables highlight similarities and differences between subroutines and functions: 70 Chapter 7. Manipulating Data Using Compound Variables and Stems What Is a Compound Variable? Using Stems Exercises - Using Compound Variables and Stems 72 Parsing Data Parsing Instructions PULL Instruction ARG Instruction PARSE VALUE ... WITH Instruction 74 PARSE VAR Instruction More about Parsing into Words Parsing with Patterns String Variable 76 Number Page Parsing Multiple Strings as Arguments Exercise - Practice with Parsing 78 Page 80 Chapter 8. Using Commands from a program Types of Commands Using Quotations Marks in Commands Using Variables in Commands Calling Another REXX Program as a Command Calling Another Program with the EXEC Command 82 Issuing Commands from a program What is a Host Command Environment? How Is a Command Passed to the Host Environment? Changing the Host Command Environment Determining the Active Host Command Environment Using Commands from a Program Page Chapter 9. Diagnosing Problems within a program Debugging Programs Tracing Commands with the TRACE Instruction TRACE C TRACE E Tracing with the Interactive Debug Facility 86 Starting Interactive Debug Options within Interactive Debug Diagnosing Problems within a Program Saving Interactive TRACE Output Diagnosing Problems within a Program Page Chapter 10. Programming Style and Techniques Consider the Data Test Yourself... Answer: Happy Hour 90 Page Good job! Now, take a while to put your new skills into action, or continue reading. Designing a Program 92 Methods for Designing Loops The Conclusion What Do We Have So Far? Stepwise Refinement: An Example 94 Reconsider the Data Correcting Your Program Modifying Your Program Tracing Your Program Coding Style 96 Page 98 Page Page Page Page Chapter 11. Introduction Who Should Read This Reference How to Use This Reference Overview of Product Features SAA Level 2 REXX Language Support Under REXX/CICS 104 Support for the Interpretive Execution of REXX Execs CICS-Based Text Editor for REXX Execs and Data Dynamic Support for EXEC CICS Commands REXX Interface to CEDA and CEMT Transaction Programs High-level Client/Server Support Support for Commands Written in REXX Command Definition of REXX Commands SQL Interface Programming Considerations 106 Embedding SQL statments How to Read the Syntax Diagrams Page Page Chapter 12. REXX General Concepts Structure and General Syntax Characters Comments 110 Tokens 112 Page Implied Semicolons 114 Continuations Expressions and Operators Expressions Operators String Concatenation Arithmetic 116 Comparison Logical (Boolean) Parentheses and Operator Precedence 118 Page Clauses and Instructions Null Clauses 120 Labels Instructions Assignments and Symbols Constant Symbols Simple Symbols Compound Symbols 122 Stems Commands to External Environments Environment 124 Commands Basic Structure of REXX Running Under CICS REXX Exec Invocation Where Execs Execute Locating and Loading Execs 126 Editing Execs Page Page Pseudo-conversational Transaction Support Interfaces to Other Programming Languages DBCS Support Miscellaneous Features Page Chapter 13. Keyword Instructions ADDRESS ADDRESS 132 Page ARG ARG 134 CALL 136 Page DO Simple DO Group 138 Repetitive DO Loops Conditional Phrases (WHILE and UNTIL) 140 Note: Using the LEAVE or ITERATEinstructions can also modify the execution of repetitive loops. DROP DROP 142 EXIT EXIT IF IF 144 INTERPRET INTERPRET 146 ITERATE ITERATE LEAVE LEAVE 148 NOP NOP NUMERIC NUMERIC 150 OPTIONS OPTIONS PARSE PARSE 152 PARSE Page PROCEDURE 156 Page PULL PULL 158 PUSH PUSH QUEUE QUEUE 160 RETURN RETURN SAY SAY 162 SELECT SELECT SIGNAL SIGNAL 164 SIGNAL TRACE Alphabetic Character (Word) Options 166 Prefix Options Numeric Options A Typical Example 168 Format of TRACE Output Page UPPER UPPER 170 Chapter 14. Functions Syntax Functions and Subroutines Search Order 172 Errors During Execution Built-in Functions 174 ABBREV (Abbreviation) ABS (Absolute Value) ADDRESS ARG (Argument) BITAND (Bit by Bit AND) 176 BITOR (Bit by Bit OR) BITXOR (Bit by Bit Exclusive OR) B2X (Binary to Hexadecimal) CENTER/CENTRE COMPARE 178 CONDITION COPIES C2D (Character to Decimal) C2X (Character to Hexadecimal) DATATYPE 180 DATE 182 DBCS (Double-Byte Character Set Functions) DELSTR (Delete String) DELWORD (Delete Word) DIGITS D2C (Decimal to Character) D2X (Decimal to Hexadecimal) ERRORTEXT 184 EXTERNALS FIND FORM FORMAT FUZZ 186 INDEX INSERT JUSTIFY LASTPOS (Last Position) LEFT 188 MAX (Maximum) LENGTH MIN (Minimum) OVERLAY POS (Position) QUEUED RANDOM 190 REVERSE RIGHT SIGN SOURCELINE SPACE STORAGE STRIP 192 SUBSTR (Substring) SUBWORD SYMBOL TIME 194 TRACE TRANSLATE TRUNC (Truncate) USERID VALUE 196 VERIFY WORD WORDINDEX WORDLENGTH 198 WORDPOS (Word Position) WORDS X2B (Hexadecimal to Binary) X2C (Hexadecimal to Character) X2D (Hexadecimal to Decimal) External Functions Provided in REXX/CICS STORAGE 200 SYSSBA Page Chapter 15. Parsing General Description Simple Templates for Parsing into Words The Period as a Placeholder 204 Templates Containing String Patterns Templates Containing Positional (Numeric) Patterns 206 Page Combining Patterns and Parsing Into Words 208 Parsing with Variable Patterns Using UPPER Parsing Instructions Summary 210 Parsing Instructions Examples All examples in this section parse source strings into words. ARG Advanced Topics in Parsing Parsing Multiple Strings Combining String and Positional Patterns: A Special Case 212 Parsing with DBCS Characters Details of Steps in Parsing 214 Page 216 Chapter 16. Numbers and Arithmetic Introduction Definition Numbers 218 Precision Arithmetic Operators Arithmetic Operation RulesBasic Operators Addition and Subtraction Multiplication Division Basic Operator Examples Arithmetic Operation RulesAdditional Operators Power Integer Division Remainder Additional Operator Examples Numeric Comparisons Exponential Notation 222 Page Numeric Information Whole Numbers 224 Numbers Used Directly by REXX Errors Chapter 17. Conditions and Condition Traps Action Taken When a Condition Is Not Trapped Action Taken When a Condition Is Trapped 226 Page Condition Information Descriptive Strings 228 Special Variables The Special Variable RC Page Page Chapter 18. REXX/CICS Text Editor Invocation Screen Format Prefix Commands 232 Individual Line Commands Consecutive Block Commands Destination Commands Macros Under the REXX/CICS Editor Command Line Commands ARBCHAR 234 ARGS BACKWARD BOTTOM CANCEL CASE 236 CHANGE CMDLINE CTLCHAR CURLINE 238 DISPLAY DOWN EDIT 240 FILE FIND Examples 242 FORWARD GET GETLIB INPUT JOIN 244 LEFT LINEADD LPREFIX MACRO MSGLINE NULLS NUMBERS PFKEY PFKLINE 248 QQUIT QUERY QUIT 250 RESERVED RESET RIGHT SAVE SORT 252 SPLIT STRIP SYNONYM TOP TRUNC UP Page Chapter 19. REXX/CICS File System File Pools, Directories, and Files Current Directory and Path 258 Security RFS commands AUTH CKDIR CKFILE 260 COPY DISKR DISKW GETDIR MKDIR RDIR RENAME File List Utility 264 Invocation Macros under the REXX/CICS File List Utility FLST Commands CANCEL COPY 266 DOWN END FLST 268 MACRO PFKEY REFRESH RENAME 270 SORT SYNONYM UP FLST Return Codes 272 Running Execs and Transactions from FLST Chapter 20. REXX/CICS List System Directories and Lists Current Directory and Path Security 274 RLS commands CKDIR LPULL LPUSH 276 LQUEUE MKDIR READ VARDROP VARGET VARPUT WRITE Page Chapter 21. REXX/CICS Command Definition Background Highlights Accomplishing Command Definition Command Arguments Passed to REXX Programs Command Arguments Passed to Assembler Programs 282 CICPARMS Control Block Table3. CICPARMS Control Block Non-REXX Language Interfaces CICGETV - Call to Get, Set, or Drop a REXX Variable 284 Chapter 22. REXX/CICS DB2 Interface Programming Considerations Embedding SQL Statements 286 Receiving the Results Using the SQL Communications Area Example Using SQL Statements 288 Page Page Chapter 23. REXX/CICS High-level Client/Server Support Overview High-level, Natural, Transparent REXX Client Interface Support for REXX-based Application Clients and Servers Value of REXX in Client/Server Computing REXX/CICS Client Exec Example High-level Client/Server Support 292 REXX/CICS Server Exec Example High-level Client/Server Support Page Chapter 24. REXX/CICS Panel Facility Facility Example of Panel Definition Defining Panels Defining the Field Control Characters with the '.DEFINE' Verb 296 .DEFINE Default field control characters Options 298 Defining the Actual PANEL Layout with the '.PANEL' Verb .PANEL 300 Panel Generation and Panel Input/Output PANEL RUNTIME 302 Page Options 304 PANEL Variables Panel Facility Return Code Information 306 System Error Reason Codes Programmer Introduced Warning/Error Reason Codes State Codes and Input Codes State codes 308 Input codes State Codes Input codes 310 Location Codes Examples of Sample Panels Example 1 Example 2 Example 3 Example 4 Example 5 312 IF RC=4 & PAN.REA = 124THEN /* warning and no input received */ Example of a REXX Panel Program Page 314 Page 316 Page Page Chapter 25. REXX/CICS Commands Page Page Page CEDA CEMT 324 CLD CONVTMAP 326 COPYR2S 328 COPYS2R 330 Page DEFCMD 332 Page Page DEFSCMD 336 Page DEFTRNID 338 Page EDIT 340 Page EXECDROP 342 Page EXECIO 344 Page EXECLOAD 346 Page Page EXPORT FILEPOOL 350 Page Page GETVERS Page IMPORT Page Page Page Page Page PATH PSEUDO 362 RFS 364 Page RLS 366 Page Page SET 370 SETSYS 372 Page Page WAITREAD WAITREQ 376 Page Page Appendix A. Error Numbers and Messages The following are the REXX error messages: 380 Page 382 Page 384 Page 386 Appendix B. Return Codes Panel Facility SQL RFS and FLST EDITOR and EDIT DIR SET 388 PATH RLS LISTCMD CLD DEFCMD DEFSCMD DEFTRNID EXECDROP 390 EXECLOAD EXECMAP LISTCLIB and LISTELIB GETVERS COPYR2S COPYS2R LISTPOOL LISTTRNID C2S 392 PSEUDO AUTHUSER WAITREQ CEDA and CEMT EXECIO CONVTMAP SCRNINFO 394 Appendix C. Double-Byte Character Set (DBCS) Support General Description Enabling DBCS Data Operations and Symbol Use Symbols and Strings 396 DBCS-Only Symbols and Mixed SBCS/DBCS Symbols DBCS-Only Strings and Mixed SBCS/DBCS Strings Validation DBCS Symbol Validation Mixed String Validation Instruction Examples PARSE PUSH and QUEUE 398 SAY and TRACE UPPER DBCS Function Handling Built-in Function Examples ABBREV 400 COMPARE COPIES DATATYPE Note: If string is not a valid mixed string and Cor Dis specified as type,0is returned. FIND Applying the word extraction from a string and character comparison rules. INDEX, POS, and LASTPOS LENGTH REVERSE 402 SPACE STRIP TRANSLATE VALUE VERIFY WORD, WORDINDEX, and WORDLENGTH WORDS DBCS Processing Functions 404 Counting Option Function Descriptions DBADJUST DBCENTER DBCJUSTIFY DBLEFT DBRIGHT DBRLEFT 406 DBRRIGHT DBTODBCS DBTOSBCS DBUNBRACKET DBVALIDATE DBWIDTH 408 Appendix D. Reserved Keywords and Special Variables Reserved Keywords Special Variables Keywords and Variables 410 Appendix E. Debug Aids Interactive Debugging of Programs Interrupting Execution and Controlling Tracing Debug Aids 412 Appendix F. REXX/CICS Business Value Discussion Business Solutions Business Value Discussion 414 Product Positioning Business Value Discussion Page Appendix G. System Definition/Customization/Administration Authorized REXX/CICS Commands/Authorized Command Options System Profile Exec Authorized REXX/CICS VSE Librarian sublibraries Defining Authorized Users Page Page Page Appendix H. Security REXX/CICS Supports Multiple Transaction Identifiers REXX/CICS File Security ESA/VSE Command Level Security REXX/CICS Authorized Command Support Security Definitions REXX/CICS General Users 422 REXX/CICS Authorized Users REXX/CICS Authorized Commands Page Page Appendix I. Performance Considerations Page Appendix J. Basic Mapping Support Example The map DSECT follows. The following is the file structure created by CONVTMAP. The following example is exec BMSMAP1. It creates a simple panel that asks for a user ID. BMS Example 428 The BMSMAP1 exec created the following panels. BMS Example Page Appendix K. Post-Installation Configuration Create the RFS Filepools Install Resource Definitions Update LSRPOOL Definitions Rename supplied Procedures Update CICSTART.PROC Update CICS Initialization JCL 432 Format the RFS Filepools Create the Help Files Verify the Installation 434 Sample output: Configure the REXX DB2 Interface 436 Bibliography CICS Transaction Server for VSE/ESA Release 1 library Where to Find More Information Books from VSE/ESA 2.5 base program libraries VSE/ESA Version 2 Release 5 438 High-Level Assembler Language (HLASM) Language Environment for VSE/ESA (LE/VSE) VSE/ICCF VSE/POWER VSE/VSAM Books from VSE/ESA 2.5 optional program libraries C for VSE/ESA (C/VSE) COBOL for VSE/ESA (COBOL/VSE) 440 DB2 Server for VSE DL/I VSE PL/I for VSE/ESA (PL/I VSE) Screen Definition Facility II (SDF II) Page Notices Page Trademarks Page Index Special characters A B C D E 448 Page F G 450 H I M N O P Q R S 452 T U V W X Page Sending your comments to IBM