© Copyright International Business Machines Corporation 1998. All rights reserved.
Welcome to DB2 Universal Database Version 5.2!
3.0 Changes to the DB2 Documentation
4.0 HTML Search Server for OS/2: Searching HTML Documentation
5.0 HTML Search Server for Windows NT, Windows 95, and Windows 98: Searching HTML Documentation
| Note: | These Release Notes include DB2 information about issues discovered after the shipped README file was published. |
This README file contains information that was not available when the DB2 manuals were printed, as well as FixPak information about the following products:
IBM DB2 Universal Database Enterprise - Extended Edition for AIX, Version 5.2 IBM DB2 Universal Database Enterprise - Extended Edition for Solaris, Version 5.2 IBM DB2 Universal Database Enterprise - Extended Edition for Windows NT, Version 5.2 IBM DB2 Universal Database Enterprise Edition, Version 5.2 IBM DB2 Universal Database Workgroup Edition, Version 5.2 IBM DB2 Universal Database Personal Edition, Version 5.2 IBM DB2 Universal Developer's Edition, Version 5.2 IBM DB2 Personal Developer's Edition, Version 5.2
| Note: | VisualAge for Basic and Lotus Approach are no longer bundled in the product boxes. |
A separate Release Notes file, installed as READDCS.TXT, is provided for the following products:
IBM DB2 Connect Enterprise Edition, Version 5.2 IBM DB2 Connect Personal Edition, Version 5.2
The What's New book contains both an overview of some of the major DB2 enhancements for Version 5 and Version 5.2, and a detailed description of these new features and enhancements. New features that were available as part of DB2 Universal Database Version 5 and DB2 Connect Version 5 FixPaks are considered Version 5.2 enhancements.
Some portions of the DB2 Universal Database library have not been updated for Version 5.2. These include:
If a document has not been reissued for DB2 Universal Database Version 5.2, the details of any enhancements that affect it are included in the What's New book. You can use that book in conjunction with the Version 5 documentation for DB2 Universal Database Version 5.2.
DB2 UDB Version 5.2 can be purchased and installed separately,
or it can be installed as a FixPak. DB2 UDB Version 5.2 is
equivalent to DB2 UDB Version 5.0 with FixPak 6 applied. The PTF
numbers by operating system for FixPak 6 are listed in the following
table:
Table 1. PTF Numbers for DB2 UDB FixPak 6
| Operating System | PTF Number for DB2 UDB FixPak 6 |
|---|---|
| AIX | U458635 |
| HP-UX | U458637 |
| OS/2 | WR09053 |
| Solaris | U458636 |
| Windows 95, Windows 98, and Windows NT | WR09054 |
| Windows 3.1 and Windows for Workgroups 3.11 | WR09056 |
| Note: | Running the DB2 File Manager on AIX Version 4.2 requires a minimum of PTF U456886. |
The following PTF numbers, from SCO, are required for DB2 UDB Version 5.2 to run on SCO UnixWare 7:
ptf7003c ptf7010 ptf7013
The latest information about SCO PTFs required for DB2 UDB Version 5.2 can be found in the Product and Service Technical Library (see section 6.1).
The following patch levels are required for running the Sun Cluster software:
105786-05 - Required patch for Sun Cluster 2.x on Solaris 2.6. 2.1_105458-06 - Required Sun Cluster patch level for DB2.
In Version 5.2, most of the books that make up the product libraries have been updated. They are available online in HTML format (after the products are installed), and in printed format. See the What's New book for the complete list of updated books, instructions for printing them, and the form numbers that you can use to order them. You can use form number SBOF-8921-00 to order the complete set of the books that were updated for Version 5.2.
FixPak information that was previously included in files read1297.txt, read0398.txt, and read0698.txt has been included:
In addition, information that was not available when the books were printed is included in the Release Notes.
The Web Control Center is a Java version of the DB2 Universal Database Control Center (a database administration tool). Its design is modelled on the original DB2 Control Center, yet it provides a more flexible network-centric administration environment. The Web Control Center is implemented as a Java applet that uses DB2's JDBC support.
While the Web Control Center is not installed as part of this version of DB2, you can download it from the following URL:
http://www.software.ibm.com/data/db2/udb/webcc
The following SCOhelp information is available:
You can access SCOhelp in one of the following ways:
The "UnixWare 7 Update 1 (7.0.1)" CD-ROM provides, among other things, fixes for the Spanish Language Extensions (esle). The following DB2 procedures will not work when LANG is set to "es", unless the UnixWare 7 Update 1 is installed:
db:234:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services
If the default value of LANG is "es", this line should be commented out from the /etc/inittab file. Once the reboot has completed, log on as root, set LANG to "en" or "C", then run /etc/rc.db2. Set LANG back to "es" once DB2 has started.
Note that once UnixWare 7 Update 1 has been installed to fix problems for the Spanish Language Extensions, these problems will disappear. You should uncomment out the line in the etc/inittab file, so that DB2 services will autostart on the next reboot:
db:234:once:/etc/rc.db2 > /dev/console 2>&1 # Autostart DB2 Services
If you want to change the language SCOhelp returns by default on the server, edit /usr/ns-home/httpd-scohelphttp/config/lang.conf and set DocumentRoot to the desired default language. For instance:
DocumentRoot /usr/lib/scohelp/fr
Once this change is made, you must restart the web server, with the command:
/usr/ns-home/httpd-scohelphttp/restart
If this does not work, try stopping the web server, then restarting it, with the commands:
/usr/ns-home/httpd-scohelphttp/stop /usr/ns-home/httpd-scohelphttp/start
Different clients can have SCOhelp return information in different languages.
The SCOhelp server uses the *httpAcceptLanguage resource to determine which language to use when a request is received. You can set *httpAcceptLanguage to the required language as follows:
*httpAcceptLanguage: fr, en
If English is still displayed by SCOhelp, even after *httpAcceptLanguage is set, try removing English from the list altogether.
Note that there is no search in Simplified Chinese and Brazilian Portuguese.
You can select multiple languages to search by clicking on the "Options" button on the SCOhelp search panel. The list of installed languages is displayed; select the set of languages to search.
There is no direct link from the Web Control Center help to the DB2 books (displayed using SCOhelp). Use one of the methods described in 2.1 , How to Access SCOhelp to run SCOhelp and access the DB2 books.
There is no search in the SCO DB2 Web Control Center help.
The "Support Level Supplement (SLS) PTF7004A" is available from the SCO ftp site at: ftp://ftp.sco.com/SLS/ Download the files ptf7004a.Z and ptf7004a.txt
PTF7004A "corrects problems with the configuration of the Verity search engine within SCOhelp and with the localization of various utilities and SCOadmin managers." You are required to install this SLS if you will be viewing the DB2 documentation. See the ptf7004a.txt file for more details.
Once the PTF7004A is installed, and before you use SCOhelp in Simplified Chinese or Brazilian Portuguese, you must edit the file /usr/ns-home/httpd-scohelphttp/config/lang.conf and make the following changes:
LocalizedDocRoot pt_PT.ISO8859-1 /usr/lib/scohelp/pt_BR LocalizedDocRoot zh_ZH.ISO8859-1 /usr/lib/scohelp/zh_CN
LanguageAlias zh zh_ZH.ISO8859-1
This value then matches the line:
LocalizedDocRoot zh_ZH.ISO8859-1 /usr/lib/scohelp/zh_CN
Once these changes are made restart (or stop and start) the web server, as described in 2.2.2 , Changing Default SCOhelp Language on the Server.
SCOhelp stores all of its search index files in the /usr/lib/scohelp/<lang>/_SearchIndex/parts directory.
These files will continue to grow in size as you install products, but will not get smaller as you remove products (using the current UnixWare 7 utilities). This is the case for DB2 as well as other products.
If you delete these files, your SCOhelp search will no longer work properly.
If a query is running under the cursor stability (CS) isolation level, only summary tables and replicated tables that are defined under CS or higher isolation levels are used for optimization. In general, a summary table or a replicated table is used for optimization of a query if the isolation level of the summary table or the replicated table is higher than or equal to the isolation level of the query.
This section describes how to optimize performance when data is placed on Redundant Array of Independent Disks (RAID) devices. In general, you should do the following for each table space that uses a RAID device:
DB2_PARALLEL_IO
When reading data from, or writing data to table space containers, DB2 may use parallel I/O if the number of containers in the database is greater than 1. However, there are situations when it would be beneficial to have parallel I/O enabled for single container table spaces. For example, if the container is created on a single RAID device that is composed of more than one physical disk, you may want to issue parallel read and write calls.
To force parallel I/O for a table space that has a single container, you can use the DB2_PARALLEL_IO registry variable. This variable can be set to "*" (asterisk), meaning every table space, or it can be set to a list of table space IDs separated by commas. For example:
db2set DB2_PARALLEL_IO=* {turn parallel I/O on for all table spaces}
db2set DB2_PARALLEL_IO=1,2,4,8 {turn parallel I/O on for table spaces 1, 2, 4, and 8}
After setting the registry variable, DB2 must be stopped (db2stop), and then restarted (db2start), for the changes to take effect.
DB2_STRIPED_CONTAINERS
Currently when creating a DMS table space container (device or file), a one-page tag is stored at the beginning of the container. The remaining pages are available for data storage by DB2, and are grouped into extent-sized blocks.
When using RAID devices for table space containers, it is suggested that the table space be created with an extent size that is equal to, or a multiple of, the RAID stripe size. However, because of the one-page container tag, the extents will not line up with the RAID stripes, and it may be necessary during an I/O request to access more physical disks than would be optimal.
DMS table space containers can now be created in such a way that the tag exists in its own (full) extent. This avoids the problem described above, but it requires an extra extent of overhead within the container. To create containers in this fashion, you must set the DB2 registry variable DB2_STRIPED_CONTAINERS to "ON", and then stop and restart your instance:
db2set DB2_STRIPED_CONTAINERS=ON db2stop db2start
Any DMS container that is created (with CREATE TABLESPACE or ALTER TABLESPACE) will have new containers with tags taking up a full extent. Existing containers will remain unchanged.
To stop creating containers with this attribute, reset the variable, and then stop and restart your instance:
db2set DB2_STRIPED_CONTAINERS= db2stop db2start
The Control Center and the LIST TABLESPACE CONTAINERS command will not show whether a container has been created as striped or not. They will continue to use "file" or "device", depending on how the container was created. To verify that a container was created as striped, you can use the /DTSF option of DB2DART to dump table space and container information, and look at the type field for the container in question. Also, the query container APIs, sqlbftcq( ) and sqlbtcq( ), can be used to create a simple application that will display the type.
Definitions for these new types have been added to the sqlutil.h header file:
#define SQLB_CONT_STRIPED_DISK 5 /* DMS: Striped disk */ #define SQLB_CONT_STRIPED_FILE 6 /* DMS: Striped file */
There is no audit support for summary tables, typed tables, or replicated tables.
If you want to load or import an IXF format file containing DBCS data, you must have the corresponding conversion files (located in sqllib\conv) installed on your client machine. The names of these conversion files contain both the source and the target code page numbers; the extension is always .cnv. For example, file 09320943.cnv contains the conversion table for converting code page 932 to 943.
If your client machine does not have the appropriate conversion files, you can copy them from a server machine to the sqllib\conv directory of the client machine. Be sure to copy the files from a compatible platform; for example, if the client is running on a UNIX based operating system, copy the files from a server that is also running on a UNIX based operating system.
In DB2 UDB Version 5.2 for Windows NT and DB2 UDB Version 5.2 for OS/2, users operating in an SMP environment might occasionally experience a system hang during a load operation. This is a known problem that occurs only during index creation (the BUILD phase of the load utility) involving large volumes of data (on the order of several gigabytes). To avoid this problem, it is recommended that existing indexes on a loading table be dropped before issuing the LOAD command, and recreated using the CREATE INDEX statement after the load operation has completed successfully. An alternative that does not require the dropping of indexes is to perform the load operation into a staging table that does not have any indexes defined on it. The target table can subsequently be updated by issuing an INSERT statement encompassing all of the data from the staging table.
You cannot back up a database or table space on one operating system and restore it to another operating system by using the BACKUP DATABASE and RESTORE DATABASE commands. Instead, you move tables from one operating system to another by using the db2move command (or by exporting, then importing or loading the data). For more information, see "Moving Data Using the db2move Tool" in the Administration Guide.
The current ADSM client on the Windows operating system and OS/2 is non-reentrant, and so multiple sessions cannot be created with the backup, restore, or load utilities from a single machine.
In a single node configuration, if a user attempts to issue a backup command such as:
db2 backup db sample use adsm open 3 sessions
DB2 will detect that multiple sessions are not supported by ADSM, and will return SQL2032N. The equivalent scenario also applies to load copies using ADSM.
However, in an MLN configuration on Windows NT, DB2 may not be able to detect the use of multiple sessions on a single machine if each logical node attempts to create only one session. If multiple logical nodes are being backed up, restored, or loaded in parallel using ADSM, DB2 will allow the operation to proceed if each node attempts to use a single session, even though the logical nodes actually reside on the same physical hardware. This can lead to failed backup attempts, and hung load processes, and should not be attempted.
You cannot roll forward a partitioned database from a Version 2 client.
The last line in the "OS/2 Platform" section should be changed to:
With either method, you must link your application with db2api.lib.
The last line in the "Windows NT Platform" section should be changed to:
With any of these methods, ensure that you link with db2api.lib.
When working on your database design, you may have determined that tables with 8KB page sizes were best. As a result, you should create a buffer pool with an 8KB page size (along with one or more table spaces with the same page size).
If you have chosen to place your tables in an 8KB table space, it is recommended that your temporary table spaces have an 8KB page size as well, and they should be assigned to the same 8KB page size buffer pool. The default buffer pool (IBMDEFAULTBP) or any other 4KB buffer pool to which any 4KB temporary table spaces are assigned should be kept small, since the database manager will favor the 8KB temporary table spaces, and therefore the 8KB buffer pool.
To realize the performance benefits of hash join, it may be necessary to change the value of the SORTHEAP database configuration parameter, and the SHEAPTHRES database manager configuration parameter.
Hash join plans use more sort heap space than other join plans. When SHEAPTHRES is set to be very close to SORTHEAP (less than a factor of 2 or 3 per concurrent query), hash join runs with much less memory than the optimizer anticipated. When executing with limited memory, hash join can be very slow. The problem occurs in queries with multiple sorts and hash joins, in which the first sorts or hash joins acquire most of the available memory.
The solution is to configure SHEAPTHRES to be large enough (relative to SORTHEAP).
For more information about hash join, including how to determine whether a hash join strategy has been selected, see the description of db2expln in the SQL Reference, and the What's New book (Appendix A - System Monitor Guide and Reference Updates). See also the description of the new hash join registry variable (DB2_HASH_JOIN) below.
DB2 can fail while fetching query results from large tables (that is, tables with greater than a 4005-character record length and more than 500 columns). This can be overcome by increasing the value of the ASLHEAPSZ database manager configuration parameter from the default value of 15.
On Windows NT, on an SMP machine, the INTRA_PARALLEL database manager configuration parameter will be set to YES when an instance is created with DB2 Version 5.2. With Version 5.0, it would have been set to NO.
The DB2_GRP_LOOKUP registry value has a default of "null" and a value of "local":
Parameter Operating Values Description
System
-------------------------------------------------------------------------------------
DB2_GRP_LOOKUP Windows NT DEFAULT=null Specifies which Windows NT
Values: local security mechanism will be
used to enumerate the groups
that a user belongs to.
There is a new hash join registry variable:
Parameter Operating Values Description
System
------------------------------------------------------------------------------------
DB2_HASH_JOIN All DEFAULT=NO Specifies hash join as
Values: YES or NO a possible join method when
compiling an access plan.
The following is to be added to the rah information in Appendix Q.
To enhance performance, rah has been extended to use tree_logic on large systems. That is, rah will check how many nodes the list contains, and if that number exceeds a threshold value, it constructs a subset of the list and sends a recursive invocation of itself to those nodes. At those nodes, the recursively invoked rah follows the same logic until the list is small enough to follow the standard logic (now the "leaf-of-tree" logic) of sending the command to all nodes on the list. The threshold can be specified by environment variable RAHTREETHRESH, or defaults to 15.
In the case of a multiple-logical-node-per-physical-node system, db2_all will favor sending the recursive invocation to distinct physical nodes, which will then rsh to other logical nodes on the same physical node, thus also reducing inter-physical-node traffic. (This point applies only to db2_all, not rah, since rah always sends only to distinct physical nodes.)
This version of rah has nearly identical syntax and semantics as the old version (supplied in the product as rah.sh_old), except for some minor restrictions on what options can be used:
| Note: | The () and ## substitutions should work identically to the old rah/db2_all. |
export RAHTREETHRESH=nn
where nn can be any positive integer
or defaults to 15.
To specify the IDENTITY collating sequence when creating a database, specify SQL_CS_NONE (which implements a binary collating sequence) in the database description block (SQLEDBDESC) structure.
In the description of the pMediaTargetList parameter (value SQLU_ADSM_MEDIA), the sentence "No additional input is required." should be replaced with:
If an SQLU_MEDIA_ENTRY structure is not being used to specify a path for the backup image, initialize the MEDIA pointer in the SQLU_MEDIA_LIST_TARGETS structure to NULL.
The following section is missing from the API description:
Required Connection
Database
There is no callerac parameter available in the sqluhgne API with which to retrieve DDL information recorded in the history file. To retrieve this information, a new API has been created:
SQL_API_RC SQL_API_FN
sqluhget(
unsigned short Handle, // IN: Handle returned from sqluhops
unsigned short CallerAction, // IN: For special handling of command field
unsigned long Reserved_1, // Reserved for future use
struct sqluhinfo * pHistoryInfo, // IN/OUT: Pointer to entry data
struct sqluhadm * pAdminInfo, // IN/OUT: Pointer to enhanced adminstrative data
void * pReserved, // Reserved for future use
struct sqlca * pSqlca); // SQLCA
The parameters of this API have the same meanings as the corresponding parameters of the sqluhgne API, with the exception of CallerAction, which is new. The reserved fields of this API, Reserved_1 and pReserved, must be initialized to 0 and NULL, respectively. The possible values of CallerAction are:
SQLUH_GET_NEXT_ENTRY
Retrieves the next matching entry.
No DDL information is returned to the caller, but the length
field of the sqluhadm.command structure will be set to the length
of any DDL field that exists for that entry.
SQLUH_GET_DDL
If this action is passed to the API immediately after
fetching an entry, the DDL data associated with that entry is returned.
It is the responsibility of the caller to indicate - through
the sqluhadm.command.length field - the number of bytes
of memory which have been allocated for DDL data to be
returned through the sqluhadm.command.data field.
SQLUH_GET_NEXT_ENTRY_DDL
Retrieves the next matching entry.
It also returns any DDL information which has been recorded for the entry.
The caller must indicate - through the sqluhadm.command.length field -
the number of bytes of memory which have been allocated for
the sqluhadm.command.data field.
The second parameter of this API, which was "int callerac", is now "struct sqluhadm *pAdminInfo". The API definition is:
SQL_API_RC SQL_API_FN
sqluhgne(
unsigned short Handle,
struct sqluhadm * pAdminInfo,
struct sqluhinfo * pHistoryInfo,
struct sqlca * pSqlca);
The description of the SQLUH_LIST_ADM_ALTER_TABLESPACE caller action should be changed to:
Select only the ALTER TABLESPACE records that pass the other filters. The DDL field associated with an entry will not be returned. To retrive the DDL information for an entry, sqluhgne must be called with a caller action of SQLUH_GET_DDL immediately after the entry is fetched.
This API affects only the partition to which an application is directly connected; the load utility operates on a single database partition only.
The Usage Notes section incorrectly states that "summary tables" that are dependent on the tables being loaded are placed in check pending state. It should state that "summary tables defined with REFRESH IMMEDIATE", that are dependent on the tables being loaded, are placed in check pending state.
The valid entries for the SQLE-CLIENT-INFO TYPE element should be changed to:
SQLE_CLIENT_INFO_USERID SQLE_CLIENT_INFO_WRKSTNNAME SQLE_CLIENT_INFO_APPLNAME SQLE_CLIENT_INFO_ACCTSTR
Table 46, "Updateable Database Manager Configuration Parameters" has an additional entry for a new parameter, catalog_noauth:
Parameter Name Token Token Value Data Type -------------- ----- ----------- --------- catalog_noauth SQLF_KTN_CATALOG_NOAUTH 314 Uint16
The definition of the SQLUHADM structure should be changed to:
SQL_STRUCTURE sqluhadm
{
char end_time[SQLUH_TIMESTAMP_SZ+1]; // OUT: Completion time stamp of the event
char id[SQLUH_ID_SZ+1]; // OUT: Unique identifier of a dropped table
struct sqlca event_sqlca; // OUT: SQLCA associated with the entry
struct sqlchar command; // IN/OUT: DDL command information
};
Notes:
Change "struct Init_output *," in the "C API Syntax" section to "struct Data *,".
There is a problem with the "cob2 -dll" command of VisualAge COBOL for Windows NT Version 2.2. The original command in "x:\sqllib\samples\cobol\bldvacbs.bat" for creating a DLL is:
cob2 -dll %1.obj db2api.lib
This version of VisualAge COBOL returns the following error:
fatal error : Unable to open "E:\sqllib\samples\cobol\db2api.lib".
This will be corrected in the first FixPak of VisualAge COBOL for Windows NT. Until that FixPak is available, however, the problem can be solved by replacing the above command with:
ilib /nol /gi:%1 %1.obj ilink /free /nol /dll db2api.lib %1.exp %1.obj iwzrwin3.obj
There is a new environment variable for specifying which Java Development Kit to use on the Windows operating system for DB2 SQLJ. The environment variable is called DB2JVIEW, and it applies to all DB2 SQLJ commands (db2profc, db2profp, profdb and profp), except sqlj. If DB2JVIEW=1, Microsoft SDK for Java will be used; that is, if you call "profp", it will run as "jview sqlj.runtime.profile.util.ProfilePrinter". If DB2JVIEW=0, or not set, Sun JDK will be used; that is, if you call "profp", it will run as "java sqlj.rutnime.profile.util.ProfilePrinter". The command "sqlj" will ignore this variable, and always use the Sun JDK to run. To compile the Java source code generated by "sqlj", use the Java compiler (javac) that comes with Sun JDK.
In the "Micro Focus COBOL" section, the link option -lthread in the bldmfcc script file is no longer needed as the thread library has been incorporated into the DB2 library, represented by the -ldb2 option.
To build and run Java programs on HP-UX now requires the HP-UX Developer's Kit for Java Release 1.1.3 (or higher).
DB2 does not support Java stored procedures and Java user-defined functions (UDFs) accessing DB2 databases on HP-UX and SCO UnixWare servers. Because of this restriction, the following Java sample programs cannot be run against DB2 databases on HP-UX and SCO UnixWare:
DB2Stp.java DB2Udf.java CatUdf.sqlj DropUdf.sqlj Stp.sqlj Udfsrv.java
There have been several changes to the Java sample programs documented in this chapter. The stored procedure and UDF programs have been split into separate client and server programs. This allows users on client machines to remotely access stored procedures and UDFs on server machines. The files affected are as follows:
The script file embprep has been created to precompile and bind the SQLJ programs. It is called by the makefile and executes the db2profc command on the Java embedded SQL sample programs. It allows optional arguments for database, user ID and password, thereby facilitating remote binding to databases on DB2 servers from client machines.
The description of SQLPrepare() does not indicate that deferred prepare is now ON by default.
This means that the processing of the SQL statements passed to the database using SQLPrepare() does not take place when the SQLPrepare() function is called. Instead, it is processed when SQLExecute() is called.
Minimal error checking is performed by DB2 CLI when SQLPrepare() is called (that is, valid function arguments, and so on). The true SQLSTATEs are not returned from the prepare until SQLExecute() is called. Your application should therefore expect SQLSTATEs from SQLPrepare() after the call to SQLExecute(), followed by SQLSTATEs from the SQLExecute() call itself.
For more information see "Deferred Prepare now on by Default" in "Appendix B. Migrating Applications", in the section "Changes from Version 2.1.1 to 5.0.0". This describes how to turn deferred prepare off for Version 2 programs, as well as a further description of the deferred prepare process.
The description states that cursors are closed by a call to SQLDisconnect(). This is not the case, however, with stored procedures.
An application should not rely on SQLDisconnect() to close cursors, even if it is not a stored procedure. In both cases, the cursor should be closed using SQLCloseCursor(), then the statement handle freed with a call to SQLFreeHandle() with a HandleType of SQL_HANDLE_STMT.
The book indicates that the stored procedures must be run on a remote server. This is no longer the case. Stored procedures running on a local server can return result sets.
The book indicates that the stored procedure should call SQLFreeStmt() with either SQL_DROP or SQL_CLOSE; however, only SQL_CLOSE should be used.
All stored procedures should be registered in the new SYSCAT.PROCEDURES and SYSCAT.PROCPARMS catalog tables using the CREATE PROCEDURE statement. For more information about this SQL statement, see the SQL Reference.
If the stored procedures are not registered using the CREATE PROCEDURE statement, CLI or ODBC applications will not know that they exist. For more information, see the section "Replacement of the Pseudo Catalog Table for Stored Procedures" in the Migration Appendix.
This command is available on Windows NT, Windows 95, and Windows 98.
The following switches are now supported:
/c Invoke command.exe with the /c option (that is, execute the command,
and then terminate. For example, "db2cmd /c dir" causes the "dir" command
to be invoked in a command window, and then the command window closes.
/w Wait until the cmd.exe process ends.
For example, "db2cmd /c /w dir" invokes the "dir" command, and db2cmd.exe
does not end until the command window closes.
/i Run the command window, sharing the same console and inheriting file handles.
For example, "db2cmd /c /w /i db2 get dbm cfg > myoutput"
invokes cmd.exe to run the db2 command and to wait for its completion.
A new console is not assigned, and stdout is piped to file "myoutput".
/t Instead of using "DB2 CLP" as the title of the command window,
inherit the title from the invoking window. This is useful
if you want to set up an icon with a different title that
invokes "db2cmd /t".
| Note: | All switches must appear before any commands to be executed. For example: db2cmd /t db2 |
If DB21061E ("Command line environment not initialized.") is returned when bringing up the CLP-enabled DB2 window, or running CLP commands on Windows 95 or Windows 98, the operating system may be running out of environment space. Check the config.sys file for the SHELL environment setup parameter, and increase its value accordingly. For example:
SHELL=C:\COMMAND.COM C:\ /P /E:32768
The "Command Parameters" section is missing the following description:
-evm evmon-name
Specifies the name of the event monitor whose traces are to be analyzed.
The "-db" and "-evm" keywords are case sensitive.
Option "-a" generates statistics for all users on the database. If used with option "-e", it generates the DDL for all user tables in the database, but statistics are not generated.
Notes:
The "Command Parameters" section has been changed to:
variable=value
Sets a specified variable to a specified value.
To delete a variable, do not specify a value for the specified variable.
Changes to settings take effect after the instance has been restarted.
-g
Accesses the global profile variables.
-i instance
Specifies the instance profile to use instead of the current, or default.
node-number
Specifies a number listed in the db2nodes.cfg file.
-all
Displays all occurrences of the local environment variables as defined in:
o The environment, denoted by [e]
o The node level registry, denoted by [n]
o The instance level registry, denoted by [i]
o The global level registry, denoted by [g].
-null
Sets the value of the variable at the specified registry level to null.
This avoids having to look up the value in the next registry level,
as defined by the search order.
-r instance
Resets the profile registry for the given instance.
-n DAS node
Specifies the remote DB2 administration server node name.
-u user
Specifies the user ID to use for the administration server attachment.
-p password
Specifies the password to use for the administration server attachment.
-l
Lists all instance profiles.
-lr
Lists all supported registry variables.
-v
Specifies verbose mode.
-h/-?
Displays help information. When this option is specified,
all other options are ignored, and only the help information is displayed.
Although this utility is installed at the server, it can be run at a client; to do so, copy the executable from the server to the client.
In an MPP environment, the utility must be run from the catalog node of the specified database.
To use tape devices, DB2 users on SCO UnixWare 7 need to specify BUFFER to be 16. The default value of BUFFER is 1024 pages. If BUFFER is set to zero, the database manager configuration parameter BACKBUFSZ must be set to 16.
Table 6 ("Valid Delimiters"), and all references to it, should be removed. The information contained in the note at the bottom of the table, and the restrictions that follow the note, are correct, however.
| Note: | In a DBCS environment, the pipe (|) character delimiter is not supported. |
Replace the section "DB2 File Manager Considerations" in the Usage Notes with the following:
To ensure that a consistent copy of the table and the corresponding files referenced by the DATALINK columns are copied for export, do the following:
EXPORT is executed as an SQL application. The rows and columns satisfying the SELECT statement conditions are extracted from the database. For the DATALINK columns, the SELECT statement should not specify any scalar function. The export utility uses APIs to extract parts of the DATALINK value, such as link type, file server name, file path name, and comments.
Successful execution of EXPORT results in generation of the following files:
Use the dlfm_export utility to export files from a file server as follows:
Usage: dlfm_export <control file name> [<tar file name>]
Description:
<control file name> This is the file name that was generated by
running the export utility on the DB2 client.
<tar file name> This is the name of the archive file that will
be generated (the default is export.tar
in the current working directory).
A corresponding utility called dlfm_import is provided to retrieve and to restore files from the archive that dlfm_export generates. This utility must be used whether the archived files are being restored on the same or on a different file server. Use the dlfm_import utility to retrieve files from the archive as follows:
Usage: dlfm_import [<tar file name>]
Description:
<tar file name> This is the name of the archive file that will
be used to recover the files (the default is export.tar
in the current working directory).
Notes:
Exporting Between Instances
The table below shows how to export DB2 data and the files that are referenced by the instance called SystemA to the instance called SystemB. SystemA uses file servers DLFM1 and DLFM2. SystemB uses file servers DLFMX and DLFMY. The files on DLFM1 will be exported to DLFMX, and the files on DLFM2 will be exported to DLFMY.
+------------------------------------------------------------------------------+ | InstanceA with File Servers DLFM1 and DLFM2 | Step | +------------------------------------------------------------------------------+ | DB2 Data | File1 for DLFM1 | File2 for DLFM2 | | | on File | | | | +----------+-----------------+-----------------+-------------------------------+ | | Run the | Run the | 1. Run dlfm_export (as root) | | | dlfm_export | dlfm_export | on both file servers. | | | command | command | This will produce an archive | | | | | on both file servers. | +----------+-----------------+-----------------+-------------------------------+ +------------------------------------------------------------------------------+ | InstanceB with File Servers DLFMX and DLFMY | Step | +------------------------------------------------------------------------------+ | | On DLFMX, | On DLFMY, | 2. Run dlfm_import (as root) | | | restore from an | restore from an | on both file servers. | | | archive | archive | | +----------+-----------------+-----------------+-------------------------------+ | | | | 3. Run the IMPORT command on | | | | | InstanceB, with the parameter | | | | | DL_URL_REPLACE_PREFIX to | | | | | specify the appropriate file | | | | | server for each exported file.| +----------+-----------------+-----------------+-------------------------------+ +------------------------------------------------------------------------------+ | After the import utility is run on InstanceB, InstanceA data and all | | files referenced by DATALINK columns are imported. | +------------------------------------------------------------------------------+
A new parameter, catalog_noauth, specifies whether users are able to catalog and uncatalog databases and nodes, or DCS and ODBC directories, without SYSADM authority. The default value (0) for this parameter indicates that SYSADM authority is required. When this parameter is set to 1 (yes), SYSADM authority is not required.
In an MPP environment, if a database was bound using the INSERT BUF option, buffered insert will be disabled during any import operation to that database if the INSERT_UPDATE is specified.
NOCHECKLENGTHS is supported for both the ASC and the IXF file formats. This option does not have to be used with the FORCEIN modifier. If NOCHECKLENGTHS is specified, an attempt is made to import each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows may be successfully imported if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions.
The current default priority for delimiters is: record delimiter, character delimiter, column delimiter. To protect existing customer applications that depend on the older priority, a new file type modifier, DELPRIORITYCHAR, has been created for the import utility. This modifier reverts the delimiter priorities to: character delimiter, record delimiter, column delimiter. The syntax is: db2 import ... modified by delprioritychar ...
There can be a maximum of eight opened database directory scans per process. To overcome this restriction for a batch file that issues more than eight LIST DATABASE DIRECTORY commands within a single DB2 session, convert the batch file into a shell script. The "db2" prefix generates a new DB2 session for each command.
This command affects only the partition to which a direct connection exists; the load utility operates on a single database partition only.
The COPY option is not supported for tables with DATALINK columns.
The REPLACE option is not supported for tables with DATALINK columns.
The NONRECOVERABLE clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in - or being added to - the table.
There is a limit on the combination of DATALINK columns and the number of DLFMs used. There can be at most 256 unique combinations of DLFMs and DATALINK columns in a table. For example, the table can have a maximum of 128 DATALINK columns if the number of DLFMs configured is two.
Load copy to tapes is not supported for DB2 servers running on SCO UnixWare 7.
The Usage Notes section incorrectly states that "summary tables" that are dependent on the tables being loaded are placed in check pending state. It should state that "summary tables defined with REFRESH IMMEDIATE", that are dependent on the tables being loaded, are placed in check pending state.
NOCHECKLENGTHS is supported for both the ASC and the IXF file formats. This option does not have to be used with the FORCEIN modifier. If NOCHECKLENGTHS is specified, an attempt is made to load each row, even if the source data has a column definition that exceeds the size of the target table column. Such rows may be successfully loaded if code page conversion causes the source data to shrink; for example, 4-byte EUC data in the source could shrink to 2-byte DBCS data in the target, and require half the space. This option is particularly useful if it is known that the source data will fit in all cases despite mismatched column definitions.
The current default priority for delimiters is: record delimiter, character delimiter, column delimiter. To protect existing customer applications that depend on the older priority, a new file type modifier, DELPRIORITYCHAR, has been created for the load utility. This modifier reverts the delimiter priorities to: character delimiter, record delimiter, column delimiter. The syntax is: db2 load ... modified by delprioritychar ... The new option is not supported in an MPP environment, nor is it supported by the AutoLoader. Load SMP exploitation is disabled when this option is specified (CPU_PARALLELISM is forced to a value of 1). The new option is mutually exclusive with the "DUMPFILE" feature, and is supported only for the DEL file format.
There is an error in the description of "DLREPORT filename". There is no default value for this parameter.
The file name must be a fully qualified file name. For example:
/home/user/report.out
If the WITHOUT DATALINK option is not specified, and the DB2 File Manager containing the DATALINK data is unavailable, the restore operation will fail.
If this option is specified, and the DB2 File Manager containing the DATALINK data is unavailable, all table spaces which contain tables with DATALINK values on the unavailable server are placed in RESTORE PENDING state.
To use tape devices, DB2 users on SCO UnixWare 7 need to specify BUFFER to be 16. The default value of BUFFER is 1024 pages. If BUFFER is set to zero, the database manager configuration parameter BACKBUFSZ must be set to 16.
There are two changes to the numbered list under the "dbinfo" argument:
17. Unique application identifier (appl_id)
This field is a pointer to a C null-terminated string which
uniquely identifies the application's connection to DB2.
It is regenerated at each database connect.
The string has a maximum length of 32 characters, and its
exact format depends on the type of connection established
between the client and DB2. Generally it takes the form
<x>.<y>.<ts>
where the <x> and <y> vary by connection type, but the <ts>
is a 12 character time stamp of the form YYMMDDHHMMSS, which
is potentially adjusted by DB2 to ensure uniqueness.
Example: *LOCAL.db2inst.980707130144
17. Reserved field (resd2)
This field is for future use. It is defined as 20 characters long.
BIGINTs can now be declared in one of three ways:
This declaration is specific to the Microsoft compiler.
This is useful when writing platform-independent applications. The DB2 UDB sqlsystm.h header file will type define sqlint64 as "__int64" on the Windows NT platform when using the Microsoft compiler; otherwise, sqlint64 will be type defined as "long long".
When specifying the WCHARTYPE CONVERT option on a Windows platform, you should note that wchar_t on Windows platforms is Unicode. Therefore, if your C/C++ compiler's wchar_t is not Unicode, the wcstombs() function call may fail with SQLCODE -1421 (SQLSTATE=22504). If this happens, you can specify the WCHARTYPE NOCONVERT option, and explicitly call the wcstombs() and mbstowcs() functions from within your program.
In the section "COM.ibm.db2.app.UDF", there is a list of functions that return specific DBINFO fields. To this list has been added:
public String getDBapplid() throws Exception
When running JDBC programs with JDK 1.1.4 bundled with AIX 4.3.1 under a DBCS code page, the DBCS data can not be displayed properly. This is a known problem in this version of JDK, and a fix for it (IX78932) is available.
Because a native method cannot make use of the thread library (libthread.so from UnixWare 7; see the UnixWare release notes), DB2 JDBC sample applications (DB2Appl, DB2Udf, and DB2Stp) do not work from a Version 5.2 client on UnixWare 7 connecting to a Version 5 UDB server, or a Version 5 client connecting to a FixPak 3 UDB server.
Embedded SQL using the DATALINK data type is not supported.
On AIX, Solaris, HP-UX, SCO UnixWare 7, and Silicon Graphics IRIX, changes have been made to the functions that are used for run time querying of the code page and country code to be used for a database connection. They are now thread safe but can create some lock contention (and resulting performance degradation) in a multithreaded application which uses a large number of concurrent database connections.
A new environment variable has been created (DB2_FORCE_NLS_CACHE) to eliminate the chance of lock contention in multithreaded applications. When DB2_FORCE_NLS_CACHE is set to TRUE the code page and country code information is saved the first time a thread accesses it. From that point on the cached information will be used for any other thread that requests this information. By saving this information, lock contention is eliminated and in certain situations a performance benefit will be realized.
DB2_FORCE_NLS_CACHE should not be set to true if the application changes locale settings between connections. If this is done then the original locale information will be returned even after the locale settings have been changed. In general, multithreaded applications will not change locale settings. This ensures that the application remains thread safe.
Changes have been introduced (a) to give the user explicit control over scratchpad duration, and (b) to avoid inconsistencies in the duration of scratchpads, for table functions written in:
This difference has led to the possibility of getting inconsistent results, for example when performing a join.
The following changes have been introduced for User Defined Functions (UDFs) which are table functions:
Or, you can elect to have the scratchpad content be preserved across OPEN calls, by specifying FINAL CALL. If you do this, you need not release resources during the CLOSE call processing, because a FINAL call will be made to the table function at end-of-statement. This FINAL CALL is new with this change, as is the balancing FIRST call which takes place before the first OPEN call. These two new call types occur only on table functions which specify FINAL CALL.
| |
Point | | NO FINAL CALL | FINAL CALL
in | | LANGUAGE JAVA | LANGUAGE JAVA
scan | | SCRATCHPAD | SCRATCHPAD
time V | |
------------+------------------------------+------------------------------
Before the | No calls. | Class constructor is called
first OPEN | | (means new scratchpad).
for the | | UDF Method is called with
table func. | | "FIRST" call.
| |- - - - - - - - - - - - - - -
| | Constructor initializes
| | class and scratchpad
| | variables.
| | Method connects to the Web
| | Server.
| |
------------+------------------------------+------------------------------
At each | Class constructor is called | UDF Method is called with
OPEN of the | (means new scratchpad). | "OPEN" call.
table func. | UDF Method is called with |
| "OPEN" call. |
|- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
| Constructor initializes | Method opens the scan for
| class and scratchpad | whatever Web data it wants.
| variables. | (Might be able to avoid
| Method connects to the Web | reopen after a CLOSE repo-
| Server, and opens the scan | sition, depending on what
| for Web data. | is saved in scratchpad!)
| |
------------+------------------------------+------------------------------
At each | UDF method is called with | UDF Method is called with
FETCH for | "FETCH" call. | "FETCH" call.
new row of |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
table func | Method fetches and returns | Method fetches and returns
data. | next row of data, or EOT. | next row of data, or EOT.
| |
------------+------------------------------+------------------------------
At each | UDF method is called with | UDF Method is called with
CLOSE of the| "CLOSE" call. | "CLOSE" call.
table func. | "close" method is called if |
| it exists for class. |
|- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
| Method closes its Web scan | Method might reposition to
| and disconnects from the | the top of the scan, or
| Web Server. | close the scan. It can
| "close" does not need to do | save any state in the
| anything. | scratchpad, which will
| | persist.
| |
------------+------------------------------+------------------------------
After the | No calls. | UDF Method is called with
last CLOSE | | "FINAL" call.
of the table| | "close" method is called if
func. | | it exists for class.
| |- - - - - - - - - - - - - - -
| | Method disconnects from the
| | Web Server.
| | "close" does not need to do
| | anything.
| |
------------+------------------------------+------------------------------
Some notes on this table:
public int getCallType () throws Exception
Defines for the new call types are added to sqludf.h, the UDF include file.
UDB customers who have existing table functions are advised to examine the above options and make appropriate changes:
Detailed information changes will appear in the "SQL Reference" and the "Embedded SQL Programming Guide" (ESPG) when they are next updated. Meanwhile, a "Technote" describing these changes in more detail will be made available in the DB2 Technical Library on the World Wide Web. Use the search argument "FINAL CALL".
The following sections describe the detailed changes to the "SQL Reference" and the "Embedded SQL Programming Reference".
In Chapter 6 of the "Embedded SQL Programming Guide", under the heading "User-Defined Functions (UDF)", in the Example "Table Function Returning Document IDs", "FINAL CALL" should now read "NO FINAL CALL", and in the following paragraph note that FINAL CALL need not be specified for each table function.
In Chapter 7 of the "Embedded SQL Programming Guide", under the heading "Interface between DB2 and a UDF", in the section "The Arguments Passed from DB2 to a UDF", in the description for the scratchpad argument, the text beginning "The actual..." should be replaced by:
For table functions, the scratchpad is initialized as above only for the very first call to the UDF if FINAL CALL is specified on the CREATE FUNCTION. This "very first call" has type FIRST. After this call, the scratchpad content is totally under control of the table function.
If NO FINAL CALL was specified or defaulted, then the scratchpad is initialized as above for each OPEN call, and the scratchpad content is completely under control of the table function between OPEN calls. (This can be very important for a table function used in a join or subquery. If it is necessary to maintain the content of the scratchpad across OPEN calls, then FINAL CALL must be specified in your CREATE FUNCTION statement. With FINAL CALL specified, in addition to the normal OPEN, FETCH and CLOSE calls, the table function will also receive FIRST and FINAL calls, for the purpose of scratchpad maintenance and resource release.)
Also, in the description of the call-type argument, the second sentence should be replaced by the following sentence:
For external scalar functions this argument is only present if FINAL CALL is specified in the CREATE FUNCTION statement, but for external table functions it is ALWAYS present.
After that first paragraph, add the following new paragraph:
Note that even though all the current possible values are listed below, your UDF should contain a switch or case statement which explicitly tests for all the expected values, rather than containing "if A do AA, else if B do BB, else it must be C so do CC" type logic. This is because it is possible that additional call types may be added in the future, and if you don't explicitly test for condition C you will have trouble when new possibilities are added.
In the same section, where the values for the call-type argument are explained for Table Functions, the text should be amended as follows:
Under the heading "Summary of Argument Use", for the argument scratchpad and call-type, add the text ", and optionally FIRST and FINAL" in both cases.
In Chapter 7 of the "Embedded SQL Programming Guide", under the heading "Table Function Considerations", under the first bullet, there are now five call types instead of three: add FIRST and FINAL to the list.
Also, add a new bullet in this section as well, which should read as follows:
The error processing model for table function calls is as follows:
In the "Weather Table Function" example in Chapter 7, the UDF definition should specify NO FINAL CALL, and note that FINAL CALL is no longer mandatory for a table function (that is, ignore the statement that says it is mandatory).
In Chapter 15 of the "Embedded SQL Programming Guide", entitled "Programming in Java", under the heading "Creating Java UDFs and Stored Procedures", the second sentence of the second paragraph should be changed to read as follows:
Once you create and register these UDFs and stored procedures, and place the Java classes in the correct file location, described in "Where to Put Java Classes" on page 514, you can then (for UDFS) reference them in your SQL, or (for stored procedures) call them from you application written in any supported language.
And the sentence following that one should be removed.
In Chapter 15 of the "Embedded SQL Programming Guide", under the heading "Creating and Using Java User-Defined Functions", the fourth sentence of the first paragraph should be changed to read:
You can then refer to it in the SQL of your application.
In the subheading "Changing How a Java UDF Runs" under that same heading, the first three paragraphs should be replaced with the following five:
Typically, DB2 calls a UDF many times, once for each row of an input or result set in a query. If SCRATCHPAD is specified in the CREATE FUNCTION statement of the UDF, DB2 recognizes that some "continuity" is needed between successive invocations of the UDF, and therefore the implementing Java class is not instantiated for each call, but generally speaking once per UDF reference per statement. Generally it is instantiated before the first call and used thereafter, but may for table functions be instantiated more often-- see the NO FINAL CALL execution model in the subsection which follows this one. If, however, NO SCRATCHPAD is specified for a UDF, either a scalar or table function, then a clean instance is instantiated for each call the the UDF, by means of calling the class constructor. A scratchpad may be useful for saving information across calls to a UDF. Whereas for C, C++ and OLE UDFs the scratchpad is maintained by DB2, in Java the UDF can simply use instance variables. For table functions using a scratchpad, you can control when you get a new instance by use of the FINAL CALL / NO FINAL CALL option on the CREATE FUNCTION statement, as indicated by the execution models in the subsection which follows this one. For scalar functions, you use the same instance for the entire statement. Another choice you have is to use the DB2 provided Java methods getScratchpad() and setScratchpad(), in which case you will be using the DB2-managed scratchpad. The ability to achieve continuity between calls to a UDF my means of a scratchpad, is controlled by the SCRATCHPAD / NO SCRATCHPAD option of CREATE FUNCTION, regardless of whether the DB2 scratchpad or instance variables are used. Please note that every reference to a Java UDF in a query is treated independently, even if the same UDF is referenced multiple times. This is the same as what happens for OLE, C and C++ UDFs as well. At the end of a query, if you specify the FINAL CALL option for a scalar function then the object's close() method is called. For table functions the close() method will always be invoked as indicated in the subsection which follows this one. If you do not define a close() method for your UDF class, then a stub function takes over and the event is ignored.
And in the next paragraph, which begins "If you specify the ALLOW PARALLEL ...", the last sentence should be removed.
Following the subsection "Changing How a Java UDF Runs", add a new subsection at the same level, entitled "Table Function Execution Model for Java", which contains the following:
For table functions written in Java, it is important to understand what happens at each point in DB2's processing of a given statement which is significant to the table function. The table which follows details this information. The bottom part of each box hints what the code might be written to do for a typical table function which pulls some information in from the Web). Covered are both the NO FINAL CALL and the FINAL CALL cases, assuming SCRATCHPAD in both cases.
| |
Point | | NO FINAL CALL | FINAL CALL
in | | LANGUAGE JAVA | LANGUAGE JAVA
scan | | SCRATCHPAD | SCRATCHPAD
time V | |
------------+------------------------------+------------------------------
Before the | No calls. | Class constructor is called
first OPEN | | (means new scratchpad).
for the | | UDF Method is called with
table func. | | "FIRST" call.
| |- - - - - - - - - - - - - - -
| | Constructor initializes
| | class and scratchpad
| | variables.
| | Method connects to the Web
| | Server.
| |
------------+------------------------------+------------------------------
At each | Class constructor is called | UDF Method is called with
OPEN of the | (means new scratchpad). | "OPEN" call.
table func. | UDF Method is called with |
| "OPEN" call. |
|- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
| Constructor initializes | Method opens the scan for
| class and scratchpad | whatever Web data it wants.
| variables. | (Might be able to avoid
| Method connects to the Web | reopen after a CLOSE repo-
| Server, and opens the scan | sition, depending on what
| for Web data. | is saved in scratchpad!)
| |
------------+------------------------------+------------------------------
At each | UDF method is called with | UDF Method is called with
FETCH for | "FETCH" call. | "FETCH" call.
new row of |- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
table func | Method fetches and returns | Method fetches and returns
data. | next row of data, or EOT. | next row of data, or EOT.
| |
------------+------------------------------+------------------------------
At each | UDF method is called with | UDF Method is called with
CLOSE of the| "CLOSE" call. | "CLOSE" call.
table func. | "close" method is called if |
| it exists for class. |
|- - - - - - - - - - - - - - - |- - - - - - - - - - - - - - -
| Method closes its Web scan | Method might reposition to
| and disconnects from the | the top of the scan, or
| Web Server. | close the scan. It can
| "close" does not need to do | save any state in the
| anything. | scratchpad, which will
| | persist.
| |
------------+------------------------------+------------------------------
After the | No calls. | UDF Method is called with
last CLOSE | | "FINAL" call.
of the table| | "close" method is called if
func. | | it exists for class.
| |- - - - - - - - - - - - - - -
| | Method disconnects from the
| | Web Server.
| | "close" does not need to do
| | anything.
| |
------------+------------------------------+------------------------------
Some notes on this table:
public int getCallType () throws Exception
In Chapter 15 of the "Embedded SQL Programming Guide", under the heading "Classes for Java Stored Procedures and UDFs", and further under the subheading "COM.ibm.db2.app.UDF", the paragraph describing the "public void close()" method should be changed to read as follows:
This method is called by the database for scalar functions at the end of a statement evaluation, if the UDF was created with the FINAL CALL option, and is analogous to the final call for a C function. For table functions, it is called after the CLOSE call to the UDF method (if NO FINAL CALL is coded or defaulted), or after the FINAL call (if FINAL CALL is coded). If a Java UDF class does not implement this function, a no-op stub function will handle and effectively ignore this event.
Immediately following the description for "public void close()", add material for the new method "public int getCallType() throws Exception", which reads as follows:
This function is used by table function UDF methods to find out the call type for a particular call. It returns a value as follows (symbolic defines are provided for these values in the Com.ibm.db2.app.UDF class definition):
The following changes related to the Embedded SQL Programming Guide have been documented in the Release Notes for FixPaks prior to this release.
In chapter 1 of the ESPG, in the first paragraph under the heading "DB2 Bind File Dump Tool-db2bfd", there is a reference to "Version 3"; this should be changed to Version 5.
In chapter 4, the section "Join Strategies in a Partitioned Database" has several subsections showing examples of the different join strategies. In the pieces of artwork, all occurrences of "LINEITEMS" should be changed to "LINEITEM". Also, the subsection "Broadcast Inner-Table Joins", has incorrect text in the example shown. The text of the example should be:
The LINEITEM table is sent to all database partitions that have the ORDERS table. Table queue q3 is broadcast to all database partitions of the outer table.
The following changes pertain to chapter 7:
short indicator1, // input indicator short indicator2, // output indicator
should be changed to
short *indicator1, // input indicator short *indicator2, // output indicator
02000 Only valid for the FETCH call to table functions, it means
there are no more rows in the table.
If this is a table function, this field is a pointer to an array of short integers which is dynamically allocated by DB2. If this is a scalar function, this pointer is null.
other Invalid locator or other error (for example, memory error). The value
that is returned for these cases is the SQLCODE corresponding
to the error condition. For example, -423 means invalid
locator. Please note that before returning to the UDF with
one of these "other" codes, DB2 makes a judgement as to the
severity of the error. For severe errors, DB2 remembers that
the error occurred, and when the UDF returns to DB2, regardless
of whether the UDF returns an error SQLSTATE to DB2, DB2
takes action appropriate for the error condition. For
non-severe errors, DB2 forgets that the error has occurred,
and leaves it up to the UDF to decide whether it can take
corrective action, or return an error SQLSTATE to DB2.
-3 Bad input value is provided to the API. This includes:
- udfloc_p (address of locator) is zero
- return_len_p (address of where to put length) is zero
-3 Bad input value is provided to the API. This includes:
- udfloc_p (address of locator) is zero
- start is less than 1
- length is negative
- buffer_p (buffer address) is zero
- return_len_p (address of where to put length) is zero
-3 Bad input value is provided to the API. This includes:
- udfloc_p (address of locator) is zero
- length is negative
- buffer_p (buffer address) is zero
-3 Bad input value is provided to the API. This includes:
- udfloc_p (address of locator) is zero
- loc_type is not one of the three valid values
- loc_p (address of where to put locator) is zero.
-3 Bad input value is provided to the API. This includes:
- udfloc_p (address of locator) is zero
This API can be used to build very large LOB values in a piecemeal
manner. In cases where a large number of appends is used to build
a result, the performance of this task can be improved by:
- allocating a large application control heap (APP_CTL_HEAP_SZ is
the database manager configuration parameter)
- doing fewer appends of larger buffers; for example, instead of doing
20 appends of 50 bytes each, doing a single 1000 byte append.
SQL applications which build many large LOB values via the
sqludf_append() API may encounter errors caused by limitations on
the amount of disk space available. The chance of these errors
happening can be reduced by:
- using larger buffers for the individual appends
- doing frequent COMMITs between statements
- in cases where each row of a SELECT statement is building a
LOB value via this API, using a CURSOR WITH HOLD and doing COMMITs between rows.
Use this API to free any locators that were created with the sqludf_create_locator() API, and which were used only for internal manipulation. It is NOT NECESSARY to free locators passed into the UDF. It is NOT NECESSARY to free any locator created by the UDF via sqludf_create_locator() if that locator is passed out of the UDF as an output.
The following notes apply to the use of these APIs:
1. A UDF which is defined to return a LOB locator has several
possibilities available to it. It can return:
a. an input locator passed to it
b. an input locator passed to it which has been appended to
via sqludf_append()
c. a locator created to via sqludf_create_locator(), and appended
to via sqludf_append().
2. A table function can be defined as returning one or more
LOB locators. Each of them can be any of the possibilities
discussed in the preceding item. It is also valid for such
a table function to return the same locator as an output for
several table function columns.
3. A LOB locator passed to a table function as an input argument
remains alive for the entire duration of the row generation
process. In fact, the table function can append to a LOB using
such a LOB locator while generating one row, and see the
appended bytes on a subsequent row.
4. The internal control mechanisms used to represent a LOB which
originated in DB2 as a LOB locator output from a UDF (table
or scalar function), take 1950 bytes. For this reason, and
because there are limitations on the size of a row which is
input to a sort, a query which attempts to sort multiple
such LOBs which originated as UDF LOB locators will be
limited to (at most) two such values per row, depending on
the sizes of the other columns involved. The same
limitation applies to rows being inserted into a table.
In chapter 9, in the section "Accessing DRDA Servers", the second paragraph in point 1 should read:
For IBM products, consult the IBM SQL Reference, Version 5.2 (S10J-8165), before you start coding.
The following changes pertain to chapter 15:
class ... implements COM.ibm.db2.app.UDF
{ ... }
should be changed to
class ... extends COM.ibm.db2.app.UDF
{ ... }
The same is true for "COM.ibm.db2.app.StoredProc".
COM.ibm.db2.app.Blob public static Blob new() throws Exception; COM.ibm.db2.app.Clob public static Clob new() throws Exception;
These were renamed and moved to the new class COM.ibm.db2.app.Lob as the following functions:
COM.ibm.db2.app.Lob public static Blob newBlob() throws Exception; COM.ibm.db2.app.Lob public static Clob newClob() throws Exception;
SQL Type Java Type (UDF) Java Type (Stored Procedure) LONG VARCHAR Clob Clob LONG VARGRAPHIC Clob Clob
These should be changed to:
LONG VARCHAR String String LONG VARGRAPHIC String String
The following information should be added after the second paragraph in the section "Java UDFs and Stored Procedures":
When creating a stored procedure in the Java language, you must use the CREATE PROCEDURE statement to register the procedure to the system catalog table SYSCAT.PROCEDURES. For more information, refer to the section on the CREATE PROCEDURE statement in the SQL Reference.
The following is a limitation of the Java UDF/stored procedure support: On a mixed code page database server, Java user-defined functions and stored procedures cannot use CLOB type arguments, because random access on character boundaries on large mixed code page strings has not yet been implemented. Full support for all LOB types is intended for SBCS databases. For mixed databases, support is intended for the BLOB and the DBCLOB types. As a workaround, applications running on a mixed database system should convert CLOB arguments to DBCLOB, LONG VARGRAPH, or LONG VARCHAR types. For UDFs, this can be done with the CAST operator.
In appendix B, which describes the sample programs, references (in table 24) to the loblocud, db2uext2, and autoloader samples should be removed. For additional information, also refer to Revisions to Sample Programs and Other File Changes.
In the index, java_heap_sz is incorrectly referred to as java_heap_size.
Revisions to sample programs were made for various languages on the following platforms: AIX, HP-UX, OS/2, Solaris, Windows NT, and Windows 95. The revisions are to correct some code errors and to make better use of API calls within the programs. Some programs have been made generic for all platforms: before they were only available on specific platforms; for example, 'makeapi.sqc'. Also, the interface has been improved for several programs to better guide the user on program input, and to give more informative output. Many redundant files have had their file contents deleted, replaced by the comment "This file is not needed". Where a new file replaces the redundant file's functionality, a reference to this is also provided.
The following is a list of the updated files. Each entry contains the name of the file, its language, and the platforms for which it has been changed. Where the same file has been revised in more than one language, one file name is given with multiple extensions, as in "dbconf.c/.cbl/.f/.for". "COBOL" refers to both IBM and Micro Focus COBOL, unless otherwise indicated. "All platforms" refers to the following platforms only: AIX, HP-UX, OS/2, Solaris, Windows NT, and Windows 95. In some situations, a file may not be available on a specific platform; if you are not sure, refer to the README file in the appropriate samples directory. The file entry also contains a comment describing the nature of the change. The changes are as follows:
Fixed memory leak and improved user interface.
The correct file version is now available for OS/2.
Fixed 'version' parameter error in API call. Improved the user interface. Recoded Micro Focus COBOL version to make it generic for all platforms.
Recoded to make generic for all platforms.
Recoded to make generic for all platforms.
Redundant files: contents deleted.
Fixed 'version' parameter error in API call.
Recoded Micro Focus COBOL version to make it generic for all platforms. The correct REXX version is now available for OS/2.
Improved user interface.
Fixed 'version' parameter error in API call.
Fixed 'version' parameter error in API call.
This program is now available for OS/2 FORTRAN.
This program is now available for OS/2 FORTRAN.
This is a client program; the server program is fillsrv.sqc. These programs have been extensively rewritten, combining the functionality of the supporting programs da_manip, system, and tabinfo (which are now redundant). The use of APIs, demonstration of SQLDA structures, and user interface have all been improved.
This is a server program; the client program is fillcli.sqc (see that entry for more information).
Redundant file: contents deleted, as compiler does not need definition file for stored procedures.
Redundant file: contents deleted.
Redundant file: contents deleted.
This program is AIX-specific: file contents were deleted on the HP-UX, OS/2, Solaris, Windows NT, and Windows 95 platforms.
The correct version is now available for OS/2.
This replaces the specific program files for AIX and OS/2 with a generic program file that is now available for all platforms.
Fixed 'version' parameter error in API call.
Fixed 'version' parameter error in API call.
Redundant file: contents deleted, as compiler does not need definition file for stored procedures.
Redundant file: contents deleted.
Redundant file: contents deleted.
Improved interface provides more information and eliminates extraneous 'load file' error.
The non-embedded SQL program rechist.c has been replaced by the embedded SQL program rechist.sqc. The new file corrects a database connect error that occurred with rechist.c.
Fixed 'version' parameter error in API call.
Redundant files: contents deleted.
Redundant files: contents deleted.
Improves use of APIs to now demonstrate multiple table spaces.
Corrected error in API call to shared library.
The makefiles, where needed, have been updated to reflect the changes described in the previous section. For example, the C makefiles no longer create and link in the object files da_manip.obj, system.obj and tabinfo.obj for the stored procedure fillsrv.
The OS/2 command files for IBM COBOL and FORTRAN, and their respective makefiles, now use a stack size of 64000: the stack size link option for COBOL is now "/ST:64000", and for FORTRAN it is now "stack=64000". It was found that some of the stored procedure programs needed a larger stack size to run correctly.
The following command and batch file changes have been made:
Redundant file: contents deleted.
The correct version of this file is now available.
A remote system may not be detected if the client is on a different token ring than the server. Both the remote system and the system that initiates the discovery request must be on the same token ring in order for search discovery to work.
The TCP/IP host name returned to a client by SEARCH discovery is the same host name that is returned by the DB2 server system when a TCP/IP host name command is issued. On the client, the IP address that this host name maps to is determined by either the TCP/IP domain name server (DNS) configured on the client machine or, if a DNS is not configured, a mapping entry in the client hosts file. If multiple adapter cards are configured on the DB2 server system, ensure that TCP/IP is configured on the server to return the correct host name when a host name command is issued, and that the DNS or local client hosts file on the client maps the host name to the desired IP address.
Under the "Action" section, references to "sqllib/profile" should be changed to "sqllib/db2profile".
The following reason code for message SQL0270N has been changed:
Under "Cause":
23 REPLICATED can only be specified for a summary table.
Under "Action":
23 Either remove the REPLICATED specification or ensure that
the CREATE TABLE is specified in a summary table definition.
The following two reason codes have been added to message SQL0270N:
Under "Cause":
30 Typed tables and typed views cannot be defined in
a CREATE SCHEMA statement.
31 A partitioning key cannot be defined with more than 500 columns.
Under "Action":
30 Issue the CREATE statement for the typed view or typed table
outside of the CREATE SCHEMA statement.
31 Reduce the number of columns in the partitioning key.
Message: LOB or DATALINK data in position "<position-number>" of the select-list cannot be sent using DRDA protocol.
Cause: LOB or DATALINK data, or distinct types based on LOB or DATALINK data, cannot be flowed using the DRDA protocol. "<position-number>" is the position of the element with a LOB or DATALINK data type in the select-list.
The statement cannot be processed.
Action: Ensure that LOB or DATALINK data is not being retrieved when using DRDA, by excluding them from the select-list. For LOB data, try selecting smaller pieces of the value by using CHAR(SUBSTR(CLOB)). For DATALINK data, try using a function such as DURLCOMPLETE.
sqlcode: -351 sqlstate: 56084
Message: LOB or DATALINK data in position "<position-number>" of the input-list cannot be sent using DRDA protocol.
Cause: LOB or DATALINK data cannot be flowed using the DRDA protocol. "<position-number>" is the position of the element with a DATALINK data type in the input-list.
The statement cannot be processed.
Action: Ensure that LOB or DATALINK data is not being sent when using DRDA, by excluding it from the input-list. An alternative is to try building a value indirectly using the DLVALUE function.
sqlcode: -352 sqlstate: 56084
Message: The DB2 File Manager "<server-name>" is not currently available. Reason code = "<reason-code>".
Cause: A reference to a DATALINK value required access to the DB2 File Manager "<server-name>". The DB2 File Manager is not currently available as indicated by the reason code.
01 The DB2 File Manager in a DATALINK value is not available.
02 The database server, instance, or database from which the operation was
attempted is not registered with the DB2 File Manager in a DATALINK value.
03 Restart recovery is pending or in progress on the DB2 File Manager.
04 The DB2 File Manager in a DATALINK value is registered with the database
but is an unknown server.
05 An error occurred while communicating to the DB2 File Manager in a DATALINK
value.
The statement cannot be processed.
Action: The action depends on the reason code as follows.
01 The DB2 File Manager or a communication link may be down. Wait
a while and try again, or check with a system administrator for the DB2
File Manager. If the problem persists, disconnect the application from the
database, and retry after connecting again.
02 Register the database server, instance, or database with the DB2 File Manager.
03 Wait for the restart recovery to complete on the DB2 File Manager.
If the problem persists, check if the registration mentioned in the text
for reason code 02 above has been done. This might be one of the
reasons for the restart recovery not completing successfully.
04 Check that the DB2 File Manager configured to the database is available on
the network.
05 Check that the DB2 File Manager and the communication link are up.
If the problem persists, disconnect the application from the database,
and retry after connecting again.
sqlcode: -357
sqlstate: 57050
The description for SQL0432N has been changed to include the same error for reference types:
Message: A parameter marker cannot have the user-defined type name or reference target type name "<udt-name>".
Cause: A parameter marker in the statement has been determined to have the user-defined type "<udt-name>", or a reference type with the target type "<udt-name>", based on the context in which it is used. A parameter marker cannot have a user-defined type or reference type as its data type, unless it is part of an assignment (VALUES clause of INSERT, or SET clause of UPDATE), or it is being explicitly cast to a user-defined distinct data type, or reference data type, using the CAST specification. The statement cannot be processed.
Action: Use an explicit cast to the user-defined distinct data type, or reference data type for the parameter marker. An alternative is to cast the columns that are user-defined distinct data types to their corresponding source data type, or columns that are reference data types to their corresponding representation type.
sqlcode: -432 sqlstate: 42841
The text for SQLSTATE 42841 has been changed to:
42841 A parameter marker cannot be a user-defined type or a reference type.
Modified Cause: The maximum number of columns permitted for each table is based on the page size of the table space. The limits for tables are:
The number of columns may have exceeded the maximum allowed in a temporary table with any of the temporary table spaces available to process the statement.
New Action: If the error occurs processing a query, create a temporary table space of the largest page size supported if one does not already exist. If such a table space already exists, eliminate one or more columns from the temporary table.
Reason code 3 for message SQL0903N has been changed to:
3. One of the DB2 File Managers participating in the unit of work was unable to
prepare to commit.
There is a new reason code for message SQL0911N:
72. Transaction has been rolled back because of an error
concerning a DB2 File Manager involved in the transaction.
The following information has been added to the "Cause" section:
The error tokens will not be returned for CLI/ODBC applications. CLI/ODBC applications can use SQLGetDiagRec, SQLGetDiagField, or SQLError APIs to get more information about each error.
If message SQL5048N or SQL0901N is encountered when using DB2 Version 5.2 Change Password support, the most likely cause of the error is that the release level of the database client is not supported by the release level of the database server.
These messages may be returned from a CONNECT or ATTACH request for which a new password was specified and AUTHENTICATION=CLIENT is being used. If so, verify that the local database catalog entry for the named database alias does not specify AUTHENTICATION=SERVER.
If the above errors are returned from a CONNECT OR ATTACH request with a new password for a DB2 Common Server Version 2 or DDCS Version 2 multi-user gateway, the local database catalog entry for the database alias must specify AUTHENTICATION=CLIENT.
The following reason code has been added to message SQL20053N:
Under "Cause":
6. The table or view in the FROM clause of every subview in a
typed view hierarchy must use OUTER if any subview uses OUTER.
Under "Action":
6. If this is the first subview to use OUTER, change the FROM clause
so that OUTER is not used. If the superview uses OUTER,
include OUTER in the FROM clause of the subview.
Following is the updated message text for SQL20056N:
Processing on DB2 File Manager "<server-name>" encountered an error. Reason code = "<reason-code>". Additional diagnostic information "<diag-token>".
| Note: | Currently "<diag-token>" will always be set to "UNKNOWN". |
The following reason code for change password support, when it fails because of an invalid user name or password, has been added to message SQL30083N:
Under "Cause":
24. Username and/or password invalid.
This is returned when a change password fails due
to username or original password errors.
When you install DB2 with documentation on an NEC PC98 running Windows 95, the installation stops at 96%. To complete the installation:
If you have installed DB2 with documentation on an NEC PC98 running Windows 95, the uninstall operation stops before it has completed. To complete the uninstall operation:
Since HP is discontinuing support for NETWARE products, IPX/SPX is not supported on HP-UX Version 11.0.
PTF (Program Temporary Fix) 3 for ADSTAR Distributed Storage Manager (ADSM) Version 3 is required for an OS/2 client to work with DB2.
Two phase commit using SNA is not supported when connecting from DB2 for AS/400 to DB2 Universal Database Version 5.2 for AIX. It is supported when connecting from DB2 for AS/400 to DB2 Universal Database Version 5.2 for OS/2 and Windows NT.
When accessing a DB2 Universal Database server from a DB2 for OS/390 host the following DB2 for OS/390 PTFs are required:
The ability to change a password at a DB2 server from a DB2 client is limited to the following DB2 servers:
Chapter 15 of the Quick Beginnings for UNIX** book refers to the Nodelock Administration Tool (NAT), which is part of the License Use Management Administration Runtime Kit (LUM ARK). To count concurrent users on AIX, the LUM ARK must be installed on your system with the NAT. On some versions of LUM ARK, the NAT is in an optional file set. Please refer to your LUM documentation for more information. You can visit the IBM License Use Management Web site at:
http://www.software.ibm.com/is/lum
to download the latest LUM ARK and publications. For your convenience, a copy of the LUM ARK is included on the DB2 CD in the /cdrom/LUM directory.
The Quick Beginnings for DB2 Extended Enterprise Edition for Windows NT book states that a domain user name is required to create a database partition server, and is used to log on when DB2 is started as a Windows NT service. Note, however, that if raw devices are used, the domain user name must also have local administrative authority, or the user will not have access to the raw devices.
As a result of optimizer enhancements, the plan for any query could change, if rebound. Although every precaution is taken to ensure that the changed plan is at least as good as the plan chosen by the previous version, there is no guarantee of that, since certain changes could cause a different plan to be chosen, and it is not possible to test the virtually unlimited number of scenarios that a user could create.
It is recommended that an EXPLAIN be run on all critical queries before migrating versions, in case those queries get rebound to a new plan. If this is done, both the plan and the configuration parameters upon which it is based (under the old release) will be known. This, in turn, will help the user identify the source of any differences under the new version, and restore the conditions for the old plan, if desired.
DB2 does not support Java stored procedures and Java user-defined functions (UDFs) accessing DB2 databases on HP-UX and SCO UnixWare servers.
The abbreviated name of the Brazilian Portuguese locales for DB2 for Solaris and DB2 for SCO UnixWare 7 has been changed from "pt" to "pt_BR".
DB2 File Manager can only be used with DB2 for AIX Enterprise Edition. The File Manager cannot be used with databases on operating systems other than AIX, and cannot be used with DB2 Enterprise - Extended Edition (EEE) for AIX. DATALINK values cannot point to a DB2 EEE system; moreover, the DATALINK data type cannot be used on EEE systems to reference files on a DB2 File Manager.
The DLFM administration user ID must be "dlfm" for this release.
An SQL SELECT on a DATALINK column which was defined with the "READ PERMISSION DATABASE" option will return the URL or file name with an access token embedded within the name. This access token has an expiration time, as defined by the DL_EXPINT database configuration parameter. When using an NFS client to access such files, you may find that the access token does not expire appropriately. This can be due to caching done by the NFS client. It has been observed that the use of long file names (greater than 12 characters) may mitigate this problem.
Whenever you remotely mount a DLFS file system via NFS, you may also want to include the mount option "noac". For example:
mount -o noac birdcage:/dlink /dlink
Datalinks clients cannot be:
The command for DLFM setup has been changed to accept a dlfm_db configuration file. If the configuration file contains the setup options, the configuration options in the file are used instead of the defaults. The file is optional. If not specified, the defaults are used. The command syntax is now as follows:
dlfm setup [<dlfm_db_config_file>]
Following is a sample configuration file named dlfm_db.cfg:
# Sample dlfm_db.cfg file for DLFM setup # Please note: the equal sign is needed between the option name and the value. # Any of the updateable DB2 database configuration parameters can be included in this file. # There are also three special DLFM-specific options which the user can specify: # DLFM_DB_DIR : directory where the dlfm_db database is to be created # DLFM_DB_CATTBL_DIR : directory where the catalog table space is to be located # DLFM_DB_TMPTBL_DIR : directory where the temporary table space is to be located # If there are no values for these options, or the options are not present, # default values are used. #################### DLFM_DB_DIR= DLFM_DB_CATTBL_DIR= DLFM_DB_TEMPTBL_DIR= LOCKTIMEOUT=60 # Default and recommended value for dlfm_db is 60 LOGFILSIZ=2000 # Default and recommended value for dlfm_db is 2000 LOGPRIMARY=6 # Default and recommended value for dlfm_db is 6 MAXAPPLS=128 # Default and recommended value for dlfm_db is 128 MAXLOCKS=100 # Default and recommended value for dlfm_db is 100
This section describes how to perform recovery after a disk crash on the File Manager Server, and the backup strategies that the administrator must undertake to support such disaster recovery scenarios.
| Note: | In the case of a machine crash on a DB2 File Manager, DB2 applications interacting with the DB2 File Manager can get hung. Use the FORCE APPLICATION command to force such applications off the system. In the case of a machine crash on a DB2 server, the affected DB2 File Managers should also be shut down, using the "dlfm shutdown" command. They can then be brought up again. |
If the disk containing user data crashes, all user files, along with the directory hierarchy of the file system, are destroyed. To recover from such a scenario, the administrator must make periodic backups of the file system containing the user data and directory hierarchy, so that it can be restored. The restored file system must preserve directory and file ownerships, and time stamps. After restoring the file system, the directory structure must be brought up to the point-in-time of the crash by applying the directory changes that occurred after the file system backup was taken. After this step, the RECONCILE command must be run on all tables containing files on the damaged disk. A utility, "db2_reconcile_aid" (located in sqllib\misc), is provided to simplify this task, and is described below.
Notes:
Backing up and Restoring the File System
The following instructions are specific to AIX Version 4.1.5, and may not apply to other operating systems. Users must be logged in as root to perform backup or restore operations.
Backup
The backup should be made in Version 3 inode format:
System Storage Management --> File Systems --> Unmount a File System
System Storage Management --> File Systems --> Backup a File System
An incremental backup strategy could be used to reduce recovery time. Level 0 refers to a full backup, and levels 1 through 9 refer to incremental backups. A level n backup backs up only those files that have changed since a level (n-1) backup. After a level n backup, the next backup to be taken will be a level (n+1) backup.
A backup can also be invoked from the command line:
% backup -<level> -uf <backup device> <filesystem>
Examples:
For a full backup use:
% backup -0 -u -f/dev/rmt0 /dlfs1
For the next incremental backup use:
% backup -1 -u -f/dev/rmt1 /dlfs1
The incremental backup after this will be:
% backup -2 -u -f/dev/rmt2 /dlfs1
System Storage Management --> File Systems --> Mount a File System
Restore
A restore should be performed using one of the backups taken previously:
System Storage Management --> File Systems --> Mount a File System
System Storage Management --> File Systems --> Restore a File System
A restore can also be invoked from the command line. From the directory where the restore is to be done (that is, the mount point for the file system device), issue the following command:
% restore -r -f <backup device>
If an incremental backup strategy is being used, restore the newest level 0 backup, then restore the level 1 backup taken after the level 0 backup. Restore the level n backup taken after the level (n-1) backup. Ensure that the file "restoresymtable" in the root of the file system remains intact between restorations of incremental backups.
Attention: Do not use the "-M" option during restoration. The "-M" option changes the time stamp of the files to the time that the file system was restored.
If the backup or restore operation is being done through ADSM, a full backup by inode needs to be made after a restore operation to overwrite any previous backups.
Dedicated media should be used when making backups.
Restoring Only the Directories
The user can interactively restore files using the "-i" option on the restore command:
% restore -i -f <backup device>
This provides the user with a list of directories or files in the backup, and takes the user into an interactive shell. Specific directories can be restored using the "-h" option.
Bringing the File System Directory Hierarchy to the Current Point in Time
The directory changes are logged in the file (err_log)/fsysadm.log, where (err_log) is the error log directory as specified in the /etc/sylog.conf file. This file is appended to, and will not be truncated. There is one entry for each event. Setting the attributes of a file is also logged. The format of the entry is:
Time = <timestamp> EUID = <integer> UID = <integer> GID = <integer> Mode = <octal>
Action = <CREATE/REMOVE/SETATTR> Object type = <DIR/FILE> Path = <fully qualified name>
where:
Time is the time of the activity in local time.
EUID is the effective user ID of the user performing the action.
UID and GID are the user ID and group ID attributes of the file/directory
that was created, or whose attributes were modified.
Mode is the octal representation of the mode of the file/directory.
Action can be CREATE (the file/directory was created),
REMOVE (the file/directory was removed), or SETATTR (the mode
of the file/directory was modified by the user).
Object type can be DIR (directory), or FILE (file).
Path is the fully qualified path of the file/directory.
Running RECONCILE After Restoring A File System
The db2_reconcile_aid utility provides a mechanism for checking and running RECONCILE on tables that are potentially inconsistent with the DATALINK file data on the file server after a disk failure on the file server. The syntax is as follows:
db2_reconcile_aid -check -db <db_name> [-server_name <fileserver_name>]
[-reportdir <report_directory>]
where:
-check: List the tables that may need reconciliation.
No reconcile operation will be performed.
db_name: Name of the database for which the reconcile operation
needs to be performed.
fileserver_name: Name of the DLFM server for which the reconcile operation
is to be performed. If no name is provided, all
the file servers will be reconciled.
report_directory: Directory containing a report for each of the reconcile
operations. For each table on which reconcile was performed,
files of the following format will be created:
<tbcshcema>.<tbname>.<ext>,
where <tbschema> is the schema of the table,
<tbname> is the table name, and <ext> is .ulk or .exp.
The .ulk file contains a list of files
that were unlinked on the file server, and the .exp file
contains a list of files that were in exception
on the file server. For more information, see the description
of the RECONCILE command in the Command Reference.
As with any system, it is possible for a failure to occur that requires a certain level of recovery. DB2's File Manager introduces a new level of complexity to several of the recovery scenarios. This section lists several possible failure scenarios and the steps required to recover from them.
Definitions
DLFS file system - Registered prefix (/dlink) DLFM backup directory - Directory where files are backed up (/home/dlfm/dlfm_backup) DLFM home directory - Home directory of DLFM user ID (/home/dlfm) DLFM DB2 database - DB2 database that contains all meta-data (DLFM_DB) DB2 database - Registered database that contains DATALINK data type (CROWN)
Recommended Things to Do Prior to a Crash
Place the DLFM database (DLFM_DB), the DLFS file systems, the DLFM backup directory, and the DLFM home directory on different file systems. Ensure they do not share disks.
Back up the DLFS file system and the DLFM backup directory on a storage manager, such as IBM's ADSM. This will afford added protection from disk failure, in the event one occurs.
Have one database associated with one or more DB2 File Managers. Avoid having two databases associated with one DB2 File Manager; otherwise, certain recovery scenarios will become more complex than is necessary.
Perform full database backups of the DB2 databases (as well as the DLFM_DB database on the DB2 File Manager) at regular intervals.
What Can Fail?
Following are four possible failure components which require recovery:
DB2 File Manager recovery scenarios will be discussed for each of these components.
The following recovery scenarios may require operations to be performed on both the DB2 node and the DB2 File Manager node. Each step will be preceded by either "DB2:" or "DLFM:" to indicate the node on which the operation must be completed.
The DB2 database will be referred to as "crown" throughout all examples, and the DB2 table containing the DATALINK column will be referred to as "datalinktable".
Scenario #1a:
Failure:
DB2 database is lost or dropped; DB2 backup and log files are available.
Recovery Steps:
NODE OPERATION
DB2: db2 "restore database crown"
DB2: db2 "rollforward database crown to end of logs"
(End of logs is always recommended over point-in-time.)
DB2: db2 "reconcile table datalinktable"
Scenario #1b:
Failure:
DB2 database was explicitly dropped; DB2 backup and log files are available.
Recovery Steps: Ensure that the drop database operation is complete, and that all files associated with that database have been unlinked.
NODE OPERATION
DLFM: db2 "connect to dlfm_db"
DLFM: db2 "select dbid, dbname, dbinst, hostname from dfm_dbid"
DLFM: db2 "update dfm_dbid set action=5
where dbid=x'35B3D7BE0006BF7B'"
(Set dbid equal to value found in previous step.)
DB2: db2 "restore database crown"
DB2: db2 "rollforward database crown to end of logs"
(End of logs is always recommended over point-in-time.)
DB2: db2 "reconcile table datalinktable"
(Mandatory)
Scenario #2:
Failure:
DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available.
Recovery Steps:
NODE OPERATION DLFM: db2 "restore database dlfm_db" DLFM: db2 "rollforward database dlfm_db to end of logs"
Scenario #3:
Failure:
DLFM_DB is lost; backup of the DLFM_DB database is available, but not all log files are available.
Recovery Steps:
NODE OPERATION DLFM: db2 "restore database dlfm_db" DB2: db2 "reconcile table datalinktable"
Scenario #4:
Failure:
DLFS file system is lost.
Recovery Steps:
NODE OPERATION DLFM: Restore the DLFS file system from your storage manager. DB2: db2_recon_aid crown
Scenario #5:
Failure:
DLFM backup directory is lost.
Recovery Steps:
NODE OPERATION DLFM: Restore the DLFM backup directory from your storage manager.
Scenario #6:
Failure:
DLFS file system is lost; DLFM backup directory is lost.
Recovery Steps:
NODE OPERATION DLFM: Restore the DLFM backup directory from your storage manager. DLFM: Restore the DLFS file system from your storage manager. DB2: db2_recon_aid crown
Scenario #7:
Failure:
DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available. DLFS file system is lost; DLFM backup directory is lost.
Recovery Steps:
NODE OPERATION
DLFM: db2 "restore database dlfm_db"
DLFM: db2 "rollforward database dlfm_db to end of logs"
DLFM: Restore the DLFM backup directory from your storage manager.
DLFM: Restore the DLFS file system from your storage manager.
(You need the directory structure.)
DB2: db2_recon_aid crown
Scenario #8:
Failure:
DLFM_DB is lost; backup of the DLFM_DB database is available, but not all log files are available. DLFS file system is lost; DLFM backup directory is lost.
Recovery Steps:
NODE OPERATION
DLFM: db2 "restore database dlfm_db"
DLFM: Restore the DLFM backup directory from your storage manager.
DLFM: Restore the DLFS file system from your storage manager.
(You need the directory structure.)
DB2: db2_recon_aid crown
Scenario #9:
Failure:
DB2 database is lost; DLFM_DB is lost; backup, and all log files for the DLFM_DB database are available. DLFS file system is lost; DLFM backup directory is lost.
Recovery Steps:
NODE OPERATION
DLFM: db2 "restore database dlfm_db"
DLFM: db2 "rollforward database dlfm_db to end of logs"
DLFM: Restore the DLFM backup directory from your storage manager.
DLFM: Restore the DLFS file system from your storage manager.
DB2: db2 "restore database crown"
DB2: db2 "rollforward database crown to end of logs"
(End of logs is always recommended over point-in-time.)
DB2: db2 "reconcile table datalinktable"
| Note: | Rollforward to a point-in-time (PIT) may not put tables that have all DATALINK columns defined as "recovery = no" into DataLink_Reconcile_Pending (DRP) state. For all such tables, insert a row with the DATALINK value after the rollforward to a PIT, issue a SET CONSTRAINTS statement to put the table in DRP, and then run the reconcile utility. |
References to the command:
db2 add file manager <hostname> <portnum>
should be replaced by references to a new utility called db2dlcfg. The syntax of this utility is as follows:
db2dlcfg -d <database name> -a <action> [-s <server host name>] [-p <port number>] -h
where:
database name Represents the name of the database (mandatory).
action Represents the action that is to be performed (mandatory):
LIST - List all DB2 File Managers defined for this database.
ADD - Add a single DB2 File Manager for access by this database.
server host name Represents the DLFM File Manager's fully qualified host name.
port number Represents the DLFM File Manager's port number.
-h Displays help information. When this option is specified,
all other options are ignored and only the help is displayed.
For example:
db2dlcfg -d SAMPLE -a LIST db2dlcfg -d SAMPLE -a ADD -s myhost.mydomain.mycompany.com -p 10001
If moving from a previous version of DB2 File Manager, do the following:
For example:
cat datalink.cfg 1 myfs1.mydomain.mycompany.com 10001 2 myfs2.mydomain.mycompany.com 10001 4 myfs2.mydomain.mycompany.com 10001 (Note that the third one is missing.) mv datalink.cfg datalink.old db2dlcfg -d SAMPLE -a ADD -s myfs1.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s myfs2.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s nullfs.mydomain.mycompany.com -p 10001 db2dlcfg -d SAMPLE -a ADD -s myfs4.mydomain.mycompany.com -p 10001 (Note that the third one is null.)
The additional information in this section relates to the following DB2 Version 5.0 Quick Beginnings manuals:
Reference is made in the DB2 manuals to the DB2 Application Developer's Kit. This product is now available in two editions, known as the IBM DB2 Personal Developer's Edition Version 5, and the IBM DB2 Universal Developer's Edition Version 5 products. Both editions are available on OS/2, Windows NT, Windows 95, and Windows 98 only.
The updates in this section relate to software prerequisites and fixes.
In the "Server Product Requirements" section, the following operating system information should be changed for the DB2UDB Enterprise and Workgroup Edition:
From: To: -------------------- --------------------------------------------- OS/2 Warp Server SMP OS/2 Warp Server Advanced V4 with SMP Feature
A similar change must be made in the "Client Product Requirements" section for the DB2 Client Application Enabler for OS/2, the SDK and Net.Data:
From: To: -------------------- --------------------------------------------- OS/2 Warp Server SMP OS/2 Warp Server Advanced V4 with SMP Feature
Using IPX/SPX to communicate between Windows 95, Windows 98, or OS/2 clients and servers is not recommended. This connection is unreliable. Working with Novell, we have determined the cause to be at the IPX/SPX protocol stack level. The problem lies with the level of SPX I supported by Windows 95 and Windows 98, and the level of SPX I supported by OS/2. When Windows 95 or Windows 98 negotiates a connection, it specifies an SPX I level connection, but on Windows 95 or Windows 98, this includes SPX II level full-media size support. However, when OS/2 negotiates an SPX I connection, full-media size is not supported.
Novell is currently developing full-media size support for OS/2 SPX I connections, for a consistent representation of SPX I connections. This will prevent connection terminations from occurring when an unexpected packet size is sent. If you would like the status of this development, you can call Novell and ask for the status of SPD147611.
The"Netfinity Support Program" service will affect the installation of DB2 UDB EEE for Windows NT when upgrading DB2 UDB.
In order to upgrade DB2 UDB EE to DB2 UDB EEE, the "Netfinity Support Program" service should be stopped before the installation of DB2 UDB EEE is begun.
When using DB2 Connect Enterprise Edition for Windows NT and IBM Communications Server for Windows NT (CS/NT), in order to enable the canceling of host queries (SQL CANCEL), you will require DB2 UDB Version 5.2 (or DB2 UDB Extended Enterprise Edition for Windows NT), and the APAR fixes JR11529 and JR11170 for CS/NT.
AUTHENTICATION DCS can now be used with DB2 UDB DRDA-AS to permit APPC connections from DRDA clients using security SAME (no password required), while at the same time enforcing SERVER authentication (which requires a password) for all other client requests.
When using a DB2 UDB V5.0 Client Application Enabler, problems can arise when using the Control Center to attach to an Extended Enterprise Edition (EEE) partitioned database instance.
The Control Center originally provided with DB2 Connect and UDB Version 5 did not include the administrative functions required to administer partitioned database systems.
As a result, it is necessary to upgrade DB2 clients in order to perform UDB EEE administrative functions. Possible indications that this is necessary include the following:
If you have multiple logical nodes per single physical node, then the backup and restore commands are not supported from DB2 Version 2 clients.
In general, if you want to use the Control Center to administer a DB2 UDB EEE server, then you should upgrade the Client Application Enabler. The fix for this problem was first supplied in the first FixPak for DB2 UDB V5.0:
AIX U452196 Solaris U452197 HP-UX U452198 OS/2 WR09003 Windows NT WR09004 Windows 3.1 WR09006
These fixes are obtainable from: http://www.software.ibm.com/data/db2/db2tech. The correct client level can also be obtained from the client pack that is included with DB2 UDB and DB2 Connect Enterprise Edition Version 5.2.
DB2 UDB FixPaks are cumulative, so these fixes are also included FixPaks 2, 3, 4, 5, and 6, as well as in the Client Application Enabler included with DB2 UDB EEE for Windows NT, and for Solaris, Version 5.
When using ODBC applications from Windows NT and Windows 95 clients, we recommend that you upgrade the DB2 Client Application Enabler to fix pack level WR09014 or later.
This fix is obtainable from: http://www.software.ibm.com/data/db2/db2tech. The correct client level can also be obtained from the client pack that is included with DB2 UDB and DB2 Connect Enterprise Edition Version 5.2.
In an AIX partitioned database environment, after an online table space backup has been taken using ADSM, DB2 may fail if the database is restored.
The symptom is that the online restore fails, and the DB2 instance is brought down (traps) with the diagnostic message SIG11sqloEDUCodeTrap. The solution to this problem is to install the ADSM Client fix pack 2.1.0.6 or higher.
Two-phase commit using SNA is now supported on Windows NT when using the DB2 Syncpoint Manager (SPM) function together with IBM Communications Server V5.01 for Windows NT (CS/NT).
The SNA-based version of the SPM is part of the following products:
The SPM permits DB2 UDB client applications to access host data through DB2 Connect EE with SNA two-phase commit data protection provided by the SPM. Similarly, host database applications can access data on DB2 UDB servers.
After installing IBM Communications Server for Windows NT (CS/NT) you should register it as a Windows NT Service. This will automatically start CS/NT when the machine is booted.
To register CS/NT as an Windows NT Service execute the following command. Either:
csstart -a
To autostart CS/NT with the default configuration file, or:
csstart -a c:\ibmcs\private\your.acg
Where c:\ibmcs\private\your.acg is the name of the non-default CS/NT configuration file you wish to be used.
The next time your machine is rebooted CS/NT will be started automatically with the requested configuration file.
DB2 Universal Database and DB2 Connect Version 5.2 provide support for:
Two new DLLs have been provided:
By default, db2dce.grd has been copied to db2dce.dll.
| Note: | DB2 will only load db2dce.dll. You must copy dcedce.ibm to db2dce.dll if you want to use IBM DCE CDS. |
In addition to the existing support using APPC, IPX/SPX, NETBIOS, and TCP/IP, support for DCE using the Named Pipes (NPIPE) networking protocol is now provided. A control statement similar to the following example may now also be specified for a DCE object having the DB_Communication_Protocol attribute, with value:
NPIPE;Computer_name;Instance_name
For more information, see Appendix G of the Administration Guide.
For logging to raw devices with DB2 on Windows NT, Microsoft Service Pack 3 for Windows NT 4.0 is required.
In the Quick Beginnings manuals for UNIX, when discussing security issues, it is assumed that the user has not configured the SYSADM_GROUP parameter in the database manager configuration file. If the SYSADM_GROUP parameter is not configured, then the SYSADM group defaults to the primary group of the instance owner.
The Quick Beginnings manuals always refer to the primary group of the instance owner as having SYSADM authority. However, if you have configured the SYSADM_GROUP parameter, you should take the references in the manuals to refer to the SYSADM group that you have defined in the database manager configuration file.
The updates in this section relate to installation and configuration tasks.
IBM Communications Server for Windows NT Version 5.01 is required if using the SPM over SNA. The SPM is also supported over TCP/IP in DB2 Version 5.2. Chapter 22 of "DB2 Connect Enterprise Edition Quick Beginnings" book describes SPM setup.
The following additional instructions assume that you have already configured CS/NT for DB2 Connect. Refer to Chapter 14 of the DB2 Connect Enterprise Edition Quick Beginnings book.
LOCAL_LU=(LU_NAME=SPMNAME
LU_ALIAS=SPMNAME
LU_SESSION_LIMIT=0
NAU_ADDRESS=0
ROUTE_TO_CLIENT=0
SYNCPOINT_SUPPORT=0)
To verify that syncpoint support has been enabled for this LU check the "Local LU6.2" information on the CS/NT SNA Node Operations. The LU should have "Yes" listed in the "Syncpoint Support" column.
update dbm cfg using diaglevel 4
You need to set this in order to be able to view possible SPM-related diagnostic messages.
Note also that when the SPM is started for the first time a directory \sqllib\spmlog is created. If this directory does not exist, or if the diagnostics indicate that the SPM has failed to start, then you must recheck your DB2 and CS/NT configurations.
The Quick Beginnings manual for DB2 Connect Enterprise Edition will be updated to include this additional information.
The Quick Beginnings manual for DB2 Connect Enterprise Edition will be updated to include this new information.
On AIX Version 4.1.5 or higher, when a user is running Network Information System (NIS) for password authentication, the message DB21015E may be received when the command line processor times out.
The command line processor back-end program cannot recognize the user-ID of the DB2 instance from the NIS master server in this case. To correct this problem:
:!::::::
Extended SNA Security Codes were not implemented by some SNA subsystem providers. This may affect customers using one or more of the following:
A new DLL, called db2cpic2.dll has been provided in order to support
Extended SNA Security Codes in these environments. The new DLL was
originally packaged with FixPak 1 for DB2 Universal Database Version 5.
It is identical to the original DLL db2cpic.dll, except that it
includes a call to extract secondary information (cmesi()).
Table 2. Which version of db2cpic.dll to use?
| If you have this SNA subsystem | On Windows NT use | On Windows 95 use |
|---|---|---|
| IBM Communications Server for Windows NT | db2cpic2.dll (new) | db2cpic.dll (old) |
| IBM Personal Communications for Windows (see note below) | db2cpic2.dll | db2cpic.dll |
| Microsoft SNA Server | db2cpic.dll | db2cpic.dll |
| Wall Data RUMBA (also Integrated SNA Support provided with DB2 Connect Personal Edition) | db2cpic2.dll | db2cpic2.dll |
If DB2 fails to load db2cpic2.dll, you can do the following in the directory where it has been installed:
copy db2cpic2.dll db2cpic2.bak copy db2cpic.dll db2cpic2.dll
This will make db2cpic2.dll the same as db2cpic.dll.
| Note: | If you wish to use Extended Security Support with DB2 UDB Version 5.0 on Windows 95, we recommend using IBM Personal Communications (PComm) for Windows 95 Version 4.2 (not Version 4.1). Copy db2cpic.dll to db2cpic.bak and copy db2cpic2.dll to db2cpic.dll. This will make db2cpic.dll the same as db2cpic2.dll. |
In "Controlling Your DB2 Environment" in the Quick Beginnings manual for DB2 UDB Extended Enterprise Edition for Windows NT, it is incorrectly stated that "DB2NTMEMSIZE is used to override the upper limit of the DB2 shared memory segment".
Windows NT requires that all shared memory segments be reserved at DLL initialization time in order to guarantee matching addresses across processes, and a new profile variable DB2NTMEMSIZE has been introduced to permit the user to override the DB2 defaults on Windows NT if necessary. In most situations the default values should suffice.
The following is the list of memory segments and their default sizes:
Memory Segment Default Size Override Option ---------------------------------------------------------------------------- Database Kernel 16777216 (16MB) DBMS:[number of bytes] MPP FCM Buffers 33554432 (32MB) FCM:[number of bytes] Database Admin GUI 33554432 (32MB) DBAT:[number of bytes] Fenced DARIs 16777216 (16MB) APLD:[number of bytes]
More than one segment may be overridden by separating the override options with ':'.
For example, to limit DBMS to (approximately) 256K, and FCM to (approximately) 64MB, use:
db2set DB2NTMEMSIZE=DBMS:256000:FCM:64000000
On OS/2, when you resize the Performance Graph View of the Snapshot Monitor to a smaller size, the Control Center may shut down without any error message.
You cannot perform an action that either alters, changes, or deletes a database object (such as an instance or database) while you are performing snapshot monitoring on either that object, or on any it its child objects. (In addition, if you are monitoring a partitioned database system, you cannot refresh a view of partitioned database objects.) For example, you cannot monitor database A if you want to remove its instance. If, however, you are monitoring the instance only, you can alter database A.
To stop all monitoring for an instance (including any of its child objects), select "Stop all monitoring" from the pop-up menu for the instance. You should always stop monitoring from the instance, as this ensures that all locks that are held by the performance monitor are released.
Information on using Java with DB2 (JDBC for instance) is located on the internet at:
http://www.software.ibm.com/data/db2/java/
The following information should be added to the tip that is at the end of Step 1, "Prepare for Installation," in Chapter 6, "Installing DB2 Universal Database Extended Enterprise Edition," of the "IBM DB2 Universal Database Extended Enterprise Edition for AIX Quick Beginnings" book. The tip describes how to ensure that network-related parameters are set on all SP nodes.
After the note that specifies "You can use the no -a command to view the current setting for network parameters," add the following paragraph:
The above values are minimum values for the parameters. If you are already setting any of the network-related parameters to a larger value, do not set the parameter to the lower value listed above.
After the sentence that begins "To view the configuration for css0," add the following paragraph:
You should only use the following method to set the network-related parameters if you are not using the /tftpboot/tuning/cst file to tune the system.
The following information should be added to Step 8, "Setting up the Client Configuration Assistant and the Control Center" in Chapter 6, "Installing DB2 Universal Database Extended Enterprise Edition," of the "IBM DB2 Universal Database Extended Enterprise Edition for AIX Quick Beginnings" manual.
For performance reasons, it is not recommended that you set discover=SEARCH on the administration server that resides on a key catalog node or Control Center node.
There are additional ways to distribute client requests amongst different coordinator nodes. You can use the Client Configuration Assistant IMPORT/EXPORT or manual catalog functions to distribute the workload. Another alternative is to use the LoadLeveler product to distribute client requests transparently.
You cannot use the Server Communications Setup function in the Control Center to perform remote setup of nodes (database partition servers) in the partitioned database environment, if there is no administration server running on the node.
Discovery is supported using the TCP/IP protocol.
IPX/SPX file server addressing is not supported in the partitioned database environment.
In the "Verify that Databases Can Be Migrated" section, it is stated that DB2 must be stopped to prepare the instance for migration. This is incorrect. In order to successfully run the "db2ckmig" tool, the database manager must be running. In the same section, change the following:
DB2DIR/instance/db2ckmig -e -a 0 -1 INSTHOME/migration.log
to:
DB2DIR/bin/db2ckmig -e -a 0 -l INSTHOME/migration.log
In the "Migrate the DB2 Instance" section, the "db2imigr" command syntax is shown with "-u fencedID" as an optional parameter. The -u option is not required only when the DB2 Client Application Enabler product is installed; however, for all other DB2 products, the -u option is required.
Simple Network Management Protocol (SNMP) products include IBM NetView for AIX and IBM NetView for OS/2. DB2 provides support for SNMP management products via the DB2 SNMP Subagent, included as a component of the DB2 Server. SNMP management products such as NetView allow centralized management of both hardware and software components of a system. The DB2 SNMP Subagent allows DB2 servers to be managed just like any other SNMP-managed resource.
The software requirements for using the SNMP Subagent are as follows:
In order to connect properly using the IBM Communications Server SNA API Client for Windows NT or Windows 95, you must start the APING application prior to starting any GUI application that will connect via APPC. Such GUI applications include the Client Configuration Assistant, Control Center, and any ODBC GUI applications.
There is a change to a note in Table 7 within the section "User Profile Management Passwords" that refers to translated values for user IDs and passwords. The note to Table 7, "Default User IDs and Passwords" states the following:
If your country does not appear in the list, assume USERID and PASSWORD are used. If this fails, see the README file.
This note is no longer valid and should be disregarded.
When setting up ADSM Client for DB2 UDB Extended Enterprise Edition for Solaris, the DSMI_DIR, DSMI_CONFIG, and DSMI_LOG ADSM environment variables should be set up in db2profile. If these variables are merely exported using .profile or .kshrc, they will not be available to each node in the partitioned database environment. The ADSM client on Solaris must be at Version 2.1.0.8 or higher.
The following information should be added to the chapter that describes how to use the DB2 workstation as a server for host applications, in the section that lists the PTFs required for DRDA application servers:
Please apply the fix for DB2 for OS/390 APAR PQ07537 if you will be connecting from DB2 for OS/390 Version 5 to DB2 Universal Database Version 5.
NetBIOS on Windows NT does not properly receive data that is larger than the receive buffer. However, you can use the DB2 registry value db2nbrecvbuffsize to overcome this problem.
You need to set the receive buffer on the server to be larger than the largest amount of data that will be sent by a client. The default RQRIOBLK size is 32767 bytes, but the default server "receive buffer size" is 4096 bytes. Before you start DB2, issue the following command:
db2set db2nbrecvbuffsize=32768 -g
This command sets the Server's receive buffer size globally.
For Windows NT and Windows 95, in the "Managing Instances" section of the Quick Beginnings manual, the following revised information applies.
The default database manager shared memory address is x'20000000' for Windows NT, and x'90000000' for Windows 95. If a shared memory address collision occurs, this value can be modified to force the database manager instance to allocate its shared memory at a different address. Use the DB2DBMSADDR registry profile variable to assign an address value in the range of x'20000000' to x'B0000000' on Windows NT, and x'80000000' to x'B00000000' on Windows 95 using increments of x'4000000'. For example, on Windows 95, try the values: x'840000000', x'88000000', x'8C000000', and so on...
The value of DB2DBMSADDR is in hexadecimal format without the preceding 0x and may be set using the db2set profile registry command.
For example, to set DB2DBMSADDR to x'84000000', use the following command:
db2set DB2DBMSADDR=84000000
Under the heading "Running Java Applications" - "Java Applets", the Quick Beginnings and Administration Getting Started manuals say:
To run your applets, do the following:
1. Start the DB2 JDBC applet server on your Web server by entering:
db2jstrt portno
where portno is the number of the unused TCP/IP port that you
specified in the DB2Applt.java file.
This information is out of date, as follows:
The DB2 JDBC Applet Server can now be started as a Windows NT service, and it must be registered as a service first, before it is be available in the services section of the control panel.
In order to register the service, run the following command while logged on as an administrator:
db2regjdbc i
To register the service to run with a specific userid, run:
db2regjdbc i [userid] [password]
To remove the service, run:
db2regjdbc u
Once the service is registered, it can be started and stopped from the services section of the control panel:
The service can also be set to start and stop automatically.
The topic "SQL Help", which explains the syntax of SQL statements, is not available on UNIX based operating systems.
The list of language identifiers in the first note at the bottom of the table containing information about the DB2 Books should include the following entries:
Language Identifier ________ __________ Greek A Japanese J Korean K Simplified Chinese C Traditional Chinese T
If you prefer to have printed copies of the manuals, you can decompress and print PostScript versions.
| Note: | Specify the full path name for the file you intend to print. |
On OS/2 and Windows platforms:
print filename.psz
On UNIX-based platforms:
zcat filename | qprt -P PSPrinter_queue
zcat filename | lp -d PSPrinter_queue
zcat < filename | lp -d PSPrinter_queue
where filename is the name of the full path name and extension of the compressed PostScript file and PSprinter_queue is the name of the PostScript printer queue.
For example, to print the English version of Quick Beginnings for UNIX on AIX, you can use the following command:
zcat /cdrom/doc/en/ps/db2ixe50.ps.Z | qprt -P ps1
The manuals included with this product are in Hypertext Markup Language (HTML) softcopy format. Softcopy format enables you to search or browse the information, and provides hypertext links to related information. It also makes it easier to share the library across your site.
You can use any HTML Version 3.2-compliant browser to view the online books.
To view online books:
file:/INSTHOME/sqllib/doc/%L/html/index.htm
where %L is the locale name.
sqllib\doc\html\index.htm
The path is located on the drive where DB2 is installed.
You can also open the page by double-clicking on the DB2 Online Books icon. Depending on the system you are using, the icon is in the main product folder or the Windows Start menu.
| Note: | The DB2 Online Books icon is only available if you do not install the Information Center. |
By default the DB2 information is installed on your local system. This means that each person who needs access to the DB2 information must install the same files. To have the DB2 information stored in a single location, use the following instructions:
http://www.software.ibm.com/data/pubs/papers/db2html.html
For information about setting up a search, see the What's New book.
To search for information in the HTML books, you can do the following:
Currently, the default settings on Internet Explorer cause a conflict with the DB2 search server. As a workaround, disable the HTTP 1.1 settings check box from Internet Explorer's advanced options (on each client that will access the documentation). Note that due to a limitation in Internet Explorer this requires a reboot of your system to take effect.
After you install DB2 UDB EEE for Windows NT on multiple nodes, the install process will only configure the "instance owning node" to be the coordinator node for communications from client machines. In order to remotely administer the cluster using the Control Center you will need to enable communications on each of the nodes. One way to do this is as follows:
DB2 update dbm cfg using svcename 4000
rah "db2set db2comm=TCPIP"
Each node will accept inbound TCP/IP connections when it is restarted.
IBM Replication is a set of easy-to-use, automated copy tools that replicate data from sources to targets. Data can be copied:
Two messages for the Apply program are missing from Chapter 21, "IBM Replication Messages":
ASN1054S The Apply program could not find the registration information for source owner "<src_ownr>", source table "<src_tbl>", and source view qualifier "<src_view_qual>". Explanation The source table registration is incorrect or incomplete. User Response Drop the registration and redo it. Also make sure that the registration information is in both the register table and the pruning control table. ASN1055S The Apply program could not find the prune control information for source owner "<src_ownr>", source table "<src_tbl>", source view qualifier "<src_view_qual>", target owner "<tgt_ownr>", and target table "<tgt_tbl>". Explanation The source table registration is incorrect. User Response Drop the subscription and redo it.
The capture program for DB2 UDB Version 5.2 now has the capability to support LONG VARCHAR data types. New Long Field Manager long records, which contain the information needed to capture changes made to the LONG VARCHAR columns, are propagated.
For DB2 UDB Version 5 users who need the new capture capability, there is the option to add an additional clause to the ALTER TABLE statement. There are no changes to the CREATE TABLE syntax.
To take advantage of the new LONG VARCHAR support, you must specify the DATA CAPTURE CHANGES INCLUDE LONGVAR COLUMNS clause on the ALTER TABLE statement. When this DDL statement is executed, the DATA CAPTURE CHANGES column in the SYSCAT.TABLES is set to "L". Whenever base tables with the INCLUDE LONGVAR option row are updated, Long Field Manager long records are written and read by the capture program. These records include both before and after images of columns.
If you want to disable the new LONG VARCHAR support, you must specify the appropriate DATA CAPTURE CHANGES value in an ALTER TABLE statement. For example, you could specify "YES" or "NONE" without the additional clause.
To register LONG VARCHAR columns, do the following from the Control Center:
The following restriction applies: For a DELETE operation (IBMSNAP_OPERATION="D"), the LONG VARCHAR column values will be null.
VisualAge for Basic and Lotus Approach are no longer bundled in the product boxes.
The ACTIVATE NOT LOGGED INITIALLY clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are being added to the table.
Under both CREATE FUNCTION (External Scalar) and CREATE FUNCTION (External Table), there is a list of the information contained in the DBINFO structure which is passed to the UDF if the definer specifies DBINFO. The following bullet has been added to each of these lists:
Application ID - unique application ID which is established
for each connection to the database.
The NOT LOGGED INITIALLY clause should not be used when DATALINK columns with the FILE LINK CONTROL attribute are present in the table.
Replace the description of "fullselect" under "summary-table-definition":
fullselect
Defines the query on which the table is based. The summary-table-options
specified define attributes of the summary table. The option chosen also
defines the contents of the fullselect as follows.
When DEFINITION ONLY is specified, any valid fullselect can be specified.
The data type and nullability attribute are determined from the result
columns of the select list. Every select list element must have a name.
When REFRESH DEFERRED is specified, the fullselect cannot include:
o References to a view, summary table, or typed table in any FROM clause.
o Expressions that are of type LONG VARCHAR, CLOB, LONG VARGRAPHIC, DBCLOB,
BLOB, REFERENCE, or DATALINK (or a distinct type based on these types).
o Functions that have external action.
o Functions that depend on physical characteristics (NODENUMBER, PARTITION).
o Table or view references to system objects (explain tables also
should not be specified).
Under "summary-table-options", the REFRESH IMMEDIATE option is not supported.
Replace the description of "REPLICATED":
REPLICATED
Specifies that the data stored in the table is physically replicated on
each database partition of the nodegroup of the table space in which the
table is defined. This means that a copy of all the data in the table
exists on each of these database partitions. This option can only be
specified for a summary table (SQLSTATE 42997).
The limit for the number of tables in a DMS table space has been increased from 6648 to 51971. This new limit will automatically apply to any table spaces with a page size other than 4KB, and to all newly created 4KB DMS table spaces.
For existing 4KB DMS table spaces, the new /ets option of the db2dart utility is used to apply the new limit:
Notes:
The syntax diagram for the CREATE EVENT MONITOR statement shows that by default, event monitors are created with global scope. This is incorrect. Event monitors are created with local scope by default.
The note at the end of this section has been changed to:
The REFRESH IMMEDIATE option is not available in Version 5.2. It will return SQLCODE SQL0628N if used. You can use the REFRESH TABLE statement to keep replicated tables synchronized with the associated base tables.
To build and run embedded SQL for Java (SQLJ) programs now requires the Java Development Kit (JDK) Version 1.1.4 (or higher).
There have been several changes to the Java sample programs documented in this appendix. The stored procedure and UDF programs have been split into separate client and server programs. This allows users on client machines to remotely access stored procedures and UDFs on server machines. The programs affected are as follows:
The script file embprep on UNIX platforms, embprep.bat on Windows platforms, and embprep.cmd on OS/2, has been created to precompile and bind the SQLJ programs. It is called by the makefile and executes the db2profc command on the Java embedded SQL sample programs. It allows optional arguments for database, user ID and password, thereby facilitating remote binding to databases on DB2 servers from client machines.
Following are additional limitations on SQLJ support for DB2 UDB Version 5.2:
profile name default package name
--------------------- --------------------
App_SJProfile1 App_SJP1
App_SJProfile123 App_S123
App_SJProfile1234567 A1234567
App_SJProfile12345678 A2345678
Some browsers do not yet have support for loading a serialized object from a resource file associated with the applet. You will get the following error message when trying to load the applet Applt in those browsers:
java.lang.ClassNotFoundException: Applt_SJProfile0
As a work-around, there is a utility which converts a serialized profile into a profile stored in Java class format. The utility is a Java class called sqlj.runtime.profile.util.SerProfileToClass. It takes a serialized profile resource file as input and produces a Java class containing the profile as output. Your profile can be converted using the following command:
profconv Applt_SJProfile0.ser
or
java sqlj.runtime.profile.util.SerProfileToClass Applt_SJProfile0.ser
The class Applt_SJProfile0.class is created as a result. Replace all profiles in .ser format used by the applet with profiles in .class format.
For an SQLJ applet, you need both db2java.zip and runtime.zip files. If you choose not to package all your applet classes, classes in db2java.zip and runtime.zip into a single Jar file, put both db2java.zip and runtime.zip (separated by a comma) into the archive parameter in the "applet" tag. For those browsers that do not support multiple zip files in the archive tag, specify db2java.zip in the archive tag, and unzip runtime.zip with your applet classes in a working directory that is accessible to your web browser.
The instructions for enabling remote documentation searches on UNIX operating systems are incorrect. Correct instructions can be found on the web at:
http://www.software.ibm.com/data/pubs/papers/db2html.html
The published example of the "nqmap" command for OS/2 and the Windows operating system should be changed to:
nqmap -u DB2ADMEN "http://yourserver/doc/html/" DB2ADMEN
The instructions for editing your Start HTML Search Server icon's properties on the OS/2 operating system should read:
1. Right-click on the Start HTML Search Server icon in your DB2 for OS/2 folder.
Select Settings and click on the Program tab. In the Optional Parameters field,
add CONFIG DB2WEBCC.CNF to the end of the string of parameters. Click OK to close.
2. Edit your db2init.cmd file. This file is located in x:\sqllib\bin\, where x: is
the drive on which you installed DB2. Find the line in this file containing both
"DB2SS.EXE" and "DB2NETQD.EXE". At the end of this line add CONFIG "DB2WEBCC.CNF".
(Note that you need double quotation marks around the file name). Save and exit.
Continue with the remainder of the instructions for enabling remote documentation
searches.
If you want to search all the DB2 online information, you must install both the DB2 documentation (referred to as the "Product Library" on UNIX systems) and the Web Control Center with its online help. If you install the Web Control Center and its help, but do not install the rest of the DB2 online documentation, then attempting to select the search categories for administration, programming, and DB2 Connect may produce an error code of 24, indicating that those search indexes are not installed. In the same way, if you install the DB2 online documentation but not the Web Control Center, attempting to search the Web Control Center online help may produce this same error code.
The following are restrictions of the Control Center:
IBM DB2 Universal Database (DB2 UDB) online documentation comes with an HTML search server to help you find information. These release notes describe the setup and use of the search system on OS/2.
You must have TCP/IP Version 3 or higher installed on your machine. OS/2 Warp V4 is recommended because it has the appropriate level of TCP/IP. OS/2 Warp V3 Internet Access Kit (IAK) will also work if you set it up for local loopback and if you install the latest FixPak.
You need a browser such as Netscape 2.02 for OS/2. If a Netscape browser is not available for your language, use Web Explorer 1.1 or higher. Ensure you turn off proxy handling for localhost in the browser you use.
If you are installing DB2 UDB on a system that has VisualAge for C++ for OS/2, you need to have CSD6 or above installed for the VisualAge for C++ product.
The search server will function with or without a network adapter installed as long as TCP/IP local loopback and localhost are enabled on your system.
To enable local loopback:
To enable localhost on your system:
ifconfig lo 127.0.0.1
| Note: | If you have OS/2 Warp with Internet Access Kit, add the line to the \STARTUP.CMD instead of the MPTN\BIN\SETUP.CMD file. If the file doesn't exist, you will have to create it. |
| Note: | If you have a hostname for your machine on the "Configure LAN Name Resolution Services" page, you must add this name as an alias when you set the IP Address 127.0.0.1 to localhost. |
| Note: | This step tells OS/2 that when it is looking for a host, such as localhost, it should use the host address found on your machine rather than checking the nameserver. If the host is not defined on your machine, OS/2 continues looking for the host by using the nameserver you configured. |
Click the "Start HTML Search Server" icon, which is located in the DB2 for OS/2 folder. If an error message appears, TCP/IP is not configured properly. Follow the instructions in 4.1.2 , Configuring TCP/IP on OS/2 to make sure that TCP/IP is correctly configured. Reboot OS/2 if you change any settings.
The search system is stored in its own directory because it may be used by other products. For example, if you installed DB2 UDB along with the search system on drive G, and later you installed VisualAge for Java on drive H, only one search system is installed -- the one that came first.
Some instructions in this document require you to specify the location of this directory. To locate this directory enter this command on an OS/2 command line:
echo %IMNNLPSSRV%
The search server is assigned to port 49213, a number beyond the public ports assigned for TCP/IP. If you have another product that uses this port, you can change the search server port with the following steps:
<form action="http://localhost:49213/cgi-bin/db2sr*xx.exe" method="POST">
where:
* is D for DBCS and S for SBCS
xx is a two-character identifier for the language in which the
documentation is written
49213 is the new port for localhost
If you received error messages while installing the search server, enter the following command from an OS/2 command line and follow the instructions that it returns:
SNIFFLE /P
Make sure you reboot your system after you're done.
After you install the search system, it will start automatically after you reboot.
If the search server starts without any errors but you encounter the following error message in your browser, click the "Stop HTML Search Server" icon, then click the "Start HTML Search Server" icon.
A network error occurred: unable to connect to server. The server may be down or unreachable. Try connecting again later.
After you have finished searching, stop the search server to reclaim the memory it used. To stop it, double-click the "Stop HTML Search Server" icon in the DB2 for OS/2 folder.
If you are not on a network (for example, if you are using a laptop computer and are temporarily away from a LAN connection), you have to enable localhost to search the documentation. The instructions for doing this task are described in step 3 of 4.1.2 , Configuring TCP/IP on OS/2.
The search system searches predefined indexes. All of these indexes are installed with the search system, regardless of the DB2 UDB products you install. Therefore, when you conduct a search, some of the results might return a "File not found" error because they pertain to a product that is not installed on your system.
If you installed the search server but you get an error when you try to search the documentation, check the following items:
NQMAP -A
The documentation for DB2 UDB is called DB2ADMxx, DB2APDxx, or DB2CONxx, where xx is a two-character identifier of the language in which the documents are written. One or more of these names should appear in the list of names that NQMAP returns.
If the files are not there, either TCP/IP is incorrectly installed or the indexes are corrupt. Use SNIFFLE /P to fix the former, and reinstall the documentation to fix the latter.
If you encounter this error:
HS0410: An error was detected when starting the search service. Stop the service and start it again. Press Enter to continue... (Sometimes the error is EHS0411 but the text is the same. This message comes up on a full black screen.)
your hostname might have changed since doing the install and you have to record it again. Go into the NetQuestion directory and enter:
type netq.cfg
to see what hostname NetQuestion is using. If you have changed the hostname, for example while configuring DHCP and DDNS, enter:
netqinit <data-path> Example: netqinit d:\netqos2\data
After you uninstall DB2 for OS/2, the search system is left on your hard drive. Run UNINSTNQ.CMD to remove it.
If you ran UNINSTNQ.CMD and the search system does not uninstall, the cause may be:
NQMAP -A
If this command returns no indexes, skip to step 6.
If the list contains indexes that do not belong to DB2 UDB (that is, their names begin with something other than "DB2"), you cannot remove the search server.
If it contains any of the DB2 index file names (DB2ADMxx, DB2APDxx, DB2CONxx), DB2 UDB could not unregister the indexes therefore causing the search system uninstall to fail. This occurs if DB2 UDB was incorrectly uninstalled (for example, the SQLLIB folder was deleted). In this case, you have to manually unregister the indexes and remove the search system directory using the following steps:
NETQ START SERVER //for SBCS IMQSS.EXE -START DBCSHELP //for DBCS
NQMAP -D <index_name>
NQDELET <index_name> //for SBCS TMDELET <index_name> //for DBCS
NETQ STOP SERVER //for SBCS IMQSS.EXE -STOP DBCSHELP //for DBCS
NQCOUNTI <search system directory> //for SBCS TMCOUNTI <search system directory> //for DBCS
| Note: | See 4.1.4 , Locating the Search System Directory if you do not know where it is installed. |
| Note: | Install creates numbered backups, of the form config.xyz, where xyz is the first available number from 000 to 100. The backups will have the time and date stamp of the time of the install. There were two backups created during install: one for the DB2 UDB install, one for the search system install. |
IBM DB2 Universal Database (DB2 UDB) online documentation comes with an HTML search system to help you find information. These release notes describe the setup and use of the search system on Windows NT, Windows 95, and Windows 98.
The search system consists of a search engine and a search server. The search system is stored in its own directory because it may be used by other products.
This search system does not support Windows 3.1.
This search system is not enabled for Thin Clients. If you attempt to search the documentation, you will receive a network error.
The following prerequisites are required:
For Windows 95, TCP/IP must be enabled as follows:
| Note: | These configuration options will apply to all TCP/IP adapters even though they have only been changed for this one. You will not be able to use both LAN and Dial-Up without reconfiguring. |
Dial-Up networking TCP/IP properties for your internet service provider(s) (ISP) must be configured as documented by the ISP. The Dial-Up networking TCP/IP properties will override the properties in the Dial-Up Adapter TCP/IP properties configured via the "Network" icon in the Windows 95 Control Panel. The overriding of the properties will only take place so long as the Dial-Up Adapter TCP/IP properties are configured as above. Enabling the DNS in the Dial-Up Adapter TCP/IP properties or setting an IP address in the Dial-Up Adapter TCP/IP properties will interfere with the Dial-Up networking configuration for the ISP and must be avoided.
For Windows NT 4.0, either of the TCP/IP configurations detailed above will work. If you are running standalone, you can also enable the MS Loopback Adapter without the other two adapters.
If the search system was previously installed by another product (for example, VisualAge for Java), the search server must be stopped. To stop the search server, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu.
The search system is stored in its own directory because it may be used by other products. For example, if you installed DB2 UDB along with the search system on drive G, and later you installed VisualAge for Java on drive H, only one search system is installed -- the one that came first.
Some instructions in this document require you to specify the location of this directory. To locate this directory use this command:
echo %IMNINSTSRV%
The search server is assigned to port 49213, a number beyond the public ports assigned for TCP/IP. If you have another product that uses this port, you can change the search server port with the following steps:
DB2SET DB2PATH
<form action="http://localhost:49213/cgi-bin/db2sr*xx.exe" method="POST">
where:
* is D for DBCS and S for SBCS
xx is a two-character identifier for the language in which the
documentation is written
49213 is the new port for localhost
If you install DB2 UDB on a LAN-connected drive on Windows 95 and the drive does not get reconnected before AUTOEXEC.BAT is run on Windows 95, the search system will not be able to have its environment variables set. The search system sets environment variables through a batch file (IMNENV.BAT) that resides in the search system directory (for example, (F:\IMNNQ_95). To bypass this problem, copy IMNENV.BAT from the search system directory to another drive/directory that is connected before AUTOEXEC.BAT is executed. Then modify AUTOEXEC.BAT to call this batch file on startup. For example, if you copy IMNENV.BAT to C:\WINDOWS\IMNNQ, you can add the following line to AUTOEXEC.BAT:
IF EXIST C:\WINDOWS\IMNNQ\IMNENV.BAT CALL IMNENV.BAT
If during DB2 UDB install you encounter a failed search server installation or initialization, the DB2 UDB install will proceed to completion. The following will assist in diagnosing what happened and what to do.
Look in the <temp>\imnnq\install directory, where <temp> is your system's %TEMP% directory, to find the IMNNQ.ERR file. If it does not exist, reboot and try installing the product again. If IMNNQ.ERR does exist, here is the possible contents:
1 - This indicates that the current PATH is too long and adding the search
server into the PATH will cause the entire PATH to be erased.
Note: The limit on Windows NT 4.0 is 512; on Windows 95 it is 255.
It is recommended that you perform the following steps:
a) Rename the PATH variable in the AUTOEXEC.BAT file (PATHGOOD), save
changes and reboot.
b) Remove the IMNNQ.ERR file from <temp>\imnnq\install.
c) Run the product install again to install the search system properly.
d) Merge the PATHGOOD variable with the PATH variable that was created
by the latest installation.
2 - Miscellaneous error, please contact IBM service.
3 - Out of disk space error. Please ensure that there is at least 4.5MB
of disk space for the search system plus enough space for AUTOEXEC.BAT
to be changed for Windows 95.
If IMNNQ.ERR contains a message saying XXX.EXE DOES NOT EXIST, the search system executables could not be found. Try rerunning the search system install/initialization program again. See 5.3.4 , If Search Does Not Work (ERROR 500 when trying to search).
After you install the search system, it will start automatically after you reboot.
If the search server starts without any errors but you encounter the following error message in your browser, click "Stop HTML Search Server", then click on "Start HTML Search Server" in the DB2 menu item of the Start Menu.
A network error occurred: unable to connect to server. The server may be down or unreachable. Try connecting again later.
After you have finished searching, stop the search server to reclaim the memory it used. To stop it, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu.
If you use Netscape or Internet Explorer with proxies enabled manually, you can speed up search significantly by modifying your proxy information.
In Netscape 3:
--------------
1. Select Options - Network Preferences.
2. Click the Proxies tab.
3. Click View at the Manual Proxy Configuration selection.
4. In the "No proxies for" box, type:
localhost:49213
If you have other entries here, separate them with commas.
5. Click OK to close the Manual Proxy Configuration window.
6. Click OK to exit the Preferences Window.
In Netscape 4 (Communicator):
-----------------------------
1. Select Edit - Preferences.
2. Double-click Advanced in the Category tree.
3. Click Proxies in the Advanced subtree.
4. Click View at the Manual Proxy Configuration selection.
5. In the "Exceptions... Do not use proxy servers for domains beginning
with" box, type:
localhost:49213
If you have other entries here, separate them with commas.
6. Click OK to close the Manual Proxy Configuration window.
7. Click OK to exit the Preferences Window.
In Internet Explorer 3:
-----------------------
1. Select View - Options.
2. Select Connection.
3. In the "Exceptions... Do not use proxy servers for domains beginning
with" box, type:
localhost:49213
If you have other entries here, separate them with commas.
4. Select the "Do not use proxy server for local (intranet) addresses" box.
5. Click OK to exit the Options Window.
In Internet Explorer 4:
-----------------------
1. Select View... Internet Options.
2. Select the Connection tab.
3. Select the "Bypass proxy server for local (Intranet) access" check box.
NOTE: this check box is only available if you are using a proxy or socks connection
and have selected the "Access the Internet Using a Proxy Server" check box
4. Select the "Advanced" Button.
5. Type:
localhost:49213
in the "Exceptions... Do not use proxy servers for addresses beginning with" box.
If you have other entries here, separate the new entry with a semi-colon.
6. Select OK, then OK to exit the Options Tab.
Nameservers:
If you use a laptop that is normally connected to a LAN as a disconnected development platform, you may run into difficulty searching. To search successfully, you need to disable your nameserver, IP address in your TCP/IP configuration. In other words, you need two separate TCP/IP configurations--one for connected operations and another for disconnected ones.
Best fix for multiple TCP/IP configurations in Windows 95:
Windows 95 only allows you to have a single TCP/IP configuration. However, there are shareware utilities available on the internet that let you specify more than one setting, and then change them according to your connection status (connected or disconnected from a LAN). TCPSwitch is one of these programs.
Socks servers and proxies:
If you are using Netscape 3, and you dial-in to a secure site to access the internet with a proxy or socks server, you will need to delete these settings in Netscape before you can search. This is a bug with Netscape 3--Netscape Communicator's "direct connect" setting should fix this.
If the product installation worked, but searching does not work, try the following steps:
NQMAP -A //for SBCS TMMAP -A //for DBCS
The documentation for DB2 UDB is called DB2ADMxx, DB2APDxx, or DB2CONxx, where xx is a two-character identifier of the language in which the documents are written. One or more of these names should appear in the list of names that NQMAP (or TMMAP) returns.
With the list of indexes the NQMAP (or TMMAP) command provides, you can get additional details on each index with the following command:
IMNIXSTA <index_name> //for SBCS IMQIXSTA <index_name> //for SBCS
This command provides details such as the status of the index and the number of documents in the index.
If any of the above conditions are not true, you can rerun the DB2 UDB product installation program. If the only condition that is not true is that the files DB2SRCH.EXE, DB2HEAD.HTM and DB2FOOT.HTM are missing, then you can just copy them over from the directory <db2path>\misc into search system's directory, (for example, E:\IMNNQ_NT). The product installation program will rerun the search server's installation and initialization.
The search system searches predefined indexes. All of these indexes are installed with the search system, regardless of the DB2 UDB products you install. Therefore, when you conduct a search, some of the results might return a "File not found" error because they pertain to either a product that is not installed on your system, or, a document that you chose not to install during installation.
If you encounter this error:
EHS0410: An error was detected when starting the search service. Stop the service and start it again. Press Enter to continue... (Sometimes the error is EHS0411 but the text is the same. This message comes up on a full black screen.)
your hostname might have changed since doing the install, and you have to record it again. Go into the NetQuestion directory and enter:
type netq.cfg
to see what hostname NetQuestion is using. If you have changed the hostname, for example while configuring DHCP and DDNS, enter:
netqinit <data-path> Example: netqinit c:\imnnq_nt\data
| Note: | Before uninstalling the DB2 UDB product, the search system must be stopped. To stop the search system, click on "Stop HTML Search Server" in the DB2 menu item of the Start Menu. |
The search system is uninstalled with the DB2 UDB product by clicking on the "Uninstall" item in the DB2 menu item of the Start Menu.
If the search system does not get uninstalled, the cause may be:
To determine which products are still registered with the search system, issue the following command:
NQMAP -A //for SBCS TMMAP -A //for DBCS
If this command returns no indexes, skip to step 6.
If the list contains indexes that do not belong to DB2 UDB (that is, their names begin with something other than "DB2"), you cannot remove the search server.
If it contains any of the DB2 index file names (DB2ADMxx, DB2APDxx, DB2CONxx), DB2 UDB could not unregister the indexes therefore causing the search system uninstall to fail. This occurs if DB2 UDB was incorrectly uninstalled (for example, the SQLLIB folder was deleted). In this case, you have to manually unregister the indexes and remove the search system directory using the following steps:
IMNSS START SERVER //for SBCS IMQSS.EXE -START DBCSHELP //for DBCS
NQMAP -D <index_name> //for SBCS TMMAP -D <index_name> //for DBCS
NQDELET <index_name> //for SBCS TMDELET <index_name> //for DBCS
IMNSS STOP SERVER //for SBCS IMQSS.EXE -STOP DBCSHELP //for DBCS
NQCOUNTI <search system directory> //for SBCS TMCOUNTI <search system directory> //for DBCS
| Note: | See 5.2.3 , Locating the Search System Directory if you do not know where it is installed. |
\\HKEY_LOCAL_MACHINE\SOFTWARE\IBM\NetQuestion.
Remove this entry including all its subtrees.
After a DB2 UDB uninstall, it is very important to reboot before doing another install. The reason is that some search system DLLs can be held by the operating system and are not removed until the next reboot. If a a search system install happens before the reboot, the newly installed search system DLLs will be deleted on the next reboot, rendering the search system unusable.
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