home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 29 Fixes_o
/
29-Fixes_o.zip
/
us90_2.zip
/
README.TXT
< prev
next >
Wrap
Text File
|
1996-03-22
|
150KB
|
3,840 lines
***********************************************************************
** **
** (c) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1996. **
** ALL RIGHTS RESERVED. **
** **
***********************************************************************
Welcome to DB2* for OS/2* Version 2.1.1
+--- NOTE! ----------------------------------------------------------+
| |
| Set the font to monospace for better viewing of this README. |
| |
+--------------------------------------------------------------------+
This README file contains information for the following products and
components:
OS/2-Specific
-------------
IBM DB2 for OS/2 Server (DB2 for OS/2)
IBM DB2 for OS/2 Single-User (DB2 for OS/2)
IBM DB2 Software Developer's Kit for OS/2
IBM DB2 Client Application Enabler for OS/2
IBM DB2 Administrator's Toolkit for OS/2
DDCS for OS/2
-------------
IBM Distributed Database Connection Services* Single-User for OS/2
(DDCS Single-User for OS/2)
IBM Distributed Database Connection Services Multi-User Gateway for OS/2
(DDCS Multi-User Gateway for OS/2)
For information, see also:
Part Number Publication
----------- -----------------------------------------------------------
59H1095 IBM* DATABASE 2* for OS/2 Installation and Operation Guide
59H1097 IBM DATABASE 2 Installing and Using OS/2 Clients
20H4787 IBM DATABASE 2 Software Developer's Kit for OS/2
Building Your Applications
20H4793 IBM DATABASE 2 IBM Distributed Database Connection Services
User's Guide for common servers
59H1094 IBM DATABASE 2 for OS/2 IBM Distributed Database
Connection Services Installation and Configuration Guide
---------------------------------------------------------------------------
1.0 Contents
2. INSTALLATION NOTES
2.1 System Requirements
2.2 TZ Environment Variable
2.3 LAN Support Program and NetBIOS rc=0x18
3. MIGRATION CONSIDERATIONS FROM DB2 2.1.0 PRODUCTS OR DB2 2.1.1 BETA
DRIVERS
3.1 Coexistance with DB2 V2.1.0 and DDCS V2.3.0 products
3.2 Migrating from the DB2 2.1.0 GA or from the DB2 2.1.1 Beta
3.2.1 Database Considerations
3.2.2 Application Considerations
4. USAGE NOTES
4.1 Product Documentation
4.1.1 Ordering the Cross-Platform Printed Manuals
4.2 Database Directory Utility
4.2.1 Linking to Online Books from Database Director Online
Help
4.3 Roll Forward and Log File Size
4.4 DRDA Application Server Support Authentication
4.5 Replacement for SQLQMF Utility
4.6 Import/Load APIs
4.7 sqlestrd() API
5. ENVIRONMENT CONSIDERATIONS
5.1 TCPBEUI Users
5.2 HPFS/HPFS386 users
5.3 Back-Level Support
5.4 DBA Utilities
5.4.1 Userid and Password Validation on an AIX/UNIX Server
5.5 Backup/Restore to User Exit
5.6 Dealing with Container in Use Error Message -294
6. PROGRAMMING NOTES
6.1 Sample Applications
6.2 Considerations when Using Micro Focus COBOL
6.3 BINARY/COMP-4 COBOL Data Types in the DB2 Precompiler
6.4 Using REDEFINES in COBOL Group Data Items
6.5 VisualAge C Set ++ and DB2 for OS/2
6.6 Precompiler Customization
6.7 Considerations When Using REXX Stored Procedures
7. CHANGES TO THE DB2 DOCUMENTATION
7.1 For All Refreshed Books - "Changes for This Release" Section
7.2 Administration Guide
7.3 API Reference
7.3.1 Backup and Restore API's for Vendor Products Appendix
DB2-INFO Data Structure
7.3.2 QUIESCE TABLESPACES FOR TABLE
7.4 Command Reference
7.4.1 FORCE APPLICATION
7.4.2 ROLLFORWARD DATABASE
7.4.3 EXPORT
7.4.4 QUIESCE TABLESPACES FOR TABLE
7.4.5 RUNSTATS Enhancement
7.5 Call Level Interface Guide and Reference
7.5.1 SQLColAttributes - Get Column Attributes
7.5.2 New CLI/ODBC Configuration Keyword
7.6 Database System Monitor Guide and Reference
7.6.1 db_conn_time Data Element
7.6.2 Buffer Pool Data Pages Written to Disk
7.6.3 DBMON Element Formula
7.6.4 Buffer Pool Threshold Cleaners Triggered
7.6.5 Event Start Time
7.6.6 Package Cache Inserts/Package Cache Lookups
7.6.7 Status of Database and Status of DB2 Instance
7.7 Planning Guide
7.7.1 Segments Versus 4KB Pages
7.7.2 BUFFPAGE and SORTHEAP
7.8 Problem Determination Guide
7.8.1 First Failure Data Capture
7.9 Configuration Parameters
7.9.1 DIR_CACHE
7.9.2 MAXAPPLS
7.9.3 TP_MON_NAME
7.9.4 REC_HIS_RETENTN
7.9.5 Configuration Parameters (YES/NO and ON/OFF Values)
7.9.6 DFT_QUERYOPT
7.10 Building Your Applications
7.10.1 Building Your Embedded SQL Applications
7.10.2 Building FORTRAN Stored Procedures
7.10.3 Definition Files for UDFs and Stored Procedures
7.10.4 C++ Type Decoration Consideration
7.10.5 IBM COBOL Build Scripts
7.10.6 Miscellaneous
7.11 Installation and Operation Guides
7.12 Installing and Using OS/2 Clients
7.12.1 New CLI/ODBC Configuration Keywords
7.13 SQL Reference
7.13.1 ALTER TABLE Notes Addition
7.13.2 SQL Communications Area (SQLCA)
7.13.3 GROUP-BY and ORDER-BY Changes
7.13.3.1 Expressions Section
7.13.3.2 Queries Section
7.14 Application Programming Guide
7.15 References to CompuServe
7.16 Messages Reference
7.16.1 New Messages
7.16.2 Changed Messages
8. DDCS-SPECIFIC INFORMATION
8.1 Documentation Changes
8.1.1 Installation Requirements
8.1.2 National Language (Code Page and Conversion) Information
Changes
8.1.3 OS/2 and UNIX-Based Databases
8.1.4 SET CURRENT PACKAGESET Support
8.1.5 DB2 for OS/400 Column Types and Length
8.1.6 Binding DDCS Utilities to an OS/400 Database
8.1.6.1 Possible Solutions
8.1.7 Binding Utilities for Back-Level Clients
8.1.8 SPM Instance Name
8.2 Syncpoint Manager (SPM)
8.2.1 Prerequisite PTFs
8.2.2 IBM Communications Server for OS/2 Warp Version 4
8.3 Authentication Default
8.4 Performance Troubleshooting
8.5 DDCS 2.3 Gateway Connection to DB2/MVS
8.5.1 Summary of Steps
8.5.2 Products used in this example
8.5.3 Network Element Names
8.5.4 Configuring SNA Communications
8.5.4.1 Creating a Communications Manager Configuration
8.5.4.2 DLC Profile
8.5.4.3 SNA Local Node Characteristics
8.5.4.4 SNA Connections
8.5.4.5 SNA Features
8.5.4.6 Completing the CM Configuration
8.5.4.7 Sample NDF File created by the above procedure
8.5.5 Cataloging DB2 Directory Entries at the Workstation
8.5.5.1 Cataloging the Workstation
8.5.5.2 Cataloging the Database
8.5.5.3 Cataloging the DCS Database
8.5.5.4 Verify DCS Directory Information
8.5.6 Configuring VTAM
8.5.7 VTAM DB2 APPL Definition
8.5.7.1 VTAM DB2 PU and LU definitions
8.5.7.2 Sample Log Mode Definition
8.5.8 Configuring DB2/MVS
8.5.8.1 Updating the SYSIBM.SYSUSERNAMES Table
8.5.8.2 Update the SYSIBM.SYSLUNAMES Table
8.6 Connectivity Via DDCS (Thailand Users Only)
8.7 DDCS User's Guide BIND Information
8.8 OS/2 Coexistence Considerations
9. COMMUNICATIONS ISSUES
9.1 TCP/IP
10. SAMPLE TOOLS AND UTILITIES
11. LIMITATIONS IN DOCUMENTED FUNCTIONS
11.1 Transaction Manager Database Considerations
11.2 Command and API Limitations
11.3 WCHARTYPE CONVERT Precompile Option Limitation
11.4 Using DB2 with an XA-Compliant Transaction Manager
11.5 Database Director Limitations
11.5.1 Restriction for Visual Explain
11.5.1.1 Snapshot Monitor
11.5.1.2 Event Analyzer
11.6 Online Backup and Load with COPY NO option
12. USING THE DB2 ODBC DRIVER WITH ODBC APPLICATIONS
12.1 Common Application Problems and Work Arounds
12.1.1 Visigenic's ODBC Driver Manager
12.1.2 Intersolv's ODBC Driver Manager
12.2 Limitations of CLI/ODBC Administrator and Client Setup GUI
Tools
12.3 SQLDescribeParam()
12.4 New CLI/ODBC Configuration Keywords in db2cli.ini
12.4.1 CLI/ODBC Trace File
12.4.2 Specifying a Directory for Temporary Files
12.4.3 SQLDescribeParam() Enable/Disable
12.4.4 Asynchronous ODBC Enable/Disable
13. MISCELLANEOUS
13.1 DB2 Family Newsletter
=========================================================================
2. Installation Notes
2.1 System Requirements
The Planning Guide was not updated for this release and so the system
requirements that are documented in the last version are not correct.
The following are the minimum system requirements for DB2 V2.1.1 and
DDCS V2.3.1:
o 26 MB to 64 MB hard disk space, depending on options chosen
o 16 MB memory
o OS/2 V2.11 or later including OS/2 Warp
For Communications, one of the following products for OS/2
o For APPC: IBM Communications Manager Version 1.1 or later. The
Syncpoint Manager function of DDCS Multi-User Gateway V2.3.1
requires IBM Communication Server Version 4
o For IPX/SPX: Novell NetWare Requester, Version 2.10
o For TCP/IP: IBM TCP/IP Version 2.0 or later
o For NetBIOS: IBM NTS/2 Version 1.0, or IBM Communications Manager
V1.1, or IBM OS/2 LAN Requester
2.2 TZ Environment Variable
When connecting a client to the server which runs on an operating
system that supports the concept of local time versus Universal
Coordinated Time (UCT, also known as Greenwich Mean Time or GMT), the
client machine should also set up timezone information that
corresponds to the server machine. If it is not set, the client may
display certain timestamps (that are returned by the server)
incorrectly (being off by the difference between the UCT and local
time).
So for example, if a server is running under Pacific Standard Time
(PST) and a client is running under Eastern Standard Time (EST),
timestamps on the client will show PST unless the TZ variable is set.
2.3 LAN Support Program and NetBIOS rc=0x18
If you use IBM LAN Support Program to provide NetBIOS communications
protocol support, and you load the LAN Support Program device drivers
in DOS upper / high memory, you may encounter intermittent NetBIOS
communications errors "SQL30081, rc=0x18". To avoid these
communications errors, load the LSP device drivers in conventional
memory.
3. Migration Considerations from DB2 2.1.0 Products or DB2 2.1.1 Beta
Drivers
3.1 Coexistance with DB2 V2.1.0 and DDCS V2.3.0 products
All DB2 V2.1.0 and DDCS V2.3.0 products on your workstation must be
updated to V2.1.1 and V2.3.1 respectively to ensure coexistence.
The DATABASE 2 for OS/2 CD contains updated versions of all the
products. If you are installing your new product from this CD, you can
also update any other DB2 V2.1.0 or DDCS V2.3.0 products currently
installed.
Alternatively, you can contact IBM Service to obtain the DB2 V2.1.1 or
DDCS V2.3.1 service refresh for other products already installed.
3.2 Migrating from the DB2 2.1.0 GA or from the DB2 2.1.1 Beta
3.2.1 Database Considerations
The following points apply to the workstations that have either
installed the 2.1.1 Beta driver or one of the following products:
IBM DB2 for OS/2 Server V2.1.0
IBM DB2 for OS/2 Single-User V2.1.0
and they have created databases with these code levels.
o It is strongly recommended that you perform an optional migration
on any of your databases that were created by the V2.1.0 database
manager, in order to give your applications access to the new
scalar functions that have been implemented in V2.1.1.
o DBADM authority is granted implicitly by DB2 when a SYSADM user
creates a view or package that requires DBADM authority. However,
if such a user is later removed from the SYSADM group, their DBADM
authority will also be removed. Therefore, any users with DBADM
authority who do not belong to the SYSADM group must have been
explicitly granted DBADM access.
o It is recommended that you use the runstats utility to update the
index statistics for your tables.
3.2.2 Application Considerations
The following point applies to systems that have installed the 2.1.1
Beta driver and created applications:
o If you are migrating from the V2.1.1 beta driver, you must rebind
your applications prior to running them. Use the db2rbind command
to rebind all packages on your databases.
4. Usage Notes
4.1 Product Documentation
4.1.1 Ordering the Cross-Platform Printed Manuals
You can order the cross-platform printed manuals. The part number for
this set of books is 41H2451. The following books are included:
Form Number Part Number Book
------------ ----------- ---------------------------------------
S20H-4664-00 20H4664 Information and Concepts Guide
S20H-4580-01 59H1088 Administration Guide
S20H-4871-00 20H4871 Database System Monitor Guide and Reference
S20H-4984-01 59H1089 API Reference
S20H-4643-01 59H1090 Application Programming Guide
S20H-4644-01 59H1092 Call Level Interface Guide and Reference
S20H-4645-01 59H1093 Command Reference
S20H-4808-01 59H1100 Messages Reference
S20H-4665-01 59H1101 SQL Reference
S20H-4779-00 20H4779 Problem Determination Guide
S20H-4793-00 20H4793 DDCS User's Guide
4.2 Database Directory Utility
4.2.1 Linking to Online Books from Database Director Online Help
If you are using Database Director online help, and you try
double-clicking on a hypertext link in order to open an online book
but the book is not displayed (that is, you receive an error message),
it is possible that the online book has not been installed. You may
need to install the English version of the book if it has not been
translated or the BOOKSHELF environment variable has not been set.
If an online book is installed and a hypertext link to it still does
not work, you can open the book by going to the DB2 information folder
and double-clicking on the book title.
4.3 Roll Forward and Log File Size
If the log file size (LOGFILSIZ) was modified during the course of
normal processing, then database level roll forward through these log
files will behave as follows:
On any invocation of roll forward, only log files with the same log
file size will be processed. When roll forward encounters a log file
of different size (excluding truncated log files), roll forward
processing will stop and return error SQL1042N. Diagnostic
information will be logged. You will then be required to re-issue the
roll forward command to continue processing from the next log file.
4.4 DRDA Application Server Support Authentication
The DRDA AS support in DB2 for OS/2 supports both client and server
authentication. However, there can be restrictions imposed by the
communications product that you are using along with DB2 for OS2 with
regard to what authentication type you can use.
If you are using CM/2 1.11, then you cannot use server authentication
because CM/2 does not expose the password to the DRDA AS. You can
only use client or DCS authentication. With client authentication,
DB2 for OS/2 will not authenticate the user for any incoming
connection, whether or not it is via DRDA. With DCS authentication,
DB2 for OS/2 will not authenticate the user if the connection is via
DRDA, but will authenticate the user the same way as with server
authentication if the connection is non-DRDA. Because of this
limitation, the use of CM/2 1.11, along with the DRDA AS is not
recommended.
The recommended approach is to use IBM Communications Server for OS/2
Warp V4 (CS/2) instead of CM/2 1.11. With CS/2, you can use both
client and server authentication. In order to use server
authentication, you will need to do the following before issuing
db2start:
1. Update the database manager configuration SPM_NAME value with the
local LU for the DRDA AS.
2. Modify the definition of the local LU which corresponds to the
database manager configuration SPM_NAME value by editing the CS/2
NDF file in the \CMLIB directory by adding the line
EXTERNAL_SUPPORT(SYNCPT_PROVIDER,REMOTE_TP_PROVIDER)
before the line
NAU_ADDRESS(INDEPENDENT_LU);
The steps above are in addition to the regular setup required for the
DRDA AS as documented in the DB2 for OS/2 Installation and Operation
Guide.
NOTE: Special Considerations for Using Client Authentication:
If the DRDA AR clients are coming from a secure system and you do not
want to send the password to the DRDA AS to have it authenticated
there, you could set up the DRDA AR and AS to use APPC Security=SAME
connections using client authentication. If you do this you should
also enforce the use of APPC LU_LU Verification between the LU at the
AR and the LU at the AS to ensure the AR is coming from where it
claims it is from. If Hopping is to be used from the DRDA AS system
to another remote DB2 database, then the second system must be set up
with client authentication also because no password will be available.
4.5 Replacement for SQLQMF Utility
The function of the SQLQMF utility has been replaced by the DDCS
Import/Export functions. The advantages are:
o no need for QMF on the host
o no need to logon to the host (no TSO id required)
o supports AS/400 as well as SQL/DS and DB2/MVS
o good performance achieved by using compound SQL
o supports several file formats, in addition to ASCII
o can be run from a client machine with no SNA connectivity
See the DDCS User's Guide and the DB2 Command Reference for more
information on using these commands.
4.6 Import/Load APIs
The following are changes to the import API:
o Additional information is stored in IXF files to be used by import
with the CREATE option. If you specify a select statement of the
format "SELECT * FROM tablename" on export, the following
information will also be retrieved and stored in the IXF file:
- the default value for each column
- the original lob length even if the data is being truncated or
the lobs are stored in individual files as specified with the
LOBSINFILE option
- the user defined type.
When the IXF file is used to create a new table, this new
information will be used.
o You can specify a target tablespace when issuing a import with the
CREATE option. New syntax allowed for the tcolstrg is:
CREATE INTO tablename [(column_list)]
[IN tablespace [INDEX IN indextblespace] [LONG IN longtblspace]]
Note that to specify a tablespace for the indexes or long data,
you must specify a tablespace for the table.
o If you explicitly do not specify a source column for one of the
target table columns, one of the following things will happen:
- if the column is defaultable, the default will be imported
- if the column is nullable and not defaultable, a NULL will be
imported
- if the column is not nullable and not defaultable, an error
will be returned and the utility will stop processing.
Previously, if a source column was not provided, and if the column
was nullable, a NULL would have been imported, but if the column
was not nullable, an error would have been returned. If the old
behavior is desired, specify the NODEFAULTS option in the
filetype-modifier.
The following is a change to both the import and load APIs:
o A source column may be designated for a target table column but
may be omitted for any given row instance. Examples of missing
data are:
- for DEL files, ",," is specified for the column
- for DEL/ASC/WSF files, a row that does not have enough columns
or is not long enough for the original specification.
In these cases, import/load was used to import/load a NULL if the
column was nullable, and reject the row if the column is not
nullable. If the USEDEFAULTS option is specified in the
filetype-modifier, the following will be the new behavior:
- if the column is defaultable, the default will be
imported/loaded
- if the column is nullable and not defaultable, a NULL will be
imported/loaded
- if the column is not nullable and not defaultable, the row
will be rejected.
4.7 sqlestrd() API
Applications which use the sqlestrd() API to connect to databases must
change this to EXEC SQL CONNECT syntax if the application needs to
take advantage of using version 2 distributed unit of work features
specified by precompiler options.
5. Environment Considerations
5.1 TCPBEUI Users
For multiple clients running TCPBEUI, where NETBIOS runs over TCP/IP,
APAR IC10549 fix is required for LAN Transport.
5.2 HPFS/HPFS386 users
If you are storing database files on HPFS/HPFS386 drives, the
following fix packs are required:
HPFS - Fix Pack 12 (or later) for OS/2 Warp
- Fix Pack 103 (or later) for OS/2 2.11
HPFS386 - CSD IP08178 (or later) for LAN Server 4.0
CSD IP07088 (or later) for LAN Server 3.0
5.3 Back-Level Support
1. SQLBIND.EXE and SQLPREP.EXE are provided to bind and precompile
Version 1 programs only. New PREP and BIND options introduced in
Version 2 are not supported.
2. DB2 for OS/2 provides full binary compatibility for existing
applications written for previous versions of DB2 Common Server
programming interfaces. This means that you can upgrade your
database server software to Version 2.1.1 and existing
applications will continue to function.
3. You can also continue to maintain source code for existing
applications without rewriting them to use newer APIs right away.
\sqllib\include subdirectory contains header and library files
(db2api.lib and sql_dyn.lib) that should be used to maintain
applications that use back-level APIs.
NOTE: You will need to use previous versions of DB2 programming
documentation as these older APIs are not described in the V2.1.1
programming documentation.
It is highly recommended that applications be converted to use new
APIs when possible as these older APIs will not be supported in
future releases.
4. If you have applications that use Operational Status APIs such as
sqlestat, sqlefree, sqlenext, and sqleuser, modify them to use the
corresponding new Database Monitor APIs.
5. The Client Setup utility displays the Version 1 APPC/APPN node
directories with two asterisks around the protocol name (*APPC* OR
*APPN*). This is to indicate that the setting for that particular
node cannot be changed by the user. You need to uncatalog the
directory and re-catalog it again.
5.4 DBA Utilities
5.4.1 Userid and Password Validation on an AIX/UNIX Server
When an OS/2 client is communicating with an AIX server, a User
Profile Management (UPM) node logon window may be displayed to provide
a userid and password. This password is folded to uppercase by UPM
when it is sent to the server to be validated. Therefore the password
on AIX must be defined in uppercase for the validation to take place,
otherwise the logon will fail.
LAN Server (UPM) APAR IC11643 fix is required to support mixed cases
in UPM.
5.5 Backup/Restore to User Exit
Under certain conditions, Restore can be much slower than expected.
The conditions are:
o The Restore is using an 8mm tape drive for input.
o The database being restored contains multiple containers.
The first phase of Restore processing is to find the header file for
each container. Due to limitations in the Sytos device driver for 8mm
tape, a scan of the entire set of input tapes is conducted for each
container. After these slow scans are complete, the actual restore of
data is started.
For performance reasons the OS/2 userexit interface has been changed
for the task of restoring a database. Beginning with V2.11, the
database manager will directly call routines found in DB2USRXT rather
than DB2UEXIT. To maintain compatibility with userexits written to
the original "DB2UEXIT" interface a REXX script "DB2USRXT.CMD" is
provided in the \SQLLIB\BIN directory to map the new interface back to
the original.
The original interface was designed to only restore one container at a
time. This proved to be inefficient and for some tape devices
required that many passes be made on the tape. With V2.11, the restore
portion of the userexit will be called twice. The userexit will be
called once with Mode set to 1 to retrieve the "Media Headers" which
describe the layout of the database to be restored. With this
information the userexit will be called with Mode set to 2, and
provided a list of all files to be restored. By providing all files to
be restored in one call, devices can more efficiently retrieve the
files.
Application writers familiar with the original "DB2UEXIT" interface
will find the new interface very similar. The change is to parameter 4
passed to RESTORE routine. Previously parameter 4 was a subdirectory
which when combined with the drive supplied with parameter 1 specified
where the retrieve files were to be placed. Now parameter 4 represents
a file provided by the database manager at restore time, which user
written applications use to determine where files should be restored
to.
As an example:
/* DB2USRXT RESTORE C: BRNSDDB1 C:\SQLDBDIR\BRNSDDB1.RST C:\SQLDBDIR\BRNSDDB1.NEW 1 */
DB_DRIVE = strip(PARM1) /* ex. C: */
DB_NAME = strip(PARM2) /* ex. BRNSDDB1 */
RESP_FILE = strip(PARM3) /* ex. C:\SQLDBDIR\BRNSDDB1.RST */
REDIRECT_FILE = strip(PARM4) /* ex. C:\SQLDBDIR\BRNSDDB1.NEW */
MODE = strip(PARM5) /* ex. 1 */
C:\SQLDBDIR\BRNSDDB1.RST
Is a list of files to be restored (can include wildcards).
ex.
D:\SQL00001\*.*
D:\SQL00001\SQLT0000.0\*.*
D:\SQL00001\SQLT0001.0\*.*
D:\SQL00001\SQLT0002.0\*.*
C:\SQLDBDIR\BRNSDDB1.NEW
Is a list of path pairs, with the first path denoting where the original files were
backed up from, second path were the files need to be restored to.
ex.
D:\SQL00001\*.*
F:\SQL00003\*.*
D:\SQL00001\SQLT0000.0\*.*
F:\SQL00003\SQLT0000.0\*.*
D:\SQL00001\SQLT0001.0\*.*
F:\SQL00003\SQLT0001.0\*.*
D:\SQL00001\SQLT0002.0\*.*
F:\SQL00003\SQLT0002.0\*.*
Performance example:
The default database contains 3 tablespaces (SYSCATSPACE, TEMPSPACE1
and USERSPACE1). If the database was 4 Gigabytes in size, spanned 2
tapes, the tape device retrieve data at approximately 1 Gig./hour, and
the software running the tape device required the user to scan all the
files transferring those the user wanted. Version 2.10 would make 4
calls to the userexit, (one for the "Media header", and then three
separate calls to retrieve each tablespace). Total time would be 16
hours (4 * 4 hours), and require 7 tape changes.
Batching the three separate calls into one saves 8 hours of tape
reading and 4 tape changes. Time saved increases as the number of
containers increase.
NOTE: If your database backup image spans across multiple tapes,
Sytos Premium V2.1 is required if you use Sytos tape device.
5.6 Dealing with Container in Use Error Message -294
-294 sqlcodes can be returned from create database or create/alter
tablespace operations. Normally, this situation indicates a
specification error on the OS resource name, since apparently the
container is already in use by another tablespace. Note that the text
of the error message explanation indicates that SMS containers can be
shared. This is no longer true. A container can be in use by only
one tablespace at any time.
A system administrator or database administrator who finds that the
database which had last used the container has been deleted, yet
somehow the container's tag was not removed, can use the db2untag
tool. This tool will display a container tag's information so that
the sysadm can check the "owning" database. If the sysadm decides to
release the container, the following steps should also be taken:
o For SMS containers: Remove the directory and contents using the
usual OS file delete command.
o For DMS containers: Either delete the file/device or let db2untag
remove the container tag. db2untag will leave such a DMS
container otherwise unmodified.
6. Programming Notes
6.1 Sample Applications
The following files contain information about the sample applications
which are used in the DB2 publications. The sample applications will
only be useful to you if you have the DB2 Software Developer's Kit or
DB2 Single-User package for your platform installed.
samples\c\README
README file for the c sample applications.
samples\cobol\README
README file for the COBOL sample applications.
samples\fortran\README
README file for the FORTRAN sample applications.
samples\rexx\README
README file for the REXX sample applications.
samples\cli\README
README file for the Call Level Interface sample applications.
samples\cli\INTCLI.DOC
Documentation for the Interactive Call Level Interface applet.
samples\clp\README
README file for the Command Line Processor examples.
6.2 Considerations when Using Micro Focus COBOL
DB2 V2.1.1 has been modified to correctly handle abnormal termination
of Micro Focus COBOL applications compiled with the DB2 PREP command.
This ensures that partial transactions are rolled back in the event of
an error.
It is recommended that any existing DB2 applications precompiled with
DB2 PREP and compiled with Micro Focus COBOL be re-precompiled with
the updated DB2 V2.1.1 precompiler, and then recompiled with Micro
Focus COBOL. If applications built with Micro Focus COBOL and the IBM
precompiler from previous releases or versions are not rebuilt, there
is a danger of database corruption if abnormal termination occurs.
This support requires Micro Focus COBOL V3.2.46 plus the following
patch, available on CompuServe:
Forum: MICROFOCUS
Library: ICD Product Updates (Library 4)
Filename: DB2211UP.ZIP
This patch may also be available directly from Micro Focus. Obtain
the patch and apply it to your 3.2.46 compiler.
This patch will be integrated with the Micro Focus 16-bit COBOL
product following V3.2.46, so it will not be required once subsequent
versions of the compiler are available.
Once the necessary version of the Micro Focus compiler is available,
use commands like the following to build your applications:
cobol app.cbl /notrunc;
link /st:32000 /pm:vio /noi
app.obj,,,coblib+os2286+sqldyn16+db2gmf16;
and for stored procedures, the link command should be like the
following:
link /st:32000 /pm:vio /noi /nod app.obj,app.dll,,
1cobol+os2286+sqldyn16+db2gmf16,app.def;
6.3 BINARY/COMP-4 COBOL Data Types in the DB2 Precompiler
The DB2 Precompiler supports use of BINARY, COMP-4 and COMP COBOL data
types wherever integer host variables and indicators are permitted, as
long as the target COBOL compiler views (or can be made to view)
BINARY/COMP-4/COMP as equivalent to COMP-5. In the DB2 Application
Programming Guide, such host variables and indicators are shown with
type COMP-5. Target compilers supported by DB2 which treat
BINARY/COMP-4/COMP and COMP-5 as equivalent are:
IBM COBOL Set for AIX
Micro Focus COBOL for AIX
IBM COBOL Visual Set for OS/2 (with the -qbinary(native)
option set)
6.4 Using REDEFINES in COBOL Group Data Items
The REDEFINES clause may be used when declaring host variables with
the DB2 COBOL precompiler. If a member of a group data item is
declared with the REDEFINES clause, and that group data item is
referred to as a whole in an SQL statement, any subordinate items
containing the REDEFINES clause will not be expanded. For example:
01 foo.
10 a pic s9(4) comp-5.
10 a1 redefines a pic x(2).
10 b pic x(10).
Referring to foo in an SQL statement like so:
... INTO :foo ...
is equivalent to
... INTO :foo.a, :foo.b ...
That is, the subordinate item a1 with the REDEFINES is not
automatically expanded out in such situations. A subordinate with a
REDEFINES may be referred to in an SQL statement explicitly, as in
... INTO :foo.a1 ...
or
... INTO :a1 ...
if a1 is unambiguous.
6.5 VisualAge C Set ++ and DB2 for OS/2
VisualAge* C Set ++* Version 3 provides a database application
development assist tool called the Data Access Builder.
VisualAge C ++ Version 3 corrective service (CSD) contains Data Access
Builder enhancements to access DB2 for OS/2 Version 2.1 databases.
This service is included in a CTV30x.ZIP file (where x is a numeric
identifier of the particular CSD). To find out if the enhancement is
in a particular CTV30x.ZIP file, look in the CTV30x.LST file that can
be found in the same place as the CTV30x.ZIP file.
These files will be available in the following places:
COMPUSERVE**
o GO OS2DF1
o Look in library 4
INTERNET
o Use anonymous ftp to get to the software.watson.ibm.com site.
o The files will be in the pub/os2/cset++ directory.
THE DEVELOPER'S CONNECTION FOR OS/2 CD-ROM (DEVCON)
The latest fixes for VisualAge C++ Version 3 will also be put on
DEVCONs that ship after the CSDs are available. The actual volume
these fixes will be on has not been determined at this time.
For building your applications with the VisualAge C++ linker, use the
following command in place of the link386 command as documented for
the bldcspp2 command file on page 32 of the DB2 for OS/2 "Building
Your Applications" book (Part #20H4787):
link /NOFREE /NOI /DEBUG /ST:32000 %1.obj,%1.dll,,db2api,%1.def;
The sample command files for building your applications provided in
the ".../sqllib/samples/c/..." directory contain this command, but it
is commented out. Replace the link386 command with this link command
to use the VisualAge C++ linker instead of the OS/2 Linear Executable
Linker.
6.6 Precompiler Customization
If you are writing your own precompiler interface to DB2 Precompiler
Services, you should be aware that the program_id parameter to
sqlainit() must indicate static storage. This storage is referred to
by the Precompiler Services functions sqlaalhv() and sqlacmpl() during
precompilation. The program_id storage must remain accessible and
undisturbed until Precompiler Services has been terminated with a call
to sqlafini().
6.7 Considerations When Using REXX Stored Procedures
ON THE CLIENT SIDE (WHEN CLIENT IS WRITTEN IN REXX):When using host
variables in the CALL statement, each host variable must be
initialized to a value compatible in type with the data that is
returned into it from the server procedure (if any). It should be done
even if the corresponding indicator is negative. When using
descriptors, SQLDATA must be initialized and contain data compatible
in type with the data that is returned from the server procedure (if
any). It should be done even if the SQLIND field contains a negative
value. (See also OUTCLI.CMD example in the Application Programming
Guide for common servers).
ON THE SERVER SIDE:
Make sure that all the SQLDATA (and if it is a nullable type, SQLIND)
fields of the predefined output sqlda SQLRODA are initialized. For
example, if SQLRODA.SQLD is 2, the following fields must contain some
data (even if the corresponding indicators are negative and the data
is not being passed back to the client):
SQLRODA.1.SQLDATA
SQLRODA.2.SQLDATA
7. Changes to the DB2 Documentation
7.1 For All Refreshed Books - "Changes for This Release" Section
The following changes apply to the "Changes for This Release" sections
of the refreshed books.
o In the DB2 CLI and ODBC Enhancements section, under 'New ODBC
Scalar Functions', the new function which calculates the number of
seconds since midnight is incorrectly named as MIDNIGHT_SECONDS.
This is the correct name for the SYSFUN function, but in ODBC this
name is mapped to SECONDS_SINCE_MIDNIGHT instead.
o In the section entitled "DB2 CLI and ODBC Enhancements", under
"New ODBC Scalar Functions", JULIAN_DAY(day_expr) should be
JULIAN_DAY(date_expr).
o In the section entitled "DRDA Two-Phase Commit", the following
sentence should be clarified as follows, "See the DDCS
Installation and Configuration Guide for your platform for
instructions on how to install the SPM component and
considerations for migrating from Encina Monitor PPC gateway."
o In the section entitled "Graphical Tools", the last sentence in
the section is augmented to read,
For more information on the keywords for the DB2 CLI
initialization file (db2cli.ini), see the "Call Level Interface
Guide and Reference" or the appendix on "Configuring the Client
Workstation" in the "Installing and Using DB2 Clients for Windows"
manual.
Also, the DB2 Performance Monitor is not available from a Windows
client.
7.2 Administration Guide
In chapter 5, "Utilities for Moving Data", there is a section "Using
the EXPORT Utility" which requires a change to the first note. In the
note, the extensions are listed as being from 000 to 999. This is not
correct: the first file extension begins with 001.
In chapter 6, Recovering a Database, there is a section
"Considerations for Managing Log Files" which requires two changes:
o The second bullet should have the following change made: The
sentence in the middle of the bullet beginning "At this time..."
should now read "At this time, if there are no more logs available
and you are rolling forward the entire database, you can use the
ROLLFORWARD command to stop processing."
o The sixth bullet should have the following changes made: The
sentence at the beginning of the third paragraph beginning "If you
terminate..." should now read "If you are rolling forward the
entire database and you terminate the roll-forward recovery (by
specifying the STOP option on the ROLLFORWARD command) and the log
containing the completion of a transaction has not been applied to
the database, then the incomplete transaction will be rolled back
to ensure that the database is left in a consistent state."
In chapter 6, "Recovering a Database", it is explained that
tablespaces can be restored online. However, restoring the tablespace
containing the system catalogs requires an exclusive connection, so it
must be restored offline.
7.3 API Reference
7.3.1 Backup and Restore API's for Vendor Products Appendix DB2-INFO
Data Structure
The following ADSM information has been added to the DB2-INFO data
structure.
Field Name Data Type Description
nodename char The current value of ADSM_NODENAME from
the database manager configuration
parameters.
password char The current value of ADSM_PASSWORD from
the database manager configuration
parameters.
owner char The current value of ADSM_OWNER from
the database manager configuration
parameters.
Nodename, password, and owner can be utilized to enable the restore of
a backup taken on one node, to a different node (for example, during
disaster recovery). By setting these values in the database manager
configuration, they will be passed to the vendor device support
routines on the sqluvint call. Then they can be used in the retrieval
of the correct backup. Owner can be used to specify the name (id) of
the individual who performed the backup.
These values will only be passed in the DB2_INFO structure on a
sqluvint call for a restore. They will be set to null on a backup.
Added to C structure
char *nodename /* name of node at which the backup */
/* was generated */
char *password /* password for the node at which the backup */
/* was generated */
char *owner /* backup originator id */
7.3.2 QUIESCE TABLESPACES FOR TABLE
The following is a correction to the Usage notes:
When a quiesce request is received, the transaction requests a quiesce
mode of SHARE, UPDATE, or EXCLUSIVE for the tablespaces of the table.
The quiesce mode is granted only if there are no conflicting quiesce
modes held by other applications.
A quiescer can upgrade their quiesce mode of a tablespace from a less
restrictive mode to a more restrictive one (for example, from S to U
or X, or U to X). Quiesce modes can not be downgraded. If a quiescer
requests a mode lower than one that is already held, the original
quiesce mode is maintained.
The quiesce mode, authorization ID, and the database agent ID of the
quiescer is recorded in the tablespace table so that the quiesce is
persistent. When the transaction commits or rolls back, the
tablespaces for the table remain quiesced until the quiesce is
explicitly reset.
No tables can be changed while any tablespace for the table is
quiesced in SHARE or UPDATE mode. An application that has the
tablespaces for a table quiesced in EXCLUSIVE mode has full
(exclusive) access to the table and the tablespaces.
There is a limit of five quiescers on a tablespace at any given time.
Since EXCLUSIVE is incompatible with any other mode, and UPDATE is
incompatible with another UPDATE, the five quiescer limit, if reached,
would consist of at least four SHARE modes and a maximum of one UPDATE
mode.
The tablename parameter in the QUIESCE TABLESPACES FOR TABLE command
should be described as follows:
Specifies the unqualified table name. The table cannot be a
system catalog table.
schema.tablename - Specifies the qualified table name. If schema
is not provided, the authorization ID used for the database
connection will be used as the schema. The table cannot be a
system catalog table.
7.4 Command Reference
7.4.1 FORCE APPLICATION
The following corrects the parameter description for the FORCE
APPLICATION command:
The ALL parameter disconnects all users connected to any database
defined in the instance.
7.4.2 ROLLFORWARD DATABASE
The ROLLFORWARD DATABASE command AND STOP, and the STOP parameters are
not required for table space roll-forward.
7.4.3 EXPORT
The following corrects the parameter description for the EXPORT
command:
The MODIFIED BY parameter accepts LOBSINFILE for IXF filetypes.
7.4.4 QUIESCE TABLESPACES FOR TABLE
The following is a correction to the Usage notes:
When a quiesce request is received, the transaction requests a quiesce
mode of SHARE, UPDATE, or EXCLUSIVE for the tablespaces of the table.
The quiesce mode is granted only if there are no conflicting quiesce
modes held by other applications.
A quiescer can upgrade their quiesce mode of a tablespace from a less
restrictive mode to a more restrictive one (for example, from S to U
or X, or U to X). Quiesce modes can not be downgraded. If a quiescer
requests a mode lower than one that is already held, the original
quiesce mode is maintained.
The quiesce mode, authorization ID, and the database agent ID of the
quiescer is recorded in the tablespace table so that the quiesce is
persistent. When the transaction commits or rolls back, the
tablespaces for the table remain quiesced until the quiesce is
explicitly reset.
No tables can be changed while any tablespace for the table is
quiesced in SHARE or UPDATE mode. An application that has the
tablespaces for a table quiesced in EXCLUSIVE mode has full
(exclusive) access to the table and the tablespaces.
There is a limit of five quiescers on a tablespace at any given time.
Since EXCLUSIVE is incompatible with any other mode, and UPDATE is
incompatible with another UPDATE, the five quiescer limit, if reached,
would consist of at least four SHARE modes and a maximum of one UPDATE
mode.
The tablename parameter in the QUIESCE TABLESPACES FOR TABLE command
should be described as follows:
Specifies the unqualified table name. The table cannot be a
system catalog table.
schema.tablename - Specifies the qualified table name. If schema
is not provided, the authorization ID used for the database
connection will be used as the schema. The table cannot be a
system catalog table.
7.4.5 RUNSTATS Enhancement
The new RUNSTATS enhancement allows the user to specify a specific
index for which the statistics should be collected as an alternative
to INDEXES ALL. The following information will be added to the
Command Reference manual:
Wherever ---INDEXES ALL--- appears now in the syntax diagram it should
be replaced with:
--------INDEXES ALL-----------
| |
-----INDEX index-name---
The following explains the new parameter:
AND INDEX INDEX-NAME Update statistics on both the table and its
specified index "index-name", where index-name is a fully
qualified name in the form: schema.index-name.
FOR INDEX INDEX-NAME Update statistics for index index-name only. If
statistics on the table have never been generated, the
database manager calculates statistics on the table as well
as on the index. The index-name is a fully qualified name in
the form: schema.index-name.
This is an example of the new parameter: Collect distribution
statistics on index INDEX1 only:
db2 runstats on table smith.table1 with distribution for index smith.index1
7.5 Call Level Interface Guide and Reference
7.5.1 SQLColAttributes - Get Column Attributes
In the "Function Arguments" section, the Data Type for the rgbDesc
argument is listed as 'SQLCHAR *'. This is incorrect; the correct
type for the argument rgbDesc is 'SQLPOINTER'.
7.5.2 New CLI/ODBC Configuration Keyword
See section 12.4, "New CLI/ODBC Configuration Keywords in db2cli.ini"
for the new CLI/ODBC configuration keywords that are not listed in the
CLI Guide and Reference.
7.6 Database System Monitor Guide and Reference
7.6.1 db_conn_time Data Element
The "first database connect timestamp" (db_conn_time) element is
incorrectly described as being part of the Buffer Pool monitor group.
This element is only part of the Buffer Pool monitor group for table
space snapshots. For Database and Table snapshots, this data element
is part of the Basic monitor group.
7.6.2 Buffer Pool Data Pages Written to Disk
In Chapter 5, "Database System Monitor Data Elements", in the sections
entitled "Buffer Pool Asynchronous Data Writes" and "Buffer Pool
Asynchronous Index Writes" which describe 'pool_async_data_writes' and
'pool_async_index_writes', the following phrase:
The number of times a buffer pool data page was physically written
to disk, by an asynchronous page cleaner
should read as follows in order to indicate that the write could also
have been performed by a prefetcher:
The number of times a buffer pool data page was physically written
to disk, either by an asynchronous page cleaner or by a
prefetcher, which may have had to write dirty pages to disk in
order to make space for the pages to prefetch
7.6.3 DBMON Element Formula
The formula associated with the Buffer Pool Victim Page Cleaners
Triggered (pool_drty_pg_steal_clns) data element is incorrect. The
formula should be:
buffer pool victim page cleaners triggered
/ ( buffer pool victim page cleaners triggered
+ buffer pool threshold cleaners triggered
+ buffer pool log space cleaners triggered)
7.6.4 Buffer Pool Threshold Cleaners Triggered
If this value is set too low, pages might be written out too early,
requiring them to be read back in. If set too high, then too many
pages may accumulate, requiring users to write out pages
synchronously.
7.6.5 Event Start Time
The following sentence has been added to the description for the Event
Start Time:
This element, in the sqlm_evmon_start_event API structure indicates
the start of the event monitor.
7.6.6 Package Cache Inserts/Package Cache Lookups
The formula in the Usage sections for calculating the package cache
hit ratio must be updated as follows:
1 - ( Package Cache Inserts / Package Cache Lookups )
If the hit ratio is high (more than 0.8), the cache is performing
well. A smaller ratio may indicate that the package cache should be
increased.
7.6.7 Status of Database and Status of DB2 Instance
In the Usage section for Status of Database Instance, the following is
a correction: "SQLM_DB2_ACTIVE is the only valid value for this
field."
In the Usage section for Status of Database, the following is a
correction: "SQLM_DB_ACTIVE is the only valid value for this field."
7.7 Planning Guide
7.7.1 Segments Versus 4KB Pages
This section applies to the SQL Reference manual as well.
In the chapter entitled "Incompatibilities Between Releases", in the
"Configuration Parameters" section, under the heading "Segments Versus
4KB Pages", the following statement exists:
All configuration parameters in OS/2 that were expressed in
segments in Version 1 are now expressed in 4KB pages.
The following text expands on the previous sentence:
For an application which makes a call to a configuration API that
involves one of the following tokens names, the points listed
below apply:
SQLF_DBTN_DBHEAP
SQLF_DBTN_APPLHEAPSZ
SQLF_DBTN_STMTHEAP
SQLF_DBTN_SORTHEAP
1. If the application was compiled against Version 1, then if the
binary is run against either Version 1 or Version 2, the value
specified will be treated as being specified in segments.
2. If the application is re-compiled against Version 2, the
values specified will be treated by the Version 2 product as
being specified in 4K pages.
3. If, however, the token names were not coded in the
application, and instead the actual Version 1 token values
were coded (for example, instead of coding with the token name
SQLF_DBTN_DBHEAP, the Version 1 token value of 8 is used),
then if compiled against either Version 1 or Version 2, these
parameters will be treated as being specified in segments.
(The token values for these tokens have changed since Version
1).
In Version 2, in addition to being specified now in 4K pages, the
database configuration parameter sortheap on OS/2 has also changed
from being specified as a two-byte integer to being specified as a
four-byte integer. If an application written in Version 1 code using
the token SQLF_DBTN_SORTHEAP is recompiled against Version 2, it is
treated as a two-byte integer as in Version 1 (although the units will
change from segments to 4KB pages). However, the full range of values
will not be accessible to a two-byte integer. Therefore, it is
recommended that the Version 2 token SQLF_DBTN_SORT_HEAP be used, and
the application code changed to reflect that the API will treat the
token SQLF_DBTN_SORT_HEAP as indicating a four-byte integer, in order
to migrate the application code from Version 1 to Version 2,
NOTE: In Version 2, all code using the Version 2 token names (see the
API Reference) will be treated the same on UNIX and OS/2 platforms
with respect to the data type and the units. (The ranges however may
differ).
7.7.2 BUFFPAGE and SORTHEAP
In the chapter entitled "Incompatibilities Between Releases", in the
"Configuration Parameters" section, under the heading "BUFFPAGE and
SORTHEAP", the following clarification should be added. Also, please
note that the word "byte" should be "bit" in this section in the
Planning Guide.
Symptom:
OS/2 - If an application was compiled against DB2 for OS/2 Version
1 using the old tokens, the binary will run as expected against
Version 2 as well.
However, since the old tokens indicate that the parameter is to be
treated as being specified as a 16 bit unsigned integer, the full
Version 2 range of values for BUFFPAGE (up to 524288) will not be
available. Thus, if BUFFPAGE is updated (by a Version 2
application or by a Version 2 CLP) to a value such as 100,000, and
the Version 1 application is attempting to GET that value, it will
not be able to reflect the true value.
(Note that with the range in Version 2 for sortheap being 524288 4
KB pages, and with SORTHEAP in DB2 for OS/2 Version 1 being
specified in segments, this translates to a new range of up to
32768 segments. This value is in the range of an unsigned 16 bit
integer, and hence the DB2 for OS/2 Version 1 binary should not
have the same range restrictions for SORTHEAP as it does for
BUFFPAGE.)
Response:
For a Version 1 binary complied under DB2 for OS/2 Version 1, the
values used for BUFFPAGE should be restricted to that which can be
stored in an unsigned 16 bit integer. For most situations this
should be sufficient.
When recompiling the application against Version 2 on any
platform, use the new tokens for these parameters.
7.8 Problem Determination Guide
7.8.1 First Failure Data Capture
In Chapter 2, "Logging and Tracing", in the section entitled
"Configuration Parameters" under "First Failure Data Capture", the
fourth bullet currently reads:
DB2 "UPDATE DATABASE MANAGER CONFIGURATION USING /tmp/errors"
It is not correct. It should read as follows:
DB2 "UPDATE DATABASE MANAGER CONFIGURATION USING DIAGPATH X: \tmp\errors"
7.9 Configuration Parameters
7.9.1 DIR_CACHE
The DDCS User's Guide incorrectly refers to the DIR_CACHE
configuration parameter as DIRCACHE.
Please refer to the directory cache documentation in the Installing
and Using OS/2 Clients manual, Appendix D, "Configuring the Client
Workstation", before attempting to catalog databases.
7.9.2 MAXAPPLS
The maximum value of MAXAPPLS is 5000 on UNIX** systems and 1500 on
OS/2 systems. The prior value was 1000. The following manuals
incorrectly show the prior maximum value of 1000:
o DB2 for OS/2 Planning Guide
o DB2 for AIX Planning Guide
7.9.3 TP_MON_NAME
The current DB2 documentation for this parameter applies to UNIX
environments only. For OS/2 environments, this parameter should
contain the path and name of the DLL in an external transaction
manager product containing functions ax_reg and ax_unreg if an XA
Distributed Transaction Processing environment is being used.
The following manuals described this configuration parameter
incorrectly:
o Installing and Using DB2 Clients for Windows
o Planning Guide
7.9.4 REC_HIS_RETENTN
The Planning Guide (Appendix C) and Installation and Operation Guides
(Appendix D) indicate that the maximum value for the database
configuration parameter rec_his_retentn is 32767. This is incorrect.
The correct value is 30000.
7.9.5 Configuration Parameters (YES/NO and ON/OFF Values)
A number of database configuration parameters (including logretain and
userexit) are described as having acceptable values of either
"Yes"/"No" or "On"/"Off" in the Online Help, and Planning Guide. In
some cases, these do not correspond to what the Command Line Processor
(CLP) displays.
For example, the Planning Guide indicates that the acceptable value
for the database configuration parameter autorestart is either Yes or
No, whereas CLP displays it as being either ON or OFF. In this
context "Yes" is to be considered synonymous with "On", and "No" with
"Off".
7.9.6 DFT_QUERYOPT
CONFIGURATION TYPE Database
PARAMETER TYPE Configurable
DEFAULT [RANGE] 5 [ 0-9 ]
UNIT OF MEASUREMENT Query optimization class (see below)
RELATED PARAMETERS None
The query optimization class is used to direct the optimizer to use
different degrees of optimization in compiling SQL queries.
DFT_QUERYOPT has been added as a database configuration parameter to
provide additional flexibility in setting the default query
optimization class used when neither the SET CURRENT QUERY
OPTIMIZATION statement nor the QUERYOPT bind option are used
explicitly.
The query optimization classes currently defined are:
0 minimal query optimization
1 roughly comparable to DB2 Version 1
3 moderate query optimization
5 significant query optimization with heuristics to limit the
effort expended on selecting an access plan. This is the
default.
7 significant query optimization
9 maximal query optimization
For additional information and guidance for selecting a suitable query
optimization class see the Administration Guide.
The API reference describes how a program can retrieve and modify
database configuration parameters. Table 39 provides a list
describing each parameter. The additional information for the default
query optimization class is:
PARAMETER NAME dft_queryopt
TOKEN SQLF_DBTN_DFT_QUERYOPT
TOKEN VALUE 57
DATA TYPE SINT32
The Database Director does not support the update or the display of
this parameter.
7.10 Building Your Applications
7.10.1 Building Your Embedded SQL Applications
In this chapter, some build commands files use the backslash (\)
character for continuation of the command (for example, the "bldibmcb"
command file on page 35). This is incorrect and should be ignored
throughout.
7.10.2 Building FORTRAN Stored Procedures
In the "Building FORTRAN Stored Procedures" section on page 46, you
require the following statement in your stored procedure:
c$pragma aux (_syscall) <sp_nm> parm ( data_reference, data_reference, \
c data_reference, data_reference )
In addition, the compile statement for the "bldforsr" command file
should be:
wfc386 /noref %1.for
and the link statement should be:
wlink sys os2v2 dll export %1 file %1.obj library db2api.lib library
os2386.lib option stack=32000
The build scripts and makefile in directory "...\sqllib\samples\
fortran\..." have been updated accordingly.
7.10.3 Definition Files for UDFs and Stored Procedures
The example definition file as documented in the "Building Your
Applications book, on page 28, should be:
LIBRARY UDF INITINSTANCE TERMINSTANCE
DESCRIPTION 'Library for DB2 User Defined Functions'
PROTMODE
DATA
MULTIPLE
NONSHARED
CODE
LOADONCALL
SHARED
EXPORTS
INCREASE = increase @1
RAISE = raise @2
WORDCOUNT = wordcount @3
FINDVWL = findvwl @4
CTR = ctr @5
ILOB = ilob @6
LENI = leni @7
PROMOTE = promote @8
The example definition as originally documented:
o May fail in a re-entrant environment
o May not free up memory allocated by OS/2 and/or C-RUNTIME during
loading or unloading the DLL.
The "DATA MULTIPLE NONSHARE" addresses the first problem. The
"INITINSTANCE" and "TERMINSTANCE" addresses the second. All the
sample ".def" files for stored procedures and UDFs have been updated
accordingly in directory "...\sqllib\samples..." for C and COBOL.
7.10.4 C++ Type Decoration Consideration
When writing a stored procedure of a UDF using C++, you may want to
consider declaring the procedure of UDF as:
extern "C" ...procedure or function declaration...
The extern "C" prevents type decoration or mangling of the function
name by the C++ compiler. Without this declaration, you have to
include all the type decoration for the function name when you call
the stored procedure, or issue the CREATE FUNCTION statement.
7.10.5 IBM COBOL Build Scripts
In the section "IBM COBOL VisualSet for OS/2" beginning on page 35,
observe the following changes:
o All references to the "IBM COBOL VisualSet for OS/2" compiler,
should be "IBM VisualAge for COBOL for OS/2" in this section.
o The link step, as documented for the "bldibmcb" and "bldicobs"
command files now utilizes the "IBM Linker for OS/2" linker. For
bldibmcb.cmd, the link step should be:
ilink %1.obj checkerr.obj db2api.lib /ST:32000 /PM:VIO /NOI /DEBUG
For bldicobs.cmd, (for building stored procedures) the link step
should be:
ilink %1.obj checkerr.obj %1.def db2api.lib /ST:32000 /PM:VIO /NOI /DEBUG
Both of these build command files have been updated in the
"...\sqllib\samples\cobol\..." directory.
7.10.6 Miscellaneous
Compound SQL statements containing user-defined SQLDAs are not
permitted in a 16-bit application on OS/2.
All the command files to build applications using Micro Focus COBOL
now require the addition of the db2gmf16 library (see the section
"Considerations when Using Micro Focus Cobol" in this README file for
more information) in the link step. In addition, use the "os2286"
library in place of the "doscalls" library in the link step as
documented. The updated build command files are provided in
"...\sqllib\samples\cobol\...".
7.11 Installation and Operation Guides
In Appendix J
o Under Datetime Values:
- In the table "Formats for String Representations of Dates",
change the Site-defined (Local) description of Date Format
from "Depends on database country code" to "Depends on
application country code".
- In the table "Formats for String Representations of Times",
change the Site-defined (Local) description of Time Format
from "Depends on database country code" to "Depends on
application country code".
o Under Date and Time Formats change the fourth list item (Output
Date Format) preceding the table "Date and Time Formats by Country
Code", from "The default output date format is equal to the local
date format." to "The default output date format is described in
Table nn. Date and Time Formats by Country Code, under column
Default Output Date Format"
In the English version of the DB2 for OS/2 Installation and Operation
manual:
o The screen capture on page 13 should appear as the lower screen
capture on page 14.
o The terms Communication Manager and Communications Server both
appear on page 45. At the time of this writing, most users will
have Communications Manager. Use this name in place of
Communications Server.
7.12 Installing and Using OS/2 Clients
7.12.1 New CLI/ODBC Configuration Keywords
See section 12.4, "New CLI/ODBC Configuration Keywords in db2cli.ini"
for the new CLI/ODBC configuration keywords that are not listed in the
CLI Guide and Reference.
7.13 SQL Reference
7.13.1 ALTER TABLE Notes Addition
The following bullet should be included with the "Notes" found in the
section on "ALTER TABLE":
o Adding a column with a default value that involves an explicit
value or an implicit or explicit datetime special register, will
cause all packages with an insert usage on the object table to be
invalidated.
7.13.2 SQL Communications Area (SQLCA)
Replace the existing text for the "Field values" column for the
SQLWARN1 entry in the "Fields of SQLCA" table, with the following:
Contains X or W if the value of a string column was truncated when
assigned to a host variable. Contains N if the null terminator was
truncated.
The value of X is returned when all of the following conditions are
met:
o a mixed code page connection exists where conversion of character
string data from the database codepage to the application codepage
may involve a change in the length of the data,
o the cursor is blocked
o an indicator variable was provided by the application.
The value returned in the indicator variable will be the length of the
resultant character string in the application's code page.
In all other cases involving data truncation, versus null terminator
truncation, W will be returned. The value returned in the indicator
variable in this case will be the length of the resultant character
string in the code page of the select list item (either the
application's, the database's or none).
7.13.3 GROUP-BY and ORDER-BY Changes
7.13.3.1 Expressions Section
The following changes should be applied to the "Expressions" section
of the "SQL Reference Changes" section, for CASE Expressions. Replace
the second paragraph with the following:
If the CASE expression is in a select list, a VALUES clause, an IN
predicate, a GROUP BY clause, or an ORDER BY clause, the
search-condition in a searched-when-clause cannot be a quantified
predicate, IN predicate using a fullselect, or an EXISTS predicate
(SQLSTATE: 42625).
7.13.3.2 Queries Section
In the select-clause section of the "Queries" section, replace the
first bullet in the "If GROUP BY or HAVING is used:" list with:
o An expression X (not a column function) used in the select list
must have a GROUP BY clause with:
- a grouping-expression that matches the portion of the X
referencing columns of R (see group-by clause) or
- each column of R referenced in X as a separate
grouping-expression.
GROUP-BY-CLAUSE: Replace the text of the group-by-clause section of
the "Queries" section with the following:
+--------------------------------------------------------------------+
| |
| ┌─,───────────────────┐ |
| ─ GROUP BY ─ grouping-expression ┴───────────────────────────── |
| |
+--------------------------------------------------------------------+
The GROUP BY clause specifies an intermediate result table that
consists of a grouping of the rows of R. R is the result of the
previous clause of the subselect.
A grouping-expression is an expression used in defining the grouping
of R. Each column-name included in grouping-expression must
unambiguously identify a column of R (SQLSTATE 42702 or 42703). The
length attribute of each grouping-expression must not be more than 254
bytes (SQLSTATE 42907). An grouping-expression cannot include a
scalar-fullselect (SQLSTATE 42822) or any function that is variant or
has an external action (SQLSTATE 42845).
The result of GROUP BY is a set of groups of rows. In each group of
more than one row, all values of each grouping-expression are equal;
and all rows with the same set of values of the grouping-expressions
are in the same group. For grouping, all null values from a
grouping-expression are considered equal.
A grouping-expression can be used(1) in a search condition in a HAVING
clause, in an expression in a SELECT clause or in a
sort-key-expression of an ORDER BY clause (see order-by clause for
details). In each case, the reference specifies only one value for
each group. For example, if the grouping-expression is col1+col2 then
an allowed expression in the select list would be col1+col2+3.
Associativity rules for expressions would disallow the similar
expression, 3+col1+col2, unless parentheses are used to ensure that
the corresponding expression is evaluated in the same order. Thus,
3+(col1+col2) would also be allowed in the select list.
If the grouping-expression contains varying-length strings with
trailing blanks, the values in the group can differ in the number of
trailing blanks and may not all have the same length. In that case, a
reference to the grouping-expression still specifies only one value
for each group, but the value for a group is chosen arbitrarily from
the available set of values. Thus, the actual length of the result
value is unpredictable.
As noted, there are some cases where the GROUP BY clause cannot refer
directly to a column that is specified in the SELECT clause as an
expression (scalar-fullselect, variant or external action functions).
To group using such an expression, use a nested table expression or a
common table expression to first provide a result table with the
expression as a column of the result. For an example using nested
table expressions, see Example 9.
The following example is to be added at the end of the examples
section.
Example 9: Display the average education level and salary for 5
random groups of employees.
This query requires the use of a nested table expression to first set
a random value for each employee so that it can subsequently be used
in the GROUP BY clause.
SELECT RANDID , AVG(EDLEVEL), AVG(SALARY)
FROM (
SELECT EDLEVEL, SALARY,
INTEGER(RAND()*5) AS RANDID
FROM EMPLOYEE
) AS EMPRAND
GROUP BY RANDID
ORDER-BY-CLAUSE: Replace the text of the order-by-clause section of
the "Queries" section with the following:
+--------------------------------------------------------------------+
| |
| ┌─,───────────────────┐ |
| │ ┌─ASC──┐ │ |
| ─ ORDER BY ── sort-key ─┼──────┼─┴───────────────────────────── |
| └─DESC─┘ |
| |
| SORT-KEY: |
| ├──┬─simple-column-name ─┬───────────────────────────────────────┤ |
| ├─simple-integer──────┤ |
| └─sort-key-expression─┘ |
| |
+--------------------------------------------------------------------+
The ORDER BY clause specifies an ordering of the rows of the result
table. If a single sort specification (one sort-key with associated
direction) is identified, the rows are ordered by the values of that
sort specification. If more than one sort specification is
identified, the rows are ordered by the values of the first identified
sort specification, then by the values of the second identified sort
specification, and so on. The length attribute of each sort-key must
not be more than 254 characters for a character column or 127
characters for a graphic column (SQLSTATE 42907).
A named column in the select list may be identified by a sort-key that
is a simple-integer or a simple-column-name. An unnamed column in the
select list must be identified by an simple-integer or, in some cases,
by a sort-key-expression that matches the expression in the select
list (see details of sort-key-expression). A column is unnamed if the
AS clause is not specified and it is derived from a constant, an
expression with operators, or a function.(2)
Ordering is performed in accordance with the comparison rules
described in Chapter 3. The null value is higher than all other
values. If the ORDER BY clause does not completely order the rows,
rows with duplicate values of all identified columns are displayed in
an arbitrary order.
For the purposes of the descriptions that follow, consider an ordering
subselect to be the fullselect of a select-statement that is a
subselect.
simple-column-name
Usually identifies a column of the result table. In this case,
simple-column-name must be the column name of a named column in
the select list.
The simple-column-name may also identify a column name of a table,
view or nested table identified in the FROM clause of an ordering
subselect. An error occurs if the ordering subselect:
o specifies DISTINCT in the select-clause (SQLSTATE 42822)
o produces a grouped result and the simple-column-name is not a
grouping-expression (SQLSTATE 42803).
Determining which column is used for ordering the result is
described under "Column name in sort keys" (see "Notes").
simple-integer
Must be greater than 0 and not greater than the number of columns
in the result table (SQLSTATE 42805). The integer n identifies
the nth column of the result table.
sort-key-expression
An expression that is not simply a column name or an unsigned
integer constant. The fullselect of the select-statement must be
a subselect to use this form of sort-key. The sort-key-expression
cannot include a correlated scalar-fullselect (SQLSTATE 42703) or
a function with an external action (SQLSTATE 42845).
Any column-name within a sort-key-expression must conform to the
rules described under "Column names in sort keys" (see "Notes").
There are a number of special cases that further restrict the
expressions that can be specified.
o DISTINCT is specified in the SELECT clause of the ordering
subselect (SQLSTATE 42822).
The sort-key-expression must match exactly with an expression
in the select list of the ordering subselect
(scalar-fullselects are never matched).
o The ordering subselect is grouped (SQLSTATE 42803).
The sort-key-expression can:
- be an expression in the select list of the ordering
subselect,
- include a grouping-expression from the GROUP BY clause of
the ordering subselect
- include a column function, constant or host variable.
ASC
Uses the values of the column in ascending order. This is the
default.
DESC
Uses the values of the column in descending order.
NOTES
o COLUMN NAMES IN SORT KEYS:
- The column name is qualified.
The fullselect in the select-statement must be a subselect
(SQLSTATE 42877). The column name must unambiguously identify
a column of some table, view or nested table in the FROM
clause of the ordering subselect (SQLSTATE 42702). The value
of the column is used to compute the value of the sort
specification.
- The column name is unqualified.
-- The fullselect of the select-statement is a subselect.
If the column name is identical to the name of more than
one column of the result table, the column name must
unambiguously identify a column of some table, view or
nested table in the FROM clause of the ordering subselect
(SQLSTATE 42702). If the column name is identical to one
column, that column is used to compute the value of the
sort specification. If the column name is not identical
to a column of the result table, then it must
unambiguously identify a column of some table, view or
nested table in the FROM clause of the fullselect in the
select-statement (SQLSTATE 42702).
-- The fullselect of the select-statement is not a subselect
(simple-column-name only).
The column name must not be identical to the name of more
than one column of the result table (SQLSTATE 42702). The
column name must be identical to exactly one column of the
result table (SQLSTATE 42707) and this column is used to
compute the value of the sort specification.
o LIMITS: The use of a sort-key-expression or a simple-column-name
where the column is not in the select list may result in the
addition of the column or expression to the temporary table used
for sorting. This may result in reaching the limit of the number
of columns in a table or the limit on the size of a row in a
table. Exceeding these limits will result in an error if a
temporary table is required to perform the sorting operation.
NOTES:
(1) In general, the grouping-expression must match part of the
expression where it is used. If the concatenation operator is
used, the grouping-expression must be used exactly as the
expression where it is used.
(2) The rules for determining the name of result columns for a
fullselect that involves set operators (UNION, INTERSECT, or
EXCEPT) can be found in 'fullselect' in the same chapter.
7.14 Application Programming Guide
The following information on the bottom of page 24, reads:
The host-language linker creates an executable application. For
example,
o With OS/2, the application can be an executable file or a dynamic
link library.
o With Windows applications, the application is an executable file.
o With UNIX-based systems, the application can be an executable load
module or a shared library.
It should read as follows:
The host-language linker creates an executable application. For
example,
o On OS/2 or Windows platforms, the application can be an executable
file or a dynamic link library (DLL).
o On UNIX-based systems, the application can be an executable load
module or a shared library.
Note that although applications can be DLLs on Windows platforms, for
Windows 3.1 or Windows 95, the DLLs are loaded directly by the
application and not by the DB2 database manager. On Windows NT, DLLs
can be loaded by the database manager. Stored procedures, which are
normally built as DLLs (on OS/2 and Windows NT) or shared libraries
(on UNIX-based platforms) are unsupported on the Windows 3.1 and
Windows 95 platforms as they are client-only. For information on
using stored procedures, refer to Chapter 5 in the DB2 Application
Programming Guide.
7.15 References to CompuServe
In the CompuServe section found in the introductions of the DB2
manuals, and at the end of the manuals in the section on contacting
IBM, please note the following corrections:
o "Compuserve" should be "CompuServe".
o "IBMDB2 Family Forum" should be the "IBM DB2 Family Forum".
7.16 Messages Reference
7.16.1 New Messages
The following messages are to be added to the Messages Reference book.
SQL2548N DATABASE CODEPAGE INDICATED IN BACKUP IMAGE DIFFERS FROM
THAT OF THE CURRENT ON-DISK DATABASE. THE RESTORE OPERATION
HAS FAILED.
EXPLANATION: The database contained in the backup image contains data
stored ina codepage different from that of the database into which the
data is being restored.
This problem can be caused in three ways:
1. The database you are restoring into has a different codepage than
that of the database in the backup image.
2. The user is restoring into a new database from a session with a
different codepage than the backup.
3. The backup, image is corrupt, and contains invalid character set
information.
USER RESPONSE:
1. If restoring over an existing database, make sure that the
codepage of the existing database matches that of the backup
image.
2. If restoring to a new database, make sure that the codepage of the
session from which the restore command was issued is the same as
that of the backup image.
3. Contact IBM Service.
SQL0214N AN EXPRESSION STARTING WITH "<EXPRESSION-START>" IN THE
"<CLAUSE-TYPE>" CLAUSE IS NOT VALID. REASON CODE =
"<REASON-CODE>".
EXPLANATION: The expression identified by the first part of the
expression "<expression-start>" in the "<clause-type>" clause is not
valid for the reason specified by the "<reason-code>" as follows:
1 The fullselect of the select-statement is not a subselect.
Expressions are not allowed in the ORDER BY clause for this type
of select-statement. This reason code occurs only when
"<clause-type>" is ORDER BY.
2 DISTINCT is specified in the select clause and the expression
cannot be matched exactly with an expression in the select list.
This reason code occurs only when "<clause-type>" is ORDER BY.
3 Grouping is caused by the presence of a column function in the
ORDER BY clause. This reason code occurs only when
"<clause-type>" is ORDER BY.
4 Expression in a GROUP BY clause cannot be a scalar-fullselect.
This reason code occurs only when "<clause-type>" is GROUP BY.
The statement cannot be processed.
USER RESPONSE: Modify the select-statement based on the reason
specified by the "<reason-code>" as follows:
1 Remove the expression from the ORDER BY clause. If attempting to
reference a column of the result, change the sort key to the
simple-integer or simple-column-name form.
2 Remove DISTINCT from the select clause or change the sort key to
the simple-integer or simple-column-name form.
3 Add a GROUP BY clause or remove the column function from the ORDER
BY clause.
4 Remove any scalar-fullselect from the GROUP BY clause. If
grouping is desired on a column of the result that is based on a
scalar-fullselect use a nested table expression or a common table
expression to first provide a result table with the expression as
a column of the result.
SQLCODE: -214
SQLSTATE: 42822
7.16.2 Changed Messages
The following is additional information for message SQL1419N:
EXPLANATION: The package "<pkgname>" can not be executed. The package
was bound on a database manager level which lacks EUC (Extended Unix
Code) and non-SBCS character conversion support. An attempt to
execute the package from one of these environments was detected.
USER RESPONSE: To execute the package in the current environment,
explicitly rebind the named package using either the REBIND or the
BIND command. Otherwise, execute the package from the original
binding environment.
The following messages are changed as follows:
SQL0119N AN EXPRESSION STARTING WITH "<EXPRESSION-START>" IS
SPECIFIED IN A SELECT CLAUSE, HAVING CLAUSE, OR ORDER BY
CLAUSE IS NOT SPECIFIED IN THE GROUP BY CLAUSE OR IT IS IN A
SELECT CLAUSE, HAVING CLAUSE, OR ORDER BY CLAUSE WITH A
COLUMN FUNCTION AND NO GROUP BY CLAUSE IS SPECIFIED.
EXPLANATION: The SELECT statement has one of the following errors:
o The identified expression and a column function are contained in
the SELECT clause, HAVING clause, or ORDER BY clause but there is
no GROUP BY clause
o The identified expression is contained in the SELECT clause,
HAVING CLAUSE, or ORDER BY clause but is not in the GROUP BY
clause.
The identified expression is an expression that starts with
"<expression-start>". The expression may be a single column name.
The statement cannot be processed.
USER RESPONSE: Correct the statement by including the expression in
the GROUP BY clause that are in the SELECT clause, HAVING clause or
ORDER BY clause or by removing the column function from the SELECT
statement.
SQLCODE: -119
SQLSTATE: 42803
SQL0120N A WHERE CLAUSE, GROUP BY CLAUSE, SET CLAUSE, OR SET
TRANSITION-VARIABLE STATEMENT CONTAINS A COLUMN FUNCTION.
EXPLANATION: A WHERE clause can contain a column function only if
that clause appears within a subquery of a HAVING clause and the
argument of the column function is a correlated reference to a group.
A GROUP BY clause can contain a column function only if the argument
of the column function is a correlated reference to a column in a
different subselect than the one containing the GROUP BY clause. A
SET clause of an UPDATE statement or a SET transition-variable
statement can only include a column function within a fullselect on
the right hand side of an assignment.
The statement cannot be processed.
USER RESPONSE: Change the statement so that the column function is
not used or used only where it is supported.
SQLCODE: -120
SQLSTATE: 42903
SQL0206N "<NAME>" IS NOT A COLUMN IN AN INSERTED TABLE, UPDATED
TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE OR IS NOT A
VALID TRANSITION VARIABLE FOR THE SUBJECT TABLE OF A
TRIGGER.
EXPLANATION: This error can occur in the following cases:
o For an INSERT or UPDATE statement, the specified column is not a
column of the table, or view that was specified as the object of
the insert or update.
o For a SELECT or DELETE statement, the specified column is not a
column of any of the tables or views identified in a FROM clause
in the statement.
o For an ORDER BY clause, the specified column is a correlated
column reference in a subselect, which is not allowed.
o For a CREATE TRIGGER statement:
- A reference is made to a column of the subject table without
using an OLD or NEW correlation name.
- The left hand side of an assignment in the SET
transition-variable statement in the triggered action
specifies an old transition variable where only a new
transition variable is supported.
The statement cannot be processed.
USER RESPONSE: Verify that the column and table names are specified
correctly in the SQL statement. For a SELECT statement, ensure that
all the required tables are named in the FROM clause. For a subselect
in an ORDER BY clause, ensure that there are no correlated column
references. If a correlation name is used for a table, verify that
subsequent references use the correlation name and not the table name.
For a CREATE TRIGGER statement, ensure that only new transition
variables are specified on the left hand side of assignments in the
SET transition-variable statement and that any reference to columns of
the subject table have a correlation name specified.
SQLCODE: -206
SQLSTATE: 42703
SQL0208N THE ORDER BY CLAUSE IS NOT VALID BECAUSE COLUMN "<NAME>" IS
NOT PART OF THE RESULT TABLE.
EXPLANATION: The statement is not valid because a column "<name>"
specified in the ORDER BY list is not specified in the SELECT list and
is not in the result table. Only columns in the result table can be
used to order that result when the fullselect of the select-statement
is not a subselect.
The statement cannot be processed.
USER RESPONSE: To correct the syntax of the statement, either add the
specified column to the result table or delete it from the ORDER BY
clause.
SQLCODE: -208
SQLSTATE: 42707
SQL0582N A CASE EXPRESSION IN A SELECT LIST, VALUES CLAUSE, IN
PREDICATE, OR ORDER BY CLAUSE CANNOT INCLUDE A QUANTIFIED
PREDICATE, AN IN PREDICATE USING A FULLSELECT OR AN EXISTS
PREDICATE.
EXPLANATION: A search condition of a CASE expression is:
o a quantified predicate (one using SOME, ANY, or ALL),
o an IN predicate using a fullselect or
o an EXISTS predicate
and the CASE expression is part of:
o a select list
o a VALUES clause
o an IN predicate
o an GROUP BY clause or
o an ORDER BY clause.
Such CASE expressions are not supported.
The statement cannot be processed.
USER RESPONSE: Remove the use of the quantified predicate, IN
predicate, or EXISTS predicate from the CASE expression. In some
cases, it may be possible to write the query so that the CASE
expression is not part of the select list or an IN predicate.
SQLCODE: -582
SQLSTATE: 42625
SQL0583N THE USE OF FUNCTION "<FUNCTION.NAME>" IS INVALID BECAUSE IT
IS VARIANT OR HAS AN EXTERNAL ACTION.
EXPLANATION: The function "<function-name>" is defined as a variant
function or a function with an external action. This type of function
is not supported in the context in which it is used. The contexts in
which these are not valid are:
o as the first operand of a BETWEEN predicate
o in the expression prior to the first WHEN keyword in a
simple-case-expression.
o in an expression of a GROUP BY clause
o in an expression of an ORDER BY clause (external action only)
The statement cannot be processed.
USER RESPONSE: If the use of a variant or external action function
was not intended, substitute a function without these characteristics.
If the behaviour associated with the variant or external action
function is intentional, use the alternate form of the statements that
make that intent explicit.
o Instead of the BETWEEN predicate, use the corresponding statement
using the equivalent combination of comparison predicates (a>=b
and a<=c instead of a between b and c).
o Instead of a simple-when-clause, use the corresponding
searched-when-clause where the function would get specified in
each search-condition.
o Remove the variant or external action function from the GROUP BY
clause. If grouping is desired on a column of the result that is
based on a variant or external action function use a nested table
expression or a common table expression to first provide a result
table with the expression as a column of the result.
o Remove the external action function from the ORDER BY clause. If
the column is part of the result set of the query, change the
expression in the ORDER BY clause to the simple-integer or
simple-column-name form of the sort key.
SQLCODE: -583
SQLSTATE: 42845
8. DDCS-Specific Information
8.1 Documentation Changes
8.1.1 Installation Requirements
The DB2 Planning Guides for OS/2 and AIX contain out of date
information for memory and hard disk space requirements for DDCS
V2.3.1. Please refer to the information in Chapter 1 of the updated
DDCS Installation and Configuration Guides.
8.1.2 National Language (Code Page and Conversion) Information
Changes
Some of the information contained in the V2.1.0 DDCS User's Guide has
been superseded by updated versions of tables and other information
relating to National Language, and related considerations such as
character set conversion rules. Please refer to updated information
in the DDCS Installation and Configuration Guide (S20H-4795-01).
8.1.3 OS/2 and UNIX-Based Databases
Throughout the DDCS manuals, references to OS/2 and UNIX-based
databases should be references to the DB2 Common Server Family of
Products instead.
8.1.4 SET CURRENT PACKAGESET Support
In the DDCS User's Guide in Chapter 7, "Programming in a DRDA
Environment", the following sentence should be included:
The DDCS support for the SET CURRENT PACKAGESET command is now
available for DB2 for MVS/ESA and DB2 for Common Servers.
8.1.5 DB2 for OS/400 Column Types and Length
In the DDCS User's Guide in Chapter 7, "Programming in a DRDA
Environment", in the section that describes DB2 for OS/400 Column
Types and Length, the following text should exist:
DB2 for OS/400 Version 3.1 supports the column types GRAPHIC and
VARGRAPHIC. Please refer to your DRDA AS documentation for the most
up-to-date features supported by your DRDA Server.
8.1.6 Binding DDCS Utilities to an OS/400 Database
The following step is documented in the DDCS User's Guide in Chapter 4
to allow you to bind all the DDCS utilities to the DRDA server
database; however, an error occurs. The error is a result of using
the GRANT bind option when binding a list of bind files. The bind
receives a -901 sqlcode from the OS/400 database on the third bind
file in the list.
db2 bind @ddcs400.lst blocking all sqlerror continue grant public
messages ddcs400.msg
8.1.6.1 Possible Solutions
1. PTF SF23634 is available for the OS/400 V3.1 database to fix this
problem.
2. Alternatively, use either of the following two methods:
a. Issue separate bind requests for each bind file in
ddcs400.lst, (19 bind files resulting in 19 bind requests),
for example:
===> connect step
db2 connect to DBALIAS user USERID using PASSWORD
===> bind step
db2 bind db2ajgrt.bnd blocking all sqlerror continue grant public
messages db2ajgrt.msg
db2 bind db2clics.bnd blocking all sqlerror continue grant public
messages db2clics.msg
...
db2 bind db2uimpm.bnd blocking all sqlerror continue grant public
messages db2uimpm.msg
===> drop connection
db2 connect reset
b. Issue the bind request against ddcs400.lst but omit the GRANT
PUBLIC bind option. As a separate step, grant execute
permission to public on each of the packages. Use the
ddcspkgn utility to determine the package names of the bind
files in ddcs400.lst. An example is provided below. Refer to
Chapter 4 of the DDCS User's Guide for more details on this
option.
===> connect step
db2 connect to DBALIAS user USERID using PASSWORD
===> bind step
db2 bind @ddcs400.lst blocking all sqlerror continue
messages ddcs400.msg
===> determine the package names of bind files in ddcs400.lst
ddcspkgn @ddcs400.lst
===> grant step
db2 grant execute on package SQLAB4C0 to public
db2 grant execute on package SQLL14C0 to public
...
db2 grant execute on package SQLUF4C0 to public
===> drop connection
db2 connect reset
8.1.7 Binding Utilities for Back-Level Clients
In the DDCS User's Guide, in Appendix D, "Binding Utilities for Back
Level Clients", the command to find out the package names for bind
files should read ddcspkgn @bindfile.lst, NOT ddcspkgn bindfile.lst.
8.1.8 SPM Instance Name
In Appendix D. "LU6.2 Sync Point Manager Considerations", in the
section "Installing the SPM", the DDCS Installation and Configuration
Guides for OS/2 and AIX both refer to the "SPM database". This should
read "SPM instance", as referred to in the DB2 Command Reference:
Before issuing LIST DRDA INDOUBT TRANSACTIONS, the application
must be connected to the Sync Point Manager (SPM) instance. Use
the SPM_NAME database manager configuration parameter as the
dbalias on the CONNECT statement.
Throughout the "Installing SPM" section in Appendix D, wherever the
words "SPM database" appear alone without the word "alias" immediately
following them, please replace the word "database" with the word
"instance".
The documentation will be changed in the next update of these
publications.
8.2 Syncpoint Manager (SPM)
8.2.1 Prerequisite PTFs
All DB2/MVS V3.1 systems
PTFs UN73393 and UN76673 (fixes APARs PN67179 and PN70102)
All DB2/MVS V4.1 systems
PTF UN76674 (fixes APAR PN70102 )
All DB2/400 V3.1 systems
PTF SF26564 (fixes APAR SA46917)
APAR SA48935 (no PTF available at time of printing README)
PTF SF26804 (fixes for OS/400 V3R1M0, product 5763SS1)
8.2.2 IBM Communications Server for OS/2 Warp Version 4
Terminating IBM Communications Server for OS/2 Warp Version 4 while
the SPM is active causes the SPM to stop. Once IBM Communications
Server for OS/2 Warp Version 4 is restarted, you must issue a db2stop
and db2start on the DDCS Multi-User Gateway in order to allow the SPM
to reinitialize.
8.3 Authentication Default
For any system database directory entry that DDCS uses for
establishing a connection, if the authentication parameter is not
specified, DDCS will use a default authentication of SERVER.
This default applies to the system database directory entry on the
DDCS machine. In addition, this default applies to the system
database directory entry at the remote client in a multi-user gateway
DDCS environment.
8.4 Performance Troubleshooting
If DDCS for OS/2 Multi-User Gateway or DDCS for OS/2 Single-User users
are experiencing a long response time during large queries from DRDA
AS hosts (that is, DB2 for MVS, SQL/DS, or DRDA AS for DB2 for OS/2),
the following areas should be examined for the possible cause of the
performance problem:
1. For queries which result in returning large data blocks from the
DRDA host (usually 32K of data and above), ensure that the
database manager configuration parameter RQRIOBLK is set to 32767.
This can be done using the Command Line Processor (CLP) as
follows:
db2 update database manager configuration using RQRIOBLK 32767
2. If VTAM is used in the connection to DRDA AS, look under "switched
major node" configuration for the value of the PACING parameter.
On the DDCS workstation, examine the communication setup of the
"LU 6.2 Mode Profile" for IBMRDB mode definition. In this
definition, ensure the value for the "Receive pacing window"
parameter is less than or equal to the PACING value defined on
VTAM. A common value for "Receive pacing window" on the DDCS
workstation and "PACING" on VTAM is 8.
3. Ensure the maximum RU size defined in the IBMRDB mode definition
is not less than 4K.
4. Consult with the VTAM administrator in your environment to ensure
that VTAM is using "adaptive pacing" in LU-LU sessions with your
DDCS workstation.
8.5 DDCS 2.3 Gateway Connection to DB2/MVS
This section documents important additional information about setting
up a working DDCS Gateway connection to DB2/MVS:
o VTAM definitions required at your MVS host.
o DB2/MVS configuration steps.
This information is not presently provided in "DDCS Installation and
Configuration" manuals. The sample presented here illustrates a DDCS
for OS/2 scenario, but it can readily be used as a model to help you
connect other variants of DDCS to DB2 for MVS.
For example, if you are knowledgeable about IBM SNA Server for AIX,
and you follow the instructions for setting up SNA Server profiles
provided in "DDCS for AIX Installation and Customization Guide", the
documentation in this section will help you complete a working
connection between DDCS for AIX and DB2/MVS.
You must use your own values for elements such as network name, LU
name, etc., and we recommend that you use the planning worksheets
provided in Appendix A of the "DDCS Installation and Configuration
Guide" for your system.
For further information about DRDA Connectivity, we recommend that you
refer to "DRDA Connectivity Guide", SC26-4783.
Also, if you will use the LU6.2 Syncpoint Manager (SPM) function of
the DDCS Multi-User Gateway programs for OS/2 or AIX, it is
particularly important to refer to the SPM appendix in your "DDCS
Installation and Configuration Guide". The examples documented in
this section do not include detailed steps required for SPM.
8.5.1 Summary of Steps
In order to setup the connection, you must complete the following
steps:
1. At your DDCS Gateway:
a. Configure APPC.
b. Configure Database Manager - see 8.5.5, "Cataloging DB2
Directory Entries at the Workstation."
These steps are more completely documented in "Chapter 3:
Configuring Your Network" of the "DDCS Installation and
Configuration Guide" for your system.
2. At your DB2/MVS host:
a. Configure VTAM - see 8.5.6, "Configuring VTAM"
b. Configure DB2/MVS - see 8.5.8, "Configuring DB2/MVS."
8.5.2 Products used in this example
The following products were used:
o IBM PS/2 with Token-ring adapter
o DB2 for OS/2 2.1
o DDCS for OS/2 2.3
o DB2/MVS 2.3
o OS/2 Warp 3.0
o IBM LAN Server 4.0
o IBM Communications Manager/2 Version 1.11 (check your "DDCS
Installation and Configuration Guide" for the right level if you
will work with SPM).
8.5.3 Network Element Names
This example uses the following names:
DDCS Gateway:
- Network ID : CAIBMOML
- Local Node Name : OMXRSA (PU name)
- Local Node ID : 05D 30376
- LU Name : OMXRSA0A
- LU Alias : OMXRSA0A
HOST:
- Network ID : CAIBMOML
- Node Name : HOSTDB
- LU Name : SFLU
- LU Alias : SFLU
- LAN Destination Address : 400011528905 (Tic Address in NCP-NTRI)
MODE DEFINITION:
- Mode Name : IBMRDB
DB2/MVS:
- Location : SAN_FRANCISCO
SECURITY:
- Security Type : Program
- Authentication Type : DCS
NOTE: In this scenario, both USERID and password were only checked on
the Host. If you use Authentication SERVER, which is the default,
then authentication will also take place at the Gateway.
8.5.4 Configuring SNA Communications
Regardless of which variant of DDCS you are working with, at this
point you should refer to "Chapter 3 Configuring your Network" and
"Appendix A Worksheets", in your "DDCS Installation and Configuration
Guide".
When you have completed SNA communications configuration tasks,
proceed to 8.5.5, "Cataloging DB2 Directory Entries at the
Workstation."
The following section documents the steps required to configure
Communications Manager/2 Version 1.11, in order for the DDCS Gateway
machine to communicate to the DB2 DRDA Application Server (AS). This
example does not support SPM.
8.5.4.1 Creating a Communications Manager Configuration
1. From the OS/2 Desktop double click on the CM/2 icon.
2. From the CM/2 - Icon View, double click on the CM Setup.
3. When you see the IBM CM/2 INSTALLATION & SETUP screen click on OK.
4. When the CM Setup panel appear click on Setup.
5. Type in the name of the configuration and its description then
select OK.
6. From the Communications Manager Configuration Definition panel:
a. Click on Commonly used definitions.
b. Select APPC APIs over Token-ring.
c. Click on Configure.
7. From the APPC APIs over Token-ring panel, enter the following:
a. Network ID : CAIBMOML
b. Local Node name: OMXRSA
8. If you want an End Node to Network Node connection from your DB2/2
clients to this particular gateway, select Local Node Type as
Network Node. Otherwise select End Node - no network node server.
The sample configuration uses Network Node.
9. Click on Advanced to continue.
8.5.4.2 DLC Profile
1. From the Communications Manager Profile List panel, select 'DLC -
Token-ring or other LAN types', then select Configure.
2. Verify the information (see "DDCS Installation and
Configuration"), and then click on OK.
8.5.4.3 SNA Local Node Characteristics
1. From the Communications Manager Profile List Sheet panel, select
'SNA local node characteristics', then select Configure.
2. Verify the appropriate information and define Local node ID
X'05D30376', then click on OK.
NOTE: The Local Node ID has to match the IDBLK/IDNUM specification
for the DDCS workstation, as defined for the VTAM PU.
8.5.4.4 SNA Connections
1. From the Communications Manager Profile List Sheet panel select
'SNA connections', then select Configure.
2. Select 'To host' for Partner type.
NOTE: If you wish to connect to multiple DB2s that are in
different subarea networks, select the first host as To Host.
Select the other host(s) as 'To peer node'. The VTAM SSCP name of
this host must be specified as the Partner node name in the
'Creating a Connection to a Peer Node' menu.
3. To create a new definition, click on Create. To change an
existing definition, select a link from the Link Name list and
click on Change. The Adapter List appears.
4. Select 'Token-ring or other LAN types' from the Adapter List. The
'Configured' field indicates whether the adapter is configured,
and whether the selections available in the 'Adapter number' field
change to match the adapter type you selected. Please make sure
that the appropriate information is selected, then click on
Continue.
5. From the 'Create a Connection to a Host' panel, enter the
following:
o Link name: HOST0001 (or name of your choice, such as LINK0001)
o LAN destination address: 400011528905
o Partner network ID: CAIBMOML
o Partner node name: HOSTDB.
6. Click on Define Partner LUs, then enter the following from the
Partner LUs panel:
o Network ID: CAIBMOML
o LU Name: SFLU
o Alias: SFLU.
7. Click on Add. After the Add operation has completed successfully,
you will now see the box on the right populated with the fully
qualified LU name and the alias.
8. When you have finished, continue selecting OK until you reach the
'Connections List' panel.
9. Select Close.
8.5.4.5 SNA Features
1. From the Communications Manager Profile List Sheet panel select
'SNA features', then select 'Configure'.
2. Select 'Local LUs', then click on 'Create'.
3. From the 'Create a Local LU' panel, enter the following:
a. LU name: OMXRSA0A
b. Alias: OMXRSA0A.
4. Select Independent LU for NAU address, check off the box that says
"Use this local LU as your default local LU alias", then click on
OK.
NOTE: When you are using DDCS/2 version 2.3, the default local LU
name is normally used. Please refer to "DDCS for OS/2
Installation Configuration Guide" regarding optional use of the
APPCLLU environment variable.
5. Select 'Modes', then click on Create.
6. From the 'Mode Definition' panel, enter mode name IBMRDB.
Verify additional parameters, especially Mode-session-limit, and
Minimum contention winners. Refer to your "DDCS Installation and
Configuration Guide".
7. Click on OK.
8. Select 'CPI Communications side info', then click on Create.
9. From the 'CPI Communications Side Information' panel, enter the
following:
a. Symbolic destination name: SFLUNODE. The Symbolic Destination
name can be any name, but it must match the
symbolic-destination-name of your CATALOG APPC NODE. Note
that this parameter is case sensitive when used with the DB2
CATALOG NODE command.
b. Partner LU's Alias: SFLU, or enter fully qualified name:
CAIBMOML.SFLU.
c. Check off the box that says "Service TP".
d. TP name: X'07'6DB
e. Select 'Program' for Security type. Note that what is
specified here does not have to be the same as what is
specified in the Node catalog. Whatever is specified in the
Node Directory will override this value.
f. Set mode name to IBMRDB, then click on Continue.
g. From the 'CPI Communications Program Security' panel, enter
the USERID and Password that will be used, then click on OK.
8.5.4.6 Completing the CM Configuration
1. After completing the above steps, select Close twice. CM/2 will
automatically verify your configuration.
2. If your CM/2 is already started then you will see the pop-up
prompt "Would you like to dynamically update your SNA resources?".
Select No. Close CM Setup, then stop and restart Communications
Manager.
3. If CM/2 is not started, you will see the pop-up screen that says:
"Communications Manager installation is complete. You can start
Communication Manager immediately."
4. Click on OK.
5. Close the CM Setup screen then restart Communications Manager.
NOTE: If you will use SPM, additional parameters are required. You
must manually edit the NDF file, as described in the SPM appendix to
"DDCS for OS/2 Installation Configuration Guide".
8.5.4.7 Sample NDF File created by the above procedure
Sample NDF file:
DEFINE_LOCAL_CP FQ_CP_NAME(CAIBMOML.OMXRSA )
CP_ALIAS(OMXRSA )
NAU_ADDRESS(INDEPENDENT_LU)
NODE_TYPE(NN)
NODE_ID(X'05D30376')
NW_FP_SUPPORT(NONE)
HOST_FP_SUPPORT(YES)
HOST_FP_LINK_NAME(HOST0001)
MAX_COMP_LEVEL(NONE)
MAX_COMP_TOKENS(0);
DEFINE_LOGICAL_LINK LINK_NAME(HOST0001)
FQ_ADJACENT_CP_NAME(CAIBMOML.HOSTDB )
ADJACENT_NODE_TYPE(LEN)
DLC_NAME(IBMTRNET)
ADAPTER_NUMBER(0)
DESTINATION_ADDRESS(X'400011528905')
ETHERNET_FORMAT(NO)
CP_CP_SESSION_SUPPORT(NO)
SOLICIT_SSCP_SESSION(YES)
NODE_ID(X'05D30376')
ACTIVATE_AT_STARTUP(YES)
USE_PUNAME_AS_CPNAME(NO)
LIMITED_RESOURCE(USE_ADAPTER_DEFINITION)
LINK_STATION_ROLE(USE_ADAPTER_DEFINITION)
MAX_ACTIVATION_ATTEMPTS(USE_ADAPTER_DEFINITION)
EFFECTIVE_CAPACITY(USE_ADAPTER_DEFINITION)
COST_PER_CONNECT_TIME(USE_ADAPTER_DEFINITION)
COST_PER_BYTE(USE_ADAPTER_DEFINITION)
SECURITY(USE_ADAPTER_DEFINITION)
PROPAGATION_DELAY(USE_ADAPTER_DEFINITION)
USER_DEFINED_1(USE_ADAPTER_DEFINITION)
USER_DEFINED_2(USE_ADAPTER_DEFINITION)
USER_DEFINED_3(USE_ADAPTER_DEFINITION);
DEFINE_LOCAL_LU LU_NAME(OMXRSA0A)
LU_ALIAS(OMXRSA0A)
NAU_ADDRESS(INDEPENDENT_LU);
DEFINE_PARTNER_LU FQ_PARTNER_LU_NAME(CAIBMOML.SFLU )
PARTNER_LU_ALIAS(SFLU)
PARTNER_LU_UNINTERPRETED_NAME(SFLU )
MAX_MC_LL_SEND_SIZE(32767)
CONV_SECURITY_VERIFICATION(NO)
PARALLEL_SESSION_SUPPORT(YES);
DEFINE_PARTNER_LU_LOCATION FQ_PARTNER_LU_NAME(CAIBMOML.SFLU )
WILDCARD_ENTRY(NO)
FQ_OWNING_CP_NAME(CAIBMOML.HOSTDB )
LOCAL_NODE_NN_SERVER(YES);
DEFINE_MODE MODE_NAME(IBMRDB )
COS_NAME(#CONNECT)
DEFAULT_RU_SIZE(YES)
RECEIVE_PACING_WINDOW(4)
MAX_NEGOTIABLE_SESSION_LIMIT(32767)
PLU_MODE_SESSION_LIMIT(8)
MIN_CONWINNERS_SOURCE(2)
COMPRESSION_NEED(PROHIBITED)
PLU_SLU_COMPRESSION(NONE)
SLU_PLU_COMPRESSION(NONE);
DEFINE_DEFAULTS IMPLICIT_INBOUND_PLU_SUPPORT(YES)
DEFAULT_MODE_NAME(BLANK)
MAX_MC_LL_SEND_SIZE(32767)
DIRECTORY_FOR_INBOUND_ATTACHES(*)
DEFAULT_TP_OPERATION(NONQUEUED_AM_STARTED)
DEFAULT_TP_PROGRAM_TYPE(BACKGROUND)
DEFAULT_TP_CONV_SECURITY_RQD(NO)
MAX_HELD_ALERTS(10);
DEFINE_CPIC_SIDE_INFO SYMBOLIC_DESTINATION_NAME(SFLUNODE)
PARTNER_LU_ALIAS(SFLU )
MODE_NAME(IBMRDB)
SNA_SERVICE_TP_NAME(X'07'6DB);
START_ATTACH_MANAGER;
8.5.5 Cataloging DB2 Directory Entries at the Workstation
When you have configured your SNA subsystem, before you can to use the
DDCS connection, you may also need to catalog the database, the
workstation (node), and the DCS database at the DDCS workstation.
8.5.5.1 Cataloging the Workstation
1. From an OS/2 prompt, issue the following command:
DB2 CATALOG APPC NODE HOSTMVS REMOTE SFLUNODE SECURITY PROGRAM
The node name (HOSTMVS) can be any name, but it must match the
entry in the system database directory.
2. If you have not previously logged on, you will be prompted for
your USERID and Password.
NOTES:
a. The Node Name must be unique in the Node Directory
b. The Node Name in the System Database Directory maps to the
Node in the Node Directory.
You can verify the Node directory information using the following
command:
DB2 LIST NODE DIRECTORY
The output will look like this:
Node Directory
Number of entries in the directory = 1
Node 1 entry:
Node name = HOSTMVS
Comment =
Protocol = APPC
Symbolic destination name = SFLUNODE
Security type = PROGRAM
8.5.5.2 Cataloging the Database
1. Use the following command:
DB2 CATALOG DATABASE SFLUDB AS HOSTDB AT NODE HOSTMVS
AUTHENTICATION DCS
NOTES:
a. The Database name in the System Database Directory maps to the
Local Database Name in the DCS Directory.
b. The Node Name in the System Database Directory maps to the
Node Name in the Node Directory.
c. The Alias is the name used with the CONNECT statement.
You can verify the Database directory information using the following
command:
DB2 LIST DATABASE DIRECTORY
The output will look like this:
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = HOSTDB
Database name = SFLUDB
Node name = HOSTMVS
Database release level = 6.00
Comment =
Directory entry type = Remote
Authentication = DCS
8.5.5.3 Cataloging the DCS Database
1. Use the following DCS Database Catalog command:
DB2 CATALOG DCS DATABASE SFLUDB AS SAN_FRANCISCO
NOTES:
a. The local database name must be unique in the DCS directory.
b. The database name in the System Database Directory maps to the
Local database name in the DCS Directory.
c. The target database name in the DCS directory maps to the DRDA
AS location name (RDB_NAME).
8.5.5.4 Verify DCS Directory Information
You can verify your DCS directory information using the following
command:
DB2 LIST DCS DIRECTORY
The output will look like this:
Database Connection Services (DCS) Directory
Number of entries in the directory = 1
DCS 1 entry:
Local database name = SFLUDB
Target database name = SAN_FRANCISCO
Application requestor name =
DCS parameters =
Comment =
DCS directory release level = 0x0100
8.5.6 Configuring VTAM
To configure the VTAM host, you must prepare the necessary
definitions:
1. The APPL name for the DB2 subsystem (SFLU in these examples).
2. PU and LU definitions for the DDCS workstation (OMXRSA and
OMXRSA0A respectively in these examples).
3. The log mode entry to be used for the connection (IBMRDB in these
examples).
Work with your VATM Administrator to determine the options to be used
on your system. The following VTAM definitions correlate to
parameters used elsewhere in this sample scenario.
8.5.7 VTAM DB2 APPL Definition
Sample VTAM definition for the DB2 Application:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
DB2APPLS VBUILD TYPE=APPL
SFLU APPL APPC=YES, X
AUTH=(ACQ), X
AUTOSES=1, X
DLOGMOD=IBMRDB, X
DMINWNL=512, X
DMINWNR=512, X
DSESSLIM=2048, X
EAS=6000, X
MODETAB=RDBMODES, X
PARSESS=YES, X
PRTCT=SFLU, X
MODETAB=RDBMODES, X
SECACPT=ALREADYV, X
SRBEXIT=YES, X
VERIFY=NONE, X
VPACING=8
NOTES:
1. Continuations must begin in column 16.
2. If you will use SPM, you must also include:
SYNCLVL=SYNCPT
8.5.7.1 VTAM DB2 PU and LU definitions
Sample Switched Major Node Definition:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
SWITCHED MAJOR NODE DEFINITION FOR PU OMXRSA and
INDEPENDENT LU OMXRSA0A
LOC300 VBUILD TYPE=LOCAL
OMXRSA ADDR=01, IDBLK=05D, IDNUM=30376, ANS=CONT, DISCNT=NO, X
IRETRY=YES, ISTATUS=ACTIVE, MAXDATA=4302,MAXOUT=7, X
MAXPATH=1,PUTYPE=2,SECNET=NO,MODETAB=RDBMODES X
SSCPFM=USSSCS,PACING=0,VPACING=2
OMXRSA0A LOCADDR=000, MODETAB=RDBMODES, DLOGMODE=IBMRDB
OTHERLU LOCADDR=002
8.5.7.2 Sample Log Mode Definition
Sample Log Mode:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7---
RDBMODES MODTAB
IBMRDB MODEENT LOGMODE=IBMRDB, DRDA DEFAULT MODE *
TYPE=0, NEGOTIABLE BIND *
PSNDPAC=X'01', PRIMARY SEND PACING COUNT *
SSNDPAC=X'01', SECONDARY SEND PACING COUNT *
SRCVPAC=X'00', SECONDARY RECEIVE PACING COUNT *
RUSIZES=X'8989', RUSIZES IN-4K OUT-4K *
FMPROF=X'13', LU6.2 FM PROFILE *
TSPROF=X'07', LU6.2 TS PROFILE *
PRIPROT=X'B0', LU6.2 PRIMARY PROTOCOLS *
SECPROT=X'B0', LU6.2 SECONDARY PROTOCOLS *
COMPROT=X'D0B1', LU6.2 COMMON PROTOCOLS *
PSERVIC=X'060200000000000000122F00' LU6.2 LU TYPE
SNASVCMG MODEENT LOGMODE=SNASVCMG, DRDA DEFAULT MODE *
PSNDPAC=X'00', PRIMARY SEND PACING COUNT *
SSNDPAC=X'02', SECONDARY SEND PACING COUNT *
SRCVPAC=X'00', SECONDARY RECEIVE PACING COUNT *
RUSIZES=X'8585', RUSIZES IN-1K OUT-1K *
FMPROF=X'13', LU6.2 FM PROFILE *
TSPROF=X'07', LU6.2 TS PROFILE *
PRIPROT=X'B0', LU6.2 PRIMARY PROTOCOLS *
SECPROT=X'B0', LU6.2 SECONDARY PROTOCOLS *
COMPROT=X'D0B1', LU6.2 COMMON PROTOCOLS *
PSERVIC=X'060200000000000000000300' LU6.2 LU TYPE
NOTE: You must define SNASVCMG when using APPC.
8.5.8 Configuring DB2/MVS
Before you can use the DDCS connection, you need to configure DB2/MVS
to allow connections remote requests. The example below suggests a
simple configuration. Work with your DB2 Administrator to determine
the options to be used on your DB2/MVS system.
8.5.8.1 Updating the SYSIBM.SYSUSERNAMES Table
COLUMN EXAMPLE REMARK
====== ======= ======
Type I
Authid (blank)
LU Name OMXRSA0A Must match the DDCS/2 g/w's LU name
NewAuthID (blank)
Password (blank)
USERNAMES types are: O (outbound translation), I (inbound translation),
B (both inbound and outbound) and blank (no authorization ids are
translated, and no password is sent to the server).
You can use a DB2/MVS command such as the following to update this
table:
INSERT INTO SYSIBM.SYSUSERNAMES VALUES('I','JASI','OMXRSA0A',' ',' ');
8.5.8.2 Update the SYSIBM.SYSLUNAMES Table
COLUMN EXAMPLE REMARK
====== ======= ======
LUNAME OMXRSA0A Must match the DDCS Gateway LU name
SYSMODENAME IBMRDB
USERSECURITY A
ENCRYPTPSWDS N
MODESELECT N
USERNAMES I
You can use a DB2/MVS command such as the following to update this
table:
INSERT INTO SYSIBM.SYSLUNAMES VALUES('OMXRSA0A',' ','A','N',' ','I');
8.6 Connectivity Via DDCS (Thailand Users Only)
Please note that currently only connectivity between DB2 for OS/2 and
DB2 for AIX is supported.
Connectivity between the following is not supported due to the Thai
CCSID, 838, not being supported on the host:
DB2 for OS/2 and DB2 for MVS via DDCS
DB2 for OS/2 and DB2 for OS/400 via DDCS
DB2 for OS/2 and DB2 for VM and VSE via DDCS
DB2 for AIX and DB2 for MVS via DDCS
DB2 for AIX and DB2 for OS/400 via DDCS
DB2 for AIX and DB2 for VM and VSE via DDCS
8.7 DDCS User's Guide BIND Information
On Page 20 of "DDCS User's Guide", S20H-4793-00, Figure 4 "Bind Files
and Packages" contains information that has been superseded for DDCS
Version 2.3.1. The respective "xxx" values for DB2 Version 2.1.1 and
DDCS Version 2.3.1 are as follows:
5W0 DB2 Client Application Enabler for Windows Version 2.1.1
5D0 DB2 Client Application Enabler for OS/2 Version 2.1.1
5C0 DB2 Client Application Enabler for AIX Version 2.1.1, also
for HP-UX and Solaris.
For the complete updated text of this table please refer to the
appropriate "Installing and Using Clients" manual for your system.
8.8 OS/2 Coexistence Considerations
With reference to Page 72 of S20H-4795-01 "DDCS for OS/2 Installation
and Configuration Guide", and the two paragraphs under this heading,
there are no additional considerations which apply to OS/2.
9. Communications Issues
9.1 TCP/IP
If an application attempting a CONNECT to the server using the TCP/IP
protocol receives a -30081 error message with the first error token
containing a value of ETIMEDOUT (10060, 78, 145, 238) or EADDRINUSE
(10048, 67, 125, 226), or ECONNREFUSED (10061, 79, 146, 239); these
errors indicate that the server is handling too many incoming
connection requests or that the network is very busy. The application
should pause for a short time then attempt the connection again.
Also, in the case where ECONNREFUSED is received, check that TCP/IP
support at the database server has been successfully started.
10. Sample Tools and Utilities
A number of sample tools and utilities have been provided with this
product for your convenience. These samples are provided "as-is"
without any warranty of any kind. However, if you encounter an error,
let us know.
The sample tools and utilities can be found in the X:\sqllib\misc
directory, where X: is the drive where the product was installed. The
db2cli utility can be found in the sqllib\samples\cli directory.
The following is a list of the sample tools and utilities provided:
Name Description
---- -----------
db2batch DB2 Dynamic SQL Statement Processor
(Described in the Administration Guide)
db2bfd DB2 Bind file description tool
(Described in the Application Programming Guide)
db2cli DB2 Interactive CLI (executable) for design and prototyping in CLI
(Described in INTCLI.DOC in sqllib\samples\cli)
db2evmon Formats event monitor file and named pipe output,
and writes it to standard out.
(Described in the Performance Monitor Guide)
db2exfmt Formats the contents of the Explain tables.
Given a database name and other qualifying information,
this tool will, query the Explain tables for information
and format the results.
(Located in sqllib/misc)
Information about this utility is available by typing
"db2exfmt -help" on a command line.
db2expln DB2 SQL Explain Tool
(Described in the Administration Guide)
db2ico Run this REXX program from sqllib\misc to recreate
the IBM DATABASE 2 folder and icons. No parameters
are necessary if DB2 for OS/2 is installed.
db2ipxad Returns the server's IPX/SPX** internetwork address for use by the
user on a remote client machine who is cataloging an IPX/SPX node
using direct addressing mode.
(Mentioned in the Installing and Using OS/2 Clients and
Installing and Using DB2 Clients for Windows manual)
db2look DB2 statistics extraction tool
(Described in the Administration Guide)
db2resdb An executable to support the restoring of back-level database
backups taken from DB2 for OS/2 Version 1.x as well as Extended Services 1.0.
The syntax is as follows:
db2resdb <dbname> <source drive> <target drive>
db2tbst Takes a hexadecimal state value (as given from the
"list tablespaces" command) and interprets the state.
When you execute a DB2 command that leaves a tablespace in
anything other than the normal state, using that
tablespace may not be possible, but the cause of the
problem is not always clear. The "list tablespaces"
command indicates the state in the form of a hexadecimal
string which must be interpreted based on a combination
of state values found in sqlutil.h. Rather than perform
this interpretation manually, the db2tbst applet may be
used. For example, if you enter db2tbst 0x000c,
the following output is generated (in English only):
State = Quiesced Exclusive
+ Load Pending
db2untag Displays a container tag's information so that the sysadm can
check the "owning" database.
(Described in this README file in the section entitled
Dealing with Container in Use Error Message -294)
dynexpln Example of how to explain dynamic SQL using db2expln
(Described in the Administration Guide)
explain.ddl Sample DDL to create Explain tables
(Described in the Administration Guide)
isql Creates dummy tables to allow access from DB2 for VM using ISQL.
(Described in the Installation and Operation Guide)
lstaltsm Sample DDL to retrieve alerts for Snapshot Monitor
(Located in sqllib/samples/mon)
(Described in the Database Director Getting Started)
sample.pv Sample performance variable profile for Snapshot Monitor
(Located in sqllib/samples/mon)
(Described in the Database Director Getting Started)
sqldbsu Creates dummy tables to allow access from DB2 for VM using SQLDBSU.
(Described in the Installation and Operation Guide)
storproc.ddl Sample DDL to create stored procedures
pseudo_catalog DB2CLI.PROCEDURES
(Described in the Call Level Interface Guide and Reference)
storproc.dml Sample DML to insert entries into stored procedure
pseudo_catalog DB2CLI.PROCEDURES
(Described in the Call Level Interface Guide and Reference)
vesampl.ddl Sample DDL to export snapshots for Visual Explain
(Located in sqllib/samples/ve)
(Described in the Database Director Getting Started)
11. Limitations in Documented Functions
This section identifies limitations that exist in the current release.
11.1 Transaction Manager Database Considerations
When Distributed Unit of Work (DUOW) is used, DB2 for OS/2 and DDCS
for OS/2 need to have a Transaction Manager (TM) database created.
While this database is very much like any other DB2 database, there
are certain restrictions and considerations that should be taken into
account:
o This is a system database for use by DB2. It is recommended that
it not be used for storing any other data.
o The name of this database should not be made known to the general
user population.
o This database should not be accessed by DRDA client systems.
o This database should not be catalogued in the DCS directory.
o When the DDCS for OS/2 product is installed without DB2 for OS/2,
DDCS License Terms prohibit the use of this database for storing
user data.
11.2 Command and API Limitations
The following limitation exists for version 2.1.1:
o The precompiler OPTLEVEL option indicates whether the precompiler
is to optimize initialization of internal SQLDAs when host
variables are used in SQL statements. The use of OPTLEVEL 1 to
turn on SQLDA optimization has been disabled due to potential
problems it may cause. This is done at the runtime level, so that
existing applications precompiled with OPTLEVEL 1 do not have to
be reprecompiled. However, no SQLDA optimization will take place.
This is OPTLEVEL 0 behaviour.
11.3 WCHARTYPE CONVERT Precompile Option Limitation
In the Application Programming Guide, in Chapter 10, "Programming in C
and C++", under the heading "The WCHARTYPE Precompiler Option", the
WCHARTYPE CONVERT precompile option is described.
The WCHARTYPE CONVERT precompile option is not currently supported in
programs executing from the DB2 V2.1 Windows client. In these cases
the default (WCHARTYPE NOCONVERT) should be used.
When using WCHARTYPE CONVERT on DB2 for OS/2 with the C/Set++
compiler, the /Sn compile option must be used to enable the compiler
to support L-literals.
11.4 Using DB2 with an XA-Compliant Transaction Manager
In the Administration Guide, in Appendix D, "X/Open Distributed
Transaction Processing Model", in the section entitled "Application
Program (AP)", under the item "Global Transactions", loosely coupled
global transactions and tightly coupled global transactions are
described.
The X/OPEN xa.h file has been modified to support the _System linkage
convention on OS/2 and is provided under the \sqllib\include
directory. The modified version should be used for DB2 XA-interface.
11.5 Database Director Limitations
The Database Director cannot be used to manage DRDA servers.
The following 4 configuration parameters are not supported by the
Database Director:
adsm_password
adsm_nodename
adsm_owner
dft_queryopt
11.5.1 Restriction for Visual Explain
The following restrictions apply to Visual Explain:
o Connectivity between AIX clients and OS/2 servers and OS/2 clients
and AIX servers is not supported. You will not be able to invoke
the access plan graph.
o The client used to display an access plan graph must use the same
code page as the database to which it is connected.
o If you have created a snapshot in one code page, and are viewing
the Access Plan graph for that snapshot in another code page, you
will see unreadable characters for the names of database objects
such as tables, columns, indices.
o You can only display the Access Plan graph overview window once
for every Access plan graph. The overview menu item is grayed out
and the overview icon is disabled once the overview window is
opened the first time.
11.5.1.1 Snapshot Monitor
o There is no limit to the number of performance variables that can
be displayed on performance graphs on AIX and OS/2. However, the
AIX graph can differentiate between 63 performance variables
before it starts reusing the same combinations of line colors and
point markers. The limit for the OS/2 graph is 56 performance
variables. However, if you plan to change either the scale or
threshold of a displayed performance variable on a graph on either
platform, limit the number of displayed performance variables to
20. This limit becomes evident when using the "Change" menu
action from the "Performance variable" pull down.
o If you monitor in a code page that differs from the code page of
the database, no performance data will be returned for monitored
tables and table spaces, unless their names have the same
representation in both code pages.
If you are using a DBCS version of the DB2 server, you will not be
able to put DBCS labels and performance variable names (including the
name, group name and description) in the variable profile of the
Performance Monitor.
Also, the IBM-supplied labels on the Performance Graph View will be
displayed in English.
The 2 labels are:
x axis ... "Local time"
y axis ... "Percent of SCALE"
11.5.1.2 Event Analyzer
When analyzing a trace and connecting to a remote AIX database, to
obtain static text using the -conn and -db options, you need to set
the database manager authentication parameter to 'client'.
If you have created an event monitor trace in one code page and
analyze it in a different code page, you may see unreadable characters
for the names of database objects such as packages, tables, and table
spaces.
11.6 Online Backup and Load with COPY NO option
At the end of a LOAD with the COPY NO option, the tablespace(s) are
put into Backup Pending state. Before backing up the loaded
tablespaces, all connections to the database must be terminated. This
can be performed by issuing FORCE APPLICATION ALL and/or DEACTIVATE
DATABASE.
If an online backup is in progress, do not start a LOAD with the COPY
NO option.
12. Using the DB2 ODBC Driver with ODBC Applications
Please pay particular attention to the chapters titled 'Setting up
Communications for the Client', and 'Using the Client' in the manual
called "Installing and Using DB2 Clients for OS/2 Version 2". There
will be no separate DB2ODBC.TXT file. All the information related to
ODBC usage is provided in the "Installing and Using DB2 Clients for
OS/2 Version 2" manual and the rest of this section.
12.1 Common Application Problems and Work Arounds
The Patch1 keyword (specified in the DB2CLI.INI file) is used to
specify work arounds for specific application problems. There are
currently no work arounds that apply to OS/2 applications.
NOTE: Most ODBC applications do not support LOB data types, and thus
require the LONGDATACOMPAT keyword to be set to 1.
There are currently two supported ODBC Driver Managers for ... OS/2:
o Visigenic's ODBC Driver Manager (shipped with all DB2 for OS/2 V
2.1.1 products).
o Intersolv's ODBC Driver Manager (contains its own DB2 ODBC ...
Driver).
12.1.1 Visigenic's ODBC Driver Manager
This Driver Manager is shipped with DB2 for OS2, and is installed or
updated when the ODBC Installer is ran from the DB2 folder
(DB2ODBC.EXE in -sqllib-bin). The steps for using the ODBC installer
and adding data sources is described in the "Installing and using DB2
Clients for OS/2".
Visigenic uses the ODBC_PATH environment variable to indicate where
the Driver Manager is installed.
Note, the Visigenic ODBC Driver Manager copies the DB2CLI.DLL to its
own directory, and adds this path to the system LIB path. If any DB2
service upgrades are applied, you must rerun the ODBC installer to
copy the new DB2CLI.DLL.
12.1.2 Intersolv's ODBC Driver Manager
In the OS/2 environment, if you wish to use IBM's DB2 ODBC Driver
instead of the DB2 ODBC driver provided in the Intersolv DataDirect
ODBC Driver Pack for OS/2, you need to run the "DB2ODBC" executable
(DB2ODBC.EXE in SQLLIB\BIN) from an OS/2 command line. Running this
executable registers IBM's DB2 ODBC Driver in the Intersolv ODBC
installation file.
If the DB2ODBC executable completed successfully, you will be returned
to the OS/2 command prompt. Otherwise, an error message will be
printed before returning you to the command prompt.
After running DB2ODBC.EXE successfully, you can now use the Intersolv
ODBC Administrator to add, configure DB2 data sources.
To configure your DB2 ODBC Driver for OS/2 with DB2 specific features,
you need to use the DB2CLI.INI file. Refer to the book titled
"Installing and Using DB2 Clients for OS/2 Version 2".
12.2 Limitations of CLI/ODBC Administrator and Client Setup GUI Tools
The CLI/ODBC Administrator and Client Setup GUI utilities do not
support the use of data source names that are different than the
catalogued database alias
You can manually edit the ODBC.INI and DB2CLI.INI files to specify a
data source name that is longer than the 8 character database alias
catalogued in the database directory. The DBALIAS keyword must then
be used within the data source section of the DB2CLI.INI file to map
the new data source name to the catalogued database alias. You must
edit the data source name (within square brackets) in the DB2CLI.INI
file to match the value in the ODBC.INI file.
If you change the data source names, the CLI/ODBC Administrator and
Client Setup utilities will not be able to recognize the cataloged
database as being a valid ODBC data source.
The following configuration keywords cannot yet be modified using the
CLI/ODBC Administrator:
o CURRENTFUNCTIONPATH
o MULTICONNECT
o OPTIMIZEFORNROWS
See either the "Installing and Using DB2 Clients for OS/2 Version 2"
manual or the "Call Level Interface" manual for more information on
these configuration keywords.
12.3 SQLDescribeParam()
With this release of DB2 v2.1.1, the DB2 CLI/ODBC function call
SQLDescribeParam() is now supported. This function returns the
description of a parameter marker associated with a prepared SQL
statement. You will receive an SQLSTATE of S1C00 - 'Driver not
capable' if you attempt to run this command against any DB2 common
server before this release of 2.1.1, or against a DRDA data source.
One output of the SQLDescribeParam() function indicates whether the
parameter allows NULL values. Currently the DB2 CLI/ODBC driver always
returns SQL_NULLABLE_UNKNOWN.
SQLGetFunctions() will not correctly indicate whether
SQLDescribeParam() is available. Until you run either SQLPrepare() or
SQLExecDirect(), SQLGetFunctions() will always return TRUE (always
indicating that SQLDescribeParam() is available). Once you run either
of these two functions, SQLGetFunctions() will correctly indicate
whether SQLDescribeParam() is supported, based on the version of your
DB2 server.
See the new db2cli.ini keyword 'DescribeParam' for details on enabling
or disabling this new CLI/ODBC function when connecting to DB2 v2.1.1
servers.
12.4 New CLI/ODBC Configuration Keywords in db2cli.ini
The following ODBC configuration keywords were not described in the
book:
CURRENTFUNCTIONPATH = CURRENT_FUNCTION_PATH
This keyword defines the path used to resolve function references
and data type references that are used in dynamic SQL statements.
It contains a list of one or more schema-names, where schema-names
are enclosed in double quotes and separated by commas.
The default value is "SYSIBM","SYSFUN",X where X is the value of
the USER special register delimited by double quotes. The schema
SYSIBM does not need to be specified. If it is not included in
the function path then it is implicitly assumed as the first
schema.
This keyword is used as part of the process for resolving
unqualified function references that may have been defined in a
schema name other than the current users' schema. The order of
the schema names determines the order in which the function names
will be resolved. For more information on function resolution,
refer to the SQL Reference guide.
MULTICONNECT = 0 | 1
This keyword is used to specify how "SQLConnect()" requests are
mapped to physical database connections.
0 = false
Each "SQLConnect()" request by the application will result in
a physical database connection.
1 = true
All connections for the application are mapped to one physical
connection.
This may be used if the ODBC application runs out of file
handles because it uses so many connections.
NOTE: If MULTICONNECT is set to true then all statements are
executed on the same connection and therefore in the same
transaction. This means that a rollback will rollback ALL
statements on all connections. Be sure that the application is
designed to work with MULTICONNECT set to true before doing so or
the application may not operate correctly.
OPTIMIZEFORNROWS = INTEGER
This keyword will append the "OPTIMIZE FOR n ROWS" clause to every
select statement, where n is an integer larger than 0. The
default action is not to append this clause.
For more information on the effect of the OPTIMIZE FOR n ROWS
clause, refer to the SQL Reference guide.
IGNOREWARNINGS = 0 | 1
0 = Warnings reported as usual (default).
1 = All warnings are ignored, SQL_SUCCESS is returned.
Use this keyword if an application does not handle warnings
properly.
12.4.1 CLI/ODBC Trace File
The DB2 CLI/ODBC driver can now write the details of all function
calls to a trace file which can be sent to IBM support to help resolve
problems. A programmer familiar with the workings of CLI/ODBC could
also look at this file to gain a better understanding of program
execution.
Three additional keywords in the db2cli.ini have been added to
facilitate this function. They are located in the [COMMON] section in
the initialization file:
o Trace = 1 (on) or 0 (off) - defaults to 0
o TraceFileName = <full file name>
If the file does not exist then it will be created, otherwise the
new trace information will be appended to the end of the file.
No trace will occur, and no error message will be returned, if the
filename given is invalid or if the file cannot be created or
written to.
o TraceFlush = 1 (on) or 0 (off) - defaults to 0
Set this keyword to 1 to force a write to disk after each Trace
entry. This will slow down the trace process, but will ensure
that each entry is written to disk before the program continues to
the next statement.
For example, the following settings in the db2cli.ini file will write
all details of DB2 CLI/ODBC function calls to a file called
C:\TRACE\MONDAY.TRC after each is executed.
[COMMON]
...
Trace=1
TraceFileName=C:\TRACE\MONDAY.TRC
TraceFlush=1
12.4.2 Specifying a Directory for Temporary Files
When working with Large Objects (CLOBS, BLOBS, etc...), a temporary
file is often created on the client machine to store the information.
It is possible that in some cases the system temporary directory
setting will not be used. The db2cli.ini keyword 'TempDir' has been
created to ensure that the DB2 CLI/ODBC driver manager selects an
appropriate path.
The keyword is placed in the datasource specific section of the
db2cli.ini file, and has the following syntax:
o TempDir=<full path name>
When a Large Object is accessed, an SQLSTATE of S1507 will be
returned if the path name is invalid, or if the temporary files
cannot be created in the directory specified.
12.4.3 SQLDescribeParam() Enable/Disable
The keyword 'DescribeParam' has been added to allow you to enable or
disable the new SQLDescribeParam() function. The keyword is placed in
the datasource specific section of the db2cli.ini file, and has the
following syntax:
o DescribeParam = 0 or 1 (defaults to 0)
0 = Disable SQLDescribeParam() - default
1 = Enable SQLDescribeParam()
This keyword has the following effect based on the version of the DB2
server you are connecting to:
o DB2 server before 2.1.1
Keyword has no effect
- SQLGetFunctions() will indicate that SQLDescribeParam() is not
supported
- if you call SQLDescribeParam() it will return SQLSTATE S1C00 -
'Driver not capable'
o DB2 server version 2.1.1, up to but not including this National Language
version.
Set DescribeParam = 0 to avoid errors if your application does
call SQLDescribeParam(). This will:
- disable the SQLDescribeParam() function (if you call
SQLDescribeParam() it will return SQLSTATE S1C00 - 'Driver not
capable')
- have SQLGetFunctions() indicate that SQLDescribeParam() is not
available
o This current National Language version of the DB2 server version 2.1.1
Setting DescribeParam to 1 will:
- enable the SQLDescribeParam() function
- have SQLGetFunctions() indicate that SQLDescribeParam() is
available
Setting DescribeParam to 0 will:
- disable the SQLDescribeParam() function (if you call
SQLDescribeParam() it will return SQLSTATE S1C00 - 'Driver not
capable')
- have SQLGetFunctions() indicate that SQLDescribeParam() is not
available
12.4.4 Asynchronous ODBC Enable/Disable
The keyword 'AsyncEnable' has been added to allow you to enable or
disable the new asynchronous features. The keyword is placed in the
datasource specific section of the db2cli.ini file, and has the
following syntax:
AsyncEnable = 0 or 1
o 0 = Disable the new asynchronous features. The DB2 CLI/ODBC driver
will act as it did with previous versions that did not support
asynchronous
o 1 = Enable the new asynchronous features as described above.
13. Miscellaneous
13.1 DB2 Family Newsletter
For the latest information about the DB2 family of products, you may
subscribe to the "DB2 Family Newsletter" (English only). Simply send
your request in a FAX to 1-905-316-4733, and include the following
information:
Name
Company name
Full mailing address
Telephone number
DB2 products you currently use
-------------------------------------------------------------------
* are trademarks or registered trademarks of IBM Corporation.
** are trademarks or registered trademarks of their respective owners.
-------------------------------------------------------------------
***********************************************************************
** **
** (c) COPYRIGHT INTERNATIONAL BUSINESS MACHINES CORPORATION 1996. **
** ALL RIGHTS RESERVED. **
** **
***********************************************************************