home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 14 Text
/
14-Text.zip
/
DB2COM.ZIP
/
DB2COM.DOC
Wrap
Text File
|
1993-02-21
|
37KB
|
895 lines
DB2 Application Development
on the Workstation
5 January, 1993
Phil Singleton
Richard Hoffman
Bertha Dodel
This paper contains information on certain aspects of developing
DB2* applications using AD/Cycle* facilities and the OS/2*
Database Manager (Extended Services 1.0 Database Manager) on a
workstation.
The paper states IBM's* strategy for developing host-based database
applications using AD/Cycle, and describes how to deal with certain
features of DB2 and Extended Services 1.0 Database Manager that are
not source compatible. This will not be an exhaustive list of
differences between the two products.
This minor revision of the paper contains only one change - to refer
to the Extended Services 1.0 Database Manager by its proper name.
Special Notices
This document is intended to assist application developers, database
administrators and system programmers in designing and implementing
applications intended for use with DB2, where the application is
developed on the workstation before porting it up to the DB2 host.
IBM field personnel can provide this document to customers who might
benefit from this information.
References in this publication to IBM products, programs, or
services do not imply that IBM intends to make these available in
all countries in which IBM operates.
IBM may have patents or pending patent applications covering subject
matter in this publication. The furnishing of this publication
does not give you any license to these patents. You can send license
inquiries, in writing, to the IBM Director of Commercial Relations,
IBM Corporation, Purchase, NY 10577, USA.
The information contained in this publication has not been submitted
to any formal IBM test and is distributed on an 'AS IS' basis
without any warranty either expressed or implied, including but not
limited to, the implied warranties of merchantability or fitness for
a particular purpose. This disclaimer does not apply if inconsistent
with local law. The use of this information or the implementation of
any of these techniques is a customer responsibility and depends on
the customer's ability to evaluate and integrate them into the
customer's operational environment. While each item may have been
reviewed by IBM for accuracy in a specific situation, there is no
guarantee that the same or similar results will be obtained
elsewhere. Customers attempting to adapt these techniques to their
own environments do so at their own risk.
Trademarks
The following terms, denoted by an asterisk (*) on the first usage
of each in this publication, are trademarks of the IBM Corporation
in the United States and/or other countries:
AD/Cycle MVS
CICS OS/2
DB2 SAA
IBM
Strategy for Database Application Development
The AD/Cycle framework is a framework which covers all the phases for
developing and maintaining applications throughout the entire
development process. The AD/Cycle framework is made up of both the
platform and the tools to perform the full range of application
development activities from enterprise modeling through development.
The strategy for AD/Cycle is to provide these services on a
workstation even when
the target operating environment may be on a host system.
When developing applications that include database services, Extended
Services 1.0 Database Manager can be a valuable tool. Extended
Services 1.0 Database Manager provides strategic functions such as
the SAA* Database CPI, and remote data access. A high degree of
compatibility between Extended Services 1.0 Database Manager and
other IBM database systems allows development of applications for
several target platforms.
The focus of this paper is on a particular instance of this general
capability: to develop and maintain DB2 applications using Extended
Services 1.0 Database Manager, CSP, and the Micro Focus COBOL/2
Workbench.
AD/Cycle's direction is to support coding and unit test activities at
the workstation. This objective is
accomplished through a combination of development and unit test tools
(e.g., Micro Focus COBOL/2 Workbench, CSP AD) and compatible function
(e.g., SAA Database Interface).
SAA Database and IBM's open systems strategy provides a high degree
of consistency between relational databases including Extended
Services 1.0 Database Manager and DB2. In addition to the function
included in SAA, it is IBM's objective to provide the consistency
that will allow development and unit test of most relational database
applications on the workstation which are targeted for running on a
host.
AD/Cycle also will help the developers to system test their
applications in the target execution environment. Such assistance
includes debug tools, regression test drivers, debug analysis, etc.
For workstations that are connected through DDCS/2 to a host running
DB2, the SQL statements can be tested directly on DB2 while the
application is running on the workstation. The application can direct
the
SQL statements to DB2 using distributed database. This can be
advantageous to the installation in several ways. First, the
application can run against existing databases; test databases need
not be created on the workstation. Second, almost all of the DB2 SQL
statements can be used through distributed database, even those not
supported by Extended Services 1.0 Database Manager
Applicability
The statements and suggestions in this paper assume the following
release levels of the applicable products:
~ DB2 V2R3 ~ OS/2 ES 1.0 DBM
~ CSP/370AD V4
~ CSP/370RS V2 ~ CSP/2AD V1
~ CSP/2RS V1
~ Micro Focus COBOL/2 Workbench V2.5 ~ Micro Focus Host Compatibility
Option for IBM Database Manager V1.0
The level of SQL referred to in this document is IBM SAA Database
(SQL) Level 2 and SQL extensions provided by DB2 V2R3.
Compatibility
IBM took major steps toward product compatibility when it launched
SAA. Included in SAA were provisions for a common user interface,
common application program interface, and common communication
support. SAA was subsequently enhanced to provide additional
interfaces, frameworks, and tools to increase compatibility for users
and software developers.
SAA provides a very substantial base of common function for the
application developer, and this includes the common function
available in the programming languages and the database managers. In
particular, the SAA Database Interface provides all of the basic
needs for accessing and manipulating data in relational databases.
Also, the AD/Cycle application development facilities provide all of
the steps necessary in the application development life cycle.
As robust as the SAA capabilities are, IBM knows that even higher
levels of compatibility will further cut development time and
increase programmer productivity. Our strategy is to increase the
compatibility between our products. Each product release should add
to the level of compatibility. IBM will continue to assess customer
needs and will make improvements that make application development
easier and better. The following functional items demonstrate this
strategic commitment.
Recent Compatibility Enhancements in Extended Services 1.0
Database Manager
With the availability of OS/2 Extended Services 1.0, (which includes
Extended Services 1.0 Database Manager) new support has been added
which removes some of the incompatibilities between DB2 and OS/2 EE
DBM. Customers who migrate from OS/2 EE DBM to Extended Services 1.0
Database Manager can take advantage of increased compatibility in the
following areas.
Date/time Arithmetic and Scalar Functions
SQL allows certain addition and subtraction operations on date/time
values such as the subtraction of one date from another to obtain a
date duration. SQL also has certain scalar functions associated with
date/time values such as a DAY function which can extract the day
part out of a timestamp. This capability relieves the application
programmer from having to extract parts of a datetime value, convert
them to numeric data, perform the arithmetic, and convert the result.
DB2 supports date/time data types, date/time arithmetic, and the
date/time functions. Prior to ES 1.0, the OS/2 DBM supported the
date/time data types, but not the arithmetic or functions. This
support is now available in Extended Services 1.0 Database Manager.
Collating Sequence
DB2 runs on an EBCDIC machine. OS/2 runs on an ASCII machine. The
collating sequence of characters is different between these two
encoding schemes. In addition, the OS/2 EE DBM implemented a
collating sequence that merged upper and lower case letters next to
each other. The results of character comparison or ordering was
different between the systems if the character strings included
upper and lower case or non-alphabetic characters. The difference
affected predicate evaluation, order by, and group by processing.
This problem is resolved in Extended Services 1.0 Database Manager by
choosing a collating sequence when the database is created. This may
be the EBCDIC collating sequence or some other collating sequence.
The collating sequence is specified by the 'COLLATE USER collating
string ' clause on the CREATE DATABASE statement (see the example on
page 14).
FOR UPDATE clause
The FOR UPDATE clause is used in the declaration of a cursor when the
cursor will be referred to in a positioned UPDATE statement. It
notifies the database manager that updates might take place and that
the appropriate locks should be obtained. If the clause is not
specified, updates through that cursor are normally not allowed.
DB2 allows an exception to this rule if either of two precompiler
options, NOFOR or STDSQL(86), are in effect when the application is
prepared. The OS/2 EE DBM provides no exceptions, so some
installations running this level of DBM have to include the FOR
UPDATE clause when porting their DB2 applications down to the
workstation. Extended Services 1.0 Database Manager provides a
precompiler option, STDLBL, which allows the same exception as does
DB2. Through the use of these precompiler options, application
source compatibility can be achieved.
NUMERIC Data Type
The NUMERIC data type is quite similar to the DECIMAL data type, but
with enforced precision. Some DB2 applications, probably those
written to conform to ISO-ANSI standards, might have used NUMERIC
instead of DECIMAL. The NUMERIC data type is not supported by the
OS/2 EE DBM but IS supported by Extended Services 1.0 Database
Manager. Applications using the NUMERIC data type can now be ported
to the workstation.
SQLSTATE
SQLCODEs and SQLSTATEs are return codes that are made available to
the application after execution of an SQL statement. Both codes
reflect either successful completion or a particular error condition.
There are hundreds of SQLCODES defined for each DBM and they are
usually not compatible between DBMs. SQLSTATEs, on the other hand,
are designed to be compatible between database managers and some of
them are standardized in the ISO-ANSI standards.
If an application checks only for success or failure, then SQLCODE
can be used compatibly between DBMs. If an application is designed to
check for particular classes or types of errors, then only SQLSTATE
would be reliably compatible between DBMs. DB2 and Extended Services
1.0 Database
Manager implement SQLSTATE, the OS/2 EE DBM does not.
DECLARE TABLE
The DECLARE TABLE statement is used for program documentation and can
provide the DB2 precompiler with information useful for error
checking the SQL statements in an application program.
DB2 applications which contain this statement cannot be ported to the
OS/2 EE DBM. A syntax error will result during program preparation on
the workstation. Extended Services 1.0 Database Manager allows this
statement to pass through the precompiler without generating a syntax
error. Extended Services 1.0 Database Manager does not use this
statement for any error checking.
DECLARE STATEMENT
The DECLARE STATEMENT statement is used for program documentation. It
declares names that are used to identify prepared SQL statements. DB2
applications which contain this statement cannot be ported to the
OS/2 EE DBM. A syntax error will result during program preparation on
the workstation. Extended Services 1.0 Database Manager allows this
statement to pass through the precompiler without generating a
syntax error. Extended Services 1.0 Database Manager does not use
this statement for any error checking.
Incompatibilities Resolved Using CSP
In general, CSP provides a high level of compatibility for
applications that are developed for different operating
environments. CSP application developers need only concern themselves
with the SQL statements used in the applications and ensure that the
SQL statements are compatible between DB2 and Extended Services 1.0
Database Manager
If practical, the CSP application should be written so that CSP
automatically generates the needed SQL statements. CSP will tailor
the SQL statements, if necessary, to the target environment. In this
case, the application can be portable at the source level.
Logical NOT Sign (^)
DB2 allows the ]^ X symbol to be used in SQL. It is not supported in
Extended Services 1.0 Database Manager because the ]^ X symbol is
not a symbol in any of the ASCII code pages currently defined for
OS/2. Adding it to a new set of code pages and providing support for
the new set is a significant change to all of OS/2 and its
components. Instead, it is easier to translate operators containing
the ]^ X symbol to the SAA "<>" characters at precompile time.
CSP automatically converts the ]^ X symbol to the universal NOT
("<>") sign.
Use of the ]^ X symbol should be discouraged. The alternative coding
above is much more widely accepted.
SQL Data Types
Certain character strings and graphic strings (i.e., those having
lengths greater than 254 and 127 respectively) have different
SQLTYPE codes assigned them by DB2 and the OS/2 DBMs. CSP resolves
this difference by changing the OS/2 number to the equivalent DB2
number before returning control to the application.
Incompatibilities Resolved Using the Micro Focus COBOL/2
Workbench
Certain features of the Micro Focus COBOL/2 Workbench assist in
writing compatible applications. In addition, Micro Focus has
released a Host Compatibility Option for use with their COBOL/2
Workbench. This option contains several enhancements that improve
compatibility between DB2 and Extended Services 1.0 Database Manager
applications. A brief description of the useful features in the
Workbench and the Host Compatibility Option follows:
COBOL COMP Declarations
DB2 DCLGEN output contains COBOL COMP declarations which are not
tolerated in OS/2. This means that a DB2 application which contains
DCLGEN output may not port to the workstation without change. In
addition, the COBOL data type for an integer on S/390 and OS/400 is
COMP-4. The COBOL data type for an integer on OS/2 is COMP-5. These
declarations must be changed when
the application is ported between OS/2 and another platform.
The Micro Focus COBOL/2 Workbench can relieve the programmer from
this concern. Small integers can be declared with USAGE BINARY,
COMP, COMP-4, or COMP-5.
Declare Section
A declare section of a program is the place where host variables are
usually declared. The declare section is begun by a BEGIN DECLARE
SECTION statement and ended by an END DECLARE SECTION statement. A
declare section is required by the OS/2 DBMs but not always by DB2.
In DB2, (and except in the C language or whenever the STDSQL(86)
precompiler option is is effect) host variables can be declared
either in or out of a declare section. Some DB2 applications,
therefore, will not easily port to the workstation.
The Micro Focus COBOL/2 Workbench eliminates this problem for the
programmer because the compiler does not require the BEGIN DECLARE
SECTION and END DECLARE SECTION statements.
This feature is enabled using the the SQLDB2 directive.
INCLUDE Text
The SQL INCLUDE statement inserts declarations or code into a source
program during precompilation. This can be very useful for copying
common host variable declarations into the source programs. In OS/2,
the INCLUDE statement only allows specific SQLCA and SQLDA
declarations to be copied into the program. Normally, a DB2
application which is written to INCLUDE source code could not
be ported to the workstation without change.
The Micro Focus COBOL/2 Workbench interprets the statements "EXEC SQL
INCLUDE filename END-EXEC" the same as the COBOL "COPY filename."
statement. The named file may contain any COBOL statements that a
copybook can, including further EXEC SQL statements. This is
compatible with DB2.
Host Variables in Structures
In DB2, applications written in PL/I, C, and COBOL can declare their
host variables as host structures. That is, several variables can
be collected together into a structure, and a single structure name
can be referenced in an SQL statement instead of the individual
variable names. This is an important shorthand method, especially
when a table contains many columns and FETCHing a row would result in
assigning many host variables.
The OS/2 DBMs do not allow host structures. A DB2 application that
contains host structure declarations cannot normally be ported to
the workstation without significant changes.
The Micro Focus COBOL/2 Workbench, however, recognizes host
structures and arrays of indicator
variables and so enables compatible application source code.
The SQLDB2 directive must be set to enable the use of group host
variables and group indicator arrays.
Implicit Connection
An MVS application is implicitly connected to the database manager
identified in a bind option (defaulted to the local DB2 subsystem).
In the OS/2 DBMs a START USING DATABASE statement must be issued to
connect to a database; there is no implicit connection. The START
USING DATABASE statement is not tolerated by DB2. An application
cannot be ported between OS/2 and other platforms without a change.
When using Micro Focus COBOL/2 Workbench, this statement can be
omitted from the application. The proper OS/2 database will
automatically be started by the application if it is compiled using
the SQLINIT and SQLDB directives.
EBCDIC Data
Although Extended Services 1.0 Database Manager provides for an
EBCDIC collating sequence, the data is stored in the database encoded
in ASCII. As the data is retrieved and returned into host variables,
it remains ASCII encoded. Some developers have found it necessary (or
more convenient) to handle EBCDIC data within their programs. If the
application can tolerate data conversion during execution, the Micro
Focus Host Compatibility Option provides for this by inserting
statements into the application to convert the host variable data
between EBCDIC and ASCII.
The CHARSET(EBCDIC) directive enables this conversion.
DB2 Data Definition Statements
The Micro Focus Host Compatibility Option allows certain
DB2-exclusive SQL statements to be used in a COBOL program or in an
off-line batch utility. This facility helps to create a database on
the workstation that is similar to the one on the host, using the
same SQL statements that are used on the
host. Examples of these statements are CREATE DATABASE, ALTER TABLE,
DROP INDEX, and COMMENT ON TABLE.
Other DB2 statements that are not needed or don't make sense on OS/2
are parsed by the Micro Focus Host Compatibility Option but are
treated as comments. This allows the statements to remain in the
application where they will be used by DB2 but ignored on OS/2 and
still achieve equivalent results.
SQLCODE Mapping
Many of the SQLCODEs used to report error conditions are not the same
between DB2 and Extended Services 1.0 Database Manager. If SQLCODE
needs to be used instead of SQLSTATE (see ]SQLSTATEX on page 5), the
Micro Focus Host Compatibility Option provides a mapping facility to
support the translation of Extended Services 1.0 Database Manager
SQLCODEs to their DB2 equivalent for special situations.
Logical NOT Sign (^)
DB2 allows the ]^ X symbol to be used in SQL. It is not supported in
Extended Services 1.0 Database Manager (see ]Logical NOT Sign (^ )X
on page 6).
The Micro Focus COBOL Workbench will perform the following
translations in source programs when the SQLNOT directive is properly
set.
^= is translated to <> ^< is translated to >= ^> is translated to <=
Use of the ]^ X symbol should be discouraged. The alternative coding
above is much more widely accepted.
DCLGEN
DB2's DCLGEN utility produces table declarations and host structures
which can be included in application programs. This automates the
task of declaring variables that correspond to the database tables.
The OS/2 DBMs have no such utility making it a little harder to
develop applications on the workstation.
The Micro Focus Host Compatibility Option provides a DCLGEN facility
that creates a COBOL copy member with a DECLARE TABLE and host
variable structures from the Extended Services 1.0 Database Manager
table definition.
Extended Services 1.0 Database Manager Directions
The following items have been identified as compatibility
enhancements that IBM is interested in for future releases of
Extended Services 1.0 Database Manager. The appearance of the items
here is NOT a guarantee that this function will be supplied.
NOT NULL WITH DEFAULT
NOT NULL WITH DEFAULT is a clause in the CREATE TABLE or ALTER TABLE
statement that prevents a column from containing null values, and
allows a default value other than the null value. The default value
depends on the data type of the column (0, blank, empty string,
etc.). DB2 supports this clause but the OS/2 DBMs do not. When
Extended Services 1.0 Database Manager supports this clause, it will
be easier to create tables on the host and the workstation that are
the same.
Until this support is provided, workstation tables must be created
and explicitly loaded with the appropriate values corresponding to
the DB2 defaults.
CONNECT
The CONNECT statement connects an application process to the
application server identified in the statement. In the current OS/2
DBMs, the CONNECT statement is not supported, however, the START
USING DATABASE statement is used for similar purposes. When Extended
Services 1.0 Database Manager supports the CONNECT statement,
applications needing explicit connections can be ported between
platforms without having to change between CONNECT and START USING
DATABASE.
CURRENT SERVER Special Register
The CURRENT SERVER special register identifies the current
application server. An application can obtain this value to determine
to which server it is currently connected. DB2 supports this special
register but the OS/2 DBMs do not. Until support is provided in
Extended Services 1.0 Database Manager, applications will have to be
written to test for their server in another way. This is easily done.
For example, each server could have a small, one-column, one-row
table with the server's name
as the value in the table. The table name would be the same in all
servers. The application could retrieve the value from the table to
determine the current server.
CURRENT TIMEZONE Special Register
The CURRENT TIMEZONE special register specifies the difference
between Coordinated Universal Time (UTC, formerly Greenwich Mean
Time) and the local time at the application server. Subtracting
CURRENT TIMEZONE from a local time results in a value that represents
UTC. This is an ease-of-use feature that may be useful to
applications that are sensitive to time zones or need coordinated
timestamps.
Until this support is available in Extended Services 1.0 Database
Manager, applications that are sensitive to timezone differences
must keep track of the differences some other way, e.g., a hard-coded
constant in the application or a value stored in a small table in
the database, etc.
Escape Clause on LIKE Predicate
The LIKE predicate searches for strings that have a certain pattern.
It is used for finding data where, for example, the exact spelling of
a name is not known. Two special characters, the underscore and the
percent sign, are normally used as "wild card" characters in the
search pattern. If either of these characters are needed as actual
matching characters in the search pattern, then some way must be
found to specify that they should not be treated as "wild cards". The
escape clause does this. DB2 supports the escape clause. The OS/2
DBMs do not.
Until Extended Services 1.0 Database Manager supports the escape
clause, applications developed on the workstation will be restricted
from using this feature.
UPDATE Privilege on Columns
DB2 has an option on the GRANT table privileges statement that allows
an UPDATE operation on only those columns identified in a column
list. The Extended Services 1.0 Database Manager GRANT statement
applies to an entire table and not to specific columns.
Until Extended Services 1.0 Database Manager supports this
capability, applications developed on the
workstation will be restricted from using this feature.
String Concatenation
Extended Services 1.0 Database Manager does not provide a concatenate
operation for two or more character strings. This function is
available in DB2.
Until Extended Services 1.0 Database Manager supports this
capability, applications developed on the workstation will be
restricted from using this feature.
Numeric Data Type Conversion Functions
The DECIMAL, INTEGER, FLOAT, and VALUE functions are provided by DB2,
but not in
Extended Services 1.0 Database Manager.
Until Extended Services 1.0 Database Manager supports these
functions, applications developed on the workstation will be
restricted from using them.
Aliases
Aliases are names that can be assigned to tables or views. This can
be useful when porting an application from one environment to
another. The database object name can change between environments but
the alias name within the application can remain the same. The
application need not be modified.
A synonym is like an alias, with a few differences. No authorization
is required to define a synonym. A synonym can only refer to a local
table or view. An alias can be defined on an undefined name.
Dropping a table or view has no effect on its aliases. An alias can
be used by any authorization ID.A synonym can only be used by the
authid that created it. See the DB2 SQL Reference for more
information.
DB2 supports aliases and synonyms but Extended Services 1.0 Database
Manager does not.
Aliases should be used instead of synonyms. It is more likely that
Extended Services 1.0 Database Manager will implement aliases than
synonyms.
Until Extended Services 1.0 Database Manager supports aliases, the
convenience of their use is not available on the workstation. The
workaround for this is to have identical authids and object names on
both the host and the workstation. In some cases, a view with a
portable name can be used as a substitute for an alias.
Note: The following three items are of concern only if the
application developer is interested in conforming to the ISO-ANSI
standards for SQL and needs to code these functions as specified in
the standards. Otherwise, it is easy to find compatible
alternatives to these functions.
UNIQUE Clause on CREATE TABLE
The UNIQUE clause defines a unique key composed of identified
columns. Unique keys are used to ensure that there are no duplicate
rows in a table. This clause is available in DB2 but not in Extended
Services 1.0 Database Manager.
Until Extended Services 1.0 Database Manager supports this clause,
unique keys can be defined using the CREATE INDEX statement, but
applications doing this would not conform to ISO-ANSI SQL.
Stand-Alone SQLCODE
SQLCODE is an SQL return code that has been historically included in
a control block called the SQLCA in IBM products. The ISO-ANSI
standards define SQLCODE to be a stand-alone variable
instead of a field in a control block. DB2 has a precompiler option
that designates which form of SQLCODE will be used in an application
program. Extended Services 1.0 Database Manager does not have this
option.
The obvious circumvention for this problem is to use the SQLCODE
field of the SQLCA and not generate the standalone SQLCODE variable,
however, applications doing this would not conform to ISO-ANSI SQL.
INDICATOR Keyword
Indicator variables are used with host variables to designate that
the host variable contains the null value. Host variables and
indicator variables are declared in an application in the general
form:
:V1 INDICATOR :V2
where V1 is the host variable, the word INDICATOR is an optional
keyword, and V2 is the indicator variable. Extended Services 1.0
Database Manager does not allow the optional INDICATOR keyword.
The obvious workaround for this is to leave "INDICATOR" out of the
declaration. It is optional in ISO-ANSI SQL.
Workarounds, Tools, and Helpful Hints
Message Formatting Modules
SQLAINTP in OS/2 and DSNTIAR in DB2 are modules having similar
function, the formatting of exception messages. Each module has
different parameter requirements. Mapping from one to the other is
required for compatibility.
The following pseudocode is an outline of what can be done to provide
the mapping. The strategy is for the application to call the DB2
module DSNTIAR. When running on the host, DSNTIAR is executed to
format the message. When running on the workstation, this mapping
module, also named DSNTIAR, gets control, re-maps the parameters to
the OS/2 format, then calls the OS/2 formatting module SQLAINTP.
int DSNTIAR (sqlca, message, lrecl);
/* One difference between sqlaintp and DSNTIAR is that */ /* DNSTIAR
returns the message to a varchar, while sqlaintp */ /* returns the
message to a null-terminated string.*
struct {short int l; char m5241:} message;
/* DSNTIAR assumes that at least 240 bytes are available. */
rc = sqlaintp (message.m, 241, lrecl, sqlca);
/* The other difference is the return */ /* code. This mapping is
approximate. */
if (rc > 0 && rc < 241) then {message.l = rc; rc = 0;} if (rc > 240)
then {message.l = rc; rc = 4;} if (rc == -4) then {rc = 8;} if (rc ==
-5) then {rc = 12;} if (rc == -1) then {rc = 16;}
return (rc);
end DSNTIAR.
COMMIT/ROLLBACK WORK
It is good coding practice to issue a COMMIT or a ROLLBACK (or the
equivalent CICS Syncpoint) before ending an application program. If
this isn't done, an implicit COMMIT is performed as the application
process terminates. In certain circumstances, the effect of the
implicit action can be different between DB2 and Extended Services
1.0 Database Manager.
There is no current plan to change Extended Services 1.0 Database
Manager or DB2 so that the default action is the same in all cases.
To ensure that the results are as expected, an explicit COMMIT or
ROLLBACK should be issued in the application just prior to
termination.
EBCDIC Collating Sequence on the Workstation
The following REXX procedure is an example of a way to define a
database with an EBCDIC collating sequence on the workstation. /*
create an EBCDIC database (PC codepage=850, mainframe codepage=500)
*/
/* LOAD REXX INTERFACE TO DBM*/ if Rxfuncquery('SQLEXEC') \= 0 then
rcy = Rxfuncadd('SQLEXEC','SQLAR','SQLEXEC')
if Rxfuncquery('SQLDBS') \= 0 then rcy =
Rxfuncadd('SQLDBS','SQLAR','SQLDBS')
/* GET DB NAME, DRIVE FROM ARGUMENTS */ arg dbname drive comment
if dbname='' then do say 'Usage is "DB2DB dbname 5drive 5comment::"'
exit end
/* Collating Sequence from \SQLLIB\SQLECSRX.CMD: */ /* SOURCE = ASCII
850; TARGET = EBCDIC 500 */
SQLE_850_500 = '00010203372D2E2F1605250B0C0D0E0F'x||,
'101112133C3D322618193F271C1D1E1F'x||,
'404F7F7B5B6C507D4D5D5C4E6B604B61'x||,
'F0F1F2F3F4F5F6F7F8F97A5E4C7E6E6F'x||,
'7CC1C2C3C4C5C6C7C8C9D1D2D3D4D5D6'x||,
'D7D8D9E2E3E4E5E6E7E8E94AE05A5F6D'x||,
'79818283848586878889919293949596'x||,
'979899A2A3A4A5A6A7A8A9C0BBD0A107'x||,
'68DC5142434447485253545756586367'x||,
'719C9ECBCCCDDBDDDFECFC70B180BFFF'x||,
'4555CEDE49699A9BABAFBAB8B7AA8A8B'x||,
'2B2C092128656264B438313433B0B224'x||,
'22172906202A46661A35083936303A9F'x||,
'8CAC7273740A757677231514046A783B'x||,
'EE59EBEDCFEFA08EAEFEFBFD8DADBCBE'x||,
'CA8F1BB9B6B5E19D90BDB3DAFAEA3E41'x
/* BUILD "CREATE DATABASE" STRING */
string = 'CREATE DATABASE 'dbname if drive <> '' then string =
string' ON 'drive if comment <> '' then string = string' WITH
"'comment'"' string = string' COLLATE USER :SQLE_850_500'
/* DO IT */ call sqldbs string if SQLCA.SQLCODE <> 0 then say
"SQLCODE="SQLCA.SQLCODE
Testing the Collating Sequence on a Database
The following REXX procedure is an example of a way in which the user
can interrogate the collating sequence of a database on the
workstation.
/* Query collating sequence of database */
/* LOAD REXX INTERFACE TO DBM*/ if Rxfuncquery('SQLEXEC') \= 0 then
rcy = Rxfuncadd('SQLEXEC','SQLAR','SQLEXEC')
if Rxfuncquery('SQLDBS') \= 0 then rcy =
Rxfuncadd('SQLDBS','SQLAR','SQLDBS')
/* GET DB NAME, DRIVE FROM ARGUMENTS */ arg dbname
if dbname='' then do say 'Usage is "QCOLLATE dbname"' exit end
call sqldbs 'START USING DATABASE 'dbname if SQLCA.SQLCODE<0 &
SQLCA.SQLCODE<>-1098 then do say "Can't start: SQLCODE="SQLCA.SQLCODE
exit end
stmt = 'CREATE TABLE QCOLLATE.QCOLLATE (C CHAR(1))' call sqlexec
'EXECUTE IMMEDIATE :stmt' if SQLCA.SQLCODE<0 then do say "Can't
create test table: SQLCODE="SQLCA.SQLCODE signal finis end
stmt = 'INSERT INTO QCOLLATE.QCOLLATE VALUES(?)' call sqlexec
'PREPARE S1 FROM :stmt' if SQLCA.SQLCODE<0 then do say "Can't prepare
insert stmt: SQLCODE="SQLCA.SQLCODE signal finis end
do i=0 to 255 c = "'"d2c(i)"'" call sqlexec 'EXECUTE S1 USING :c' if
SQLCA.SQLCODE<0 then do say "Can't insert row "i":
SQLCODE="SQLCA.SQLCODE signal finis end end
stmt = 'SELECT C FROM QCOLLATE.QCOLLATE ORDER BY C'
call sqlexec 'DECLARE C2 CURSOR FOR S2' if SQLCA.SQLCODE<0 then do
say "Can't declare cursor: SQLCODE="SQLCA.SQLCODE signal finis end
call sqlexec 'PREPARE S2 FROM :stmt' if SQLCA.SQLCODE<0 then do say
"Can't prepare select stmt: SQLCODE="SQLCA.SQLCODE signal finis end
call sqlexec 'OPEN C2' if SQLCA.SQLCODE<0 then do say "Can't open
cursor: SQLCODE="SQLCA.SQLCODE signal finis end
do i=0 to 15 string = '' do j=0 to 15 call sqlexec 'FETCH C2 INTO :c'
if SQLCA.SQLCODE<0 then do say "Can't read cursor:
SQLCODE="SQLCA.SQLCODE signal finis end if c<' ' then string=string'
^'d2c(c2d(c)+c2d('@')) else string = string' 'c end say string end
finis: call sqlexec 'CLOSE C2' call sqlexec 'ROLLBACK' call sqldbs
'STOP USING DATABASE' exit