Problem Log:
Today, I used DATAX to extract DB2 data offline, and reported that the USER field is not a number; the field “USER” in the source data
The data type of the source “USER” field is as follows:
[root@p0-01 tmp]$ db2 "describe select TA.TEST.USER from TA.TEST fetch first 1 rows only" Column Information Number of columns: 1 SQL type Type length Column name Name length -------------------- ----------- ------------------- ----------------------- 485 DECIMAL 6, 0 USER 4
The stored data is a number:
[root@p0-01 tmp]$ db2 "select TA.TEST.USER from TA.TEST fetch first 2 rows only" USER -------- 30177. 32875. 2 record(s) selected.
The log shows the value of this field: TESTUSER cannot be inserted into the USER field, “TESTUSER” is the DB2 user I logged into the machine; so it is thought that the USER field is recognized as the current user information output; the DATAX source code cannot be directly modified;
Here is how the DB2 script solves the problem:
Method 1:
DB2 can directly add the table name or table alias before the field for keywords to avoid this problem
Method 2:
Carry out field identification eg: “USER”\
[root@p0-01 tmp]$ db2 "select TA.TEST.USER,"USER"\,USER from TA.TEST fetch first 2 rows only" USER USER 3 -------- -------- ----------------------------------- -------------------------------------------------- -------------------------------------------- 9101. 9101. TESTUSER 9101. 9101. TESTUSER 2 record(s) selected.
Extension:
ISO/ANSI SQL2003 and other IBM? database products contain reserved words that Db2 does not implement; however, it is not recommended to use these words as ordinary identifiers, as this reduces portability.
For portability across IBM database products, the following words should be considered reserved words:
ACTIVATE DOUBLE LOCALE RESULT WLM ADD DROP LOCALTIME RESULT_SET_LOCATOR WRITE AFTER DSSIZE LOCALTIMESTAMP RETURN XMLELEMENT ALIAS DYNAMIC LOCATOR RETURNS XMLEXISTS ALL EACH LOCATORS REVOKE XMLNAMESPACES ALLOCATE EDITPROC LOCK RIGHT YEAR ALLOW ELSE LOCK MAX ROLE YEARS ALTER ELSEIF LOCKSIZE ROLLBACK AND ENABLE LONG ROUND_CEILING ANY ENCODING LOOP ROUND_DOWN AS ENCRYPTION MAINTAINED ROUND_FLOOR ASENSITIVE END MATERIALIZED ROUND_HALF_DOWN ASSOCIATE END-EXEC MAXVALUE ROUND_HALF_EVEN ASUTIME ENDING MICROSECOND ROUND_HALF_UP AT ERASE MICROSECONDS ROUND_UP ATTRIBUTES ESCAPE MINUTE ROUTINE AUDIT EVERY MINUTES ROW AUTHORIZATION EXCEPT MINVALUE ROWNUMBER AUX EXCEPTION MODE ROWS AUXILIARY EXCLUDING MODIFIES ROWSET BEFORE EXCLUSIVE MONTH ROW_NUMBER BEGIN EXECUTE MONTHS RRN BETWEEN EXISTS NAN RUN BINARY EXIT NEW SAVEPOINT BUFFERPOOL EXPLAIN NEW_TABLE SCHEMA BY EXTENDED NEXTVAL SCRATCHPAD CACHE EXTERNAL NO SCROLL CALL EXTRACT NOCACHE SEARCH CALLED FENCED NOCYCLE SECOND CAPTURE FETCH NODENAME SECONDS CARDINALITY FIELDPROC NODENUMBER SECQTY CASCADED FILE NOMAXVALUE SECURITY CASE FINAL NOMINVALUE SELECT CAST FIRST1 NONE SENSITIVE CCSID FOR NOORDER SEQUENCE CHAR FOREIGN NORMALIZED SESSION CHARACTER FREE NOT 2 SESSION_USER CHECK FROM NOT NULL SET CLONE FULL NULL SIGNAL CLOSE FUNCTION NULLS SIMPLE CLUSTER GENERAL NUMPARTS SNAN COLLECTION GENERATED OBID SOME COLLID GET OF SOURCE COLUMN GLOBAL OFF SPECIFIC COMMENT GO OFFSET SQL COMMIT GOTO OLD SQLID CONCAT GRANT OLD_TABLE STACKED CONDITION GRAPHIC ON STANDARD CONNECT GROUP OPEN START CONNECTION HANDLER OPTIMIZATION STARTING CONSTRAINT HASH OPTIMIZE STATEMENT CONTAINS HASHED_VALUE OPTION STATIC CONTINUE HAVING OR STATMENT COUNT HINT ORDER STAY COUNT_BIG HOLD OUT STOGROUP CREATE HOUR OUTER STORES CROSS HOURS OVER STYLE CURRENT IDENTITY OVERRIDING SUBSTRING CURRENT_DATE IF PACKAGE SUMMARY CURRENT_LC_CTYPE IMMEDIATE PADDED SYNONYM CURRENT_PATH IMPORT PAGE SIZE SYSFUN CURRENT_SCHEMA IN PARAMETER SYSIBM CURRENT_SERVER INCLUDING PART SYSPROC CURRENT_TIME INCLUSIVE PARTITION SYSTEM CURRENT_TIMESTAMP INCREMENT PARTITIONED SYSTEM_USER CURRENT_TIMEZONE INDEX PARTITIONING TABLE CURRENT_USER INDICATOR PARTITIONS TABLESPACE CURSOR INDICATORS PASSWORD THEN CYCLE INF PATH TIME DATA INFINITY PERCENT TIMESTAMP DATABASE INHERIT PIECE SIZE TO DATAPARTITIONNAME INNER PLAN TRANSACTION DATA PARTITION NUM INOUT POSITION TRIGGER DATE INSENSITIVE PRECISION TRIM DAY INSERT PREPARE TRUNCATE DAYS INTEGRITY PREVVAL TYPE DB2 GENERAL INTERSECT PRIMARY UNDO DB2GENRL INTO PRIQTY UNION DB2SQL IS PRIVILEGES UNIQUE DBINFO ISNULL PROCEDURE UNTIL DBPARTITIONNAME ISOBID PROGRAM UPDATE DBPARTITION NUM ISOLATION PSID USAGE DEALLOCATE ITERATE PUBLIC USER DECLARE JAR QUERY USING DEFAULT JAVA QUERYNO VALIDPROC DEFAULTS JOIN RANGE VALUE DEFINITION KEEP RANK VALUES DELETE KEY READ VARIABLE DENSERANK LABEL READS VARIANT DENSE_RANK LANGUAGE RECOVERY VCAT DESCRIBE LAST3 REFERENCES VERSION DESCRIPTOR LATERAL REFERENCING VIEW DETERMINISTIC LC_CTYPE REFRESH VOLATILE DIAGNOSTICS LEAVE RELEASE VOLUMES DISABLE LEFT RENAME WHEN DISALLOW LIKE REPEAT WHENEVER DISCONNECT LIMIT RESET WHERE DISTINCT LINKTYPE RESIGNAL WHILE DO LOCAL RESTART WITH DOCUMENT LOCALDATE RESTRICT WITHOUT
ISO/ANSI SQL2003 reserved words not in the preceding list:
ABS GROUPING REGR_INTERCEPT
ARE INT REGR_R2
ARRAY INTEGER REGR_SLOPE
ASYMMETRIC INTERSECTION REGR_SXX
ATOMIC INTERVAL REGR_SXY
AVG LARGE REGR_SYY
BIGINT LEADING ROLLUP
BLOB LN SCOPE
BOOLEAN LOWER SIMILAR
BOTH MATCH SMALLINT
CEIL MAX SPECIFICTYPE
CEILING MEMBER SQL EXCEPTION
CHAR_LENGTH MERGE SQLSTATE
CHARACTER_LENGTH METHOD SQLWARNING
CLOB MIN SQRT
COALESCE MOD STDDEV_POP
COLLATE MODULE STDDEV_SAMP
COLLECT MULTISET SUBMULTISET
CONVERT NATIONAL SUM
CORR NATURAL SYMMETRIC
CORRESPONDING NCHAR TABLESAMPLE
COVAR_POP NCLOB TIMEZONE_HOUR
COVAR_SAMP NORMALIZE TIMEZONE_MINUTE
CUBE NULLIF TRAILING
CUME_DIST NUMERIC TRANSLATE
CURRENT_DEFAULT_TRANSFORM_GROUP OCTET_LENGTH TRANSLATION
CURRENT_ROLE ONLY TREAT
CURRENT_TRANSFORM_GROUP_FOR_TYPE OVERLAPS TRUE
DEC OVERLAY UESCAPE
DECIMAL PERCENT_RANK UNKNOWN
DEREF PERCENTILE_CONT UNNEST
ELEMENT PERCENTILE_DISC UPPER
EXEC POWER VAR_POP
EXP REAL VAR_SAMP
FALSE RECURSIVE VARBINARY
FILTER REF VARCHAR
FLOAT REGR_AVGX VARYING
FLOOR REGR_AVGY WIDTH_BUCKET
FUSION REGR_COUNT WINDOW
WITHIN
Reference: DB2 reserved schemas and reserved words