preface
When creating tables and other objects in Dameng database, you may encounter creation failure and error "syntax analysis error" when using some words in the object name. This situation may be that the object name used is the system reserved word and cannot be used. In this case, it is generally recommended to modify the object name to a name other than the system reserved word. If a system reserved word must be used on an existing system, some system reserved words (not all) can be processed by parameter masking.
There are two methods to exclude reserved words of shielding system in Dameng database:
First: modify the database DM Parameter exclude in ini_ RESERVED_ WORDS
The second is to configure the client dm_svc.conf file, add KEYWORDS configuration item (recommended)
Parameter introduction
1. Database configuration file DM Exclude in ini_ RESERVED_ Words parameters
EXCLUDE_RESERVED_WORDS: list of reserved words to be removed during syntax parsing. The reserved words are separated by commas. The default value is null, which is a static parameter.
V$RESERVED_ Res in words view_ The keyword fixed = n is passed through exclude_ RESERVED_ After the words parameter is set, it will be invalid. V $reserved_ The words view will no longer be recorded.
2,dm_ svc. KEYWORDS configuration item in conf configuration file
KEYWORDS: identifies the user keyword. All strings in the list. If they appear in SQL statements as words, the word will be enclosed in double quotes. This configuration item is mainly used to solve the problem that users need to use the reserved word in DM8 as the object name.
It should be noted that in DM_ svc. When configuring KEYWORDS in the conf file, KEYWORDS should be configured in the "service configuration area", not directly in the "global configuration area".
3,V$RESERVED_WORDS view
V$RESERVED_ The words view records information about database system keywords. The meanings of view fields are as follows (refer to DM8 system administrator's manual):
KEYWORD: Keyword name LENGTH: Keyword length RESERVED: Is it a reserved word RES_SQL: Is it SQL Reserved words cannot be used SQL Identifier in RES_PL: Is it DMSQL Program reserved word, cannot be used DMSQL Identifier in program statement block RES_SCHEMA: Whether it is a mode reserved word cannot be used as a mode identifier RES_VARIABLE: Whether it is a variable reserved word cannot be used as a variable identifier RES_ALIAS: Whether it is an alias reserved word cannot be used as another name identifier RES_FIXED: Can keywords be EXCLUDE，Y may not, N sure
be careful:
V$RESERVED_ The keyword RESERVED=Y in the words view is a system reserved word and cannot be used directly in SQL statements. RES_ Reserved words with fixed = y cannot be shielded. Shielding will affect the use.
In DM8 1-1-190, there are 77 keywords that cannot be masked.
Use example
Environmental description
DB: dmv8 version 1-1-190
OS: KylinV10
Test keywords take PERCENT and ORDER as examples. Where PERCENT is an exclusive keyword and ORDER is a non exclusive keyword.
Confirm keyword information:
SQL> select * from v$reserved_words where keyword in ('PERCENT','ORDER');
Line number KEYWORD LENGTH RESERVED RES_SQL RES_PL RES_SCHEMA RES_VARIABLE RES_ALIAS RES_FIXED
---------- ------- ----------- -------- ------- ------ ---------- ------------ --------- ---------
1 ORDER 5 Y Y Y N N N Y
2 PERCENT 7 Y Y Y N N N N
The first method: modify DM Exclude in ini_ RESERVED_ Words parameters
(1) Create two tables containing the keywords of the test (directly use the reserved word as the column name)
SQL> create table A (PERCENT VARCHAR(10));
create table A (PERCENT VARCHAR(10));
create table A (PERCENT VARCHAR(10));
*
Line 1, Column 35[PERCENT]Error near[-2007]:
Parsing error.
Elapsed time: 0.459(millisecond). Execution number:0.
SQL> create table B (ORDER VARCHAR(10));
create table B (ORDER VARCHAR(10));
create table B (ORDER VARCHAR(10));
*
Line 1, Column 33[ORDER]Error near[-2007]:
Parsing error.
Elapsed time: 0.323(millisecond). Execution number:0.
SQL>
(2) Modify DM Exclude in ini_ RESERVED_ Words parameters
The modified parameter is: EXCLUDE_RESERVED_WORDS =PERCENT,ORDER
Restart the database.
(3) Create test table
SQL> create table A (PERCENT VARCHAR(10));
Operation executed
Elapsed time: 16.635(millisecond). Execution number:400.
SQL> create table B (ORDER VARCHAR(10));
create table B (ORDER VARCHAR(10));
create table B (ORDER VARCHAR(10));
*
Line 1, Column 33[ORDER]Error near[-2007]:
Parsing error.
Elapsed time: 0.409(millisecond). Execution number:0.
Through the test, it can be found that for res_ Reserved word with fixed = y, modify DM Exclude in ini_ RESERVED_ The words parameter cannot be masked. It is not valid for res_ Reserved words with fixed = n can be masked.
Rest res_ Reserved words with fixed = y can also be tested several more.
The second method: configure dm_svc.conf file, add KEYWORDS configuration item
(1) Configuring DM on client machines_ svc. Conf file, add KEYWORDS configuration item
dm_ svc. The conf configuration is as follows:
# Global configuration area TIME_ZONE=(480) LANGUAGE=(cn) DB1=(192.168.15.35:5236) [DB1] # Service configuration area KEYWORDS=PERCENT,ORDER
dm_ svc. Refer to the DM8 system administrator's Manual for the storage path of conf files on different platforms.
It is better to add the database configuration in the "service configuration area" to avoid affecting other databases when there are other databases_ svc. The conf service name is more convenient for applications.
(2) Connect using the configured service name
Admin tool login:
Test tables were created successfully:
SQL> desc AA; Line number NAME TYPE$ NULLABLE ---------- ------- ----------- -------- 1 PERCENT VARCHAR(10) Y Elapsed time: 31.550(millisecond). Execution number:1200. SQL> desc BB; Line number NAME TYPE$ NULLABLE ---------- ----- ----------- -------- 1 ORDER VARCHAR(10) Y Elapsed time: 13.170(millisecond). Execution number:1201.
Through the test, it can be found that for res_ Reserved word with fixed = y, dm_svc.conf can also be masked, but it may cause problems in use.
(3)dm_svc.conf shield res_ Problems caused by reserved words with fixed = y
Take ORDER as an example:
Through DM_ svc. After conf masking, use DM_ svc. The program connected with conf will not be able to use the functions containing separate order words in the database, which will lead to abnormal program functions.
For example, order by cannot be used:
For example, some functions of programs such as management tools are abnormal:
Note: through dm_svc.conf masks reserved words and only affects dm_svc.conf for applications that do not use dm_svc.conf does not affect. As shown in the following figure:
summary
(1) For all SQL statements and object names that use the reserved word of the system keyword, it is most preferred to modify the SQL statement and object name;
(2) For keywords that must be masked by the database, DM is recommended_ svc. Screen the conf file to minimize the impact;
(3)RES_ Reserved words with fixed = y cannot be shielded, and the use will be affected after shielding;
(4) If the object name is enclosed in double quotation marks, the object can be created successfully even if the object name is an unshielded reserved word. However, when using SQL statements on the object name, you also need to put double quotes on the object name.
(5) For more information, please go to Dameng technology community: https://eco.dameng.com