Here you can read all about how to install, configure and use CXU. You will meet the phrase "the CXU-key" which refers to the function key you choose to activate CXU with. Generally CXU will acknowledge with an ISPF message in the upper right corner of the active panel. All ISPF messages issued by CXU is accompanied by a detailed description which is displayed by pressing the HELP-key (normally F1).
Installation.
You can skip this chapter if the command TSO CXU already works for you.
In this chapter it is described how CXU is delivered, how you transfer CXU to the mainframe and how you unpack CXU on the mainframe. If you already have installed CXU before then remember to read about re-installation of CXU. How you configure CXU is described in Configuration and how you use CXU is described in Guide.
Delivery.
CXU is delivered as a download. The name of the download file must be cxu.xmt and
you must save this file locally on your PC. Whether it is the first time or not you download
CXU, you must carry out the transfer and unpacking in the same manner every time.
Transfer.
You must transfer cxu.xmt to the mainframe. It can be done in several different ways
depending on the setup of your PC and the setup of the mainframe where your TSO session is
running. The most important is that:
If you use FTP from a command line on your PC the following commands (after login to the mainframe using your TSO userid) will carry out the transfer:
Unpacking.
The file cxu.xmt is created in a so-called XMIT-format and the corresponding dataset must be
unpacked before CXU is ready to be used. You must issue TSO command RECEIVE
INDATASET(CXU.XMT) in order to unpack CXU. When the RECEIVE command asks you to enter
parameters you just press Enter. You have now created a dataset called
userid.CXU.LOAD and it contains four members CXU, CXULCNSY, CXULCNSI and CXUPARM.
Now CXU is ready to be used. Before you proceed it is a good idea to logoff TSO and logon again. If you do not logoff and logon again you might run into problems using CXU later until you logoff and logon.
Re-installation of CXU.
If you are using CXU already and are performing a re-installation, then you must copy the
members in userid.CXU.LOAD to the library where you have installed CXU permanently. Normally
you will install CXU permanently on your ISPLLIB-concatenation.
Warning: CXU is not re-entrant and thus cannot be put on the link-list.
If CXU is available on ISPLLIB you can use the command:
to copy the new version of CXU to ISPLLIB.
Configuration.
In this chapter it is described how to configure CXU. If the command TSO CXU works for you
even though you have not installed CXU, somebody else on your installation has made CXU
available for you. In this case you can instead of TSO CALL CXU(CXU) '...' type in
TSO CALL CXU ... which is shorter to type!
Important: Any TSO CXU or TSO CALL CXU(CXU) command issues a KEYLIST OFF.
First time CXU is used.
If you have followed the instructions for installing CXU or CXU is already available and you
are ready to use CXU then you need to:
HELP-function for configuration.
First of all it is a good idea to try HELP for CXU. Please try command:
The displayed HELP varies depending on how far you are in the configuration process. HELP is only available in english.
Definition of the CXU-key.
Please activate ISPF EDIT on a dataset or member of your own choice. When you are ready
to edit data, you place the cursor in the command field and executes the command:
where you replace Fnn with the name of the function key you want to active CXU with. A good choice is either F4 or F6, as you probably very seldom use these function keys in EDIT. If you for example wants to use shift-F4 you must specify F16.
Now you can use the selected function key (the CXU-key) to activate CXU with. If you at a later stage either in the same TSO-session or another TSO-session experience that the CXU-key does not work, you just repeat the command TSO CALL CXU(CXU).
Copying to ISPLLIB.
You can avoid repeating the activation of the CXU-key by copying CXU to your
ISPLLIB-concatenation. You should copy all members in userid.CXU.LOAD or you can choose to
add userid.CXU.LOAD to your ISPLLIB-concatenation.
You may not have ISPLLIB allocated. In this case you need to seek help among your colleagues to find out how you allocate ISPLLIB. It is different from installation to installation how this is done and the reason why you cannot find a description of it here.
Definition of the CXU-key everywhere.
If you want to use the same CXU-key everywhere in your TSO-session, you can use the command:
which activates Fnn as the CXU-key for all the ISPF applications you have used so far. You can still experience that the CXU key does not work in some ISPF applications. This will happen if it is the first time you use the ISPF application in question or if the ISPF application automatically sets KEYLIST ON. Use TSO CALL CXU(CXU) or TSO CXU to set KEYLIST OFF.
Guide.
In this chapter you can read all about how to use CXU in your daily work. The idea behind CXU
is to place the cursor somewhere on the data you are processing with ISPF EDIT/VIEW and then
press the CXU-key to make something happen. What happens depends on what the cursor is
positioned at. The action CXU chooses to perform is depending on the data around the cursor
position. There are no hidden dependencies to dataset names or similar. The CXU-key also
works on lines that is not part of data as for example ==MSG> lines.
If the CXU-key does not work and you are sure you have defined it as described in Definition of the CXU-key then you can temporarily solve the problem using command TSO CALL CXU(CXU) which re-activates the CXU-key. The permanent solution is to perform the Copying to ISPLLIB of CXU.
All names without exception are converted internally to uppercase by CXU. Consequently it does not matter if you use lowercase or uppercase. Basically dataset names must be fully qualified as CXU does not use TSO prefixing. There is one exception to this rule: if the character in front of the dataset name is a blank then CXU will first lookup the dataset as specified, but if this dataset does not exist the dataset name is prefixed by your TSO userid and the lookup is repeated. This mechanism is only enforced if the specified dataset name contains at least two qualifiers.
You can control how CXU works using so-called ¶meters within comments. It is a
good idea to place ¶meters in a comment type corresponding to the overall contents
of data. This way it will only be CXU that responds to the ¶meters.
Hint: You can deactivate an ¶meter by removing the & character.
In some cases CXU writes ==MSG> lines. You make ==MSG> lines disappear again either by deleting them or by using the EDIT command RES SPE (RESET SPECIAL). The EDIT command RES (RESET) also makes ==MSG> lines disappear, but it also resets other stuff like the last used search argument for FIND. Thus it is a good idea to use RES SPE.
Press the CXU-key when you have placed the:
Cursor on: &HELP
Displays the HELP text directly in data as ==MSG> lines. HELP is only available in english.
Cursor on: datasetname
The action is dependend on the dataset type, your access to the dataset and whether the
dataset i HSM migrated or not. If you do not have any access to the dataset you will be told
so. If the dataset is migrated to ML2 (migration level 2, typically TAPE) the dataset will
be recallet and you receive control again. You will not be forced to wait. If it is migrated
to ML1 (typically DISK) the dataset is recalled while you wait.
If you only have READ access to the dataset CXU will display it in ISPF VIEW mode (BROWSE mode if it is a LOAD library). If you have more than READ access to the dataset CXU will use ISPF MEMLIST if it is a PO-dataset (member-dataset). Otherwise CXU will display it in ISPF EDIT mode). ISPF MEMLIST not only enables you to EDIT, VIEW or BROWSE members, but also to rename and delete members.
If the first qualifier in the dataset name is &SYSUID. then CXU will replace &SYSUID. with your TSO userid.
If the dataset does not exist and the character in front of the dataset name is a blank, then CXU prefixes the dataset name with your userid and tries again. If this dataset does not exist either and the specified dataset name only consist of two qualifiers then CXU assumes the name to be a DB2 table name instead. Read more in Cursor on: DB2 tablename. Thus you may expect the answer "dataset not found", but instead you receive "table not found" or something similar.
Cursor on: datasetname(membername)
Works in the same manner as for datasetname, but MEMLIST is skipped. If the member is a LOAD
module BROWSE is invoked instead.
Cursor on: dot in datasetname or datasetname(membername)
Activates ISPF DSLIST using the specified dataset name. ISPF DSLIST enables you to carry out
all the commands you are familiar with in ISPF 3.4 on the dataset.
Cursor on: datasetname containing * or %
All the datasets matching the specified wildcard are displayed using ISPF DSLIST. The builtin
prefixing with your TSO userid is disabled when the dataset name is a wildcard.
Cursor on: datasetname(membername) where membername contains * or %
All the members matching the specified wildcard are displayed in a member list. If you have
READ access to the dataset the member list is displayed using ISPF VIEW (BROWSE if it is a
LOAD library). If you have more than READ access to the dataset the member list is displayed
using ISPF MEMLIST which not only enables you to EDIT, VIEW or BROWSE members, but also to
rename and delete members.
Cursor on: commandname
Executes the specified command. It is the equivalent of issuing a TSO commandname. All
characters following commandname are used as parameters until two or more consecutive blanks
are encountered.
Cursor on: %commandname
Executes a REXX or CLIST having the name commandname. It is the equivalent of issuing a TSO
%commandname. All characters following %commandname are used as parameters until two or more
consecutive blanks are encountered.
Cursor on: -commandname
Executes a DB2 command having the name commandname. All characters following -commandname
are used as parameters until two or more consecutive blanks are encountered. The result is
displayed directly in data as ==MSG> lines. If the commandname is DISPLAY the result is
displayed in an ISPF VIEW session.
Hint: Use DIS as commandname if you want to display the result as ==MSG> lines.
However, very large results will always be displayed in an ISPF VIEW-session.
If more than one active DB2 system is available then CXU will search backwards from -commandname for the text &DB2SYS= and use the first four characters specified after &DB2SYS= as DB2 system. If the text &DB2SYS= is not found CXU will display a list of available DB2 systems as ==MSG> lines and ask you to select one of them using ISPF line command MD. When you have done this, please use the CXU-key on -commandname again.
Cursor on: DB2 tablename
Displays index overview and column information in data as ==MSG> lines below the line
containing DB2 tablename.
For each index the index type (PRIMARY,UNIQUE or DUPLICATE) and the column names of the columns constituting the index is displayed. Only the first ten columns are displayed. If all column names cannot be displayed on the same line column numbers are used instead. Only the numbers of the first 12 columns in the index are included.
For each column the name, the column number, the type and the length are displayed. Following length the text NULL is displayed if the column accepts NULL values.
If CXU does not detect any active DB2 systems then CXU will treat tablename as a command if tablename is specified without creator, and as a dataset name if it is specified with creator. If the cursor is placed at the dot between creator and name then CXU interprets the name as a dataset name.
If more than one active DB2 system is available then CXU will search backwards from tablename for the text &DB2SYS= and use the first four characters specified after &DB2SYS= as DB2 system. If the text &DB2SYS= is not found CXU will display a list of available DB2 systems as ==MSG> lines and ask you to select one of them using ISPF line command MD. When you have done this, please use the CXU-key on tablename again.
If tablename is specified without creator CXU will search backwards from tablename for the text &CREATOR= and use the name specified after &CREATOR= as creator. If &CREATOR= is not found CXU will use your TSO userid as creator.
If tablename also exists as a dataset name or a command the dataset name or the command will be used. You can force CXU to interpret the name as a table name by entering a comma (,) in front of the name without intervening blanks.
Cursor on: DB2 tablename containing %
All DB2 tables matching the specified wildcard are displayed in data as ==MSG> lines below
the line containing the wildcard. For each table the table name (as creator.tablename) and
the table type is displayed. CXU assigns DB2 system and creator using the same rules as
for DB2 tablename.
Hint: You can place the cursor on a table name in a ==MSG> line and press the
CXU-key. This will display the index and column overview for the selected table.
Cursor on: SQL statement keyword
The SQL statement starting in the line containing the selected SQL statement keyword is
executed. As end of statement CXU reacts on /* or // in position 1, semicolon (;),
END-EXEC, ENDEXEC or "Bottom of data". Data in comments are ignored. As comment you may use
-- as in SPUFI and QMF, /* to */ as in PLI, * in position 1 as in ASSEMBLER and * in
position 7 as in COBOL.
All characters including position 1 to the maximum length of a line in data are considered as part of the SQL statement. CXU automatically removes superfluous blanks. The resulting maximum length of a SQL statement is 32760 bytes.
Cursor on: SQL statement keywords SELECT or WITH
The result of executing the SQL statement is displayed in an ISPF VIEW session. The result
is saved in a dataset having the name userid.CXUSQL01.ONLOUTnn where nn is a number
making the dataset name unique if you are running more CXU sessions at the same time. The
dataset is overwritten at the next execution of a SELECT or WITH.
The size of the dataset is automatically limited by CXU. The ISPF messsage "Dataset ran out of space" is displayed when it happens. CXU does not limit on the number of rows returned, but on the size of the result in bytes. Fewer and shorter columns makes you able to see more rows. You can avoid the limit by submitting your SQL statement for batch execution by using the &BATCH-parameter. When a SELECT or WITH is executed in batch by CXU it is only the disk space available that limits the size of the result.
The INTO clause of a singleton SELECT will be ignored. This enables you to execute SELECT ... INTO ... FROM ... SQL statements directly from program source without having to remove the INTO clause.
Cursor on: all other SQL statement keywords (INSERT, UPDATE, DELETE, CREATE, DROP, ...)
The result of the execution is displayed as ==MSG> lines.
Cursor on: line-command field in SQL result created by CXU
The row in the SQL result is displayed in a new ISPF VIEW session. Each column in the row is
displayed on its own line using the column name as guiding text. If more than 70 characters
are present in the column the first 70 characters are displayed on the first line following
the guiding text, the next 70 characters are displayed on the second line following the
guiding text and so on.
XUNION as end of SQL statement
You can concatenate the results of two or more adjacent SQL SELECT or WITH statements into
one dataset by using the text XUNION as separator between the statements. A header is
generated for the first statement only. Whether the columns in the result of the adjacent
statements matches each other is not validated. ¶meters
related to the formatting of the result must be specified before the first XUNION as they
are ignored in following statements.
It is possible to use different values for the ¶meters &DB2SYS, &LOCATION and &CREATOR in each statement separated by XUNION. Thus you have the opportunity to make a union almost equal to a UNION in SQL against different DB2 systems and/or locations, which is not possible in the same SQL statement using DB2.
&DB2SYS=ssid
The SQL statement is executed by the DB2 system having the name ssid. The DB2 system must be
active on the MVS system where your TSO session is running. If there is only one active DB2
system on the MVS system you do not have to specify this parameter. A DB2 group attach name
may be used as ssid.
&CREATOR=creatorname
If any of the tables in the SQL statement are specified without creator (Typically in static SQL)
CXU will use creatorname as creator when executing the SQL statement. This parameter is
highly useful when executing SQL statements directly from program source.
&EXPLAIN
Instead of executing the SQL statement CXU will perform an EXPLAIN of the SQL statement and
display the result in a VIEW session. When this parameter is used any hostvariables will be
replaced by so-called parameter markers. The INTO part of a singleton SELECT will be ignored.
&EXPLAIN is particularly useful to use directly from program source as it will provide
you with valuable information about the access path chosen by DB2 for your SQL statement
in an easy manner.
&EXPLAIN requires you to have created a PLAN_TABLE table. If you have not created your own PLAN_TABLE, please ask a colleague for help on how to create a PLAN_TABLE. This is done in several different ways depending on your installation. Thus you will not find any description on how to do it here. Please remember that you need a PLAN_TABLE for each DB2 system you are using CXU on.
The result of an &EXPLAIN is composed of columns from PLAN_TABLE in the following manner:
&EXPLAIN=dataset/member
This parameter works in exactly the same manner as &EXPLAIN, but the result displayed
in the VIEW session is formatted by the SELECT clause specified in dataset/member.
This makes it possible for you to create your own layout of the EXPLAIN result. You can
either specify datasetname or datasetname(membername) depending on the type of dataset you
choose to save the SELECT clause in.
In dataset/member you may use * in position 1 as comment. Only the &WIDTH-parameter is available. Any clause following the SELECT clause is not allowed and all the columns in the SELECT clause must exist in your PLAN_TABLE.
&BATCH=sysout,job,msg,schenv,steplib,acct
When specified this parameter will submit the SQL statement for execution in batch by a
batch job generated by CXU. None of the subparameters are required and the parameter list is
interpreted like parameter lists in JCL:
&WIDTH=w1,w2,colname1:wc1,colname2:wc2,,w6 ...
Sometimes it can be practical to change the width of selected columns in the result. w1 is
the requested width of the first column in the result. w2 is the requested width of the
second column in the result. w6 is the requested width of the sixth column in the result.
Use can use commas to separate the column widths from each other. It is possible to
specify the &WIDTH-parameter more than once. If you do so it is the specifications of
the last parameter that are used in case of overlap.
It is not always practical to calculate the position of a column in the result. You can also specify the name of a column followed by a colon followed by the requested width. In the above parameter specification colname1 is the name of a column to be assigned the width wc1 and colname2 is the name of a column to be assigned the width wc2. You must use the name used as heading of the column in order to make it work. If more than one column has the same heading name all the columns will receive the same width.
Columns receiving a width less than the default width will be marked using equal signs (=) just below the column name instead of dashes (-). If you want to suppress equal signs being used for columns that are shortened you must type in a dash (-) in front of the column width in &WIDTH.
Columns with any kind of LOB type will be assigned the width 256 characters by CXU. If you wish to display a LOB column in any other width you must specify the requested width using &WIDTH.
&HIDE=col1,col2,colname1,colname2,col3 ...
It is possible to remove columns from the result. col1, col2 and col3 are positions of the
columns in the SELECT clause to be excluded from the result. If col1 is 2 the second column
in the SELECT clause will be removed from the result. You can use commas to separate the
column positions. It is possible to specify the &HIDE-parameter more than once.
It is not always practical to calculate the position of a column in the SELECT clause. You can also specify the name of a column. In the above parameter specification colname1 is the name of a column to be excluded and colname2 the name of another column to be excluded. You must use the name used as heading of the column in order to make it work. If more than one column has the same heading name all the columns with that name will be excluded.
&BREAK=col1,col2,colname1,colname2,col3 ...
In order to make the result easier to read it is possible to specify breaks. If two or more
consecutive rows contains the same value in a column specified as a break column in the
&BREAK-parameter, the value is only displayed in the first row where it occurs. The
value is displayed, though, if the value is displayed in another break column specified
before the actual column. col1 is the position of the first break column in the result, col2 is
the position of the second break column in the result and col3 is the position of the fifth
break column in the result. You can use commas to separate the column positions.
It is not always practical to calculate the position of a column in the result. You can also specify the name of a column. In the above parameter specification colname1 is the third break column in the result and colname2 is the fourth break column in the result. You must use the name used as heading of the column in order to make it work.
&HEADER=headerline
The header lines inserted by CXU in the result of a SELECT or WITH is replaced by the
specified headerline. If headerline (the parameter value) is omitted the two default header
lines are not generated and only the contents of the rows are written to the result.
headerline contains the rest of the line regardless of its contents of ¶meters and
spaces. If the line contains an end of comment (*/) and the comment was started using /* the
contents of headerline ends here. ¶meters in headerline are ignored by CXU.
It is possible to append to headerline by specifying more &HEADER=headerline in separate comments. The value of headerline specified in following &HEADER-parameters are appended to the headerline from the previous &HEADER-parameters. Please note that trailing spaces are part of headerline.
&:hostvariablename=varvalue
Defines a variable named hostvariablename and assigns it the value varvalue. If the text
:hostvariablename occurs as part of the SQL statement after the definition of the variable,
CXU will replace the text :hostvariablename with the value specified in varvalue. This
enables you to execute SQL statements containing hostvariables directly from program source
without making any changes to the SQL statement text itself. Letters, digits and the
characters colon (:), dot (.), dash (-) and underscore (_) are allowed in hostvariablename.
No distinction is made between lowercase and uppercase letters.
Please remember to put apostrophes (') around varvalue if a hostvariable is to be replaced by a string. An example: &:myhostvar='thisIsMyString'. If the terminating apostrophe is missing varvalue will contain the contents of the remaining line. This is not true, though, if the variable is defined in a /* comment. Then varvalue will continue until the next apostrophe occurs, so please remember to specify the terminating apostrophe in /* comments. If varvalue must contain an apostrophe you just specify two consecutive apostrophes.
In connection with &EXPLAIN all hostvariables assigned a value using &:hostvariablename=varvalue will be replaced with the specified value while remaining hostvariables are replaced by a parameter marker before execution. Because of this you may experience different access pathes depending on whether a value for a hostvariable has been defined or not.
&COLSEP=char
The first character of char is inserted as separator between each column in every row.
&MARGINS=leftmargin,rightmargin
Only the characters between and including position leftmargin and rightmargin are considered
part of the SQL statement and its associated comments. Leftmargin and rightmargin has effect
for all lines following the line where &MARGINS is specified.
&LEFTCUT=integer
All lines in the result are shifted integer positions to the left.
&SEPARATOR=char
The default statement terminator semicolon (;) is replaced with char. Only the first
character of char is used. &SEPARATOR is used to execute SQL statements where semicolon
is part of the syntax like CREATE TRIGGER.
&TOLABEL=labelname
Instead of stopping the scanning and execution of SQL at the termination of the current
SQL statement, the scanning and execution stops at the line in data carrying the label
labelname. A label is defined using a dot as the first character. It is optional to
specify the dot in labelname. If labelname does not exist or is specified before the line
containing the &TOLABEL-parameter, &TOLABEL is ignored. The &TOLABEL-parameter
is also ignored if it is present in a SQL statement following the first SQL statement to
be executed.
Data between the current SQL statement and the line containing labelname are interpreted as a number of SQL statements separated by semicolon (;), END-EXEC or ENDEXEC. If one of the SQL statements fails (returns a negative sqlcode) the scanning stops and a ROLLBACK of any changes is performed. If all SQL statements are executed without errors then a COMMIT is performed. If the value of the &DB2SYS-parameter changes value in a consecutive SQL statement a COMMIT of the previously executed SQL statements is performed and execution continues using the new DB2 system.
If the executed SQL statements are SELECT or WITH the result of each SQL statement is saved in separate datasets having the name userid.CXUSQLnn.ONLOUTxx where nn is 01 for the first executed SQL statement, 02 for the second and so on. The result of the last executed SQL statement is displayed in a VIEW session. If SELECT or WITH is executed by a batch job the result is saved in separate datasets having the name userid.CXUSQLnn.jobname.
Hint: By specifying &TOLABEL=ZL the current SQL statement and all the following SQL statements in data will be executed. You do not need to assign a label to the last line in data, because EDIT/VIEW has always assigned the label .ZL to the last line.
When more SQL statements are executed sequentially using &TOLABEL the latest assigned value of the following parameters will survive from the previous SQL statement:
and the value of the &BATCH-parameter in the first SQL statement will be used for all following SQL statements. All SQL statements submitted to batch execution in this fashion are executed by the same batch job.
&ACCEPT=integer
If the sqlcode of the current SQL statement returns the value integer CXU will consider the
statement as successfully executed. &ACCEPT may be used in conjuction with
&TOLABEL as it lets CXU perform a COMMIT of the executed statements
even though a single or a few statements failed during execution.
An example of how to use &ACCEPT is &ACCEPT=-803 while executing many inserts. If one or more inserts fails giving sqlcode -803 all the other inserts will still be executed and CXU will finally perform a COMMIT.
&LRECL=integer
The result of a SELECT or WITH is saved in a dataset having LRECL=integer regardless of the
original width of the result.
If two or more consecutive SELECT or WITH statements contains exactly the
same &LRECL-parameter and are executed by the means of &TOLABEL
then the result will be merged together in the same dataset. In other words it is possible to
create a UNION between two different DB2 systems by combining &DB2SYS,
&TOLABEL and &LRECL.
Hint: It is much easier to achieve the same result by using
XUNION. Neither &LRECL nor &TOLABEL is necessary when using
XUNION.
&SQLID=name
The SQL statement is executed using the SQLID specified as name.
&SYNTAX
Instead of executing the SQL statement only the syntax of the statement is checked. This
parameter is very handy if you are writing a SQL statement that may take a while to execute,
but you want to code it correctly before execution.
&ROLLBACK
After executing the SQL statement a ROLLBACK is performed. This parameter may be used to
test SQL updates without saving the result. Please be careful if you use this parameter
in a SQL statement that updates many rows, because the rows are actually updated and
afterwards rolled back. Consider using &SYNTAX instead.
&LOCATION=locationname:ssid
The SQL statement is executed by the database system known under the name locationname by
the current DB2 system. It is optional to specify colon (:) followed by ssid after the
locationname. If :ssid is specified it means that the SQL statement is only executed at
locationname if ssid is equal to the name of the current DB2 system (maybe specified by
the &DB2SYS-parameter).
&UNLOAD
Instead of formatting every single row into a readable format the result is saved
unformatted. The two default header lines are omitted. Using this parameter CXU creates the
same output format as DSNTIAUL does. When &UNLOAD is used the
&LRECL-parameter is ignored. All other ¶meters involved in
formatting of the result are ignored, too.