|
|
138
Database Administration
Chapter 7. Customizing the HELP Text and Messages Text
The DB2 Server for VSE & VM messages and HELP texts are stored in tables,
meaning that they can be retrieved and manipulated just like any other data. You
can modify the information to suit local needs in the following ways:
v Adding or deleting topics
v Changing the information in existing topics
v Adding HELP text supplied by IBM in supported languages
v Adding your own HELP text in supported languages.
Note: A HELP command causes ISQL to issue a SELECT statement on the tables.
Figure 28 shows the relationships between the tables used by the application server
for HELP text support.
SYSTEXT1
SYSLANGUAGE
ITEM
TOPIC
LANGUAGE
LANGKEY
REMARKS
LANGID
ITEM
SEQNO
SQL/DS HELP
LANGKEY
SYSTEXT 2
Figure 28. Relationships between SYSLANGUAGE, SYSTEXT1, and SYSTEXT2
The relationships between the tables are maintained through the following sets of
matching columns:
v LANGKEY in both SYSLANGUAGE and SYSTEXT2
v ITEM in both SYSTEXT1 and SYSTEXT2.
These tables are explained in more detail below.
The SYSLANGUAGE Table
HELP and messages texts are provided in several national languages. During
installation, one language is established as the default; it can be changed after
installation. In addition, you can make more than one language available to ISQL
users.
The SYSLANGUAGE table is created as part of the installation process. It lists all
national languages that are currently supported on the application server, meaning
that both HELP text and messages text are available in these languages. Its primary
purpose is for use with the message repository, which is a mandatory part of the
product installation. Installing the HELP text is optional.
Each entry in this table has the following fields:
1. LANGUAGE — the name of the language. There can be more than one entry to
describe the same language: for example, FRANCAIS, FRENCH, and FR can all
be LANGUAGE field values for French.
139
2. LANGKEY — the language key. This is a four-character code that uniquely
identifies each language, regardless of what name labels it in the LANGUAGE
field.
3. REMARKS — a description of the entry.
4. LANGID — the language identifier.
To view all the columns of the SYSLANGUAGE table, enter the following query:
SELECT * FROM SQLDBA.SYSLANGUAGE
Figure 29 shows a sample SYSLANGUAGE table.
LANGUAGE
LANGKEY REMARKS
LANGID
--------------
-------
---------------------------------------
--------
ENGLISH
S001
AMERICAN ENGLISH VERSION OF HELP TEXT
AMENG
ENGLISH
S002
ENGLISH UPPER CASE VERSION OF HELP TEXT
UCENG
FRENCH
S003
FRENCH VERSION OF HELP TEXT
FRANC
FRANCAIS
S003
TEXTE D’AIDE FRANCAIS
FRANC
Figure 29. Sample SQLDBA.SYSLANGUAGE Table
The language key (LANGKEY) can be one of those listed in Table 26.
Table 26. Language Keys
Language Key
Description
Language
ID
S001
American mixed case
AMENG
S002
English upper case
UCENG
S003
French
FRANC
S004
German
GER
D001
Japanese
KANJI
D003
Simplified Chinese
HANZI
Note: IBM has reserved the following language key ranges for use by future
languages supplied by IBM:
v S000 to S500 for single-byte character set (SBCS) or EBCDIC languages
v D000 to D500 for double-byte character set (DBCS) languages.
In VM, the default language is established by the language currently set in CMS. If
this language is not supported by the application server, then the default language
defined during installation is used.
In VSE, the default language is established by the following:
v ISQL — from either a parameter in the CIRB transaction (LANGID), or a
language supplied by IBM.
v DBSU — link-edited with the default language (messages only)
v PREP — link-edited with the default language (messages only).
v DSQG, DSQU, DSQQ and DSQD — from a parameter in the CIRB transaction
(LANGID).
v CBND — from a parameter in the CIRB transaction (LANGID).
|
v Batch Binding — link-edited with the default language (messages only).
v SQLGLOB File Batch Update/Query Program — link-edited with the default
language (messages only).
140
Database Administration
The SYSTEXT1 and SYSTEXT2 Tables
The HELP text tables are normally loaded during the installation process. The DBS
Utility accomplishes this task by creating the HELP text tables SYSTEXT1
and
SYSTEXT2 for the user SQLDBA, loading data into both tables (through
DATALOAD), and creating an index on each.
Figure 30 shows the formats of these tables, but not the actual tables.
SYSTEXT1
SYSTEXT2
TOPIC
ITEM
ITEM SEQNO
"SQL/DS HELP"
LANGKEY
-----------
----
----
-----
-----------------------
-------
VIEW
5260
5260
10
TOPIC NAME: CREATE VIEW
S001
VIEW
5330
•
•
•
•
VIEW
5920
5260
110
CREATE VIEW is an SQL ...
S001
VIEWS
5260
5260
120
more tables. You can ...
S001
VIEWS
5330
•
•
•
•
VIEWS
5920
•
•
•
•
•
•
5260
1070
DELIVERY_TIME was less ...
S001
CREATE VIEW
5260
5260
1080
S001
•
5260
10
RUBRIQUE : CREATE VIEW
S003
DROP VIEW
5330
•
•
•
•
•
•
•
•
•
•
•
•
5260
100
CREATE VIEW est une ...
S003
•
•
5260
110
d’une ou plusieurs ...
S003
•
•
•
•
•
•
•
•
5260
1110
FAB, ART et JOURS) ...
S003
5260
1120
S003
5330
10
TOPIC NAME: DROP VIEW
S001
•
•
•
•
•
•
•
•
5330
90
DROP VIEW is an SQL ...
S001
5330
100
SQL/DS also automatically ... S001
•
•
•
•
•
•
•
•
Figure 30. Formats of the Tables SYSTEXT1 and SYSTEXT2
The following SQL statements are used during the loading process to create
SYSTEXT1 and SYSTEXT2:
CREATE TABLE SQLDBA.SYSTEXT1 (TOPIC CHAR(20) FOR BIT DATA NOT NULL,
ITEM SMALLINT NOT NULL)
IN "PUBLIC"."HELPTEXT"
CREATE TABLE SQLDBA.SYSTEXT2 (ITEM SMALLINT NOT NULL,
SEQNO SMALLINT NOT NULL,
"SQL/DS HELP" CHAR(60) FOR BIT DATA NOT NULL,
LANGKEY CHAR(4) NOT NULL)
IN "PUBLIC"."HELPTEXT"
When a user enters a HELP command, a query like this is processed:
SELECT "SQL/DS HELP"
FROM SQLDBA.SYSTEXT1, SQLDBA.SYSTEXT2
WHERE TOPIC = ’topicname’
AND SQLDBA.SYSTEXT1.ITEM = SQLDBA.SYSTEXT2.ITEM
AND LANGKEY = ’XXXX’
Chapter 7. Customizing the HELP Text and Messages Text
141
where XXXX is the four-character language key that indicates a specific HELP text
language from among those currently installed on the DB2 Server for VSE & VM
application server.
When the support for languages is installed, HELP text may or may not be
available depending on your site’s requirements. Each ISQL user can select from
among the languages currently installed on the application server. To view which
languages are currently installed, a user enters the following query:
SELECT LANGUAGE FROM SQLDBA.SYSLANGUAGE
The user can then change the default language with the ISQL SET LANGUAGE
command.
The topic that the user supplies is substituted in topicname. An ORDER BY clause is
not used in the query because these indexes are defined on the tables:
CREATE INDEX SQLDBA.SYSTEXT1INDEX
ON SQLDBA.SYSTEXT1(TOPIC,ITEM)
CREATE INDEX SQLDBA.SYSTEXT2INDEX
ON SQLDBA.SYSTEXT2(ITEM,SEQNO,LANGKEY)
CREATE INDEX SQLDBA.SYSLANGINDEX
ON SQLDBA.SYSLANGUAGE(LANGUAGE)
CREATE INDEX SQLDBA.SYSLANGINDEX
ON SQLDBA.SYSLANGUAGE(LANGID)
A HELP command uses SYSTEXT1 as a pointer to SYSTEXT2. Suppose an ISQL
user enters:
help ’view’
The parameter ‘view’ is converted to uppercase. The database manager finds all
occurrences of the character string ‘VIEW’ in the TOPIC column of SYSTEXT1 for
the HELP text of the current language. See Figure 31.
SYSTEXT1
TOPIC
ITEM
-----------
----
-------> VIEW
5260
-------> VIEW
5330
-------> VIEW
5920
CREATE VIEW
5260
CREATE V
5260
DROP VIEW
5330
VIEW QUERY
5030
VIEW MODS
5040
•
•
•
•
•
•
Figure 31. Use of the SYSTEXT1 Table
Figure 31 shows three occurrences of the string VIEW. Each has an item number
associated with it (5260, 5330, 5920). These numbers and the language key are used
as pointers (through the query join) to the ITEM numbers and language key in
table SYSTEXT2. The rows in SYSTEXT2 having those ITEM numbers and
142
Database Administration
language key are retrieved in order, primarily by ITEM number and the language
key, and secondarily by sequence number (SEQNO). Thus, three unique topics are
returned when HELP ‘VIEW’ is entered.
Note that other rows in SYSTEXT1 have identical ITEM numbers but different
names (TOPIC). These rows enable retrieval of each of the four topics separately.
For example, the command HELP ‘CREATE VIEW’ retrieves only the topic having
ITEM number 5260. Similarly, the ‘CREATE V’ entry in table SYSTEXT1 is an alias
for ‘CREATE VIEW’; it also points to ITEM 5260.
This cross-referencing scheme has three forms:
v Duplicate topic names pointing to more than one actual topic (for example,
HELP ‘VIEW’).
v Multiple topic names pointing to the same topic (for example, HELP ‘CREATE
VIEW’ and HELP ‘CREATE V’).
v A unique topic name pointing to one topic (for example, HELP ‘VIEW MODS’).
Adding Topics to HELP Text Tables
You can add new topics to the HELP text tables supplied by IBM, or create your
own HELP text table. As modifying the HELP text supplied by IBM greatly
increases the amount of administrative work required if you must later reinstall the
HELP text, a much better method is to set up your own independent HELP text
tables in some other PUBLIC dbspace. This method is described in “Creating Your
Own HELP Text Tables” on page 144.
Adding a HELP Topic to the HELP Text Supplied by IBM
Parts of this task require DBA authority (or at least INSERT authority on the
SYSTEXT1 and SYSTEXT2 tables). If you plan to add much new material to the
HELP text, see “Making the HELPTEXT Dbspace Larger” on page 145 and
“Moving the HELP Text to Another Dbspace” on page 147.
To add your own topic to the tables, follow these steps:
1. Pick a TOPIC name, up to a maximum of 20 characters. This name must be
unique among all TOPIC names in table SYSTEXT1. An easy way to check this
is to enter the query:
SELECT * FROM SQLDBA.SYSTEXT1 WHERE TOPIC = ’candidate name’
If rows are returned, that TOPIC name already exists, and you must choose and
test another.
2. Choose an ITEM number less than 5 000 for the new topic. Numbers of 5 000
and above are reserved for topics supplied by IBM.
3. Insert a row into SYSTEXT1 for the new TOPIC name and its ITEM number.
For example:
INSERT INTO SQLDBA.SYSTEXT1 VALUES (’HOURS’,1000)
4. Insert rows into SYSTEXT2 for the information to be displayed when a user
requests HELP on this new topic. This information must include the values to
be used in the four columns of table SYSTEXT2. For example:
INSERT INTO SQLDBA.SYSTEXT2
VALUES(1000,10,’HOURS OF USE:’,’S001’)
INSERT INTO SQLDBA.SYSTEXT2
VALUES(1000,20,’8 AM TO 6 PM’,’S001’)
Chapter 7. Customizing the HELP Text and Messages Text
143
where “S001” is the English language key. You can repeat this type of INSERT
for every other language.
Note: The “SQL/DS HELP” column has a length of 60 characters.
When adding HELP text to the SYSTEXT2 table, a language key must be specified.
A list of valid language keys is found in Table 26 on page 140. You should use
installation procedures supplied by IBM.
Creating Your Own HELP Text Tables
You should consider using the SYSTEXT1 and SYSTEXT2 tables as the basis for
creating your own HELP text tables. SYSLANGUAGE must still exist for the HELP
command to work, unless you establish HELP text tables and query those tables as
shown in Figure 32.
Figure 32 shows example SQL commands to set up your own local HELP text.
CREATE TABLE SQLDBA.LTEXT1 (TOPIC CHAR(20) FOR BIT DATA NOT NULL,
ITEM SMALLINT NOT NULL)
IN "PUBLIC".LOCAL
CREATE TABLE SQLDBA.LTEXT2 (ITEM SMALLINT NOT NULL,
SEQNO SMALLINT NOT NULL,
"LOCAL HELP" CHAR(60) FOR BIT DATA NOT NULL,
LANGKEY CHAR(4) NOT NULL)
IN "PUBLIC".LOCAL
CREATE INDEX SQLDBA.LTEXT1INDEX
ON SQLDBA.LTEXT1(TOPIC,ITEM)
CREATE INDEX SQLDBA.LTEXT2INDEX
ON SQLDBA.LTEXT2(ITEM,SEQNO,LANGKEY)
SELECT "LOCAL HELP"
FROM SQLDBA.LTEXT1, SQLDBA.LTEXT2
WHERE TOPIC = ’topicname’
AND SQLDBA.LTEXT1.ITEM = SQLDBA.LTEXT2.ITEM
AND LANGKEY=’XXXX’
Figure 32. Implementing Your Own HELP Text Tables
XXXX in the LANGKEY column represents the language key.
In this example, two tables, SQLDBA.LTEXT1 and SQLDBA.LTEXT2, are created in
a PUBLIC dbspace called LOCAL. Appropriate indexes are also defined. Once the
tables are created, you can add topics in a way similar to that described previously
for the tables of HELP text supplied by IBM. Replace the names supplied by IBM
for the HELP text tables, dbspace, and column names with your own names.
Users can then access the new HELP text with an ISQL routine that contains a
SELECT statement (see the example in Figure 32). The ISQL stored routines
supplied by IBM for accessing the original HELP text may not work for the new
tables, so it may be necessary to set up new ones. The SELECT authority must be
granted to all users on the table containing the routine and on the HELP text
tables.
144
Database Administration
Making the HELPTEXT Dbspace Larger
The size of the original HELPTEXT dbspace is 8192 pages, which is sufficient to
hold the HELP text supplied by IBM and four or five languages. If you plan to add
extensively to the text or to add more than five languages, it may be necessary to
increase the size of this dbspace.
To see how many pages are currently active in the HELPTEXT dbspace, issue the
following query through ISQL or the DBS Utility:
SELECT DBSPACENAME,NACTIVE
FROM SYSTEM.SYSDBSPACES
WHERE DBSPACENAME=’HELPTEXT’
If the NACTIVE (number of active data pages) value is close to 4646 (8192 minus
the index pages allowance), consider making the HELPTEXT dbspace larger. To
estimate how many pages are needed in the dbspace for the modified HELP text,
see Appendix A, “Estimating Your Dbspace Requirements,” on page 223.
If the estimated number of pages (for both current and future estimated usage) is
greater than or close to 8192, increase the size of the dbspace. To do this, you must
drop and re-create the dbspace, as follows:
1. UNLOAD the “PUBLIC”.“HELPTEXT” dbspace using the DBS Utility.
2. DROP the “PUBLIC”.“HELPTEXT” dbspace.
3. ACQUIRE a new “PUBLIC”.“HELPTEXT” dbspace with the new required
number of pages.
4. RELOAD the dbspace using the DBS Utility.
5. Reinstate the required indexes and authorities.
6. Reinstate any user-defined indexes, views, or authorities.
7. Proceed with the updates to the HELP text.
Figure 33 and Figure 34 show examples of increasing the size of the
“PUBLIC”.“HELPTEXT” dbspace to 8448 pages. A tape is used in this example to
temporarily hold the HELP information that is on your database.
Chapter 7. Customizing the HELP Text and Messages Text
145
// JOB UNLOAD HELP TEXT
// EXEC PROC=DBNAME01
// EXEC PROC=ARIS71PL
// TLBL HELPTAP,
// ASSGN SYS005,
// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,PROGNAME=ARIDBS’
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
UNLOAD DBSPACE ("PUBLIC"."HELPTEXT") OUTFILE(HELPTAP);
DROP DBSPACE "PUBLIC"."HELPTEXT";
ACQUIRE PUBLIC DBSPACE NAMED "HELPTEXT" (PAGES=8448);
RELOAD DBSPACE ("PUBLIC"."HELPTEXT") NEW INFILE(HELPTAP);
CREATE INDEX SQLDBA.SYSTEXT1INDEX ON SQLDBA.SYSTEXT1 (TOPIC,ITEM);
CREATE INDEX SQLDBA.SYSTEXT2INDEX ON SQLDBA.SYSTEXT2 (ITEM,SEQNO,LANGKEY);
GRANT SELECT ON SQLDBA.SYSTEXT1 TO PUBLIC;
GRANT SELECT ON SQLDBA.SYSTEXT2 TO PUBLIC;
COMMENT ’ ** OPTIONALLY ADD SQL STATEMENTS TO GRANT AUTHORIZATIONS **
** OR CREATE ANY VIEWS REQUIRED FOR THE NEW DATA BASE. **’;
CREATE VIEW
;
GRANT
;
/&
Figure 33. Unloading and Reloading the HELP Text in VSE
FILEDEF HELPTAP TAPn...
SQLDBSU DB(DBNAME01) IN(TERM)
CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;
UNLOAD DBSPACE ("PUBLIC"."HELPTEXT") OUTFILE(HELPTAP);
DROP DBSPACE "PUBLIC"."HELPTEXT";
ACQUIRE PUBLIC DBSPACE NAMED "HELPTEXT" (PAGES=8448);
RELOAD DBSPACE ("PUBLIC"."HELPTEXT") NEW INFILE(HELPTAP);
CREATE INDEX SQLDBA.SYSTEXT1INDEX ON SQLDBA.SYSTEXT1 (TOPIC,ITEM);
CREATE INDEX SQLDBA.SYSTEXT2INDEX ON SQLDBA.SYSTEXT2 (ITEM,SEQNO,LANGKEY);
GRANT SELECT ON SQLDBA.SYSTEXT1 TO PUBLIC;
GRANT SELECT ON SQLDBA.SYSTEXT2 TO PUBLIC;
COMMENT ’ ** OPTIONALLY ADD SQL STATEMENTS TO GRANT AUTHORIZATIONS **
** OR CREATE ANY VIEWS REQUIRED FOR THE NEW DATA BASE. **’;
CREATE VIEW
;
GRANT
;
COMMIT WORK RELEASE;
Figure 34. Unloading and Reloading the HELP Text in VM
146
Database Administration
Moving the HELP Text to Another Dbspace
The HELP text can also be moved to another dbspace if more space is needed for
additional user documentation or if it needs to be moved for other administrative
reasons. The current size of the HELPTEXT dbspace is 8192 pages. The dbspace to
which the HELP text is being moved must be at least that size. To accomplish the
move:
1. UNLOAD the “PUBLIC”.“ HELPTEXT” dbspace using the DBS Utility.
2. DROP the “PUBLIC”.“HELPTEXT” dbspace.
3. ACQUIRE a new dbspace with the desired number of pages.
4. RELOAD the new dbspace using the DBS Utility.
5. Reinstate any user-defined indexes, views, or authorities.
6. Proceed with updates to the HELP text (if updates are being done).
Note: The names of the tables, columns, and indexes cannot be changed. In
addition, the owner name cannot change. Future reinstallations of the HELP
text will assume that the original names exist in the database.
Printing the HELP Text Using the DBS Utility
Use the DBS Utility to produce hardcopy output of the HELP topics. Because the
softcopy is stored in tables, you need only code a SELECT statement that retrieves
the desired topics, and execute this statement through the DBS Utility control file
input. The DBS Utility formats the output of the SELECT statement for you.
The broad categories of HELP topics and their ranges of ITEM numbers are as
follows:
Text for
Appears in ITEMs
Commands (SQL and ISQL)
5000 - 9999
Messages
10000 - 19999
SQLCODES
20000 - 29999
Copyright Notice
30000
By using these ranges, you can code queries to retrieve various subsets of the
HELP topics. For example, the following query retrieves all the SQL statements
and ISQL commands (which were extracted from the DB2 Server for VSE & VM
Interactive SQL Guide and Reference manual):
SELECT * FROM SQLDBA.SYSTEXT2
WHERE ITEM BETWEEN 5000 AND 9999 OR ITEM = 30000
AND LANGKEY=’S001’
ORDER BY 1, 2;
Item 30,000 (the copyright notice) must be retrieved and printed whenever you
print IBM machine-readable information.
To print a copy of all messages and codes for a language, you can use a query like:
SELECT * FROM SQLDBA.SYSTEXT2
WHERE ITEM BETWEEN 10000 AND 30000
AND LANGKEY=’XXXX’
ORDER BY 1, 2;
where “XXXX” represents a selected language key.
Chapter 7. Customizing the HELP Text and Messages Text
147
Printing the HELP Text Using ISQL
You can also enter the SQL statement described above through an ISQL terminal.
When the desired HELP topics are displayed on the screen, enter a PRINT
command to obtain a hardcopy. Perform the desired formatting before entering the
PRINT command.
The class and number of copies desired can be specified on the PRINT command;
otherwise, the defaults are used. See the DB2 Server for VSE & VM Interactive SQL
Guide and Reference manual for detailed information.
Because the HELP topics contain both upper- and lowercase characters, a print
class that prints both characters should be specified. This depends on which HELP
text language you select: in the case of English, for example, the HELP text
contains both upper- and lowercase characters, so you should specify an
appropriate print class.
148
Database Administration
Chapter 8. Application Design Considerations
This chapter describes the facilities that are available for designing and
implementing applications, and discusses some considerations that application
developers and the database administrator (DBA) should take into account.
Application Implementation Capabilities
This section discusses the application implementation alternatives that developers
can consider. For each environment, several different ways of implementing
application functions are identified and discussed.
The following broad categories of applications are also discussed:
v Query Capabilities
v Report Writing Capabilities
v Programmed Application Capabilities
v Execs that use DB2 Server for VM Facilities
Batch/Interactive Capabilities
For batch/interactive DB2 Server for VSE usage environments, there are two
alternatives for application implementations:
v Assembler and High-Level-Language Programs
These are programs written in Fortran, PL/I, COBOL, or assembler that would
run as VSE batch jobs or interactive (VSE/ICCF) applications.
In addition, you can use SQL Extended Dynamic Statements to code your own
preprocessor in assembler language. Although the data requests must be made
in SQL, you can code your preprocessor to translate some other data
manipulation language to SQL statements. General concepts for coding a
preprocessor are in the DB2 Server for VSE & VM Application Programming
manual.
v DBS File Maintenance and Reporting
These are executions of the Database Services (DBS) Utility, which supports
execution of SQL statements and DBS commands.
High-Level-Language Programs
The primary vehicle for implementing application functions would be
high-level-language programs written for execution as batch jobs in either VSE
partitions or interactive partitions.
VSE Batch Partitions: VSE batch jobs support unit record devices or
VSE/POWER spool files and VSE files for input and output processing. The DB2
Server for VSE system can be used to support data sharing, data recovery, and
data function requirements of the applications.
VSE/ICCF Applications: Application programs written for execution under
VSE/ICCF can support some level of interaction with a terminal user. The
VSE/ICCF environment supports invocation of an application from a user
terminal, and can be effectively used to manage user input (SYSIPT) and output
(SYSLST) files as VSE/ICCF files.
149
DBS File Maintenance and Reporting
On batch/interactive systems with sufficient real storage available for dynamic
SQL processing, the DBS Utility can be effectively used for file maintenance and
reporting operations. For detailed information on maintenance, see Chapter 3,
“Maintaining Your Database,” on page 57.
Briefly, the DBS Utility can be used to:
v
Load DB2 Server for VSE database tables from sequential files on DASD,
magnetic tape, or SYSIPT
v
Unload DB2 Server for VSE database tables to sequential files on DASD,
magnetic tape, or SYSIPT
v
Load DB2 Server for VSE tables from terminal users from bulk input files
developed using the VSE/ICCF editor
v
Unload data from DB2 Server for VSE tables to VSE/ICCF Files
v
Implement SQL procedures
Application functions that do not require procedural logic or variable
information can be carried out as a sequence of DBS SQL statements in a SYSIPT
(or VSE/ICCF) input file.
v
Produce DBS reports, which may contain formatted listings of the selected data
from an SQL SELECT statement
v
Reorganize data in the database
You can unload the data from the database using a DBS UNLOAD command,
and reload it with a different structure using the DBS RELOAD command.
v
Convert data for interchange with non-DB2 Server for VSE products
For example, the utility can be used to load and unload data in a zoned-decimal
format. Zoned-decimal is not a DB2 Server for VSE data type, so the DBS Utility
converts the data as needed.
v
Support interactive DBS processing from a VSE/ICCF terminal.
If you include the VSE/ICCF /DATA INCON job control statement in the DBS job
control, the utility takes its input (SYSIPT records) from the terminal input, and
displays its output (SYSLST) at the terminal. You can tailor the DBS output to
your terminal, by using the DBS SET LINEWIDTH command to specify the number
of characters to be displayed on an output line.
Online (CICS) Transaction Processing Capabilities
The basic capabilities available in the batch/interactive environment are extended
with the addition of the CICS subsystem and the online support. The following
sections describe these additional capabilities.
The main method for implementing online transactions is through CICS
high-level-language transaction processing programs. These programs can access
the DB2 Server for VSE system and exploit the unique facilities of the CICS
subsystem. Your transactions must be coded in COBOL, assembler, PL/I, or some
other language that the CICS subsystem supports and for which you have coded a
preprocessor. The CICS subsystem does not support Fortran.
The CICS transaction processing environment supports terminal-driven,
fast-response-time application processing. It also provides facilities for
interconnecting systems and distributing application processing in a network of
systems.
150
Database Administration
In addition to supporting CICS transaction access to the DB2 Server for VSE
system, data stored by the DB2 Server for VSE system can be shared with batch
and VSE/ICCF application programs.
Query Capabilities
Data can be queried through either application programs, the DBS Utility, or ISQL.
Which facility should be used depends on the complexity of the query and
whether it will be used repeatedly.
One-Time Queries
Query functions can be coded as application programs or processed through ISQL.
ISQL enables end users to formulate SQL queries on data and view and format the
results.
You can satisfy many of your end users’ data retrieval requirements by making the
ISQL facilities available to them.
Periodic Queries
There are several ways to design queries that will be used repeatedly:
Stored Queries: Queries can be developed under ISQL and stored for future,
repetitive use. End users can develop and store their own, or as DBA, you may
choose to create a specific set for distribution. When you develop a stored query
you can also save information about how its display should be formatted, so that
when users invoke the query, the display will be automatically formatted for them.
In addition, if you must later change a stored query, you can also change the
formatting information. When possible, ISQL saves existing formatting information
so that you do not have to re-enter it when there is a minor change to a stored
query.
Stored queries cannot be shared among users, so a separate copy must be stored
for each of them. The developer of a set of queries could use an ISQL routine (see
“ISQL Routines”) to enter and store them; then, any user who needed access to
those queries would simply run the routine to obtain a copy of them. The user
must have SELECT authority on the developer’s ROUTINE table.
One of the advantages of stored queries is that users simply START them; they
need not be familiar with SQL. With parameters, stored queries can be developed
that are general in nature—that is, they can support variable input for the same
basic function.
Note: If you develop stored queries and routines for use by multiple users, you
may want to consider devising your own HELP tables to provide
information on them. See “Creating Your Own HELP Text Tables” on page
144.
ISQL Routines: For more complex application functions that involve multiple
SQL functions, consider using ISQL routines. Routines have the following
advantages over stored queries:
v They can hold multiple SQL statements
v They support ISQL statements
v They can be shared.
Like stored queries, routines can be parameterized to provide variability in the
function provided, and users need not understand the details of the underlying
statements.
Chapter 8. Application Design Considerations
151
Because stored routines may support complex functions, you may want to account
for possible error conditions, by using the ISQL SET RUNMODE and SET
AUTOCOMMIT commands to provide for error handling in routines.
Figure 35 illustrates a routine that updates the SALARY value in the sample
EMPLOYEE table. It also displays a report showing the old and new values, and
prints the report of the transaction.
SALUPD
0010
COMMIT WORK
SALUPD
0020
SET AUTOCOMMIT OFF
SALUPD
0030
SET RUNMODE CANCEL
SALUPD
0040
UPDATE SQLDBA.EMPLOYEE -
SALUPD
0050
SET SALARY = SALARY + &3 -
SALUPD
0060
WHERE EMPNO = &1 AND JOB = &2;
SALUPD
0070
SELECT EMPNO,JOB,SALARY-&3,SALARY -
SALUPD
0080
FROM SQLDBA.EMPLOYEE -
SALUPD
0090
WHERE EMPNO = &1 AND JOB = &2 -
SALUPD
0100
UNION -
SALUPD
0110
SELECT EMPNO,JOB,SALARY -
SALUPD
0120
FROM SQLDBA.EMPLOYEE -
SALUPD
0130
WHERE EMPNO ¬= &1 AND JOB = &2 -
SALUPD
0140
ORDER BY 1
SALUPD
0150
FORMAT COL 3 NAME ’OLD SALARY’
SALUPD
0160
FORMAT COL 4 NAME ’NEW SALARY’
SALUPD
0170
FORMAT TOTAL (3 4)
SALUPD
0180
FORMAT TTITLE ’UPDATED THE SALARY OF EMPLOYEE &1 BY $ &3’
SALUPD
0190
DISPLAY
SALUPD
0200
PRINT COPIES 3
SALUPD
0210
END
SALUPD
0220
COMMIT WORK
Figure 35. Example ISQL Routine for the EMPLOYEE Table Update
To execute the ISQL routine in Figure 35, a user would enter (during an ISQL
session):
RUN SALUPD (empno job change)
The routine is designed to update one row of the EMPLOYEE table based on
parameter input specified on the RUN command, and run a query that displays
the results of the update. All salaries for the job are displayed, not just the updated
salary. After reviewing the display, the user enters an END command to have three
copies of the display printed; then, the routine commits the transaction to the
database. If the displayed results are not correct, the user can cancel the update by
issuing the CANCEL command in place of the END command.
ISQL EXECs: In VM, ISQL and SQL statements can be stacked by an EXEC for
execution by ISQL. Such EXECs can be created using either EXEC 2 or the System
Product Interpreter, and can be written for execution either during or outside of an
ISQL session. (You cannot write one that will run both ways.)
To process an ISQL EXEC that is designed to run during an ISQL session, the user
enters “CMS” to get into Subset mode, and then types in the name of the EXEC.
The EXEC must place a RETURN CMS command as the first entry on the stack, in
order to cause control to be returned to ISQL for processing of the rest of the
statements on the stack.
CAUTION: EXECs that are processed from CMS Subset should not run ISQL, the
DBS Utility, or SQL applications, as the results will be unpredictable.
152
Database Administration
Figure 36 shows an example of an EXEC called UPDSAL.
/*
UPDSAL EXEC
6/10/90
*/
/* THIS EXEC PROGRAM ALLOWS A USER TO PERFORM UPDATES ON
*/
/* THE SALARY COLUMN OF THE EMPLOYEE TABLE. IT IS DESIGNED
*/
/* TO BE STARTED WHILE IN A CMS SUBSET, AND IT AUTOMATICALLY
*/
/* RETURNS TO ISQL TO EXECUTE THE UPDATE COMMANDS WHICH HAVE BEEN
*/
/* PLACED ON THE PROGRAM STACK; THEN THE TABLE IS DISPLAYED AND
*/
/* PRINTED TO SHOW THE CHANGES MADE.
*/
PARTLIST = ""
/* WILL CONTAIN LIST OF EMPNO’S WHOSE
*/
/* TOTAL SALARY HAS CHANGED
*/
DO COUNT = 1
DO FOREVER
SAY "ENTER EMPNO
(ENTER ’END’ WHEN DONE)"
PULL E
IF E = ’END’ THEN LEAVE COUNT
SAY ENTER JOB
/* USER ENTERS UPDATE INFORMATION
*/
PULL J
/* (DATA IS CHECKED FOR VALID
*/
SAY ENTER CHANGE TO SALARY
/* TYPES)
*/
PULL CTS
IF DATATYPE(E,W) & DATATYPE(J,A) & DATATYPE(CTS,N) THEN LEAVE
ELSE SAY "DATA ENTERED INCORRECTLY--TRY AGAIN"
END
UPD.COUNT = "UPDATE EMPLOYEE SET SALARY = SALARY + "CTS,
" WHERE EMPNO = "E" AND JOB = "J
/* UPDATE COMMANDS ARE
*/
EMPLIST = EMPLIST", "E
/* HELD IN AN ARRAY
*/
END
QUEUE RETURN
/* TO ISQL */
IF COUNT = 1 THEN EXIT
/* NO UPDATES?
*/
ELSE EMPLIST = SUBSTR(EMPLIST,3)
/* REMOVE FIRST COMMA */
QUEUE COMMIT WORK
QUEUE SET AUTOCOMMIT OFF
DO N = 1 TO COUNT-1
/* PLACE UPDATE COMMANDS
*/
QUEUE UPD.N
/* ON PROGRAM STACK
*/
END
QUEUE "SELECT JOB, EMPNO, SALARY FROM EMPLOYEE -"
IF COUNT > 2
THEN QUEUE "WHERE EMPNO IN ("EMPLIST") -"
ELSE QUEUE "WHERE EMPNO = "EMPLIST" -"
/* QUERY, FORMATTING,
*/
QUEUE "ORDER BY JOB, EMPNO"
/* AND PRINT COMMANDS
*/
QUEUE FORMAT GROUP JOB
QUEUE FORMAT SUBTOTAL SALARY
QUEUE "FORMAT TTITLE ’SUMMARY OF CHANGES IN SALARY TOTALS’"
QUEUE DISPLAY
QUEUE PRINT COPIES 3
QUEUE END
QUEUE COMMIT WORK
Figure 36. ISQL EXEC for Updating the EMPLOYEE Table During an ISQL Session
Here, an ISQL EXEC stacks SQL UPDATE statements that are defined based on the
user’s responses to prompts for information. The prompts and stacking of updates
are done in a loop, so that multiple EMPLOYEE rows can be updated with one
execution of the EXEC.
After the user has entered all the updates, the EXEC stacks a query that will
display and print the results of the updates.
Chapter 8. Application Design Considerations
153
To process an ISQL EXEC that is designed to run outside of an ISQL session, the
user simply enters the name of the EXEC—that is, the user initiates the EXEC
while using CMS, without having to start or even know about ISQL.
This type of EXEC would not include a RETURN command, because there is no
ISQL session to return to. Instead, it must include an ISQL EXIT command as the
last entry on the stack, and must start ISQL (EXEC ISQL) after the stack entries
have been completed.
Figure 37 on page 155 illustrates an ISQL EXEC designed to be run outside of an
ISQL session, which carries out the same function as the one shown in Figure 36
on page 153.
154
Database Administration
/*
XUPDSAL EXEC
*/
/* THIS EXEC PROGRAM ALLOWS A USER TO PERFORM UPDATES ON
*/
/* THE SALARY COLUMN OF THE EMPLOYEE TABLE. IT IS DESIGNED
*/
/* TO BE STARTED WHILE IN CMS (WITHOUT AN ISQL SESSION). IT
*/
/* AUTOMATICALLY EXECUTES ISQL AFTER QUEUING UPDATE COMMANDS AND
*/
/* AN ISQL EXIT COMMAND ON THE PROGRAM STACK. COMMANDS ARE ALSO
*/
/* STACKED TO DISPLAY AND PRINT THE CHANGES MADE.
*/
EMPLIST = ""
/* WILL CONTAIN LIST OF EMPNO’S WHOSE */
/* TOTAL SALARY HAS CHANGED
*/
DO COUNT = 1
DO FOREVER
SAY "ENTER EMPNO
(ENTER ’END’ WHEN DONE)"
PULL E
IF E = ’END’ THEN LEAVE COUNT
SAY ENTER JOB
/* USER ENTERS UPDATE INFORMATION*/
PULL J
/* (DATA IS CHECKED FOR VALID
*/
SAY ENTER CHANGE TO SALARY
/* NUMBERS)
*/
PULL CTS
IF DATATYPE(E,W) & DATATYPE(J,A) & DATATYPE(CTS,N) THEN LEAVE
ELSE SAY "DATA ENTERED INCORRECTLY--TRY AGAIN"
END
UPD.COUNT = "UPDATE EMPLOYEE SET SALARY = SALARY + "CTS,
" WHERE EMPNO = "E" AND JOB = "J
/* UPDATE COMMANDS ARE
*/
EMPLIST = EMPLIST", "E
/* HELD IN AN ARRAY
*/
END
IF COUNT = 1 THEN EXIT
/* NO UPDATES?
*/
ELSE EMPLIST = SUBSTR(EMPLIST,3)
/* REMOVE FIRST COMMA */
QUEUE COMMIT WORK
QUEUE SET AUTOCOMMIT OFF
DO N = 1 TO COUNT-1
/* PLACE UPDATE COMMANDS
*/
QUEUE UPD.N
/* ON PROGRAM STACK
*/
END
QUEUE "SELECT EMPNO, JOB, SALARY FROM EMPLOYEE -"
IF COUNT > 2
THEN QUEUE "WHERE EMPNO IN ("EMPLIST") -"
ELSE QUEUE "WHERE EMPNO = "EMPLIST" -"
/* QUERY, FORMATTING,
*/
QUEUE "ORDER BY JOB, EMPNO"
/* AND PRINT COMMANDS
*/
QUEUE FORMAT GROUP JOB
QUEUE FORMAT SUBTOTAL SALARY
QUEUE "FORMAT TTITLE ’SUMMARY OF CHANGES IN SALARY TOTALS’"
QUEUE DISPLAY
QUEUE PRINT COPIES 3
QUEUE END
QUEUE COMMIT WORK
QUEUE EXIT
/* THE ISQL EXECUTION
*/
EXEC ISQL
/* TO EXECUTE THE COMMANDS THAT WERE STACKED
*/
Figure 37. ISQL EXEC for Updating the EMPLOYEE Table Outside of an ISQL Session
Avoid using commands that would result in ISQL issuing a message that requires
a response. For example, SET AUTOCOMMIT OFF will cause message ARI7602D
to be issued when the EXIT command is entered, and this message requires a
response of either COMMIT or ROLLBACK. Because of the interactive design of
ISQL, the response must be entered by the user, and will not be accepted from the
command stack.
Programmed Query Functions: If neither stored queries nor ISQL routines are
appropriate, you can program query functions. Their primary advantage is
application tailoring of the end user interface—that is, the application controls the
Chapter 8. Application Design Considerations
155
user syntax for requesting data and the output format for displaying results.
Program a query function if an application-specific interface must be provided to
end users.
Another advantage is their ability to apply procedural logic. Unlike stored queries
which support only a single SQL statement, or ISQL routines which support a
fixed sequence of statements, programmed query functions can run different
statements or statement sequences based on the results of previous statements or
function input.
When designing a programmed query function, you may want to consider using
the SQL Dynamic Statement support. With this, the program can translate queries
in an application-specific syntax to SQL statements, which are then dynamically
compiled and processed. Such a program can provide many query functions with
minimal coding.
For even more sophisticated applications, you can use extended dynamic
statements to code preprocessors for programming languages that are not
supported by the application server. See the DB2 Server for VSE & VM Application
Programming manual for information.
Report Writing Capabilities
Reports can be produced through ISQL, the DBS Utility, or an application program.
Report Writing Using ISQL
When ISQL terminal users obtain query results through a SELECT statement, they
can create reports from them using the FORMAT command. This command
provides the following:
v Titles
Both top and bottom titles can be created. If no top title is specified, a default is
provided that consists of the first 100 characters of the SELECT statement that
provided the query results. The bottom title defaults to blanks.
v Totals
Both subtotals and totals can be provided for desired columns.
v Column Separation
The characters to be used to separate columns can be specified.
v Outlining
If outlining is specified, successive duplicate values for a desired column are not
repeated unless they start a new screen (or a new page for printed reports).
v Column Characteristics
Users can control such things as:
- The number of decimal places for numeric columns
- The width of a column
- Whether leading zeros are displayed
- The column heading
- The inclusion and exclusion of columns.
For more information on formatting reports, see the DB2 Server for VSE & VM
Interactive SQL Guide and Reference manual.
To obtain copies of a report, a user enters an ISQL PRINT command. This
command allows you to specify the number of copies desired and the output
printer class to be used. The printed reports are dated and the pages numbered.
156
Database Administration
In VSE, if you enter a PRINT command, but the printer is busy, ISQL will send
you a message. Along with this message, ISQL will give you the option of:
v Retrying
v Ending the attempt to print
v Queueing your request until the printer is available.
When the printer is free, ISQL displays the message “THE PRINT IS IN
PROGRESS”.
Note: Some terminals support a copy key that, when pressed, causes a screen
image to be printed on the CICS local printer. Such support does not follow
any queue protocol and, if you use it while ISQL PRINT is in progress, the
screen image may be interspersed with the ISQL PRINT output.
In VM, the results of an ISQL PRINT command are sent to the user’s virtual
printer. The default print location will be wherever the user normally has his or
her output printed. However, the user can change the destination of the files by
going into CMS Subset mode, entering the SPOOL and TAG commands to route
the output elsewhere, then returning to ISQL (by entering the RETURN command)
and entering the PRINT command.
Example: The following command, entered in CMS, will cause the printer output
to be sent to the user’s virtual reader:
CP SP PRT TO *
A reader file can read into a CMS file for inclusion in the text report.
Routines can be used to generate reports automatically. This is especially helpful
for daily or weekly reports. A routine could issue a SELECT statement, format the
output into the desired report, and print the report.
For more information on ISQL report writing, see the DB2 Server for VSE & VM
Interactive SQL Guide and Reference manual.
Report Writing Using the DBS Utility
The DBS Utility provides a limited report-writing capability through its support for
SQL SELECT statement processing. The DBS SELECT processing writes the results
of a query to the DBS Utility message file (SYSLST print file), with a default of 120
print positions per print line and 60 print lines per print page. These defaults can
be changed through the DBS SET command. Refer to the DB2 Server for VSE & VM
Database Services Utility manual for more information.
Programmed Reports
If an application requires special handling that is not supported by ISQL or the
DBS Utility, it may be necessary to write a program to generate a report. For
example, an application may need to generate output on special forms in a special
format.
You can vary the contents of a programmed report with program variables. Using
the Dynamic Statement support in SQL, you could even vary the tables being
reported. When using the Dynamic Statement support, you would use the SQL
DESCRIBE statement to obtain information on the data being accessed (for
example, column names and column data types).
Chapter 8. Application Design Considerations
157
Programmed Application Capabilities
For complex application requirements that cannot be met by ISQL or DBS Utility
facilities, you must code a program using DB2 Server for VSE & VM facilities.
In addition, you can use DB2 Server for VSE & VM extended dynamic statements
to code your own preprocessor in assembler language to support other languages
that can be mapped to SQL. Extended dynamic statements are explained in the
DB2 Server for VSE & VM Application Programming manual.
EXECs that Use DB2 Server for VM Facilities
Some application functions can be implemented using a combination of VM EXEC
and DB2 Server for VM EXEC facilities. Several useful examples are discussed in
the following sections.
Note: If you are using EXECs to invoke applications or to invoke other EXECs that
access the application server, refer to “SQLRMEND EXEC” on page 261.
Editing Private Tables
The DBS Utility provides facilities for unloading tables to and loading them from
CMS files. While in a CMS file, data can be manipulated by an editor (for example,
XEDIT). Users can take advantage of the combination of these capabilities for
editing data in their tables.
CAUTION: The following technique is not recommended for tables for which
multiple users have UPDATE, INSERT, or DELETE privileges. It
assumes that only the user doing the editing has update capabilities.
Figure 38 on page 159 shows an EXEC that can be used to edit private tables.
158
Database Administration
/*
EDITTAB EXEC
*/
/* THIS EXEC PROGRAM USES THE SQLDBSU EXEC TO UNLOAD A USER’S TABLE*/
/* INTO A CMS FILE FOR EDITING WITH XEDIT. AFTER EDITING, THE USER*/
/* HAS THE OPTION OF REPLACING THE TABLE WITH THE EDITED CMS FILE,
*/
/* AND THEN MAY HAVE THE TABLE DISPLAYED BY ISQL. TWO CMS FILES
*/
/* MUST PREVIOUSLY HAVE BEEN CREATED WHICH CONTAIN COMMANDS TO
*/
/* SQLDBSU; THEIR FILENAMES MUST BE THE SAME AS THE NAME OF THE
*/
/* TABLE, TRUNCATED TO 8 CHARACTERS, AND THE FILETYPES MUST BE
*/
/* ’UNLD’ AND ’REPL’ (EXAMINE CLOSELY THE EXAMPLE GIVEN FOR THE
*/
/* EMPLOYEE TABLE.)
*/
SIGNAL ON ERROR
SAY WHICH TABLE WOULD YOU LIKE TO EDIT?
PULL TNAME
FN = STRIP(LEFT(TNAME,8))
"STATE" FN "UNLD"
/* VERIFIES EXISTENCE OF */
"STATE" FN "REPL"
/* DBSU CONTROL FILES
*/
"FILEDEF WORKFILE DISK" FN "TABLE (LRECL 80 RECFM FBA"
"EXEC SQLDBSU IN("FN "UNLD) PR(PRINTER)"
/* UNLOAD TABLE */
"XEDIT" FN "TABLE"
/* FOR EDITING
*/
SAY "DO YOU WANT TO REPLACE THE" TNAME "TABLE? (Y OR N)"
PULL ANSWER1
IF ABBREV(NO,ANSWER1,1) THEN EXIT
"EXEC SQLDBSU IN("FN "REPL) PR(PRINTER)"
/* TABLE IS REPLACED */
SAY WOULD YOU LIKE TO DISPLAY THE NEW TABLE? (Y OR N)
PULL ANSWER2
IF ABBREV(NO,ANSWER2,1) THEN EXIT
QUEUE "SELECT * FROM" TNAME
/* MOVE ISQL COMMANDS INTO THE */
QUEUE DISPLAY
/* PROGRAM STACK
*/
QUEUE END
QUEUE EXIT
EXEC ISQL
EXIT
/* END OF PROGRAM */
ERROR:
/* ERROR HANDLING
*/
SAY "UNEXPECTED EDITTAB TERMINATION
RETURN CODE:" RC,
" LINE:" SIGL
Figure 38. Example EXEC for Editing a Private Table
Here, the EDITTAB EXEC prompts the user for the name of the table to be edited
(only simple names are accepted), then uses that name to verify the existence of
DBS command files needed to support editing of the table. The work file used for
the CMS file version of the table is then defined (in the FILEDEF WORKFILE
command). The DBS Utility (SQLDBSU) is then initiated to unload the table to the
work file. Once the table has been unloaded, XEDIT is initiated to edit the work
file.
On completion of the XEDIT session, the user is asked if the table is to be replaced
in the database by its edited version. If the answer is yes, the DBS Utility is
initiated to perform the REPLACE operation.
Finally, the EXEC asks the user if the new version of the table should be displayed.
If the answer is yes, the table is displayed using ISQL.
Chapter 8. Application Design Considerations
159
For the EDITTAB EXEC to work, two DBS command files must be established for
each table that is to be supported. Figure 39 and Figure 40 show the DBS command
files needed to edit a user’s version of the EMPLOYEE sample table.
Note: These examples assume that the user’s version of the EMPLOYEE table,
userid.EMPLOYEE, has already been created.
The command file in Figure 39 unloads the EMPLOYEE table to a file that has been
defined as WORKFILE. (This is the file defined in EDITTAB as a CMS file with a
file name using the first eight characters of the table name, and a file mode of
TABLE.) The information following the SELECT statement identifies the location in
the output file (WORKFILE) where the data for the columns in the select-list
should be placed.
COMMENT ’EMPLYEE UNLD A’
COMMENT ’DATAUNLOAD JOB FOR EDITING A USERS EMPLOYEE TABLE’
DATAUNLOAD
SELECT * FROM EMPLOYEE ORDER BY EMPNO;
EMPNO
5-10 CHAR
FIRSTNME 12-23 CHAR
MIDINIT 25 CHAR
LASTNAME 27-41 CHAR
WORKDEPT 43-45 CHAR
PHONENO 47-50 CHAR
HIREDATE 52-61 CHAR
JOB 63-70 CHAR
EDLEVEL 72-73 CHAR
SEX 75 CHAR
BIRTHDATE 77-86 CHAR
SALARY 88-96 CHAR
BONUS 98-106 CHAR
COMM 108-116 CHAR
OUTFILE (WORKFILE)
Figure 39. DBS Unload Command File for Editing EMPLOYEE Table
COMMENT ’EMPLYEE REPL A’
COMMENT ’DATALOAD JOB FOR REPLACING AN EDITED EMPLOYEE TABLE’
DELETE FROM EMPLOYEE;
DATALOAD TABLE (EMPLOYEE)
EMPNO
5-10 CHAR
FIRSTNME 12-23 CHAR
MIDINIT 25 CHAR
LASTNAME 27-41 CHAR
WORKDEPT 43-45 CHAR
PHONENO 47-50 CHAR
HIREDATE 52-61 CHAR
JOB 63-70 CHAR
EDLEVEL 72-73 CHAR
SEX 75 CHAR
BIRTHDATE 77-86 CHAR
SALARY 88-96 CHAR
BONUS 98-106 CHAR
COMM 108-116 CHAR
INFILE (WORKFILE)
COMMIT WORK;
Figure 40. DBS Command File for Replacing Edited EMPLOYEE Table
160
Database Administration
The command file in Figure 40 deletes the existing rows of the user’s EMPLOYEE
table, and loads the edited WORKFILE version of the table into it. The information
between the DATALOAD table statement and the INFILE statement identifies the
columns in the table to be loaded with the data from the input file at the specified
locations. All the EMPLOYEE table columns here will be loaded with data from the
WORKFILE input file. For example, data in positions 5 to 10 of the file will be
loaded into the EMPNO column.
Editing Routines
Another variation of the EDITTAB EXEC would be an EXEC that edited only a
portion of the user’s table. To do this, the DATAUNLOAD and DATALOAD
command files must be selective about which rows are unloaded and replaced.
This can be done using a subquery on the DATAUNLOAD, and a WHERE clause
on the DELETE statement.
Figure 41 shows an example where the EXEC unloads an ISQL routine from the
user’s ROUTINE table, invokes XEDIT on the unloaded rows, and gives the user
the option of reloading the edited routine back into the ROUTINE table.
/* EDITROUT EXEC
*/
SIGNAL ON ERROR
SAY WHICH ROUTINE WOULD YOU LIKE TO EDIT?
PULL RNAME
"STATE" RNAME "UNLD"
"STATE" RNAME "REPL"
"FILEDEF WORKFILE DISK" RNAME "TABLE (LRECL 100 RECFM FBA"
"EXEC SQLDBSU IN("RNAME "UNLD) PR(PRINTER)
"XEDIT" RNAME "TABLE"
SAY "DO YOU WANT TO REPLACE THE" RNAME "ROUTINE? (Y OR N)"
PULL ANSWER1
IF ABBREV(NO,ANSWER1,1) THEN EXIT
"EXEC SQLDBSU IN("RNAME "REPL) PR(PRINTER)"
SAY WOULD YOU LIKE TO DISPLAY THE ROUTINE? (Y OR N)
PULL ANSWER2
IF ABBREV(NO,ANSWER2,1) THEN EXIT
QUEUE "SELECT * FROM ROUTINES WHERE NAME=’"RNAME"’ -"
QUEUE "ORDER BY SEQNO"
QUEUE DISPLAY
QUEUE END
QUEUE EXIT
EXEC ISQL
EXIT
ERROR:
SAY "UNEXPECTED TERMINATION OF ROUTINE EDIT
RETURN
CODE:" RC,
"
LINE:" SIGL
Figure 41. Example EXEC for Editing Routines
COMMENT ’XMPLROUT UNLD A’
DATAUNLOAD
SELECT NAME, SEQNO, COMMAND FROM ROUTINE
WHERE NAME = ’XMPLROUT’;
NAME
1-8
CHAR
SEQNO
10-15
CHAR
COMMAND 17-95
CHAR
OUTFILE (WORKFILE)
Figure 42. Example DBS DATAUNLOAD Command File for Editing Routine XMPLROUT
Chapter 8. Application Design Considerations
161
COMMENT ’XMPLROUT REPL A’
DELETE FROM ROUTINE
WHERE NAME = ’XMPLROUT’;
DATALOAD TABLE (ROUTINE)
NAME
1-8
CHAR
SEQNO
10-15 CHAR
COMMAND 17-95 CHAR
INFILE (WORKFILE)
COMMIT WORK;
Figure 43. Example DBS Command File for Replacing Routine XMPLROUT
Application Development Capabilities
Complex applications that are coded as programs (as opposed to DBS Utility input
files or ISQL sessions) could involve many programs that operate on many tables.
This section discusses how to make large-scale application development easier.
Data Prototyping
The DB2 Server for VSE & VM system can be used by application developers to
prototype data designs and implement them during the application development
process. In particular, in DB2 Server for VSE, the ability to dynamically CREATE,
ALTER, and DROP tables from an online, interactive environment allows a
developer to experiment with different design alternatives. A developer can then
exploit the DB2 Server for VSE & VM catalog tables and explanation tables for
documentation and analysis of data designs.
The DB2 Server for VSE & VM facilities that should be considered for data
prototyping activities are identified in the following sections.
Modeling Data Designs: ISQL or the DBS Utility can be used to enter table, view
and index definitions for validating and testing data design. The interactive
definition through ISQL gives the developer direct feedback on definitional errors.
This feedback not only addresses syntax errors, but also addresses data mapping
errors in view definitions.
Furthermore, if SQL definitional commands are entered through ISQL, these
commands may be saved as stored queries. By saving the definitional commands,
they can be recalled, modified and rerun as needed.
If you are developing your system under VSE/ICCF, you can save definitional
statements by storing them in VSE/ICCF files that are used as input (SYSIPT) to
the DBS Utility.
If you are developing your system under CMS, you can save definitional
statements by storing them in CMS files that are used as input (SYSIN) to the DBS
Utility.
Generation/Loading of Test Data: Tables created for data design purposes can be
loaded with test data using any one of several facilities, depending on the source
of test data and the availability of machine readable versions of the data.
If data exists on a sequential file, or can be put into a sequential file, test data can
be loaded using the DBS DATALOAD command.
If the data does not exist in machine readable form, or cannot be readily converted
to a sequential format, it may be necessary to enter the data by hand. This could
162
Database Administration
be done using the ISQL INPUT command or by building a file for input to the
DBS Utility. In DB2 Server for VM the input file to the DBS Utility is a CMS file. In
DB2 Server for VSE the input file to the DBS Utility is a VSE/ICCF file.
If the data can be found in existing DB2 Server for VSE & VM tables in the
application development database, then data can be copied using the SQL INSERT
statement.
If the data can be found in existing tables in another database, the data can be
moved using either DBS UNLOAD and RELOAD or DBS DATAUNLOAD and
DATALOAD commands. The UNLOAD/RELOAD commands allow easy
movement of data on a table or dbspace level. DATAUNLOAD/DATALOAD lets
you be more selective in what you want to unload, and where you want to load it.
That is, rather than move an entire table, you can use DATAUNLOAD/
DATALOAD to move only certain columns of certain rows of a table.
Design Documentation and Analysis: The catalog tables form a base for design
documentation in as much as the catalog tables can be queried and used to
generate reports. In addition to containing the base information from the SQL
definitional commands, the catalog tables contain useful statistical information and
dependency information.
You can analyze how a given design will perform by using the explanation tables
and the SQL EXPLAIN statement. The EXPLAIN statement can be issued in a
program, from an ISQL terminal, by way of the DBS Utility, or through an
application program. It lets you get information about the structure and execution
performance of other SQL statements (especially the SELECT statement). Naturally,
execution performance is affected by the data design.
In addition to the EXPLAIN statement, you can get an idea of how well a given
SELECT statement performs by using the ISQL query cost estimate. The query cost
estimate is displayed before the result of a SELECT statement is displayed. It is
also displayed at the end of every SELECT result in ISQL.
The query cost estimate is a relative number (not expressed in real units) that
represents an estimate of the resources used to process the statement. The query
cost estimate displayed in ISQL is not the same number that can be obtained by
using EXPLAIN. The cost estimate displayed by EXPLAIN is the number that is
used internally, while the number displayed by ISQL is the internal number
divided by 1000. This makes the query cost estimate more significant to a terminal
user.
Prototyping Application Function
Application function can be prototyped using ISQL or DBS facilities for testing and
debugging SQL statements to be used by the application.
Using Stored Queries to Test SQL Statements: The stored queries support can be
effectively used to develop SQL statements to be used in an application. The stored
queries could be developed for a test database. By using parameterized stored
queries, you can simulate the use of program variables and test the results of your
SQL statement against various input cases.
Using ISQL Routines to Test SQL Functions: You can develop logical sequences
of SQL statements by using the ISQL routine support. Different routines would be
developed for different paths through the application logic. Again, parameterized
Chapter 8. Application Design Considerations
163
stored routines can be effectively used to simulate program variables and test the
functional results of the application path against various input cases.
You can use the ISQL SET RUNMODE command to aid in testing (and, perhaps,
correcting) the application logic in routines. SET RUNMODE can be coded in the
routine or issued from the terminal. It lets you stop or continue the processing of
an ISQL routine when an error is encountered.
Using the DBS Utility to Test SQL Functions: You can use the DBS Utility to try
out SQL statements or sequences of SQL statements. Note however, you cannot use
it to process parameterized SQL statements or SQL statements having host
variables.
Using the DBS Utility to test SQL statements in VSE has the advantage of keeping
the SQL statements in a VSE/ICCF file that can be modified. When testing is
complete, you can include the VSE/ICCF file in a source code file. If you are using
the DBS Utility under VSE/ICCF, use the VSE/ICCF editor to modify the
command sequence for each test run.
Using the DBS Utility to test SQL statements in VM has the advantage of keeping
the SQL statements in a CMS file that can be modified. When testing is complete,
the CMS file can be included in a source code file. Using the DBS Utility under
CMS, you would have to modify the command sequence using a CMS editor
(XEDIT, for example) for each test run.
As in ISQL, the DBS Utility also provides error handling. Issue the command SET
ERRORMODE to tell the utility how (or if) it is to process SQL and DBS
commands after an error has occurred.
Code Development
For development of application code, VSE/ICCF and CMS provides an interactive
environment in which to build source code files, to run the DB2 Server for VSE &
VM preprocessors, to run the high-level-language compilers, and to test batch (or
VSE/ICCF) applications. In VSE, tests of CICS transactions must be run under the
CICS subsystem.
Building Source Code Files: Using either the VSE/ICCF editor or a CMS editor,
developers can interactively build and edit source statements for their programs.
Developers can use the DBS Utility to test SQL statements. They can copy tested
statements into the source file, and modify them for the appropriate programming
language syntax.
If many applications are to use the same host variables or the same SQL
statements, the developers should consider using the SQL INCLUDE statement,
which causes the preprocessors to include source lines from other source members
(VSE) or CMS files (VM). For example, a developer can place a lengthy SELECT
statement here and use that query in many programs by coding SQL INCLUDE
statements.
Preprocessing Programs under Development: The DB2 Server for VSE
preprocessors can be run under VSE/ICCF, using VSE/ICCF files for source code
input, or under CMS, using CMS files for source code input. The printed output
from the preprocessors, SYSLST for VSE and SYSPRINT for VM, can be directed
under VSE/ICCF to VSE/ICCF files, or under CMS, to CMS files for developer
review from the terminal. Similarly, the punch output, SYSPCH for VSE and
SYSPUNCH for VM, from the preprocessors can be directed under VSE/ICCF to
164
Database Administration
VSE/ICCF files or under CMS, to CMS files, for input to the appropriate compiler.
The preprocessors integrate any external source lines from INCLUDE statements.
Use of the INCLUDE statement does not cause more compilation steps.
When developing a program with embedded SQL statements, run the
preprocessors with a CHECK option. Under this option the preprocessor produces
diagnostics on the SQL in the program, but does not create a package or compiler
input. Therefore initial code development and debugging can be done on just a
skeleton of the final program.
When preprocessing programs under development, application developers can
back up packages that they create with the DBS Utility UNLOAD PROGRAM
command. For information on this command, see the DB2 Server for VSE & VM
Database Services Utility manual.
Testing Application Code under VSE/ICCF: To test user SQL programs the
developer must preprocess, compile, and link-edit the program as a multiple user
mode batch application program. This can either be done under VSE/ICCF, or as a
normal VSE batch job.
Once an application has been preprocessed, compiled, and link-edited, normal
VSE/ICCF procedures for application execution can be used. The only DB2 Server
for VSE requirement for program execution is that the VSE/ICCF control statement
“/OPTION GETVIS=AUTO” must follow the “/LOAD” statement. The program
only needs to be re-preprocessed if the SQL statements that the program runs are
modified.
Testing Application Code Under CMS: To test user SQL programs under CMS,
the application developer would preprocess, compile, and link-edit the program as
a multiple user mode application program, using the usual CMS commands.
Following this, CMS commands for application execution can be entered (for
example, START and RUN).
CMS Subset Considerations
If you develop a DB2 Server for VM application that invokes CMS Subset, be sure
to tell users not to invoke any commands, programs, or EXECs that access the
application server while in CMS Subset mode. (The results would be unpredictable
and error conditions could be generated.) This also applies if they invoke CMS
Subset from ISQL.
Application Database Considerations
The following sections discuss the implications that the various types of
application implementations have on database design.
Database Support for Application Development
When applications are being developed, not all of the data is already predefined.
You will therefore need to set up your database to support both data that exists in
a predefined state, and data that is still under development. For the latter, you
should consider establishing both PUBLIC and PRIVATE dbspaces specifically
defined for application development purposes.
PRIVATE Dbspaces in Application Development
Application developers and database designers will need their own PRIVATE
dbspaces for prototyping data designs and functions on various data designs.
Chapter 8. Application Design Considerations
165
These are better than PUBLIC dbspaces for this activity, because they provide an
environment of less concurrency and no deadlocks. They also have the advantage
of being user-controlled, so application developers need not worry about others
altering the data in their test tables.
Such dbspaces must only be large enough to support sample data in the tables.
Because they are used primarily for functional feasibility testing, they do not have
to support large versions of the tables.
PUBLIC Dbspaces in Application Development
PUBLIC dbspaces are required to model the final database implementation: the
final testing stages and performance testing of applications and data organizations.
They would probably represent the actual production environment, as they allow a
greater concurrency and are DBA-controlled. Tables stored here would hold a
larger, more representative sampling of the data.
Database Support for Query/Report Writing
Queries and report writing also have unique database design requirements. In
particular, the needs of query users for private storage of their data, queries, and
routines must be considered.
Private Query User Data
Many query users will want to be able to store their own private data. To support
this, you need to set up space in the database: how you do so will depend on how
you want to control space usage and table creation. Some variations on this are
described below.
User Control of Own Data: You can enable query users to define and control
their own data by giving them RESOURCE authority, which lets them create tables
in the database. For more information see “Granting Authorities” on page 92. They
will also need PRIVATE dbspaces to hold their tables. Users with RESOURCE
authority can issue their own ACQUIRE DBSPACE statements; however, you will
probably prefer to do this for them. (See “Identifying Dbspace Requirements” on
page 21.)
Having One User Control Data for a Group: If you do not want to give all query
users RESOURCE authority, you could set up a PUBLIC dbspace that would
support the data requirements of a whole group, and give just one user
RESOURCE authority to handle the data requirements of the group.
Having the DBA Control All Data: If you need to tightly control or centralize
control of database usage, you (or someone with DBA authority) can establish
PRIVATE dbspaces for individual users and PUBLIC dbspaces for common data
requirements, but create all tables yourself and restrict access to those tables to
certain users only.
Giving Users Their Own Dbspaces: Users who do not have RESOURCE
authority can still create tables in PRIVATE dbspaces that the DBA has acquired for
them. This still allows the DBA to control how much space each user has in the
database, but gives users the freedom to create whatever tables they choose within
that space. This technique is sometimes called “create table authority.”
166
Database Administration
Storage of ISQL Routines
Query users who want to develop their own ISQL routines will need to have a
ROUTINE table somewhere in the database. Creating this table is typically done by
the DBA when enrolling a new query user on the system. (See “Adding a New
User” on page 81.)
Users who have their own PRIVATE dbspaces can create their own ROUTINE
tables there. If a user’s routines are to be shared by others, then this table should
be created in a PUBLIC dbspace instead, and access to it established through views
(rather than duplicating the table or having the other users qualify the name of the
routine by a user ID).
If users are invoking ISQL to access a non-DB2 Server for VSE & VM application
server and you require a master ISQL profile routine, then you must create a table
called SQLDBA.ROUTINE and store the master routine in this table. See the DB2
Server for VM System Administration and DB2 Server for VSE System Administration
manuals for details on setting up a routine table.
Note: Access to an application server using the DRDA protocol is only possible if
the Distributed Relational Database Architecture (DRDA) facility has been
installed on the application requester and if the application server supports
IBM’s implementation of the DRDA protocol.
System Dbspace Considerations
A final requirement for supporting a query/report writing environment is to define
data for three dbspaces: “PUBLIC”.ISQL, “PUBLIC”.HELPTEXT, and
“PUBLIC”.SAMPLE.
“PUBLIC”.ISQL Dbspace: This dbspace contains the SQLDBA.“STORED
QUERIES” table, which holds the queries stored by ISQL users. Because stored
queries cannot be shared, users must have their own copy of any stored query
they need to run. One or more standard stored queries may be established for each
new user; in addition, some users may also have application programmer-
developed stored queries established for them. Thus, this table may contain
redundancy.
If an installation has many stored queries, the “PUBLIC”.ISQL dbspace may run
out of space. If this happens:
1. Unload the dbspace using the DBS Utility, thus saving its stored queries and
routines in an external file.
2. Drop the dbspace and acquire a bigger one with the same name.
3. Reload the new dbspace with the data that was unloaded from the previous
version.
“PUBLIC”.“HELPTEXT” Dbspace: If the HELP text has been installed, this
dbspace contains the SQLDBA.SYSTEXT1, SQLDBA.SYSTEXT2, and
SQLDBA.SYSLANGUAGE tables, which hold the information displayed in
response to an ISQL HELP command. If you plan to expand the text or topics
covered in the HELP tables, you will need to increase the size of this dbspace. See
“Making the HELPTEXT Dbspace Larger” on page 145.
“PUBLIC”.“SAMPLE” Dbspace: This dbspace contains the sample tables
provided with the DB2 Server for VSE & VM product: SQLDBA.EMPLOYEE,
SQLDBA.DEPARTMENT, SQLDBA.PROJECT, SQLDBA.ACTIVITY,
SQLDBA.EMP_ACT, SQLDBA.PROJ_ACT, SQLDBA.CL_SCHED and
Chapter 8. Application Design Considerations
167
SQLDBA.IN_TRAY.
168
Database Administration
Application Implementation Considerations
The considerations regarding the implementation of applications are discussed
here.
VSE Batch/Interactive Application Considerations
The considerations pertinent to user-written application programs for the
batch/ICCF environment and to DBS Utility applications are security, recovery and
error handling.
Batch/ICCF Application Security
DB2 Server for VSE data protection applies to programs written for batch and
VSE/ICCF execution (and to the data itself). In particular, when a user
preprocesses a program, the database manager checks the authority and privilege
of that user on tables and views used in the program. When a user runs a
preprocessed program, the database manager checks only for RUN privilege of that
user on the program.
When a program is preprocessed, users with the appropriate data authority for the
program functions must supply a user ID and password. Users are verified by the
supplied password, and their authority is checked for each SQL request embedded
in the program. On successful completion of the preprocessor job, a user becomes
the owner of the application program, and can control who else can run it by
issuing a GRANT RUN statement.
Note: In a batch/interactive environment, a GRANT statement is typically entered
through a DBS Utility execution. For program execution, the batch and
VSE/ICCF applications must be written to establish a user connection to the
DB2 Server for VSE application server through the SQL CONNECT
statement. This statement establishes the user of the program and checks
that user’s authority to run the program and to perform any interpretive
SQL functions in the program.
For example, security-sensitive applications can be written to require that the user
ID and password of the program runner be supplied through control statements or
terminal input at execution time. The programs should be written to read this
information into the host program variables referenced in a DB2 Server for VSE
CONNECT statement.
You can bypass this security facility by writing the application so that it supplies
the user ID and password independent of the actual user of the application. If you
do so, you must code the CONNECT statement in the application and grant RUN
authority to the user IDs to be generated.
In general, you can use program authorization as a means of controlling access to
data. If the end user of the application has access to the data only through specific
application programs, the user can do only what the application is programmed to
do.
Batch/ICCF Recovery
All batch/ICCF programs should explicitly issue COMMIT WORK and
ROLLBACK WORK statements as required, rather than relying on the implicit
COMMIT and ROLLBACK functions of the database manager. The rules
determining whether to do an implicit COMMIT WORK or an implicit ROLLBACK
Chapter 8. Application Design Considerations
169
WORK are rather complex. By coding explicit COMMIT WORK and ROLLBACK
WORK statements, you can determine what work is done by a batch application
from the last statement completed.
Batch/ICCF Error Handling
In general, if a batch/ICCF application is terminated abnormally, the database
manager backs out all uncommitted changes. If it terminates normally, the
database manager commits changes not explicitly committed by the application.
An application program can be coded to handle negative SQLCODEs by using
SQL WHENEVER statements, as described in the DB2 Server for VSE & VM
Application Programming manual.
DBS Utility Application Security
The DBS Utility input must include an SQL CONNECT statement before any other
SQL statement or UNLOAD, RELOAD or DATALOAD commands are issued. The
only exception to this rule is when the DBS Utility is called by a user application
that has already issued an SQL CONNECT statement.
A DBS input (SYSIPT) file can contain multiple SQL CONNECT statements. This
capability can be used to write one DBS input file that performs operations for
multiple users. The operations to be performed for any one user are preceded by
an SQL CONNECT statement.
DBS Utility Application Recovery
The DBS Utility applications can control commit processing through the
appropriate use of the DBS SET AUTOCOMMIT command and SQL COMMIT
WORK and ROLLBACK WORK statements. By setting AUTOCOMMIT ON, DBS
will automatically issue an SQL COMMIT WORK after each SQL or DBS command
(except for certain statements that imply commit processing is not appropriate, like
ROLLBACK WORK). By setting AUTOCOMMIT OFF, no commit processing will
be done unless explicitly requested by an SQL COMMIT WORK statement or
when the input command file is exhausted.
For batch processing, you typically run with AUTOCOMMIT set OFF, so commit
points are explicitly identified by SQL COMMIT WORK statements. This is the
default for DBS processing.
When running DBS in an interactive fashion (under VSE/ICCF with /DATA
INCON specified), you should run with AUTOCOMMIT set ON. If you run with
AUTOCOMMIT set OFF, shared data is not available to other users while you are
thinking about command responses or entering commands, unless the other users
are using isolation level UR.
DBS Utility Application Error Handling
DBS terminates execution of commands in the input (SYSIPT) file and performs a
ROLLBACK WORK if it encounters an error on any of the commands. However,
DBS will read all the input records and provide diagnostics for the remaining
commands. In addition, you can include SET ERRORMODE OFF commands to
cause DBS to stop processing the input in error mode (that is, resume execution of
commands in the input file).
The SET ERRORMODE OFF capability is useful for execution of independent
command sequences in the same input file. Each independent sequence of
commands would be preceded by a SET ERRORMODE OFF command.
170
Database Administration
Another use of the SET ERRORMODE command is when running DBS under
VSE/ICCF in conversational mode (/DATA INCON). In this case, you should use
the SET ERRORMODE CONTINUE command. If a normal SQL error is
encountered on any command entered, the DBS utility processes subsequent
commands from the terminal user. It goes into error mode processing only if the
error is fatal. This saves the terminal user from having to enter SET ERRORMODE
OFF every time a minor mistake is made.
Online CICS/VSE Transaction Considerations
Online Application Security
Security design in an online (CICS) transaction processing environment should
consider the facilities of the CICS subsystem as well as those offered by the DB2
Server for VSE application server. In particular, the CICS subsystem provides
facilities for performing user verification (signon), and for controlling user
authority to run CICS transactions. SQL programs written for execution in the
CICS programming environment can be designed to take advantage of these
facilities.
User Identification and Verification: User identification and verification for CICS
SQL transactions can be handled in one of the following ways:
v CICS Signon
The CICS SQL transactions do not have to contain SQL CONNECT statements. If
a CONNECT statement is not present in the transaction, the DB2 Server for VSE
online support attempts to obtain the CICS signon userid using the EXEC CICS
ASSIGN command. The user ID of the program user is assumed to be this
signon ID.
v DB2 Server for VSE CONNECT
If your CICS users do not go through a signon process for access to CICS
transactions, user identification and verification can still be accomplished
through the SQL CONNECT statement in the individual transaction programs.
However, the transaction would have to obtain a user ID and password in order
to issue a CONNECT statement.
Note: This does not apply in a DRDA environment.
v No user identification/verification
For the online (CICS) environment, you can choose to run without requiring any
user identification or verification, by treating all CICS users as though they had
the same user ID and authority. To do this, you would identify the default CICS
user ID when starting the DB2 online support (see “CICS Transaction
Environment” on page 103).
Note: This does not apply in a DRDA environment.
You can design each CICS transaction to handle user verification and identification
differently. Some may require the user to sign on to the CICS subsystem, others
may issue SQL CONNECT statement, and yet others may assume the default user
ID for CICS users.
Note: In cases where the CICS subsystem does user verification or the user runs
under the default user ID, it is not necessary to have the user defined to the
DB2 Server for VSE application server through the GRANT statement
(CONNECT authority).
Chapter 8. Application Design Considerations
171
Online Application Recovery
Application recovery processing for CICS SQL transactions is coordinated between
the CICS subsystem and the DB2 Server for VSE database manager. In particular, a
CICS SYNCPOINT request causes an SQL COMMIT WORK to be issued to commit
table information as well as CICS information. Similarly, a CICS SYNCPOINT
ROLLBACK request causes an SQL ROLLBACK WORK to be issued. The reverse
is also true: a COMMIT WORK causes a CICS SYNCPOINT to be issued and an
SQL ROLLBACK WORK causes a CICS SYNCPOINT ROLLBACK to be issued.
In addition, CICS end-of-task processing is coordinated with the DB2 Server for
VSE database manager to assure that transaction processing is properly committed
or rolled back, depending on the conditions under which the transaction ended.
For supporting recovery processing for CICS SQL transactions, the CICS subsystem
must be generated with the Dynamic Transaction Backout Program (DBP
parameter), and individual transactions must be installed with the CICS DTB=YES
option.
Pseudoconversational Transactions
Pseudoconversational transactions must not be run on the same terminal with
ISQL while ISQL has “timed out”.
Application Development Considerations
Loading Data into Test Dbspaces
You can load test data in any of these ways:
v
DBS Utility UNLOAD/RELOAD
Live data can be unloaded and then reloaded back into the system, but directed
at the test dbspace. The tables can be created new (using the NEW option of the
RELOAD command); or, if the tables already exist, then all rows can be deleted
and the unloaded data inserted using the PURGE option of the RELOAD
command. If an application development environment exists where the test data
is on a separate test database, then DBS UNLOAD/RELOAD can be used to
load data from one database to another.
v
INSERT with subselect
Live data can also be loaded into a table by using the INSERT with subselect:
INSERT INTO TESTTABLE
SELECT * FROM USERID.LIVETABLE
WHERE ...
This approach to loading test data has the advantage of using a WHERE clause
for defining a sample from the live data rather than the entire table. An INSERT
with subselect can be entered through ISQL, the DBS Utility, or an application
program. An INSERT with subselect can be used to convert data from one data
type to another; the specific limits on data type conversion depend on the
number of conversions and the data types involved. Refer to the DB2 Server for
VSE & VM Application Programming manual for the general restrictions on data
type conversions.
v
DBS Utility DATAUNLOAD/DATALOAD
The DBS Utility DATALOAD command may be used to input test data for new
tables. The input to the DATALOAD command is from SYSIPT or sequential
(SAM) files in VSE, from SYSIN or sequential (CMS) files in VM. A subset of all
the data on the input sequential file can be loaded by using the “IF POS” clause
of the DATALOAD command. For example, suppose that on an input sequential
172
Database Administration
file containing customer information, the telephone number data is in positions
28-39 and positions 28-30 contain the area code. You could then load just the 555
area codes into the table TESTCUST by specifying the following DATALOAD
command format:
DATALOAD TABLE (TESTCUST)
IF POS (28-30)=’555’
The test table would contain a subset of the actual data that the application will
use.
You can get even more selectivity by using the DATAUNLOAD command to
create the sequential file. This is especially useful if the data exists in tables, but
not input files.
As the DATAUNLOAD command incorporates an SQL SELECT statement, you
can be highly selective about what data you wish to unload. Furthermore,
because the DATALOAD command can be used to reload the data, you can
significantly restructure the data when you load it. That is, DATAUNLOAD/
DATALOAD is not restricted to a table-to-table or dbspace-to-dbspace data
movement. For example, you can unload data generated from a subquery, and
then load only a portion of the result into a completely different table. This
facility is useful for rapidly getting data into a new design prototype.
Use of Synonyms in Application Development
To simplify coding and testing of SQL statements that will eventually reference the
live data, a developer may use the SYNONYM capability. Under the user ID
established for a developer, synonyms would be defined so table references would
translate to test tables when preprocessed under the developer’s user ID.
For example, an application needs to be written that will access the PAYROLL
table. The fully qualified table name is LOCALDBA.PAYROLL, having been
created for the user ID LOCALDBA. A developer, with userid = DEV, has a
temporary version of the payroll table called DEV.TESTPAY. Because the SQL
statements refer to the table name PAYROLL, the developer creates the name
PAYROLL as the synonym for TESTPAY:
CREATE SYNONYM PAYROLL FOR TESTPAY
Now all references to PAYROLL made from userid DEV translate to TESTPAY.
When it comes time to switch to the live data, the program will be preprocessed
under the userid of the creator of the PAYROLL table (LOCALDBA in this case), so
that the program will access the PAYROLL table no matter who runs it.
Note: An exception to the above commands are applications that require Dynamic
Statement Support (including Extended Dynamic Statements). For those
dynamic statements, table references are translated based on the userid of
the user that runs the program (the userid specified in the CONNECT
statement). Execution of any test program against live data can be prevented
by not granting run authority to anyone who does not have the appropriate
synonyms defined.
Testing SQL Statements
Using ISQL and Stored Queries
Before actually coding an application, the programmer may test/develop SQL
statements to be embedded in the program by using ISQL against test data. The
programmer would develop a set of SQL statements using the stored query
Chapter 8. Application Design Considerations
173
facilities of ISQL. As each statement is formed, it would be run against the test
data to verify expected results. Syntax and execution errors will be caught and
error messages returned. The HELP facility of ISQL could be used to obtain
detailed error descriptions and SQL statement descriptive information. For user
logic errors on non-query statements (such as INSERT or UPDATE), the
programmer can issue SELECT statements to inspect the effects of the tested
statements.
Maintaining Database Consistency Under ISQL
To maintain a consistent state of the test database when using non-query
statements, the programmer will want to issue SET AUTOCOMMIT OFF from the
ISQL terminal, so that any changes that the test statements may make to the test
database can be undone with a ROLLBACK WORK.
Using ISQL Stored Queries for Testing SQL Statements
To place an SQL command in the stored queries table without executing it first, the
programmer should use the ISQL HOLD and STORE commands under
AUTOCOMMIT ON mode. For example:
HOLD DELETE FROM PAYROLL WHERE NAME = ’SMITH’
STORE DELETE1
The HOLD command will place the command in the SQL command buffer of
ISQL, but will not run it. Then the STORE command will place the contents of the
SQL command buffer into the “STORED QUERIES” table. Once the command is in
the “STORED QUERIES” table, the programmer can run it while controlling his
own logical unit of work (under SET AUTOCOMMIT OFF), so that the changes
done by the command can be rolled back.
Using ISQL Routines to Test SQL Statements
As each command is corrected and verified, it can be stored away as
parameterized stored SQL command in a ROUTINE table. The programmer would
use stored command parameters where the program will have program variables.
The commands can be placed in the ROUTINE table in the same logical order that
they will be run in the application program. In this manner a prototype will be
created that will demonstrate sample application usage. End users can then see the
proposed system in operation before it is coded. Design flaws can be more easily
corrected at this early phase.
Note: Stored queries and synonyms cannot be shared, but routines can be shared.
You can run another user’s routine if you have obtained the SELECT
privilege (through a GRANT command) on that user’s ROUTINE table. Care
must be taken in running another user’s routines however, because any
stored SQL commands or synonyms used in a routine will not be recognized
unless you have also defined them yourself.
Checking Application Code
Using the Preprocessor CHECK Option
After debugging and testing the SQL commands on ISQL, the application
programmer would then code the application. Having developed the source
program with embedded SQL commands, the next step is to run the program
through the appropriate DB2 Server for VSE & VM preprocessor. If the
programmer is unsure of the SQL commands embedded in the program, he can
run the preprocessor with the CHECK option. The SQL commands will be
preprocessed and error messages will be output to SYSLST in VSE and SYSPRINT
in VM, but a package is not created and no modified source will be produced.
174
Database Administration
Running the preprocessor without the CHECK option will generate a package and
the modified source to be used as input to the desired compiler.
Use of ROLLBACK WORK During Application Execution
After the program has been preprocessed and compiled, the final step in the
testing cycle would be execution against the test data. To ensure a consistent test
database the application programmer should place a ROLLBACK WORK statement
in his application that will undo any changes that the program may make during
execution before the program terminates. This ROLLBACK WORK statement may
be left in the application for the first few runs on the live data. Once the program
is operating correctly on the live data, the ROLLBACK WORK statement can be
removed (or replaced with a COMMIT WORK statement).
Query/Report Writing Considerations
User Identifiers (Userids) for Query Users
Each query user should be given a unique user identifier and CONNECT authority
on the DB2 Server for VSE & VM application server using GRANT statements, as
described under “Adding a New User” on page 81. Multiple users can use the
same DB2 Server for VSE & VM userid, but this can result in conflicts between the
users’ access to the system and to data.
ISQL users should be careful if there is more than one user using the same userid.
In particular:
v Stored queries should be stored with names that identify the owner. This can be
done by using the owner’s initials as a prefix to the name.
v Multiple users with the same userid will experience severe contention if they try
to update (insert or delete) data in a PRIVATE dbspace owned by their common
userid. In such cases, PRIVATE dbspaces should not be used unless the access to
the data is read only.
v When multiple users use the same userid, the DB2 Server for VSE & VM
security facilities cannot distinguish the individual users. All users using a
common userid will share the same access privileges to the database.
v Only one PROFILE ROUTINE can exist for each unique userid. If multiple users
(using the same userid) require different profile routines, they can create unique
routines (again, perhaps appending their initials to the routine name). This
unique routine can then be run either as part of ISQL signon or at any time after
signon.
Application Independence with CMS Work Units
Applications that use multiple CMS Work Units can:
v Start a logical unit of work.
v Invoke other application programs in new CMS Work Units.
v Run these application programs independently of one another. When one
program commits or rolls back work, it does not affect the work in other CMS
Work Units.
v Access a different application server in each CMS work unit. The logical unit of
work can be on the same application server or on different application servers.
The application server can be a DB2 Server for VM or non-DB2 Server for VM.
For more information on CMS Work Units, see the DB2 Server for VSE & VM
Application Programming manual.
Chapter 8. Application Design Considerations
175
Note: Access to an application server that is not DB2 Server for VM is only
possible if the Distributed Relational Database Architecture (DRDA) facility
has been installed on the application requester and if the non-DB2 Server for
VM application server supports IBM’s implementation of the DRDA
protocol.
Application Maintenance Considerations
DB2 Server for VSE & VM users and programs are independent of the physical
storage of data. This means that procedures and programs need not be changed
when their information is updated or reorganized, and logical changes can be
made to the data without requiring expensive rewrites, retraining, or
reorganization of the supporting application system.
This data independence improves productivity, by enabling users and programs to
concentrate on the application instead of on details such as how data is stored,
which users share it, or what changes have been made to it. It also means that new
applications may be written with little initial regard for performance
considerations: much of the optimization is handled automatically, and
user-directed optimization can be done later without significant effect on the
applications using the data. In addition, one user may change the format or
organization of some data with minimal effect on other users who share it.
Data Administration Support
The DB2 Server for VSE & VM product supports a powerful query capability, as
well as an easy-to-learn interactive support system (ISQL). The DBA can use these
functions to scan stored data to determine when reorganization of data is
appropriate, decide how to logically organize the data, audit its consistency and
accuracy, and assess the impact of changes.
Another way to examine data is through the catalog tables, which are internally
updated as a result of many SQL statements. For example, a CREATE TABLE
statement causes a new entry in the SYSTEM.SYSCATALOG table; each column in
the new table results in an entry in SYSTEM.SYSCOLUMNS. Because the catalog
tables are regular tables (with appropriate security protection), the SQL language
examines them. DBAs can look at these tables to determine table sizes and
statistics, what programs use particular tables or columns, the current data types of
columns in a particular table, various security information, and many other things
required for understanding the status and dependencies of the database.
Refer to the DB2 Server for VSE & VM SQL Reference manual for more information
about the catalog tables along with examples of their use.
To see if a data design is meeting performance requirements, the DBA can use the
EXPLAIN statement to analyze the structure and performance of frequently used
SQL statements, and to determine whether any statements or the data they access
should be redesigned. See the DB2 Server for VSE & VM Application Programming
manual for a description of the EXPLAIN statement.
Data Independence Support
Data Type Changes
A wide range of conversions from one data type to another is supported. This
means that, within reason, the data type or the size of a column may be changed
without requiring changes to the accessing programs. Data is converted on input
176
Database Administration
and output if the data types used in program variables do not match those defined
for the stored data. Data conversions and their restrictions are shown below;
explanatory notes follow.
Target Data Type
FLOAT-
FLOAT-
Source Data Type
CHAR
DATE
DECIMAL
DOUBLE
SINGLE
GRAPHIC
INTEGER
CHAR
YES3
YES6
NO
NO
NO
NO
NO
DATE
YES7
YES
NO
NO
NO
NO
NO
DECIMAL
NO
NO
YES1,4
YES13
YES12,13
NO
YES1,2
FLOAT-DOUBLE
NO
NO
YES1,4,5
YES
YES11
NO
YES1,2
FLOAT-SINGLE
NO
NO
YES1,4,5
YES10
YES
NO
YES1,2
GRAPHIC
NO
NO
NO
NO
NO
YES3
NO
INTEGER
NO
NO
YES1
YES
YES12
NO
YES
LONG VARCHAR
YES3
NO
NO
NO
NO
NO
NO
LONG VARGRAPHIC
NO
NO
NO
NO
NO
YES3
NO
SMALLINT
NO
NO
YES1
YES
YES12
NO
YES
TIME
YES7
NO
NO
NO
NO
NO
NO
TIMESTAMP
YES7
NO
NO
NO
NO
NO
NO
VARCHAR8
YES3
YES6
NO
NO
NO
NO
NO
VARGRAPHIC9
NO
NO
NO
NO
NO
YES3
NO
Figure 44. Data Conversion Chart (Part 1 of 2)
Chapter 8. Application Design Considerations
177
|
||
|
|
|