8.6.5 Notes on version upgrading
This section provides cautionary notes that apply when upgrading the HADB server version.
- Organization of this subsection
(3) Retrieval results of viewed tables defined in an earlier version
(5) Re-creation of viewed tables in the event of a version upgrade
(6) Checking the viewed tables that might not be re-creatable
(7) Re-creation of a viewed table (if the version is upgraded from a version earlier than 05-01)
(8) Notes about the JDBC driver (if the version is upgraded from a version earlier than 05-01)
(1) Collecting cost information again
The cost information that was collected by using an HADB server whose version is earlier than 04-03 cannot be used with an HADB server whose version is 04-03 or later. Therefore, if you upgrade the version of the HADB server from a version earlier than 04-03 to version 04-03 or later, use the adbgetcst command to collect the cost information again.
(2) Results of value expressions and set operations
When an HADB server is upgraded from a version earlier than 03-00, the data type and data length of the results of the value expressions and set options described below, as well as the result values, change after the version upgrade. The following two tables show the items whose results change after a version upgrade.
|
No. |
Items for which the data type and data length of the results change |
Explanation |
|
|---|---|---|---|
|
1 |
Set functions |
PERCENTILE_CONT |
The data type of the result changes from aggregated argument to DOUBLE PRECISION. |
|
2 |
MEDIAN |
||
|
3 |
0-byte literal |
Changes from CHAR(0) to VARCHAR(1) data with an actual length of 0. |
|
|
4 |
CASE expression |
If the data type of the result is character string data, the data type of the result changes to VARCHAR. |
|
|
5 |
Scalar functions |
COALESCE |
If the data type of the result is character string data, the data type of the result changes to VARCHAR. |
|
6 |
DECODE |
||
|
7 |
GREATEST |
||
|
8 |
LEAST |
||
|
9 |
NULLIF |
||
|
10 |
CEIL |
If the target data is DECIMAL, the data type of the result changes from DECIMAL (precision of target data, scaling of target data) to DECIMAL (precision of target data, 0). |
|
|
11 |
FLOOR |
||
|
12 |
ROUND |
If the target data is DECIMAL (excludes a case in which precision is 38), the data type of the result changes from DECIMAL (precision of target data, scaling of target data) to DECIMAL (precision of target data + 1, scaling of target data). |
|
|
13 |
Set operations |
UNION |
The data type of any column that is derived as a result of a set operation changes. If the data types of the columns corresponding to the columns derived in a result of each query primary are all CHAR, and their maximum lengths are equal, the data type of the result becomes CHAR. In all other cases, the data type of the result becomes VARCHAR. |
|
14 |
UNION ALL |
||
|
No. |
Items whose result value changes |
Explanation |
|
|---|---|---|---|
|
1 |
Scalar functions |
CAST |
If the post-conversion length is longer than the data length of the result, no error occurs and the decimal portion is discarded (rounded to the nearest even number). |
|
2 |
CONVERT (no number format specified, conversion from the DOUBLE PRECISION data type to character string data) |
||
|
3 |
SUBSTR |
For the following items, data with an actual length of 0 bytes is returned instead of the null value:
For the following item, no error occurs and 1 is treated as the start position:
For the following item, data that can be extracted is returned instead of the null value:
|
|
|
4 |
LEFT |
||
|
5 |
RIGHT |
||
|
6 |
TRIM |
For the following items, data with an actual length of 0 bytes is returned instead of the null value:
|
|
|
7 |
LTRIM |
||
|
8 |
RTRIM |
||
(3) Retrieval results of viewed tables defined in an earlier version
If any of the following items are present in the viewed tables defined in an HADB server whose version is earlier than 03-00, the data type and data length of the result is determined according to the description in (2) Results of value expressions and set operations.
After that, the scalar function CONVERT is executed to match the data type and data length to those determined in the HADB server whose version is earlier than 03-00.
Targeted items
-
Set functions
-
PERCENTILE_CONT
-
MEDIAN
-
-
0-byte literal
-
CASE expression
-
Scalar functions
-
COALESCE
-
CEIL
-
DECODE
-
FLOOR
-
GREATEST
-
LEAST
-
NULLIF
-
ROUND (mathematical function)
-
(4) Changing to a system that uses the multi-node function
During a version upgrade, even if you want to change the system to one that uses the multi-node function, you cannot perform the upgrade and change the system to one that uses the multi-node function at the same time. If you specify the operand for the multi-node function in the server definition before a version upgrade, the upgrade fails (the KFAA50038-E message is output). Therefore, first perform the version upgrade, and then change the system to one that uses the multi-node function.
For details about the action to take when the KFAA50038-E message is output, see (c) If variable aa....aa is "of using the multiple node facility" in (3) Steps to take when the KFAA50038-E message is output under 8.6.4 Steps to take when version upgrading fails.
(5) Re-creation of viewed tables in the event of a version upgrade
When you upgrade the HADB server to version 05-01, viewed tables that satisfy all of the following conditions are re-created as part of the upgrade process. Viewed tables whose re-creation failed are invalidated.
- ■ Conditions for re-creation of viewed tables as part of a version upgrade
-
Viewed tables that satisfy all of the following conditions will be re-created:
-
Viewed tables that depend on a dictionary table or system table that is modified by the version upgrade process
For details about how to find out which viewed tables depend on a dictionary table or system table, see (33) Finding a list of dependent viewed tables in B.22 Searching a dictionary table.
For details about which dictionary tables and system tables are modified as part of the version upgrade process, see Table 8‒28: Dictionary tables and system tables modified during version upgrade (1) and Table 8‒29: Dictionary tables and system tables modified during version upgrade (2).
-
Viewed tables that are valid at the time of the version upgrade (viewed tables that have not been invalidated)
For details about how to find out whether a viewed table is valid, see (22) When checking whether a viewed table has been invalidated under B.22 Searching a dictionary table.
-
If viewed tables are successfully re-created during the version upgrade process, the message KFAA51311-I is output.
If re-creating the viewed tables fails during the version upgrade process, the message KFAA51312-W is output. Viewed tables whose re-creation failed are invalidated. If this occurs, the version upgrade processing continues.
If the message KFAA51312-W is output, wait until the version upgrade is complete and then check the message that was output immediately before KFAA51312-W. Based on the contents of the message, resolve the issue that caused re-creation of the viewed table to fail. Then, re-create the viewed table. For details, see (6) When rebuilding of viewed tables at version upgrade fails under 11.2.8 Releasing a viewed table from invalidation.
The following two tables list the dictionary tables and system tables that are modified when you upgrade the HADB server to version 05-01.
|
No. |
Dictionary table or system table name |
HADB server from which version upgrade is being performed |
|||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
|
02-00 |
02-01 |
02-02 |
03-00 |
03-01 |
03-02 |
03-03 |
03-04 |
03-05 |
03-06 |
||
|
1 |
SQL_TABLES |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
2 |
SQL_COLUMNS |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
3 |
SQL_INDEXES |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
4 |
SQL_DIV_TABLE |
-- |
Y |
Y |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
5 |
SQL_DIV_INDEX |
-- |
Y |
Y |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
6 |
SQL_DBAREAS |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
7 |
SQL_SCHEMATA |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
8 |
SQL_VIEWS |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
-- |
|
9 |
SQL_VIEW_TABLE_USAGE |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
10 |
SQL_DEFINE_SOURCE |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
-- |
|
11 |
SQL_DEFINE_ENVIRONMENT |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
-- |
|
12 |
SQL_USERS |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
13 |
SQL_TABLE_CONSTRAINTS |
-- |
-- |
Y |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
14 |
SQL_INDEX_COLINF |
-- |
-- |
Y |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
15 |
SQL_KEY_COLUMN_USAGE |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
16 |
SQL_REFERENTIAL_CONSTRAINTS |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
17 |
SQL_TABLE_PRIVILEGES |
-- |
-- |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
-- |
|
18 |
SQL_AUDITS |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
19 |
STATUS_TABLES |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
20 |
STATUS_INDEXES |
-- |
Y |
Y |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
21 |
STATUS_CHUNKS |
-- |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
22 |
STATUS_SYNONYM_DICTIONARIES |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
|
23 |
STATUS_COLUMNS |
-- |
-- |
-- |
-- |
-- |
-- |
-- |
Y |
Y |
Y |
- Legend:
-
Y: The dictionary table or system table is modified as part of the version upgrade process.
--: The dictionary table or system table is not modified as part of the version upgrade process.
|
No. |
Dictionary table or system table name |
HADB server from which version upgrade is being performed |
|||
|---|---|---|---|---|---|
|
04-00 |
04-01 |
04-02 |
04-03 or 05-00 |
||
|
1 |
SQL_TABLES |
Y |
-- |
-- |
-- |
|
2 |
SQL_COLUMNS |
Y |
-- |
-- |
-- |
|
3 |
SQL_INDEXES |
-- |
-- |
-- |
-- |
|
4 |
SQL_DIV_TABLE |
-- |
-- |
-- |
-- |
|
5 |
SQL_DIV_INDEX |
-- |
-- |
-- |
-- |
|
6 |
SQL_DBAREAS |
-- |
-- |
-- |
-- |
|
7 |
SQL_SCHEMATA |
-- |
-- |
-- |
-- |
|
8 |
SQL_VIEWS |
-- |
-- |
-- |
-- |
|
9 |
SQL_VIEW_TABLE_USAGE |
-- |
-- |
-- |
-- |
|
10 |
SQL_DEFINE_SOURCE |
-- |
-- |
-- |
-- |
|
11 |
SQL_DEFINE_ENVIRONMENT |
-- |
-- |
-- |
-- |
|
12 |
SQL_USERS |
-- |
-- |
-- |
-- |
|
13 |
SQL_TABLE_CONSTRAINTS |
-- |
-- |
-- |
-- |
|
14 |
SQL_INDEX_COLINF |
-- |
-- |
-- |
-- |
|
15 |
SQL_KEY_COLUMN_USAGE |
-- |
-- |
-- |
-- |
|
16 |
SQL_REFERENTIAL_CONSTRAINTS |
-- |
-- |
-- |
-- |
|
17 |
SQL_TABLE_PRIVILEGES |
-- |
-- |
-- |
-- |
|
18 |
SQL_AUDITS |
-- |
-- |
-- |
-- |
|
19 |
STATUS_TABLES |
Y |
Y |
Y |
-- |
|
20 |
STATUS_INDEXES |
-- |
-- |
-- |
-- |
|
21 |
STATUS_CHUNKS |
Y |
-- |
-- |
-- |
|
22 |
STATUS_SYNONYM_DICTIONARIES |
-- |
-- |
-- |
-- |
|
23 |
STATUS_COLUMNS |
Y |
Y |
Y |
-- |
- Legend:
-
Y: The dictionary table or system table is modified as part of the version upgrade process.
--: The dictionary table or system table is not modified as part of the version upgrade process.
(6) Checking the viewed tables that might not be re-creatable
The ALTER VIEW statement might be unable to re-create a viewed table that was defined by using an HADB server whose version is earlier than 05-00 if the viewed table satisfies all the conditions shown in ■ Conditions under which a viewed table cannot be re-created.
- ■ Conditions under which a viewed table cannot be re-created
-
-
A derived table is specified in the CREATE VIEW statement.
-
No derived column list is specified for the derived table in Condition 1.
-
The selection expression in the derived query for the derived table in Condition 1 includes at least one occurrence of each of the following specifications:
-
The AS clause includes either of the following items: a column specification that uses EXPnnnn_NO_NAME as a column name, or a value expression that uses EXPnnnn_NO_NAME as a column name.
The nnnn portion is an unsigned integer in the range from 0001 to 1000.
-
A value expression that is not a column specification and does not include the AS clause
-
-
For a viewed table that cannot be re-created by using the ALTER VIEW statement, use the DROP VIEW statement to delete the viewed table, and then use the CREATE VIEW statement to redefine a viewed table. The following shows the procedure.
- ■ Procedure for redefining a viewed table
-
-
Check for viewed tables that cannot be re-created.
Check whether the viewed tables that were defined by an HADB server whose version is earlier than 05-00 include viewed tables that satisfy the conditions under which a viewed table cannot be re-created.
-
Modify the CREATE VIEW statements for the viewed tables.
You can check the CREATE VIEW statements for viewed tables in the DEFINE_SOURCE column of the dictionary table SQL_DEFINE_SOURCE. Before you check the DEFINE_SOURCE column, see (31) Finding out viewed table definition information in B.22 Searching a dictionary table.
Use either of the following methods to modify the CREATE VIEW statements for the viewed tables that you checked in step 1:
-
Change the column name EXPnnnn_NO_NAME specified in the selection expression in the derived query for the derived table.
-
Add the AS clause to the selection expression in the derived query or add a derived column list to the derived table to prevent the name of the derived column from being EXPnnnn_NO_NAME.
-
-
Delete the viewed table.
Use the DROP VIEW statement to delete the viewed tables that you checked in step 1.
-
Redefine the viewed tables.
Redefine the viewed tables by executing the CREATE VIEW statement that you modified in step 2.
-
Re-create the viewed tables that have been disabled.
If viewed tables that were deleted in step 3 are disabled, use the ALTER VIEW statement to re-create viewed tables in ascending order of view level.
-
(7) Re-creation of a viewed table (if the version is upgraded from a version earlier than 05-01)
The number of conditions for expanding the internal derived table for a query name was increased in version 05-01. However, the added conditions are not applied to viewed tables that were defined in a version earlier than 05-01. Therefore, to apply the conditions added in version 05-01 to such viewed tables, re-create the viewed tables by using the following procedure:
Procedure
-
Confirm the relevant viewed tables.
Viewed tables that satisfy all of the following conditions are subject to re-creation:
-
Viewed table for which the WITH clause is specified
-
Viewed table defined in a version earlier than 05-01
To confirm the CREATE VIEW statements that were used when viewed tables were defined, see the DEFINE_SOURCE column of the dictionary table SQL_DEFINE_SOURCE. To confirm the versions in which viewed tables were defined, see the DEFINE_VR column of the dictionary table SQL_DEFINE_ENVIRONMENT. For details about how to confirm these with the DEFINE_SOURCE column and the DEFINE_VR column, see (31) Finding out viewed table definition information in B.22 Searching a dictionary table.
-
-
Re-create the relevant viewed tables.
Use the ALTER VIEW statement to re-create the relevant viewed tables in ascending order of view levels.
-
Re-create the viewed tables that have been disabled.
If there are viewed tables that were disabled by the ALTER VIEW statement executed in step 2, use the ALTER VIEW statement to re-create them in ascending order of view levels.
(8) Notes about the JDBC driver (if the version is upgraded from a version earlier than 05-01)
Provision of the JDBC driver for JRE 6 or 7 (adbjdbc.jar) was discontinued in version 05-01. If the JDBC driver for JRE 6 or 7 is used, replace it by the JDBC driver for JRE 8 (adbjdbc8.jar). To change the JDBC driver to be used, change the JAR file that is referenced by the application programs (from adbjdbc.jar to adbjdbc8.jar). Changing adbjdbc.jar to adbjdbc8.jar does not affect the application programs. Note that, to use adbjdbc8.jar, Java 8 or later is required.